Seats Left Per Course

Two statements, then the SELECT already at the bottom. First, create an index called idx_enrollments_course on enrollments(course_id), the column the view below joins on. Then create a view called course_seats with one row per course and five columns: code, title, max_seats, enrolled (the number of enrollments whose status is not 'dropped') and seats_left (max_seats minus enrolled). Every course must appear, including ART140, which has no enrollments at all and must show enrolled 0. The final SELECT lists the view by seats_left, then code.

Expected result:

Table enrollments
✓ id INTEGER PRIMARY KEY
✓ student_id INTEGER NOT NULL
✓ course_id INTEGER NOT NULL
✓ enrolled_on TEXT NOT NULL
✓ grade REAL
✓ letter TEXT
✓ status TEXT NOT NULL DEFAULT 'enrolled'
✓ attendance_pct REAL
✓ paid INTEGER NOT NULL DEFAULT 0
✓ notes TEXT
✓ course_id REFERENCES courses(id)
✓ student_id REFERENCES students(id)
✓ UNIQUE (course_id, student_id)
✓ INDEX ON (course_id)

Table course_seats
✓ code TEXT
✓ title TEXT
✓ max_seats INTEGER
✓ enrolled (no type)
✓ seats_left (no type)

+---------+----------------------+-----------+----------+------------+
| code    | title                | max_seats | enrolled | seats_left |
+---------+----------------------+-----------+----------+------------+
| ART140  | Drawing Fundamentals |        20 |        0 |         20 |
| BIO305  | Genetics             |        25 |        4 |         21 |
| CS230   | Databases            |        35 |       11 |         24 |
| CS310   | Operating Systems    |        30 |        4 |         26 |
| ENG250  | Modern Literature    |        30 |        4 |         26 |
| CS350   | Software Engineering |        30 |        2 |         28 |
| CS201   | Data Structures      |        40 |        6 |         34 |
| BUS320  | Financial Accounting |        40 |        2 |         38 |
| MATH220 | Linear Algebra       |        45 |        6 |         39 |
| ENG101  | Academic Writing     |        50 |       10 |         40 |
+---------+----------------------+-----------+----------+------------+
(showing 10 of 14 rows)

Hints4 available

Topics:

Views & Indexes

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.