Our Journey Today

• The Big Picture: How SQL really works.
• The Playground: Introducing our sample database.
• The Logical Walkthrough: Building a query step-by-step.
• Practice Makes Perfect: Let's write some queries!

How We Write vs. How SQL Thinks

Understanding Logical Processing Order is the Key

The "Baking a Cake" Analogy

Follow the Recipe for a Perfect Result

• 1. FROM: Get all your ingredients (tables).
• 2. WHERE: Remove spoiled ingredients (filter rows).
• 3. GROUP BY: Put ingredients into mixing bowls (group rows).
• 4. HAVING: Check each bowl; discard some (filter groups).
• 5. SELECT: Measure what you need from the final bowls (choose columns).
• 6. ORDER BY: Arrange finished cakes on a platter (sort results).

The University Database

Our Playground for Today's Queries

Students

Enrollments

Step 1: FROM

Where does the data come from?

This is the starting point. It specifies the table to query. The database engine virtually loads this entire table into memory.

FROM Students

Step 2: WHERE

Filtering individual records

It keeps only the rows that match a condition. This happens before any grouping.

FROM Students
WHERE Major = 'Computer Science'

The database now only considers Sarah's and Maria's rows for the next steps.

Step 3: GROUP BY

Partitioning into groups

It collapses multiple rows into a single summary row based on a column. This is essential for aggregate functions like COUNT() or AVG().

FROM Students
GROUP BY Major

The database now sees three "buckets": 'Computer Science', 'History', and 'Business'.

Step 4: HAVING

Filtering the groups

It filters the groups created by GROUP BY. You must use HAVING to filter on an aggregate result.

FROM Students
GROUP BY Major
HAVING COUNT(StudentID) > 1

Only the 'Computer Science' group remains, as it's the only one with more than one student.

WHERE vs. HAVING

A Critical Distinction

Step 5: SELECT

Choosing what to output

This specifies the final columns to display. It's where you use aggregate functions and can create aliases (AS).

SELECT
  Major,
  COUNT(StudentID) AS NumberOfStudents
FROM Students
GROUP BY Major
HAVING COUNT(StudentID) > 1

Step 6: ORDER BY

Sorting the final output

This is the very last step. It sorts the final result set. You can use aliases defined in the SELECT clause here.

SELECT Major, AVG(GPA) AS AverageGPA
FROM Students
GROUP BY Major
ORDER BY AverageGPA DESC;

Let's Practice!

Problem 1: Medium

Task: How many students are in each major? List the major and the count of students, aliased as NumberOfStudents.

Solution 1

Counting students in each major

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

Let's Practice!

Problem 2: Hard

Task: List the majors that have an average GPA greater than 3.4. Show the major and its average GPA, sorted with the highest average GPA first.

Solution 2

Finding top-performing majors

SELECT
  Major,
  AVG(GPA) AS AverageGPA
FROM Students
GROUP BY Major
HAVING AVG(GPA) > 3.4
ORDER BY AverageGPA DESC;

Recap Complete!

Questions?

You now understand the logical journey of a SQL query. Keep practicing!