SQL Handbook — From Zero to Hero

Table of Contents

1. SQL Basics

-- SELECT statement
SELECT * FROM students;
SELECT name, age FROM students;

-- WHERE clause
SELECT * FROM students WHERE age > 18;
SELECT * FROM students WHERE city = 'Lucknow';

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

-- LIMIT
SELECT * FROM students LIMIT 10;

2. JOINs

-- INNER JOIN (only matching rows)
SELECT s.name, c.course_name
FROM students s
INNER JOIN enrollments e ON s.id = e.student_id
INNER JOIN courses c ON e.course_id = c.id;

-- LEFT JOIN (all from left table)
SELECT s.name, e.course_id
FROM students s
LEFT JOIN enrollments e ON s.id = e.student_id;

-- RIGHT JOIN (all from right table)
SELECT s.name, e.course_id
FROM students s
RIGHT JOIN enrollments e ON s.id = e.student_id;

-- FULL OUTER JOIN (all rows from both)
SELECT s.name, e.course_id
FROM students s
FULL OUTER JOIN enrollments e ON s.id = e.student_id;

3. Aggregations

-- COUNT, SUM, AVG, MIN, MAX
SELECT COUNT(*) FROM students;
SELECT SUM(amount) FROM payments;
SELECT AVG(age) FROM students;

-- GROUP BY
SELECT city, COUNT(*) as student_count
FROM students
GROUP BY city;

-- HAVING (filter after GROUP BY)
SELECT city, COUNT(*) as student_count
FROM students
GROUP BY city
HAVING COUNT(*) > 5;

4. Window Functions

-- ROW_NUMBER()
SELECT name, age,
    ROW_NUMBER() OVER (ORDER BY age DESC) as rank
FROM students;

-- RANK() and DENSE_RANK()
SELECT name, marks,
    RANK() OVER (ORDER BY marks DESC) as rank,
    DENSE_RANK() OVER (ORDER BY marks DESC) as dense_rank
FROM students;

-- SUM() OVER()
SELECT name, amount,
    SUM(amount) OVER (ORDER BY date) as running_total
FROM payments;

-- LAG() and LEAD()
SELECT name, marks,
    LAG(marks) OVER (ORDER BY date) as prev_marks,
    LEAD(marks) OVER (ORDER BY date) as next_marks
FROM results;

5. Common Table Expressions (CTEs)

-- Basic CTE
WITH student_stats AS (
    SELECT city, AVG(age) as avg_age
    FROM students
    GROUP BY city
)
SELECT * FROM student_stats WHERE avg_age > 20;

-- Recursive CTE
WITH RECURSIVE employee_hierarchy AS (
    SELECT id, name, manager_id, 1 as level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id, eh.level + 1
    FROM employees e
    INNER JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy;

6. Subqueries

-- Subquery in WHERE
SELECT * FROM students
WHERE id IN (SELECT student_id FROM enrollments WHERE course_id = 1);

-- Subquery in FROM
SELECT city, avg_age
FROM (SELECT city, AVG(age) as avg_age FROM students GROUP BY city) as city_avg;

-- Correlated subquery
SELECT name, age
FROM students s1
WHERE age > (SELECT AVG(age) FROM students s2 WHERE s1.city = s2.city);

7. SQL Optimization

• Use indexes: Create indexes on frequently queried columns
• Avoid SELECT *: Only select columns you need
• Use EXPLAIN: Analyze query execution plan
• Optimize JOINs: Join on indexed columns
• Use LIMIT: For testing and pagination
• Avoid subqueries: Use JOINs or CTEs instead

8. SQL Projects for Resume

9. SQL Interview Questions

Download SQL Handbook PDF

Get the complete SQL Handbook as a free PDF. Enter your email to receive the download link.

Start Learning with DSWallah

Join 300+ students who transformed their careers with DSWallah. IIT-certified mentor, 50+ real projects, placement support.