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.