Web programming TipsBy Sergey Skudaev
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.
The most significant updates are the new columns, which open the door to powerful **SQL functions**:
students.dob (Date of Birth): This field replaces the static age field from the first page. You can no longer just filter by age. You must use **Date and Time Functions** (like DATEDIFF() or TIMESTAMPDIFF()) to calculate the student's age relative to today's date.
Challenge: Find the exact age of the youngest student.
schedule.enddate: The schedule table now has both a startdate and an enddate. This allows you to find courses that were **active** on a specific date or those that have a duration longer than a certain number of weeks.
Challenge: Use the BETWEEN operator and date functions to list courses running in the month of December.
The updated tables support more detailed analysis, requiring the use of **aggregate functions** (AVG, SUM, MIN, MAX, COUNT) combined with advanced grouping and filtering:
student_course.score: This field replaces the simple grade. Scores allow for sophisticated statistical analysis. You will need to master the **GROUP BY** clause with **HAVING** to filter the results of your aggregations.
Challenge: Find all courses that have an average student score (AVG(score)) lower than 75.
students.city, students.state, students.zip: These new location fields allow for filtering using **string functions** (LIKE, SUBSTRING, LENGTH) and grouping students geographically.
For many of the new complex questions, you will need to utilize powerful SQL techniques:
SELECT statement nested inside another query. They are essential for answering questions that depend on an intermediate calculation.
Challenge: List the students whose score in the 'Java' class is higher than the class average. You must calculate the average first using a subquery.
INNER JOIN before, **OUTER JOINs** are critical when you need to see all records from one table, even if they have no match in the other.
Challenge: List all teachers, including those who have not yet been assigned a car (where a record would be missing in the cars table).
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:
SELECT): What columns do you need?FROM): Where does that data live?JOIN): How do you connect the tables?WHERE): What initial conditions must be met?AVG/GROUP BY/HAVING): What aggregations or final filters are needed?Good luck, and enjoy continuing your journey with SQL!
SELECT firstname, lastname FROM students ORDER BY lastname
| firstname | lastname |
|---|---|
| Julia | Barklay |
| Cindy | Brown |
| Ryan | Brown |
| John | Clifford |
| Alison | Cremette |
| Aron | Falcons |
| James | Folkner |
| Michael | Holden |
| Molly | James |
| Michael | Johnson |
| Holly | Michaels |
| Ann | Williams |
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**.
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;
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;
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);