🗄️ SQL & Databases
SQL Complete Cheatsheet
From SELECT to window functions — master SQL for data and backend development.
📖 10 sections
⏱ 22 min read
✅ Quizzes included
🌙 Dark mode
01 Data Types & Tables ▼
INT/BIGINT
Whole numbers
VARCHAR(n)
Variable text up to n chars
TEXT
Unlimited text
DECIMAL(p,s)
Precise decimal — use for money
BOOLEAN
true/false
DATE/DATETIME
Dates and times
UUID
Universally unique ID
JSON
Structured JSON column
SQLCreate table
CREATE TABLE users (
  id         INT PRIMARY KEY AUTO_INCREMENT,
  email      VARCHAR(255) UNIQUE NOT NULL,
  name       VARCHAR(100) NOT NULL,
  age        INT CHECK (age >= 0),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
02 SELECT & CRUD ▼
SQLCRUD operations
-- Create
INSERT INTO users (email, name, age) VALUES ('ali@test.com', 'Ali', 22);

-- Read
SELECT * FROM users;
SELECT name, email FROM users;
SELECT DISTINCT country FROM users;

-- Update
UPDATE users SET age = 23 WHERE id = 1;

-- Delete
DELETE FROM users WHERE id = 5;
TRUNCATE TABLE users;   -- delete ALL rows fast
⚠️
Always use WHERE with DELETE and UPDATE. DELETE FROM users; removes every row!
03 WHERE & Filtering ▼
SQLFiltering
WHERE age > 18
WHERE age BETWEEN 18 AND 30
WHERE name IN ('Ali', 'Sara')
WHERE email LIKE '%@gmail.com'   -- ends with
WHERE email LIKE 'a%'            -- starts with a
WHERE name IS NULL
WHERE age > 18 AND country = 'PK'
ORDER BY name ASC;
ORDER BY age DESC, name ASC;
LIMIT 10;
LIMIT 10 OFFSET 20;   -- pagination
04 JOINs ▼
JOIN Types
SQLAll joins
-- INNER: only matching rows
SELECT u.name, o.total FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT: all left + matching right (NULLs for no match)
SELECT u.name, o.total FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- Self join
SELECT e.name, m.name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.id;
INNER JOIN
Intersection only
LEFT JOIN
All left + matched right
RIGHT JOIN
All right + matched left
CROSS JOIN
Every combination — be careful!
05 Aggregation ▼
SQLGROUP BY and aggregates
SELECT COUNT(*) FROM users;
SELECT AVG(age) FROM users;
SELECT SUM(total), MAX(price), MIN(price) FROM orders;

SELECT country, COUNT(*) AS cnt
FROM users
GROUP BY country
HAVING COUNT(*) > 100
ORDER BY cnt DESC;
💡
WHERE filters BEFORE grouping. HAVING filters AFTER grouping. Can't use aggregate functions in WHERE.
06 Subqueries ▼
SQLSubqueries
-- Scalar
SELECT name FROM users WHERE age > (SELECT AVG(age) FROM users);

-- IN
SELECT * FROM orders WHERE user_id IN (
  SELECT id FROM users WHERE country='PK'
);

-- EXISTS
SELECT * FROM users u WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);
07 Indexes ▼
SQLIndexes
CREATE INDEX idx_email ON users(email);
CREATE UNIQUE INDEX idx_email ON users(email);
CREATE INDEX idx_comp ON orders(user_id, created_at);

SHOW INDEXES FROM users;
DROP INDEX idx_email ON users;

-- Analyze query plan
EXPLAIN SELECT * FROM users WHERE email = 'test@test.com';
💡
Index columns in WHERE, JOIN ON, ORDER BY. Too many indexes slow INSERT/UPDATE.
08 Window Functions ▼
SQLWindow functions
SELECT name, salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
  RANK() OVER (ORDER BY salary DESC) AS rank_val
FROM employees;

-- Partition by department
SELECT name, department, salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC)
FROM employees;

-- Running total
SELECT date, amount,
  SUM(amount) OVER (ORDER BY date) AS running_total
FROM sales;
09 Transactions ▼
SQLTransactions (ACID)
BEGIN TRANSACTION;

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

COMMIT;   -- or ROLLBACK;

SAVEPOINT sp1;
ROLLBACK TO sp1;
Atomicity
All or nothing
Consistency
Data stays valid
Isolation
Concurrent transactions don't interfere
Durability
Committed data survives failures
10 Mini Quizzes ▼
❓ Quiz 1
What's the difference between WHERE and HAVING?
WHERE filters individual rows BEFORE grouping. HAVING filters groups AFTER GROUP BY. Can't use aggregate functions in WHERE.
❓ Quiz 2
Which JOIN returns ALL rows from the left table?
LEFT JOIN returns ALL rows from the left table. Non-matching right rows become NULL.