SQL Cheat Sheet

SELECT * FROM users;

SELECT name, email FROM users;

SELECT * FROM users WHERE age > 18;

SELECT * FROM users WHERE age > 18 AND status = 'active';

SELECT * FROM users ORDER BY name ASC;

SELECT * FROM users LIMIT 10 OFFSET 20;

SELECT DISTINCT country FROM users;

SELECT u.name, o.total
FROM users u
INNER 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.total
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

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

SELECT COUNT(*) FROM users;

SELECT SUM(amount) FROM orders;

SELECT AVG(price) FROM products;

SELECT MIN(age), MAX(age) FROM users;

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

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

INSERT INTO users (name, email)
VALUES ('John', 'john@example.com');

INSERT INTO users (name, email) VALUES
('John', 'john@example.com'),
('Jane', 'jane@example.com');

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

DELETE FROM users WHERE id = 1;

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

ALTER TABLE users ADD COLUMN phone VARCHAR(20);

DROP TABLE IF EXISTS users;

TRUNCATE TABLE users;

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

WITH active_users AS (
  SELECT * FROM users WHERE status = 'active'
)
SELECT * FROM active_users WHERE age > 18;

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

CREATE INDEX idx_email ON users(email);

CREATE UNIQUE INDEX idx_email ON users(email);

CREATE INDEX idx_name_email ON users(name, email);

DROP INDEX idx_email ON users;

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

SELECT name, salary,
  RANK() OVER (ORDER BY salary DESC) as rank,
  DENSE_RANK() OVER (ORDER BY salary DESC) as dense_rank
FROM employees;

SELECT department, name, salary,
  ROW_NUMBER() OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) as dept_rank
FROM employees;

SELECT date, amount,
  LAG(amount, 1) OVER (ORDER BY date) as prev_amount,
  LEAD(amount, 1) OVER (ORDER BY date) as next_amount
FROM sales;

SELECT date, amount,
  SUM(amount) OVER (
    ORDER BY date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) as running_total
FROM sales;

SELECT name, salary,
  NTILE(4) OVER (ORDER BY salary) as quartile
FROM employees;

SELECT name,
  CASE status
    WHEN 'A' THEN 'Active'
    WHEN 'I' THEN 'Inactive'
    ELSE 'Unknown'
  END as status_name
FROM users;

SELECT name, salary,
  CASE
    WHEN salary > 100000 THEN 'High'
    WHEN salary > 50000 THEN 'Medium'
    ELSE 'Low'
  END as salary_band
FROM employees;

SELECT * FROM orders
WHERE status = CASE
  WHEN @filter = 'active' THEN 'pending'
  ELSE status
END;

SELECT * FROM products
ORDER BY
  CASE WHEN featured = 1 THEN 0 ELSE 1 END,
  name;

SELECT COALESCE(nickname, name, 'Unknown') as display_name
FROM users;

-- Returns NULL if equal, prevents division by zero
SELECT total / NULLIF(count, 0) as average
FROM stats;

SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;

-- MySQL
SELECT IFNULL(email, 'N/A') FROM users;

-- Oracle
SELECT NVL(email, 'N/A') FROM users;

BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;

BEGIN TRANSACTION;

UPDATE inventory SET qty = qty - 1 WHERE id = 1;

-- Something went wrong
ROLLBACK;

BEGIN TRANSACTION;

INSERT INTO orders (...) VALUES (...);
SAVEPOINT after_order;

INSERT INTO order_items (...) VALUES (...);
-- Oops, rollback just the items
ROLLBACK TO SAVEPOINT after_order;

COMMIT;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SELECT CONCAT(first_name, ' ', last_name) as full_name
FROM users;

SELECT SUBSTRING(name, 1, 3) FROM users;  -- First 3 chars
SELECT LEFT(name, 3) FROM users;           -- Same result
SELECT RIGHT(name, 3) FROM users;          -- Last 3 chars

SELECT TRIM(name) FROM users;
SELECT LTRIM(name) FROM users;  -- Left trim
SELECT RTRIM(name) FROM users;  -- Right trim

SELECT UPPER(name), LOWER(email) FROM users;

SELECT REPLACE(phone, '-', '') FROM users;

SELECT name, LENGTH(name) as name_length FROM users;