Clubs and Their Members

Write two CREATE TABLE statements in one script. First clubs: id (an INTEGER PRIMARY KEY), name (TEXT, required, and no two clubs may share a name) and founded_year (INTEGER, optional). Then club_members, which links students to clubs: student_id (INTEGER, required, references students(id) with ON DELETE CASCADE), club_id (INTEGER, required, references clubs(id) with ON DELETE CASCADE) and joined_on (TEXT, required). A student can join a club only once, so the primary key of club_members is the pair (student_id, club_id) and there is no separate id column. Deleting a club or a student must remove their memberships.

Expected result:

Table clubs
✓ id INTEGER PRIMARY KEY
✓ name TEXT NOT NULL
✓ founded_year INTEGER
✓ UNIQUE (name)
✓ Add student 1 to club 1 twice: rejected (UNIQUE)
✓ Add student 1 to two different clubs: accepted
✓ Add student 1 to club 99, which does not exist: rejected (FOREIGN KEY)
✓ Create two clubs called Chess Club: rejected (UNIQUE)
✓ Add a member to club 1, then delete club 1: accepted
✓ Add student 28 to club 1, then delete student 28: accepted

Table club_members
✓ student_id INTEGER PRIMARY KEY
✓ club_id INTEGER PRIMARY KEY
✓ joined_on TEXT NOT NULL
✓ club_id REFERENCES clubs(id)
✓ student_id REFERENCES students(id)
✓ Add student 1 to club 1 twice: rejected (UNIQUE)
✓ Add student 1 to two different clubs: accepted
✓ Add student 1 to club 99, which does not exist: rejected (FOREIGN KEY)
✓ Create two clubs called Chess Club: rejected (UNIQUE)
✓ Add a member to club 1, then delete club 1: accepted
✓ Add student 28 to club 1, then delete student 28: accepted

Hints4 available

Topics:

Constraints

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.