The Complete SQL Cheat Sheet: From Zero to Pro

This cheat sheet is designed for beginners to advanced learners who want a practical, real-world SQL reference for databases, data analysis, backend development, and data science. Each section includes short notes explaining when and why the command is used.

---

1. DATABASE LEVEL COMMANDS (DDL)

Create Database

CREATE DATABASE school;

Use case: Creates a new database to store tables and data.

Use Database

USE school;

Use case: Selects the database you want to work with.

Delete Database

DROP DATABASE school;

Use case: Permanently removes the database and all its data.

---

2. TABLE COMMANDS (DDL)

Create Table

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    marks FLOAT,
    city VARCHAR(30)
);

Use case: Defines the structure of data (columns, types, constraints).

Show Tables

SHOW TABLES;

Use case: Lists all tables inside the current database.

Describe Table

DESC students;

Use case: Shows column names, data types, and constraints.

Delete Table

DROP TABLE students;

Use case: Removes the table and its data permanently.

---

3. INSERT DATA (DML)

Insert Rows

INSERT INTO students VALUES
(1, 'Subin', 18, 85, 'Kathmandu'),
(2, 'Ram', 19, 72, 'Pokhara');

Use case: Adds new records to a table.

Insert Specific Columns

INSERT INTO students (id, name, marks)
VALUES (3, 'Sita', 90);

Use case: Insert data when some columns can be left NULL or have defaults.

---

4. SELECT (MOST USED)

SELECT * FROM students;
SELECT name, marks FROM students;

Use case: Retrieves data from tables. This is the most frequently used SQL command.

---

5. WHERE + CONDITIONS

WHERE age > 18
WHERE marks >= 60
WHERE city = 'Kathmandu'

Use case: Filters rows based on conditions.

AND / OR

WHERE age > 18 AND marks > 70
WHERE city = 'Kathmandu' OR city = 'Pokhara'

Use case: Combine multiple conditions.

---

6. IN / NOT IN

WHERE city IN ('Kathmandu', 'Pokhara')
WHERE city NOT IN ('Butwal', 'Biratnagar')

Use case: Cleaner alternative to multiple OR conditions.

---

7. BETWEEN / NOT BETWEEN

WHERE marks BETWEEN 40 AND 80
WHERE age NOT BETWEEN 18 AND 25

Use case: Filters values within or outside a range.

---

8. LIKE (PATTERN MATCHING)

LIKE 'S%'      -- starts with S
LIKE '%a'      -- ends with a
LIKE '%th%'    -- contains "th"

Use case: Text searching and partial matches.

---

9. NULL CHECK

WHERE marks IS NULL
WHERE marks IS NOT NULL

Use case: Checks for missing or available values.

---

10. ORDER BY

ORDER BY marks ASC
ORDER BY marks DESC
ORDER BY marks DESC, age ASC

Use case: Sorts query results.

---

11. LIMIT / OFFSET

LIMIT 5
LIMIT 5 OFFSET 10

Use case: Pagination and top-N queries.

---

12. DISTINCT

SELECT DISTINCT city FROM students;

Use case: Removes duplicate values from results.

---

13. AGGREGATE FUNCTIONS

COUNT(*)
SUM(marks)
AVG(marks)
MAX(marks)
MIN(marks)

Example:

SELECT AVG(marks) FROM students;

Use case: Performs calculations on multiple rows.

---

14. GROUP BY

SELECT city, AVG(marks)
FROM students
GROUP BY city;

Use case: Groups rows to apply aggregate functions per category.

---

15. HAVING

SELECT city, AVG(marks)
FROM students
GROUP BY city
HAVING AVG(marks) > 70;

Use case: Filters grouped data (used with aggregates).

WHERE filters rows, HAVING filters groups.

---

16. UPDATE

UPDATE students
SET marks = 90
WHERE id = 1;

Use case: Modifies existing data. Always use WHERE.

---

17. DELETE

DELETE FROM students WHERE id = 2;

Delete all rows:

DELETE FROM students;

Use case: Removes data from a table.

---

18. TRUNCATE

TRUNCATE TABLE students;

Use case: Fast way to delete all rows (cannot be rolled back in most DBs).

---

19. CONSTRAINTS

PRIMARY KEY
FOREIGN KEY
UNIQUE
NOT NULL
DEFAULT
CHECK

Example:

age INT CHECK (age >= 0)

Use case: Enforces data integrity.

---

20. JOINS

Tables:
students(id, name)
orders(student_id, course)

INNER JOIN

SELECT s.name, o.course
FROM students s
INNER JOIN orders o
ON s.id = o.student_id;

Use case: Returns matching records from both tables.

LEFT JOIN

SELECT s.name, o.course
FROM students s
LEFT JOIN orders o
ON s.id = o.student_id;

Use case: Returns all left table rows even if no match exists.

RIGHT JOIN

SELECT s.name, o.course
FROM students s
RIGHT JOIN orders o
ON s.id = o.student_id;

---

21. SUBQUERIES

SELECT name
FROM students
WHERE marks > (SELECT AVG(marks) FROM students);

Use case: Query inside another query for advanced filtering.

---

22. CASE (IF–ELSE LOGIC)

SELECT name,
CASE
    WHEN marks >= 80 THEN 'A'
    WHEN marks >= 60 THEN 'B'
    ELSE 'C'
END AS grade
FROM students;

Use case: Conditional logic inside SQL queries.

---

23. INDEX

CREATE INDEX idx_city ON students(city);

Use case: Improves query performance on large tables.

---

24. VIEW

CREATE VIEW top_students AS
SELECT * FROM students WHERE marks > 80;

Use case: Saves reusable queries as virtual tables.

---

25. TRANSACTIONS

BEGIN;
UPDATE students SET marks = 95 WHERE id = 1;
ROLLBACK;
COMMIT;

Use case: Ensures data consistency in critical operations.

---

26. SQL EXECUTION ORDER

FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
LIMIT

Use case: Helps understand why some queries work and others fail.

---

SQL FOR DATA SCIENCE (REAL USE)

Frequently used commands:

• SELECT
• WHERE
• JOIN
• GROUP BY
• HAVING
• ORDER BY
• LIMIT
• SUBQUERY

---

ONE PRACTICE QUERY

SELECT city, COUNT(*) AS total, AVG(marks) AS avg_marks
FROM students
WHERE age BETWEEN 18 AND 25
GROUP BY city
HAVING AVG(marks) > 70
ORDER BY avg_marks DESC
LIMIT 5;

---

WHAT TO MASTER FIRST

1. SELECT + WHERE
2. GROUP BY + HAVING
3. JOINs
4. Subqueries
5. CASE

---

This cheat sheet is suitable for blogs, documentation, and revision before interviews.