SQL Cheat Sheet

-- SQL statements end with semicolons
SELECT column1, column2 FROM table_name WHERE condition;

INT, BIGINT, DECIMAL(10,2)
VARCHAR(255), TEXT
DATE, DATETIME, TIMESTAMP
BOOLEAN

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(100)
);

ALTER TABLE users ADD email VARCHAR(255);
DROP TABLE users;

INSERT INTO users (id, name) VALUES (1, 'Alice');

SELECT name, email FROM users;

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

DELETE FROM users WHERE id = 1;

SELECT * FROM users WHERE age > 18;

SELECT * FROM users ORDER BY age DESC;

SELECT department, COUNT(*) FROM employees GROUP BY department;

SELECT department, COUNT(*) 
FROM employees GROUP BY department 
HAVING COUNT(*) > 5;

SELECT * FROM users LIMIT 10 OFFSET 20;

SELECT DISTINCT department FROM employees;

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 * FROM a 
FULL OUTER JOIN b ON a.id = b.id;

SELECT * FROM products CROSS JOIN categories;

SELECT a.name, b.name 
FROM employees a, employees b 
WHERE a.manager_id = b.id;

SELECT COUNT(*) FROM users WHERE active = TRUE;

SELECT SUM(total) FROM orders WHERE status = 'completed';

SELECT AVG(salary) FROM employees;

SELECT MIN(age) FROM users;

SELECT MAX(score) FROM results;

-- In SELECT
SELECT name, (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u;

-- In FROM
SELECT avg_salary FROM (
  SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id
) AS dept_avg;

-- In WHERE
SELECT * FROM employees WHERE department_id IN (SELECT id FROM departments WHERE name = 'Sales');

SELECT name FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);

-- UNION
SELECT name FROM customers
UNION
SELECT name FROM vendors;

-- UNION ALL
SELECT name FROM customers
UNION ALL
SELECT name FROM vendors;

-- INTERSECT
SELECT name FROM customers
INTERSECT
SELECT name FROM vendors;

-- EXCEPT
SELECT name FROM customers
EXCEPT
SELECT name FROM vendors;

CREATE TABLE accounts (
  id INT PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  balance DECIMAL(10,2) DEFAULT 0.0,
  status VARCHAR(20) CHECK (status IN ('active', 'inactive')),
  user_id INT,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE INDEX idx_users_email ON users(email);
DROP INDEX idx_users_email;

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

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

-- Rollback example
BEGIN;
DELETE FROM users WHERE id = 99;
ROLLBACK;

-- Savepoint example
BEGIN;
SAVEPOINT sp1;
UPDATE accounts SET balance = 0;
ROLLBACK TO sp1;
COMMIT;

CREATE VIEW active_users AS
SELECT * FROM users WHERE active = TRUE;

ALTER VIEW active_users AS
SELECT id, name FROM users WHERE active = TRUE;

DROP VIEW active_users;

-- Procedure
CREATE PROCEDURE AddUser(IN username VARCHAR(50))
BEGIN
  INSERT INTO users(name) VALUES(username);
END;

CALL AddUser('John');

-- Function
CREATE FUNCTION GetUserCount() RETURNS INT
BEGIN
  DECLARE total INT;
  SELECT COUNT(*) INTO total FROM users;
  RETURN total;
END;

SELECT GetUserCount();

CREATE TRIGGER trg_before_insert
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
  SET NEW.created_at = NOW();
END;

CREATE TRIGGER trg_after_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
  INSERT INTO audit_log(user_id, change_date) VALUES(NEW.id, NOW());
END;

Aliases (AS)

SELECT name AS customer_name FROM users;

CASE Statements

SELECT name,
  CASE WHEN active THEN 'Active' ELSE 'Inactive' END AS status
FROM users;

NULL Handling

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

String Functions

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;
SELECT LENGTH(name) FROM users;

Date Functions

SELECT NOW();
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);
SELECT DATEDIFF('2024-12-31', '2024-01-01');