Set difference: students not in the club

Use the difference operation to find what one relation has and the other does not.

What to build

A query that computes π name ( Student ) − ClubMember.

Requirements

• Use EXCEPT.
• Return each name once.
• Sort the names alphabetically.

Tables

Each test fills these tables with its own rows, then runs your SQL:

CREATE TABLE Student (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT NOT NULL
);
CREATE TABLE ClubMember (
  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

Club members need not be students, and two students may share a name.

Example

When a test starts with:

INSERT INTO Student VALUES (1, 'Rahim', 'CSE'), (2, 'Karim', 'EEE'), (3, 'Nadia', 'CSE');
INSERT INTO ClubMember VALUES ('Nadia'), ('Imran');

your SQL should print:

name
-----
Karim
Rahim
(2 rows)

Why it works

EXCEPT keeps the rows of the first result that do not appear in the second, and like the algebra it removes repeats.

Examples

CREATE TABLE Student (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT NOT NULL
);
CREATE TABLE ClubMember (
  name TEXT NOT NULL
);
INSERT INTO Student VALUES (1, 'Rahim', 'CSE'), (2, 'Karim', 'EEE'), (3, 'Nadia', 'CSE');
INSERT INTO ClubMember VALUES ('Nadia'), ('Imran');

name
-----
Karim
Rahim
(2 rows)

CREATE TABLE Student (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT NOT NULL
);
CREATE TABLE ClubMember (
  name TEXT NOT NULL
);
INSERT INTO Student VALUES (1, 'A', 'CSE');
INSERT INTO ClubMember 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

Solution

SELECT name FROM Student
EXCEPT
SELECT name FROM ClubMember
ORDER BY name;

Lesson: Relational algebra →