SQL & relational foundations

SQL is a declarative language: you describe the result you want, while the database engine decides how to produce it. That difference matters. Good SQL is less about memorizing commands and more about expressing sets, relationships, filters, and transformations precisely.

Relational databases in one model

A relational database stores data in tables. A table has named columns and rows. Each column represents an attribute with a data type; each row represents one occurrence of the thing being modeled. Tables become useful when relationships are explicit: an order refers to a customer, an order item refers to an order and a product, and so on.

The sample database used by this site contains realistic tables such as users, customers, orders, order_items, employees, departments, events, payments, and test_results. Most examples in this learning path use that same dataset so you can run them rather than only read them.

Keys and relationships

A primary key identifies one row inside a table. A foreign key stores the key of a related row in another table. The database can enforce that relationship with a foreign-key constraint, but SQL queries can also inspect data where those guarantees are absent or temporarily violated.

For example, orders.customer_id is conceptually related to customers.id. A join expresses that relationship when retrieving data.

SELECT o.id AS order_id,
       c.name AS customer,
       o.status,
       o.total
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
ORDER BY o.id;

Run the query to see the result.

SQL is standardized, engines are not identical

Core SQL concepts transfer across PostgreSQL, MySQL, SQL Server, Oracle, SQLite, and other relational systems, but syntax and behavior differ around data types, date functions, auto-generated keys, pagination, JSON, stored procedures, locking, and many advanced features. This path teaches portable concepts first and calls out dialect-specific behavior when it matters.

The browser runner on this site uses SQLite in memory. PostgreSQL documentation is used as the deeper semantic reference because it explains standard relational concepts clearly and documents advanced SQL rigorously. W3Schools is used as a beginner-oriented breadth checklist, not as the sole authority for engine behavior.

Read queries versus write queries

SQL work usually falls into several groups:

• DQL / querying: SELECT and related clauses retrieve data.
• DML: INSERT, UPDATE, and DELETE change rows.
• DDL: CREATE, ALTER, and DROP change schema objects.
• Transaction control: BEGIN, COMMIT, ROLLBACK, and SAVEPOINT control atomic units of work.
• Security / permissions: GRANT and REVOKE are available in server databases such as PostgreSQL, but not in the same form in SQLite.

Start with reads. A SELECT is safer to explore, easier to reason about, and forms the basis for most debugging and QA data checks.

First query and mental checklist

Read a query in this order: what rows exist in FROM, which rows survive WHERE, how rows are grouped, which groups survive HAVING, what expressions are selected, then how the result is sorted and limited.

SELECT id, email, status
FROM users
WHERE status = 'active'
ORDER BY id;

Run the query to see the result.

Practice: run the query, then change the selected columns and filter. Predict the result before pressing Run. That predict-then-run habit is more valuable than copying syntax.