Part 4 — SQL Queries

CLO6 · ~20 marks. Eight sub-questions on the same university schema from Part 3. Write clean, runnable SQL — right keys, right joins, right filters.

Q7(a)smallCreate the student table and insert two records. (1)

CREATE TABLE student (
  student_id  VARCHAR(10) PRIMARY KEY,
  name        VARCHAR(50) NOT NULL,
  program     VARCHAR(30)
);

INSERT INTO student VALUES ('S001', 'Rahim', 'CSE');
INSERT INTO student VALUES ('S002', 'Karima', 'EEE');

Q7(b)Create the enrolls table. (2)

CREATE TABLE enrolls (
  student_id  VARCHAR(10),
  courseno    VARCHAR(10),
  secno       INT,
  semester    VARCHAR(10),
  year        INT,
  grade       VARCHAR(2),
  PRIMARY KEY (student_id, courseno, secno, semester, year),
  FOREIGN KEY (student_id) REFERENCES student(student_id),
  FOREIGN KEY (courseno, secno, year, semester)
      REFERENCES course_offering(courseno, secno, year, semester)
);

Why this shape: a junction table for the M:N enrolment — composite PK over all key columns, one FK per parent entity, and grade as the descriptive attribute.

Q7(c)Add column ‘booklist’ to course; rename ‘room’ → ‘classroom’ in course-offering. (2)

ALTER TABLE course ADD booklist VARCHAR(255);

ALTER TABLE course_offering RENAME COLUMN room TO classroom;

Both are DDL. ALTER … ADD and ALTER … RENAME COLUMN change structure. Exact syntax varies by DBMS but this is the standard form.

Q7(d)Raise salary 5% for professors earning > 10000, and 10% for everyone else. Use CASE. (3)

UPDATE instructor
SET salary = CASE
  WHEN salary > 10000 AND designation = 'professor'
       THEN salary * 1.05
  ELSE salary * 1.10
END;

Order matters. Put the specific condition (professor & > 10000) first — CASE takes the first matching WHEN, and ELSE sweeps up everyone else at 10%.

Q7(e)List students (id, name, secno, semester) enrolled in CSE303 in Spring 2026, sorted by student id ascending. (3)

SELECT s.student_id, s.name, e.secno, e.semester
FROM   student s
JOIN   enrolls e ON s.student_id = e.student_id
WHERE  e.courseno = 'CSE303'
  AND  e.semester = 'Spring'
  AND  e.year     = 2026
ORDER BY s.student_id ASC;

Q7(f)bigList courses + sections with more than 30 students in Spring 2026, ordered by course & section no. (4)

SELECT courseno, secno, COUNT(*) AS num_students
FROM   enrolls
WHERE  semester = 'Spring' AND year = 2026
GROUP BY courseno, secno
HAVING COUNT(*) > 30
ORDER BY courseno, secno;

WHERE vs HAVING — the #1 error here. WHERE filters rows before grouping (semester/year); HAVING filters groups after aggregation (count > 30). Never swap them.

Q7(g)Find instructors (name, id) who taught in Autumn 2025 but NOT Summer 2025. Use a set operation. (3)

(SELECT i.instructor_id, i.name
 FROM instructor i
 JOIN teaches t ON i.instructor_id = t.instructor_id
 WHERE t.semester = 'Autumn' AND t.year = 2025)
EXCEPT
(SELECT i.instructor_id, i.name
 FROM instructor i
 JOIN teaches t ON i.instructor_id = t.instructor_id
 WHERE t.semester = 'Summer' AND t.year = 2025);

“but not” = set difference. EXCEPT (some DBMS use MINUS) is the relational-algebra − operator: “A but not B” → A EXCEPT B.

MySQL note: older MySQL lacks EXCEPT. There, use an anti-join:

SELECT DISTINCT i.instructor_id, i.name
FROM instructor i
JOIN teaches a ON a.instructor_id = i.instructor_id
             AND a.semester = 'Autumn' AND a.year = 2025
WHERE NOT EXISTS (
  SELECT 1 FROM teaches s
  WHERE s.instructor_id = i.instructor_id
    AND s.semester = 'Summer' AND s.year = 2025);

Q7(h)List every program with its number of admitted students. (2)

SELECT program, COUNT(*) AS num_students
FROM   student
GROUP BY program;

Rule of thumb: “per / for each X” → GROUP BY X. Anything in SELECT that isn't aggregated must appear in GROUP BY.

SQL clause order — say it out loud

SELECT → FROM → JOIN → WHERE → GROUP BY → HAVING → ORDER BY

Want to run SQL on live seeded tables? Open the SQL Playground →

Back to the Exam Pattern & Cheat Sheet →