Essential SQL Query Examples for Common Database Operations

Introduction

This reference supplies clear SQL query examples for common database queries and operations. The examples cover CRUD (Create, Read, Update, Delete), JOINs, aggregates, transactions, and basic optimization patterns so queries can be copied, adapted, and used with most relational databases.

Common SQL queries: core examples

The examples below illustrate common SQL queries and patterns that address everyday data tasks. Replace table and column names to match the schema in use.

Basic SELECT (read)

-- select specific columns with a filter
SELECT id, name, email
FROM users
WHERE active = TRUE
ORDER BY created_at DESC
LIMIT 50;

INSERT (create) — SQL insert update delete examples

-- single row insert
INSERT INTO products (sku, name, price, created_at)
VALUES ('P1001', 'Widget', 19.99, NOW());

-- bulk insert
INSERT INTO products (sku, name, price)
VALUES
  ('P1002','Gadget',29.99),
  ('P1003','Thing',9.95);

UPDATE (modify)

-- update with a WHERE clause to avoid full-table changes
UPDATE users
SET last_login = NOW(), login_count = login_count + 1
WHERE id = 123;

DELETE (remove)

-- delete soft vs hard; prefer soft deletes when possible
-- hard delete
DELETE FROM sessions WHERE expires_at < NOW();

-- soft delete pattern (add a deleted_at column)
UPDATE orders SET deleted_at = NOW() WHERE id = 456;

JOINs and multi-table reads — SELECT JOIN aggregate query examples

-- inner join example: customers with recent orders
SELECT c.id, c.name, o.id AS order_id, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.created_at > CURRENT_DATE - INTERVAL '30 days';

-- left join with aggregate
SELECT c.id, c.name, COUNT(o.id) AS order_count, COALESCE(SUM(o.total),0) AS total_spent
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;

Transactions and upserts — SQL transaction and indexing examples

-- transaction: transfer balance example
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- upsert (Postgres syntax)
INSERT INTO products (sku, name, price)
VALUES ('P1001','Widget',19.99)
ON CONFLICT (sku) DO UPDATE
SET price = EXCLUDED.price;

CRUD Query Checklist (named framework)

Use the CRUD Query Checklist before running any modifying statement:

1. Validate filters: confirm WHERE clauses target intended rows.
2. Test with SELECT: run a SELECT with the same WHERE to preview affected rows.
3. Wrap in a transaction: use BEGIN/COMMIT for grouped changes.
4. Use LIMIT on test runs: reduce risk during verification.
5. Log and backup: ensure recent backups exist before large operations.

Real-world scenario

Scenario: A small online store needs to list customers with total orders and remove stale carts older than 90 days.

-- total orders per customer
SELECT c.id, c.email, COUNT(o.id) AS orders_count, SUM(o.total) AS lifetime_value
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.email
ORDER BY lifetime_value DESC;

-- remove stale carts (use transaction)
BEGIN;
DELETE FROM carts WHERE updated_at < NOW() - INTERVAL '90 days';
COMMIT;

Practical tips

• Always run a SELECT with the same WHERE before UPDATE/DELETE to confirm the result set.
• Use parameterized queries (prepared statements) to prevent SQL injection and improve performance.
• Read execution plans with EXPLAIN to find slow operations before adding indexes.
• Prefer explicit column lists in INSERT and SELECT to avoid schema-coupled bugs.

Trade-offs and common mistakes

Common mistakes can cause data loss or performance issues. Key trade-offs:

• Hard delete vs soft delete: hard deletes free storage but remove recovery options; soft deletes preserve data but require filtering logic.
• Indexes speed reads but slow writes and increase storage; add indexes selectively based on query patterns.
• Complex JOINs and subqueries can be readable but may perform worse than denormalized or pre-aggregated data for large datasets.

Frequent mistakes: missing WHERE in UPDATE/DELETE, not testing queries on a copy of production data, and constructing SQL with string concatenation that invites injection.

Resources

For authoritative syntax and behavior, consult the official database documentation such as the PostgreSQL manual: PostgreSQL documentation.

FAQ

What are the most common SQL queries?

The most common SQL queries are SELECT for reads, INSERT for creating rows, UPDATE for modifying rows, DELETE for removing rows, plus JOINs for combining tables, aggregate queries (COUNT, SUM, AVG), and transaction blocks (BEGIN/COMMIT) to ensure atomic changes.

How can SELECT queries be optimized?

Use proper indexes on filtered and joined columns, avoid SELECT *, limit returned rows, read EXPLAIN plans, and rewrite queries to reduce expensive operations like large sorts and full table scans.

How to safely perform UPDATE and DELETE?

Preview affected rows with SELECT using the same WHERE clause, wrap multiple changes in transactions, and keep backups or use a soft-delete pattern for recoverability.

When should transactions be used?

Use transactions whenever multiple statements must succeed or fail together, for financial updates, inventory changes, or any operation where partial completion leads to inconsistency.

How to handle errors and rollbacks in SQL?

Use ROLLBACK to undo changes after an error within a transaction. Implement application-level error handling that checks database error codes and applies compensating actions when necessary.