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 子句。单行子查询和多行子查询是子查询的主要类型

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

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

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

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

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

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

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

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

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

回答：C。

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

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

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

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

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

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

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

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

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 的薪水作为结果提供给外部查询。

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

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

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。*多行运算符不能用于单行子查询，反之亦然。

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

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

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

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

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

回答：C。

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

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

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

employees
departments

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” 可能会失败。

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 语句中编写多个子查询，以添加多个条件。

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

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

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

  1. 内部查询只能从一张表获取数据

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

以上哪个说法是正确的？

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

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. 以下查询的结果是什么？（选择最合适的答案）

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 可以在子查询中使用组函数。

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 子句

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 子句对组进行限制。

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

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

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

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。

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：单行子查询返回多行”的异常。

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

  • 回答：B 多行子查询返回多行结果。可以使用多行子查询的运算符包括 IN、ALL、ANY 和 EXISTS。多行运算符 IN、ANY 和 ALL 必须与单行运算符一起使用，如选项 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。

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

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

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

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

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

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

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' 行必须匹配子查询中的值

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）

*答案：D。*当使用 IN 运算符时，Oracle 会将子查询的各个结果视为选项 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' 行必须匹配子查询中的值

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

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

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 一起使用。

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

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。*内部子查询中的一个值为空（所有员工都不是经理！）

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）。

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

回答：C。

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

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

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)

考虑以下查询。

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);

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

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

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. *

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

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

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 行；因此，只有当我们知道需要什么值时，才推荐使用它们。

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

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。

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);

此更改的结果是什么？

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

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

employees
grade

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

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

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

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

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

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

  • 51. 关于相关子查询，哪条说法是正确的？

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

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

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

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

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

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，用于评估父查询，并且如果该行中的薪资高于该行的各部门的平均薪资，则返回该结果。

  • 55. 请检查给定的表结构。以下哪个查询将显示 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 运算符用于测试关系或链接是否存在。它可用于在表中查找重复行，其中重复在列或列集内部进行。

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

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 子句中。在这种情况下，子查询返回的值对于外部查询生成的每行输出均可用。通常，此技术用于使用子查询生成的值执行计算。

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

回答：C。

  • 请检查示例并回答 58 个问题：*

employees
departments
locations

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

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

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 个问题。

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

  • 59. 以下哪些查询可以通过使用子查询替换上述查询以得到相同的结果？*

回答：A、B。

  • 60. 假设上述查询中显示的子查询已修改为以下查询。*

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

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

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

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;

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

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

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

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

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

audit

请考虑以下查询：

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 中的列不能用于子查询中。

  • [style="arabic"]1. 以下查询的结果是什么？*

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);

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

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

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

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

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

employees
departments

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。

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。

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。

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 子句中列出的顺序传递给外部查询。

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 子句中列出的顺序传递给外部查询。

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。

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。

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

employees

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 运算符用于测试关系或链接是否存。非关联子查询首先执行子查询，并将值传递给外部查询。

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 的否定运算符。

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. *

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 阶结果的伪列。

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. *

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

答案：A。

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

回答：B。

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

回答：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' 行必须与子查询中的值匹配。

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

回答：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。

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

回答：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');

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

*答案：A，D. *

  • 86. 需要显示在部门 100 中薪水最高的员工。可以像下面这样触发查询。*

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);

上述查询的结果会怎样？

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

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

employees
departments

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。

  • 88. 在上述查询中（选项 C 为正确答案），需要显示 JOB ID 为 'SA_REP' 且薪水在部门 100 最高的所有员工。下列哪条查询会给出所需输出？*

答案：A。

  • 89.选择将为您提供最高薪酬和最高佣金百分比的查询。如果最高薪酬的员工获得最高佣金百分比，该查询还应给出支付的最高佣金百分比。

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

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

回答：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 子句中。

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

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.以下哪些查询等同于上述查询？*

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

  • [style="arabic"]1. 如果在上述查询中列表 (10,20,30) 被子查询替换，那么以下哪些查询将为部门编号 100 提供所需的输出？*

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

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 运算符用于对子查询返回的结果进行否定

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 运算符用于测试关系或链接是否存在。非相关子查询首先执行子查询，然后将值传递给外查询。

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

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 子句，它后面跟着一个列表。

  • [style="arabic"]1. 在上述查询中，如果列表 (10, 20, 30) 被子查询替换，那么以下哪些查询将为部门编号 100 提供所需的输出？*

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

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

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

  • 99. 检查给定的表结构。如果提到的子查询返回 0 行，将生成多少行？

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，因此返回“无行”。

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

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