SQL Essentials for Fundamentals

The standard relational language

SQL is the declarative language of relational databases: you state what you want and the optimiser decides how. It has several parts. DDL (data definition) creates and changes schema objects: CREATE TABLE, ALTER TABLE, DROP, constraints and indexes. DML (data manipulation) reads and changes data: SELECT, INSERT, UPDATE, DELETE. DCL controls access: GRANT, REVOKE. TCL controls transactions: BEGIN, COMMIT, ROLLBACK, SAVEPOINT. A SELECT is evaluated logically in this order: FROM (and joins), WHERE (filter rows), GROUP BY, HAVING (filter groups), SELECT (compute columns), ORDER BY, then LIMIT/OFFSET, which explains why a column alias defined in SELECT cannot be used in WHERE. Joins come in INNER, LEFT, RIGHT and FULL OUTER forms. NULL means unknown, so comparisons with NULL yield UNKNOWN and you must use IS NULL. Subqueries, EXISTS and common table expressions (WITH) build complex queries from simple pieces.

Core SQL on the university schema

Joins, grouping, HAVING and a correlated subquery.

-- average score per department, only departments with more than 50 enrolments
SELECT s.dept, ROUND(AVG(m.score), 1) AS avg_score, COUNT(*) AS enrolments
FROM students s
JOIN marks m ON m.roll_no = s.roll_no
GROUP BY s.dept
HAVING COUNT(*) > 50
ORDER BY avg_score DESC;

-- students with no marks recorded yet (LEFT JOIN + IS NULL)
SELECT s.roll_no, s.name
FROM students s
LEFT JOIN marks m ON m.roll_no = s.roll_no
WHERE m.roll_no IS NULL;

-- students scoring above their department average (correlated subquery)
SELECT s.name, m.subject, m.score
FROM students s JOIN marks m ON m.roll_no = s.roll_no
WHERE m.score > (SELECT AVG(m2.score) FROM marks m2
                 JOIN students s2 ON s2.roll_no = m2.roll_no
                 WHERE s2.dept = s.dept);

WHERE filters rows, HAVING filters groups

A condition on an aggregate such as COUNT(*) > 50 must go in HAVING, because WHERE runs before grouping. Conditions on plain columns belong in WHERE, where they reduce work earlier.

Quick check: Why can a column alias defined in SELECT not be used in the WHERE clause?

• Aliases are not allowed in SQL
• WHERE only accepts numbers
• WHERE is evaluated logically before SELECT computes the aliased columns
• Aliases are only valid in GROUP BY