Lecture 44: Final Project – Advanced Queries

This lecture covers advanced queries with aggregate functions on the student enrollment system.

Now that we’re comfortable with basic joins, let’s use our knowledge of aggregate functions and `GROUP BY` to extract more complex insights from our Student Enrollment System.

Query 1: Count Students per Major

How many students are in each major? This is a classic `COUNT` with `GROUP BY` query.

SELECT
    Major,
    COUNT(StudentID) AS NumberOfStudents
FROM Students
GROUP BY Major;

Query 2: Count Courses per Instructor

How many courses does each instructor teach? We need to join `Instructors` and `Courses` and then group by instructor name.

SELECT
    i.InstructorName,
    COUNT(c.CourseID) AS NumberOfCoursesTaught
FROM Instructors AS i
LEFT JOIN Courses AS c ON i.InstructorID = c.Instructor_ID
GROUP BY i.InstructorName;

Notice the `LEFT JOIN` here. This ensures that even instructors who aren’t currently teaching any courses will still appear in the list with a count of 0.

Query 3: Calculate Average Grade per Course

This is a bit trickier because our grades are letters (A, B, C). To calculate an average, we need to convert them to numbers. We can do this using a `CASE` statement (which we haven’t covered yet, but it’s a good preview of more advanced SQL functions).

SELECT
    c.CourseName,
    AVG(CASE e.Grade
            WHEN 'A' THEN 4.0
            WHEN 'B' THEN 3.0
            WHEN 'C' THEN 2.0
            WHEN 'D' THEN 1.0
            WHEN 'F' THEN 0.0
            ELSE NULL -- Ignore 'W' (Withdrawn) grades
        END) AS AverageGPA
FROM Courses AS c
INNER JOIN Enrollments AS e ON c.CourseID = e.Course_ID
WHERE e.Grade IS NOT NULL -- Only consider graded enrollments
GROUP BY c.CourseName;

This query demonstrates how powerful SQL can be when you combine different clauses and functions.

Query 4: Find Students with No Enrollments

Similar to finding authors with no posts, we can find students who haven’t enrolled in any courses yet using a `LEFT JOIN` and checking for `NULL`.

SELECT
    s.StudentName
FROM Students AS s
LEFT JOIN Enrollments AS e ON s.StudentID = e.Student_ID
WHERE e.EnrollmentID IS NULL;

In the Next Lecture…

We will explore more advanced SQL features, including the `HAVING` clause (which filters `GROUP BY` results) and the `CASE` statement in more detail.

Lecture 43: Final Project – Basic Queries