SQL Quick Reference

SELECT * FROM users;

SELECT id, name FROM users;

SELECT name AS full_name FROM users;

SELECT DISTINCT country FROM users;

SELECT * FROM users LIMIT 10;

SELECT * FROM users LIMIT 10 OFFSET 20;

SELECT * FROM users ORDER BY created_at DESC;

ORDER BY last_name, first_name

SELECT COUNT(*) FROM users;

WHERE country = 'IN'

WHERE status <> 'banned'

WHERE age >= 18

WHERE age >= 18 AND country = 'IN'

WHERE country IN ('IN', 'US', 'UK')

WHERE price BETWEEN 10 AND 20

WHERE email LIKE '%@gmail.com'

WHERE name ILIKE 'a%'  -- PostgreSQL, case-insensitive

WHERE deleted_at IS NULL

WHERE phone IS NOT NULL

WHERE id IN (SELECT user_id FROM orders)

WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = users.id)

SELECT u.name, o.total
FROM users u
JOIN orders o ON o.user_id = u.id;

SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

SELECT u.*
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

SELECT * FROM a FULL OUTER JOIN b ON a.id = b.id;

SELECT e.name, m.name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.id;

SELECT email FROM users UNION SELECT email FROM leads;

UNION ALL  -- keeps duplicates

SELECT COUNT(*), SUM(total), AVG(total), MIN(total), MAX(total)
FROM orders;

SELECT country, COUNT(*) AS users
FROM users
GROUP BY country;

SELECT country, COUNT(*)
FROM users
GROUP BY country
HAVING COUNT(*) > 100;

SELECT name,
  CASE WHEN age < 18 THEN 'minor' ELSE 'adult' END AS age_group
FROM users;

SELECT COALESCE(nickname, name) FROM users;

SELECT name, total,
  ROW_NUMBER() OVER (ORDER BY total DESC) AS rank
FROM orders;

SUM(total) OVER (ORDER BY created_at)

WITH big_orders AS (
  SELECT * FROM orders WHERE total > 1000
)
SELECT * FROM big_orders;

INSERT INTO users (name, email)
VALUES ('Ada', 'ada@example.com');

INSERT INTO users (name) VALUES ('Ada'), ('Grace'), ('Linus');

INSERT INTO archive SELECT * FROM orders WHERE created_at < '2024-01-01';

UPDATE users SET status = 'active' WHERE id = 7;

DELETE FROM sessions WHERE expires_at < NOW();

INSERT INTO users (email, name) VALUES ('a@x.com', 'Ada')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;

UPDATE users SET name = 'Ada' WHERE id = 1 RETURNING *;

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  name TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

user_id INTEGER REFERENCES users(id) ON DELETE CASCADE

ALTER TABLE users ADD COLUMN phone TEXT;

ALTER TABLE users RENAME COLUMN phone TO mobile;

ALTER TABLE users DROP COLUMN mobile;

CREATE INDEX idx_users_email ON users(email);

CREATE UNIQUE INDEX ...

DROP TABLE users;

DROP TABLE IF EXISTS users;

TRUNCATE TABLE logs;

CREATE VIEW active_users AS
SELECT * FROM users WHERE status = 'active';

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;  -- or ROLLBACK;

EXPLAIN SELECT * FROM users WHERE email = 'a@x.com';

EXPLAIN ANALYZE ...

CREATE USER app WITH PASSWORD 'secret';

GRANT SELECT, INSERT ON users TO app;

\dt               -- psql

SHOW TABLES;       -- MySQL

.tables            -- SQLite