Clubs and Memberships

Build two related tables in one script. clubs: id INTEGER PRIMARY KEY; name TEXT NOT NULL UNIQUE; founded_year INTEGER with a CHECK that it is at least 1900; advisor_id INTEGER (optional) that REFERENCES instructors(id). memberships: id INTEGER PRIMARY KEY; club_id INTEGER NOT NULL that REFERENCES clubs(id) ON DELETE CASCADE; student_id INTEGER NOT NULL that REFERENCES students(id) ON DELETE CASCADE; role TEXT NOT NULL DEFAULT 'member'; and UNIQUE (club_id, student_id). Create clubs first, since memberships points at it; deleting a club must take its memberships with it, and the same student joining the same club twice must be refused.

Expected result:

Table clubs
✓ id INTEGER PRIMARY KEY
✓ name TEXT NOT NULL
✓ founded_year INTEGER
✓ advisor_id INTEGER
✓ advisor_id REFERENCES instructors(id)
✓ UNIQUE (name)
✓ A club founded in 1800: rejected (CHECK)
✓ A club advised by instructor 99: rejected (FOREIGN KEY)
✓ A membership of club 99: rejected (FOREIGN KEY)
✓ Student 1 joining the same club twice: rejected (UNIQUE)
✓ A membership with no role given: accepted
✓ Deleting a club that has a member: accepted

Table memberships
✓ id INTEGER PRIMARY KEY
✓ club_id INTEGER NOT NULL
✓ student_id INTEGER NOT NULL
✓ role TEXT NOT NULL DEFAULT 'member'
✓ student_id REFERENCES students(id)
✓ club_id REFERENCES clubs(id)
✓ UNIQUE (club_id, student_id)
✓ A club founded in 1800: rejected (CHECK)
✓ A club advised by instructor 99: rejected (FOREIGN KEY)
✓ A membership of club 99: rejected (FOREIGN KEY)
✓ Student 1 joining the same club twice: rejected (UNIQUE)
✓ A membership with no role given: accepted
✓ Deleting a club that has a member: accepted

Hints4 available

Topics:

CREATE TABLE

Code Editor

Tab to indent (Esc then Tab to move on) · Ctrl+Enter to run · Ctrl+Space to expand shortcuts (sel, ins, desc)

Results

Run your SQL to see every statement's output here, then how it compares with the expected answer.

Not sure what is in a table? SHOW TABLES lists them and DESCRIBE students shows the columns — or open the database browser on the left.