SQL Cheat Sheet

SELECT name, email FROM users;
SELECT * FROM users;
SELECT DISTINCT category FROM products;

SELECT * FROM users WHERE age > 18;
SELECT * FROM users WHERE name LIKE 'A%';
SELECT * FROM users WHERE age BETWEEN 18 AND 30;
SELECT * FROM users WHERE country IN ('US', 'UK', 'CA');
SELECT * FROM users WHERE email IS NOT NULL;

SELECT * FROM users ORDER BY created_at DESC;
SELECT * FROM users ORDER BY name ASC LIMIT 10;
SELECT * FROM users LIMIT 10 OFFSET 20;

SELECT first_name AS name, COUNT(*) AS total
FROM users u
JOIN orders o ON u.id = o.user_id;

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

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

SELECT u.name, o.id, p.name
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN products p ON o.product_id = p.id;

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

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

SELECT category, COUNT(*) AS cnt
FROM products
GROUP BY category
ORDER BY cnt DESC;

SELECT user_id, SUM(total) AS spent
FROM orders
GROUP BY user_id
HAVING SUM(total) > 1000;

SELECT name, salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank
FROM employees;

SELECT name, dept, salary,
  RANK() OVER (PARTITION BY dept ORDER BY salary DESC)
FROM employees;

SELECT date, amount,
  SUM(amount) OVER (ORDER BY date) AS running_total
FROM transactions;

SELECT date, revenue,
  LAG(revenue) OVER (ORDER BY date) AS prev_revenue,
  revenue - LAG(revenue) OVER (ORDER BY date) AS change
FROM daily_stats;

SELECT * FROM users
WHERE id IN (
  SELECT user_id FROM orders WHERE total > 100
);

WITH high_spenders AS (
  SELECT user_id, SUM(total) AS spent
  FROM orders
  GROUP BY user_id
  HAVING SUM(total) > 1000
)
SELECT u.name, hs.spent
FROM users u
JOIN high_spenders hs ON u.id = hs.user_id;

WITH RECURSIVE org AS (
  SELECT id, name, manager_id, 0 AS depth
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, o.depth + 1
  FROM employees e JOIN org o ON e.manager_id = o.id
)
SELECT * FROM org;

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 age INT;
ALTER TABLE users DROP COLUMN age;
ALTER TABLE users RENAME COLUMN name TO full_name;

CREATE INDEX idx_users_email ON users(email);
CREATE UNIQUE INDEX idx_users_username ON users(username);

INSERT INTO users (name, email)
VALUES ('Alice', 'alice@example.com');

INSERT INTO users (name, email) VALUES
  ('Bob', 'bob@example.com'),
  ('Carol', 'carol@example.com');

UPDATE users SET name = 'Alice B.' WHERE id = 1;
UPDATE products SET price = price * 1.1
WHERE category = 'electronics';

DELETE FROM users WHERE id = 1;
DELETE FROM sessions WHERE expires_at < NOW();

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