清华大学计算中心ORACLE培训资料SQL讲稿.ppt_第1页
清华大学计算中心ORACLE培训资料SQL讲稿.ppt_第2页
清华大学计算中心ORACLE培训资料SQL讲稿.ppt_第3页
清华大学计算中心ORACLE培训资料SQL讲稿.ppt_第4页
清华大学计算中心ORACLE培训资料SQL讲稿.ppt_第5页
已阅读5页,还剩22页未读 继续免费阅读

下载本文档

版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领

文档简介

1、SQL语言与SQL*PLUS,Oracle9i 数据库语言,SQL命令,SQL关键字(以下命令为SQL命令,以“;”结束) Alter、 Audit、Commit、Comment、 Create、Delete Drop、Grant、Insert、Lock、NoAudit 、Rename Revoke、Rollback、Select 、Update 、Validate。,SQL*PLUS命令,SQL*PLUS关键字(以下命令以“CR”结束) 、 #、/、 Accept、 Append、 Break、 Btitle、 Change 、 Clear、 Column、 Compute、 Connect

2、、 Copy、 Define、 Del、 Describe、 Disconnect、 Document、 Edit、 Exit、 Get 、 Help、 Host 、 Input、 List、 Newpage 、 Pause、 Quit、 Remark、 Run、 Save、 Set、 Show、 Spool、 Start、 Timing、 Ttitle、 Undefine、,编辑和运行SQL缓冲区的SQL*PLUS命令,命令 缩写 解释APPEND text A text 行尾增加 text CHANGE C/old/new 在当前行中将old换为/new CHANGE C/text/ 在当

3、前行中删除 textCLEAR Buffer CL Buff 清除缓冲区 DEL DEL 删除当前行 INPUT I 增加一行 INPUT text I text 增加有text组成的行 LINE L 显示缓冲区内容 LINE n L n 显示第 n 行 RUN R 运行SQL缓冲区命令,Oracle Quiz,QL 测 试 Oracle Assessment Tests,Oracle Assessment Tests,You query the database with this command: SELECT name FROM employee WHERE name LIKE _a%;

4、Which names are displayed?A. names starting with aB. names starting with a or AC. names containing a as the second letterD. names containing a as any letter except the first,Oracle Assessment Tests,For which task would it be most appropriate to use the keyword DISTINCT?A. Identify duplicate rows in

5、a table.B. Identify which column has unique data.C. Eliminate duplicate columns in a table.D. Eliminate duplicate rows in the result set.,Oracle Assessment Tests,You query the database with this SQLstatement: SELECT name,NVL(salary,0) FROM employee WHRER salary IS NULL ORDER BY name;What is displaye

6、d for the SALARY columnwhen a NULL value is returned? A. 0 B. NULL C.spaces D.nothing,For which task would you use the BETWEEN operator?A. Query the database for unknown values.B. Query the database for a range of values.C. Query the database for a character pattern.D. Query the database for values

7、in a specified list. (Answers: C D A B),Oracle Assessment Tests,Oracle Quiz,DML,DCL 测 试 Oracle Assessment Tests,Oracle Assessment Tests,What happens when you update a tables without a WHERE clause?A. The statement will not execute.B. Only the rows specified will be updated.C. All of the rows in the

8、table will be updated.D. The statement will execute,but no change will be made.,Oracle Assessment Tests,Which commands cause a transaction to end?A. ALTERB. GRANT.C. DELETE.D. INSERTE. UPDATE.F. ROLLBACK,Oracle Assessment Tests,The Correct Answers 1.C2.A B F,Oracle Quiz,DDL 测 试 Oracle Assessment Tes

9、ts,Oracle Assessment Tests,You query the database with this command: SELECT object_name FROM user_objects WHERE object_type =TABLE;Which values are displayed?A. names of all objects you ownB. only the names of tables you ownC. names of all objects you have privileges toD. only the names of tables yo

10、u have privileges to access.,Oracle Assessment Tests,You query the database with this command: SELECT object_name FROM all_objects WHERE object_type =TABLE;Which values are displayed?A. only names of table you ownB. only names of objects you ownC. Only names of all objects you have privileges to acc

11、ess.D. only the names of all the tables you can access.,Oracle Assessment Tests,You attempt to create the ALPHA_3000 tablewith this statement:1. CREATE TABLE alpha_30002. (3000_id number(9)3. CONSTRAINT alpha_3000_id_pk PRIMARY KEY,4. name VARCHAR2(25),5. title VARCHAR2(25),6. Idname VARCHAR2(25)7.

12、CONSTRAINT alpha_3000_id_nn NOT NULL); What line in the statement will cause an error? A. 1 B. 2 C.3 D.7,Oracle Assessment Tests,You alter the database with this command: ALTER TABLE employee ADD (end_date DATE);Which task is accomplished ?A. A constraint is added to an existing column.B. A constrai

13、nt is modified on an existing column.C. A new column with a constraint is added to the table.D. A new column with no constraint is added to the table.,You attempt to create a view with this command: CREATE VIEW last_first_vu ASSELECT first_name| | last_name“Employee Names” FROM employee ORDER BY las

14、t_name,first_name;Which clause causes an error? A. FROM employee.B. ORDER BY last_name,first_name.C. CREATE VIEW last_first_vuD. SELECT first_name| |last_name”Employee Names”,Oracle Assessment Tests,Oracle Assessment Tests,You alter the database with this command: RENAME streets to CITY; Which task

15、is accomplished ?A. The streets user is renamed city.B. The STREETS table is renamed CITY.C. The STREETS column is renamed CITY.D. The streets constraints is renamed city.,Oracle Assessment Tests,The Correct Answers B,D,B,D,B,B,使用 SQL*PLUS命令,SQL*PLUS是Oracle对SQL语言的扩充,是一中交互式的表报生成工具.它使用SQL命令从Oracle中查询信

16、息,用SQL*PLUS命令设置控制表报的输出格式,对SQL命令加以扩充,可以对题目,列标题,以及汇总信息进行控制. 定义表头与表尾 定义表头: SQLTtitle 描述 定义表尾: SQLBtitle 描述 option: Left 左对齐 Center 居中 Right 右对齐 使用TITLE 则报表输出在每页加上日期及页号,SQL*PLUS命令总结,聚组函数的使用,聚组函数是从一组中返回汇总信息,聚组函数有SUM,COUNTCOUNT DISTINCT,MAX,MIN,AVG,STDDEV 计算max,min,avg,suma.SQLSELECT MIN(sal),MAX(sal),AVG

17、(sal), SUM(sal) FROM emp;b.找出具有最高工资的员工? SQLSELECT ename,job,sal FROM emp WHERE sal=(SELECT MAX(sal) FROM emp); COUNT与COUNT DISTINCTa.求emp表中总行数? SQLSELECT COUNT(*) FROM emp;b.求job的行数? SQLSELECT COUNT(job) FROM emp;c.求有多少工中?SQLSELECT COUNT(DISTINCT job) FROM emp;,SELECT 字句中目标的一致性不能把单行函数与聚组函数混在一起使用,如 S

18、QLSELECT ename,SUM(sal) FROM emp; 单行目标 聚组函数 使用GROUP BY 分组a.查询从事每一个工种的员工的工资总和? SQLSELECT job,SUM(sal) FROM emp GROUP BY job;b.求出从事每一个工种的员工总数? SQLSELECT job,COUNT(*) FROM emp GROUP BY job;c.查询每个部门的最高工资及工资总和?,句子语法错误!,SQLSELECT deptno,SUM(sal),MAX(sal) FROM emp GROUP by deptno; 按多个条件分组SQLSELECT deptno,job,COUNT(*) FROM emp GROUP BY deptno,job; 使用Having,选则满足条件的组SQLSELECT deptno,SUM(s

温馨提示

  • 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
  • 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
  • 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
  • 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
  • 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
  • 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
  • 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。

评论

0/150

提交评论