OCP-1Z0-051-2015-8题

QUESTION NO: 8
Where can sub queries be used? (Choose all that apply)

A. field names in the SELECT statement

B. the FROM clause in the SELECT statement
C. the HAVING clause in the SELECT statement
D. the GROUP BY clause in the SELECT statement
E. the WHERE clause in only the SELECT statement
F. the WHERE clause in SELECT as well as all DML statements
Answer: A,B,C,F

Explanation:
SUBQUERIES can be used in the SELECT list and in the FROM, WHERE, and HAVING clauses
of a query. 所以B、C正确
A subquery can have any of the usual clauses for selection and projection. The following are
required clauses:
A SELECT list
A FROM clause
The following are optional clauses:
WHERE
GROUP BY
HAVING
The subquery (or subqueries) within a statement must be executed before the parent query that

calls it, in order that the results of the subquery can be passed to the parent.

A：子查询可以作为select语句中的字段名称

D：GROUP BY 只能跟列名，不能跟子句

E：where子句中select语句唯一，错误

F：where子句中查询所有DML语句

DML（data manipulation language）：
       它们是SELECT、UPDATE、INSERT、DELETE，这4条命令是用来对数据库里的数据进行操作的语言
DDL（data definition language）：
       DDL比DML要多，主要的命令有CREATE、ALTER、DROP等，DDL主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用
DCL（Data Control Language）：
       是数据库控制功能。是用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句。在默认状态下，只有sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权力执行DCL

 

举例说明DML语句中使用子查询

            --insert中 

              insert into employee (id,name,title,salary) 

               select emptno，ename ,job,sal from emp; 

            --update中 

              update emp set (sal,comm)= 

                ( 

                  select sal,comm from emp 

                  where ename ='SMITH' 

                )         

              where job =  

                ( 

select job from emp where ename ='SMITH'

                )  ; 

            --去除销售部门 

              delete from emp where deptno = 

                ( 

                  select deptno from dept 

                  where dname ='SALES' 

                )  ;