Updating Incomplete Exam Records in SQL
Tech
1
Problem Description
The exam_record table stores user exam attempt records over multiple years. Each record contains the user ID, exam ID, start time, submission time, and score.
Table Schema
| Field | Type | Description |
|---|---|---|
| id | int(11) | Primary key, auto-increment |
| uid | int(11) | User ID |
| exam_id | int(11) | Exam ID |
| start_time | datetime | Exam start timestamp |
| submit_time | datetime | Submission timestamp (NULL if not submitted) |
| score | tinyint(4) | Exam score |
Task
Udpate all incomplete exam records that started before September 1, 2021. For these records:
- Set the submission time to '2099-01-01 00:00:00'
- Set the score to 0
A incomplete record is identified by having a NULL submit_time value.
Example
Sample Data
DROP TABLE IF EXISTS exam_record;
CREATE TABLE IF NOT EXISTS exam_record (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT NOT NULL,
exam_id INT NOT NULL,
start_time DATETIME NOT NULL,
submit_time DATETIME,
score TINYINT
);
INSERT INTO exam_record(user_id, exam_id, start_time, submit_time, score) VALUES
(1001, 9001, '2020-01-02 09:01:01', '2020-01-02 09:21:01', 80),
(1001, 9002, '2021-09-01 09:01:01', '2021-09-01 09:21:01', 90),
(1002, 9001, '2021-08-02 19:01:01', NULL, NULL),
(1002, 9002, '2021-09-05 19:01:01', '2021-09-05 19:40:01', 89),
(1003, 9001, '2021-09-02 12:01:01', NULL, NULL),
(1003, 9002, '2021-09-01 12:01:01', NULL, NULL);
Expected Output
1001|9001|2020-01-02 09:01:01|2020-01-02 09:21:01|80
1001|9002|2021-09-01 09:01:01|2021-09-01 09:21:01|90
1002|9001|2021-08-02 19:01:01|2099-01-01 00:00:00|0
1002|9002|2021-09-05 19:01:01|2021-09-05 19:40:01|89
1003|9001|2021-09-02 12:01:01|None|None
1003|9002|2021-09-01 12:01:01|None|None
Solution
UPDATE exam_record
SET submit_time = '2099-01-01 00:00:00',
score = 0
WHERE start_time < '2021-09-01'
AND submit_time IS NULL;
Explanation
The UPDATE statement modifies records based on two conditions:
- start_time < '2021-09-01': The exam started before September 1, 2021
- submit_time IS NULL: The exam was never submitted (incomplete)
Only the record with user_id 1002 and exam_id 9001 satisfies both conditions, as it started on 2021-08-02 and has no submission time. Records starting on or after 2021-09-01 are not affectde, and records that were already submitted remain unchanged.
