SQL (Structured Query Language) is the standard language used to communicate with relational databases. It allows users to create databases and tables, insert and manipulate data, query (retrieve) data, control access, and manage transactions. SQL is declarative — you describe · what · you want, not · how · to get it.
Categories of SQL Commands
SQL commands are divided into sub-languages based on their purpose:
- DDL (Data Definition Language): Defines or modifies database structure.
- CREATE — creates a database, table, or other object.
- ALTER — modifies an existing table (add/drop/modify column).
- DROP — permanently deletes a table or database.
- TRUNCATE — removes all rows from a table but keeps its structure.
- DML (Data Manipulation Language): Works with the actual data.
- INSERT — adds new rows.
- UPDATE — modifies existing rows.
- DELETE — removes rows.
- DQL (Data Query Language):
- SELECT — retrieves data (sometimes classed under DML).
- DCL (Data Control Language): Controls access rights.
- GRANT — gives privileges to a user.
- REVOKE — takes away privileges.
- TCL (Transaction Control Language): Manages transactions.
- COMMIT — permanently saves changes.
- ROLLBACK — undoes changes since the last COMMIT.
- SAVEPOINT — sets a point within a transaction to roll back to.
Creating a Table
CREATE TABLE Student (
RollNo INT PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
DOB DATE,
Marks DECIMAL(5,2),
Grade CHAR(1)
);
- Common Data Types:
- INT / INTEGER — whole numbers.
- VARCHAR(n) — variable-length text up to n characters.
- CHAR(n) — fixed-length text.
- DECIMAL(p, s) / FLOAT — decimal numbers; p = total digits, s = digits after decimal.
- DATE — date in YYYY-MM-DD format.
- Constraints:
- NOT NULL — column cannot be left empty.
- UNIQUE — all values in column must be distinct.
- PRIMARY KEY — uniquely identifies the row; implies NOT NULL + UNIQUE.
- FOREIGN KEY — references a primary key in another table.
- DEFAULT value — assigns a default when no value is given.
- CHECK(condition) — enforces a condition on column values.
Inserting Data
INSERT INTO Student VALUES (101, 'Priya Sharma', '2007-04-12', 87.5, 'A');
INSERT INTO Student (RollNo, Name) VALUES (102, 'Ankit Rao');
Updating Data
UPDATE Student SET Marks = 92.0 WHERE RollNo = 101;
UPDATE Student SET Grade = 'B' WHERE Marks ≥ 70 AND Marks < 85;
Deleting Data
DELETE FROM Student WHERE RollNo = 102;
The SELECT Statement
SELECT · FROM Student;
SELECT Name, Marks FROM Student WHERE Grade = 'A';
SELECT Name, Marks FROM Student ORDER BY Marks DESC;
SELECT Name, Marks FROM Student WHERE Marks BETWEEN 70 AND 90;
SELECT · FROM Student WHERE Name LIKE 'P%'; -- starts with P
SELECT · FROM Student WHERE Name LIKE '%ar%'; -- contains 'ar'
Aggregate Functions
- Aggregate functions compute a single result from multiple rows:
- COUNT( · ) — total number of rows.
- SUM(column) — total of numeric values.
- AVG(column) — arithmetic mean.
- MAX(column) — largest value.
- MIN(column) — smallest value.
SELECT COUNT( · ) FROM Student;
SELECT AVG(Marks) FROM Student WHERE Grade = 'A';
SELECT MAX(Marks), MIN(Marks) FROM Student;
GROUP BY and HAVING
GROUP BY groups rows sharing a common value; aggregate functions are then applied per group.
HAVING filters groups (used after GROUP BY); it is the group-level equivalent of WHERE.
SELECT Grade, COUNT( · ) AS Total, AVG(Marks) AS Average
FROM Student
GROUP BY Grade
HAVING AVG(Marks) > 75;
Joins
Joins combine rows from two or more tables based on a related column.
INNER JOIN (or simply JOIN): Returns only rows where there is a match in both tables.
SELECT S.Name, E.CourseName
FROM Student S
INNER JOIN Enrollment E ON S.RollNo = E.StudentID;
LEFT JOIN (LEFT OUTER JOIN): Returns all rows from the left table; unmatched rows from the right show NULL.
RIGHT JOIN (RIGHT OUTER JOIN): Returns all rows from the right table.
CROSS JOIN: Cartesian product — every row of Table A paired with every row of Table B.
Ordering of SELECT Clauses
The standard order is: SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ...
Common mistakes
- Writing HAVING without GROUP BY (HAVING must follow GROUP BY).
- Using WHERE on aggregate functions — use HAVING instead.
- Forgetting that DELETE without a WHERE clause deletes all rows.
- Confusing DROP TABLE (removes structure + data) with TRUNCATE (removes only data, structure remains) and DELETE (removes rows conditionally).
- Using single quotes for column names — column names have no quotes; only string values use single quotes.
Summary
SQL is the backbone of all relational database operations. Master the five categories (DDL, DML, DQL, DCL, TCL), the SELECT statement with all its clauses, aggregate functions, GROUP BY/HAVING, and the concept of joins. These topics are heavily tested in CBSE board examinations.