Week 1 — Advanced SQL: Joins, Subqueries & Set Operations¶

-- Core university schema used throughout ITEC 445
CREATE TABLE departments (
    dept_id   INT          PRIMARY KEY AUTO_INCREMENT,
    dept_name VARCHAR(100) NOT NULL,
    building  VARCHAR(50),
    budget    DECIMAL(12,2)
);

CREATE TABLE instructors (
    instructor_id INT          PRIMARY KEY AUTO_INCREMENT,
    first_name    VARCHAR(50)  NOT NULL,
    last_name     VARCHAR(50)  NOT NULL,
    email         VARCHAR(100) UNIQUE,
    dept_id       INT,
    hire_date     DATE,
    salary        DECIMAL(10,2),
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

CREATE TABLE courses (
    course_id   INT          PRIMARY KEY AUTO_INCREMENT,
    course_code VARCHAR(10)  NOT NULL UNIQUE,  -- e.g. 'ITEC445'
    title       VARCHAR(150) NOT NULL,
    credits     TINYINT      NOT NULL DEFAULT 3,
    dept_id     INT,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

CREATE TABLE students (
    student_id INT          PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50)  NOT NULL,
    last_name  VARCHAR(50)  NOT NULL,
    email      VARCHAR(100) UNIQUE,
    gpa        DECIMAL(3,2),
    dept_id    INT,                            -- major department
    enroll_year YEAR,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

CREATE TABLE enrollments (
    enrollment_id INT     PRIMARY KEY AUTO_INCREMENT,
    student_id    INT     NOT NULL,
    course_id     INT     NOT NULL,
    instructor_id INT,
    semester      CHAR(6) NOT NULL,            -- e.g. 'F2025'
    grade         CHAR(2),                     -- A, A-, B+, …, F, W, NULL=in-progress
    UNIQUE KEY uq_enroll (student_id, course_id, semester),
    FOREIGN KEY (student_id)    REFERENCES students(student_id),
    FOREIGN KEY (course_id)     REFERENCES courses(course_id),
    FOREIGN KEY (instructor_id) REFERENCES instructors(instructor_id)
);

-- List every student and the courses they are currently enrolled in
SELECT  s.first_name,
        s.last_name,
        c.course_code,
        c.title,
        e.semester
FROM    students    s
INNER JOIN enrollments e ON e.student_id = s.student_id
INNER JOIN courses     c ON c.course_id  = e.course_id
WHERE   e.grade IS NULL          -- currently in progress
ORDER BY s.last_name, s.first_name;

-- Find ALL students, including those not enrolled in anything this semester
SELECT  s.student_id,
        s.first_name,
        s.last_name,
        COUNT(e.enrollment_id) AS courses_enrolled
FROM    students   s
LEFT JOIN enrollments e
       ON e.student_id = s.student_id
      AND e.semester   = 'F2025'        -- filter on the JOIN, not WHERE
GROUP BY s.student_id, s.first_name, s.last_name
ORDER BY courses_enrolled DESC;

-- All courses offered this semester, including those with no students enrolled
SELECT  c.course_code,
        c.title,
        COUNT(e.enrollment_id) AS enrolled_count
FROM    enrollments e
RIGHT JOIN courses c ON c.course_id = e.course_id
                    AND e.semester  = 'F2025'
GROUP BY c.course_id, c.course_code, c.title
ORDER BY enrolled_count;

-- Generate all possible (student, course) combinations for schedule planning
SELECT  s.student_id,
        s.last_name,
        c.course_code
FROM    students s
CROSS JOIN courses c
WHERE   s.dept_id = c.dept_id   -- cross within the same department
ORDER BY s.last_name, c.course_code;

-- Find all instructors in the same department (exclude self-pairing)
SELECT  a.first_name AS instructor_a,
        b.first_name AS instructor_b,
        d.dept_name
FROM    instructors a
INNER JOIN instructors b ON a.dept_id = b.dept_id
                        AND a.instructor_id < b.instructor_id  -- avoid (A,B) and (B,A)
INNER JOIN departments d ON d.dept_id = a.dept_id
ORDER BY d.dept_name, a.last_name;

-- Students who enrolled the same semester as another student in the same course
SELECT  e1.student_id AS student1,
        e2.student_id AS student2,
        e1.course_id,
        e1.semester
FROM    enrollments e1
INNER JOIN enrollments e2
        ON e1.course_id = e2.course_id
       AND e1.semester  = e2.semester
       AND e1.student_id < e2.student_id;

-- Show each student's GPA vs the department average
SELECT  s.first_name,
        s.last_name,
        s.gpa,
        (SELECT AVG(s2.gpa)
         FROM   students s2
         WHERE  s2.dept_id = s.dept_id) AS dept_avg_gpa,
        s.gpa - (SELECT AVG(s2.gpa)
                 FROM   students s2
                 WHERE  s2.dept_id = s.dept_id) AS gpa_delta
FROM    students s
ORDER BY gpa_delta DESC;

-- Find students whose (dept, enroll_year) matches the most common combination
SELECT student_id, first_name, last_name
FROM   students
WHERE  (dept_id, enroll_year) = (
    SELECT dept_id, enroll_year
    FROM   students
    GROUP BY dept_id, enroll_year
    ORDER BY COUNT(*) DESC
    LIMIT 1
);

-- Average enrollment count per department
SELECT  d.dept_name,
        dept_stats.avg_courses
FROM    departments d
INNER JOIN (
    SELECT  s.dept_id,
            AVG(course_count) AS avg_courses
    FROM    students s
    INNER JOIN (
        SELECT  student_id, COUNT(*) AS course_count
        FROM    enrollments
        WHERE   semester = 'F2025'
        GROUP BY student_id
    ) AS ec ON ec.student_id = s.student_id
    GROUP BY s.dept_id
) AS dept_stats ON dept_stats.dept_id = d.dept_id
ORDER BY dept_stats.avg_courses DESC;

-- Students who have a GPA above their department's median
-- (correlated: references s.dept_id from outer query)
SELECT  s.student_id, s.first_name, s.last_name, s.gpa, s.dept_id
FROM    students s
WHERE   s.gpa > (
    SELECT AVG(s2.gpa)
    FROM   students s2
    WHERE  s2.dept_id = s.dept_id   -- correlated reference
);

-- Equivalent rewrite — computes dept averages once
SELECT  s.student_id, s.first_name, s.last_name, s.gpa, s.dept_id
FROM    students s
INNER JOIN (
    SELECT dept_id, AVG(gpa) AS avg_gpa
    FROM   students
    GROUP BY dept_id
) avg ON avg.dept_id = s.dept_id
WHERE s.gpa > avg.avg_gpa;

-- Correct: Students NOT enrolled this semester (NOT EXISTS)
SELECT s.student_id, s.first_name
FROM   students s
WHERE  NOT EXISTS (
    SELECT 1 FROM enrollments e
    WHERE  e.student_id = s.student_id
      AND  e.semester   = 'F2025'
);

-- DANGEROUS: If ANY enrollment has a NULL student_id, returns NO ROWS
SELECT student_id, first_name
FROM   students
WHERE  student_id NOT IN (
    SELECT student_id FROM enrollments WHERE semester = 'F2025'
    -- If student_id is NULLable, this subquery can return NULLs,
    -- making the entire NOT IN return empty
);

-- UNION: All distinct email addresses from students and instructors
SELECT email, 'student'    AS role FROM students
UNION
SELECT email, 'instructor' AS role FROM instructors
ORDER BY email;

-- UNION ALL: Include duplicates (faster — no deduplication step)
SELECT course_id FROM enrollments WHERE semester = 'F2024'
UNION ALL
SELECT course_id FROM enrollments WHERE semester = 'F2025';

-- Give a 5% salary raise to instructors in departments that have a budget > 500,000
UPDATE instructors i
INNER JOIN departments d ON d.dept_id = i.dept_id
SET    i.salary = i.salary * 1.05
WHERE  d.budget > 500000;

-- Reset grade to NULL for students who withdrew (W) from a course
-- where the instructor was subsequently terminated (not in instructors table)
UPDATE enrollments e
LEFT JOIN instructors i ON i.instructor_id = e.instructor_id
SET    e.grade = 'W'
WHERE  i.instructor_id IS NULL
  AND  e.grade IS NULL;

-- Delete enrollment records for students who have graduated (no longer in students table)
DELETE e
FROM   enrollments e
LEFT JOIN students s ON s.student_id = e.student_id
WHERE  s.student_id IS NULL;

-- Delete a student and all their enrollments atomically
-- (assumes FK with ON DELETE CASCADE is not set)
DELETE s, e
FROM   students s
INNER JOIN enrollments e ON e.student_id = s.student_id
WHERE  s.student_id = 1042;

-- Detect students with the same name (potential duplicate entries)
SELECT   first_name, last_name, COUNT(*) AS occurrences
FROM     students
GROUP BY first_name, last_name
HAVING   COUNT(*) > 1
ORDER BY occurrences DESC;

-- Get full details of the duplicates
SELECT  s.*
FROM    students s
INNER JOIN (
    SELECT first_name, last_name
    FROM   students
    GROUP BY first_name, last_name
    HAVING COUNT(*) > 1
) dups ON dups.first_name = s.first_name
       AND dups.last_name  = s.last_name
ORDER BY s.last_name, s.first_name, s.student_id;

-- Find gaps in student_id sequence (e.g., after bulk deletes)
SELECT  a.student_id + 1 AS gap_start,
        MIN(b.student_id) - 1 AS gap_end
FROM    students a
INNER JOIN students b ON b.student_id > a.student_id
GROUP BY a.student_id
HAVING   gap_start < MIN(b.student_id)
ORDER BY gap_start;

-- Top 3 students by GPA in each department
SELECT  dept_name, first_name, last_name, gpa
FROM (
    SELECT  d.dept_name,
            s.first_name,
            s.last_name,
            s.gpa,
            ROW_NUMBER() OVER (
                PARTITION BY s.dept_id
                ORDER BY s.gpa DESC
            ) AS dept_rank
    FROM    students s
    INNER JOIN departments d ON d.dept_id = s.dept_id
    WHERE   s.gpa IS NOT NULL
) ranked
WHERE  dept_rank <= 3
ORDER BY dept_name, dept_rank;