Union, intersection, difference in DBMS Theory - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding UNION operation in SQL

Consider two tables, TableA and TableB, each with a single column value. TableA contains values (1, 2, 3) and TableB contains values (3, 4, 5). What will be the result of the following SQL query?

SELECT value FROM TableA
UNION
SELECT value FROM TableB;

DBMS Theory

SELECT value FROM TableA
UNION
SELECT value FROM TableB;

A (1, 2, 4, 5)

B (1, 2, 3, 4, 5)

C (1, 2, 3, 3, 4, 5)

D (3)

💡 Hint

Remember that UNION removes duplicate rows.

The UNION operation combines rows from both tables and removes duplicates. Since 3 appears in both tables, it appears only once in the result.

🧠 Conceptual

intermediate

Understanding INTERSECT operation in SQL

Given the same tables TableA (values 1, 2, 3) and TableB (values 3, 4, 5), what is the output of this query?

SELECT value FROM TableA
INTERSECT
SELECT value FROM TableB;

DBMS Theory

SELECT value FROM TableA
INTERSECT
SELECT value FROM TableB;

A (3)

B (1, 2, 3, 4, 5)

C (1, 2)

D (4, 5)

💡 Hint

INTERSECT returns only the common rows between both tables.

Only the value 3 is present in both TableA and TableB, so the result contains only 3.

🧠 Conceptual

advanced

Understanding EXCEPT (Difference) operation in SQL

Using the same tables TableA (values 1, 2, 3) and TableB (values 3, 4, 5), what does this query return?

SELECT value FROM TableA
EXCEPT
SELECT value FROM TableB;

DBMS Theory

SELECT value FROM TableA
EXCEPT
SELECT value FROM TableB;

A (1, 2, 3, 4, 5)

B (4, 5)

C (3, 4, 5)

D (1, 2)

💡 Hint

EXCEPT returns rows from the first table that are not in the second table.

Values 1 and 2 are in TableA but not in TableB, so they are returned.

🔍 Analysis

advanced

Comparing UNION ALL and UNION

What is the difference in output between these two queries given TableA (1, 2, 3) and TableB (3, 4, 5)?

Query 1:
SELECT value FROM TableA
UNION
SELECT value FROM TableB;

Query 2:
SELECT value FROM TableA
UNION ALL
SELECT value FROM TableB;

DBMS Theory

SELECT value FROM TableA
UNION
SELECT value FROM TableB;

SELECT value FROM TableA
UNION ALL
SELECT value FROM TableB;

A Query 1 removes duplicates; Query 2 includes duplicates

B Query 1 includes duplicates; Query 2 removes duplicates

C Both queries return the same result

D Query 1 returns only TableA values; Query 2 returns only TableB values

💡 Hint

Think about how UNION and UNION ALL treat duplicate rows.

UNION removes duplicate rows, while UNION ALL includes all rows from both tables, including duplicates.

❓ Reasoning

expert

Determining the number of rows after set operations

Given two tables, TableX with 5 unique rows and TableY with 7 unique rows, where 3 rows are common to both tables, how many rows will the following query return?

SELECT * FROM TableX
INTERSECT
SELECT * FROM TableY;

DBMS Theory

SELECT * FROM TableX
INTERSECT
SELECT * FROM TableY;

A 5

B 9

C 3

D 7

💡 Hint

INTERSECT returns only rows present in both tables.

Since 3 rows are common, the INTERSECT returns exactly those 3 rows.