SELECT, filtering, joins, grouping and subqueries

SELECT execution model

SQL is declarative: specify the result, not a manual row-reading loop. A useful logical order is FROM/JOIN, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT. The optimizer may execute differently while preserving semantics. WHERE filters rows before grouping; HAVING filters groups.

Filtering and ordering

USE academy;
SELECT student_id, full_name, city FROM students;
SELECT * FROM students WHERE city = 'Pune';
SELECT * FROM students WHERE city IS NULL;
SELECT * FROM students WHERE city IN ('Pune','Nashik');
SELECT * FROM students WHERE full_name LIKE 'A%';
SELECT * FROM courses WHERE fee BETWEEN 5000 AND 15000;
SELECT DISTINCT city FROM students;
SELECT * FROM students ORDER BY joined_on DESC, student_id DESC LIMIT 2;

Use IS NULL, not = NULL. BETWEEN includes both endpoints. % matches any-length text in LIKE and _ matches one character; case behaviour depends on collation. Always order before LIMIT when a predictable subset matters. For timestamp date ranges prefer a half-open interval, such as created_at >= '2026-01-01' AND created_at < '2026-02-01'.

Joins and aggregates

SELECT s.full_name, c.title, e.score
FROM enrollments e
JOIN students s ON s.student_id=e.student_id
JOIN courses c ON c.course_id=e.course_id;

SELECT s.student_id,s.full_name,COUNT(e.course_id) AS course_count
FROM students s LEFT JOIN enrollments e ON e.student_id=s.student_id
GROUP BY s.student_id,s.full_name
ORDER BY s.student_id;

SELECT c.course_id,c.title,AVG(e.score) AS average_score
FROM courses c JOIN enrollments e ON e.course_id=c.course_id
GROUP BY c.course_id,c.title HAVING AVG(e.score)>80;

The left join includes Meera with zero enrollments. COUNT(*) would count the unmatched left-join output row; COUNT(e.course_id) ignores NULL. Joining two one-to-many relations can multiply rows and inflate totals; establish the grain before aggregating.

Subqueries, CASE and windows

SELECT title, fee FROM courses WHERE fee > (SELECT AVG(fee) FROM courses);
SELECT full_name, COALESCE(city,'Not supplied') AS city FROM students;
SELECT student_id,score,
 CASE WHEN score>=80 THEN 'Strong' ELSE 'Practice' END AS feedback
FROM enrollments;
SELECT student_id,course_id,score,
 DENSE_RANK() OVER (PARTITION BY course_id ORDER BY score DESC) AS rank_in_course
FROM enrollments;

Window functions preserve individual rows. GROUP BY reduces rows. UNION removes duplicate result rows; UNION ALL preserves them. EXISTS tests whether a matching row exists without requiring row multiplication.

Assignment and expected checks

Return students with no enrollments using NOT EXISTS. Find each course's student count including zero. Explain why a WHERE condition on the right table can accidentally turn a left join into an inner-join-like result. Confirm Meera still appears when your requirement says every student.

Official reference

MySQL SELECT

Interview and revision check

Why count a joined key instead of COUNT(*) for zero enrollments?

A LEFT JOIN retains an unmatched student row. COUNT(*) counts that row; COUNT of the nullable enrollment key ignores its NULL.