SQL

Relational databases with schemas, joins and ACID transactions — PostgreSQL, MySQL, SQL Server.

3 min read 3 lesson sections
  • #postgres
  • #mysql
  • #relational
  • #acid
SQL: Relational databases with schemas, joins and ACID transactions — PostgreSQL, MySQL, SQL Server.
Concept map

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 with GROUP BY, and combine tables with JOIN.
  • 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)
Private by design: queries stay on this device.