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.

Practice

1. Why do we use set operations like UNION and INTERSECT in SQL?

easy

A. To create new tables permanently

B. To delete rows based on conditions

C. To update values in a single table

D. To combine or compare rows from two or more tables easily

Solution

1. Step 1: Understand the purpose of set operations

   Set operations like UNION and INTERSECT are designed to combine or compare rows from multiple tables.

2. Step 2: Identify what set operations do not do

   They do not create tables, update, or delete rows; those are different SQL commands.

3. Final Answer:

   To combine or compare rows from two or more tables easily -> Option D

4. Quick Check:

   Set operations combine or compare data [OK]

Hint: Set operations combine or compare rows from tables [OK]

Common Mistakes:

• Confusing set operations with data modification commands
• Thinking UNION creates a new permanent table
• Assuming INTERSECT deletes rows

2. Which of the following is the correct syntax to combine two SELECT queries using UNION in SQL?

easy

A. UNION SELECT * FROM table1, SELECT * FROM table2;

B. SELECT * FROM table1 JOIN UNION SELECT * FROM table2;

C. SELECT * FROM table1 UNION SELECT * FROM table2;

D. SELECT * FROM table1 AND SELECT * FROM table2 UNION;

Solution

1. Step 1: Recall correct UNION syntax

   The correct syntax is to write one SELECT query, then UNION, then another SELECT query.

2. Step 2: Check each option for syntax errors

   SELECT * FROM table1 UNION SELECT * FROM table2; follows the correct syntax. Options A, B, and D have incorrect keywords or order.

3. Final Answer:

   SELECT * FROM table1 UNION SELECT * FROM table2; -> Option C

4. Quick Check:

   Correct UNION syntax = SELECT * FROM table1 UNION SELECT * FROM table2; [OK]

Hint: UNION joins two SELECT queries directly [OK]

Common Mistakes:

• Adding JOIN keyword with UNION
• Using commas instead of UNION
• Placing UNION at the end incorrectly

3. Given two tables:
Table A: {1, 2, 3}
Table B: {2, 3, 4}
What is the result of SELECT * FROM A INTERSECT SELECT * FROM B;?

medium

A. {2, 3}

B. {1, 2, 3}

C. {1, 4}

D. {1, 2, 3, 4}

Solution

1. Step 1: Understand INTERSECT operation

   INTERSECT returns only rows present in both tables.

2. Step 2: Find common elements in Table A and Table B

   Common elements are 2 and 3.

3. Final Answer:

   {2, 3} -> Option A

4. Quick Check:

   INTERSECT = common rows [OK]

Hint: INTERSECT returns only common rows [OK]

Common Mistakes:

• Confusing INTERSECT with UNION
• Including all rows from both tables
• Mixing up EXCEPT with INTERSECT

4. You wrote this SQL query:
SELECT * FROM table1 UNION table2;
What is the error and how to fix it?

medium

A. Missing SELECT before table2; fix by adding SELECT * FROM table2

B. UNION cannot be used with tables; use JOIN instead

C. UNION requires parentheses around queries

D. No error; query runs fine

Solution

1. Step 1: Identify syntax error in UNION usage

   UNION requires two complete SELECT statements, but second query lacks SELECT.

2. Step 2: Correct the query syntax

   Add SELECT * FROM before table2 to fix the error.

3. Final Answer:

   Missing SELECT before table2; fix by adding SELECT * FROM table2 -> Option A

4. Quick Check:

   UNION needs two SELECTs [OK]

Hint: UNION needs two full SELECT queries [OK]

Common Mistakes:

• Omitting SELECT in second query
• Using UNION with tables directly
• Adding unnecessary parentheses

5. You have two customer lists:
List A: customers who bought product X
List B: customers who bought product Y
How do you find customers who bought either product X or Y but not both using set operations?

hard

A. SELECT * FROM A UNION SELECT * FROM B

B. (SELECT * FROM A EXCEPT SELECT * FROM B) UNION (SELECT * FROM B EXCEPT SELECT * FROM A)

C. SELECT * FROM A INTERSECT SELECT * FROM B

D. (SELECT * FROM A EXCEPT SELECT * FROM B) UNION (SELECT * FROM A INTERSECT SELECT * FROM B)

Solution

1. Step 1: Understand the problem

   We want customers who bought product X or Y but not both (exclusive customers).

2. Step 2: Use EXCEPT and UNION to find exclusive customers

   Find customers in A but not in B, and customers in B but not in A, then combine them with UNION.

3. Step 3: Check other options

   SELECT * FROM A UNION SELECT * FROM B gives all customers who bought either product (including both). SELECT * FROM A INTERSECT SELECT * FROM B gives only those who bought both. (SELECT * FROM A EXCEPT SELECT * FROM B) UNION (SELECT * FROM A INTERSECT SELECT * FROM B) gives all customers who bought product X.

4. Final Answer:

   (SELECT * FROM A EXCEPT SELECT * FROM B) UNION (SELECT * FROM B EXCEPT SELECT * FROM A) -> Option B

5. Quick Check:

   Exclusive customers = (A EXCEPT B) UNION (B EXCEPT A) [OK]

Hint: Use EXCEPT both ways, then UNION results [OK]

Common Mistakes:

• Using UNION alone includes both customers
• Using INTERSECT returns only common customers
• Confusing EXCEPT with INTERSECT