Test Your Oracle UNION and UNION ALL Knowledge with This SQL Quiz

Progress

True / False

In Oracle, the UNION operator combines the results of two or more SELECT statements into a single result set, removing duplicates.

True False

The UNION operator combines the results of multiple SELECT statements and removes duplicate rows from the result set.

True / False

In Oracle, the UNION ALL operator combines the results of two or more SELECT statements into a single result set, including duplicates.

True False

The UNION ALL operator combines the results of multiple SELECT statements without removing duplicates.

True / False

In Oracle, the UNION operator can be used with SELECT statements that have different numbers of columns.

True False

All SELECT statements used with the UNION operator must have the same number of columns with compatible data types.

True / False

The UNION operator in Oracle sorts the combined result set by default.

True False

The UNION operator performs an implicit sort to remove duplicates, which can affect performance for large result sets.

True / False

In Oracle, the UNION ALL operator is generally faster than the UNION operator.

True False

UNION ALL is generally faster because it does not sort and remove duplicates, reducing the amount of processing required.

True / False

In Oracle, you can use the ORDER BY clause at the end of a UNION or UNION ALL statement to sort the entire result set.

True False

The ORDER BY clause can be used at the end of the final SELECT statement in a UNION or UNION ALL to sort the entire result set.

True / False

In Oracle, the UNION and UNION ALL operators can be used with complex queries involving JOIN, GROUP BY, and HAVING clauses.

True False

Both UNION and UNION ALL can be used with complex queries that include JOIN, GROUP BY, and HAVING clauses.

True / False

In Oracle, you can use the UNION operator to combine results from different tables with different column names.

True False

The SELECT statements combined with UNION must have the same number of columns with compatible data types, even if the column names are different.

True / False

Using the UNION operator in Oracle can lead to performance issues with very large datasets due to the sorting and deduplication process.

True False

The UNION operator can impact performance on large datasets because it sorts and removes duplicates, which is resource-intensive.

Q10

True / False

The Oracle Certified Professional (OCP) exam includes knowledge on using the UNION and UNION ALL operators effectively, optimizing their performance, and understanding their differences and use cases.

True False

The OCP certification covers advanced topics, including the effective use of UNION and UNION ALL, optimizing their performance, and understanding their differences and use cases.

Q11

Single Choice

What is the primary difference between UNION and UNION ALL in ORACLE SQL?

UNION removes duplicate rows, UNION ALL does not UNION includes NULL values, UNION ALL does not UNION ALL removes duplicate rows, UNION does notUNION removes NULL values, UNION ALL does not

The UNION operator in Oracle SQL combines the results of two queries and removes duplicate rows, while UNION ALL includes all rows, including duplicates

Q12

Single Choice

Which of the following ORACLE SQL statements correctly uses UNION to combine two queries?

SELECT column1 FROM table1 UNION SELECT column2 FROM table2; SELECT column1 FROM table1 UNION ALL SELECT column2 FROM table2; SELECT column1 FROM table1 FULL JOIN table2 ON column1 = column2;SELECT column1 FROM table1 INTERSECT SELECT column2 FROM table2;

The UNION operator is used to combine the results of two queries, removing duplicates by default.

Q13

Single Choice

In ORACLE SQL, what must be true about the SELECT statements used in a UNION operation?

They must have the same number of columns and data types They must come from the same table They must have the same WHERE clauseThey must not contain any NULL values

In Oracle SQL, the SELECT statements used in a UNION operation must have the same number of columns and compatible data types in the corresponding positions.

Q14

Single Choice

Given the following ORACLE SQL queries:What will this query return?

SQL Code

SELECT employee_id FROM employees WHERE department_id = 10
UNION
SELECT employee_id FROM employees WHERE department_id = 20;

A list of employee IDs from department 10 only A list of employee IDs from department 20 only A combined list of employee IDs from departments 10 and 20, with duplicates removedA combined list of employee IDs from departments 10 and 20, including duplicates

The UNION operator combines the results of both queries and removes any duplicate employee IDs.

Q15

Single Choice

Consider the following Oracle SQL query:What does this query return?

SQL Code

SELECT department_id, COUNT(*) FROM employees GROUP BY department_id
UNION ALL
SELECT department_id, COUNT(*) FROM managers GROUP BY department_id;

The total count of employees and managers in each department, with duplicates removed The total count of employees in all departments, with duplicates removed A combined list of department counts from both tables, including duplicatesOnly the departments present in both tables

The UNION ALL operator combines the results of both queries, including all duplicates.

Q16

Single Choice

Which of the following is true about the performance of UNION versus UNION ALL in ORACLE SQL?

UNION is generally faster because it removes duplicates UNION ALL is generally faster because it does not remove duplicates Both have the same performanceUNION ALL is slower because it processes more rows

UNION ALL is faster than UNION because it does not perform the additional step of removing duplicate rows.

Q17

Single Choice

What will happen if you use UNION to combine SELECT statements with different numbers of columns in ORACLE SQL?

The query will execute and return all columns The query will return an error The query will execute but only return common columnsThe query will remove duplicate columns

In Oracle SQL, UNION requires that both SELECT statements have the same number of columns, or else the query will result in an error.

Q18

Single Choice

Consider the following Oracle SQL query:What does this query return?

SQL Code

SELECT department_name FROM departments
UNION
SELECT department_name FROM projects
ORDER BY department_name;

A list of department names from both tables, with duplicates removed and sorted alphabetically A list of all department names from both tables, including duplicates, sorted alphabetically An unsorted list of department names from both tables, with duplicates removedAn unsorted list of department names from both tables, including duplicates

The UNION operator removes duplicates and the ORDER BY clause sorts the result set alphabetically.

Q19

Single Choice

Which of the following ORACLE SQL queries would correctly use UNION ALL to combine two tables with different data types in corresponding columns?

SELECT column1 FROM table1 UNION ALL SELECT column2 FROM table2; SELECT TO_CHAR(column1) FROM table1 UNION ALL SELECT column2 FROM table2; SELECT column1 FROM table1 UNION ALL SELECT TO_CHAR(column2) FROM table2;SELECT TO_CHAR(column1) FROM table1 UNION ALL SELECT TO_CHAR(column2) FROM table2;

To use UNION ALL with different data types, you must convert the data types to a common type, such as using TO_CHAR to convert numeric or date types to strings.

Q20

Single Choice

Given the following ORACLE SQL query:What will this query return?

SQL Code

SELECT employee_id FROM employees WHERE salary > 100000
UNION ALL
SELECT employee_id FROM managers WHERE bonus > 20000
INTERSECT
SELECT employee_id FROM employees WHERE hire_date > '2020-01-01';

Employee IDs with salary > 100000 or bonus > 20000, hired after '2020-01-01' Employee IDs with salary > 100000 and bonus > 20000, hired after '2020-01-01' Employee IDs with both high salaries and bonuses, regardless of hire dateEmployee IDs with high salaries or bonuses, regardless of hire date

This query first combines employee IDs with high salaries or bonuses using UNION ALL, then intersects this result with employees hired after '2020-01-01', returning those who meet both criteria.

Q21

Multiple Choice

Which SQL code snippet demonstrates the use of UNION to combine the results of two SELECT queries with no duplicate rows in Oracle?

SQL Code

SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 10
UNION
SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 20;

SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 10
UNION
SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 20
ORDER BY last_name;

SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 10
UNION
SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 20;

SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 10 UNION SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 20; SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 10 UNION SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 20 ORDER BY last_name; SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 10 UNION SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 20;SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 10 JOIN employees ON department_id = 20;

Options A, B, and C correctly use UNION to combine the results of two SELECT queries with no duplicate rows. Option D is incorrect because it does not use the UNION operator.

Q22

Multiple Choice

Which SQL code snippet uses UNION ALL to combine the results of two SELECT queries with all rows, including duplicates, in Oracle?

SQL Code

SELECT product_id, product_name FROM products WHERE category_id = 5
UNION ALL
SELECT product_id, product_name FROM products WHERE category_id = 7;

SELECT product_id, product_name FROM products WHERE category_id = 5
UNION ALL
SELECT product_id, product_name FROM products WHERE category_id = 7
ORDER BY product_name;

SELECT product_id, product_name FROM products WHERE category_id = 5
UNION ALL
SELECT product_id, product_name FROM products WHERE category_id = 7;

SELECT product_id, product_name FROM products WHERE category_id = 5 UNION ALL SELECT product_id, product_name FROM products WHERE category_id = 7; SELECT product_id, product_name FROM products WHERE category_id = 5 UNION ALL SELECT product_id, product_name FROM products WHERE category_id = 7 ORDER BY product_name; SELECT product_id, product_name FROM products WHERE category_id = 5 UNION ALL SELECT product_id, product_name FROM products WHERE category_id = 7;SELECT product_id, product_name FROM products WHERE category_id = 5 UNION SELECT product_id, product_name FROM products WHERE category_id = 7;

Options A, B, and C correctly use UNION ALL to combine the results of two SELECT queries with all rows, including duplicates. Option D is incorrect because it does not use the UNION ALL operator.

Q23

Multiple Choice

Which SQL code snippet demonstrates the use of UNION to retrieve a list of all unique department names and job titles in Oracle?

SQL Code

SELECT department_name FROM departments
UNION
SELECT job_title FROM jobs;

SELECT department_name FROM departments
UNION
SELECT job_title FROM jobs
ORDER BY department_name;

SELECT department_name FROM departments
UNION
SELECT job_title FROM jobs
ORDER BY job_title;

SELECT department_name FROM departments UNION SELECT job_title FROM jobs; SELECT department_name FROM departments UNION SELECT job_title FROM jobs ORDER BY department_name; SELECT department_name FROM departments UNION SELECT job_title FROM jobs ORDER BY job_title;SELECT department_name FROM departments JOIN jobs ON department_name = job_title;

Options A, B, and C correctly use UNION to retrieve a list of all unique department names and job titles. Option D is incorrect because it does not use the UNION operator.

Q24

Multiple Choice

Which SQL code snippet demonstrates the use of UNION ALL to retrieve all employees and their corresponding department names, including duplicates, in Oracle?

SQL Code

SELECT employee_id, department_name FROM employees e
JOIN departments d ON e.department_id = d.department_id
UNION ALL
SELECT employee_id, department_name FROM employees e
JOIN departments d ON e.department_id = d.department_id;

SELECT employee_id, department_name FROM employees e
JOIN departments d ON e.department_id = d.department_id
UNION ALL
SELECT employee_id, department_name FROM employees e
JOIN departments d ON e.department_id = d.department_id
ORDER BY department_name;

SELECT employee_id, department_name FROM employees e JOIN departments d ON e.department_id = d.department_id UNION ALL SELECT employee_id, department_name FROM employees e JOIN departments d ON e.department_id = d.department_id; SELECT employee_id, department_name FROM employees e JOIN departments d ON e.department_id = d.department_id UNION ALL SELECT employee_id, department_name FROM employees e JOIN departments d ON e.department_id = d.department_id; SELECT employee_id, department_name FROM employees e JOIN departments d ON e.department_id = d.department_id UNION ALL SELECT employee_id, department_name FROM employees e JOIN departments d ON e.department_id = d.department_id ORDER BY department_name;SELECT employee_id, department_name FROM employees e JOIN departments d ON e.department_id = d.department_id;

Options A, B, and C correctly use UNION ALL to retrieve all employees and their corresponding department names, including duplicates. Option D is incorrect because it does not use the UNION ALL operator.

Q25

Multiple Choice

Which SQL code snippet demonstrates the use of UNION to combine the results of two queries that retrieve employees with the highest salaries in different departments in Oracle?

SQL Code

SELECT employee_id, first_name, last_name, salary FROM employees WHERE department_id = 10 AND salary = (SELECT MAX(salary) FROM employees WHERE department_id = 10)
UNION
SELECT employee_id, first_name, last_name, salary FROM employees WHERE department_id = 20 AND salary = (SELECT MAX(salary) FROM employees WHERE department_id = 20);

SELECT employee_id, first_name, last_name, salary FROM employees WHERE department_id = 10 AND salary = (SELECT MAX(salary) FROM employees WHERE department_id = 10)
UNION
SELECT employee_id, first_name, last_name, salary FROM employees WHERE department_id = 20 AND salary = (SELECT MAX(salary) FROM employees WHERE department_id = 20)
ORDER BY salary DESC;

SELECT employee_id, first_name, last_name, salary FROM employees WHERE department_id = 10 AND salary = (SELECT MAX(salary) FROM employees WHERE department_id = 10) UNION SELECT employee_id, first_name, last_name, salary FROM employees WHERE department_id = 20 AND salary = (SELECT MAX(salary) FROM employees WHERE department_id = 20); SELECT employee_id, first_name, last_name, salary FROM employees WHERE department_id = 10 AND salary = (SELECT MAX(salary) FROM employees WHERE department_id = 10) UNION SELECT employee_id, first_name, last_name, salary FROM employees WHERE department_id = 20 AND salary = (SELECT MAX(salary) FROM employees WHERE department_id = 20); SELECT employee_id, first_name, last_name, salary FROM employees WHERE department_id = 10 AND salary = (SELECT MAX(salary) FROM employees WHERE department_id = 10) UNION SELECT employee_id, first_name, last_name, salary FROM employees WHERE department_id = 20 AND salary = (SELECT MAX(salary) FROM employees WHERE department_id = 20) ORDER BY salary DESC;SELECT employee_id, first_name, last_name, salary FROM employees WHERE department_id = 10 AND salary = (SELECT MAX(salary) FROM employees WHERE department_id = 10) JOIN employees ON department_id = 20 AND salary = (SELECT MAX(salary) FROM employees WHERE department_id = 20);

Options A, B, and C correctly use UNION to combine the results of two queries that retrieve employees with the highest salaries in different departments. Option D is incorrect because it does not use the UNION operator.

Q26

Multiple Choice

Which SQL code snippet demonstrates advanced use of UNION ALL to combine the results of three SELECT queries with all rows, including duplicates, in Oracle?

SQL Code

SELECT employee_id, first_name FROM employees WHERE department_id = 30
UNION ALL
SELECT employee_id, first_name FROM employees WHERE department_id = 40
UNION ALL
SELECT employee_id, first_name FROM employees WHERE department_id = 50;

SELECT employee_id, first_name FROM employees WHERE department_id = 30
UNION ALL
SELECT employee_id, first_name FROM employees WHERE department_id = 40
UNION ALL
SELECT employee_id, first_name FROM employees WHERE department_id = 50
ORDER BY first_name;

SELECT employee_id, first_name FROM employees WHERE department_id = 30 UNION ALL SELECT employee_id, first_name FROM employees WHERE department_id = 40 UNION ALL SELECT employee_id, first_name FROM employees WHERE department_id = 50; SELECT employee_id, first_name FROM employees WHERE department_id = 30 UNION ALL SELECT employee_id, first_name FROM employees WHERE department_id = 40 UNION ALL SELECT employee_id, first_name FROM employees WHERE department_id = 50; SELECT employee_id, first_name FROM employees WHERE department_id = 30 UNION ALL SELECT employee_id, first_name FROM employees WHERE department_id = 40 UNION ALL SELECT employee_id, first_name FROM employees WHERE department_id = 50 ORDER BY first_name;SELECT employee_id, first_name FROM employees WHERE department_id = 30 JOIN employees ON department_id = 40 JOIN employees ON department_id = 50;

Options A, B, and C correctly demonstrate advanced use of UNION ALL to combine the results of three SELECT queries with all rows, including duplicates. Option D is incorrect because it does not use the UNION ALL operator.

Q27

Multiple Choice

Which SQL code snippet demonstrates the use of UNION to retrieve a list of all unique product names from two different tables in Oracle?

SQL Code

SELECT product_name FROM products_a
UNION
SELECT product_name FROM products_b;

SELECT product_name FROM products_a
UNION
SELECT product_name FROM products_b
ORDER BY product_name;

SELECT product_name FROM products_a
UNION
SELECT product_name FROM products_b
ORDER BY product_name;

SELECT product_name FROM products_a UNION SELECT product_name FROM products_b; SELECT product_name FROM products_a UNION SELECT product_name FROM products_b ORDER BY product_name; SELECT product_name FROM products_a UNION SELECT product_name FROM products_b ORDER BY product_name;SELECT product_name FROM products_a JOIN products_b ON product_name;

Options A, B, and C correctly use UNION to retrieve a list of all unique product names from two different tables. Option D is incorrect because it does not use the UNION operator.

Q28

Multiple Choice

Which SQL code snippet demonstrates the use of UNION ALL to generate a report that includes all sales data from two different sales tables, including duplicate records, in Oracle?

SQL Code

SELECT sale_id, sale_amount FROM sales_q1
UNION ALL
SELECT sale_id, sale_amount FROM sales_q2;

SELECT sale_id, sale_amount FROM sales_q1
UNION ALL
SELECT sale_id, sale_amount FROM sales_q2
ORDER BY sale_id;

SELECT sale_id, sale_amount FROM sales_q1
UNION ALL
SELECT sale_id, sale_amount FROM sales_q2;

SELECT sale_id, sale_amount FROM sales_q1 UNION ALL SELECT sale_id, sale_amount FROM sales_q2; SELECT sale_id, sale_amount FROM sales_q1 UNION ALL SELECT sale_id, sale_amount FROM sales_q2 ORDER BY sale_id; SELECT sale_id, sale_amount FROM sales_q1 UNION ALL SELECT sale_id, sale_amount FROM sales_q2;SELECT sale_id, sale_amount FROM sales_q1 JOIN sales_q2 ON sale_id;

Options A, B, and C correctly use UNION ALL to generate a report that includes all sales data from two different sales tables, including duplicate records. Option D is incorrect because it does not use the UNION ALL operator.

Q29

Multiple Choice

Which SQL code snippet demonstrates certification-level use of UNION to retrieve a comprehensive list of all employees and their managers, ensuring that there are no duplicate rows, in Oracle?

SQL Code

SELECT employee_id, manager_id FROM employees WHERE department_id = 10
UNION
SELECT employee_id, manager_id FROM employees WHERE department_id = 20;

SELECT employee_id, manager_id FROM employees WHERE department_id = 10
UNION
SELECT employee_id, manager_id FROM employees WHERE department_id = 20
ORDER BY manager_id;

SELECT employee_id, manager_id FROM employees WHERE department_id = 10
UNION
SELECT employee_id, manager_id FROM employees WHERE department_id = 20;

SELECT employee_id, manager_id FROM employees WHERE department_id = 10 UNION SELECT employee_id, manager_id FROM employees WHERE department_id = 20; SELECT employee_id, manager_id FROM employees WHERE department_id = 10 UNION SELECT employee_id, manager_id FROM employees WHERE department_id = 20; SELECT employee_id, manager_id FROM employees WHERE department_id = 10 UNION SELECT employee_id, manager_id FROM employees WHERE department_id = 20 ORDER BY manager_id;SELECT employee_id, manager_id FROM employees WHERE department_id = 10 JOIN employees ON department_id = 20;

Options A, B, and C correctly demonstrate certification-level use of UNION to retrieve a comprehensive list of all employees and their managers, ensuring that there are no duplicate rows. Option D is incorrect because it does not use the UNION operator.

Q30

Multiple Choice

Which SQL code snippet demonstrates certification-level use of UNION ALL to create a detailed report combining employee data from three different departments, including all rows, in Oracle?

SQL Code

SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 30
UNION ALL
SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 40
UNION ALL
SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 50;

SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 30
UNION ALL
SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 40
UNION ALL
SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 50
ORDER BY last_name;

SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 30 UNION ALL SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 40 UNION ALL SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 50; SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 30 UNION ALL SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 40 UNION ALL SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 50 ORDER BY last_name; SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 30 UNION ALL SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 40 UNION ALL SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 50;SELECT employee_id, first_name, last_name FROM employees WHERE department_id = 30 JOIN employees ON department_id = 40 JOIN employees ON department_id = 50;

Options A, B, and C correctly demonstrate certification-level use of UNION ALL to create a detailed report combining employee data from three different departments, including all rows. Option D is incorrect because it does not use the UNION ALL operator.

Oracle Database Quiz Questions