Computer Programming web Web programming Tips



By Sergey Skudaev


🚀 Welcome to Advanced SQL Practice

Congratulations on mastering the basics! This page is connected to a richer, more complex database designed to challenge you with **intermediate and advanced SELECT queries**.

The core of our **Computer School Database Structure** remains—we still have teachers, students, courses, and linking tables like student_course and schedule. However, we have added **new fields** and **new challenges** that will require you to go beyond simple JOINs and WHERE clauses.


1. Enhanced Data & Date Functions

The most significant updates are the new columns, which open the door to powerful **SQL functions**:

2. Complex Filtering and Aggregation

The updated tables support more detailed analysis, requiring the use of **aggregate functions** (AVG, SUM, MIN, MAX, COUNT) combined with advanced grouping and filtering:

3. Mastering Advanced Joins and Subqueries

For many of the new complex questions, you will need to utilize powerful SQL techniques:


Practice Tips

As before, you have a dropdown of advanced pre-written queries and a text area for typing your own. Start by using SELECT * FROM [table name] to inspect the new data fields. When you encounter a complex problem, remember to break it down:

Good luck, and enjoy continuing your journey with SQL!

Select a query



Executing:

SELECT firstname, lastname FROM students ORDER BY lastname
firstnamelastname
JuliaBarklay
CindyBrown
RyanBrown
JohnClifford
AlisonCremette
AronFalcons
JamesFolkner
MichaelHolden
MollyJames
MichaelJohnson
HollyMichaels
AnnWilliams

No record found.


🤯 Bonus: 3 Really Hard SQL Queries

These five queries are designed to test your mastery of advanced SQL concepts, including **Window Functions (like NTILE and DENSE_RANK)**, **Complex Subqueries**, and **Self-Joins**.

1. Find the Teacher Who Taught the Course With the Highest Standard Deviation in Scores

Uses the STDDEV() aggregate function and nested subqueries to find the course with the highest score variability, then identifies the teacher.

SELECT
    t.firstname,
    t.lastname,
    c.coursename,
    CourseStats.ScoreDeviation
FROM teachers t
JOIN schedule s ON t.teacherid = s.teacherid
JOIN courses c ON s.courseid = c.courseid
JOIN (
    -- Subquery: Finds the course with the maximum standard deviation in scores
    SELECT
        courseid,
        STDDEV(score) AS ScoreDeviation
    FROM student_course
    GROUP BY courseid
    ORDER BY ScoreDeviation DESC
    LIMIT 1 
) CourseStats ON c.courseid = CourseStats.courseid;

2. List Students Whose Total Paid Amount Is Less Than the Total Cost of Courses Enrolled

Requires two separate subqueries to aggregate paid amounts versus cost, followed by a comparison in the main WHERE clause.

SELECT
    st.lastname,
    st.firstname,
    TotalPaid.SumPaid,
    TotalCost.SumCost
FROM students st
JOIN (
    -- Subquery 1: Calculates total paid amount by student
    SELECT
        studentid,
        SUM(CASE WHEN paid = 'Y' THEN (SELECT cost FROM courses WHERE courseid = sc.courseid) ELSE 0 END) AS SumPaid
    FROM student_course sc
    GROUP BY studentid
) TotalPaid ON st.studentid = TotalPaid.studentid
JOIN (
    -- Subquery 2: Calculates total *cost* of all enrolled courses
    SELECT
        sc.studentid,
        SUM(c.cost) AS SumCost
    FROM student_course sc
    JOIN courses c ON sc.courseid = c.courseid
    GROUP BY sc.studentid
) TotalCost ON st.studentid = TotalCost.studentid
WHERE TotalPaid.SumPaid < TotalCost.SumCost;

3. Self-Join: Find Pairs of Teachers Who Have the Same Car Make and Were Hired in the Same Year

Uses a self-join on the teachers table and joins to the cars table to find matching attributes.

SELECT
    t1.lastname AS Teacher1_LastName,
    t1.hiredate AS Teacher1_HireDate,
    t2.lastname AS Teacher2_LastName,
    t2.hiredate AS Teacher2_HireDate,
    car1.cmake AS Shared_Car_Make
FROM teachers t1
JOIN teachers t2 ON t1.teacherid < t2.teacherid 
JOIN cars car1 ON t1.teacherid = car1.teacherid
JOIN cars car2 ON t2.teacherid = car2.teacherid
WHERE
    car1.cmake = car2.cmake AND
    YEAR(t1.hiredate) = YEAR(t2.hiredate);