ITPub博客

首页 > 数据库 > Oracle > OCP课程8:SQL之使用子查询

OCP课程8:SQL之使用子查询

原创 Oracle 作者:stonebox1122 时间:2015-11-24 14:23:28 0 删除 编辑

1、子查询语法

clipboard

  • 主查询执行前,子查询先执行,而且只执行一次
  • 主查询使用子查询的结果

 

例子:查询薪水比Abel高的人员的名字和薪水

SQL> select last_name,salary

  2  from employees

  3  where salary>(select salary from employees where last_name='Abel');

LAST_NAME                     SALARY

------------------------- ----------

Hartstein                      13000

 

使用子查询的注意事项:

  • 子查询需要使用括号括起来
  • 子查询一般放在比较条件的右边
  • 只有在进行Top-N分析的时候,子查询才可以使用order by进行排序,其他情况都不行
  • 对单行子查询使用单行操作符,对多行子查询使用多行操作符

 

例子:子查询如果不加括号会报错

SQL> select last_name,salary

  2  from employees

  3  where salary>select salary from employees where last_name='Abel';

where salary>select salary from employees where last_name='Abel'

             *

ERROR at line 3:

ORA-00936: missing expression

 

例子:子查询可以放在比较条件的左边,不过一般都放在右边,便于阅读和理解。

SQL> select last_name,salary

  2  from employees

  3  where (select salary from employees where last_name='Abel')<salary; </salary;<>

LAST_NAME                     SALARY

------------------------- ----------

Hartstein                      13000

 

例子:一般情况下子查询加上order by进行排序会报错

SQL> select last_name,salary

  2  from employees

  3  where salary>(select salary from employees where last_name='Abel' order by 1);

where salary>(select salary from employees where last_name='Abel' order by 1)

                                                                  *

ERROR at line 3:

ORA-00907: missing right parenthesis

 

例子:查找薪水最大的10名员工,在进行Top-N分析的时候,子查询才可以使用order by进行排序

SQL> select *

  2  from (select last_name,salary from employees order by 2 desc)

  3  where rownum<11;

LAST_NAME                     SALARY

------------------------- ----------

King                           24000

 

 

2、子查询类型

  • 单行子查询,只返回一行结果
  • 多行子查询,返回多行结果

 

 

(1)单行子查询

单行子查询比较操作符

clipboard[1]

 

例子:查找job_id与employee_id为141号员工相同,薪水比143号员工大的人员

SQL> select last_name,job_id,salary

  2  from employees

  3  where job_id=(select job_id from employees where employee_id=141)

  4  and   salary>(select salary from employees where employee_id=143);

LAST_NAME                 JOB_ID         SALARY

------------------------- ---------- ----------

Nayer                     ST_CLERK         3200

 

在子查询里面使用组函数

例子:查找最低薪水员工的信息

SQL> select last_name,job_id,salary

  2  from employees

  3  where salary=(select min(salary) from employees);

LAST_NAME                 JOB_ID         SALARY

------------------------- ---------- ----------

Olson                     ST_CLERK         2100

 

在having子句使用子查询

例子:查找部门最小薪水比50部门最小薪水大的部门

SQL> select department_id,min(salary)

  2  from employees

  3  group by department_id

  4  having min(salary)>(select min(salary) from employees where department_id=50);

DEPARTMENT_ID MIN(SALARY)

------------- -----------

          100        6900

如果将上面语句子查询中的where替换成group by就会报错,因为使用group by后,子查询结果为多行,而“>”为单行操作符

SQL> select department_id,min(salary)

  2  from employees

  3  group by department_id

  4  having min(salary)>(select min(salary) from employees group by department_id=50);

having min(salary)>(select min(salary) from employees group by department_id=50)

                                                                            *

ERROR at line 4:

ORA-00907: missing right parenthesis

 

 

(2)多行子查询

如果子查询结果返回多行,则需要使用多行操作符,再使用单行操作符会报错

 

例子:子查询返回多行,使用单行操作符报错

SQL> select employee_id,last_name from employees

  2  where salary=(select min(salary) from employees group by department_id);

where salary=(select min(salary) from employees group by department_id)

              *

ERROR at line 2:

ORA-01427: single-row subquery returns more than one row

 

如果子查询返回0行,则主查询也返回0行,但是在单行子查询中,通过使用“=”进行判断的时候,即使主查询返回0行,也不能说明子查询返回0行,因为子查询有可能返回的是null,而null是不能使用“=”进行判断的。

例子:查询与人员编号100有相同的管理人员的员工信息

SQL> select last_name,manager_id from employees

  2  where manager_id=(select manager_id from employees where employee_id=100);

no rows selected

子查询的结果为null

SQL> select manager_id from employees where employee_id=100;

MANAGER_ID

----------

 

多行子查询比较操作符

clipboard[2]

前面都可以使用not

 

在多行子查询中使用any操作符

例子:查询比职位为“IT_PROG”的任何一位员工的薪水低的员工信息(实际上就是比职位为“IT_PROG”的最大薪水的员工薪水小,小于最大的,如果是大于any,就是大于最小的)

SQL> select employee_id,last_name,job_id,salary from employees

  2  where salary<any(select salary="" from="" employees="" where="" job_id="IT_PROG" )

  3  and job_id<>'IT_PROG';

EMPLOYEE_ID LAST_NAME                 JOB_ID         SALARY

----------- ------------------------- ---------- ----------

        132 Olson                     ST_CLERK         2100

 

在多行子查询中使用all操作符

例子:查询比职位为“IT_PROG”的所有员工的薪水低的员工信息(实际上就是比职位为“IT_PROG”的最小薪水的员工薪水小,小于最小的,如果是大于all,就是大于最大的)

SQL> select employee_id,last_name,job_id,salary from employees

  2  where salary<all(select salary="" from="" employees="" where="" job_id="IT_PROG" )

  3  and job_id<>'IT_PROG';

EMPLOYEE_ID LAST_NAME                 JOB_ID         SALARY

----------- ------------------------- ---------- ----------

        185 Bull                      SH_CLERK         4100

 

在多行子查询中使用in或者not in操作符

例子:查找是管理人员的员工姓名和不是管理人员的员工名字

SQL> select emp.last_name from employees emp

  2  where emp.employee_id in (select mgr.manager_id from employees mgr);

LAST_NAME

-------------------------

Zlotkey

18 rows selected.

SQL> select emp.last_name from employees emp

  2  where emp.employee_id not in (select mgr.manager_id from employees mgr);

no rows selected

这里因为如果你使用in只需要和子查询返回结果的任何一个匹配都可以,而使用not in则需要和子查询返回结果的所有进行比较,包括null,而与null进行计算,都为null,故结果为0行,可以先在子查询中排除null

SQL> select emp.last_name from employees emp

  2  where emp.employee_id not in (select mgr.manager_id from employees mgr where mgr.manager_id is not null);

LAST_NAME

-------------------------

Whalen

89 rows selected.

 

 

3、相关习题

(1)View the Exhibit and examine the description of the EMPLOYEES table. You want to display the EMPLOYEE_ID, FIRST_NAME, and DEPARTMENT_ID for all the employees who work in the same department and have the same manager as that of the employee having EMPLOYEE_ID 104. To accomplish the task, you execute the following SQL statement: SELECT employee_id, first_name, department_id FROM employees WHERE (manager_id, department_id) =(SELECT department_id, manager_id FROM employees WHERE employee_id = 104) AND employee_id <> 104  When you execute the statement it does not produce the desired output. What is the reason for this ?

A.The WHERE clause condition in the main query is using the = comparison operator, instead of EXISTS.
B.The WHERE clause condition in the main query is using the = comparison operator, instead of the IN operator.
C.The WHERE clause condition in the main query is using the=comparison operator, instead of the =ANY operator.
D.The columns in the WHERE clause  condition of the main query and the columns selected in the subquery should be in the same order.

 

答案:D

 

 

(2)View the Exhibit and examine PRODUCTS and ORDER_ITEMS tables. You executed the following query to display PRODUCT_NAME and the number of times the product has been ordered: SELECT p.product_name, i.item_cnt FROM (SELECT product_id, COUNT (*) item_cnt FROM order_items GROUP BY product_id) i RIGHT OUTER JOIN products p ON i.product_id = p.product_id? What would happen when the above statement is executed?

clipboard[3]

A.The statement would execute successfully to produce the required output.

B.The statement would not execute because inline views and outer joins cannot be used together.

C.The statement would not execute because the ITEM_CNT alias cannot be displayed in the outer query.

D.The statement would not execute because the GROUP BY clause cannot be used in the inline view.

 

答案:A

 

 

(3)A non-?correlated subquery can be defined as ____.

A.a set of sequential queries, all of which must always return a single value

B.a set of sequential queries, all of which must return values from the same table

C.a SELECT statement that can be embedded in a clause of another SELECT statement only

D.a set of one or more sequential queries in which generally the result of the inner query is used as the search value in the outer query

 

答案:D

 

 

(4)Which two statements are true regarding operators used with subqueries? (Choose two.)

A. The NOT IN operator is equivalent to IS NULL.

B. The <any operator="" means="" less="" than="" the="" maximum.

C. =ANY and =ALL operators have the same functionality.

D. The IN operator cannot be used in single-row subqueries.

E. The NOT operator can be used with IN, ANY and ALL operators.

 

答案:BE

 

 

(5)Which two statements are true regarding multiple?row subqueries? (Choose two.)

A.They can contain group functions.

B.They always contain a subquery within a subquery.

C.They use the < ALL operator to imply less than the maximum.

D.They can be used to retrieve multiple rows from a single table only.

E.They should not be used with the NOT IN operator in the main query if NULL is likely to be a part of the result of the subquery.

 

答案:AE

 

 

(6)View the Exhibit and examine the structure of the EMPLOYEES table. You want to know the FIRST_NAME and SALARY for all employees who have the same manager as that of the employee with the first name 'Neena' and have salary equal to or greater than that of 'Neena'. Which SQL statement would give you the desired result?

A.SELECT  first_name,  salary  FROM employees  WHERE (manager_id,  salary)  >=  ALL  (SELECT manager_id, salary FROM employees WHERE first_name = 'Neena' ) AND first_name <> 'Neena';

B.SELECTfirst_name, salary FROM employees WHERE (manager_id, salary) >= (SELECT manager_id, salary FROM employees WHERE first_name = 'Neena' ) AND first_name <> 'Neena';

C.SELECT  first_name,  salary  FROM  employees WHERE (manager_id,  salary)  >=  ANY  (SELECT manager_id, salary FROM employees WHERE first_name = 'Neena' ) AND first_name <> 'Neena';

D.SELECT first_name, salary FROM employees WHERE ( manager_id = (SELECT manager_id FROM employees WHERE first_name = 'Neena' ) AND salary >= ( SELECT salary FROM employees WHERE first_name = 'Neena' ) ) AND first_name <> 'Neena';

 

答案:D

 

 

(7)View  the  Exhibit  and examine  the  structure  of  the  ORDERS table. You have  to  display ORDER_ID, ORDER_DATE, and CUSTOMER_ID for all those orders that were placed after the last order placed by the customer whose CUSTOMER_ID is 101. Which query would give you the desired output?

A.SELECT order_id, order_date FROM orders WHERE order_date > ALL (SELECT MAX(order_date) FROM orders ) AND customer_id = 101;
B.SELECT order_id, order_date FROM orders WHERE order_date > ANY (SELECT order_date FROMorders WHERE customer_id = 101);
C.SELECT order_id, order_date FROM orders WHERE order_date > ALL (SELECT order_date FROM orders WHERE customer_id = 101);
D.SELECT order_id, order_date FROM orders WHERE order_date IN (SELECT  order_date FROM orders WHERE customer_id = 101);

 

答案:C

 

 

(8)View the Exhibit and examine the data in the PRODUCT_INFORMATION table. Which two tasks would require subqueries?(Choose two.)

clipboard[4]

A.displaying the minimum list price for each product status.
B.displaying all supplier IDs whose average list price is more than 500.
C.displaying the number of products whose list prices are more than the average list price.
D.displaying all the products whose minimum list prices are more than the average list price of products having the product status orderable.
E.displaying  the  total  number  of products supplied  by  supplier  102071  and  having product status OBSOLETE.

 

答案:CD

 

 

(9)Which two statements are true regarding subqueries?(Choose two.)
A.Only two subqueries can be placed at one level.
B.A subquery can be used to access data from one or more tables or views.
C.If the subquery returns 0 rows, then the value returned by the subquery expression is NULL.
D.The columns in a subquery must always be qualified with the name or alias of the table used.
E.A subquery in the WHERE clause of a SELECT statement can be nested up to three levels only.

 

答案:BC

 

 

(10)View the Exhibit and examine the structure of the ORDERS table. Which task would require subqueries?

A. displaying the total order value for sales representatives 161 and 163

B. displaying the order total for sales representative 161 in the year 1999

C. displaying the number of orders that have order mode online and order date in 1999

D. displaying the number of orders whose order total is more than the average order total for all online orders

 

答案:D

 

 

(11)Which two statements are true regarding subqueries?(Choose two.)

A.The ORDER BY clause can be used in the subquery.
B.A subquery can be used in the FROM clause of a SELECT statement.
C.If the subquery returns NULL, the main query may still return result rows.
D.A subquery can be placed in a WHERE clause, GROUP BY clause, or a HAVING clause.
E.Logical operators, such as AND, OR and NOT, cannot be used in the WHERE clause of a subquery.

 

答案:AB

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28536251/viewspace-1845385/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-12-13

  • 博文量
    204
  • 访问量
    1080360