Set operations with ORDER BY in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of UNION with ORDER BY on combined result

Consider two tables EmployeesA and EmployeesB with a single column name. What is the output of the following query?

SELECT name FROM EmployeesA
UNION
SELECT name FROM EmployeesB
ORDER BY name ASC;

SQL

CREATE TABLE EmployeesA(name VARCHAR(20));
INSERT INTO EmployeesA VALUES ('Alice'), ('Bob'), ('Charlie');
CREATE TABLE EmployeesB(name VARCHAR(20));
INSERT INTO EmployeesB VALUES ('Bob'), ('Diana'), ('Eve');

A

Alice
Bob
Charlie
Diana
Eve

B

Alice
Bob
Bob
Charlie
Diana
Eve

C

Bob
Alice
Charlie
Diana
Eve

D

Alice
Charlie
Diana
Eve

💡 Hint

UNION removes duplicates and ORDER BY sorts the combined result.

UNION combines rows from both tables and removes duplicates. Then ORDER BY sorts the unique names alphabetically.

❓ query_result

intermediate

Effect of ORDER BY inside individual SELECTs with UNION ALL

Given two tables Numbers1 and Numbers2 each with a column num, what is the output of this query?

SELECT num FROM Numbers1 ORDER BY num DESC
UNION ALL
SELECT num FROM Numbers2 ORDER BY num ASC;

SQL

CREATE TABLE Numbers1(num INT);
INSERT INTO Numbers1 VALUES (3), (1), (2);
CREATE TABLE Numbers2(num INT);
INSERT INTO Numbers2 VALUES (4), (5);

A Syntax error due to ORDER BY in subqueries

B

3
1
2
4
5

C

1
2
3
4
5

D

5
4
3
2
1

💡 Hint

ORDER BY is not allowed inside individual SELECTs in UNION unless wrapped in subqueries.

ORDER BY clauses inside individual SELECT statements in a UNION cause syntax errors in standard SQL. ORDER BY should be applied once after the UNION.

📝 Syntax

advanced

Correct placement of ORDER BY with INTERSECT

Which of the following queries correctly returns the common id values from tables Table1 and Table2 sorted in descending order?

A SELECT id FROM Table1 ORDER BY id DESC INTERSECT SELECT id FROM Table2;

B SELECT id FROM Table1 INTERSECT SELECT id FROM Table2;

C SELECT id FROM Table1 INTERSECT SELECT id FROM Table2 ORDER BY DESC id;

D SELECT id FROM Table1 INTERSECT SELECT id FROM Table2 ORDER BY id DESC;

💡 Hint

ORDER BY applies to the final result of the set operation.

ORDER BY must come after the entire INTERSECT operation to sort the final result. Option D places ORDER BY correctly.

❓ query_result

advanced

Output of EXCEPT with ORDER BY and duplicates

Given tables SetA and SetB with column val, what is the output of this query?

SELECT val FROM SetA
EXCEPT
SELECT val FROM SetB
ORDER BY val ASC;

SQL

CREATE TABLE SetA(val INT);
INSERT INTO SetA VALUES (1), (2), (2), (3);
CREATE TABLE SetB(val INT);
INSERT INTO SetB VALUES (2);

A

1
2
2
3

B

1
2
3

C

1
3

D

2
3

💡 Hint

EXCEPT removes all values from SetA that appear in SetB, duplicates removed.

EXCEPT returns distinct values in SetA not in SetB. Since 2 is in SetB, all 2s are excluded. Result sorted ascending is 1 and 3.

🧠 Conceptual

expert

Why ORDER BY must be last in set operations

Why is it required that the ORDER BY clause appears only once at the end of a query combining multiple SELECTs with set operations like UNION, INTERSECT, or EXCEPT?

A Because SQL does not allow ORDER BY anywhere in queries with set operations.

B Because set operations combine unordered sets, so sorting must happen after the full result is formed.

C Because ORDER BY inside individual SELECTs would sort only partial results, which is ignored by the database.

D Because ORDER BY clauses inside set operations cause the database to run slower.

💡 Hint

Think about how set operations treat their inputs and outputs.

Set operations treat their inputs as unordered sets. Sorting before combining would be meaningless. ORDER BY must be applied once after the full combined result is ready.

Practice

1. What does the UNION operation do when combining results from two SELECT queries?

easy

A. Sorts the results in ascending order

B. Combines results and removes duplicate rows

C. Combines results and keeps all duplicate rows

D. Joins tables based on a common column

Solution

1. Step 1: Understand UNION operation

   The UNION operation combines results from two SELECT queries into one result set.

2. Step 2: Check duplicate handling

   UNION removes duplicate rows, unlike UNION ALL which keeps duplicates.

3. Final Answer:

   Combines results and removes duplicate rows -> Option B

4. Quick Check:

   UNION removes duplicates = C [OK]

Hint: UNION removes duplicates; UNION ALL keeps them [OK]

Common Mistakes:

• Confusing UNION with UNION ALL
• Thinking UNION sorts results automatically
• Mixing UNION with JOIN operations

2. Which of the following is the correct syntax to combine two SELECT queries with UNION and sort the final result by column name?

easy

A. SELECT name FROM table1 UNION ORDER BY name SELECT name FROM table2;

B. SELECT name FROM table1 ORDER BY name UNION SELECT name FROM table2;

C. SELECT name FROM table1 UNION SELECT name FROM table2 ORDER BY name;

D. SELECT name FROM table1 UNION SELECT name FROM table2 SORT BY name;

Solution

1. Step 1: Understand correct UNION syntax

   The UNION combines two SELECT queries; ORDER BY applies after the last SELECT.

2. Step 2: Check placement of ORDER BY

   ORDER BY must come after the entire UNION, not between or before queries.

3. Final Answer:

   SELECT name FROM table1 UNION SELECT name FROM table2 ORDER BY name; -> Option C

4. Quick Check:

   ORDER BY after UNION = A [OK]

Hint: Place ORDER BY after all UNION queries [OK]

Common Mistakes:

• Putting ORDER BY before UNION
• Using SORT BY instead of ORDER BY
• Placing ORDER BY between SELECTs

3. Given two tables:
table1 with values (1, 'Alice'), (2, 'Bob')
table2 with values (2, 'Bob'), (3, 'Charlie')
What is the result of this query?

SELECT id, name FROM table1 UNION SELECT id, name FROM table2 ORDER BY id;

medium

A. (1, 'Alice'), (2, 'Bob'), (3, 'Charlie')

B. (1, 'Alice'), (2, 'Bob'), (2, 'Bob'), (3, 'Charlie')

C. (2, 'Bob'), (3, 'Charlie')

D. (1, 'Alice'), (3, 'Charlie')

Solution

1. Step 1: Combine rows with UNION

   UNION merges rows from both tables and removes duplicates, so (2, 'Bob') appears once.

2. Step 2: Sort combined results by id

   Ordering by id gives rows in order: 1, 2, 3.

3. Final Answer:

   (1, 'Alice'), (2, 'Bob'), (3, 'Charlie') -> Option A

4. Quick Check:

   UNION removes duplicates, ORDER BY sorts = B [OK]

Hint: UNION removes duplicates; ORDER BY sorts final list [OK]

Common Mistakes:

• Expecting duplicates with UNION
• Ignoring ORDER BY sorting
• Confusing UNION with UNION ALL

4. Identify the error in this SQL query:

SELECT name FROM table1 UNION ALL ORDER BY name SELECT name FROM table2;

medium

A. SELECT statements must have different column names

B. UNION ALL cannot be used with ORDER BY

C. Missing semicolon after first SELECT

D. ORDER BY is placed incorrectly between UNION ALL and second SELECT

Solution

1. Step 1: Check UNION ALL syntax

   UNION ALL combines two SELECT queries; ORDER BY must come after both queries, not between.

2. Step 2: Identify ORDER BY placement error

   ORDER BY is incorrectly placed between UNION ALL and second SELECT, causing syntax error.

3. Final Answer:

   ORDER BY is placed incorrectly between UNION ALL and second SELECT -> Option D

4. Quick Check:

   ORDER BY after UNION ALL queries = D [OK]

Hint: ORDER BY must follow all UNION ALL queries [OK]

Common Mistakes:

• Placing ORDER BY between UNION ALL and second SELECT
• Thinking UNION ALL disallows ORDER BY
• Assuming column names must differ

5. You have two tables:
employees with columns (id, name, department)
contractors with columns (id, name, department)
Write a query to list all unique names from both tables sorted alphabetically, but include duplicates if the same name appears in both tables.
Which query achieves this?

hard

A. SELECT name FROM employees UNION ALL SELECT name FROM contractors ORDER BY name;

B. SELECT name FROM employees UNION SELECT name FROM contractors ORDER BY name;

C. SELECT name FROM employees INTERSECT SELECT name FROM contractors ORDER BY name;

D. SELECT name FROM employees EXCEPT SELECT name FROM contractors ORDER BY name;

Solution

1. Step 1: Understand requirement for duplicates

   The query should include duplicates if the same name appears in both tables, so duplicates must be kept.

2. Step 2: Choose correct set operation

   UNION removes duplicates, so it is not suitable. UNION ALL keeps duplicates from both tables.

3. Step 3: Confirm sorting

   ORDER BY name sorts the combined result alphabetically.

4. Final Answer:

   SELECT name FROM employees UNION ALL SELECT name FROM contractors ORDER BY name; -> Option A

5. Quick Check:

   UNION ALL keeps duplicates, ORDER BY sorts = A [OK]

Hint: Use UNION ALL to keep duplicates, ORDER BY to sort [OK]

Common Mistakes:

• Using UNION which removes duplicates
• Confusing INTERSECT or EXCEPT with UNION
• Placing ORDER BY incorrectly