SQL Cheat Sheet

Data Definition Language (DDL)

Command	Example
`CREATE DATABASE`	`CREATE DATABASE company;`
`DROP DATABASE`	`DROP DATABASE company;`
`CREATE TABLE`	`CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50));`
`ALTER TABLE`	`ALTER TABLE users ADD email VARCHAR(100);`
`DROP TABLE`	`DROP TABLE users;`
`TRUNCATE TABLE`	`TRUNCATE TABLE users;`
`CREATE INDEX`	`CREATE INDEX idx_name ON users(name);`
`DROP INDEX`	`DROP INDEX idx_name;`

Data Query Language (DQL)

Command	Example
`SELECT DISTINCT`	`SELECT DISTINCT department FROM employees;`
`WHERE`	`SELECT * FROM users WHERE age BETWEEN 18 AND 65;`
`ORDER BY`	`SELECT * FROM users ORDER BY name ASC, age DESC;`
`GROUP BY`	`SELECT department, COUNT(*) FROM employees GROUP BY department;`
`HAVING`	`SELECT department, COUNT() FROM employees GROUP BY department HAVING COUNT() > 5;`
`LIMIT/TOP`	`SELECT * FROM users LIMIT 10;` / `SELECT TOP 10 * FROM users;`

Aggregate Functions

Command	Example
`COUNT`	`SELECT COUNT(*) FROM users;`
`SUM`	`SELECT SUM(salary) FROM employees;`
`AVG`	`SELECT AVG(age) FROM users;`
`MIN`	`SELECT MIN(price) FROM products;`
`MAX`	`SELECT MAX(created_date) FROM orders;`
`GROUP_CONCAT`	`SELECT department, GROUP_CONCAT(name) FROM employees GROUP BY department;`

Date Functions

Command	Example
`NOW/CURRENT_TIMESTAMP`	`SELECT NOW();`
`CURDATE/CURRENT_DATE`	`SELECT CURDATE();`
`DATE`	`SELECT DATE(created_at) FROM orders;`
`YEAR`	`SELECT YEAR(birth_date) FROM users;`
`MONTH`	`SELECT MONTH(order_date) FROM orders;`
`DAY`	`SELECT DAY(created_at) FROM posts;`
`DATEDIFF`	`SELECT DATEDIFF(end_date, start_date) FROM projects;`
`DATE_ADD`	`SELECT DATE_ADD(NOW(), INTERVAL 30 DAY);`

Subqueries

Command	Example
`IN`	`SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);`
`EXISTS`	`SELECT * FROM users WHERE EXISTS (SELECT 1 FROM orders WHERE user_id = users.id);`
`NOT EXISTS`	`SELECT * FROM users WHERE NOT EXISTS (SELECT 1 FROM orders WHERE user_id = users.id);`
`ANY/SOME`	`SELECT * FROM products WHERE price > ANY (SELECT price FROM products WHERE category = 'Electronics');`
`ALL`	`SELECT * FROM products WHERE price > ALL (SELECT price FROM products WHERE category = 'Books');`

Window Functions

Command	Example
`ROW_NUMBER`	`SELECT name, ROW_NUMBER() OVER (ORDER BY salary DESC) FROM employees;`
`RANK`	`SELECT name, RANK() OVER (ORDER BY salary DESC) FROM employees;`
`DENSE_RANK`	`SELECT name, DENSE_RANK() OVER (ORDER BY salary DESC) FROM employees;`
`NTILE`	`SELECT name, NTILE(4) OVER (ORDER BY salary) FROM employees;`
`LAG`	`SELECT name, salary, LAG(salary) OVER (ORDER BY hire_date) FROM employees;`
`LEAD`	`SELECT name, salary, LEAD(salary) OVER (ORDER BY hire_date) FROM employees;`

Transaction Control

Command	Example
`START TRANSACTION`	`START TRANSACTION;`
`COMMIT`	`COMMIT;`
`ROLLBACK`	`ROLLBACK;`
`SAVEPOINT`	`SAVEPOINT sp1;`
`ROLLBACK TO`	`ROLLBACK TO sp1;`

Data Manipulation Language (DML)

Command	Example
`SELECT`	`SELECT name, email FROM users WHERE age > 18;`
`INSERT`	`INSERT INTO users (name, email) VALUES ('John', '[email protected]');`
`UPDATE`	`UPDATE users SET email = '[email protected]' WHERE id = 1;`
`DELETE`	`DELETE FROM users WHERE age < 18;`
`REPLACE`	`REPLACE INTO users (id, name) VALUES (1, 'Jane');`
`MERGE`	`MERGE INTO target USING source ON condition WHEN MATCHED THEN UPDATE;`

Joins

Command	Example
`INNER JOIN`	`SELECT * FROM users u INNER JOIN orders o ON u.id = o.user_id;`
`LEFT JOIN`	`SELECT * FROM users u LEFT JOIN orders o ON u.id = o.user_id;`
`RIGHT JOIN`	`SELECT * FROM users u RIGHT JOIN orders o ON u.id = o.user_id;`
`FULL OUTER JOIN`	`SELECT * FROM users u FULL OUTER JOIN orders o ON u.id = o.user_id;`
`CROSS JOIN`	`SELECT * FROM users CROSS JOIN products;`
`SELF JOIN`	`SELECT e1.name, e2.name FROM employees e1 JOIN employees e2 ON e1.manager_id = e2.id;`

String Functions

Command	Example
`CONCAT`	`SELECT CONCAT(first_name, ' ', last_name) FROM users;`
`SUBSTRING`	`SELECT SUBSTRING(name, 1, 3) FROM users;`
`LENGTH`	`SELECT LENGTH(name) FROM users;`
`UPPER`	`SELECT UPPER(name) FROM users;`
`LOWER`	`SELECT LOWER(email) FROM users;`
`TRIM`	`SELECT TRIM(name) FROM users;`
`REPLACE`	`SELECT REPLACE(email, '@old.com', '@new.com') FROM users;`
`LIKE`	`SELECT * FROM users WHERE name LIKE 'J%';`

Conditional Expressions

Command	Example
`CASE`	`SELECT name, CASE WHEN age >= 18 THEN 'Adult' ELSE 'Minor' END FROM users;`
`IF`	`SELECT IF(salary > 50000, 'High', 'Low') FROM employees;`
`COALESCE`	`SELECT COALESCE(phone, email, 'No contact') FROM users;`
`NULLIF`	`SELECT NULLIF(department, 'Unknown') FROM employees;`
`ISNULL/IFNULL`	`SELECT IFNULL(middle_name, 'N/A') FROM users;`

Set Operations

Command	Example
`UNION`	`SELECT name FROM customers UNION SELECT name FROM suppliers;`
`UNION ALL`	`SELECT name FROM customers UNION ALL SELECT name FROM suppliers;`
`INTERSECT`	`SELECT name FROM customers INTERSECT SELECT name FROM suppliers;`
`EXCEPT/MINUS`	`SELECT name FROM customers EXCEPT SELECT name FROM suppliers;`

Data Control Language (DCL)

Command	Example
`GRANT`	`GRANT SELECT, INSERT ON users TO 'username'@'localhost';`
`REVOKE`	`REVOKE INSERT ON users FROM 'username'@'localhost';`
`CREATE USER`	`CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';`
`DROP USER`	`DROP USER 'username'@'localhost';`
`SET PASSWORD`	`SET PASSWORD FOR 'username'@'localhost' = PASSWORD('newpassword');`

Constraints

Command	Example
`PRIMARY KEY`	`CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50));`
`FOREIGN KEY`	`CREATE TABLE orders (id INT, user_id INT, FOREIGN KEY (user_id) REFERENCES users(id));`
`UNIQUE`	`CREATE TABLE users (id INT, email VARCHAR(100) UNIQUE);`
`NOT NULL`	`CREATE TABLE users (id INT NOT NULL, name VARCHAR(50) NOT NULL);`
`CHECK`	`CREATE TABLE users (id INT, age INT CHECK (age >= 0));`
`DEFAULT`	`CREATE TABLE users (id INT, status VARCHAR(20) DEFAULT 'active');`

Common WHERE Clauses

Command	Example
`=, !=, <>`	`SELECT * FROM users WHERE age = 25;`
`>, <, >=, <=`	`SELECT * FROM products WHERE price > 100;`
`BETWEEN`	`SELECT * FROM users WHERE age BETWEEN 18 AND 65;`
`IN`	`SELECT * FROM users WHERE city IN ('New York', 'London', 'Tokyo');`
`LIKE`	`SELECT * FROM users WHERE name LIKE 'J%';`
`IS NULL`	`SELECT * FROM users WHERE phone IS NULL;`
`AND, OR, NOT`	`SELECT * FROM users WHERE age > 18 AND city = 'New York';`