Basic SQL: SELECT, INSERT, UPDATE, DELETE

After this lesson, you will be able to: Use SELECT, FROM, WHERE, ORDER BY, LIMIT to query tables; INSERT, UPDATE, DELETE to mutate them.

The four basic SQL operations cover 90% of daily work. Get these clean and the rest builds easily.

Prerequisites:Relational Database Basics

SELECT essentials

Read data from tables.

sql

-- Everything
SELECT * FROM users;

-- Specific columns
SELECT id, email, name FROM users;

-- With alias
SELECT name AS user_name FROM users;

-- Filtering
SELECT * FROM users WHERE created_at >= '2026-01-01';
SELECT * FROM posts WHERE published = TRUE;

-- Sorting
SELECT * FROM users ORDER BY created_at DESC;
SELECT * FROM posts ORDER BY published DESC, created_at DESC;

-- Limiting
SELECT * FROM users LIMIT 10;
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;   -- page 3 of 10

INSERT, UPDATE, DELETE

Mutating data.

sql

-- INSERT
INSERT INTO users (email, name) VALUES ('[email protected]', 'Sam');

-- Multi-row INSERT
INSERT INTO users (email, name) VALUES
  ('[email protected]', 'A'),
  ('[email protected]', 'B'),
  ('[email protected]', 'C');

-- INSERT and return the row (Postgres-specific, very handy)
INSERT INTO users (email, name) VALUES ('[email protected]', 'Jordan')
RETURNING id, email;

-- UPDATE, ALWAYS include WHERE or you'll update every row
UPDATE users SET name = 'Alex Chen' WHERE id = 1;
UPDATE posts SET published = TRUE WHERE user_id = 1 AND published = FALSE;

-- DELETE, same warning, WHERE matters
DELETE FROM posts WHERE published = FALSE AND created_at < '2025-01-01';

-- Truncate empties a whole table fast (no WHERE)
TRUNCATE TABLE temp_logs;

Common mistakes

`SELECT *` in production code (returns more than you need; breaks when schema changes). Forgetting WHERE on UPDATE/DELETE (immortalized disaster). Using OFFSET for big paginations (slow, use keyset pagination instead). Sorting on unindexed columns at scale (full table scans).

←Relational Database Basics

Back to SQL

Filtering and Sorting→