SQL interview question: INSERT and UPDATE in One Transaction
Enroll and Count, Together
Student 1 is joining SQL Mastery (course_id 1). Enrollment touches two tables: a new row in enrollments (enrollment_id 48, semester 'Fall 2024', grade NULL) and the course's enrolled counter in course_enrollment goes up by one. If only one happened, the seat count would lie.
Write a transaction doing both, then COMMIT.
Note: Multiple statements allowed. Runs on a scratch copy and is discarded afterwards.
Tables
enrollments 47 rows
- enrollment_id integer PK
- student_id integer → students
- course_id integer → courses
- semester text
- grade text
| enrollment_id | student_id | course_id | semester | grade |
|---|---|---|---|---|
| 1 | 1 | 1 | Fall 2024 | A |
| 2 | 1 | 3 | Fall 2024 | A |
| 3 | 1 | 5 | Fall 2024 | A- |
course_enrollment 5 rows
- course_id integer PK
- course_name text
- enrolled integer
- completed integer
| course_id | course_name | enrolled | completed |
|---|---|---|---|
| 1 | SQL Mastery | 150 | 120 |
| 2 | Python Basics | 200 | 180 |
| 3 | Data Science | 100 | 65 |