SQL: The Practical Side of DBMS

By the end of this lesson you will be able to sort SQL statements into DDL, DML, DCL and TCL, translate a SELECT query into relational algebra and back, explain the logical order in which a query's clauses run, and predict how NULL behaves in conditions. This lesson is the bridge between the theory in this course and the hands-on SQL tutorial.

SQL in one paragraph

SQL (Structured Query Language) is the standard language of relational databases. It grew out of IBM's System R research project in the 1970s, became an ANSI standard in 1986 and an ISO standard in 1987, and has been revised many times since. MySQL, PostgreSQL, SQLite, Oracle and SQL Server all speak SQL, each with its own extra features (a dialect). The core you learn here works in all of them.

The four families of SQL statements

Some books put SELECT in a fifth family, DQL (Data Query Language). Either answer is accepted if you explain it.

Here is one statement from each family, in MySQL syntax. The DCL statement needs a MySQL server with users, so it does not run in the browser:

CREATE TABLE notices (notice_id INTEGER PRIMARY KEY, title VARCHAR(80));   -- DDL
INSERT INTO notices VALUES (1, 'Mid-term exams start on 10 March');        -- DML
GRANT SELECT ON college.notices TO 'student_portal'@'localhost';           -- DCL
COMMIT;                                                                     -- TCL

From relational algebra to SELECT

A basic SELECT query is relational algebra in disguise:

SELECT DISTINCT a1, a2        π a1, a2
FROM R1, R2                   (R1 × R2)
WHERE condition               σ condition

SELECT a1, a2 FROM R1, R2 WHERE P     ≈     π a1, a2 ( σ P ( R1 × R2 ) )

The "≈" matters. SQL works with bags (multisets), which may contain duplicate rows. Relational algebra works with sets. Without DISTINCT, SQL keeps duplicates. This is a deliberate choice: removing duplicates needs sorting or hashing, which costs time, and duplicates are often what you want (for example, when you SUM prices).

Step 1: see bag semantics. Count cities with and without DISTINCT:

SELECT COUNT(city) AS all_rows, COUNT(DISTINCT city) AS distinct_cities
FROM customers;

all_rows | distinct_cities
---------+----------------
12       | 11
(1 row)

You should see 12 rows but only 11 different cities, because Rohtak appears twice.

The logical order of a query

You write a query in the order SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT. The DBMS thinks about it in a different order. Knowing this order explains many errors.

1. FROM and JOIN: build the combined rows (× and ⋈).
2. WHERE: keep rows that pass the condition (σ).
3. GROUP BY: put rows into groups.
4. HAVING: keep groups that pass the condition.
5. SELECT: compute the output columns (π) and aliases.
6. DISTINCT: remove duplicate output rows.
7. ORDER BY: sort.
8. LIMIT / OFFSET: cut the result.

This is the logical order. The optimiser may run things in a different physical order, as long as the answer is the same.

Step 2: follow one query through the steps. Which product categories earned more than ₹10,000 from delivered orders?

SELECT p.category, SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id
WHERE o.status = 'delivered'
GROUP BY p.category
HAVING SUM(oi.quantity * oi.unit_price) > 10000
ORDER BY revenue DESC;

category    | revenue
------------+--------
Displays    | 47997
Accessories | 14191
Audio       | 11498
(3 rows)

You should see three categories. FROM joined three tables, WHERE dropped pending, shipped and cancelled orders, GROUP BY made one group per category, HAVING dropped small groups, and ORDER BY sorted them.

Because WHERE runs before SELECT, you cannot use the alias revenue inside WHERE. And because WHERE runs before grouping, conditions on SUM(...) must go in HAVING.

NULL and three-valued logic

NULL means "unknown" or "not applicable". It is not zero and not an empty string. Any comparison with NULL gives a third truth value, UNKNOWN, so SQL uses three-valued logic: TRUE, FALSE and UNKNOWN.

NOT UNKNOWN is still UNKNOWN. A WHERE clause keeps a row only when the condition is TRUE; FALSE and UNKNOWN rows are both dropped.

Step 3: see the trap. Two customers have no email. Who has an email that is not Aarav's?

SELECT
  (SELECT COUNT(*) FROM customers) AS total,
  (SELECT COUNT(*) FROM customers WHERE email <> 'aarav.sharma@example.com') AS not_aarav,
  (SELECT COUNT(*) FROM customers WHERE email IS NULL) AS no_email;

total | not_aarav | no_email
------+-----------+---------
12    | 9         | 2
(1 row)

You should see 12 customers, but only 9 pass the "not Aarav's email" test, not 11. For the 2 customers with NULL email, NULL <> '...' is UNKNOWN, so they are dropped. Use IS NULL and IS NOT NULL to test for NULL; = NULL is never TRUE. The NULL Values lesson covers this in depth.

Where to learn each part of SQL

This course uses SQL to illustrate theory. For the full syntax, these lessons go deeper:

PostgreSQL users can follow the PostgreSQL tutorial, which runs real PostgreSQL in the browser.

Common mistakes

• Using an alias from SELECT in WHERE. WHERE revenue > 10000 fails, because WHERE runs before SELECT. Repeat the expression or use HAVING.
• Writing = NULL. It is never true. Write IS NULL.
• Assuming SQL removes duplicates. Only DISTINCT, UNION (without ALL), INTERSECT and EXCEPT do.
• Calling TRUNCATE DML. It removes all rows but is classed as DDL, and in MySQL it cannot be rolled back.

Key points

• SQL statements fall into DDL (schema), DML (rows), DCL (permissions) and TCL (transactions).
• SELECT a FROM R WHERE P corresponds to π a (σ P (R)), but SQL keeps duplicates unless you write DISTINCT.
• The logical order is FROM, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, LIMIT.
• NULL comparisons give UNKNOWN, and WHERE keeps only TRUE rows.
• Test for missing values with IS NULL and IS NOT NULL.

π first_name, city ( σ status = 'pending' ( customers ⋈ orders ) )

SELECT DISTINCT c.first_name, c.city                -- step 5 and step 6
FROM customers c JOIN orders o                      -- step 1
  ON o.customer_id = c.customer_id
WHERE o.status = 'pending'                          -- step 2
ORDER BY c.first_name;                              -- step 7