Alright, let’s build a complete beginner-to-advanced guide to SQL step by step.
This will give you
everything you need to work with databases confidently.
SQL Complete Guide
1. Introduction to SQL
• SQL = Structured Query Language.
• Used to manage and query relational databases.
• Databases store data in tables (like Excel sheets).
Table structure:
• Columns = attributes (e.g., Name, Age).
• Rows = records (e.g., one student’s details).
2. SQL Basics
a) Create a Database & Table
CREATE DATABASE School;
USE School;
CREATE TABLE Students (
ID INT PRIMARY KEY,
Name VARCHAR(50),
Age INT,
Grade CHAR(1)
);
b) Insert Data
INSERT INTO Students (ID, Name, Age, Grade)
VALUES (1, 'Riya', 16, 'A'),
(2, 'Aarav', 17, 'B'),
(3, 'Meera', 16, 'A');
1
c) Select Data
-- All columns
SELECT * FROM Students;
-- Specific columns
SELECT Name, Grade FROM Students;
3. Filtering Data
WHERE clause
SELECT * FROM Students
WHERE Grade = 'A';
Operators
• = equal
• <> not equal
• > greater than
• < less than
• BETWEEN range
• LIKE pattern search
• IN list of values
SELECT * FROM Students WHERE Age BETWEEN 15 AND 17;
SELECT * FROM Students WHERE Name LIKE 'R%';
SELECT * FROM Students WHERE Grade IN ('A','B');
Sorting (ORDER BY)
SELECT * FROM Students ORDER BY Age DESC;
Limiting Results
SELECT * FROM Students LIMIT 2;
2
4. Updating & Deleting
-- Update
UPDATE Students SET Grade = 'B' WHERE ID = 1;
-- Delete
DELETE FROM Students WHERE ID = 2;
5. Joins (Working with Multiple Tables)
Suppose we have another table:
CREATE TABLE Courses (
CourseID INT PRIMARY KEY,
CourseName VARCHAR(50),
StudentID INT
);
INNER JOIN
SELECT Students.Name, Courses.CourseName
FROM Students
INNER JOIN Courses
ON Students.ID = Courses.StudentID;
LEFT JOIN
(Return all students, even if no course assigned)
SELECT Students.Name, Courses.CourseName
FROM Students
LEFT JOIN Courses
ON Students.ID = Courses.StudentID;
RIGHT JOIN
(Return all courses, even if no student enrolled)
SELECT Students.Name, Courses.CourseName
FROM Students
3
RIGHT JOIN Courses
ON Students.ID = Courses.StudentID;
FULL JOIN
SELECT Students.Name, Courses.CourseName
FROM Students
FULL OUTER JOIN Courses
ON Students.ID = Courses.StudentID;
6. Aggregate Functions
• COUNT() = number of rows
• SUM() = total
• AVG() = average
• MAX() = highest
• MIN() = lowest
SELECT COUNT(*) FROM Students;
SELECT AVG(Age) FROM Students;
SELECT MAX(Age) FROM Students;
GROUP BY
SELECT Grade, COUNT(*)
FROM Students
GROUP BY Grade;
HAVING (filter groups)
SELECT Grade, COUNT(*)
FROM Students
GROUP BY Grade
HAVING COUNT(*) > 1;
7. Constraints
• PRIMARY KEY = unique + not null
• FOREIGN KEY = links tables
4
• NOT NULL = must have value
• UNIQUE = value must not repeat
• DEFAULT = default value if not given
• CHECK = condition must be true
CREATE TABLE Teachers (
ID INT PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
Subject VARCHAR(30) UNIQUE,
Age INT CHECK (Age >= 21),
Salary DECIMAL(10,2) DEFAULT 30000
);
8. Views
(Like virtual tables)
CREATE VIEW A_Students AS
SELECT Name, Grade FROM Students WHERE Grade = 'A';
9. Indexes
(Speed up search)
CREATE INDEX idx_name ON Students(Name);
10. Subqueries
SELECT Name FROM Students
WHERE Age = (SELECT MAX(Age) FROM Students);
11. Transactions
BEGIN;
UPDATE Students SET Grade='A' WHERE ID=2;
5
ROLLBACK; -- undo
COMMIT; -- save
12. Advanced Topics
• Stored Procedures
• Triggers
• Functions
• Window Functions (e.g., ROW_NUMBER, RANK)
• CTEs (Common Table Expressions)
Example:
WITH TopStudents AS (
SELECT Name, Grade FROM Students WHERE Grade = 'A'
)
SELECT * FROM TopStudents;
✅ SQL Roadmap
1. Learn SELECT, INSERT, UPDATE, DELETE.
2. Practice WHERE, ORDER BY, LIMIT.
3. Learn Joins.
4. Get comfortable with Aggregate Functions + GROUP BY.
5. Explore Views, Indexes, Constraints.
6. Move to Advanced (CTE, Window Functions, Procedures).
