SQL Documentation
The language of relational databases — query, filter, join and group data.
SQL (Structured Query Language) is the standard language for working with relational databases — data stored in tables of rows and columns. The same core SQL works in MySQL, PostgreSQL, SQLite, SQL Server and Oracle. Every backend developer, data analyst and DBMS interview needs it.
1. Introduction & Setup
Pick any database to practise:
- SQLite — no server needed: install DB Browser for SQLite or run
sqlite3 school.db. - MySQL or PostgreSQL — install the server, or use a free online playground.
All examples on this page use this sample database (copy and run it once):
-- Sample database: departments and students
CREATE TABLE departments (
id INTEGER PRIMARY KEY,
name VARCHAR(50) NOT NULL UNIQUE
);
CREATE TABLE students (
id INTEGER PRIMARY KEY,
name VARCHAR(50) NOT NULL,
dept_id INTEGER REFERENCES departments(id),
city VARCHAR(50),
marks INTEGER CHECK (marks BETWEEN 0 AND 100),
joined DATE
);
INSERT INTO departments (id, name) VALUES (1, 'CSE'), (2, 'ECE'), (3, 'ME'), (4, 'Civil');
INSERT INTO students (id, name, dept_id, city, marks, joined) VALUES
(1, 'Asha', 1, 'Pune', 91, '2024-07-01'),
(2, 'Ravi', 2, 'Delhi', 78, '2024-07-03'),
(3, 'Neha', 1, 'Mumbai', 85, '2024-07-05'),
(4, 'Kabir', 3, 'Pune', 66, '2024-07-10'),
(5, 'Isha', 2, 'Delhi', 92, '2024-08-01'),
(6, 'Arjun', NULL, 'Jaipur', 55, '2024-08-15');
SELECT * FROM students;
Output:
id | name | dept_id | city | marks | joined
1 | Asha | 1 | Pune | 91 | 2024-07-01
2 | Ravi | 2 | Delhi | 78 | 2024-07-03
3 | Neha | 1 | Mumbai | 85 | 2024-07-05
4 | Kabir | 3 | Pune | 66 | 2024-07-10
5 | Isha | 2 | Delhi | 92 | 2024-08-01
6 | Arjun | NULL | Jaipur | 55 | 2024-08-15
SQL commands are grouped as DDL (CREATE, ALTER, DROP), DML (INSERT, UPDATE, DELETE), DQL (SELECT), DCL (GRANT, REVOKE) and TCL (COMMIT, ROLLBACK).
⚠️ Common error: strings need single quotes (
'Pune'). Double quotes mean identifiers (column/table names) in standard SQL and PostgreSQL.
2. SELECT Basics
SELECT name, marks FROM students;
name | marks
Asha | 91
Ravi | 78
Neha | 85
Kabir | 66
Isha | 92
Arjun | 55
Aliases, expressions, DISTINCT, sorting and limiting:
SELECT name AS student, marks + 5 AS with_bonus
FROM students
ORDER BY marks DESC
LIMIT 3;
SELECT DISTINCT city FROM students ORDER BY city;
student | with_bonus
Isha | 97
Asha | 96
Neha | 90
city
Delhi
Jaipur
Mumbai
Pune
The logical order in which a query runs: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. That is why you can't use a SELECT alias inside WHERE.
⚠️ Common error:
LIMITis MySQL/PostgreSQL/SQLite. SQL Server usesSELECT TOP 3 ..., Oracle usesFETCH FIRST 3 ROWS ONLY.
3. Filtering with WHERE
SELECT name, city, marks
FROM students
WHERE marks >= 80 AND city IN ('Pune', 'Delhi')
ORDER BY name;
name | city | marks
Asha | Pune | 91
Isha | Delhi | 92
SELECT name FROM students WHERE marks BETWEEN 60 AND 80 ORDER BY name; -- inclusive
SELECT name FROM students WHERE name LIKE 'A%' ORDER BY name; -- starts with A
SELECT name FROM students WHERE dept_id IS NULL; -- not "= NULL"
name
Kabir
Ravi
name
Arjun
Asha
name
Arjun
| Operator | Example |
|---|---|
= <> != < > <= >= |
marks <> 0 |
AND OR NOT |
NOT (city = 'Pune') |
IN (...) |
city IN ('Pune', 'Delhi') |
BETWEEN a AND b |
inclusive range |
LIKE |
% = any characters, _ = one character |
IS NULL / IS NOT NULL |
missing values |
⚠️ Common error:
WHERE dept_id = NULLreturns nothing — any comparison withNULLis unknown. UseIS NULL.
4. Functions & Expressions
SELECT
UPPER(name) AS upper_name,
LENGTH(name) AS len,
SUBSTR(city, 1, 3) AS city3,
ROUND(marks * 1.0 / 3, 2) AS third,
COALESCE(dept_id, 0) AS dept,
CASE
WHEN marks >= 90 THEN 'A'
WHEN marks >= 75 THEN 'B'
ELSE 'C'
END AS grade
FROM students
ORDER BY id
LIMIT 4;
upper_name | len | city3 | third | dept | grade
ASHA | 4 | Pun | 30.33 | 1 | A
RAVI | 4 | Del | 26.0 | 2 | B
NEHA | 4 | Mum | 28.33 | 1 | B
KABIR | 5 | Pun | 22.0 | 3 | C
Common functions (names vary slightly by database):
- Text:
UPPER,LOWER,LENGTH/LEN,SUBSTR/SUBSTRING,TRIM,REPLACE,CONCAT(or||in PostgreSQL/SQLite). - Numbers:
ROUND,ABS,CEIL,FLOOR,MOD. - Dates:
CURRENT_DATE,NOW(),EXTRACT(YEAR FROM d),DATE_ADD(MySQL),d + INTERVAL '7 days'(PostgreSQL). - NULL handling:
COALESCE(a, b),NULLIF(a, b).
⚠️ Common error: integer division —
marks / 3gives a whole number in PostgreSQL, SQL Server and SQLite. Multiply by1.0(or cast) first.
5. Aggregation: GROUP BY & HAVING
Aggregate functions: COUNT, SUM, AVG, MIN, MAX.
SELECT COUNT(*) AS total, COUNT(dept_id) AS with_dept, MAX(marks) AS top, MIN(marks) AS lowest
FROM students;
total | with_dept | top | lowest
6 | 5 | 92 | 55
SELECT city, COUNT(*) AS students, ROUND(AVG(marks), 1) AS avg_marks
FROM students
GROUP BY city
HAVING COUNT(*) >= 2 -- filter groups (WHERE filters rows)
ORDER BY avg_marks DESC;
city | students | avg_marks
Delhi | 2 | 85.0
Pune | 2 | 78.5
⚠️ Common error:
column "name" must appear in the GROUP BY clause— every selected column must be either grouped or inside an aggregate function.
6. Joins
Joins combine rows from two tables using a related column.
-- INNER JOIN: only students that have a matching department
SELECT s.name, d.name AS dept
FROM students s
JOIN departments d ON d.id = s.dept_id
ORDER BY s.id;
name | dept
Asha | CSE
Ravi | ECE
Neha | CSE
Kabir | ME
Isha | ECE
-- LEFT JOIN: every student, department if any
SELECT s.name, COALESCE(d.name, 'No dept') AS dept
FROM students s
LEFT JOIN departments d ON d.id = s.dept_id
WHERE s.marks < 70
ORDER BY s.name;
-- departments with no students (LEFT JOIN + IS NULL)
SELECT d.name
FROM departments d
LEFT JOIN students s ON s.dept_id = d.id
WHERE s.id IS NULL;
-- count students per department, including empty ones
SELECT d.name, COUNT(s.id) AS students
FROM departments d
LEFT JOIN students s ON s.dept_id = d.id
GROUP BY d.name
ORDER BY students DESC, d.name;
name | dept
Arjun | No dept
Kabir | ME
name
Civil
name | students
CSE | 2
ECE | 2
ME | 1
Civil | 0
| Join | Returns |
|---|---|
INNER JOIN |
matching rows in both tables |
LEFT JOIN |
all rows from the left table + matches (NULL if none) |
RIGHT JOIN |
all rows from the right table + matches |
FULL OUTER JOIN |
all rows from both (not in MySQL) |
CROSS JOIN |
every combination |
| self join | a table joined with itself (e.g. employee → manager) |
⚠️ Common error: forgetting the
ONcondition creates a cross join — rows × rows results.
7. Subqueries & CTEs
-- students scoring above the overall average
SELECT name, marks
FROM students
WHERE marks > (SELECT AVG(marks) FROM students)
ORDER BY marks DESC;
name | marks
Isha | 92
Asha | 91
Neha | 85
Ravi | 78
(The average is 467 / 6 ≈ 77.83, so Ravi's 78 also counts.)
-- correlated subquery: topper of each department
SELECT s.name, s.dept_id, s.marks
FROM students s
WHERE s.marks = (SELECT MAX(marks) FROM students x WHERE x.dept_id = s.dept_id)
ORDER BY s.dept_id;
-- the same idea with a CTE (WITH) — easier to read
WITH dept_avg AS (
SELECT dept_id, AVG(marks) AS avg_marks
FROM students
WHERE dept_id IS NOT NULL
GROUP BY dept_id
)
SELECT d.name, ROUND(a.avg_marks, 1) AS avg_marks
FROM dept_avg a
JOIN departments d ON d.id = a.dept_id
ORDER BY avg_marks DESC;
name | dept_id | marks
Asha | 1 | 91
Isha | 2 | 92
Kabir | 3 | 66
name | avg_marks
CSE | 88.0
ECE | 85.0
ME | 66.0
Also: EXISTS / NOT EXISTS, IN (subquery), and set operations UNION (removes duplicates), UNION ALL, INTERSECT, EXCEPT.
⚠️ Common error:
NOT IN (subquery)returns no rows if the subquery contains aNULL. PreferNOT EXISTS.
8. Modifying Data & Tables
INSERT INTO students (id, name, dept_id, city, marks, joined)
VALUES (7, 'Meera', 4, 'Pune', 88, '2024-09-01');
UPDATE students SET marks = marks + 2 WHERE city = 'Jaipur';
DELETE FROM students WHERE marks < 60;
SELECT id, name, city, marks FROM students WHERE id IN (6, 7);
id | name | city | marks
7 | Meera | Pune | 88
(Arjun's marks became 57 and he was then deleted because 57 < 60.)
Changing table structure:
ALTER TABLE students ADD COLUMN email VARCHAR(100);
ALTER TABLE students RENAME COLUMN city TO hometown; -- MySQL 8, PostgreSQL, SQLite 3.25+
CREATE TABLE archive AS SELECT * FROM students WHERE marks < 70;
DROP TABLE archive; -- deletes the table
TRUNCATE TABLE logs; -- removes all rows quickly (not SQLite)
Constraints keep data valid: PRIMARY KEY, FOREIGN KEY ... REFERENCES, UNIQUE, NOT NULL, CHECK, DEFAULT. Auto-increment ids: INT AUTO_INCREMENT (MySQL), SERIAL/GENERATED ALWAYS AS IDENTITY (PostgreSQL), INTEGER PRIMARY KEY (SQLite).
⚠️ Common error: running
UPDATEorDELETEwithoutWHEREchanges every row. Run the sameWHEREwith aSELECTfirst, and use transactions.
9. Indexes & Transactions
An index is a sorted lookup structure (usually a B-tree) that makes searches fast — like a book's index.
CREATE INDEX idx_students_city ON students (city);
CREATE UNIQUE INDEX idx_dept_name ON departments (name);
CREATE INDEX idx_city_marks ON students (city, marks); -- composite: helps WHERE city = ? AND marks > ?
- Index columns used in
WHERE,JOIN ... ONandORDER BY. - Indexes speed up reads but slow down writes and use space — don't index everything.
- Check how a query runs with
EXPLAIN(EXPLAIN ANALYZEin PostgreSQL).
A transaction groups statements so they all succeed or all fail (ACID: Atomicity, Consistency, Isolation, Durability).
CREATE TABLE accounts (id INTEGER PRIMARY KEY, owner TEXT, balance INTEGER CHECK (balance >= 0));
INSERT INTO accounts VALUES (1, 'Asha', 1000), (2, 'Ravi', 500);
BEGIN;
UPDATE accounts SET balance = balance - 300 WHERE id = 1;
UPDATE accounts SET balance = balance + 300 WHERE id = 2;
COMMIT; -- or ROLLBACK; to undo both
SELECT owner, balance FROM accounts;
owner | balance
Asha | 700
Ravi | 800
⚠️ Common error: wrapping a function around an indexed column (
WHERE YEAR(joined) = 2024) prevents the index from being used. WriteWHERE joined >= '2024-01-01' AND joined < '2025-01-01'.
10. Advanced Concepts
Window functions compute values across related rows without collapsing them (supported in MySQL 8+, PostgreSQL, SQLite 3.25+):
SELECT name, dept_id, marks,
RANK() OVER (ORDER BY marks DESC) AS overall_rank,
ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY marks DESC) AS rank_in_dept,
SUM(marks) OVER (ORDER BY id) AS running_total
FROM students
WHERE dept_id IS NOT NULL
ORDER BY id;
name | dept_id | marks | overall_rank | rank_in_dept | running_total
Asha | 1 | 91 | 2 | 1 | 91
Ravi | 2 | 78 | 4 | 2 | 169
Neha | 1 | 85 | 3 | 2 | 254
Kabir | 3 | 66 | 5 | 1 | 320
Isha | 2 | 92 | 1 | 1 | 412
Also: DENSE_RANK, LAG/LEAD (previous/next row), NTILE, moving averages with ROWS BETWEEN 2 PRECEDING AND CURRENT ROW.
Views save a query as a virtual table:
CREATE VIEW toppers AS
SELECT name, marks FROM students WHERE marks >= 85;
SELECT * FROM toppers ORDER BY marks DESC;
name | marks
Isha | 92
Asha | 91
Neha | 85
Normalisation removes duplicated data: 1NF (atomic values, no repeating groups), 2NF (no partial dependency on part of a composite key), 3NF (no transitive dependency — non-key columns depend only on the key), BCNF (every determinant is a candidate key).
Other topics: stored procedures and functions, triggers, GRANT/REVOKE, isolation levels (read committed, repeatable read, serializable), and preventing SQL injection with parameterised queries in your application code.
11. Examples with Output
Example 1 — Second highest marks
SELECT MAX(marks) AS second_highest
FROM students
WHERE marks < (SELECT MAX(marks) FROM students);
second_highest
91
Example 2 — Nth highest with DENSE_RANK (N = 3)
SELECT name, marks
FROM (
SELECT name, marks, DENSE_RANK() OVER (ORDER BY marks DESC) AS r
FROM students
) ranked
WHERE r = 3;
name | marks
Neha | 85
Example 3 — Monthly joins report
SELECT SUBSTR(joined, 1, 7) AS month, COUNT(*) AS joined_count
FROM students
GROUP BY month
ORDER BY month;
month | joined_count
2024-07 | 4
2024-08 | 2
Example 4 — Find duplicates
SELECT city, COUNT(*) AS n
FROM students
GROUP BY city
HAVING COUNT(*) > 1
ORDER BY city;
city | n
Delhi | 2
Pune | 2
12. Practice Problems
Use the sample database above:
- List students from Pune or Mumbai with marks above 80, highest first.
- Show each department's name with its number of students and average marks (include departments with no students).
- Find students who score more than their department's average.
- Find the second highest marks without using
LIMIT/OFFSET. - Show the topper of every department (handle ties).
- Give every student a grade (A/B/C) using
CASEand count students per grade. - Write a transaction that moves Kabir to the Civil department and gives him 5 bonus marks.
- Add an index that speeds up
WHERE city = ? ORDER BY marks DESCand check it withEXPLAIN.
More practice: DBMS interview questions and the DBMS notes.
13. Interview Questions
1. WHERE vs HAVING?
WHERE filters rows before grouping; HAVING filters groups after GROUP BY and can use aggregates.
2. DELETE vs TRUNCATE vs DROP?
DELETE removes chosen rows (can use WHERE, logged, can roll back); TRUNCATE quickly removes all rows; DROP removes the whole table and its structure.
3. Primary key vs unique key? Both enforce uniqueness. A table has one primary key (NOT NULL, identifies rows); it can have many unique keys, which may allow NULLs.
4. What is a foreign key? A column that references another table's primary key, enforcing referential integrity (no orphan rows).
5. Types of joins? INNER, LEFT, RIGHT, FULL OUTER, CROSS and self joins.
6. What is normalisation? Organising tables to remove redundancy and update anomalies — 1NF, 2NF, 3NF, BCNF.
7. What is an index and its trade-off? A data structure (usually a B-tree) that speeds up lookups; it costs extra storage and slows inserts/updates.
8. What are ACID properties? Atomicity (all or nothing), Consistency (valid state to valid state), Isolation (concurrent transactions don't interfere), Durability (committed data survives crashes).
9. UNION vs UNION ALL?
UNION removes duplicates (slower); UNION ALL keeps all rows.
10. RANK vs DENSE_RANK vs ROW_NUMBER?
For ties: RANK gives 1,1,3; DENSE_RANK gives 1,1,2; ROW_NUMBER always gives unique 1,2,3.
14. Cheat Sheet & References
Query structure
-- the order you write a query
SELECT col, AGG(col) AS alias FROM t WHERE cond GROUP BY col HAVING AGG(col) > 1 ORDER BY alias DESC LIMIT 10;
SELECT DISTINCT city FROM students;
SELECT * FROM t WHERE c IN (1, 2) AND name LIKE 'A%' AND d IS NOT NULL;
SELECT * FROM t WHERE c BETWEEN 10 AND 20;
Joins
SELECT * FROM a JOIN b ON b.a_id = a.id;
SELECT * FROM a LEFT JOIN b ON b.a_id = a.id WHERE b.id IS NULL;
SELECT e.name, m.name AS manager FROM emp e LEFT JOIN emp m ON m.id = e.manager_id;
Aggregates and CASE
SELECT COUNT(*), SUM(x), AVG(x), MIN(x), MAX(x) FROM t;
SELECT CASE WHEN marks >= 90 THEN 'A' ELSE 'B' END FROM students;
COALESCE(a, 0); NULLIF(a, 0); ROUND(x, 2); UPPER(s); LENGTH(s);
Subqueries, CTEs, windows
SELECT * FROM t WHERE x > (SELECT AVG(x) FROM t);
WITH c AS (SELECT dept, AVG(x) AS a FROM t GROUP BY dept) SELECT * FROM c;
SELECT name, RANK() OVER (PARTITION BY dept ORDER BY marks DESC) FROM t;
SELECT x, LAG(x) OVER (ORDER BY d) FROM t;
Changing data
INSERT INTO t (a, b) VALUES (1, 'x'), (2, 'y');
UPDATE t SET a = a + 1 WHERE id = 5;
DELETE FROM t WHERE id = 5;
BEGIN; UPDATE t SET a = 1; COMMIT;
Tables and indexes
CREATE TABLE t (id INT PRIMARY KEY, name VARCHAR(50) NOT NULL UNIQUE, dept_id INT REFERENCES d(id));
ALTER TABLE t ADD COLUMN email VARCHAR(100);
CREATE INDEX idx_t_name ON t (name);
CREATE VIEW v AS SELECT * FROM t WHERE active = 1;
DROP TABLE t;
Official documentation