SQL Fundamentals

Reading data

SELECT name, price
FROM products
WHERE category = 'books' AND price < 20
ORDER BY price DESC
LIMIT 10;

• SELECT chooses columns. Avoid SELECT * in application code, because it fetches more than needed and breaks when columns change.
• WHERE filters rows. Use AND, OR, IN (...), BETWEEN, LIKE and IS NULL.
• ORDER BY sorts. Without it, the order of rows is not guaranteed.
• LIMIT (PostgreSQL, MySQL, SQLite) or TOP / FETCH FIRST (SQL Server) caps the number of rows.

Aggregating

SELECT category, COUNT(*) AS items, AVG(price) AS avg_price
FROM products
GROUP BY category
HAVING COUNT(*) > 5;

COUNT, SUM, AVG, MIN and MAX collapse many rows into one value per group. WHERE filters rows before grouping, HAVING filters groups afterwards.

Joining tables

SELECT c.name, o.id, o.total
FROM customers AS c
INNER JOIN orders AS o ON o.customer_id = c.id
WHERE o.total > 100;

• INNER JOIN keeps only rows that match on both sides.
• LEFT JOIN keeps all rows from the left table and fills NULL where there is no match. Use it to find “customers without orders”.

Changing data

INSERT, UPDATE and DELETE modify rows. Always include a WHERE on updates and deletes, and run it first as a SELECT to see which rows it would touch. Related changes belong in a transaction.

SQL injection

Never build SQL by pasting user input into a string. Use parameterized queries, which keep data separate from the command. See Common Vulnerabilities and Input Validation.

Things to explore later

Subqueries, UNION, window functions, views, and reading a query plan with EXPLAIN (see Indexing Basics).

Try it

Install SQLite (or use any local database) and create a products table with a few rows. Write one query each for: filtering and sorting, a group count per category, and a LEFT JOIN that finds rows with no match in a second table.