Common queries, joins, and database operations.
CREATE TABLECREATE TABLE users (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, email VARCHAR(255) NOT NULL UNIQUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);Data Typesprice DECIMAL(10,2), active BOOLEAN DEFAULT TRUE, meta JSONConstraintsCONSTRAINT chk_age CHECK (age >= 0 AND age <= 150)ALTER TABLEALTER TABLE users ADD COLUMN avatar VARCHAR(500), DROP COLUMN old_field, MODIFY email VARCHAR(320);CREATE INDEXCREATE INDEX idx_user_email ON users(email); CREATE INDEX idx_comp ON orders(user_id, status, created_at);FOREIGN KEY ... ON DELETEFOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE RESTRICTPartial / Functional indexesCREATE INDEX idx_active ON users(email) WHERE active = TRUE; -- PostgreSQLSELECT DISTINCTSELECT DISTINCT city, country FROM users ORDER BY country;WHERE vs HAVINGSELECT dept, COUNT(*) cnt FROM emp GROUP BY dept HAVING cnt > 5;LIKE / ILIKE / REGEXPWHERE email LIKE '%@gmail.com' AND name REGEXP '^[A-Z]'IN / NOT IN / EXISTSWHERE id IN (1,2,3) -- or -- WHERE EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id)BETWEEN ... ANDWHERE created_at BETWEEN '2024-01-01' AND '2024-12-31'COALESCE / NULLIF / IFNULLSELECT COALESCE(nickname, full_name, email) AS display_name FROM users;CASE WHEN ... ENDSELECT CASE WHEN score>=90 THEN 'A' WHEN score>=70 THEN 'B' ELSE 'C' END grade FROM students;LIMIT / OFFSET / FETCH NEXTSELECT * FROM posts ORDER BY id DESC LIMIT 20 OFFSET 40;INNER JOINSELECT p.title, u.name FROM posts p INNER JOIN users u ON p.user_id = u.id;LEFT (OUTER) JOINSELECT u.name, COUNT(o.id) orders FROM users u LEFT JOIN orders o ON o.user_id=u.id GROUP BY u.id;FULL OUTER JOINSELECT * FROM a FULL OUTER JOIN b ON a.id=b.a_id;CROSS JOINSELECT s.size, c.color FROM sizes s CROSS JOIN colors c;Self JOINSELECT e.name, m.name manager FROM employees e LEFT JOIN employees m ON e.manager_id=m.id;JOIN with USING(col)SELECT * FROM orders JOIN users USING(user_id);GROUP BY + aggregateSELECT category, COUNT(*), AVG(price), MAX(price) FROM products GROUP BY category;ROLLUP / CUBE / GROUPING SETSSELECT dept, job, SUM(sal) FROM emp GROUP BY ROLLUP(dept, job);ROW_NUMBER() OVER()SELECT *, ROW_NUMBER() OVER(PARTITION BY dept ORDER BY salary DESC) rn FROM employees;RANK() / DENSE_RANK()RANK() OVER(ORDER BY score DESC) -- ties get same rank, next skipsLAG() / LEAD()LAG(revenue, 1, 0) OVER(PARTITION BY region ORDER BY month) prev_revenueSUM ... OVER (running total)SUM(amount) OVER(ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)NTILE(n)NTILE(4) OVER(ORDER BY score DESC) AS quartileWITH cte AS (...)WITH top10 AS (SELECT * FROM products ORDER BY sales DESC LIMIT 10) SELECT * FROM top10 WHERE price > 100;Recursive CTEWITH RECURSIVE tree AS (SELECT * FROM cats WHERE parent IS NULL UNION ALL SELECT c.* FROM cats c JOIN tree t ON c.parent=t.id) SELECT * FROM tree;Correlated subquerySELECT * FROM orders o WHERE amount = (SELECT MAX(amount) FROM orders WHERE user_id=o.user_id);Lateral JOIN / CROSS APPLYFROM users, LATERAL (SELECT * FROM orders WHERE user_id=users.id ORDER BY date DESC LIMIT 3) recentEXPLAIN / EXPLAIN ANALYZEEXPLAIN ANALYZE SELECT * FROM orders WHERE status='pending';Index selectivitySELECT COUNT(DISTINCT status)/COUNT(*) selectivity FROM orders; -- low = bad index candidateBEGIN / COMMIT / ROLLBACKBEGIN; UPDATE accounts SET balance=balance-100 WHERE id=1; UPDATE accounts SET balance=balance+100 WHERE id=2; COMMIT;Isolation levelsSET TRANSACTION ISOLATION LEVEL REPEATABLE READ;UPSERT: INSERT ... ON CONFLICTINSERT INTO hits(date,count) VALUES(TODAY,1) ON DUPLICATE KEY UPDATE count=count+1;Covering indexCREATE INDEX idx_cover ON orders(user_id, status) INCLUDE (amount, created_at);JSON functionsSELECT meta->>'$.color' FROM products WHERE meta->>'$.size' = 'L';