# The Ultimate SQL Cheatsheet and Guide for 2025

-- Create a new database
CREATE DATABASE db_name;

-- Switch to a database
USE db_name;

-- Delete a database
DROP DATABASE db_name;

-- Show all databases
SHOW DATABASES;

-- Create a table
CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  age INT,
  department_id INT
);

-- Show all tables
SHOW TABLES;

-- View table schema
DESCRIBE employees;

-- Delete a table
DROP TABLE employees;

-- Rename a table
RENAME TABLE employees TO staff;

-- Alter table
ALTER TABLE employees ADD salary DECIMAL(10,2);
ALTER TABLE employees DROP COLUMN salary;
ALTER TABLE employees MODIFY name VARCHAR(150);

-- Insert data
INSERT INTO employees (id, name, age, department_id)
VALUES (1, 'Alice', 30, 2);

-- Bulk insert
INSERT INTO employees (id, name, age, department_id)
VALUES
  (2, 'Bob', 25, 1),
  (3, 'Charlie', 35, 2);

-- Update data
UPDATE employees SET age = 32 WHERE name = 'Alice';

-- Delete data
DELETE FROM employees WHERE id = 2;

-- Select everything
SELECT * FROM employees;

-- Select specific columns
SELECT name, age FROM employees;

-- Aliases
SELECT name AS employee_name FROM employees;

-- DISTINCT values
SELECT DISTINCT department_id FROM employees;

-- WHERE clause
SELECT * FROM employees WHERE age > 30;

-- Comparison operators
=, !=, <, >, <=, >=

-- BETWEEN, IN, LIKE
SELECT * FROM employees WHERE age BETWEEN 25 AND 35;
SELECT * FROM employees WHERE department_id IN (1, 2);
SELECT * FROM employees WHERE name LIKE 'A%';

-- NULL checks
SELECT * FROM employees WHERE department_id IS NULL;

-- ORDER BY
SELECT * FROM employees ORDER BY age DESC;

-- LIMIT and OFFSET
SELECT * FROM employees LIMIT 10;
SELECT * FROM employees LIMIT 10 OFFSET 20;

-- COUNT, SUM, AVG, MIN, MAX
SELECT COUNT(*) FROM employees;
SELECT AVG(age) FROM employees;
SELECT MAX(salary) FROM employees;

-- GROUP BY
SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;

-- HAVING
SELECT department_id, COUNT(*) as total FROM employees GROUP BY department_id HAVING total > 2;

-- INNER JOIN
SELECT e.name, d.name AS department
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

-- LEFT JOIN
SELECT e.name, d.name AS department
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

-- RIGHT JOIN
SELECT e.name, d.name AS department
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;

-- FULL OUTER JOIN
SELECT e.name, d.name AS department
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.id;

-- CROSS JOIN
SELECT * FROM employees CROSS JOIN departments;

-- Subquery in SELECT
SELECT name, (SELECT COUNT(*) FROM employees) AS total_employees FROM employees;

-- Subquery in WHERE
SELECT * FROM employees WHERE department_id = (SELECT id FROM departments WHERE name = 'HR');

-- UNION (removes duplicates)
SELECT name FROM employees
UNION
SELECT name FROM customers;

-- UNION ALL (keeps duplicates)
SELECT name FROM employees
UNION ALL
SELECT name FROM customers;

-- INTERSECT (common rows)
SELECT name FROM employees
INTERSECT
SELECT name FROM customers;

-- EXCEPT (PostgreSQL / MSSQL)
SELECT name FROM employees
EXCEPT
SELECT name FROM customers;

-- Create view
CREATE VIEW senior_employees AS SELECT * FROM employees WHERE age > 30;

-- Query view
SELECT * FROM senior_employees;

-- Drop view
DROP VIEW senior_employees;

-- Begin a transaction
BEGIN;

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

-- Commit
COMMIT;

-- Rollback if needed
ROLLBACK;

-- Savepoint
SAVEPOINT sp1;
ROLLBACK TO sp1;
RELEASE SAVEPOINT sp1;

-- Create index
CREATE INDEX idx_name ON employees(name);

-- Drop index
DROP INDEX idx_name;

DELIMITER //
CREATE PROCEDURE GetEmployees()
BEGIN
  SELECT * FROM employees;
END //
DELIMITER ;

-- Call procedure
CALL GetEmployees();

CREATE TRIGGER before_insert_trigger
BEFORE INSERT ON employees
FOR EACH ROW
SET NEW.created_at = NOW();

-- Create user
CREATE USER 'kamal'@'localhost' IDENTIFIED BY 'password';

-- Grant permissions
GRANT ALL PRIVILEGES ON db_name.* TO 'kamal'@'localhost';

-- Revoke
REVOKE ALL PRIVILEGES ON db_name.* FROM 'kamal'@'localhost';

-- Delete user
DROP USER 'kamal'@'localhost';

# Export
mysqldump -u user -p dbname > backup.sql

# Import
mysql -u user -p dbname < backup.sql