SQL / KNOWLEDGE SYSTEM

Learn SQL.
See the system.

One interactive reference for SQL fundamentals, commands, clauses, joins, functions, subqueries, CTEs, windows, transactions, database design, applications and the history of the language.

Beginner → AdvancedQuery ReferenceDatabase Thinking
SELECT
FROM
WHERE
JOIN
GROUP BY
ORDER BY
SQL • DATA • TABLES • QUERIES • RELATIONS • JOINS • AGGREGATION • TRANSACTIONS • INDEXES • SQL • DATA • TABLES • QUERIES • RELATIONS • JOINS • AGGREGATION • TRANSACTIONS • INDEXES •
01 / ORIGIN

From relational theory to modern SQL.

SQL grew from the relational model and IBM research in the 1970s, then became standardized and widely implemented across relational database systems.

1970

Relational model

E. F. Codd published the relational model that became foundational to modern relational database systems.

1974

SEQUEL

IBM researchers Donald Chamberlin and Raymond Boyce published work on SEQUEL, the predecessor of SQL.

1977–79

Commercial era

Relational Software, later Oracle, developed and commercially shipped a relational SQL implementation.

1983

DB2

IBM shipped DB2, helping establish SQL-based relational databases in enterprise computing.

1986–87

Standardization

ANSI standardized SQL in 1986; ISO followed with an SQL standard in 1987.

1992

SQL-92

The SQL standard was substantially expanded, including richer query and data-definition capabilities.

1999+

Modern SQL

Later revisions added features such as recursive queries, windowing and richer data capabilities.

2023

SQL:2023

SQL:2023 is the current standard referenced by the University of Edinburgh's 2026 database course material.

02 / FOUNDATIONS

Understand the objects before the queries.

SQL becomes easier when tables, keys, relationships, schemas and constraints are treated as a system rather than isolated syntax.

01

Database

A structured collection of data managed by a database system.

02

Table

A relation made of rows and columns.

03

Row

One record or tuple in a table.

04

Column

An attribute describing one property of each record.

05

Primary Key

A column or column set that uniquely identifies rows.

06

Foreign Key

A reference that connects a row to another table.

07

Schema

The logical structure that defines tables, relationships, constraints and other database objects.

08

Constraint

A rule that protects data integrity, such as NOT NULL, UNIQUE or CHECK.

03 / SQL ESSENTIALS

The pieces that make SQL click.

Build a mental model before memorizing syntax. These fundamentals explain how SQL statements are structured and how a relational database interprets them.

01

What is SQL?

SQL (Structured Query Language) is used to define, query, manipulate and control data in relational database systems.

SELECT name FROM employees;
02

Statement vs query

A SQL statement is an instruction sent to the database. A query usually describes a request for data, most commonly a SELECT statement.

SELECT name, salary FROM employees WHERE salary > 50000;
03

Logical query order

A SELECT query is commonly reasoned about as FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT.

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT
04

NULL is not zero

NULL means an unknown or missing value. Use IS NULL or IS NOT NULL instead of = NULL.

SELECT * FROM employees WHERE manager_id IS NULL;
05

Aliases

Aliases make long table and column references easier to read, especially in JOINs and calculated expressions.

SELECT e.name AS employee_name FROM employees AS e;
06

Comparison operators

Use =, <>, >, <, >=, <= and logical operators such as AND, OR and NOT to express conditions.

SELECT * FROM shop WHERE price > 1000 AND stock >= 10;
07

Common data types

Relational databases commonly use numeric, character, date/time and boolean-like types. Exact names vary by database engine.

CREATE TABLE users (id INT, name VARCHAR(100), joined_at DATE);
08

Aggregation

Aggregate functions reduce multiple rows into summaries. GROUP BY defines which rows belong to each group.

SELECT department_id, AVG(salary) FROM employees GROUP BY department_id;
04 / QUERY LANGUAGE

The SQL command map.

SQL statements are commonly discussed by the kind of work they perform: reading data, changing rows, defining structure and controlling access or transactions.

DQL — Query data

READ
SELECT

Retrieve columns and expressions.

SELECT name, salary FROM employees;
WHERE

Filter rows before grouping.

SELECT * FROM employees WHERE salary > 50000;
ORDER BY

Sort the result.

SELECT * FROM employees ORDER BY salary DESC;
DISTINCT

Remove duplicate result rows.

SELECT DISTINCT department FROM employees;
LIMIT / FETCH

Restrict the number of rows returned.

SELECT * FROM employees LIMIT 10;
Try it in SQLWhale↗

DML — Change data

WRITE
INSERT

Add new rows.

INSERT INTO employees (name, salary) VALUES ('Asha', 60000);
UPDATE

Change existing rows.

UPDATE employees SET salary = 65000 WHERE id = 7;
DELETE

Remove rows.

DELETE FROM employees WHERE id = 7;
MERGE

Combine insert/update logic where supported.

MERGE INTO target USING source ON target.id = source.id ...;
Try it in SQLWhale↗

DDL — Define structure

SCHEMA
CREATE

Create database objects.

CREATE TABLE employees (id INT PRIMARY KEY, name VARCHAR(100));
ALTER

Change an existing object.

ALTER TABLE employees ADD COLUMN email VARCHAR(200);
DROP

Remove an object.

DROP TABLE old_employees;
TRUNCATE

Remove all rows while retaining the table structure.

TRUNCATE TABLE staging;
Try it in SQLWhale↗

DCL / TCL — Access & transactions

CONTROL
GRANT

Give privileges.

GRANT SELECT ON employees TO analyst;
REVOKE

Remove privileges.

REVOKE UPDATE ON employees FROM analyst;
COMMIT

Make a transaction's changes durable.

COMMIT;
ROLLBACK

Undo uncommitted transaction work.

ROLLBACK;
SAVEPOINT

Create a transaction rollback point.

SAVEPOINT before_update;
Try it in SQLWhale↗
SELECT PIPELINE
FROM

Choose the source table(s).

FROM employees
JOIN ... ON

Combine related rows from multiple tables.

JOIN departments d ON e.department_id = d.id
WHERE

Filter individual source rows.

WHERE salary >= 50000
GROUP BY

Form groups for aggregate calculations.

GROUP BY department_id
HAVING

Filter groups after aggregation.

HAVING AVG(salary) > 60000
SELECT

Choose the final expressions/columns.

SELECT department_id, AVG(salary)
DISTINCT

Remove duplicate final rows.

SELECT DISTINCT city
ORDER BY

Sort the result.

ORDER BY AVG(salary) DESC
LIMIT / FETCH

Restrict returned rows where supported.

LIMIT 10
Try it in SQLWhale↗
SELECT
    d.name AS department,
    COUNT(*) AS people,
    AVG(e.salary) AS avg_salary
FROM employees e
JOIN departments d
    ON e.department_id = d.id
WHERE e.active = TRUE
GROUP BY d.name
HAVING AVG(e.salary) > 50000
ORDER BY avg_salary DESC;
05 / RELATIONSHIPS

JOINs: where tables become a data model.

A JOIN combines rows from multiple table expressions according to a matching condition or join rule.

INNER JOIN

Only matching rows from both sides.

SELECT e.name, d.name FROM employees e INNER JOIN departments d ON e.department_id = d.id;

LEFT JOIN

All rows from the left table plus matching rows on the right.

SELECT e.name, d.name FROM employees e LEFT JOIN departments d ON e.department_id = d.id;

RIGHT JOIN

All rows from the right table plus matching rows on the left.

SELECT e.name, d.name FROM employees e RIGHT JOIN departments d ON e.department_id = d.id;

FULL OUTER JOIN

All rows from both sides, matched where possible.

SELECT * FROM A FULL OUTER JOIN B ON A.id = B.id;

CROSS JOIN

Cartesian product: every left row paired with every right row.

SELECT * FROM colors CROSS JOIN sizes;

SELF JOIN

Join a table to itself using aliases.

SELECT e.name, m.name FROM employees e JOIN employees m ON e.manager_id = m.id;
Try it in SQLWhale↗
06 / EXPRESSIONS

Functions, NULLs and conditional logic.

Expressions let a query calculate, transform and classify values while returning or updating data.

FUNC

COUNT()

Count rows or non-null values.

COUNT(*)
FUNC

SUM()

Add numeric values.

SUM(amount)
FUNC

AVG()

Calculate an average.

AVG(salary)
FUNC

MIN()

Find the smallest value.

MIN(price)
FUNC

MAX()

Find the largest value.

MAX(price)
FUNC

COALESCE()

Return the first non-null expression.

COALESCE(phone, 'N/A')
FUNC

NULLIF()

Return NULL when two expressions are equal.

NULLIF(score, 0)
FUNC

CASE

Implement conditional expressions.

CASE WHEN salary > 80000 THEN 'Senior' ELSE 'Other' END
Try it in SQLWhale↗
07 / ADVANCED SQL

When simple queries stop being enough.

Advanced SQL combines query composition, analytics, reusable definitions, integrity and performance techniques.

Subqueries

A query nested inside another query.

SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

CTEs

Named query blocks introduced with WITH, useful for readable multi-step logic.

WITH high_paid AS (...) SELECT * FROM high_paid;

Window Functions

Calculate across related rows without collapsing them into groups.

AVG(salary) OVER (PARTITION BY department_id)

Set Operators

Combine compatible query results.

SELECT city FROM customers UNION SELECT city FROM suppliers;

Views

Store a reusable query definition as a database object.

CREATE VIEW active_users AS SELECT ...;

Indexes

Data structures that can accelerate selected access patterns, with storage/write trade-offs.

CREATE INDEX idx_email ON users(email);

Transactions

Group multiple changes into an atomic unit of work.

BEGIN; UPDATE ...; COMMIT;

Constraints

Enforce integrity at the database layer.

CHECK (salary >= 0)

Stored Procedures

Server-side routines supported by many SQL database systems.

CREATE PROCEDURE ...

Triggers

Database actions automatically invoked by defined events where supported.

CREATE TRIGGER ...
Try it in SQLWhale↗
08 / DESIGN

Good SQL starts before the first SELECT.

Database design affects correctness, maintainability and query performance. Learn normalization, keys, constraints and indexing before optimizing syntax.

01

Normalization

Reduce inappropriate redundancy and update anomalies by structuring related data.

02

1NF

Atomic values and a tabular structure without repeating groups.

03

2NF

1NF plus removal of partial dependency on a composite key.

04

3NF

2NF plus removal of relevant transitive dependencies.

05

Indexes

Speed selected reads by maintaining additional access structures at storage cost.

06

Transactions

Use atomic units of work with COMMIT and ROLLBACK semantics.

07

ACID

Atomicity, consistency, isolation and durability are core transaction properties.

08

Query Plans

The database optimizer chooses an execution strategy based on statistics, indexes and other factors.

09 / REAL WORLD

SQL is the data layer behind many systems.

Relational databases are used across application development, finance, commerce, analytics, engineering and many operational systems.

01

Web & SaaS

Users, authentication records, products, orders, subscriptions and application state.

02

Banking & FinTech

Accounts, transactions, ledgers, payments, compliance and reporting.

03

E-commerce

Catalogs, inventory, carts, orders, customers, pricing and fulfillment.

04

Analytics

Dashboards, KPIs, aggregations, cohort analysis and business reporting.

05

Data Engineering

ETL/ELT pipelines, staging tables, transformations and data quality checks.

06

Data Science

Feature extraction, dataset preparation, exploration and analytical workloads.

07

Healthcare

Structured records, scheduling, billing, inventory and operational reporting, subject to applicable privacy controls.

08

Education

Students, courses, assessments, attendance, learning activity and reporting.

09

Logistics

Routes, shipments, warehouses, drivers, inventory and delivery events.

10

Gaming

Players, inventories, scores, sessions, purchases and game telemetry.

10 / LEARNING PATH

Build from rows to real systems.

Use this sequence as a practical route from basic retrieval to production database thinking.

01 / Relational model02 / Tables & keys03 / SELECT04 / Filtering05 / Sorting06 / Grouping07 / JOINs08 / Subqueries09 / CTEs10 / Window functions11 / Transactions12 / Indexes13 / Design & normalization14 / Query optimization15 / Production SQL
NEXT / EXPERIENCE SQL

Don't just read SQL.
Watch it execute.

SQLWhale turns query execution into a visual learning process. Write a query, run it and follow what happens to the data.

Open SQLWhale Query Lab →