Withdrawals and a Cancelled Course

Two things happen at term end. First, the two students on_leave, Ethan Walsh (6) and Benjamin Ivanov (18), withdraw: delete them from students, and because enrollments.student_id is declared ON DELETE CASCADE their enrollments (ids 23 and 55 for Ethan, 38 for Benjamin) vanish on their own. Second, BUS320 (course id 12), which still has no instructor, is cancelled: enrollments.course_id has no cascade, so you must delete its two enrollments (ids 73 and 80) yourself before deleting the course, or SQLite refuses. Afterwards students has 28 rows, courses 13 and enrollments 85.

Expected result:

students after your statements: 28 rows
+----+------------+-----------+--------------------------------+---------------------+-----------------+------+-------------+------------+-----------+--------------------+
| id | first_name | last_name | email                          | major_department_id | enrollment_year |  gpa | city        | birth_date | status    | scholarship_amount |
+----+------------+-----------+--------------------------------+---------------------+-----------------+------+-------------+------------+-----------+--------------------+
|  1 | Ava        | Patel     | ava.patel@student.example      |                   1 |            2023 |  3.8 | Toronto     | 2005-03-14 | active    |               2500 |
|  2 | Liam       | Nguyen    | liam.nguyen@student.example    |                   1 |            2022 | 3.45 | Mississauga | 2004-11-02 | active    |               NULL |
|  3 | Sophia     | Rossi     | sophia.rossi@student.example   |                   2 |            2023 | 3.92 | Ottawa      | 2005-07-21 | active    |               5000 |
|  4 | Noah       | Kim       | noah.kim@student.example       |                NULL |            2024 | 2.95 | Toronto     | 2006-01-09 | active    |               NULL |
|  5 | Isabella   | Okoro     | isabella.okoro@student.example |                   4 |            2021 |  3.1 | Hamilton    | 2003-05-30 | graduated |                  0 |
+----+------------+-----------+--------------------------------+---------------------+-----------------+------+-------------+------------+-----------+--------------------+
(showing 5 of 28 rows)

courses after your statements: 13 rows
+----+---------+-----------------------------+---------------+---------------+---------+-------+-----------+-------+-------------+-----+
| id | code    | title                       | department_id | instructor_id | credits | level | max_seats | room  | semester    | fee |
+----+---------+-----------------------------+---------------+---------------+---------+-------+-----------+-------+-------------+-----+
|  1 | CS101   | Introduction to Programming |             1 |             1 |       3 |   100 |        60 | T-100 | Fall 2025   | 650 |
|  2 | CS201   | Data Structures             |             1 |             2 |       3 |   200 |        40 | T-102 | Winter 2026 | 650 |
|  3 | CS230   | Databases                   |             1 |             3 |       3 |   200 |        35 | T-104 | Winter 2026 | 700 |
|  4 | CS310   | Operating Systems           |             1 |             1 |       4 |   300 |        30 | T-106 | Fall 2025   | 820 |
|  5 | MATH110 | Calculus I                  |             2 |             4 |       4 |   100 |        80 | G-001 | Fall 2025   | 600 |
+----+---------+-----------------------------+---------------+---------------+---------+-------+-----------+-------+-------------+-----+
(showing 5 of 13 rows)

enrollments after your statements: 85 rows
+----+------------+-----------+-------------+-------+--------+-----------+----------------+------+---------------------+
| id | student_id | course_id | enrolled_on | grade | letter | status    | attendance_pct | paid | notes               |
+----+------------+-----------+-------------+-------+--------+-----------+----------------+------+---------------------+
|  1 |          5 |         9 | 2024-09-03  |    78 | B      | completed |           92.5 |    1 | NULL                |
|  2 |          5 |         7 | 2024-09-03  |    81 | B      | completed |             88 |    1 | NULL                |
|  3 |         10 |        11 | 2024-09-04  |    74 | B      | completed |             79 |    1 | NULL                |
|  4 |         10 |         7 | 2024-09-04  |    69 | C      | completed |           71.5 |    1 | Late essay accepted |
|  5 |         15 |         5 | 2024-09-05  |    97 | A      | completed |             98 |    1 | NULL                |
+----+------------+-----------+-------------+-------+--------+-----------+----------------+------+---------------------+
(showing 5 of 85 rows)

Hints4 available

Topics:

INSERT, UPDATE & DELETE

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.