⚡ SQL Clause Order (CRITICAL!)
SELECT -- What columns to return
FROM -- Which table(s)
WHERE -- Filter rows BEFORE grouping
GROUP BY -- Group rows for aggregation
HAVING -- Filter groups AFTER aggregation
ORDER BY -- Sort results
LIMIT -- Limit number of results
Remember: WHERE before GROUP BY, HAVING after GROUP BY!
🔍 WHERE vs HAVING (Easy to Confuse!)
| Aspect |
WHERE |
HAVING |
| When it runs |
Before GROUP BY |
After GROUP BY |
| What it filters |
Individual rows |
Grouped results |
| Can use aggregates? |
❌ No |
✅ Yes |
| Example |
WHERE gpa > 3.0 |
HAVING COUNT(*) > 5 |
-- WRONG: Can't use aggregate in WHERE
SELECT major, AVG(gpa)
FROM Students
WHERE AVG(gpa) > 3.0 -- ❌ Error!
GROUP BY major;
-- CORRECT: Use HAVING for aggregates
SELECT major, AVG(gpa)
FROM Students
GROUP BY major
HAVING AVG(gpa) > 3.0; -- ✅ Correct!
🔗 JOIN Types (Quick Visual)
| JOIN Type |
What It Returns |
When to Use |
| INNER JOIN |
Only rows with matches in BOTH tables |
When you need matching data |
| LEFT JOIN |
ALL rows from left table + matches from right (NULL if no match) |
When you need all left records |
| RIGHT JOIN |
ALL rows from right table + matches from left |
Rarely used (just swap tables and use LEFT) |
-- Students with enrollments only
SELECT s.name, e.course_id
FROM Students s
INNER JOIN Enrollments e ON s.student_id = e.student_id;
-- All students (even those without enrollments)
SELECT s.name, e.course_id
FROM Students s
LEFT JOIN Enrollments e ON s.student_id = e.student_id;
Finding orphan records: Use LEFT JOIN ... WHERE right_table.id IS NULL
📊 Aggregate Functions
| Function |
What It Does |
Example |
| COUNT(*) |
Counts all rows |
COUNT(*) |
| COUNT(col) |
Counts non-NULL values |
COUNT(email) |
| SUM(col) |
Adds up values |
SUM(price) |
| AVG(col) |
Calculates average |
AVG(gpa) |
| MAX(col) |
Finds maximum |
MAX(salary) |
| MIN(col) |
Finds minimum |
MIN(age) |
Remember: When using aggregates with non-aggregated columns, you MUST use GROUP BY!
🛠️ Useful Operators (Often Forgotten)
| Operator |
Purpose |
Example |
| IN |
Match any value in list |
major IN ('CS', 'Math') |
| BETWEEN |
Range check (inclusive) |
gpa BETWEEN 3.0 AND 4.0 |
| LIKE |
Pattern matching |
email LIKE '%@gmail.com' |
| IS NULL |
Check for NULL |
phone IS NULL |
| IS NOT NULL |
Check for non-NULL |
email IS NOT NULL |
| DISTINCT |
Remove duplicates |
SELECT DISTINCT major |
LIKE wildcards: % = any characters, _ = single character
💡 Common Patterns You'll Need
Pattern 1: Find Orphan Records
-- Students with NO enrollments
SELECT s.name
FROM Students s
LEFT JOIN Enrollments e ON s.student_id = e.student_id
WHERE e.enrollment_id IS NULL;
Pattern 2: Count Relationships
-- Count enrollments per student
SELECT s.name, COUNT(e.enrollment_id) as enrollment_count
FROM Students s
LEFT JOIN Enrollments e ON s.student_id = e.student_id
GROUP BY s.student_id, s.name;
Pattern 3: Top N Records
-- Top 10 students by GPA
SELECT name, gpa
FROM Students
ORDER BY gpa DESC
LIMIT 10;
Pattern 4: Subquery for Comparison
-- Students with above-average GPA
SELECT name, gpa
FROM Students
WHERE gpa > (SELECT AVG(gpa) FROM Students);
📋 Sample Schema (For Reference)
| Table |
Columns |
| Students |
student_id (PK), name, email, major, gpa |
| Courses |
course_id (PK), course_name, credits |
| Enrollments |
enrollment_id (PK), student_id (FK), course_id (FK), grade |
PK = Primary Key (unique identifier) | FK = Foreign Key (references another table)
← Back to SQL Skills Lab