SQL cheat sheet

SELECT name, price FROM products
WHERE category = 'Office' AND price BETWEEN 1 AND 50
ORDER BY price DESC, name
LIMIT 10 OFFSET 20;                    -- SQL Server: OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY
 
SELECT DISTINCT city FROM customers;
SELECT * FROM products WHERE name LIKE 'M%';          -- % any chars, _ one char
SELECT * FROM customers WHERE city IN ('London', 'Boston');
SELECT * FROM orders WHERE shipped_at IS NULL;         -- never = NULL
SELECT name, COALESCE(nickname, name) AS display FROM users;
SELECT name, CASE WHEN stock = 0 THEN 'sold out' ELSE 'in stock' END AS availability FROM products;

SELECT o.id, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id;              -- INNER: matches only
 
SELECT c.name, o.id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;            -- all customers, NULLs if no order
 
SELECT c.name FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;                                    -- customers without orders

SELECT category, COUNT(*) AS products, AVG(price) AS avg_price, MAX(price)
FROM products
WHERE stock > 0                 -- filter rows (before grouping)
GROUP BY category
HAVING COUNT(*) > 2             -- filter groups (after grouping)
ORDER BY products DESC;

SELECT name FROM products WHERE price > (SELECT AVG(price) FROM products);
SELECT name FROM customers WHERE id IN (SELECT customer_id FROM orders WHERE status = 'pending');
 
WITH revenue AS (
  SELECT order_id, SUM(quantity * unit_price) AS total FROM order_items GROUP BY order_id
)
SELECT * FROM revenue WHERE total > 100;

INSERT INTO products (name, category, price, stock) VALUES ('Lamp', 'Furniture', 39.99, 12);
UPDATE products SET price = price * 0.9 WHERE category = 'Furniture';   -- ALWAYS a WHERE
DELETE FROM orders WHERE status = 'cancelled';

CREATE TABLE orders (
  id          INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  status      TEXT NOT NULL DEFAULT 'pending',
  total       DECIMAL(10,2) CHECK (total >= 0)
);
ALTER TABLE orders ADD COLUMN shipped_at TIMESTAMP NULL;
CREATE INDEX ix_orders_customer ON orders (customer_id, status);
CREATE UNIQUE INDEX ux_users_email ON users (email);
DROP TABLE temp_import;

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;                          -- or ROLLBACK;
 
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 1;   -- SQLite
EXPLAIN ANALYZE SELECT ...;                                      -- PostgreSQL