SQL
Relational databases with schemas, joins and ACID transactions — PostgreSQL, MySQL, SQL Server.
SQL (Structured Query Language) is used to define, read, and change data in relational databases. A relational database organizes records into tables with named columns and a schema. Primary keys identify rows, while foreign keys express relationships between rows in different tables.
Think like a designer: If a customer places five orders, where should the customer’s name live? A good table design stores each fact once and connects records with keys.
A small relational model
Imagine an online shop with a customers table and an orders table. Each customer has a unique ID, and each order stores the ID of the customer who placed it. This avoids repeating a customer’s contact details on every order and lets the database enforce that an order refers to a real customer.
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL REFERENCES customers(id),
total_cents INTEGER NOT NULL CHECK (total_cents >= 0)
);
SELECT customers.name, orders.id, orders.total_cents
FROM customers
JOIN orders ON orders.customer_id = customers.id
WHERE orders.total_cents > 5000
ORDER BY orders.total_cents DESC;
The SELECT statement describes the desired result: match each order to its customer, filter for larger totals, and sort them. The database’s query planner chooses how to execute that request.
Transactions and consistency
A transaction groups related changes so they succeed or fail together. Relational systems commonly provide ACID properties: atomicity (all-or-nothing changes), consistency (constraints remain true), isolation (concurrent work is controlled), and durability (committed work survives a failure under the system’s guarantees). Transactions are essential when multiple updates must represent one logical action, such as recording a payment and reducing inventory.
Practical query skills
- Filter rows with
WHERE, summarize groups withGROUP BY, and combine tables withJOIN. - Use constraints such as
NOT NULL,UNIQUE, and foreign keys to keep invalid data out. - Add indexes for columns used often in filters and joins. Indexes speed up reads but take storage and add work to writes.
- Inspect query plans with your database’s explain tool before guessing why a query is slow.
- Normalize related data to avoid update anomalies, then denormalize only when measured needs justify the extra complexity.
PostgreSQL and MySQL are widely used open-source relational databases; SQL Server and Oracle are common in enterprise environments. SQL syntax and features vary between engines, so check the documentation for the database you use.
Hands-on practice
SQL playground
Run real SQL against a small sample database. Everything runs in your browser and resets when you leave this page.
Explore the sample tables
students(id, name, track)courses(id, title, topic)enrollments(student_id, course_id, score)