1.哪个SET运算符执行下图指示？
UNION
UNION ALL
INTERSECT
MINUS
答案:A.使用集合运算符组合两个(或多个)SELECT语句的结果.Oracle 11g中的有效集合运算符是UNION，UNION ALL，INTERSECT和MINUS.当与两个SELECT语句一起使用时，UNION集合运算符将返回两个查询的结果.但是，如果存在任何重复项，则会删除它们，并且仅复制一次重复记录.要在结果中包含重复项，请使用UNION ALL set operator.INTERSECT仅列出两个查询返回的记录;如果在第一个查询的结果中也找到了第二个查询的结果，则MINUS集合运算符会从输出中删除第二个查询的结果. INTERSECT和MINUS集合操作会产生不重复的结果.
2.下面的图中哪个SET运算符表示？
UNION
UNION ALL
INTERSECT
MINUS
答案:B. UNION ALL返回两个查询的合并行，而不排序或删除重复项.
sql_certificate
3.Which SET运算符如下图所示？
UNION
UNION ALL
INTERSECT
MINUS
答案:C. INTERSECT仅返回两个查询结果集中出现的行，排序它们并删除重复项.
4.哪个SET操作符执行foll欠图显示？
UNION
UNION ALL
INTERSECT
MINUS
答案:D. MINUS仅返回第一个结果集中未出现在第二个结果集中的行结果集，排序它们并删除重复项.
5. SET运算符是什么？
他们更改行的值
他们只将两个组件查询的结果合并为一个结果
他们将10个组件查询的结果合并为两个结果集.
他们将两个或多个组件查询的结果合并为一个结果
答案:D.集合运算符用于组合两个(或更多)SELECT语句的结果. Oracle 11g中的有效集合运算符是UNION，UNION ALL，INTERSECT和MINUS.
6.包含SET运算符的查询是什么？
子查询
共同相关的子查询
GROUP BY查询
复合查询
答案:D.
7.关于UNION运算符的确是什么？
它返回合并查询中的行以及NULL值
在消除重复项后返回合并查询的行
它返回组合查询的行以及重复值
它返回组合查询的行，忽略NULL值
答案:B. UNION返回两个查询的合并行，对它们进行排序并删除重复项.
8. UNION的真实情况ALL运算符？
它返回组合查询中的行以及NULL值
在删除重复项后返回合并查询的行
它返回组合查询的行以及重复值
它返回忽略NULL值的组合查询的行
答案:C. UNION ALL从两个查询中返回合并的行而不排序或删除重复项.
9. INTERSECT运算符的确如此？
它返回合并查询中的行以及NULL值
在删除重复项后返回合并查询的行
它返回合并查询中的公共行
以上都不是
答案:C. INTERSECT仅返回两个查询结果集中出现的行，对它们进行排序并删除重复项.
10.什么是真的ab退出MINUS运算符？
它返回第一个查询中的行但不返回第二个查询中的行
它返回第二个查询的行但不是第一个查询的行
它返回组合查询的重复行
它返回组合查询的行，忽略NULL值
答案:A. MINUS仅返回行在第一个结果集中没有出现在第二个结果集中，对它们进行排序并删除重复项.
11.集合运算符UNION，UNION ALL的优先级是什么， INTERSECT和MINUS？
UNION，UNION ALL，INTERSECT和MINUS
MINUS，UNION，UNION ALL和INTERSECT
INTERSECT，MINUS，UNION ALL，UNION
等同优先
答案:D. SET运算符具有相同的优先级.
12.什么是se的评价顺序t运营商？
从左到右
对到左边
随机评估
从上到下
答案:A，D.假设没有使用括号的查询分组，将从上到下，从左到右水平评估SET运算符.
13.在下列哪种情况下，应指定括号？
当INTERSECT与其他集合运算符一起使用
当UNION与UNION ALL一起使用时
当MINUS用于查询时
以上都不是
答案:A.使用括号将在使用INTERSECT时明确更改评估顺序与其他运算符一起使用.
14.使用SET运算符时，SELECT子句有什么用？
列bei没有限制已选择
SELECT子句中使用的列，表达式必须在组合查询中与数字匹配
SELECT子句中使用的列，表达式第一个查询中必须为N，后续合并查询中必须为N-1
B和C
答案:B.所有组合应该具有相同的号码.使用SET运算符时的列数构成复合查询的查询中的相应列必须属于同一数据类型组.
15. SET运算符的确是什么？
SELECT子句应具有相同的列数，数据类型可以不同
SET运算符只能用于组合两个查询
第二个查询中每列的数据类型必须与第一个查询中相应列的数据类型匹配.
以上都不是
答案:C.所有组合应该有同样没有.使用SET运算符时的列数组成复合查询的查询中的相应列必须属于同一数据类型组.
16.如果SET运算符可以使用ORDER BY子句使用？
在每个要合并的查询中
仅在第一个查询中
在复合查询的最后
以上都不是
答案:C.如果在使用SET运算符连接的任何查询之间使用ORDER BY子句，则会抛出ORA错误.
17.在WHERE子句中有SET运算符的查询是什么？
这些查询必须具有相同的编号.和SELECT子句中列的数据类型.
没有. WHERE子句查询中使用的列和主SELECT可以不同
否. WHERE子句中使用的列应该相同，数据类型可以不同
以上都不是
答案:A.所有组合应该具有相同的号码.使用SET运算符时的列数构成复合查询的查询中的相应列必须属于同一数据类型组.
18.关于第二个查询中的列的确如此第一个查询中的列？
第二个查询中的列必须相同数据类型组作为第一个查询中的相应列
如果第一个查询中的列是NUMBER，则第二个查询中的相应列应为VARCHAR2
如果第一个查询中的列是NUMBER，则第二个查询中的相应列也应为NUMBER.
以上都不是
答案:A，C.
19.关于SET运算符，以下哪些是真的？
SET运算符不能用于子查询
SET运算符只能在WHERE子句中使用
ORDER BY可以是用于由SET运算符组合的所有查询
SET运算符可用于子查询
答案:D.
20.鉴于SET运算符具有相同的优先级，改变SET运算符优先级的最佳方法是什么？
可以更改SET运算符的使用顺序以更改优先级
无法更改相同的优先级
括号可用于更改优先级
以上都不是
答案:C.括号可用于对o中的特定查询进行分组rder明确地改变优先权.在执行期间，括号优于其他SET运算符.
21.关于重复值和SET运算符可以说什么？
否SET运算符显示重复值
所有SET运算符都可以显示重复值
只有UNION ALL运算符显示重复值
以上所有
答案:C. UNION，INTERSECT和MINUS自动消除重复值
检查EMPLOYEES和DEPARTMENTS表的结构并考虑以下查询并回答问题22和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.此查询结果中会显示什么？
它将显示在EMPLOYEES和DEPARTMENTS表中共同包含的不同部门ID
它将抛出ORA错误
未选择任何行
以上都不是
答案:A. UNION返回合并的行来自两个查询，对它们进行排序并删除重复项.
23.上面给出的查询是什么情况？
此查询返回ORA错误
执行成功但是没有给出结果
来自不同表的查询不能与SET运算符一起使用
查询成功执行并按预期提供结果
答案:D.复合查询是由使用不同表的多个查询组成的一个查询.
24.当使用UNION ALL运算符时，结果的默认排序顺序是什么？
降序
升序
A或B
以上所有
答案:B.复合查询默认返回按所有列排序的行，从左到右依次递增唯一的例外是UNION ALL，其中的行不会被排序.唯一允许ORDER BY子句的地方是在复合查询的末尾.
25.复合查询的输出是什么，其中SELECT中的列是CHAR和等长？
输出将具有相等长度的VARCHAR2数据类型
输出将具有相等长度的CHAR数据类型
输出将具有不同长度的CHAR数据类型
输出将具有相等长度的NUMBER数据类型
答案:B.组成的查询中的列复合查询可以具有不同的名称，但输出结果集将使用第一个查询中的列的名称.组成复合查询的查询中的相应列必须属于同一数据类型组.
26.复合查询的输出是什么，其中列为SELECT是CHAR和不同的长度？
输出将具有相等的VARCHAR2数据类型长度
输出将具有相等长度的CHAR数据类型
输出将具有不同长度的CHAR数据类型
输出将具有VARCHAR2数据类型，其长度为较大的CHAR值
答案:D.虽然所选列列表不必是完全相同的数据类型，它们必须来自同一数据类型组.复合查询的结果集将包含具有更高精度级别的列.
27.如果其中一个或两个查询选择了值，那么复合查询的输出是什么VARCHAR2？
输出将具有VARCHAR2数据类型.
输出将具有相等长度的CHAR数据类型
输出将具有不同长度的CHAR数据类型
输出将具有VARCHAR2数据类型，其长度为较大的CHAR值
答案:A.选定的列列表不必是完全相同的数据类型，它们必须来自同一数据类型组.复合查询的结果集将包含具有更高精度级别的列.
28.如果复合查询选择数字数据，该怎么办？
数值的优先级相同，运算符
返回值将由数字优先级确定
返回值为NUMBER数据类型
以上都不是
答案:B，C.虽然所选列列表不必是完全相同的数据类型，但它们必须来自相同的数据类型组.复合查询的结果集将包含具有更高精度级别的列.
29.如果复合查询的SELECT列表同时返回VARCHAR2和a，将会发生什么NUMBER数据类型结果？
Oracle将隐式转换它们并返回VARCHAR2数据类型结果
Oracle将隐式转换它们并返回NUMBER数据类型结果
抛出ORA错误
无以上
答案:C. Oracle不会隐式转换数据类型.
30.对于UNION运算符有什么用？
它消除了忽略NULL值的重复值
它返回忽略NULL值的重复值
它返回包含NULL值的重复值
它消除重复值并且不忽略NULL值
答案:D.使用UNION运算符时不会忽略NULL值
31.可以说使用SQL查询的名称和列的名称和列UNION运算符？
列的名称应相同
列的名称和数据类型应相同
列的名称不必相同
以上都不是
答案:C.构成复合查询的查询中的列可以有不同的名称，但输出结果set将使用第一个查询中列的名称.
请考虑以下JOB_HISTORY表和后面的查询.回答查询下面的问题32和33.
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.上述查询会显示每位员工多少次？
0
1
2
4
答案:B. UNION返回两个查询的合并行，排序它们并删除重复项.
33.上述查询的结果是什么？
它显示员工当前和以前的工作详情两次
它显示当前的d员工以前的工作详情只有一次
A或B
以上都不是
答案: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.假设身份证121的员工在公司任职期间持有2个工作ID.考虑到上述问题，他的记录会在结果中显示多少次？
一次
两次
三次
以上都不是
答案:B. UNION返回两个查询的组合行，对它们进行排序并删除重复项.双重性是通过列的组合而不是单独的列来衡量的.
35.假设ID为121的员工在两个不同的部门中担任两个职位 - 10个和20个他在第10和第20部门都担任'SA_REP'.上述查询的结果是什么？
2行
3行
无行
ORA错误
答案:B.
36.这些陈述最能说明从问题34和35中得出的推论？
作业代码有重复值
查询执行但生成的结果是意外的
部门没有重复值
没有上面
答案:C.由于工作代码和部门的组合是唯一的，因此没有重复.的
37.查询获得的结果集中的排序是什么？
降低员工ID
降低工作ID
升级员工ID
升级部门ID
答案:C.默认排序将根据第一列即:在这种情况下的员工ID.但是，可以通过在末尾放置一个ORDER BY子句来修改此行为.
38.以下哪些运算符将用于从组件查询中获取重复记录？
UNION
UNION ALL
MINUS
以上都不是
答案:B. UNION ALL不会消除重复值.
39. UNION和UNION有什么区别所有运营商？
有n o差异
UNION ALL也显示重复值
UNION ALL的输出默认情况下没有排序
以上都不是
答案:B，C.当与两个SELECT语句一起使用时，UNION设置operator返回两个查询的结果.但是，如果存在任何重复项，则会将其删除，并且重复的记录仅列出一次.要在结果中包含重复项，请使用UNION ALL集合运算符
40. INTERSECT运算符的确是什么？
组件查询中列的列数和数据类型应该相同
名称组件查询中列的列和数据类型应该相同
A和B
以上都不是
答案:A.这是SET运营商的常见财产标准功能.
41.如果在使用INTERSECT时更改了相交表的顺序，结果集可以说什么？
结果已更改
结果保持不变
更改时的排序更改
以上都不是
答案:B.
42.关于INTERSECT运营商，以下哪些是正确的？
它忽略NULL值
它没有忽略NULL值
它返回第一个组件查询中的所有行
以上都不是
答案:B.
回答下面给出的相关问题43和44.
43.您需要显示当前职位名称与之前职位相同的员工的姓名和职位ID.以下哪个查询有效？ (考虑给定的表结构)
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，first_name，last_name FROM employees UNION SELECT employee_id，job_id，first_name，last_name FROM job_history;
SELECT employee_id，job_id，first_name，last_name FROM employees INTERSECT SELECT employee_id，job_id，first_name，last_name FROM job_history;
SELECT employee_id，job_id，first_name，last_name FROM employees UNION ALL SELECT employee_id，job_id，first_name， last_name FROM job_history;
以上都不是
Answe r:B.
44.考虑到上述问题，即问题43中的选项B，如果部门ID也包含在SELECT中，结果会是什么条款？
结果将是相同的
结果会有所不同
结果会相同，但订单会有所不同
以上都不是
答案:A.结果可以解释为 - 在同一部门工作过相同职位的员工.
45.关于MINUS运算符的确是什么？
它返回所有组件查询中的所有行
它只返回所有组件查询中的公共行
它返回所有行来自第一个查询而不是后续查询
它返回第一个查询选择的所有不同行，但不会出现在后续查询中ries
答案:D. MINUS集合运算符如果在第一个查询中也找到它们，则从输出中删除第二个查询的结果查询的结果
46.当使用MINUS运算符时，关于组件查询的列数和数据类型可以说什么？
它们应该相同，数据类型可能不同但它们应属于同一数据类型组.
它们应与列名相同
A和B
无以上
答案:A. SET运营商的共同特征.
47.您需要在公司任职期间显示未改变工作的员工的员工ID.在这种情况下，以下哪些查询是正确的？ (考虑给定的表结构)
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 UNION SELECT employee_id FROM job_history;
SELECT employee_id FROM employees INTERSECT 选择employee_id FROM job_history;
SELECT employee_id FROM employees 减 选择employee_id FROM job_history;
SELECT employee_id FROM employees UNION ALL SELECT employee_id FROM job_history;
答案:C.
检查给定的表结构a并考虑以下查询回答以下问题48和49:
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?
It throws an error as TO_CHAR (NULL) cannot be used
It executes successfully and gives the values for employees’ id, first_name and current job role including duplicate rows
It executes successfully and gives the values for employees’ id, first_name and all jobs held by the employees excluding duplicate rows
None of the above
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?
The result shows those employees who have an entry in the JOB_HISTORY table
The result shows those employees who do not have an entry in the JOB_HISTORY, but they are present in the EMPLOYEES table
Either of A or B
None of the above
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;
It executes successfully giving the correct results including the duplicate values
It executes successfully giving the correct results excluding the duplicate values
It throws an ORA error
None of the above
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?
It will execute successfully giving the correct results including duplicate values
It throws an ORA error
It will execute successfully giving the correct results excluding duplicate values
$b$ b
It executes successfully but gives the incorrect results.
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?
1
2
4
3
Answer: D. The SET operator to be used will are N-1 where N is the number of component queries.
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?
Horizontal joins
Cartesian Joins
Vertical joins
Outer joins
Answer: C.
54.What is the difference between a UNION and INTERSECT operators? (Choose only the best difference)
UNION combines the results of two component queries into one result set with duplicate values
INTERSECT returns only those rows that are returned by each of the two component queries
UNION gives the distinct values from the component queries, INTERSECT gives the common values from the component queries
Both B and C
Answer: C.
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)
Query 1
SELECT * FROM EMPLOYEES where department_id = 10
Query 2
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?
UNION
UNION ALL
INTERSECT
None of the above
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?
INTERSECT
UNION
MINUS
None of the above
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?
Result set from UNION ALL is filtered including duplicate values
Result set from UNION is filtered and sorted including duplicate values
Result set from UNION ALL is not sorted and it has duplicate values
Result set from UNION is filtered and sorted without duplicate values
Answer: C, D.
58.The UNION operator has more overhead on the database than the UNION ALL. What is wrong in this statement?
The statement is correct
UNION ALL operator has more overhead on the Data base than the UNION operator
UNION has to sort and eliminate duplicates which results into additional overhead
None of the above
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?
It will display only those employees who are Clerks in the Department 10
It will display all those employees who are in the department 10
It will display all the Clerks.
None of the above
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?
INTERSECT follows the ’AND’ Boolean logic, UNION follows the ’OR’ Boolean logic
UNION follows the ’OR’ Boolean logic, whereas INTERSECT follows the ’AND’ logic
Either of A or B
None of the above
Answer: A.
61.In which of the following SET operators, changing the order of the component queries will change the result set?
UNION
UNION ALL
MINUS
INTERSECT
Answer: C. MINUS Returns only the rows in the first result set that do not appear in the second resu lt 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?
No rows
4
1
NULL
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?
3
4
0
1
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?
1
4
4
1
NULL
0
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?
4
1
0
NULL
1
4
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;
1
4
4
1
NULL
None of the above
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)
It will return those rows that are different in the two tables
It will return the common rows in the two tables
It will return all the rows from the two tables
None of the above
Answer: A.
68.What can concluded if the above given query yields rows only from JOB_HISTORY table?
It shows that the JOB_HISTORY contains two rows different from JOB_HISTORY_ARCHIVE table
It shows that two rows are same in JOB_HISTORY and JOB_HISTORY_ARCHIVE tables
It shows that the JOB_HISTORY_ARCHIVE contains two rows different from JOB_HISTORY table
None of the above
Answer: A.
69.What can be said if the above query gives no results?
It shows that the two t ables have same data
It shows that the component queries are wrongly placed
It shows that the SET operators are wrongly used in the compound query
None of the above
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?
COUNT(*)
COUNT(*) and GROUP BY employee_id
COUNT (*) and ORDER BY employee_id
None of the above
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 q uery given above?
NUM TEXT ---------- ----------- 1 employee departments
NUM TEXT ---------- ----------- 1 employee NULL departments
ORA error
NUM TEXT ---------- ----------- departments 1 employee
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)
It executes successfully with correct results
It executes successfully but with no results
It throws an ORA error
None of the above
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?
It executes successfully with correct results
It executes successfully but with no results
It throws an ORA error
None of the above
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?
EMP ID
Employee ID
EMPLOYEE_ID
ORA error because the column names must be same in the component queries.
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.
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?
The results will be ordered by the employee ID from the JOB_HISTORY table
The results will be ordered by the employee ID from the EMPLOYEES table
There will be no ordering of the results
ORA error
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 clause s can replace the erroneous ORDER BY in the query given above?
ORDER BY e.employee_id
ORDER BY j.2
ORDER BY 1
None of the above, ORDER BY is not allowed in the query
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?
It gives the Audit documents between the two tables
It gives an ORA erro r on execution
It gives the Audit documents from the table AUDIT
None of the above
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?
It executes successfully with duplicates
It executes successfully without duplicates
It throws an ORA error
None of the above
Answer: C. TABLE expressions cannot be used with SET operators.
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?
ORA error
Employee_id and job_id
Employee_id
None of the above
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;;
It will give all the columns from the employees tables and only the job_id column from the job_history table
It will throw an error as the number of columns should match in the component queries
Neither B or C
None of the above
Answer: B.
81.If UNION, UNION ALL, INTERSECT are used in one SQL statement which of the following is true regarding the SQL statement?
UNION, UNION ALL will be executed first and then the result set will go for the INTERSECT statement.
The execution of INTERSECT will precede the UNION and UNION ALL execution.
The execution will be done from right to left taking into consideration all the operators at the same time.
The execution will be done from left to right taking into consideration all the operators at the same time.
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?
It executes successfully.
It throws an error
It gives the result 3.
It gives the result 3f
Answer: B. Character literals must be enclosed within single quotes.
83.Which of the following is false for set operators used in SQL queries?
The set operators are valid when used on columns with the LONG datatype.
The set operators are not valid on columns of type BLOB, CLOB, BFILE, VARRAY, or nested table.
In order for the select query containing an expression, a column alias should be provided in order to refer it to the order_by_clause.
You cannot use these operators in SELECT statements containing TABLE collection expressions.
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;
Which ORDER BY clauses are valid for the above query? (Choose all that apply.)
ORDER BY 2,1
ORDER BY EMPLOYEE_NO
ORDER BY 2, employee_id
ORDER BY "EMPLOYEE_NO"
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?
GROUP BY
ORDER BY
MINUS
UNION
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 );
It will display all the department IDs which have the average salaries and the maximum salaries
It will throw an ORA error as the no. of columns selected in both the query is different
It will display all the department IDs which have the average salaries
It will display all the department IDs which have the maximum salaries
Answer: B. The no. of columns should be the same.
87.What among the following is true about the UNION operator?
UNION operates over only the first column in the SELECT list
UNION operates over the first columns of the SELECT lists in the component queries
UNION operates over all the columns being selected.
None of the above
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)
SELECT department_id FROM employees E Where job_id = ’SA_RE’ UNION SELECT department_id FROM employees E Where job_id = ’ACCOUNTANT’;
SELECT department_id FROM employees E Where job_id = ’SA_REP’ UNION ALL Select department_id FROM employees E Where job_id = ’ACCOUNTANT’;
SELECT department_id FROM employees E Where job_id = ’SA_REP’ INTERSECT Select department_id FROM employees E Where job_id = ’ACCOUNTANT’;
SELECT department_id FROM employees E Where job_id = ’SA_REP’ MINUS Select department_id FROM employees E Where job_id = ’ACCOUNTANT’;
Answer: A.
89.Which of the following statement is true about the ordering of rows in a query which uses SET operator?
It is not possible to use ORDER BY in the individual queries that make a compound query.
An ORDER BY clause can be appended to the end of a compound query.
The rows returned by a UNION ALL will be in the order they occur in the two source queries.
The rows returned by a UNION will be sorted across all their columns, right to left.
Answer: A, B, C.
90.The UNION operator was used to fulfill which of the following function before the ANSI SQL syntax in place?
RIGHT OUTER JOIN
LEFT OUTER JOIN
EQUI-JOIN
FULL OUTER JOIN
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)
SELECT job_id FROM employees UNION ALL SELECT job_id FROM job_history;;
SELECT job_id FROM employees MINUS Select job_id FROM job_history;;
SELECT job_id FROM employees UNION SELECT job_id FROM job_history;;
None of the above
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?
SELECT job_id FROM employees UNION SELECT jo b_id FROM job_history;;
SELECT job_id FROM employees UNION ALL SELECT job_id FROM job_history;;
SELECT job_id FROM employees MINUS Select job_id FROM job_history;;
SELECT job_id FROM employees INTERSECT SELECT job_id FROM job_history;;
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?
4
3
0
8
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)
5
3
2
6
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)
3
2
1
0
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)
8
6
3
2
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?
3
7
2
No error is obtained
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?
UNION ALL
MINUS ALL
INTERSECT ALL
EXCEPT ALL
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)
SELECT job_id from employee WHERE department_id = 100 INTERSECT SELECT job_id from employee WHERE department_id = 200;
SELECT job_id from employee WHERE department_id = 100 UNION ALL SELECT job_id from employee WHERE department_id = 200;
SELECT job_id from employee WHERE department_id = 100 MI NUS Select job_id from employee WHERE department_id = 200;
SELECT job_id from employee WHERE department_id = 100 INTERSECT ALL Select job_id from employee WHERE department_id = 200;
Answer: A.
100.If a compound query contains both a MINUS and an INTERSECT operator, which will be applied first? (Choose the best answer.)
The INTERSECT, because INTERSECT has higher precedence than MINUS.
The MINUS, because MINUS has a higher precedence than INTERSECT.
The precedence is determined by the order in which they are specified.
It is not possible for a compound query to include both MINUS and INTERSECT.
Answer: C. All set operators have equal precedence, so the precedence is determined by the sequence in which they occur.
