Sure!
Here's a comprehensive guide to SQL with important topics explained in a detailed and beginner-
friendly manner, including examples.
SQL (Structured Query Language) - Complete
Guide
SQL is used to interact with relational databases. It helps in storing, retrieving, and managing data.
1. Basic SQL Syntax
• SQL keywords are not case-sensitive ( SELECT , select , Select all work).
• Statements end with a semicolon ;
SELECT column1, column2 FROM table_name;
2. Data Types in SQL
• INT: Integer numbers
• VARCHAR(n): Text up to n characters
• CHAR(n): Fixed-length string
• DATE: Date values
• FLOAT/DOUBLE: Decimal values
3. SQL Commands Categories
A. DDL (Data Definition Language)
Used to define or modify database structure.
• CREATE
CREATE TABLE Students (
ID INT PRIMARY KEY,
Name VARCHAR(100),
Age INT
);
• ALTER
1
ALTER TABLE Students ADD Email VARCHAR(100);
• DROP
DROP TABLE Students;
• TRUNCATE
TRUNCATE TABLE Students;
B. DML (Data Manipulation Language)
Used to manage data in tables.
• INSERT
INSERT INTO Students (ID, Name, Age) VALUES (1, 'Alice', 20);
• UPDATE
UPDATE Students SET Age = 21 WHERE ID = 1;
• DELETE
DELETE FROM Students WHERE ID = 1;
C. DQL (Data Query Language)
Used to retrieve data.
• SELECT
SELECT * FROM Students;
SELECT Name, Age FROM Students WHERE Age > 18;
D. DCL (Data Control Language)
Used to control access.
• GRANT
GRANT SELECT ON Students TO user1;
• REVOKE
2
REVOKE SELECT ON Students FROM user1;
E. TCL (Transaction Control Language)
Manages transactions.
• COMMIT
• ROLLBACK
• SAVEPOINT
BEGIN;
UPDATE Students SET Age = 22 WHERE ID = 2;
SAVEPOINT S1;
DELETE FROM Students WHERE ID = 2;
ROLLBACK TO S1;
COMMIT;
4. SQL Clauses
• WHERE: Filters records
SELECT * FROM Students WHERE Age > 18;
• ORDER BY: Sorts results
SELECT * FROM Students ORDER BY Age DESC;
• GROUP BY: Groups rows sharing a value
SELECT Age, COUNT(*) FROM Students GROUP BY Age;
• HAVING: Like WHERE but used with GROUP BY
SELECT Age, COUNT(*) FROM Students GROUP BY Age HAVING COUNT(*) > 1;
• LIMIT: Limits number of rows
SELECT * FROM Students LIMIT 5;
3
5. Functions in SQL
Aggregate Functions:
• COUNT(), SUM(), AVG(), MIN(), MAX()
SELECT AVG(Age) FROM Students;
String Functions:
• UPPER(), LOWER(), LENGTH(), CONCAT()
SELECT UPPER(Name) FROM Students;
Date Functions:
• NOW(), CURDATE(), DATEDIFF(), YEAR(), MONTH()
SELECT CURDATE();
6. JOINS in SQL
Used to combine rows from two or more tables.
Types of Joins:
• INNER JOIN: Matches rows in both tables
SELECT A.Name, B.Marks FROM Students A
INNER JOIN Results B ON A.ID = B.StudentID;
• LEFT JOIN: All from left table + matched from right
• RIGHT JOIN: All from right table + matched from left
• FULL OUTER JOIN: All from both tables
7. Subqueries
A query inside another query.
SELECT Name FROM Students WHERE Age = (SELECT MAX(Age) FROM Students);
4
8. Constraints in SQL
Used to limit the type of data.
• PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK
CREATE TABLE Students (
ID INT PRIMARY KEY,
Age INT CHECK (Age >= 18)
);
9. Views
A virtual table based on a SELECT query.
CREATE VIEW TeenStudents AS
SELECT * FROM Students WHERE Age < 20;
10. Indexes
Used to speed up data retrieval.
CREATE INDEX idx_name ON Students(Name);
Let me know if you want this as a PDF or quiz-style revision too!
