Basic SQL Queries

The four core commands — SELECT, INSERT, UPDATE, DELETE — used to read and change data in a database.

What is it?

Once data lives in tables, you need a way to actually read it and change it. SQL (Structured Query Language) is the language almost every relational database understands, and it's built around four core operations, often remembered by the acronym CRUD (Create, Read, Update, Delete):

  • SELECT — read existing rows
  • INSERT — add a new row
  • UPDATE — change values in existing rows
  • DELETE — remove rows

These four commands, combined with ways to filter which rows they apply to, cover the overwhelming majority of everyday database work.

Explain like I'm 10

Think of a table as a shared notebook. SELECT is reading a page without touching it. INSERT is writing a brand-new entry on a fresh line. UPDATE is crossing out part of an existing entry and writing a correction. DELETE is tearing an entry out entirely.

Examples

All four operations on a users table

-- Read every user
SELECT * FROM users;

-- Add a new user
INSERT INTO users (name, email) VALUES ('Amara', 'amara@example.com');

-- Change an existing user's email
UPDATE users SET email = 'amara@newmail.com' WHERE id = 1;

-- Remove a user
DELETE FROM users WHERE id = 1;

Each statement is a complete command on its own — SQL statements are typically written one operation at a time, ending in a semicolon.

Selecting specific columns

SELECT name, email FROM users;

UPDATE users SET age = age + 1 WHERE id = 3;

SELECT doesn't have to grab every column — you can list exactly the ones you need. UPDATE can also compute a new value based on the current one, like incrementing age by 1.

How it works

When you send a SQL statement to the database, it gets parsed and turned into a plan for how to carry it out: which table to touch, which rows match any given condition, and what to do with them. SELECT never changes data — it only reads and returns it. INSERT, UPDATE, and DELETE all modify the table's actual contents, and each one, without a WHERE clause narrowing things down, applies to every row in the table — which is why forgetting a WHERE clause on UPDATE or DELETE is one of the most dangerous mistakes in SQL.

Why does it exist?

SQL exists so that reading and changing data doesn't require custom code for every situation. Instead of writing a program to loop through files searching for matches, you describe what you want ("all users older than 30") and the database figures out how to get it, using one shared, standardized language across virtually every relational database.

When to use it

Use SELECT any time you need to read data. Use INSERT when new data is created (a new signup, a new order). Use UPDATE when existing data changes (an address update, a status change). Use DELETE when data should be permanently removed.

When not to use it

For truly bulk, one-time data loads (millions of rows at once) many databases offer faster specialized bulk-loading tools instead of individual INSERT statements. And for data you might need to recover later, consider a "soft delete" (marking a row as inactive) instead of an actual DELETE.

Common mistakes

  • Running an UPDATE or DELETE without a WHERE clause, which applies the change to every single row in the table.

  • Forgetting that SELECT * pulls every column, even ones you don't need, which wastes bandwidth on large tables.

  • Assuming INSERT requires listing every column — columns with defaults or that allow empty values can often be omitted.

Practice exercises

  1. Easy:

    Write a SELECT statement that retrieves the name and email of every row in a users table.

  2. Medium:

    Write an INSERT statement adding a new product with a name and a price to a products table, and an UPDATE statement that changes that product's price.

  3. Hard:

    Explain, step by step, what would happen if you ran DELETE FROM users; with no WHERE clause, and why that's dangerous.

Interview questions

What do the four basic SQL commands SELECT, INSERT, UPDATE, and DELETE each do?

SELECT reads rows, INSERT adds a new row, UPDATE modifies values in existing rows, and DELETE removes rows.

What happens if you run an UPDATE statement with no WHERE clause?

It applies the change to every row in the table, not just one — which is a common and dangerous mistake.

Does SELECT ever modify the underlying data?

No — SELECT only reads and returns data; it never changes what's stored.

How do you insert multiple new rows using a single INSERT statement?

List multiple parenthesized value groups after VALUES, separated by commas — for example, INSERT INTO products (name, price_cents) VALUES ('Pen', 150), ('Notebook', 300), ('Eraser', 50); inserts three rows in one statement.

Why is a single multi-row INSERT generally more efficient than the same number of single-row INSERT statements?

It sends and parses one statement instead of many, letting the database batch the work internally — fewer round trips between application and database, and often fewer repeated internal bookkeeping steps like index updates — which matters a lot when inserting many rows.

What does adding `RETURNING *` (or specific columns) to an INSERT statement do?

It hands back the actual row as it ended up stored — including any values the database generated itself, like an auto-incremented id or a default timestamp — in the same round trip as the insert, without a separate SELECT afterward.

Why is RETURNING especially useful after an INSERT that relies on an auto-generated primary key?

The application doesn't know that generated id ahead of time. Without RETURNING, a second query would be needed to look the new row back up, and you'd need some other unique detail to find it by; RETURNING hands the id back immediately as part of the insert itself.

Is RETURNING part of every SQL database's basic dialect?

No — support for it varies by database. Whether, and how, RETURNING is available depends on which specific database you're using, so it's worth checking that database's own documentation rather than assuming it works identically everywhere.

Can RETURNING be used with UPDATE and DELETE statements too, not just INSERT?

Where supported, yes — an UPDATE with RETURNING hands back a row's new values after the change, and a DELETE with RETURNING hands back the values of the row as they were right before it was removed.

Running `INSERT INTO users (name) VALUES ('Amara');` fails, and `email` is declared NOT NULL with no default. Why?

Omitting a column from an INSERT's column list is only allowed if that column has a DEFAULT value or otherwise permits NULL. Neither is true for email here, so the database refuses to leave it without a value and rejects the insert.

If you supply an explicit column list in an INSERT, like `INSERT INTO users (email, name) VALUES ('a@x.com', 'Amara');`, does the order of those columns need to match the table's declared column order?

No — when you supply an explicit column list, each value is matched to a column by position within that list, not the table's original declared order, so listing email before name works fine even if the table itself declares name first. Omitting the column list entirely, though, does require the VALUES to line up with the table's actual declared column order.

What's the difference between `UPDATE products SET price_cents = 500` and `UPDATE products SET price_cents = price_cents + 100`?

The first sets every matching row's price_cents to the exact same fixed value, 500. The second computes a new value relative to each row's current value, adding 100 to whatever it already was, so different rows end up with different results depending on their starting price.

If an UPDATE or DELETE's WHERE clause matches zero rows, is that an error?

No — the statement completes successfully having changed zero rows. Most databases report back a rows-affected count of 0 rather than raising an error, since matching no rows isn't inherently invalid.

What's the difference between `DELETE FROM orders;` with no WHERE and `TRUNCATE TABLE orders;`?

Both remove every row, but DELETE removes rows one at a time as a filterable operation (and can be given a WHERE clause), while TRUNCATE is a more specialized operation that resets the whole table at once — often faster, but it can't be limited with a WHERE clause and can interact differently with things like auto-increment counters depending on the database.

Can a SELECT return a computed value that isn't stored as an actual column?

Yes — SELECT can include expressions, not just raw column names. SELECT price_cents * quantity AS line_total FROM order_items; computes a new value per row on the fly and labels it with the alias line_total.

What does `AS` do in a statement like `SELECT price_cents * quantity AS line_total`?

It gives a computed expression, or even a plain column, an alias — a name to use for that value in the returned results — which is especially useful for expressions that wouldn't otherwise have a meaningful column name.

What's the difference between `INSERT INTO ... VALUES (...)` and `INSERT INTO ... SELECT ...`?

VALUES inserts one or more literal rows written out directly. INSERT INTO ... SELECT instead inserts whatever rows a SELECT query produces, which is useful for copying or transforming data already in the database into another table without pulling it out to the application first.

If a multi-row INSERT includes one row that violates a constraint, like a duplicate primary key, what typically happens to the other, valid rows in that statement?

In most databases the entire statement fails and none of the rows are inserted — the statement is treated as one atomic operation, not independent per-row inserts, so a single bad row rolls the whole batch back rather than leaving partial results.

Could `UPDATE users SET status = 'active' WHERE last_login > '2026-01-01';` ever match more than one row?

Yes, easily. WHERE only guarantees a single row when filtering on a column known to be unique, like a primary key. Filtering on a non-unique column like last_login can match any number of rows, all of which get updated.

After running an UPDATE or DELETE, how do you find out how many rows were actually affected?

Most database drivers or clients report a rows-affected count alongside the statement's result. Checking that count is a common way to confirm a targeted UPDATE or DELETE actually hit the row you expected, rather than silently matching zero.

Why might using `SELECT *` in application code be riskier than listing exact column names?

If the table's columns change later — one added, removed, or reordered — code relying on SELECT * can silently start receiving different data than expected. Naming exact columns keeps the query's contract stable regardless of later schema changes, and avoids fetching data the application doesn't need.

Is `WHERE id = 1` guaranteed to behave the same as `WHERE id = '1'` if id is an INTEGER column?

Many databases implicitly convert the string '1' to compare it against the integer column and still match, but relying on that conversion is poor practice — some databases are stricter about it, and it can silently defeat index usage in others — so it's better to match the literal's type to the column's declared type.

Explain what happens, step by step, if you run `DELETE FROM users;` with no WHERE clause.

The database interprets having no WHERE clause as no filter at all, so it deletes every single row currently in the users table, not just one or a few, with no built-in way to select 'the one I meant' afterward — which is why this is one of the most dangerous mistakes possible in SQL.

You want to update a row and immediately see its new value, like an updated_at timestamp the database sets automatically. What's the advantage of `UPDATE ... RETURNING updated_at` over a separate SELECT afterward?

RETURNING gets you the row's actual post-update value in the same round trip and same atomic operation. A separate follow-up SELECT is an extra query, and in theory another process could change or delete the row in the gap between the two, so RETURNING avoids that race entirely.

Write a single statement that inserts three new products, each with a name and price_cents.

INSERT INTO products (name, price_cents) VALUES ('Pen', 150), ('Notebook', 300), ('Folder', 220); — one INSERT, three comma-separated value groups, all committed together.

Why do INSERT, UPDATE, and DELETE each count as a single atomic operation, even when they affect many rows at once?

The database guarantees either the entire statement's effect on every matching row takes hold, or none of it does. There's no in-between state where only some of the intended rows changed and the statement failed partway, which is what makes a rows-affected count of 0 or the full expected count a safe way to reason about a statement's outcome.

A developer runs `UPDATE orders SET status = 'shipped' WHERE order_date = '2026-01-01';` intending to update one specific order, but several rows change. What went wrong?

They filtered on order_date, a column that isn't guaranteed unique — multiple orders can share the same date — so WHERE matched every order placed that day instead of the single one intended. Filtering by a unique identifier like the order's id would have targeted exactly one row.