Sql Certificate 简明教程

SQL - Using the Set Operators Questions

1.Which SET operator does the following figure indicate?

union

*答案：A。*集合运算符用于合并两个（或更多）SELECT 语句的结果。Oracle 11g 中有效的集合运算符有 UNION、UNION ALL、INTERSECT 和 MINUS。与两个 SELECT 语句一起使用时，UNION 集合运算符返回两个查询的结果。但是，如果有任何重复项，它们将被删除，而重复记录只列出一次。要在结果中包括重复项，请使用 UNION ALL 集合运算符。INTERSECT 只列出两个查询都返回的记录；MINUS 集合运算符会从输出中删除第二个查询的结果，如果它们也在第一个查询的结果中找到。INTERSECT 和 MINUS 集合运算产生不重复的结果。

2.Which SET operator does the following figure indicate?

union all

*答案：B。*UNION ALL 返回来自两个查询的合并行，而不进行排序或删除重复项。

3.Which SET operator does the following figure indicate?

intersect

*答案：C。*INTERSECT 仅返回两个查询结果集中都出现的行，对其进行排序并删除重复内容。

4.Which SET operator does the following figure indicate?

minus

*答案：D。*MINUS 仅返回第一个结果集中未出现在第二个结果集中的行，对其进行排序并删除重复内容。

5.What is true about SET operators?

*答案：D。*集合运算符用于合并两个（或多个）SELECT 语句的结果。Oracle 11g 中有效的集合运算符有 UNION、UNION ALL、INTERSECT 和 MINUS。

6.What are the queries containing SET operators called?

*回答：D. *

7.What is true about the UNION operator?

*答案：B。*UNION 返回两个查询的合并行，对其进行排序并删除重复内容。

8.What is true about the UNION ALL operator?

*答案：C。*UNION ALL 返回两个查询的合并行，不进行排序或删除重复内容。

9.What is true about the INTERSECT operator?

*答案：C。*INTERSECT 仅返回两个查询结果集中都出现的行，对其进行排序并删除重复内容。

10.What is true about the MINUS operator?

*答案：A。*MINUS 仅返回第一个结果集中未出现在第二个结果集中的行，对其进行排序并删除重复内容。

11.What is the precedence of the set operators UNION, UNION ALL, INTERSECT and MINUS?

*答案：D。*集合运算符的优先级相等。

12.What is the order of evaluation of set operators?

*答案：A，D。*假定没有用括号对查询进行分组，则会从上到下和从左至右水平计算集合运算符。

13.In which of the following cases, parenthesis should be specified?

*答案：A。*在 INTERSECT 与其他运算符一起使用时，使用括号将明确更改计算顺序。

14.What is true about the SELECT clause when SET operators are used?

*答案：B.*在使用 SET 运算符时，所有组合都应该具有相同数量的列。组成复合查询的查询中的对应列必须属于相同的数据类型组。

15.What is true about the SET operators?

*答案：C.*在使用 SET 运算符时，所有组合都应该具有相同数量的列。组成复合查询的查询中的对应列必须属于相同的数据类型组。

16.Where can the ORDER BY clause be used in case when SET operators are used?

*答案：C.*如果在使用 SET 运算符连接的任何查询之间使用了 ORDER BY 子句，将引发 ORA 错误。

17.What is true about the queries that have SET operators in their WHERE clause?

*答案：A.*在使用 SET 运算符时，所有组合都应该具有相同数量的列。组成复合查询的查询中的对应列必须属于相同的数据类型组。

18.What is true about the columns in the second query with respect to the columns in the first query?

回答：A、C。

19.What among the following is true about SET operators?

*回答：D. *

20.What is the best way to change the precedence of SET operators given the fact that they have equal precedence?

*答案：C.*可以使用括号对特定查询进行分组，以明确更改优先级。执行期间括号优于其他 SET 运算符。

21.What can be said about duplicate values and SET operators?

*答案：C.*UNION、INTERSECT 和 MINUS 自动消除重复值。

Examine the structure of the EMPLOYEES and DEPARTMENTS tables and consider the following query and answer the questions 22 and 23.

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)
SELECT department_id
FROM employees e
UNION
SELECT department_id
FROM departments

22.What will be displayed in the result of this query?

*答案：A.*UNION 返回两个查询的组合行，对其进行排序并删除重复项。

23.What is true about the query given above?

*答案：D.*复合查询是一个利用不同表的多个查询组成的查询。

24.What is the default sorting order of the results when UNION ALL operator is used?

*回答：B。*一个联合查询默认会按升序从左到右返回跨列排序后的行。唯一的例外是 UNION ALL，不会对行进行排序。ORDER BY 子句仅允许在联合查询的最后。

25.What will be the output of the compound query in which columns in the SELECT are of CHAR and equal length?

*回答：B。*组成联合查询的查询中的列可以有不同的名称，但输出结果集将使用第一个查询中列的名称。组成联合查询的查询中的对应列必须属于相同的数据类型组。

26.What will be the output of the compound query in which columns in the SELECT are of CHAR and different lengths?

*回答：D。*虽然所选列列表不必完全是相同的数据类型，但它们必须属于相同的数据类型组。联合查询的结果集将有更高精度的列。

27.What will be the output of a compound query if either or both queries select values of VARCHAR2?

*回答：A。*虽然所选列列表不必完全是相同的数据类型，但它们必须属于相同的数据类型组。联合查询的结果集将有更高精度的列。

28.What is true if the compound queries select numeric data?

*回答：B、C。*虽然所选列列表不必完全是相同的数据类型，但它们必须属于相同的数据类型组。联合查询的结果集将有更高精度的列。

29.What will happen if the SELECT list of the compound queries returns both a VARCHAR2 and a NUMBER data type result?

*回答：C。*Oracle 不会隐式转换数据类型。

30.What is true about the UNION operator? ：

  • 答案：D. 使用 UNION 运算符时不忽略 NULL 值

31.What can be said about the names and columns of a SQL query which uses the UNION operator? ：

  • 答案：C. 组成复合查询的查询中的列可以有不同的名称，但输出结果集将使用第一个查询中的列名称。

Consider the following exhibit of the JOB_HISTORY table and the query that follows. Answer the questions 32 and 33 below the query. ：

SQL> desc job_history
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 START_DATE		 NOT NULL DATE
 END_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 DEPARTMENT_ID			  NUMBER(4)
SELECT employee_id , first_name, last_name, job_id
FROM employees E
UNION
SELECT employee_id , first_name, last_name, job_id
From job_history;

32.How many times the each employee will get displayed by the above query? ：

*答案：B。*UNION 返回两个查询的合并行，对其进行排序并删除重复内容。

33.What will be the outcome of the above query? ：

回答：B。

检查给定的表结构并考虑以下查询，并回答问题 34 到 37：

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 job_history
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 START_DATE		 NOT NULL DATE
 END_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 DEPARTMENT_ID			  NUMBER(4)
SELECT employee_id , job_id, department_id
FROM employees
UNION
SELECT employee_id , job_id, department_id
From job_history;

34.Assuming that an employee with ID 121 has held 2 job IDs in his tenure in the company. Considering the above query, how many times will his records be displayed in the results?

*回答：B . *UNION 返回两个查询的组合行，对它们进行排序并删除重复行。重复性按列组合度量，而不是单独的各列。

35.Assuming that the employee with ID 121 held two positions in two different departments - 10 and 20 in the company.He worked as 'SA_REP' in both the departments 10 and 20. What will be the outcome of the above query ?

回答：B。

36.Which statements best describes the inference drawn from the questions 34 and 35?

*回答：C . *由于岗位代码和部门的组合是唯一的，因此不会获得重复项。

37.What will be the sorting in the result set obtained by the query?

*回答：C . *默认排序将根据第一列进行升序排序，即本例中的员工 ID。但是，可以通过在尾部放置单个 ORDER BY 子句来修改此行为。

38.Which of the following operators will be used to obtain duplicate records from the component queries?

*回答：B . *UNION ALL 不会消除重复值。

39.What is the difference between the UNION and the UNION ALL operators?

*回答：B、C . *与两个 SELECT 语句一起使用时，UNION 集合运算符会返回两个查询的结果。但是，如果有任何重复项，它们将被删除，并且重复的记录只列出一次。若要在结果中包含重复项，请使用 UNION ALL 集合运算符

40.What is true about the INTERSECT operator?

*回答：A . *这是集合运算符的公共属性条件特征。

41.What can be said about the result set if the order of the intersected tables is altered when using INTERSECT?

回答：B。

42.What among the following is true about the INTERSECT operator?

回答：B。

Answer the related questions 43 and 44 given below.

43.You need to display the names and job IDs of those employees who currently have a job title that is the same as their previous one. Which of the following queries will work? (Consider the table structures 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)
SQL> desc job_history
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 START_DATE		 NOT NULL DATE
 END_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 DEPARTMENT_ID			  NUMBER(4)

回答：B。

44.Considering the above query i.e. Option B in question 43, what will be the result if the department ID is also included in the SELECT clause?

*答案：A。*结果可解释为 - 在同一部门中使用同一工作职称工作的职员。

45.What is true about the MINUS operator?

*答案：D。*减法设定运算符从输出中移除第二个查询结果（如果它们也在第一个查询结果中出现）。

46.What can be said regarding the number of columns and data types of the component queries when a MINUS operator is used?

*答案：A。*集合运算符的共同特征。

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)
SQL> desc job_history
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 START_DATE		 NOT NULL DATE
 END_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 DEPARTMENT_ID			  NUMBER(4)

回答：C。

Examine the given table structures and consider the following query answer the questions 48 and 49 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 job_history
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 START_DATE		 NOT NULL DATE
 END_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 DEPARTMENT_ID			  NUMBER(4)
SELECT employee_id , first_name, job_id
FROM employees
UNION
SELECT employee_id , NULL "first_name", job_id
FROM job_history;

  • 48. 关于以上查询，哪些说法是正确的？

*答案：C。*每个查询都必须包含相同数量的列，按位置比较。对于同一复合查询中其他查询中缺少的列，可以使用 NULL 代替。

  • 49. 考虑以上查询，如果将 UNION 运算符替换为 MINUS 运算符，结果将意味着什么？

*答案：B。*减法运算给出了第一个查询中存在而第二个查询中不存在的唯一结果。

Consider the exhibit given below and answer the questions 50 and 51 that follow:

audit yearly
audit

50.What will be the outcome of the following query?

SELECT AU_DETAILS
FROM AUDIT
UNION
SELECT AU_DETAILS
FROM AUDIT_YEARLY;

*答案：C。*使用 UNION 集合运算符时，CLOB 或 LONG 列不能处在 SELECT 子句中。

51.What will be the outcome of the query if UNION is replaced with UNION ALL?

*答案：B。*当与 LONG 或 CLOB 列一起使用时，*UNION、UNION ALL、INTERSECT 和 MINUS 运算符会抛出错误。

52.Assume that there are 4 component queries. How many SET operators can be used to combine them in a single compound query?

*答案：D。*要使用的集合运算符为 N-1，其中 N 是组件查询的数量。

53. 由于集合运算符在基于列而不是基于行使用时涉及两个或更多 SELECT，因此集合运算符被称为什么？

回答：C。

54.What is the difference between a UNION and INTERSECT operators? (Choose only the best difference)

回答：C。

检查 EMPLOYEES 表的结构并考虑以下查询。回答后面的问题 55 到 60。

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 *
FROM EMPLOYEES
where department_id = 10
SELECT *
FROM EMPLOYEES  E
where E.job_id IN (select first_name  from EMPLOYEES  E1 where E1.job_id = 'CLERK' and E.job_id  = E1.job_id )

55.You need to extract a report where the results from both the queries are displayed. Which of the following operators should be used to get the required results?

*答案：B。*UNION ALL 返回来自两个查询的合并行，而不进行排序或删除重复项。

  • 56.您需要显示所有重复值以及来自两个查询的结果集中存在的所有值。您可以在上面给出的查询中使用以下哪种 SET 运算符？*

*答案：D. *UNION ALL 将给出包含重复值的未排序结果。

57.What is the difference between the result sets when using a UNION and a UNION ALL set operators?

回答：C、D。

58.The UNION operator has more overhead on the database than the UNION ALL. What is wrong in this statement?

*答案：A、C。*UNION 必须执行比 UNION ALL 更多的任务，因为它会对结果集进行排序和重复数据删除。因此，建议除非需要不同的行，否则应使用 UNION ALL。

59.What will be the outcome if the two queries given above are combined using the INTERSECT operator?

*答案：A。*INTERSECT 返回在查询 1 和查询 2 中都存在的记录。

  • 60.INTERSECT 和 UNION 运算符之间有什么区别？*

答案：A。

  • 61.在以下哪个 SET 运算符中，更改组件查询的顺序会更改结果集？*

*答案：C。*MINUS 仅返回第一个结果集中而未出现在第二个结果集中的行，对它们进行排序并删除重复值。

Consider the following query and answer the questions 62 to 66 that follow:

SELECT 4 from dual
INTERSECT
SELECT 1 from dual;

  • 62.给定查询的结果是什么？*

*答案：A。*不会选择任何行，因为 INTERSECT 运算符不会从两个查询中获得任何公共结果 - INTERSECT 运算符给出查询 1 和查询 2 中存在的公共结果。

63.What will be the outcome of the query if the INTERSECT operator is replaced with MINUS operator?

*答案：B。*MINUS 给出的结果存在于第一个查询中但不存在于第二个查询中。

64.What will be the outcome of the above query if the INTERSECT operator is replaced with the UNION operator?

*答案： A. *UNION 将按升序在结果集中生成唯一行。

65.What will be the outcome of the above query if the INTERSECT operator is replaced with the UNION ALL operator?

*答案： A. *UNION ALL 直接显示查询中的结果，不进行排序。

66.What will be the outcome if the above query is modified as below?

SELECT 1 from dual
UNION ALL
SELECT 4 from dual;

答案：A。

Examine the JOB_HISTORY_ARCHIVE table structure. It is a backup table for the JOB_HISTORY table with no additional column. Assuming that both the table have dissimilar data, consider the query given below and answer the questions 67 to 70 that follow:

job history archive 
SQL> desc job_history
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 START_DATE		 NOT NULL DATE
 END_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 DEPARTMENT_ID			  NUMBER(4)
(SELECT * FROM job_history;
MINUS
SELECT * FROM job_history_archive)
UNION ALL
(SELECT * FROM job_history_archive
MINUS
SELECT * FROM job_history;);

67. What will be the outcome of the query given above? (Choose the best answer)

答案：A。

68.What can concluded if the above given query yields rows only from JOB_HISTORY table?

答案：A。

69.What can be said if the above query gives no results?

答案：A。

70.With respect to the query given above, if duplicate records exist in the two tables, which of the following modifications should be made to the above given query?

Answer: B. *COUNT( ）可以用来查看表之间的差异。

Consider the following query:

SELECT 1 NUM, 'employee' TEXT FROM dual
UNION
SELECT TO_CHAR(NULL) NUM, 'departments' TEXT FROM dual;

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

*答案： C. *这里数字 1 与字符串 NULL 比较，会抛出错误“ORA-01790：表达式必须具有与对应表达式相同的数据类型”。

Consider the following query and answer the questions 72 and 73 that follow:

SELECT months_between (sysdate, to_date('21-MAY-2013','DD-MON-YYYY')) FROM dual
UNION
SELECT TO_date(NULL) NUM FROM dual;

72.What will be the outcome of the query given above? (Assume that the SYSDATE is 1st July, 2013)

*答案： C. *NUMBER 和 DATE 不属于同一种数据类型。这里由 MONTHS_BETWEEN 获取的数字与 DATE 比较，因此会产生错误。

73.Assume that the SELECT statement in the 2nd query is modified as below:

SELECT to_number (NULL) NUM FROM dual;

这种更改的结果是什么？

答案：A。

74.Examine the table structures 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)
SQL> desc job_history
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 START_DATE		 NOT NULL DATE
 END_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 DEPARTMENT_ID			  NUMBER(4)
SELECT employee_id  "Employee ID"
FROM employees
UNION
SELECT employee_id  "EMP ID"
FROM job_history;

以下哪个列标题将显示在结果集中？

*答案：B. *组成复合查询的查询中的列可以有不同的名称，但输出结果集将使用第一个查询中列的名称。

检查给出的两个表结构并考虑以下查询并回答随后的问题 75 和 76：

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 job_history
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 START_DATE		 NOT NULL DATE
 END_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 DEPARTMENT_ID			  NUMBER(4)
SELECT employee_id
FROM employees e
UNION
SELECT employee_id
FROM job_history j
ORDER BY j.employee_id ;

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

*答案：D. *ORDER BY 应基于第一个查询中列的名称而不是第 2 个查询列的名称进行。

  • 76.以下哪个 ORDER BY 从句可以替换上述查询中错误的 ORDER BY？*

*答案：C. *这是一个更通用的规范，Oracle 将基于第一个查询的第一列进行排序。

77.Consider the following exhibit and answer the question below:

audit yearly
audit 
SELECT au_doc
From audit
UNION
SELECT au_doc
From audit_yearly;

以上给出的查询结果是什么？

*答案：B. *LONG 列不能与 SET 运算符一起使用。

78.Consider the query given below:

SELECT col_1
From TABLE (package1.proc1)
UNION
SELECT col_1
From TABLE (package2.proc2);

以上给出的查询结果是什么？

*答案：C. *TABLE 表达式不能与 SET 运算符一起使用。

检查给出的两个表结构并考虑以下查询。回答随后的问题 79 和 80：

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 job_history
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 START_DATE		 NOT NULL DATE
 END_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 DEPARTMENT_ID			  NUMBER(4)
SELECT employee_id , job_id
FROM employees E
UNION
SELECT employee_id , job_id
FROM job_history J
FOR UPDATE OF job_id;

79.What happens when the query is executed?

*答案：A. *FOR UPDATE 从句不能与使用 SET 运算符组合的查询一起使用。

80.What will be the outcome of the following query?

SELECT * from employees
UNION
SELECT job_id FROM job_history;;

回答：B。

81.If UNION, UNION ALL, INTERSECT are used in one SQL statement which of the following is true regarding the SQL statement?

*回答：D. *

82.Consider the query given below and answer the question that follow:

SELECT '3' FROM dual
INTERSECT
SELECT 3f FROM dual;

关于执行上面给出的查询，什么说法是真的？

*答案：B。*字符文字必须用单引号括起来。

83.Which of the following is false for set operators used in SQL queries?

*答案：A。*SET 运算符不支持 LONG、CLOB 和 BLOB 数据类型。

84.Examine the given table structure and evaluate the following SQL statement:

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 , last_name "Last Name"
FROM employees
WHERE department_id  = 100
UNION
SELECT employee_id  EMPLOYEE_NO, last_name
FROM employees
WHERE department_id  = 101;

上面查询的哪些 ORDER BY 子句有效？（选择所有适用的答案。）

*答案：A，C。*ORDER BY 子句必须通过其位置或第一个查询引用的名称来引用列。

85.Which of the following clauses would you use to exclude the column from the 2nd query out of the two queries combined using SET operators?

回答：C。

86.Examine the given table structure as given. What will be the outcome of the below 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 distinct department_id
FROM employees
WHERE salary > ANY (SELECT AVG (salary)
FROM employees
GROUP BY department_id )
UNION
SELECT *
FROM employees
WHERE salary > ANY (SELECT MAX (salary)
FROM employees
GROUP BY department_id );

*答案：B。*列数应该相同。

87.What among the following is true about the UNION operator?

*答案：C。*UNION 运算符作用于 SELECT 列表中的所有列，并且不会忽略任何列。

88.You need to display the departments where the employees with the JOB IDs 'SA_REP' or 'ACCOUNTANT' work. Which of the following queries will fetch you the required results? (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)

答案：A。

89.Which of the following statement is true about the ordering of rows in a query which uses SET operator?

答案：A、B、C。

90.The UNION operator was used to fulfill which of the following function before the ANSI SQL syntax in place?

*回答：D. *

Answer the related questions 91 and 92 given below. Consider the table structures as given here:

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 job_history
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 START_DATE		 NOT NULL DATE
 END_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 DEPARTMENT_ID			  NUMBER(4)

91.You need to find the job IDs which do not have any JOB history logged for them. Which of the following queries will work? (Consider the given table structures)

回答：B。

92.Consider the following query:

SELECT distinct  job_id
FROM employees
NATURAL JOIN job_history ;

以下哪些查询与上面的查询相同？

答案：A。

Examine the table structures given here. Consider the query given below and answer the related questions 93 to 97 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 job_history
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 START_DATE		 NOT NULL DATE
 END_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 DEPARTMENT_ID			  NUMBER(4)
SELECT job_id
FROM employees
UNION ALL
SELECT job_id
FROM job_history;;

93.If the EMPLOYEES table contains 5 records and the JOB_HISTORY contains 3 records, how many records will be obtained from the below query?

*答案：D。*UNION ALL 返回两个查询的关联行，不排序或删除重复项。

94.If the UNION ALL operator is replaced with UNION operator, how many records will be obtained? (Assume there are 6 distinct values in both the tables)

*答案：D。*UNION 返回两个查询的关联行，对其进行排序并删除重复项。

95.If the UNION ALL operator is replaced with MINUS operator, how many records will be obtained? (Assume there are 3 distinct values in EMPLOYEES and 2 in JOB_HISTORY)

*答案：C。*MINUS 仅返回第一个结果集中而未出现在第二个结果集中的行，对它们进行排序并删除重复值。

96.If the UNION ALL operator is replaced with INTERSECT operator, how many records will be obtained? (Assume there are 3 values common between the two tables)

*答案：C。*INTERSECT 仅返回两个查询结果集中都出现的行，对其进行排序并删除重复内容。

97.Consider the following query:

1.select job_id
2. from employees
3.ORDER BY department_id
4.UNION ALL
5.select job_id
6.FROM job_history;
7.ORDER BY department_id ;

以上查询会生成一个错误。以上查询中的哪一行会生成一个错误？

*答案：A。*ORDER BY 应该只出现在复合查询的最后，而不是在组件查询中。

98.Which of the following SET operator features are supported in SQL/Foundation:2003 but not by Oracle?

答案：B、C、D。

99.You need to find out the common JOB IDs (excluding duplicates) in the departments 100 and 200. Which query will you fire to get the required results? (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。

100.If a compound query contains both a MINUS and an INTERSECT operator, which will be applied first? (Choose the best answer.)

*答案：C。*所有集合运算符的优先级相等，因此优先级由它们出现的顺序决定。