SQL: Querying and Modifying Data

Read and write real SQL — SELECT, INSERT, UPDATE, DELETE, filtering with WHERE, sorting with ORDER BY, grouping with GROUP BY/HAVING, combining tables with JOIN, using subqueries and aggregate functions, and aliasing — executed from PHP through PDO.

SQL (Structured Query Language) is how you talk to a relational database: create data, read it back with exactly the shape and filtering you need, and change or remove it. This lesson works through every core SQL clause against a small, concrete schema — users, products, and orders — showing the statement on its own and then run from PHP via PDO. By the end you should be able to look at almost any everyday SQL query and know what each clause is doing, and to write one yourself for a given question.

Sample Schema

Every example in this lesson runs against the same three tables so the queries build on each other. users(id, name, email) holds customers. products(id, name, price, stock) holds items for sale. orders(id, user_id, product_id, quantity, created_at) records purchases, with user_id and product_id as foreign keys pointing back to users and products. Keep this shape in mind — every SELECT, JOIN, and aggregate below is answering a question about this exact data.

• users(id, name, email) • products(id, name, price, stock) • orders(id, user_id, product_id, quantity, created_at)

SELECT, WHERE, and Aliases

SELECT reads rows from a table. SELECT * FROM products returns every column of every row; naming columns explicitly (SELECT name, price FROM products) is almost always better practice — it avoids pulling data you don't need and keeps the result stable if columns are added later. WHERE filters which rows come back, using comparison operators (=, !=, <, >, <=, >=), logical combinators (AND, OR, NOT), IN (...) for a set of values, BETWEEN ... AND ... for a range, and LIKE with %/_ wildcards for pattern matching on text.

An alias renames a column or table for the duration of a query, using AS (often optional). SELECT price AS unit_price FROM products renames the output column; SELECT p.name FROM products AS p gives the table a short handle, which becomes essential once a query joins several tables and needs to say which table's name column it means.

• SELECT col1, col2 FROM table — read specific columns; avoid SELECT * in real code. • WHERE condition — filters rows: =, !=, <, >, AND, OR, IN (...), BETWEEN, LIKE '%pattern%'. • Column alias: SELECT price AS unit_price. • Table alias: FROM products AS p — required once multiple tables are joined.

ORDER BY, GROUP BY, and HAVING

ORDER BY sorts the result set by one or more columns, ASC (ascending, the default) or DESC (descending): ORDER BY price DESC shows the most expensive products first. You can sort by multiple columns, where the second is a tiebreaker for rows equal on the first: ORDER BY category, price DESC.

GROUP BY collapses rows that share the same value in one or more columns into a single summary row per group, almost always paired with an aggregate function (covered next) that computes something over each group — for example, the total quantity ordered per product. Once you group, every column in the SELECT list must either be one of the GROUP BY columns or wrapped in an aggregate function; you can't select a raw column that varies within the group, because there's no single value to show.

HAVING filters groups after GROUP BY has collapsed them, the same way WHERE filters individual rows before grouping. The distinction matters: WHERE can't reference an aggregate (like COUNT(*)) because it runs before grouping happens; HAVING can, because it runs after. SELECT product_id, SUM(quantity) AS total_qty FROM orders GROUP BY product_id HAVING SUM(quantity) > 10 finds only the products ordered more than 10 units of, in total.

• ORDER BY column [ASC|DESC] — sorts results; multiple columns break ties in order. • GROUP BY column — collapses rows sharing a value into one summary row per group. • HAVING condition — filters groups after aggregation (unlike WHERE, can use aggregate functions). • WHERE filters rows before grouping; HAVING filters groups after grouping.

JOIN: Combining Tables

A JOIN combines rows from two tables based on a related column, almost always a foreign key. An INNER JOIN (often written just JOIN) returns only rows that have a match in both tables: SELECT o.id, u.name FROM orders o JOIN users u ON o.user_id = u.id returns every order alongside its buyer's name, but a user with zero orders never appears, and an order with a broken user_id (which shouldn't happen if the foreign key constraint is enforced) would be silently dropped.

A LEFT JOIN returns every row from the left (first-named) table, plus matching data from the right table where it exists, and NULLs in the right table's columns where it doesn't. SELECT u.name, o.id FROM users u LEFT JOIN orders o ON o.user_id = u.id returns every single user, even ones with no orders at all — their o.id just comes back NULL. This is the standard way to answer 'give me all X, and whatever related Y they have, including X with no Y'.

There's also RIGHT JOIN (mirror image of LEFT — keep every row from the right table) and FULL OUTER JOIN (keep every row from both sides), though MySQL/MariaDB don't support FULL OUTER JOIN directly and it's simulated with a UNION when needed. In everyday PHP application code, INNER JOIN and LEFT JOIN cover the overwhelming majority of real queries.

• INNER JOIN (JOIN): only rows with a match in both tables. • LEFT JOIN: every row from the left table, matched data from the right where it exists, NULL where it doesn't. • RIGHT JOIN: mirror of LEFT JOIN, keeping every row from the right table. • Join condition goes in ON, usually foreign_key = referenced_primary_key.

Aggregates and Subqueries

Aggregate functions compute a single value from a set of rows: COUNT(*) counts rows, SUM(col) totals a numeric column, AVG(col) averages it, MIN(col)/MAX(col) find the smallest/largest value. Used without GROUP BY, an aggregate collapses the entire table (or filtered result) into one row — SELECT COUNT(*) FROM orders WHERE product_id = 7 answers 'how many orders were placed for product 7'. Used with GROUP BY, you get one aggregate value per group, as shown above.

A subquery is a SELECT nested inside another query, used where a single value or a set of values is needed. A scalar subquery returns one value and can be used almost anywhere a literal could go: SELECT name FROM products WHERE price > (SELECT AVG(price) FROM products) finds products priced above the overall average — note the subquery is evaluated first, then the outer query compares each row's price against that single number. A subquery can also appear in a WHERE ... IN (...) clause to filter against a set: SELECT name FROM users WHERE id IN (SELECT user_id FROM orders WHERE product_id = 7) finds every user who has ever ordered product 7.

Subqueries and JOINs often solve the same problem, and it's common to reach for whichever reads more naturally for the question being asked; for anything performance-sensitive, check the query's execution plan (EXPLAIN) rather than assuming one form is always faster.

• COUNT(*), SUM(col), AVG(col), MIN(col), MAX(col) — aggregate a set of rows into one value. • Aggregates without GROUP BY collapse the whole (filtered) table into a single row. • Scalar subquery: (SELECT ...) returning one value, usable in WHERE/SELECT like a literal. • WHERE col IN (SELECT ...) — filter against a set produced by another query.

INSERT, UPDATE, and DELETE

INSERT INTO table (col1, col2) VALUES (val1, val2) adds a new row, naming the columns being set and supplying matching values in the same order; any column left out gets its default value (or NULL, or errors, if it's NOT NULL with no default). UPDATE table SET col = new_value WHERE condition modifies existing rows — critically, the WHERE clause is what limits which rows are touched; an UPDATE with no WHERE at all updates every single row in the table.

DELETE FROM table WHERE condition removes rows matching the condition, and, exactly like UPDATE, a DELETE FROM table with no WHERE deletes every row in the table. Both UPDATE and DELETE deserve a moment's pause before running against production data — many developers get in the habit of first running the equivalent SELECT with the same WHERE clause, to see exactly which rows would be affected, before switching it to UPDATE/DELETE.

• INSERT INTO table (cols...) VALUES (values...) — adds one new row. • UPDATE table SET col = value WHERE condition — modifies matching rows; no WHERE means every row. • DELETE FROM table WHERE condition — removes matching rows; no WHERE means every row. • Habit: run the equivalent SELECT with the same WHERE first, to preview what UPDATE/DELETE will touch.

Anatomy of a SELECT Query

This is also the logical execution order (roughly): FROM/JOIN, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT — which is why HAVING can use aggregates and WHERE can't. LIMIT (MySQL/PostgreSQL/SQLite) caps rows returned — useful for pagination, combined with OFFSET.

SELECT   column_list        -- what to return (with optional aliases)
FROM     table               -- which table (optionally aliased)
JOIN     other_table ON ...  -- optional: bring in related rows
WHERE    row_condition       -- optional: filter individual rows
GROUP BY grouping_columns    -- optional: collapse rows into groups
HAVING   group_condition     -- optional: filter groups (post-aggregation)
ORDER BY sort_columns        -- optional: sort the final result
LIMIT    n;                  -- optional: cap the number of rows returned

Code Examples

Selects in-stock products priced between 10 and 100, most expensive first, renaming the output columns to product_name and unit_price for clarity.

WHERE p.price BETWEEN 10 AND 100 AND p.stock > 0 Collapses orders into one row per product_id, sums the quantity ordered per product, keeps only products with more than 10 total units ordered, and sorts the most-ordered products first.

GROUP BY product_id HAVING SUM(quantity) > 10 The INNER JOIN silently drops users with no orders; the LEFT JOIN keeps every user and fills in NULL for order_id where there's no matching order. Choosing between them is choosing whether 'no related rows' should disappear or show up as NULL.

Common mistake: Using INNER JOIN when you actually want 'all X regardless of related Y' silently hides rows — always ask which side must be complete before picking the join type.

The first query's subquery runs once, producing a single average price, which the outer query then compares against; the second query's subquery produces a set of user_ids to match against with IN.

WHERE price > (SELECT AVG(price) FROM products) The same SQL clauses covered above, run through PDO prepared statements: the INSERT/UPDATE/DELETE mutate data with placeholders instead of raw string concatenation, and the SELECT combines a LEFT JOIN with COUNT and GROUP BY to report every user's order count, including users with zero orders. Expected result: Ben: 3 orders Asha: 2 orders Cara: 0 orders

UPDATE products SET price = 0; -- meant to add a WHERE id = 15 but forgot it

UPDATE products SET price = 0 WHERE id = 15;

Without a WHERE clause, UPDATE and DELETE apply to every row in the table — this is one of the most common and most damaging SQL mistakes; always double-check the WHERE clause (or run the equivalent SELECT first) before executing.

SELECT name, price FROM products GROUP BY category; -- price is not aggregated and not in GROUP BY

SELECT category, AVG(price) AS avg_price FROM products GROUP BY category;

Once you GROUP BY, every selected column must either be a GROUP BY column or wrapped in an aggregate — selecting a raw column that varies within the group is invalid because there's no single value to display for it.

What's the key difference between WHERE and HAVING? A. They are interchangeable in every case B. WHERE filters rows before grouping and cannot use aggregate functions; HAVING filters groups after grouping and can use aggregates C. WHERE only works with SELECT; HAVING only works with UPDATE D. HAVING is only valid in PostgreSQL, not MySQL

B. WHERE filters rows before grouping and cannot use aggregate functions; HAVING filters groups after grouping and can use aggregates — WHERE runs before GROUP BY collapses rows into groups, so it can't reference an aggregate like SUM(); HAVING runs after grouping and filters the resulting groups, so it can.

Given SELECT u.name, o.id FROM users u LEFT JOIN orders o ON o.user_id = u.id, what happens to a user with zero orders? A. The user is excluded from the results entirely B. The query fails with an error C. The user still appears, with o.id returned as NULL D. The user appears once per product in the products table

C. The user still appears, with o.id returned as NULL — LEFT JOIN keeps every row from the left table (users) regardless of whether a match exists on the right (orders), filling unmatched right-side columns with NULL.

Why is UPDATE products SET stock = 0; (with no WHERE) dangerous? A. It only affects the first row B. It sets stock to 0 for every single row in the products table C. It will fail unless stock already has an index D. PDO blocks UPDATE statements without a WHERE by default

B. It sets stock to 0 for every single row in the products table — Without a WHERE clause, UPDATE (and DELETE) apply to every row in the table, not just the intended one — a WHERE clause is what scopes the change.

Using the users/products/orders schema, write one query that lists each product's name alongside the total revenue it has generated (price * quantity, summed across all its orders), showing only products with more than $100 in total revenue, ordered highest revenue first.

SELECT p.name, SUM(p.price * o.quantity) AS revenue FROM products p JOIN orders o ON o.product_id = p.id GROUP BY p.id, p.name HAVING SUM(p.price * o.quantity) > 100 ORDER BY revenue DESC;

You'll need a JOIN between products and orders, an aggregate SUM expression, GROUP BY, and HAVING (not WHERE, since the condition is on an aggregate).

SELECT p.name AS product_name, p.price AS unit_price
FROM products AS p
WHERE p.price BETWEEN 10 AND 100
  AND p.stock > 0
ORDER BY p.price DESC;

SELECT product_id, SUM(quantity) AS total_ordered
FROM orders
GROUP BY product_id
HAVING SUM(quantity) > 10
ORDER BY total_ordered DESC;

-- Only users who have placed at least one order:
SELECT u.name, o.id AS order_id
FROM users u
JOIN orders o ON o.user_id = u.id;

-- Every user, including those with zero orders (order_id is NULL for them):
SELECT u.name, o.id AS order_id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

-- Products priced above the average price (scalar subquery):
SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- Users who have ordered a specific product (subquery with IN):
SELECT name
FROM users
WHERE id IN (SELECT user_id FROM orders WHERE product_id = 7);

<?php
declare(strict_types=1);

// INSERT a new product
$pdo->prepare('INSERT INTO products (name, price, stock) VALUES (?, ?, ?)')
    ->execute(['Wireless Mouse', 24.99, 50]);

// UPDATE — always scope with WHERE
$pdo->prepare('UPDATE products SET stock = stock - :qty WHERE id = :id')
    ->execute(['qty' => 1, 'id' => 15]);

// DELETE — always scope with WHERE
$pdo->prepare('DELETE FROM orders WHERE id = :id')
    ->execute(['id' => 501]);

// SELECT with JOIN + aggregate, fetched as an array of rows
$stmt = $pdo->prepare(
    'SELECT u.name, COUNT(o.id) AS order_count
     FROM users u
     LEFT JOIN orders o ON o.user_id = u.id
     GROUP BY u.id, u.name
     ORDER BY order_count DESC'
);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach ($rows as $row) {
    echo "{$row['name']}: {$row['order_count']} orders" . PHP_EOL;
}