Student Management System: CRUD Worksheet

Student Management System: CRUD Worksheet

A Beginner's Guide to Database Operations

Database Schema Reference

Before starting, review the available tables and columns in the Student Management System:

• instructors: (instructor_id, name, department)
• students: (student_id, name, enrollment_date)
• courses: (course_id, course_title, instructor_id)
• results: (result_id, student_id, course_id, grade)

Part 1: Concept Matching

Match the abstract CRUD concept on the left with its corresponding SQL command on the right.

Part 2: Write the SQL Statements

Scenario 1: CREATE

1. A new student named 'Alex Green' has just enrolled. Add them to the students table (assume enrollment_date is '2026-09-01').

2. The university introduces a new class called 'Introduction to Databases' with a course_id of 'CS102'. The instructor teaching it has an instructor_id of 12. Add this course to the courses table.

Scenario 2: READ

3. The administration wants to see a list of all instructors who work in the 'Computer Science' department. Write the query to view their names and IDs.

4. Find all the grades recorded in the results table for the student whose student_id is 505.

Scenario 3: UPDATE

5. An instructor named 'Dr. Smith' switches departments. Change their department to 'Data Science' in the instructors table.

6. A professor re-graded an assignment. Update the grade to 'A' in the results table for student_id 101 in course_id 'CS102'.

Scenario 4: DELETE

7. A student accidentally registered for the wrong class. Delete the row from the results table where the student_id is 204 and the course_id is 'MATH101'.

Part 3: Code Review & Safety Check

8. Imagine a developer tries to remove a cancelled course using this statement:
   DELETE FROM courses WHERE course_title = 'Advanced Calculus';

If there happen to be three different sections of 'Advanced Calculus' taught by three different instructors in this table, what will happen when this query runs? How could they make it safer?

Answer Key

Part 1: Concept Matching

1: C, 2: A, 3: D, 4: B

Scenario 1: CREATE

1. INSERT INTO students (name, enrollment_date) VALUES ('Alex Green', '2026-09-01');
2. INSERT INTO courses (course_id, course_title, instructor_id) VALUES ('CS102', 'Introduction to Databases', 12);

Scenario 2: READ

3. SELECT instructor_id, name FROM instructors WHERE department = 'Computer Science';
4. SELECT grade FROM results WHERE student_id = 505;

Scenario 3: UPDATE

5. UPDATE instructors SET department = 'Data Science' WHERE name = 'Dr. Smith';
6. UPDATE results SET grade = 'A' WHERE student_id = 101 AND course_id = 'CS102';

Scenario 4: DELETE

7. DELETE FROM results WHERE student_id = 204 AND course_id = 'MATH101';

Part 3: Code Review & Safety Check

8. What will happen: The query will delete all three records of 'Advanced Calculus' because they all share the exact same title name.
How to make it safer: Target the unique course_id or unique ID of the specific row instead (e.g., WHERE course_id = 'CALC-03'). Always use unique primary keys when performing updates or deletes.