Every Rule at Once

Create exams with every kind of rule at once: id INTEGER PRIMARY KEY; course_id INTEGER NOT NULL that REFERENCES courses(id) ON DELETE CASCADE; title TEXT NOT NULL; exam_date TEXT NOT NULL; weight REAL NOT NULL DEFAULT 0.25 with a CHECK that it is greater than 0 and at most 1; max_marks INTEGER NOT NULL DEFAULT 100 with a CHECK that it is greater than 0; room TEXT (optional); and a table-level UNIQUE (course_id, exam_date). The checker tries an exam with only a course, title and date (accepted, with the defaults), a weight of 1.5 and a maximum of 0 marks (both refused), course 99 (refused), two exams for one course on one day (refused) and deleting course 13 after scheduling an exam for it (accepted: the exam goes with the course).

Expected result:

Table exams
✓ id INTEGER PRIMARY KEY
✓ course_id INTEGER NOT NULL
✓ title TEXT NOT NULL
✓ exam_date TEXT NOT NULL
✓ weight REAL NOT NULL DEFAULT 0.25
✓ max_marks INTEGER NOT NULL DEFAULT 100
✓ room TEXT
✓ course_id REFERENCES courses(id)
✓ UNIQUE (course_id, exam_date)
✓ An exam given only a course, title and date: accepted
✓ An exam worth 150 percent of the grade (weight 1.5): rejected (CHECK)
✓ An exam out of 0 marks: rejected (CHECK)
✓ An exam for course 99: rejected (FOREIGN KEY)
✓ Two exams for course 1 on the same day: rejected (UNIQUE)
✓ Deleting course 13 after scheduling its exam: 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.