Getting everything ready for you…

Loading courses, problems and your progress. This takes just a moment.

0s

Guides › Database & SQL

SQL Complete Roadmap: Beginner to Advanced

Learn SQL from scratch, including database fundamentals, queries, filtering, aggregate functions, joins, subqueries, database design, normalization, indexes, transactions, MySQL, and PostgreSQL.

SQL Complete Roadmap: Beginner to Advanced

Introduction

SQL (Structured Query Language) is used to store, retrieve, update, and manage data in relational databases. It is essential for backend development, data analytics, software engineering, and database administration.

1. Database Fundamentals

A database is an organized collection of data. A relational database stores data in tables made up of rows and columns.

Important terms:

2. SQL and Database Tools

Popular relational database systems include MySQL, PostgreSQL, SQLite, Microsoft SQL Server, and Oracle Database.

SQL is the language; MySQL and PostgreSQL are database management systems that implement SQL with their own features and syntax differences.

3. Create a Database and Table

Examples below use common SQL syntax. Exact commands can vary by database system.

CREATE DATABASE college;
CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT,
    city VARCHAR(50)
);

4. Insert Data

INSERT INTO students (id, name, age, city)
VALUES
    (1, 'Aman', 20, 'Jaipur'),
    (2, 'Priya', 21, 'Delhi'),
    (3, 'Ravi', 19, 'Mumbai');

5. Retrieve Data with SELECT

SELECT * FROM students;
SELECT name, city FROM students;

Prefer selecting the columns you need instead of always using SELECT * in production queries.

6. Filter Rows with WHERE

SELECT * FROM students WHERE age >= 20;
SELECT * FROM students WHERE city = 'Jaipur';
SELECT * FROM students WHERE age BETWEEN 18 AND 21;
SELECT * FROM students WHERE city IN ('Jaipur', 'Delhi');
SELECT * FROM students WHERE name LIKE 'A%';
SELECT * FROM students WHERE city IS NULL;

Use IS NULL or IS NOT NULL to check missing values; do not use = NULL.

7. Sort and Limit Results

SELECT * FROM students ORDER BY age DESC;
SELECT * FROM students ORDER BY name ASC;

Many systems support LIMIT:

SELECT * FROM students ORDER BY age DESC LIMIT 5;

SQL Server commonly uses TOP or OFFSET/FETCH instead.

8. Update and Delete Data

UPDATE students
SET city = 'Jaipur'
WHERE id = 2;
DELETE FROM students
WHERE id = 3;

Always check the WHERE condition before running UPDATE or DELETE. Without it, every row may be affected.

9. SQL Operators

10. Aggregate Functions

SELECT
    COUNT(*) AS total_students,
    AVG(age) AS average_age,
    MIN(age) AS youngest,
    MAX(age) AS oldest,
    SUM(age) AS sum_of_ages
FROM students;

Common functions: COUNT, SUM, AVG, MIN, MAX.

11. GROUP BY and HAVING

GROUP BY creates groups; HAVING filters groups after aggregation.

SELECT city, COUNT(*) AS student_count
FROM students
GROUP BY city
HAVING COUNT(*) >= 2;

WHERE filters individual rows before grouping, while HAVING filters grouped results.

12. SQL Joins

Joins combine related data from multiple tables.

Availability and syntax vary by database system; MySQL does not directly support FULL OUTER JOIN.

13. Subqueries

A subquery is a query nested inside another query.

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

Learn scalar subqueries, multi-row subqueries, correlated subqueries, EXISTS, and NOT EXISTS.

14. Set Operations

UNION combines result sets and removes duplicates. UNION ALL retains duplicates. INTERSECT and EXCEPT are available in many systems, but support varies.

The queries being combined generally need the same number of compatible columns.

15. Constraints

16. Database Relationships

Example:

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id)
);

17. Normalization

Normalization helps reduce unnecessary duplication and update anomalies.

Normalize thoughtfully; some systems deliberately denormalize for performance.

18. Indexes

Indexes can speed up reads and filtering but use storage and can slow down writes.

CREATE INDEX idx_students_city
ON students(city);

Learn single-column, composite, unique, and database-specific index types. Use query plans to determine whether an index helps.

19. Transactions and ACID

A transaction groups operations into a logical unit.

ACID:

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

If an error occurs, use ROLLBACK as appropriate. Transaction syntax and behavior vary across systems.

20. Views

A view is a named query that can be used like a virtual table.

CREATE VIEW jaipur_students AS
SELECT id, name
FROM students
WHERE city = 'Jaipur';

Learn when views improve readability, access control, and maintainability.

21. SQL Functions

Learn string, numeric, date/time, conversion, and null-handling functions. Names and behavior differ between MySQL and PostgreSQL.

Examples include UPPER, LOWER, ROUND, COALESCE, and date functions.

22. MySQL vs PostgreSQL

MySQL and PostgreSQL are widely used relational database systems.

Study:

Do not assume every database supports identical syntax.

23. Query Performance

24. SQL Security

Never build SQL by directly concatenating untrusted user input. Use parameterized queries, least-privilege database accounts, secure credentials, and appropriate access controls.

25. Practice Projects

  1. Student Management Database
  2. Library Management System
  3. E-commerce Orders Database
  4. Employee Payroll Database
  5. Hospital Appointment Database
  6. Expense Tracker
  7. Sales Analytics Dashboard

For each project, design tables, choose keys, add constraints, insert sample data, and write meaningful queries.

26. Learning Roadmap

  1. Database basics and SELECT.
  2. Filtering, sorting, INSERT, UPDATE, and DELETE.
  3. Aggregate functions, GROUP BY, and HAVING.
  4. Joins and subqueries.
  5. Keys, constraints, and relationships.
  6. Normalization and database design.
  7. Indexes, transactions, and views.
  8. MySQL or PostgreSQL practice.
  9. Query optimization and security.
  10. Build projects and solve interview questions.

Conclusion

Practice SQL consistently. Start with simple queries, understand why each result is produced, then progress to joins, data modeling, transactions, and optimization. Building real databases is the best way to connect theory with practical skills.

More guides