Sql Certificate 简明教程

SQL - Using the Set Operators Questions

1.Which SET operator does the following figure indicate?

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

*Answer: A. *Set operators are used to combine the results of two (or more) SELECT statements.Valid set operators in Oracle 11g are UNION, UNION ALL, INTERSECT, and MINUS. When used with two SELECT statements, the UNION set operator returns the results of both queries.However,if there are any duplicates, they are removed, and the duplicated record is listed only once.To include duplicates in the results,use the UNION ALL set operator.INTERSECT lists only records that are returned by both queries; the MINUS set operator removes the second query’s results from the output if they are also found in the first query’s results. INTERSECT and MINUS set operations produce unduplicated results.

2.Which SET operator does the following figure indicate?

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

*Answer: B. *UNION ALL Returns the combined rows from two queries without sorting or removing duplicates.

3.Which SET operator does the following figure indicate?

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

*Answer: C. *INTERSECT Returns only the rows that occur in both queries' result sets, sorting them and removing duplicates.

4.Which SET operator does the following figure indicate?

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

*Answer: D. *MINUS Returns only the rows in the first result set that do not appear in the second result set, sorting them and removing duplicates.

5.What is true about SET operators?

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

*Answer: D. *Set operators are used to combine the results of two (or more) SELECT statements. Valid set operators in Oracle 11g are UNION, UNION ALL, INTERSECT, and MINUS.

6.What are the queries containing SET operators called?

*回答：D. *

*Answer: D. *

7.What is true about the UNION operator?

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

*Answer: B. *UNION Returns the combined rows from two queries, sorting them and removing duplicates.

8.What is true about the UNION ALL operator?

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

*Answer: C. *UNION ALL Returns the combined rows from two queries without sorting or removing duplicates.

9.What is true about the INTERSECT operator?

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

*Answer: C. *INTERSECT Returns only the rows that occur in both queries' result sets, sorting them and removing duplicates.

10.What is true about the MINUS operator?

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

*Answer: A. *MINUS Returns only the rows in the first result set that do not appear in the second result set, sorting them and removing duplicates.

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

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

*Answer: D. *SET operators have an equal precedence.

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

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

*Answer: A, D. *Assuming that there are no grouping of queries using parentheses, the SET operators will be evaluated from top to bottom and left to right horizontally.

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

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

*Answer: A. *Using parenthesis will explicitly change the order of evaluation when INTERSECT is used with other operators.

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

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

*Answer: B. *All the combined should have the same no. of columns when using SET operators. The corresponding columns in the queries that make up a compound query must be of the same data type group.

15.What is true about the SET operators?

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

*Answer: C. *All the combined should have the same no. of columns when using SET operators. The corresponding columns in the queries that make up a compound query must be of the same data type group.

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

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

*Answer: C. *If the ORDER BY clause is used in between any of the queries joined using SET operators, it will throw an ORA error.

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

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

*Answer: A. *All the combined should have the same no. of columns when using SET operators. The corresponding columns in the queries that make up a compound query must be of the same data type group.

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

回答：A、C。

*Answer: A, C. *

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

*回答：D. *

*Answer: D. *

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

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

*Answer: C. *Parenthesis can be used to group the specific queries in order to change the precedence explicitly. Parentheses are preferred over other SET operators during execution.

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

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

*Answer: C. *UNION, INTERSECT and MINUS automatically eliminate duplicate values

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 返回两个查询的组合行，对其进行排序并删除重复项。

*Answer: A. *UNION Returns the combined rows from two queries, sorting them and removing duplicates.

23.What is true about the query given above?

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

*Answer: D. *A compound query is one query made up of several queries using different tables.

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

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

*Answer: B. *A compound query will by default return rows sorted across all the columns,from left to right in ascending order.The only exception is UNION ALL, where the rows will not be sorted. The only place where an ORDER BY clause is permitted is at the end of the compound query.

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

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

*Answer: B. *The columns in the queries that make up a compound query can have different names, but the output result set will use the names of the columns in the first query. The corresponding columns in the queries that make up a compound query must be of the same data type group.

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

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

*Answer: D. *While the selected column lists do not have to be exactly the same data type, they must be from the same data type group. The result set of the compound query will have columns with the higher level of precision.

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

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

*Answer: A. *While the selected column lists do not have to be exactly the same data type, they must be from the same data type group. The result set of the compound query will have columns with the higher level of precision.

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

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

*Answer: B, C. *While the selected column lists do not have to be exactly the same data type, they must be from the same data type group. The result set of the compound query will have columns with the higher level of precision.

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

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

*Answer: C. *Oracle does not convert data types implicitly.

30.What is true about the UNION operator? ：

30.What is true about the UNION operator?

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

*Answer: D. *NULL values are not ignored when the UNION operator is used

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

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

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

*Answer: C. *The columns in the queries that make up a compound query can have different names, but the output result set will use the names of the columns in the first query.

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

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? ：

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

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

*Answer: B. *UNION Returns the combined rows from two queries, sorting them and removing duplicates.

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

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

回答：B。

*Answer: B. *

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

*Examine the given table structures and consider the following query and answer the questions 34 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)

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 返回两个查询的组合行，对它们进行排序并删除重复行。重复性按列组合度量，而不是单独的各列。

*Answer: B. *UNION Returns the combined rows from two queries, sorting them and removing duplicates. Duplicity is measured by the combination of columns and not the individual column separately.

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。

*Answer: B. *

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

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

*Answer: C. *As the combination of the job codes and departments is unique, there are no duplicates obtained.

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

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

*Answer: C. *The default sorting will be ascending based on the first column i.e.: Employee ID in this case.However, this behavior can be modified by placing a single ORDER BY clause at the end.

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

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

*Answer: B. *UNION ALL doesn’t eliminates the duplicate values.

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

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

*Answer: B, C. *When used with two SELECT statements, the UNION set operator returns the results of both queries. However, if there are any duplicates, they are removed, and the duplicated record is listed only once. To include duplicates in the results, use the UNION ALL set operator

40.What is true about the INTERSECT operator?

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

*Answer: A. *This is common property criteria feature of SET operators.

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

回答：B。

*Answer: B. *

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

回答：B。

*Answer: 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。

*Answer: 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。*结果可解释为 - 在同一部门中使用同一工作职称工作的职员。

*Answer: A. *The result can be interpreted as - the employees who have worked with the same job title in the same department.

45.What is true about the MINUS operator?

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

*Answer: D. *MINUS set operator removes the second query’s results from the output if they are also found in the first query’s results

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

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

*Answer: A. *Common feature of SET operators.

47. 您需要显示在公司任职期间从未更换过工作的职员的职员编号。在这种情况下，下列哪个查询正确？（请考虑给定的表结构）。

*47.You need to display the employee IDs of the employees who have not changed their jobs even once during tenure in the company. Which of the following queries will be correct in this case? (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)

回答：C。

*Answer: 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.What is true about the above query?*

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

*Answer: C. *Each query must contain the same number of columns, which are compared positionally. NULL can be substituted in place of column which is missing in the other query within the same compound query.

49.Considering the above query, if the UNION operator is replaced by the MINUS operator, what will the result mean?*

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

*Answer: B. *MINUS gives the unique results that are present in the first query but not the second query.

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

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

*Answer: C. *CLOB or LONG columns cannot be in the SELECT clause when using the UNION set operators.

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

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

*Answer: B. *.UNION, UNION ALL, INTERSECT and MINUS operators when used with a LONG or CLOB column throws error.

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 是组件查询的数量。

*Answer: D. *The SET operator to be used will are N-1 where N is the number of component queries.

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

*53.What are SET operators called owning to the fact that two or more SELECTs are involved based on columns instead of rows when SET operators are used? *

回答：C。

*Answer: C. *

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

回答：C。

*Answer: C. *

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

*Examine the structure of the EMPLOYEES table and consider the following query. Answer the questions 55 to 60 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 *
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 返回来自两个查询的合并行，而不进行排序或删除重复项。

*Answer: B. *UNION ALL Returns the combined rows from two queries without sorting or removing duplicates.

56.You need to display all the duplicate values along with all the values existing in the result set from both the queries. Which of the following SET operators you can use in the above given queries?*

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

*Answer: D. *UNION ALL will give the unsorted results with duplicates.

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

回答：C、D。

*Answer: 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。

*Answer: A, C. *UNION has to perform more tasks than UNION ALL because it sorts and deduplicates the result sets. Hence it is recommended that unless distinct rows are required, UNION ALL should be used.

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

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

*Answer: A. *INTERSECT returns those records that are present in query 1 AND query 2.

60.What among the following is the difference between the INTERSECT and the UNION operators?*

答案：A。

*Answer: A. *

61.In which of the following SET operators, changing the order of the component queries will change the result set?*

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

*Answer: C. *MINUS Returns only the rows in the first result set that do not appear in the second result set, sorting them and removing duplicates.

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

SELECT 4 from dual
INTERSECT
SELECT 1 from dual;

62.What will be the outcome of the given query?*

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

*Answer: A. *No rows will be selected as the INTERSECT operator will not get any common results from both the queries - INTERSECT operators gives common results present in query 1 AND query 2.

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

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

*Answer: B. *MINUS gives results that are present in the first query and not present in the second query.

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

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

*Answer: A. *UNION will produce distinct rows in the result set in ascending order.

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

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

*Answer: A. *UNION ALL displays the results as they are positioned in the query without sorting them.

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。

*Answer: 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:

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。

*Answer: A. *

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

答案：A。

*Answer: A. *

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

答案：A。

*Answer: 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( ）可以用来查看表之间的差异。

Answer: B. *COUNT() can be used to see the difference between the tables.

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：表达式必须具有与对应表达式相同的数据类型”。

*Answer: C. *Here the numeric 1 is compared to a character NULL which throws the error "ORA-01790: expression must have same datatype as corresponding expression".

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 比较，因此会产生错误。

*Answer: C. *NUMBER and DATE do not belong to same data type fail. Here a number obtained by MONTHS_BETWEEN is compared with a DATE and hence the error.

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

SELECT to_number (NULL) NUM FROM dual;

这种更改的结果是什么？

What will be the outcome because of this change?

答案：A。

*Answer: 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;

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

Which of the below column headings will display in the result set?

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

*Answer: B. *The columns in the queries that make up a compound query can have different names, but the output result set will use the names of the columns in the first query.

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

*Examine the two table structures given and consider the following query and answer the questions 75 and 76 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
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 个查询列的名称进行。

*Answer: D. *The ORDER BY should be done based on the names of the columns from the first query and not from the 2nd query columns.

76.Which of the following ORDER BY clauses can replace the erroneous ORDER BY in the query given above?*

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

*Answer: C. *This is a more generic specification and Oracle will order based on the first column of the first query.

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

SELECT au_doc
From audit
UNION
SELECT au_doc
From audit_yearly;

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

What will be the outcome of the above given query?

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

*Answer: B. *LONG columns cannot be used with SET operators.

78.Consider the query given below:

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

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

What will be the outcome of the query given above?

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

*Answer: C. *TABLE expressions cannot be used with SET operators.

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

*Examine the two table structures given and consider the following query. Answer the questions 79 and 80 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 , 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 运算符组合的查询一起使用。

*Answer: A. *The FOR UPDATE clause cannot be used with the query combined using the SET operators.

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

SELECT * from employees
UNION
SELECT job_id FROM job_history;;

回答：B。

*Answer: B. *

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

*回答：D. *

*Answer: D. *

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

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

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

What is true regarding the execution of the query given above?

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

*Answer: B. *Character literals must be enclosed within single quotes.

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

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

*Answer: A. *SET operators are unsupported for LONG, CLOB and BLOB data types.

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 子句有效？（选择所有适用的答案。）

Which ORDER BY clauses are valid for the above query? (Choose all that apply.)

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

*Answer: A, C. *The ORDER BY clause must reference column by its position or the name referred by the first query.

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。

*Answer: 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。*列数应该相同。

*Answer: B. *The no. of columns should be the same.

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

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

*Answer: C. *UNION operates over all the columns in the SELECT list and does not ignore any columns.

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。

*Answer: A. *

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

答案：A、B、C。

*Answer: 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: 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。

*Answer: B. *

92.Consider the following query:

SELECT distinct  job_id
FROM employees
NATURAL JOIN job_history ;

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

Which of the following queries are identical to the above query?

答案：A。

*Answer: 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 返回两个查询的关联行，不排序或删除重复项。

*Answer: D. *UNION ALL Returns the combined rows from two queries without sorting or removing duplicates.

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 返回两个查询的关联行，对其进行排序并删除重复项。

*Answer: D. *UNION Returns the combined rows from two queries, sorting them and removing duplicates.

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 仅返回第一个结果集中而未出现在第二个结果集中的行，对它们进行排序并删除重复值。

*Answer: C. *MINUS Returns only the rows in the first result set that do not appear in the second result set, sorting them and removing duplicates.

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 仅返回两个查询结果集中都出现的行，对其进行排序并删除重复内容。

*Answer: C. *INTERSECT Returns only the rows that occur in both queries' result sets, sorting them and removing duplicates.

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 ;

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

The above query generates an error. Which line in the above query generates an error?

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

*Answer: A. *ORDER BY should only appear at the end of the compound query and not in the component queries.

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

答案：B、C、D。

*Answer: 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。

*Answer: A. *

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

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

*Answer: C. *All set operators have equal precedence, so the precedence is determined by the sequence in which they occur.