SQL Cheatsheet — Query Quick Reference

SELECT col1, col2 FROM table WHERE condition ORDER BY col1 DESC LIMIT 10;
SELECT DISTINCT city FROM customers;
SELECT * FROM products WHERE price BETWEEN 10 AND 50;
SELECT * FROM orders WHERE created_at > '2024-01-01';
SELECT * FROM users WHERE email LIKE '%@example.com';
SELECT * FROM items WHERE id IN (1, 3, 5);

SELECT o.id, c.name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;

SELECT dept_id, COUNT(*) AS emp_count, AVG(salary) AS avg_sal
FROM employees
WHERE active = 1
GROUP BY dept_id
HAVING COUNT(*) > 5
ORDER BY emp_count DESC;

INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
INSERT INTO logs (msg) VALUES ('start'), ('stop');  -- multi-row

UPDATE users SET email = 'new@example.com' WHERE id = 1;

DELETE FROM users WHERE id = 1;
TRUNCATE TABLE logs;          -- faster, no rollback

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

ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users ALTER COLUMN name SET NOT NULL;
DROP TABLE users;

CREATE INDEX idx_email ON users(email);
CREATE UNIQUE INDEX idx_unq_email ON users(email);
CREATE INDEX idx_name_email ON users(name, email);  -- composite
DROP INDEX idx_email;

-- Strings
UPPER(name); LOWER(name); LENGTH(name);
TRIM(name); SUBSTRING(name, 1, 3);
REPLACE(name, 'old', 'new'); CONCAT(first, ' ', last);

-- Dates
NOW(); CURDATE(); DATE(created_at);
YEAR(created_at); DATE_ADD(created_at, INTERVAL 7 DAY);
DATEDIFF(NOW(), created_at);