1. Introduction to SQL
SQL (Structured Query Language) is the standard language used to store, retrieve, manipulate, and manage data in a relational database.
🔹 Full Form: Structured Query Language
🔹 Used In: MySQL, PostgreSQL, SQL Server, Oracle, etc.
🔹 Purpose: To interact with databases using queries.
🔹 Developed By: IBM (1970s), later adopted as ANSI standard (1986).
2. Features of SQL
✅ Easy to Learn & Use – Uses simple English-like statements.
✅ Powerful Query Language – Supports data retrieval, insertion, deletion, and updates.
✅ Highly Scalable – Works with small and large databases.
✅ Standardized Language – Used across different RDBMS (MySQL, SQL Server, etc.).
✅ Secure – Provides authentication, encryption, and role-based access.
✅ Supports Transactions – Ensures data consistency using ACID properties.
3. SQL Components (Categories of Commands)
SQL commands are classified into five categories:
| Category | Purpose | Examples |
|---|---|---|
| DDL (Data Definition Language) | Defines and modifies database structure | CREATE, ALTER, DROP, TRUNCATE |
| DML (Data Manipulation Language) | Manipulates (inserts, updates, deletes) data | INSERT, UPDATE, DELETE |
| DQL (Data Query Language) | Retrieves data from a database | SELECT |
| DCL (Data Control Language) | Controls user access | GRANT, REVOKE |
| TCL (Transaction Control Language) | Manages transactions | COMMIT, ROLLBACK, SAVEPOINT |
4. SQL Commands in Detail
A. Data Definition Language (DDL)
DDL is used to define the structure of a database (tables, indexes, etc.).
🔹 1. CREATE TABLE – Creates a new table.
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
Name VARCHAR(50),
Age INT,
Course VARCHAR(30)
);
🔹 2. ALTER TABLE – Modifies an existing table.
ALTER TABLE Students ADD Email VARCHAR(100);
🔹 3. DROP TABLE – Deletes a table permanently.
DROP TABLE Students;
🔹 4. TRUNCATE TABLE – Deletes all rows but keeps the table structure.
TRUNCATE TABLE Students;
B. Data Manipulation Language (DML)
DML is used to manipulate the data stored in a table.
🔹 1. INSERT INTO – Adds new records.
INSERT INTO Students (StudentID, Name, Age, Course)
VALUES (101, 'Alice', 22, 'CS');
🔹 2. UPDATE – Modifies existing records.
UPDATE Students SET Age = 23 WHERE StudentID = 101;
🔹 3. DELETE – Removes specific records.
DELETE FROM Students WHERE StudentID = 101;
C. Data Query Language (DQL)
DQL is used to retrieve data from tables.
🔹 SELECT – Fetches data from a table
SELECT * FROM Students;
🔹 SELECT with WHERE (Filter Data)
SELECT Name, Age FROM Students WHERE Course = 'CS';
🔹 SELECT with ORDER BY (Sorting Data)
SELECT Name, Age FROM Students ORDER BY Age DESC;
D. Data Control Language (DCL)
DCL is used to control user access to the database.
🔹 1. GRANT – Gives user permissions
GRANT SELECT, INSERT ON Students TO 'user1';
🔹 2. REVOKE – Removes user permissions
REVOKE INSERT ON Students FROM 'user1';
E. Transaction Control Language (TCL)
TCL is used to manage transactions in the database.
🔹 1. COMMIT – Saves changes permanently
COMMIT;
🔹 2. ROLLBACK – Undoes changes before commit
ROLLBACK;
🔹 3. SAVEPOINT – Creates a save state
SAVEPOINT SP1;
5. SQL Constraints
Constraints enforce rules on the data in tables.
| Constraint | Description |
|---|---|
| PRIMARY KEY | Ensures uniqueness of each record |
| FOREIGN KEY | Establishes relationship between tables |
| NOT NULL | Ensures column cannot have NULL values |
| UNIQUE | Ensures all values in a column are unique |
| CHECK | Ensures column satisfies a specific condition |
| DEFAULT | Assigns a default value to a column |
🔹 Example: Creating a table with constraints
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
Age INT CHECK (Age >= 18),
Course VARCHAR(30) DEFAULT 'Undecided'
);
6. SQL Joins (Combining Tables)
SQL Joins are used to fetch data from multiple tables based on a related column.
| Join Type | Description |
|---|---|
| INNER JOIN | Returns only matching records from both tables |
| LEFT JOIN | Returns all records from the left table and matching records from the right table |
| RIGHT JOIN | Returns all records from the right table and matching records from the left table |
| FULL JOIN | Returns all records from both tables |
🔹 Example:
SELECT Students.Name, Courses.CourseName
FROM Students
INNER JOIN Courses ON Students.CourseID = Courses.CourseID;
7. SQL Functions
SQL provides built-in functions to perform calculations on data.
A. Aggregate Functions
| Function | Description |
|---|---|
COUNT() | Counts the number of rows |
SUM() | Calculates the sum of values |
AVG() | Calculates the average value |
MAX() | Returns the maximum value |
MIN() | Returns the minimum value |
🔹 Example: Find the total number of students
SELECT COUNT(*) FROM Students;
B. String Functions
| Function | Description |
|---|---|
UPPER() | Converts text to uppercase |
LOWER() | Converts text to lowercase |
LENGTH() | Returns the length of a string |
CONCAT() | Combines two strings |
🔹 Example: Convert names to uppercase
SELECT UPPER(Name) FROM Students;
8. SQL Views
Views are virtual tables created from queries.
🔹 Example: Creating a View
CREATE VIEW CS_Students AS
SELECT Name, Age FROM Students WHERE Course = 'CS';
🔹 Accessing the View
SELECT * FROM CS_Students;
9. SQL Indexing
Indexes speed up searches in a table.
🔹 Example: Creating an index on StudentID
CREATE INDEX idx_student ON Students (StudentID);
10. Conclusion
SQL is a powerful query language for managing relational databases. By understanding DDL, DML, DQL, Joins, Constraints, and Functions, you can effectively store, retrieve, and manipulate data.