Query, join, and design relational data with confidence.
A relational database stores data in tables made of rows and columns. SELECT retrieves columns, WHERE filters rows, and ORDER BY sorts the result.
SELECT name, email
FROM users
WHERE age >= 18
ORDER BY name ASC;| id | name | age |
|---|---|---|
| 1 | Amir | 31 |
| 2 | Priya | 24 |
| 3 | Leo | 17 |
Real data is split across related tables to avoid duplication. JOIN combines rows from two tables based on a matching key. INNER JOIN returns only matching rows; LEFT JOIN keeps every row from the left table even without a match.
SELECT orders.id, users.name, orders.total
FROM orders
INNER JOIN users ON orders.user_id = users.id;
Aggregate functions like COUNT(), SUM(), and AVG() compute a single value across a group of rows, defined by GROUP BY. HAVING filters groups after aggregation, while WHERE filters rows before it.
SELECT user_id, COUNT(*) AS order_count, SUM(total) AS revenue
FROM orders
GROUP BY user_id
HAVING SUM(total) > 100;Normalization organizes tables to reduce duplicate data. A primary key uniquely identifies each row; a foreign key references a primary key in another table to model relationships. An index is a lookup structure that speeds up queries on a column, at the cost of extra storage and slower writes.
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
user_id INTEGER REFERENCES users(id),
total DECIMAL(10,2)
);
CREATE INDEX idx_orders_user_id ON orders(user_id);
A subquery is a query nested inside another query. A CTE (WITH ... AS) names a temporary result set upfront, making complex, multi-step queries far more readable than deeply nested subqueries.
WITH high_spenders AS (
SELECT user_id, SUM(total) AS spent
FROM orders
GROUP BY user_id
HAVING SUM(total) > 500
)
SELECT users.name, high_spenders.spent
FROM users
JOIN high_spenders ON users.id = high_spenders.user_id;
A transaction groups multiple statements so they all succeed or all fail together — critical when, say, moving money between two accounts. COMMIT saves the changes; ROLLBACK undoes everything since the transaction began.
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;