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:
- Table: A collection of related records.
- Row: One record in a table.
- Column: An attribute of a record.
- Primary key: Uniquely identifies each row.
- Foreign key: Establishes a relationship between tables.
- Schema: Defines database structures and objects.
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
- Comparison: =, <>, >, <, >=, <=
- Logical: AND, OR, NOT
- Range: BETWEEN
- Matching: LIKE
- Membership: IN
- Missing values: IS NULL, IS NOT NULL
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.
- INNER JOIN: Matching rows from both tables.
- LEFT JOIN: All rows from the left table and matching rows from the right.
- RIGHT JOIN: All rows from the right table and matching rows from the left.
- FULL OUTER JOIN: All rows from either side, matched where possible.
- CROSS JOIN: Every combination of rows.
- SELF JOIN: A table joined to itself.
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
- PRIMARY KEY: Unique, non-null row identifier.
- FOREIGN KEY: Enforces a relationship to referenced data.
- NOT NULL: Prevents null values.
- UNIQUE: Enforces uniqueness.
- CHECK: Enforces a condition.
- DEFAULT: Supplies a default value.
16. Database Relationships
- One-to-one
- One-to-many
- Many-to-many, commonly represented using a junction table.
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.
- First Normal Form (1NF): Each field contains an atomic value; no repeating groups.
- Second Normal Form (2NF): In 1NF, with no partial dependency of a non-key attribute on part of a candidate key.
- Third Normal Form (3NF): In 2NF, with no inappropriate transitive dependency of non-key attributes on candidate keys.
- BCNF: Every determinant is a candidate key.
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:
- Atomicity: All operations succeed or are rolled back.
- Consistency: Constraints remain satisfied.
- Isolation: Concurrent transactions are controlled.
- Durability: Committed changes persist.
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:
- Data types and auto-generated IDs.
- Date/time and string functions.
- JSON support.
- Index types and query plans.
- Transactions and constraints.
- Backup, restore, and access control.
Do not assume every database supports identical syntax.
23. Query Performance
- Select only the columns required.
- Filter data appropriately.
- Index columns based on actual query patterns.
- Avoid unnecessary joins and repeated work.
- Inspect execution plans with EXPLAIN or the system's equivalent.
- Use parameterized queries in applications.
- Measure performance using representative data.
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
- Student Management Database
- Library Management System
- E-commerce Orders Database
- Employee Payroll Database
- Hospital Appointment Database
- Expense Tracker
- Sales Analytics Dashboard
For each project, design tables, choose keys, add constraints, insert sample data, and write meaningful queries.
26. Learning Roadmap
- Database basics and SELECT.
- Filtering, sorting, INSERT, UPDATE, and DELETE.
- Aggregate functions, GROUP BY, and HAVING.
- Joins and subqueries.
- Keys, constraints, and relationships.
- Normalization and database design.
- Indexes, transactions, and views.
- MySQL or PostgreSQL practice.
- Query optimization and security.
- 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
- How to Use Codify: A 7-Day Start Plan — New here? Pick a roadmap, make a study plan, start the DSA sheet, revise with notes, practise for placements and test yourself — one step a day, then a daily routine.
- Infosys Preparation Guide: Online Test, Pseudocode and Interview — Roles (SE, DSE, SP), what each section of the Infosys online test checks, how to crack pseudocode and reasoning, a 4-week plan and the interview.
- Wipro Elite NLTH Preparation: Aptitude, Essay and Coding — The Wipro fresher test round by round — quant, logical and verbal, the written essay, two coding problems — plus a 3-week plan and interview tips.
- Accenture Preparation Guide: Cognitive, Technical, Coding and Communication — Every Accenture round explained — cognitive, MS Office/networking/cloud technical MCQs, pseudocode, coding and the spoken-English test — with a 4-week plan.