Sql Certificate 简明教程

SQL - Subqueries to Solve Queries Questions

1. Which of the following are the types of sub-queries?

*答案：C. *子查询是嵌套在另一个查询的 SELECT、FROM、HAVING 或 WHERE 子句中的一个完整查询。子查询必须括在圆括号中，且至少具有 SELECT 和 FROM 子句。单行子查询和多行子查询是子查询的主要类型

*Answer: C. *A subquery is a complete query nested in the SELECT,FROM, HAVING, or WHERE clause of another query.The subquery must be enclosed in parentheses and have a SELECT and a FROM clause, at a minimum. Single row sub-queries and multi-row sub-queries are the main types of sub-queries

2.Which of the following is true about sub-queries?

*答案：D. *子查询始终在主查询执行之前执行。先完成子查询。子查询的结果用作外部查询的输入。

*Answer: D. *The sub-query always executes before the execution of the main query.Subqueries are completed first.The result of the subquery is used as input for the outer query.

3.Which of the following is true about the result of a sub-query?

*答案：C. *先完成子查询。子查询的结果用作外部查询的输入。

*Answer: C. * Subqueries are completed first.The result of the subquery is used as input for the outer query.

4.Which of the following clause is mandatorily used in a sub-query?

*答案：A. *子查询就像必须以 SELECT 子句开头的任何其他查询。它们包含在外查询中。

*Answer: A. *A sub-query is just like any other query which has to start with a SELECT clause. They are contained within an outer query.

5. Which of the following is a method for writing a sub-query in a main query?

*答案：D。*子查询是嵌套在另一个查询的 SELECT、FROM、HAVING 或 WHERE 从句中的完整查询。子查询必须用括号括起来，并且至少具有 SELECT 和 FROM 从句。

*Answer: D. *A subquery is a complete query nested in the SELECT, FROM, HAVING, or WHERE clause of another query.The subquery must be enclosed in parentheses and have a SELECT and a FROM clause, at a minimum.

6.In the given scenarios, which one would appropriately justify the usage of sub-query?

回答：C。

*Answer: C. *

7.In which of the following clauses can a sub-query be used?

*答案：D。*子查询与常规查询没有什么不同。它可以使用 SELECT 语句的所有基本从句。

*Answer: D. *A sub-query is not different from a normal query. It can make use of all the primary clauses of a SELECT statement.

8.Which of the following single-row operators can be used for writing a sub-query?

*答案：D。*单行运算符包括 =、>、<、>=、⇐ 和 <>.

*Answer: D. *Single-row operators include =, >, <, >=, ⇐, and <>.

9.Which of the following multi-row operators can be used with a sub-query?

*答案：D。*多行子查询返回多行结果。可用于多行子查询的运算符包括 IN、ALL、ANY 和 EXISTS。

*Answer: D. *Multiple-row subqueries return more than one row of results.Operators that can be used with multiple-row subqueries include IN, ALL, ANY, and EXISTS.

10.What is true about the output obtained from a sub-query?

*答案：C。*子查询首先完成。子查询的结果用作外部查询的输入。

*Answer: C. *Subqueries are completed first.The result of the subquery is used as input for the outer query.

11.You need to find the salaries for all the employees who have a higher salary than the Vice President of a company 'ABC'.Which of the following queries will give you the required result? (Consider the table structure as given)

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

*答案：A。*在选项“A”中，内部子查询将 VP 的薪水作为结果提供给外部查询。

*Answer: A. *In the option 'A', the inner sub-query gives the VP’s salary as a result to the outer query.

12.What among the following is true about sub-queries?

*答案：A。*子查询可以根据查询缩进和可用性放在比较运算符的左侧或右侧。

*Answer: A. *Sub queries can be placed on left or right hand side of the comparison operator depending on the query indentation and usability.

13. What will be the outcome of the following query? (Consider the given table structure)

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

SELECT first_name, last_name, salary
FROM employees
WHERE salary ANY (SELECT salary FROM employees);

*答案：C。*多行运算符不能用于单行子查询，反之亦然。

*Answer: C. *Multi-row operators cannot be used in single-row sub-queries and vice versa.

14.Which of the following is true about single-row sub-queries?

*答案：C。*单行子查询最多可以返回一个值。

*Answer: C. *A single-row subquery can return a maximum of one value.

15.What is true about multi-row sub-queries?

答案：D。 多列子查询返回多个列作为结果集，多行子查询从内部查询中返回多行。

*Answer: D. *Multi-column sub-queries return more than one column in their result set, multi-row sub-queries return more than one row from the inner query.

16.What among the following is true about single-row sub-queries?

回答：C。

*Answer: C. *

17.Which of the following operators cannot be used in a sub-query?

答案：A。 单行运算符包括 =、>、<、>=、⇐ 和 <>. 可用于多行子查询的多行运算符包括 IN、ALL、ANY 和 EXISTS。

*Answer: A. *Single-row operators include =, >, <, >=, ⇐, and <>. Multi-row operators that can be used with multiple-row subqueries include IN, ALL, ANY, and EXISTS.

请检查图表，然后回答以下 18 至 21 个问题。

*Examine the exhibit and answer the questions 18 to 21 that follow. *

18.You need to find out the names of all employees who belong to the same department as the employee 'Jessica Butcher' who is in department 100 and has an employee ID 40. Which of the following queries will be correct?

答案：D。 “D” 比 “C” 更合适，因为它根据唯一的员工 ID 筛选，并且确保子查询仅返回一行。如果存在多个具有相同名字的员工，则 “C” 可能会失败。

*Answer: D. *'D' is more appropriate than 'C' because it filters on employee id which is unique and ensures that the sub-query will return single row only. 'C' can fail if there are more than one employee with the same first and last name.

19.You need to find out the employees which belong to the department of 'Jessica Butcher' and have salary greater than the salary of 'Jessica Butcher' who has an employee ID of 40. Which of the following queries will work?

答案：C。 可以在一个 SQL 语句中编写多个子查询，以添加多个条件。

*Answer: C. *More than one sub-query can be written in one SQL statement to add more than one condition.

20.Based on the answers for questions 18th and 19th, what type of sub-queries is used by them?

答案：A。 上述问题 18 和 19 展示了在 SELECT 语句中使用子查询。

*Answer: A. *The questions 18th and 19th given above demonstrate the usage sub-queries in a SELECT statement.

21.Consider two statements about outer and inner queries in context of SQL sub-queries?

The inner queries can get data from only one table

ii. 内部查询可以从多张表获取数据

ii. The inner queries can get data from more than one table

以上哪个说法是正确的？

Which of the above statements are true?

答案：B。 子查询可以从多张表获取数据。

*Answer: B. *Sub-queries can fetch data from more than one table.

Examine the table structure as follows and answer the questions 22 to 27 that follow:

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

22. 以下查询的结果是什么？（选择最合适的答案）

*22.What will be the outcome of the following query? (Choose the most appropriate answer) *

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

SELECT last_name, job_id, salary
FROM employees
WHERE salary = (SELECT max(salary)
FROM employees);

回答：A 可以在子查询中使用组函数。

*Answer: A. *A group function can be used within a sub-query.

23.What will be the outcome of the query that follows?

SELECT first_name, last_name, min(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) >
		(SELECT min(salary)
		FROM employees
		WHERE department_id = 100);

回答：A 正如所示，可以在子查询中使用 HAVING 子句

*Answer: A. *HAVING clause can be used in sub-queries as shown

24.You need to find the job which has a maximum average salary.Which of the following queries will give you the required results?

回答：D 子查询可以使用组函数和 HAVING 子句对组进行限制。

*Answer: D. *Sub-queries can make use of group functions and HAVING clause to restrict the groups.

25. 以下查询引发错误。从选项中选择错误的正确原因。

*25.The following query throws an error. Choose the correct reason for the error as given in the options. *

SELECT first_name, last_name
FROM employees
WHERE commission_pct  = (SELECT min(commission_pct )
          FROM employees
          GROUP BY department_id);

回答：C、D GROUP BY 子句提供每个部门的最低佣金百分比，因此会将多个结果返回到主查询，从而引发错误。

*Answer: C, D. *The GROUP BY clause gives the minimum commission_pct for each department and hence multiple results are fetched to the main query giving an error.

26.Consider the query given below.How many records will be returned as a result of the above query? (Assuming the no employee with job id XX exists in the company)

SELECT first_name, last_name
FROM employees
WHERE salary = (SELECT salary
		FROM employees
		WHERE job_id = 'XX');

回答：C 由于公司中没有 job_id 为“XX”的员工，所以子查询不会返回任何结果，而将此结果与主查询中的 job_id 进行比较时会得到 0。

*Answer: C. *Since there is no employee with job_id "XX" in the company, the sub-query returns no result, which when equated to job_id in the main query gives a 0.

27.What happens if the WHERE condition in the query given in question 26 is replaced with a new one (WHERE job_id IS NOT NULL)? (Assume the number of records in 'employees' table is 14).

回答：D 查询执行引发“ORA-01427：单行子查询返回多行”的异常。

*Answer: D. *The query execution raises the exception "ORA-01427: single-row subquery returns more than one row".

28.Which of the following are valid multi row operators used for sub-queries?

回答：B 多行子查询返回多行结果。可以使用多行子查询的运算符包括 IN、ALL、ANY 和 EXISTS。多行运算符 IN、ANY 和 ALL 必须与单行运算符一起使用，如选项 B 所示。

*Answer: B. *Multiple-row subqueries return more than one row of results.Operators that can be used with multiple-row subqueries include IN, ALL, ANY, and EXISTS.The multi row operators IN, ANY, ALL must be used with single row operators as shown in the option B.

Examine the table structure as given. Consider the query given below and answer the questions 29 to 33 that follow

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

SELECT first_name, last_name, salary, commission_pct
FROM employees
WHERE salary < ANY  (SELECT salary
		FROM employees
		WHERE department_id  = 100)
AND department_id  <> 101;

29.What does the ANY operator evaluates to in the above query?

回答：A 多行运算符返回布尔值结果。由于部门 100 中有薪水结果，因此返回 TRUE。如果结果为 0，则为 FALSE。

*Answer: A. *The multi row operators return Boolean results. As there are results of salary in the department 100, it returns TRUE. If there are 0 results, it evaluates to FALSE.

30.What will be the outcome of the query if we assume that the department 100 has only one employee?

回答：D 如果部门 100 有一个结果（单行子查询），则 < ANY 运算符会引发错误，因为它是一个多行运算符。

*Answer: D. *If the department 100 has one result (single row sub-query), the < ANY operator gives the error as it is a multi-row operator.

31. 如果将 < ANY 运算符替换为 = ANY 运算符，上面给出的查询结果将如何？

*31.What will be the outcome of the query given above if the < ANY operator is replaced with = ANY operator? *

答案：A。 = ANY 运算符等价于 IN 运算符。

*Answer: A. * = ANY operator is equivalent to IN operator.

32.What can be said about the < ANY operator in the query given above?

*答案：C。*多行运算符 < ANY 的计算结果为子查询的“小于最大值”语句。'> ALL' 大于子查询返回的最大值。'< ALL' 小于子查询返回的最小值。'< ANY' 小于子查询返回的最大值。'< ANY' 大于子查询返回的最小值。'= ANY' 等于子查询返回的任何值（与 IN 相同）。'[NOT] EXISTS' 行必须匹配子查询中的值

*Answer: C. *The multi row operator < ANY evaluates to the statements "Less than the maximum" of the subquery. '> ALL' More than the highest value returned by the subquery. '< ALL' Less than the lowest value returned by the subquery. '< ANY' Less than the highest value returned by the subquery. '< ANY' More than the lowest value returned by the subquery. '= ANY' Equal to any value returned by the subquery (same as IN). '[NOT] EXISTS' Row must match a value in the subquery

33.Assume that the < ANY operator is replaced with the > ANY. What is true about this operator?

*答案：C。*多行运算符 > ANY 的计算结果为子查询的“大于最小值”语句。'> ALL' 大于子查询返回的最大值。'< ALL' 小于子查询返回的最小值。'< ANY' 小于子查询返回的最大值。'> ANY' 大于子查询返回的最小值。'= ANY' 等于子查询返回的任何值（与 IN 相同）。'[NOT] EXISTS' 行必须匹配子查询中的值

*Answer: C. *The multi row operator > ANY evaluates to the statements "Greater than the minimum" of the subquery. '> ALL' More than the highest value returned by the subquery. '< ALL' Less than the lowest value returned by the subquery. '< ANY' Less than the highest value returned by the subquery. '> ANY' More than the lowest value returned by the subquery. '= ANY' Equal to any value returned by the subquery (same as IN). '[NOT] EXISTS' Row must match a value in the subquery

34. Examine the given table structure and consider the following query:

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

SELECT employee_id, first_name, last_name
FROM employees
WHERE salary IN (SELECT max(salary)
		FROM employees
		GROUP BY department_id );

以下哪个 WHERE 子句等同于上述查询中给出的 WHERE 子句？（假设工资为 2500、3000、3500、4000）

Which WHERE clause among the following is equivalent to that given in the above query? (Assume that the salaries are 2500, 3000, 3500,4000)

*答案：D。*当使用 IN 运算符时，Oracle 会将子查询的各个结果视为选项 D 中所示。

*Answer: D. *When the IN operator is used, Oracle treats individual results of the sub-query as shown in the option D.

Examine the structure of the EMPLOYEES table as given below and answer the questions 35 to 37 that follow.

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

35. You need to find out which of the employees have a salary less than that of the salary for the job ID 'FIN_ACT'. Which of the following queries will give you the required output?

答案：A。 < ALL 表示小于最小值。'> ALL' 大于子查询返回的最大值。'< ALL' 小于子查询返回的最小值。'< ANY' 小于子查询返回的最大值。'> ANY' 大于子查询返回的最小值。'= ANY' 等于子查询返回的任何值（与 IN 相同）。'[NOT] EXISTS' 行必须匹配子查询中的值

*Answer: A. * < ALL means less than the minimum. '> ALL' More than the highest value returned by the subquery. '< ALL' Less than the lowest value returned by the subquery. '< ANY' Less than the highest value returned by the subquery. '> ANY' More than the lowest value returned by the subquery. '= ANY' Equal to any value returned by the subquery (same as IN). '[NOT] EXISTS' Row must match a value in the subquery

*36. 如果将 < ALL 替换为 >ALL，上述查询（以上问题中的选项 A）的结果是什么？ *

*36.What will be the outcome of the above query (the option A in the question above), if the < ALL is replaced with the >ALL? *

答案：C。 >ALL 表示小于最小值。'> ALL' 大于子查询返回的最大值。'< ALL' 小于子查询返回的最小值。'< ANY' 小于子查询返回的最大值。'> ANY' 大于子查询返回的最小值。'= ANY' 等于子查询返回的任何值（与 IN 相同）。'[NOT] EXISTS' 行必须匹配子查询中的值

*Answer: C. * >ALL means less than the minimum. '> ALL' More than the highest value returned by the subquery. '< ALL' Less than the lowest value returned by the subquery. '< ANY' Less than the highest value returned by the subquery. '> ANY' More than the lowest value returned by the subquery. '= ANY' Equal to any value returned by the subquery (same as IN). '[NOT] EXISTS' Row must match a value in the subquery

37.You need to find the salaries for all employees who are not in the department 100. Which of the following queries will give you the required result?

答案：C。 NOT 可与多行运算符 IN、ANY 和 ALL 一起使用。

*Answer: C. * NOT can be used with the multi row operators IN, ANY and ALL.

检查给定的表结构。考虑以下查询并回答随后的 38 和 39 个问题。你需要找到没有下属向其报告的员工。（假设有 0 个预期结果）

Examine the table structure as given. Consider the following query and answer the questions 38 and 39 that follow. You need to find the employees who do not have a sub-ordinate reporting to them. (Assume there are 0 expected results)

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

SELECT first_name, last_name
FROM employees
WHERE employee_id NOT IN
		(SELECT manager_id
		FROM employees);

38.What will be the result of the query given above?

*答案：D。*内部子查询中的一个值为空（所有员工都不是经理！）

*Answer: D. *One of the values in the inner sub-query is NULL (all employees are not managers!)

39.Which of the following WHERE clauses should be added / modified to the above query to give the expected results?

*答案：B、D。*如果子查询可能有 NULL 值，不要使用 NOT IN 运算符，或者在使用时，使用一个额外的 WHERE 子句来修改子查询（选项 D）。

*Answer: B, D. *If the sub-query is likely to have NULL values, do not use the NOT IN operator or if using, modify the sub-query with an additional WHERE clause (option D)

40.What is true about sub-queries in general?

回答：C。

*Answer: C. *

41. Which of the following is true about sub-queries?

*答案：A。*子查询是在另一个查询的 SELECT、FROM、HAVING 或 WHERE 子句中嵌套的一个完整查询。子查询必须用括号括起来，并且必须至少有一个 SELECT 和一个 FROM 子句。单行子查询最多可以返回一个值。多列子查询向外部查询返回多列。

*Answer: A. *A subquery is a complete query nested in the SELECT, FROM, HAVING, or WHERE clause of another query. The subquery must be enclosed in parentheses and have a SELECT and a FROM clause, at a minimum. A single-row subquery can return a maximum of one value. Multiple-column subqueries return more than one column to the outer query.

42. Examine the table structure as given.

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

考虑以下查询。

Consider the following query.

SELECT first_name, last_name
FROM employees
WHERE employee_id NOT IN
		(SELECT manager_id, hire_date
		FROM employees
		WHERE manager_id is not null);

此查询返回一个错误。造成此错误的原因是什么？

This query returns an error. What is the reason for error?

*答案：C。*子查询中选择的列应与比较运算符的另一侧的列相同。任何数据类型或列数的不相等都将导致 ORA 错误。

*Answer: C. *The columns selected in the sub-query should be same as on the other side of comparison operator. Any inequality of data type or number of columns would result in an ORA error.

43.A report has to be extracted which displays all the departments that have one or more employees assigned to them. Which of the following queries will give the required output? (Consider the table structure as given)

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

*答案：A，D. *

*Answer: A, D. *

44.What is the maximum level of sub-queries allowed in Oracle in a single SQL statement?

*答案：D。*Oracle 支持将查询嵌套到 255 级。

*Answer: D. *Oracle supports the Nesting of queries to 255 levels.

45. What should be the best practice to follow when we know what values we need to pass on to the main query in Oracle queries?

*答案：D。*子查询可能会给出 NULL 结果，这会导致主结果集中为 0 行；因此，只有当我们知道需要什么值时，才推荐使用它们。

*Answer: D. *It might become possible that the sub-queries give a NULL result, which results in 0 rows in the main result; hence it is a good practice to use them only if we know what values we need.

检查给定的表结构。考虑以下查询并回答以下 46 和 47 问题：

*Examine the table structure as given. Consider the following query and answer the questions 46 and 47 that follow: *

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

SELECT employee_id, first_name, last_name, job_id
FROM employees
WHERE job_id = (SELECT job_id FROM employees);

46.You need to find all the employees whose job ID is the same as that of an employee with ID as 210. Which of the following WHERE clauses would you add / modify to achieve this result? (Consider the table structure as given

答案：A。

*Answer: A. *

47.Assume that you change the WHERE clause as given in the option A in question 46 as the following.

WHERE job_id = (SELECT job_id FROM employees WHERE employee_id < 210);

此更改的结果是什么？

What will be the outcome of this change?

*答案：B。*在上例中，子查询给出了多个结果，因此应使用一个多行运算符替换主查询中“=”的符号。

*Answer: B. *The sub-query gives more than one result on the given change and hence a multi row operator should replace the "=" in the main query given above.

48.Examine the table structures as shown in the exhibit below.

你需要显示薪资最高的员工的姓名。以下哪条 SQL 语句是正确的？

You need to display the names of the employees who have the highest salary. Which of the following SQL statements will be correct?

*回答：B、C。*子查询可以写在操作符的任一侧

*Answer: B, C. *The sub-queries can be written on either side of the operator

49.What is the sub-query in the FROM clause of an SQL statement? (Choose the most appropriate answer)

*回答：C。*如果子查询出现在 SELECT 语句的 FROM 子句中，则它形成一个内联视图。Oracle 会在内部为查询执行创建临时视图。

*Answer: C. *If a sub-query appears in the FROM clause of the SELECT statements,it forms an Inline view. Oracle internally creates a temporary view for the query execution.

50.What is the maximum number of nesting level allowed in an Inline View type sub-query?

*回答：D。*由于可以连接的表数没有限制，因此查询中的内联视图数没有限制。

*Answer: D. *As there is no limit on the number of tables which can be joined, there is no limit on the number of inline view in a query.

51.What is true about co-related sub-queries?*

*回答：B。*相关子查询引用外部查询中的列，并针对外部查询中的每一行执行子查询一次，而关联子查询先执行子查询，并将值传递给外部查询。

*Answer: B. *Correlated subquery references a column in the outer query and executes the subquery once for every row in the outer query while Uncorrelated subquery executes the subquery first and passes the value to the outer query.

52.Which of the following statements cannot be parent statements for a sub-query?

*回答：B。*其他选项均可以存在于子查询的主查询（父查询）中。

*Answer: B. *The rest of the options can be in the main query (parent query) of a sub-query.

53.What is true about a co-related sub-query?

*回答：C。*相关子查询引用外部查询中的列，并针对外部查询中的每一行执行子查询一次；而 EXISTS 运算符用于测试关系或链接是否存在。

*Answer: C. *Correlated subquery references a column in the outer query and executes the subquery once for every row in the outer query;and the EXISTS operator is used to test whether the relationship or link is present.

54.Examine the given table structure. You need to write a query which returns the names of the employees whose salaries exceed their respective department’s average salary. Which of the following will work? (Choose the most appropriate answer)

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

*回答：A。*在此，获取部门 ID，用于评估父查询，并且如果该行中的薪资高于该行的各部门的平均薪资，则返回该结果。

*Answer: A. *Here the department ID is obtained, used to evaluate the parent query and if the salary in that row is greater than the average salary of the departments of that row, that result is returned.

55.Examine the given table structure. Which of the following queries will display duplicate records in a table EMPLOYEES?*

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

*回答：A。*相关子查询引用外部查询中的列，并针对外部查询中的每一行执行子查询一次；而 EXISTS 运算符用于测试关系或链接是否存在。它可用于在表中查找重复行，其中重复在列或列集内部进行。

*Answer: A. *Correlated subquery references a column in the outer query and executes the subquery once for every row in the outer query;and the EXISTS operator is used to test whether the relationship or link is present. It can be used to find the duplicate rows in a table where duplicity is subjected to a column or set of columns.

检查 DEPARTMENTS 和 EMPLOYEES 表的结构，并回答以下第 56 和 57 题。

*Examine the structures for the tables DEPARTMENTS and EMPLOYEES and answer the questions 56 and 57 that follow. *

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

SQL> DESC departments
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 DEPARTMENT_ID		 NOT NULL NUMBER(4)
 DEPARTMENT_NAME	 NOT NULL VARCHAR2(30)
 MANAGER_ID			  NUMBER(6)
 LOCATION_ID			  NUMBER(4)

56.Which of the following queries will display the system date and count of records in the DEPARTMENTS and EMPLOYEES table?

*回答：D。*单行子查询也可以嵌套在外部查询的 SELECT 子句中。在这种情况下，子查询返回的值对于外部查询生成的每行输出均可用。通常，此技术用于使用子查询生成的值执行计算。

*Answer: D. *A single-row subquery can also be nested in the outer query’s SELECT clause. In this case, the value the subquery returns is available for every row of output the outer query generates. Typically, this technique is used to perform calculations with a value produced from a subquery.

57.Which of the following queries will tell whether a given employee is a manager in a Company 'XYZ'?

回答：C。

*Answer: C. *

Examine the exhibit and answer the question 58 that follows: *

58.Which of the following queries will give you maximum salary of an employee in a particular city?

*回答：C.*当在外部查询的 FROM 子句中使用多列子查询时，它将创建一个临时表，外部查询的其他子句可以引用该临时表。这个临时表更正式地称为内嵌视图。子查询的结果会像 FROM 子句中的其他表一样进行处理。如果临时表包含已分组的数据，则分组的子集在表中会被当作单独的数据行处理。

*Answer: C. *When a multiple-column subquery is used in the outer query’s FROM clause, it creates a temporary table that can be referenced by other clauses of the outer query. This temporary table is more formally called an inline view. The subquery’s results are treated like any other table in the FROM clause. If the temporary table contains grouped data, the grouped subsets are treated as separate rows of data in a table.

Examine the table structures as given below.

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

SQL> DESC departments
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 DEPARTMENT_ID		 NOT NULL NUMBER(4)
 DEPARTMENT_NAME	 NOT NULL VARCHAR2(30)
 MANAGER_ID			  NUMBER(6)
 LOCATION_ID			  NUMBER(4)

考虑以下查询并回答 59 至 62 个问题。

Consider the following query and answer the questions that 59 to 62 that follow.

SELECT  department_name
FROM departments d INNER JOIN employees e
ON (d.employee_id = e.employee_id)
GROUP BY department_name;

59.Which of the following queries can replace the above query by using sub-queries giving the same result?*

回答：A、B。

*Answer: A, B. *

60.Assume that the sub-query as shown in the query given above is modified to the following.*

(SELECT distinct (department_id ) FROM employees ORDER BY department_id );

这种修改会产生什么结果？（选择最合适的答案）

What will be the outcome as a result of this change? (Choose the most appropriate answer)

*回答：C.*子查询（FROM 子句中的除外）不能有 ORDER BY 子句。如果您需要以特定顺序显示输出，请将 ORDER BY 子句包含为外部查询的最后一个子句。

*Answer: C. *A subquery, except one in the FROM clause, can’t have an ORDER BY clause.If you need to display output in a specific order, include an ORDER BY clause as the outer query’s last clause.

61.Assume that the query given above is modified as the below one.

SELECT department_name
FROM departments
WHERE department_id  = ANY (SELECT department_id  FROM employees)
ORDER BY department_id  desc;

这种修改会产生什么结果？（选择最合适的答案）

What will be the outcome as a result of this change? (Choose the most appropriate answer)

*回答：D.*子查询（FROM 子句中的除外）不能有 ORDER BY 子句。如果您需要以特定顺序显示输出，请将 ORDER BY 子句包含为外部查询的最后一个子句。

*Answer: D. *A subquery, except one in the FROM clause, can’t have an ORDER BY clause.If you need to display output in a specific order, include an ORDER BY clause as the outer query’s last clause.

62.Which of the following can be used to order results in a sub-query?

*回答：C.*GROUP BY 子句在子查询中默认执行排序。

*Answer: C. *By default, the GROUP BY clause performs ordering in a sub-query.

Examine the exhibit below and answer the questions 63 to 65 that follow:

请考虑以下查询：

Consider the following query:

SELECT au_id, au_title
FROM audit
WHERE au_details in (SELECT au_details
		  FROM audit
		  WHERE au_title like 'S%')
		  ORDER BY au_title;

63.What will be the outcome of the query given above?

*回答：C.*CLOB、BLOB、NCLOB 或 ARRAY 中的列不能用于子查询中。

*Answer: C. *A column with a CLOB, BLOB, NCLOB or an ARRAY cannot be used in a sub-query.

64. What will be the outcome of the following query?*

SELECT *
FROM employees
WHERE salary BETWEEN (SELECT max(salary)
			FROM employees
			WHERE department_id  = 100)
AND (SELECT min(salary) FROM employees where department_id  = 100);

此查询返回错误。造成此错误的原因是什么？

This query returns an error. What is the reason for the error?

*回答：C.*BETWEEN 运算符可以在子查询中使用，但不能与子查询一起使用。

*Answer: C. *The BETWEEN operator can be used within a sub-query but not with a sub-query.

65.What is true about using NOT IN when writing queries with sub-queries in them?

*答案：C. *SQL 以不同的方式处理 NULL 值，因此如果结果集可能包含 NULL，则最好避免使用 NOT IN。

*Answer: C. *SQL handles NULL values in a different way and hence it is a good practice to avoid NOT IN if the result set might contain a NULL.

Consider the following table structures and answer the questions 66 to 72 that follow:

66. You need to find out the names and IDs of the departments in which the least salary is greater than the highest salary in the department 10. Which of the following queries will give the required result.

答案：A。

*Answer: A. *

67.Write a query to find the employees whose salary is equal to the salary of at least one employee in department of id 10. (Choose the best answer)

回答：A、B。

*Answer: A, B. *

68.You need to find out all the employees who have salary greater than at least one employee in the department 10. Which of the following queries will give you the required output?

回答：B。

*Answer: B. *

69.You need to find out all the employees who have salary lesser than the salary of all the employees in the department 10. Which of the following queries will give you the required output?

*答案：C. *多行子查询返回多行结果。与多行子查询一起使用的运算符包括 IN、ALL、ANY 和 EXISTS。多列子查询返回多列至外部查询。数据列按在子查询的 SELECT 子句中列出的顺序传递给外部查询。

*Answer: C. *Multiple-row subqueries return more than one row of results. Operators that can be used with multiple-row subqueries include IN, ALL, ANY, and EXISTS.Multiple-column subqueries return more than one column to the outer query. The columns of data are passed to the outer query in the same order in which they’re listed in the subquery’s SELECT clause.

70.You need to find out all the employees who have their manager and department matching with the employee having an Employee ID of 121 or 200. Which of the following queries will give you the required output?

*答案：A、D. *多行子查询返回多行结果。与多行子查询一起使用的运算符包括 IN、ALL、ANY 和 EXISTS。多列子查询返回多列至外部查询。数据列按在子查询的 SELECT 子句中列出的顺序传递给外部查询。

*Answer: A, D. *Multiple-row subqueries return more than one row of results. Operators that can be used with multiple-row subqueries include IN, ALL, ANY, and EXISTS. Multiple-column subqueries return more than one column to the outer query. The columns of data are passed to the outer query in the same order in which they’re listed in the subquery’s SELECT clause.

71.You need to find the department name of an employee with employee ID 200. Which of the following queries will be correct? (Choose the most appropriate answer)

回答：C。

*Answer: C. *

72.You need to find the highest earning employee with the job ID as 'SA_REP'. Which of the following queries will be correct? (Choose the most appropriate answer)

回答：B。

*Answer: B. *

请考虑展览中所示的 EMPLOYEES 表结构，并回答 73 至 77 题：

*Consider the EMPLOYEES table structure as shown in the exhibit and answer the questions 73 to 77 that follow: *

73.You need to find the job which has at least one employee in it. Which of the following queries will be correct? (Choose the most appropriate answer)

*答案：A. *EXISTS 运算符用于检查查询之间的记录并将其匹配。它返回一个 BOOLEAN 值。关联子查询引用外部查询中的一个列，并针对外部查询中的每一行执行子查询一次；且 EXISTS 运算符用于测试关系或链接是否存。非关联子查询首先执行子查询，并将值传递给外部查询。

*Answer: A. *The EXISTS operator is used to check and match records between queries. It returns a BOOLEAN value. Correlated subquery references a column in the outer query and executes the subquery once for every row in the outer query;and the EXISTS operator is used to test whether the relationship or link is present. An Uncorrelated subquery executes the subquery first and passes the value to the outer query.

74.You need to find the job which has no employees in it. Which of the following queries will be correct? (Choose the most appropriate answer)

*答案：B. *NOT EXISTS 是 EXISTS 的否定运算符。

*Answer: B. *The NOT EXISTS is the negation operator for EXISTS.

75.You need to find the 3rd maximum salary from the EMPLOYEES table. Which of the following queries will give you the required results? (Choose the most appropriate answer)

*回答：D. *

*Answer: D. *

76. You need to find the maximum salary by using the user input for getting the value of N. Which of the following queries will give you the required results? (Choose the most appropriate answer)

答案：C。 ROWUNM 是一个用于查找第 n 阶结果的伪列。

*Answer: C. *ROWNUM is a pseudo column used for finding the nth order results.

77.What will happen if a value is provided to the &N variable in the above query (option C in question 76) does not match with any row? (Choose the best answer)

*回答：D. *

*Answer: D. *

78.What is the maximum level up to which Sub-queries can be nested?

答案：A。

*Answer: A. *

79.What is true about the EXISTS operator in SQL queries with respect to sub-queries?

回答：B。

*Answer: B. *

80.What is true about the ANY operator used for sub-queries?

回答：C。

*Answer: C. *

81.What is true about the ALL operator used for sub-queries? (Choose the most appropriate answer.)

答案：C。 '> ALL' 大于子查询返回的最高值。'< ALL' 小于子查询返回的最低值。'< ANY' 小于子查询返回的最高值。'> ANY' 大于子查询返回的最低值。'= ANY' 等于子查询返回的任何值（与 IN 相同）。'[NOT] EXISTS' 行必须与子查询中的值匹配。

*Answer: C. *'> ALL' More than the highest value returned by the subquery. '< ALL' Less than the lowest value returned by the subquery. '< ANY' Less than the highest value returned by the subquery. '> ANY' More than the lowest value returned by the subquery. '= ANY' Equal to any value returned by the subquery (same as IN). '[NOT] EXISTS' Row must match a value in the subquery.

82.What is true about using sub-queries in INSERT statements in Oracle?

回答：C。

*Answer: C. *

Examine the table structures as given below and answer the questions 83 to 86 that follow.

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

SQL> DESC departments
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 DEPARTMENT_ID		 NOT NULL NUMBER(4)
 DEPARTMENT_NAME	 NOT NULL VARCHAR2(30)
 MANAGER_ID			  NUMBER(6)
 LOCATION_ID			  NUMBER(4)

83.You need to find the details of all employees who were hired for the job ID 'SA_REP' in the month of June, 2013. Which of the following queries will give the required results? (Consider the table structure as given)

回答：B。

*Answer: B. *

84. 下列哪条语句是等效的？

*84.Which of the following statements are equivalent? *

回答：A、B。

*Answer: A, B. *

85.Consider the following two queries:

SELECT first_name
FROM employees e join departments d
ON e.department_id  = d.department_id
WHERE department_name='ACCOUNTS';

SELECT first_name
FROM employees  e
WHERE department_id  = ANY (SELECT department_id 		FROM departments d
		WHERE department_name='ACCOUNTS');

关于这两条语句，可以怎么说？

What can be said about the two statements?

*答案：A，D. *

*Answer: A, D. *

86.You need to display all the employees who have the highest salary in a department 100. You fire a query as below.*

SELECT E.first_name, E.last_name , E.salary
FROM employees E
WHERE E.salary > ALL (SELECT E1.salary
      FROM employees E1
      WHERE E.department_id  =E1.department_id
      AND E.department_id  = 100);

上述查询的结果会怎样？

What will be the outcome of the above query?

答案：B、D。 >ALL 不会给出所需结果，因为可能有两个员工薪水相同，并且是部门 100 中的最高收入者。

*Answer: B, D. * >ALL will not give the required result as there may be two employees with the same salary and who are the highest earners in the department 100

Consider table structures as shown in the exhibit and answer the questions 87 to 89 that follow:

87.You need to fetch the first names (in a reverse alphabetical order) of all the employees in the department ID = 100 and who have the maximum salary in the JOB ID = 'SA_REP'. Which of the following queries will give the required output? (Choose the most appropriate output)

回答：C。

*Answer: C. *

88.In the queries given above (option C is the correct answer), you need to display all the employees with the JOB ID 'SA_REP' who have the maximum salary in the department 100. Which of the following queries will give the required output? *

答案：A。

*Answer: A. *

89.Select the query which will give you the maximum salary and maximum comm percentage. The query should also give the maximum comm percentage paid if the highest salaried employee gets the maximum comm percentage.*

90.What is true about the sub-queries used in the SELECT clause of an SQL statement?

回答：B。

*Answer: B. *

91.What will be the outcome of the following query? (Consider the table structure as given)

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

SELECT sysdate,
(SELECT max(salary) FROM employees GROUP BY department_id )
FROM DUAL;

*回答: C. 多行子查询不能用在 SQL 语句的 SELECT 子句中。只能将单行子查询嵌套在外查询的 SELECT 子句中。

*Answer: C. *A Multi row sub-query cannot be used in the SELECT clause of an SQL statement. Only a single-row subquery can be nested in the outer query’s SELECT clause.

检查给定的表结构。考虑以下查询并回答以下第 92 到 95 个问题：

*Examine the given table structure. Consider the following query and answer the questions 92 to 95 that follow: *

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

SELECT salary
FROM employees
WHERE salary > ALL (10, 20, 30);

92.Which of the following queries are equivalent to the above query?*

*回答: C. 该问题以简化的方式显示了 ALL 子句，它后面跟着一个列表。

*Answer: C. *The question shows the ALL clause in a simplified manner when it is followed by a list.

93. If in the above query the list (10,20,30) is replaced by a sub-query, which of the following queries will give the required output for the department number 100?*

*回答: B. 该问题以简化的方式显示了 ALL 子句，它后面跟着一个子查询

*Answer: B. *The question shows the ALL clause in a simplified manner when it is followed by a sub-query

94.With respect to the question 14 above, what among the following will be an equivalent query if ALL has to be replaced with ANY?

*回答: D. 使用 '⇐ ANY' 时使用的 NOT 运算符用于对子查询返回的结果进行否定

*Answer: D. *The NOT operator used while using '⇐ ANY' is used for negation of the results returned by the sub-query

95.With respect to the question 94, if the operator ANY is not to be used, which of the following queries will be correct?

*回答: B. 相关子查询引用外查询中的列，并为外查询中的每一行执行子查询一次；并且 EXISTS 运算符用于测试关系或链接是否存在。非相关子查询首先执行子查询，然后将值传递给外查询。

*Answer: B. *Correlated subquery references a column in the outer query and executes the subquery once for every row in the outer query;and the EXISTS operator is used to test whether the relationship or link is present. An Uncorrelated subquery executes the subquery first and passes the value to the outer query.

检查给定的表结构。考虑以下查询并回答以下第 96 到 98 个问题：

*Examine the given table structures. Consider the following query and answer the questions 96 to 98 that follow: *

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

SELECT salary
FROM employees
WHERE salary > ANY (10, 20, 30);

96. Which of the following queries are equivalent to the above query?

*回答: A. 该问题以简化的方式显示了 ANY 子句，它后面跟着一个列表。

*Answer: A. *The question shows the ANY clause in a simplified manner when it is followed by a list.

97. In the above query, if the list (10, 20, 30) is replaced by a sub-query, which of the following queries will give the required output for the department number 100?*

*回答: B. 该问题以简化的方式显示了 ANY 子句，它后面跟着一个子查询

*Answer: B. *The question shows the ANY clause in a simplified manner when it is followed by a sub-query

*98. 关于上述第 97 个问题，如果删除 ANY，以下哪些将是一个等效的查询？ *

*98.With respect to the question 97 above, what among the following will be an equivalent query if ANY is removed? *

*答：B。*EXISTS 运算符可以替代 ANY 运算符。关联子查询引用外部查询中的列，并对外部查询中的每一行执行一次子查询；而 EXISTS 运算符用于测试是否存在关联或链接。

*Answer: B. *The EXISTS operator can substitute the ANY operator. Correlated subquery references a column in the outer query and executes the subquery once for every row in the outer query;and the EXISTS operator is used to test whether the relationship or link is present.

99.Examine the given table structure. How many rows will get generated if the sub-query mentioned returns 0 rows?*

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

SELECT E.salary
FROM employees E
WHERE E.salary > ANY ( select E1.salary FROM employees E1 where E1.department_id  = 100);

*答：B。*如果子查询返回 0 行，“> ANY”条件求值为 FALSE，因此返回“无行”。

*Answer: B. *If the sub-query returns zero rows, the '> ANY' condition evaluates to FALSE, hence "No rows" are returned.

100. A subquery must be placed in the outer query’s HAVING clause if:

*答：B。*当需要基于某个条件限制查询的分组结果时，使用 HAVING 子句。如果必须将子查询的结果与组函数进行比较，则必须将内部查询嵌套在外层查询的 HAVING 子句中。

*Answer: B. *A HAVING clause is used when the group results of a query need to be restricted based on some condition. If a subquery’s result must be compared with a group function, you must nest the inner query in the outer query’s HAVING clause.