Why set operations are needed in SQL - Challenge Your Understanding

Challenge - 5 Problems

🧠 Conceptual

intermediate

Purpose of SQL Set Operations

Why do we use set operations like UNION, INTERSECT, and EXCEPT in SQL?

A To update multiple rows in a table simultaneously

B To delete duplicate rows within a single table

C To create new tables automatically from existing ones

D To combine or compare results from multiple queries efficiently

💡 Hint

Think about how you might want to merge or find common data from two lists.

Set operations allow combining or comparing results from different queries, like merging lists or finding common or different items.

❓ query_result

intermediate

Result of UNION Operation

Given two tables, Employees_A and Employees_B, each with a column 'Name', what will this query return? SELECT Name FROM Employees_A UNION SELECT Name FROM Employees_B;

SQL

Employees_A:
Name
Alice
Bob
Charlie

Employees_B:
Name
Bob
Diana
Eve

A Alice, Bob, Charlie, Diana, Eve (no duplicates)

B Alice, Bob, Charlie, Bob, Diana, Eve (duplicates included)

C Only names present in both tables: Bob

D Only names present in Employees_A but not in Employees_B

💡 Hint

UNION removes duplicates by default.

UNION combines rows from both queries and removes duplicates, so Bob appears only once.

📝 Syntax

advanced

Correct Syntax for INTERSECT

Which of the following SQL queries correctly uses INTERSECT to find common rows between two tables, Table1 and Table2, both having a column 'id'?

A SELECT id FROM Table1 WHERE id INTERSECT SELECT id FROM Table2;

B SELECT id FROM Table1 UNION INTERSECT SELECT id FROM Table2;

C SELECT id FROM Table1 INTERSECT SELECT id FROM Table2;

D SELECT id FROM Table1 INTERSECT WHERE id IN Table2;

💡 Hint

INTERSECT is used between two SELECT statements.

Option C correctly places INTERSECT between two SELECT queries. Other options misuse INTERSECT or have syntax errors.

❓ optimization

advanced

Optimizing Queries Using Set Operations

You want to find all customers who have placed orders or have signed up for newsletters. Which SQL approach is more efficient and why?

A Using UNION in SQL to combine customer IDs is more efficient because it reduces data transfer and leverages database optimization.

B JOIN is best because it combines tables row by row.

C Running two separate queries and merging in application is better for performance.

D Using subqueries with EXISTS is always faster than set operations.

💡 Hint

Think about where it's best to combine data: in the database or outside.

UNION lets the database combine results efficiently, reducing data sent to the application and using indexes.

🔧 Debug

expert

Why Does This EXCEPT Query Fail?

Consider these two tables with different column orders: Table1: (id INT, name VARCHAR) Table2: (name VARCHAR, id INT) Why does this query cause an error? SELECT id, name FROM Table1 EXCEPT SELECT name, id FROM Table2;

A EXCEPT requires a WHERE clause to work properly.

B Column order and types must match exactly in set operations; here, Table2 columns are selected in wrong order.

C Table2 does not exist in the database.

D EXCEPT cannot be used with more than one column.

💡 Hint

Check the order and types of columns in both SELECT statements.

Set operations require the same number of columns with matching types and order. Table2 selects columns in different order causing a type mismatch error.