SQL Certificate Study Material

SQL Certificate Questions Bank

SQL Certificate Mock Exams

SQL Certificate - Mock Exams

SQL Certificate Useful Resources

Selected Reading

SQL - Using the Set Operators

Using the Set Operators Questions

1.Which SET operator does the following figure indicate?

UNION

UNION ALL

INTERSECT

MINUS

Answer: A. Set operators are used to combine the results of two (or more) SELECT statements.Vapd 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 duppcates, they are removed, and the duppcated record is psted only once.To include duppcates in the results,use the UNION ALL set operator.INTERSECT psts 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 unduppcated results.

2.Which SET operator does the following figure indicate?

UNION

UNION ALL

INTERSECT

MINUS

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

sql_certificate

3.Which SET operator does the following figure indicate?

UNION

UNION ALL

INTERSECT

MINUS

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

4.Which SET operator does the following figure indicate?

UNION

UNION ALL

INTERSECT

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

5.What is true about SET operators?

They change values of rows

They combine the results of only two component queries into one result

They combine the results of 10 component queries into two result sets.

They combine the results of two or more component queries into one result

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

6.What are the queries containing SET operators called?

Sub-queries

Co-related sub-queries

GROUP BY queries

Compound queries

Answer: D.

7.What is true about the UNION operator?

It returns rows from the combined queries along with NULL values

It returns rows for the combined queries after epminating duppcates

It returns rows for the combined queries along with duppcate values

It returns rows for the combined queries ignoring the NULL values

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

8.What is true about the UNION ALL operator?

It returns rows from the combined queries along with NULL values

It returns rows for the combined queries after epminating duppcates

It returns rows for the combined queries along with duppcate values

It returns rows for the combined queries ignoring the NULL values

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

9.What is true about the INTERSECT operator?

It returns rows from the combined queries along with NULL values

It returns rows for the combined queries after epminating duppcates

It returns the common rows from the combined queries

None of the above

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

10.What is true about the MINUS operator?

It returns rows from the first query but not from the second query

It returns rows for the second query but not from the first query

It returns duppcate rows for the combined queries

It returns rows for the combined queries ignoring the NULL values

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

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

UNION, UNION ALL, INTERSECT and MINUS

MINUS, UNION, UNION ALL and INTERSECT

INTERSECT, MINUS, UNION ALL, UNION

Equal precedence

Answer: D. SET operators have an equal precedence.

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

Left to Right

Right to Left

Random Evaluation

Top to Bottom

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?

When INTERSECT is used with other set operators

When UNION is used with UNION ALL

When MINUS is used for the queries

None of the above

Answer: A. Using parenthesis will exppcitly 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?

There is no restriction on the columns being selected

The columns, expressions used in the SELECT clause must match in number in the combined queries

The columns, expressions used in the SELECT clause must be N in the first query and N-1 in the subsequent combined queries

Both B and C

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?

The SELECT clause should have the same number of columns, data types can be different

The SET operators can be used only for combining two queries

The data type of each column in the 2nd query must match the data type of its corresponding column in the first query.

None of the above

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?

In each of the queries being combined

In the first query only

At the very end of the compound query

None of the above

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?

These queries must have the same no. and data type of columns in their SELECT clause.

The no. of columns used in the WHERE clause query and the main SELECT can be different

The no. of columns used in the WHERE clause should be the same, the data type can be different

None of the above

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?

The column in the 2nd query must be in the same data type group as the corresponding column in the 1st query

If a column in the 1st query is a NUMBER, the corresponding column in the 2nd query should be a VARCHAR2

If a column in the 1st query is a NUMBER, the corresponding column in the 2nd query should be also be NUMBER.

None of the above

Answer: A, C.

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

SET operators cannot be used in sub-queries

SET operators can only be used in the WHERE clause

ORDER BY can be used for all queries combined by a SET operator

SET operators can be used in sub-queries

Answer: D.

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

The order of usage of the SET operators can be changed to change the precedence

The equal precedence cannot be changed

Parenthesis can be used to change the precedence

None of the above

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

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

No SET operator displays duppcate values

All SET operators can display duppcate values

Only UNION ALL operator displays duppcate values

None of the above

Answer: C. UNION, INTERSECT and MINUS automatically epminate duppcate 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?

It will display distinct department id(s) contained jointly in EMPLOYEES and DEPARTMENTS table

It will throw ORA error

No rows selected

None of the above

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

23.What is true about the query given above?

This query returns an ORA error

It executes successfully but gives no results

Queries from different tables cannot be used with the SET operators

The query executes successfully and gives the results as expected

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?

Descending

Ascending

Either A or B

All of the above

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?

The output will have VARCHAR2 data type of equal length

The output will have CHAR data type of equal length

The output will have CHAR data type of different lengths

The output will have NUMBER data type of equal length

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?

The output will have VARCHAR2 data type of equal length

The output will have CHAR data type of equal length

The output will have CHAR data type of different lengths

The output will have VARCHAR2 data type with the length of the larger CHAR value

Answer: D. While the selected column psts 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?

The output will have VARCHAR2 data type.

The output will have CHAR data type of equal length

The output will have CHAR data type of different lengths

The output will have VARCHAR2 data type with the length of the larger CHAR value

Answer: A. While the selected column psts 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?

There will be an equal precedence of the numeric values, operators

The return values will be determined by the numeric precedence

The return values will be of NUMBER data type

None of the above

Answer: B, C. While the selected column psts 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 pst of the compound queries returns both a VARCHAR2 and a NUMBER data type result?

Oracle will convert them imppcitly and return a VARCHAR2 data type result

Oracle will convert them imppcitly and return a NUMBER data type result

An ORA error is thrown

None of the above

Answer: C. Oracle does not convert data types imppcitly.

30.What is true about the UNION operator?

It epminates the duppcate values ignoring NULL values

It returns duppcate values ignoring NULL values

It returns duppcate values including NULL values

It epminates duppcate values and does not ignore NULL values

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?

The names of the columns should be identical

The names and data type of the columns should be identical

The names of the columns need not be identical

None of the above

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.

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?

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

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

It displays the current and previous job details of the employees twice

It displays the current and previous job details of the employees only once

Either A or B

None of the above

Answer: B.

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?

Once

Twice

Thrice

None of the above

Answer: B. UNION Returns the combined rows from two queries, sorting them and removing duppcates. Duppcity is measured by the combination of columns and not the inspanidual 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 ?

2 rows

3 rows

No rows

ORA error

Answer: B.

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

There are duppcate values for job codes

The query executes but results produced are unexpected

There are no duppcate values for departments

None of the above

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

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

Descending on Employee ID

Descending on Job ID

Ascending on Employee ID

Ascending on Department ID

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 duppcate records from the component queries?

UNION

UNION ALL

MINUS

None of the above

Answer: B. UNION ALL doesn t epminates the duppcate values.

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

There is no difference

UNION ALL displays duppcate values too

The output in the case of UNION ALL is not sorted by default

None of the above

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

40.What is true about the INTERSECT operator?

The number of columns and data types of the columns in the component queries should be the same

The names of the columns and data types of the columns in the component queries should be the same

Both A and B

None of the above

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?

The result is altered

The result remains the same

The sorting changes on alteration

None of the above

Answer: B.

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

It ignores NULL values

It does not ignore NULL values

It returns all the rows from the first component query

None of the above

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)

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;

None of the above

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?

The result will be the same

The result will be different

The result will be the same but the order will be different

None of the above

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?

It returns all the rows from all the component queries

It returns only the common rows from all the component queries

It returns all the rows from the first query and not from the subsequent queries

It returns all distinct rows selected by the first query, but not present in the subsequent queries

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?

They should be the same, the data type might be different but they should belong to the same data type group.

They should be the same along with the names of the columns

Both A and B

None of the above

Answer: A. Common feature of SET operators.

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)

SELECT employee_id 
FROM employees
UNION
SELECT employee_id 
FROM job_history;

SELECT employee_id 
FROM employees
INTERSECT
Select employee_id 
FROM job_history;

SELECT employee_id 
FROM employees
MINUS
Select employee_id 
FROM job_history;

SELECT employee_id 
FROM employees
UNION ALL
SELECT employee_id 
FROM job_history;

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?

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 duppcate rows

It executes successfully and gives the values for employees id, first_name and all jobs held by the employees excluding duppcate 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 duppcate values

It executes successfully giving the correct results excluding the duppcate 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 duppcate values

It throws an ORA error

It will execute successfully giving the correct results excluding duppcate values

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?

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 duppcate 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 duppcates.

56.You need to display all the duppcate 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 duppcates.

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 duppcate values

Result set from UNION is filtered and sorted including duppcate values

Result set from UNION ALL is not sorted and it has duppcate values

Result set from UNION is filtered and sorted without duppcate 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 epminate duppcates 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 deduppcates 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 result set, sorting them and removing duppcates.

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

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?

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?

NULL

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?

NULL

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;

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 tables 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 duppcate 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 query 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 clauses 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 error 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 duppcates

It executes successfully without duppcates

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 pterals must be enclosed within single quotes.

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

The set operators are vapd when used on columns with the LONG datatype.

The set operators are not vapd on columns of type BLOB, CLOB, BFILE, VARRAY, or nested table.

In order for the select query containing an expression, a column apas 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 vapd 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 pst

UNION operates over the first columns of the SELECT psts 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 pst 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 inspanidual 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   job_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?

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

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)

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

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)

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

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)

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

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 pne in the above query generates an error?

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 duppcates) 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
MINUS
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 appped 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.

Using the Set Operators Questions

友情链接