ZAK's SQL Lab
A real database in your browser — ZAK Academy: students, teachers, classes, enrolments and a library, in third normal form. Type SQL exactly as you would in the exam, run it, and see the rows. Primary keys, foreign keys and data types are enforced, so the errors you get are the ones the examiner would give. See the Databases notes for the theory.
Example library
43 queries — every clause and statement the syllabus names, plus exam-style tasks. Press Examples to search them.
SELECT & FROM 4
Choosing columns, *, DISTINCT, aliases and calculated columns
WHERE 6
Comparisons, AND / OR / NOT, LIKE with wildcards, IN, BETWEEN, IS NULL
ORDER BY 3
Ascending, descending, several keys, LIMIT
COUNT, SUM, AVG, MIN, MAX 4
Summarising a whole table or a filtered set
INNER JOIN 5
Linking tables on primary and foreign keys — two, three and four tables
GROUP BY & HAVING 4
One summary row per group, filtered with HAVING
INSERT, UPDATE, DELETE 5
Changing data — and what referential integrity stops you doing
CREATE & ALTER TABLE 5
DDL: data types, PRIMARY KEY, FOREIGN KEY, ALTER TABLE, DROP TABLE
Exam-style questions 7
The kind of query 2210 Paper 2 and 9618 Paper 1 ask you to write
What SQL is supported?
- Queries (DML): SELECT (columns, *, DISTINCT, AS aliases, arithmetic, CASE … END), FROM with INNER JOIN … ON (as many tables as you like), the comma-join form, LEFT JOIN, JOIN … USING; WHERE with = <> < <= > >= AND OR NOT LIKE IN BETWEEN IS NULL and sub-queries (IN (SELECT …), = (SELECT …), EXISTS); GROUP BY, HAVING, ORDER BY … ASC / DESC, LIMIT / OFFSET, UNION; COUNT, SUM, AVG, MIN, MAX plus UPPER, LOWER, LENGTH, LEFT, RIGHT, SUBSTRING, CONCAT, ROUND, ABS, MOD, YEAR, MONTH, DAY, DATEDIFF, COALESCE, CAST.
- Changing data: INSERT INTO … VALUES (…), (…) and INSERT … SELECT, UPDATE … SET … WHERE, DELETE FROM … WHERE, TRUNCATE.
- Defining data (DDL): CREATE DATABASE, CREATE TABLE with INTEGER, REAL / DECIMAL(p,s), VARCHAR(n) / CHAR(n) / TEXT, DATE, BOOLEAN, NOT NULL, UNIQUE, DEFAULT, CHECK, AUTO_INCREMENT, PRIMARY KEY (single or composite) and FOREIGN KEY … REFERENCES … ON DELETE / ON UPDATE CASCADE | SET NULL; ALTER TABLE ADD / DROP COLUMN, MODIFY, RENAME, ADD / DROP PRIMARY KEY, ADD FOREIGN KEY, ADD UNIQUE; DROP TABLE; SHOW TABLES; DESCRIBE.
- Checked before it runs, like a real DBMS: every keyword, table and column name is resolved first — a misspelt word (FORM, WERE, DECS, CONT, INTEGR) gets a “did you mean”, two words run together (ORDERBY) are spotted, a missing comma or a half-written condition (
Town = 'A' OR 'B') is refused, clauses in the wrong order are explained, and every value is type-checked against its column (a number against a text column, a quoted number, an impossible date, NULL with =). Aggregates, GROUP BY and joins are checked (an aggregate in WHERE, an ungrouped column, an ON that compares a table with itself or links non-key columns, a comma join with nothing linking the tables). - Enforced like a real DBMS: duplicate primary keys, NULL in a key or NOT NULL column, UNIQUE, CHECK, a foreign key that points nowhere, a DELETE / UPDATE that would orphan child rows (or cascades if the key says so), text in a number column, over-long VARCHARs and bad dates are all refused with an explanation.
- Check notes: a statement that runs but is probably not what you meant gets an amber note — DELETE / UPDATE without WHERE, LIKE without a wildcard (or with * and ?), BETWEEN the wrong way round, AND mixed with OR without brackets, a condition that can never be true, and a query that returned no rows is explained (“no Town equals 'Karrachi' — did you mean 'Karachi'?”).
- Conventions: text in single quotes (double quotes also accepted); dates as 'YYYY-MM-DD' or 'DD/MM/YYYY'; comparisons and LIKE ignore case (as in MySQL); comments start with
--; end each statement with;. When a script has several statements, the ones before an error are kept and the ones after it are not run (autocommit). - Not included (not in the syllabus): views, transactions, stored procedures, RIGHT / FULL joins.