Combine set operations in the right order, which takes more care than it seems.
What to build
A query that finds the students who play exactly one of cricket and football.
Requirements
- Find the cricket players who do not play football, and the football players who do not play cricket.
- Combine the two lists with UNION.
- Sort the names alphabetically.
Tables
Each test fills these tables with its own rows, then runs your SQL:
CREATE TABLE cricket (
name TEXT NOT NULL
);
CREATE TABLE football (
name TEXT NOT NULL
);
Output
One column, name, in alphabetical order. Your result is compared with the expected one, column names included, so name each column exactly as asked (use AS).
Constraints
A name may appear more than once in the same table.
Example
When a test starts with:
INSERT INTO cricket VALUES ('Rahim'), ('Karim'), ('Nadia');
INSERT INTO football VALUES ('Karim'), ('Tania'), ('Nadia'), ('Sumon');
your SQL should print:
name
-----
Rahim
Sumon
Tania
(3 rows)
Why it works
Written as one chain, A EXCEPT B UNION B EXCEPT A means ((A EXCEPT B) UNION B) EXCEPT A, which removes every cricket player. Brackets around each difference give the symmetric difference that is wanted.
Examples
CREATE TABLE cricket (
name TEXT NOT NULL
);
CREATE TABLE football (
name TEXT NOT NULL
);
INSERT INTO cricket VALUES ('Rahim'), ('Karim'), ('Nadia');
INSERT INTO football VALUES ('Karim'), ('Tania'), ('Nadia'), ('Sumon');
name ----- Rahim Sumon Tania (3 rows)
CREATE TABLE cricket (
name TEXT NOT NULL
);
CREATE TABLE football (
name TEXT NOT NULL
);
INSERT INTO cricket VALUES ('A');
INSERT INTO football VALUES ('A');
name ---- (0 rows)
Each test makes its own tables, runs your SQL on them, and compares what your queries return. Changes never carry over from one test to the next.
Hints
Hint 1
The answer is (cricket EXCEPT football) together with (football EXCEPT cricket).
Hint 2
SQL works through a chain of set operations from left to right, so write each EXCEPT as a subquery in brackets before joining them with UNION.
Solution
Show one correct solution (try it yourself first)
SELECT name FROM (SELECT name FROM cricket EXCEPT SELECT name FROM football) UNION SELECT name FROM (SELECT name FROM football EXCEPT SELECT name FROM cricket) ORDER BY name;