Test Your PostgreSQL UNION and UNION ALL Knowledge with This Quiz

Progress

True / False

In PostgreSQL, the UNION operator returns only distinct rows from the combined result set of two or more queries.

True False

: The UNION operator combines the result sets of two or more queries and removes duplicate rows.

True / False

The UNION ALL operator in PostgreSQL also removes duplicate rows from the combined result set.

True False

UNION ALL combines the result sets of two or more queries but does not remove duplicate rows.

True / False

In PostgreSQL, both the UNION and UNION ALL operators require the same number of columns in their result sets.

True False

The number and the order of columns must be the same in the queries being combined with UNION or UNION ALL.

True / False

: Using UNION in PostgreSQL is faster than using UNION ALL because it removes duplicates.

True False

UNION ALL is faster than UNION because UNION ALL does not perform the distinct operation to remove duplicates, which requires additional processing.

True / False

In PostgreSQL, the column names of the result set of a UNION query are taken from the first SELECT statement.

True False

The column names of the result set of a UNION operation are determined by the first SELECT statement in the query.

True / False

When using UNION in PostgreSQL, the data types of the corresponding columns in the SELECT statements must be compatible.

True False

The data types of the columns in the SELECT statements being combined must be compatible to ensure the UNION operation works correctly.

True / False

In PostgreSQL, you can use the ORDER BY clause with the individual SELECT statements within a UNION query.

True False

The ORDER BY clause can only be used at the end of the combined result set, not within the individual SELECT statements of a UNION query.

True / False

PostgreSQL allows the use of LIMIT and OFFSET clauses in individual SELECT statements within a UNION ALL operation.

True False

LIMIT and OFFSET can be used in individual SELECT statements to control the number of rows returned before the UNION ALL operation is applied.

True / False

The UNION operator in PostgreSQL guarantees the order of the result set based on the order of the input queries.

True False

UNION does not guarantee the order of the result set. If a specific order is required, an ORDER BY clause should be used at the end of the UNION operation.

Q10

True / False

In PostgreSQL, a UNION ALL operation can be used to combine result sets from different schemas within the same database.

True False

UNION ALL can combine result sets from different schemas as long as the columns are compatible and the number of columns in the SELECT statements is the same.

Q11

Single Choice

What is the main difference between the UNION and UNION ALL operators in PostgreSQL?

UNION returns only distinct rows; UNION ALL returns all rows, including duplicates. UNION ALL returns only distinct rows; UNION returns all rows, including duplicates. UNION and UNION ALL perform the same function.UNION ALL can only be used with SELECT * statements

In PostgreSQL, the UNION operator eliminates duplicate rows, while UNION ALL includes all rows, even if they are duplicates.

Q12

Single Choice

Which of the following queries will return all rows, including duplicates, from the employees and managers tables in PostgreSQL?

SELECT * FROM employees UNION SELECT * FROM managers; SELECT * FROM employees UNION ALL SELECT * FROM managers; SELECT * FROM employees;SELECT DISTINCT * FROM employees UNION SELECT DISTINCT * FROM managers;

The UNION ALL operator in PostgreSQL returns all rows from both queries, including duplicates.

Q13

Single Choice

Which PostgreSQL keyword is used to combine the result sets of two or more SELECT statements and remove duplicates?

UNION ALL INTERSECT UNIONEXCEPT

The UNION operator in PostgreSQL combines the result sets and removes duplicates, returning only distinct rows

Q14

Single Choice

Given the following SQL code, what will be the output?

SQL Code

SELECT name FROM employees
UNION ALL
SELECT name FROM managers;

All names from employees and managers, including duplicates. Only distinct names from employees and managers. Only names that appear in both employees and managers.All names from employees table only.

The UNION ALL operator returns all rows from both the employees and managers tables, including any duplicate names.

Q15

Single Choice

How can you modify the following PostgreSQL query to remove duplicate rows from the result set?

SQL Code

SELECT department FROM employees
UNION ALL
SELECT department FROM managers;

Replace UNION ALL with UNION. Use DISTINCT in each SELECT statement. Use INTERSECT instead of UNION ALLNo modification is needed.

By replacing UNION ALL with UNION, PostgreSQL will remove duplicate rows from the combined result set.

Q16

Single Choice

Which of the following SQL statements correctly uses UNION to combine results from two different tables in PostgreSQL?

SELECT * FROM table1 UNION ALL SELECT * FROM table2; SELECT * FROM table1 UNION SELECT * FROM table2; SELECT * FROM table1 UNION SELECT name FROM table2;SELECT * FROM table1 INTERSECT SELECT * FROM table2;

UNION combines the result sets of the two SELECT statements and removes any duplicate rows.

Q17

Single Choice

Given two PostgreSQL queries that use UNION and UNION ALL, which one will have a better performance when dealing with large datasets?

UNION UNION ALL Both have the same performance.It depends on the size of the data.

UNION ALL typically performs better with large datasets because it does not require the additional step of removing duplicates, unlike UNION.

Q18

Single Choice

In PostgreSQL, which of the following SQL queries will correctly return all distinct city names from the customers and suppliers tables, ensuring there are no duplicates?

SELECT city FROM customers UNION ALL SELECT city FROM suppliers; SELECT city FROM customers UNION SELECT city FROM suppliers; SELECT DISTINCT city FROM customers UNION ALL SELECT DISTINCT city FROM suppliers;SELECT city FROM customers INTERSECT SELECT city FROM suppliers;

UNION will return distinct city names by eliminating duplicates from the combined result set of the customers and suppliers tables.

Q19

Single Choice

When using UNION ALL in PostgreSQL, what happens if the number of columns in the SELECT statements does not match?

PostgreSQL will automatically fill in the missing columns with NULL. PostgreSQL will return an error. The query will execute, but only the matching columns will be combined.The query will execute, but the extra columns will be ignored.

In PostgreSQL, the number of columns and their data types must match when using UNION or UNION ALL. Otherwise, an error will occur.

Q20

Single Choice

Consider the following SQL query in PostgreSQL:

SQL Code

SELECT id, name FROM students
UNION
SELECT id, name FROM alumni
UNION ALL
SELECT id, name FROM faculty;
What will be the total number of rows returned if the students and alumni tables each have 10 rows (with 2 common rows), and the faculty table has 5 rows with no duplicates in other tables?

15 23 1825

The UNION operator between students and alumni eliminates 2 duplicates, resulting in 18 distinct rows. The UNION ALL with the faculty table adds all 5 rows without checking for duplicates, making the total 23 rows.

Q21

Multiple Choice

Which SQL queries correctly use UNION to combine results from two tables in PostgreSQL?

SELECT employee_id, name FROM employees UNION SELECT contractor_id, name FROM contractors; SELECT employee_id, name FROM employees UNION SELECT contractor_id, full_name FROM contractors; SELECT name, employee_id FROM employees UNION SELECT name, contractor_id FROM contractors;SELECT employee_id, name FROM employees UNION SELECT name, contractor_id FROM contractors;

UNION combines the results of two queries and removes duplicate rows from the result set. The queries must have the same number of columns and compatible data types.

Q22

Multiple Choice

Identify the SQL queries that correctly use UNION ALL to combine results from multiple SELECT statements in PostgreSQL.

SELECT product_id, total_sales FROM sales_q1 UNION ALL SELECT product_id, total_sales FROM sales_q2 UNION ALL SELECT product_id, total_sales FROM sales_q3; SELECT product_id, total_sales FROM sales_q1 UNION ALL SELECT id, total_sales FROM sales_q2 UNION ALL SELECT product_id, sales_total FROM sales_q3; SELECT total_sales, product_id FROM sales_q1 UNION ALL SELECT total_sales, product_id FROM sales_q2 UNION ALL SELECT total_sales, product_id FROM sales_q3;SELECT product_id, total_sales FROM sales_q1 UNION ALL SELECT product_id, total_sales FROM sales_q2 UNION ALL SELECT id, total_sales FROM sales_q3;

UNION ALL combines the results of multiple SELECT statements, including duplicates. The columns in each SELECT must match in number and data type.

Q23

Multiple Choice

Which SQL queries correctly apply UNION and UNION ALL to combine and filter data from multiple tables in PostgreSQL?

SELECT order_id, customer_id FROM orders UNION SELECT order_id, customer_id FROM archived_orders UNION ALL SELECT order_id, customer_id FROM orders WHERE order_date < '2024-01-01'; SELECT order_id, customer_id FROM orders UNION ALL SELECT order_id, customer_id FROM archived_orders UNION SELECT order_id, customer_id FROM orders WHERE order_date < '2024-01-01'; SELECT customer_id, order_id FROM orders UNION SELECT customer_id, order_id FROM archived_orders UNION ALL SELECT order_id, customer_id FROM orders WHERE order_date < '2024-01-01';SELECT order_id, customer_id FROM orders UNION ALL SELECT order_id, customer_id FROM archived_orders UNION ALL SELECT order_id, customer_id FROM orders WHERE order_date < '2024-01-01';

UNION removes duplicates while UNION ALL includes all rows. Properly structuring these queries ensures correct data retrieval based on the requirements.

Q24

Multiple Choice

Determine the SQL queries that correctly use UNION and UNION ALL with different SELECT statements in PostgreSQL.

SELECT item_id, quantity FROM inventory UNION SELECT item_id, quantity FROM warehouse UNION ALL SELECT item_id, quantity FROM inventory WHERE quantity > 100; SELECT item_id, quantity FROM inventory UNION ALL SELECT item_id, quantity FROM warehouse UNION SELECT item_id, quantity FROM inventory WHERE quantity > 100; SELECT item_id, quantity FROM inventory UNION SELECT item_id, quantity FROM warehouse UNION ALL SELECT quantity, item_id FROM inventory WHERE quantity > 100;SELECT item_id, quantity FROM inventory UNION ALL SELECT item_id, quantity FROM warehouse UNION ALL SELECT item_id, quantity FROM inventory WHERE quantity > 100;

Correct use of UNION and UNION ALL allows for effective merging of data sets, either with or without duplicates, based on the requirement.

Q25

Multiple Choice

Identify the SQL queries that correctly use UNION and UNION ALL to merge data from 'projects' and 'completed_projects' tables in PostgreSQL.

SELECT project_id, project_name FROM projects UNION SELECT project_id, project_name FROM completed_projects UNION ALL SELECT project_id, project_name FROM projects WHERE status = 'active'; SELECT project_id, project_name FROM projects UNION ALL SELECT project_id, project_name FROM completed_projects UNION SELECT project_id, project_name FROM projects WHERE status = 'active'; SELECT project_name, project_id FROM projects UNION SELECT project_name, project_id FROM completed_projects UNION ALL SELECT project_id, project_name FROM projects WHERE status = 'active';SELECT project_id, project_name FROM projects UNION ALL SELECT project_id, project_name FROM completed_projects UNION ALL SELECT project_id, project_name FROM projects WHERE status = 'active';

UNION and UNION ALL are effective for merging similar data sets while controlling for duplicates based on the data requirements.

Q26

Multiple Choice

Which SQL queries correctly use UNION and UNION ALL with filters in PostgreSQL?

SELECT student_id, name FROM students UNION ALL SELECT alumni_id, name FROM alumni WHERE graduation_year = 2020; SELECT student_id, name FROM students WHERE graduation_year = 2020 UNION SELECT alumni_id, name FROM alumni; SELECT student_id, name FROM students UNION SELECT alumni_id, name FROM alumni WHERE graduation_year = 2020 UNION ALL SELECT student_id, name FROM students;SELECT name, student_id FROM students UNION ALL SELECT name, alumni_id FROM alumni WHERE graduation_year = 2020;

Correct filtering combined with UNION and UNION ALL ensures that the merged data set meets the specific requirements, with or without duplicates.

Q27

Multiple Choice

Identify the SQL queries that correctly use UNION and UNION ALL to merge results from multiple SELECT statements in PostgreSQL.

SELECT employee_id, department FROM department_a UNION SELECT employee_id, department FROM department_b UNION ALL SELECT employee_id, department FROM department_c; SELECT employee_id, department FROM department_a UNION ALL SELECT employee_id, department FROM department_b UNION SELECT employee_id, department FROM department_c; SELECT department, employee_id FROM department_a UNION SELECT department, employee_id FROM department_b UNION ALL SELECT department, employee_id FROM department_c;SELECT employee_id, department FROM department_a UNION ALL SELECT employee_id, department FROM department_b UNION ALL SELECT employee_id, department FROM department_c;

Combining data from multiple tables using UNION and UNION ALL requires attention to the structure of SELECT statements and the handling of duplicates.

Q28

Multiple Choice

Determine the SQL queries that correctly use UNION to remove duplicates from combined results in PostgreSQL.

SELECT product_id, supplier_id FROM products UNION SELECT supplier_id, product_id FROM suppliers; SELECT product_id, supplier_id FROM products UNION SELECT product_id, supplier_id FROM suppliers WHERE product_id IS NOT NULL; SELECT product_id, supplier_id FROM products WHERE supplier_id IS NOT NULL UNION SELECT product_id, supplier_id FROM suppliers;SELECT supplier_id, product_id FROM products UNION SELECT supplier_id, product_id FROM suppliers WHERE product_id IS NOT NULL;

Using UNION ensures that any duplicate rows in the result set are removed, providing a distinct set of rows from the combined queries.

Q29

Multiple Choice

Which SQL queries correctly apply UNION ALL to include all rows from combined SELECT statements in PostgreSQL?

SELECT transaction_id, amount FROM transactions_q1 UNION ALL SELECT transaction_id, amount FROM transactions_q2; SELECT transaction_id, amount FROM transactions_q1 UNION ALL SELECT amount, transaction_id FROM transactions_q2; SELECT amount, transaction_id FROM transactions_q1 UNION ALL SELECT transaction_id, amount FROM transactions_q2;SELECT transaction_id, amount FROM transactions_q1 UNION ALL SELECT transaction_id, total_amount FROM transactions_q2;

UNION ALL includes all rows from the combined result set, including duplicates, which is essential when complete data is needed.

Q30

Multiple Choice

Identify the SQL queries that correctly use UNION to merge results and filter data from multiple tables in PostgreSQL.

SELECT member_id, name FROM members UNION SELECT subscriber_id, name FROM subscribers WHERE active = true; SELECT member_id, name FROM members UNION SELECT subscriber_id, full_name FROM subscribers WHERE active = true; SELECT name, member_id FROM members WHERE active = true UNION SELECT name, subscriber_id FROM subscribers;SELECT member_id, name FROM members WHERE active = true UNION SELECT subscriber_id, name FROM subscribers WHERE active = true;

Correct use of UNION ensures that the merged result set is distinct and that any filtering applied is consistent across all combined queries.

PostgreSQL Database Quiz Questions