➜SELECT * FROM knowledge WHERE topic = 'SQL';

CodeNFacts / sql

SQL, end to end.

What it is, why every backend and analytics stack still leans on it, every command type, syntax you can copy-paste, diagrams, a learning roadmap, a cheat sheet, and a downloadable notes file to keep for revision.

~11 min read · plain-text, opens anywhere

1970

Relational model proposed

1986

ANSI SQL standardized

Core command categories

Most in-demand data language

introduction architecture command_types detailed_notes joins_diagram roadmap cheat_sheet pros_cons use_cases blog faq

What is SQL, and why does it exist?

SQL (Structured Query Language) is the standard language for talking to relational databases — systems that store data as rows and columns inside related tables (think spreadsheets that can reference each other). Instead of writing step-by-step instructions, you write a declarative statement describing the result you want, and the database's query engine figures out the fastest way to get it.

Why it's used

One language works across almost every relational database
Set-based operations handle millions of rows efficiently
Built-in integrity rules keep data consistent
Readable, English-like syntax

Why it's needed

Almost every app needs to persist structured data
Analytics, reporting, and BI tools all query in SQL
It's the common interface between apps, data teams, and DBAs
Interviews and real jobs expect fluency in it

How a query actually runs

A block diagram of the journey a query takes from your keyboard to disk and back.

01Client / Apppsql, app code, BI tool

02SQL QuerySELECT * FROM orders ...

03ParserChecks syntax, builds parse tree

04OptimizerChooses fastest execution plan

05Execution EngineRuns the plan, uses indexes

06Storage EngineReads/writes data files & buffers

01Client / Apppsql, app code, BI tool

02SQL QuerySELECT * FROM orders ...

03ParserChecks syntax, builds parse tree

04OptimizerChooses fastest execution plan

05Execution EngineRuns the plan, uses indexes

06Storage EngineReads/writes data files & buffers

Types of SQL commands

DDL

Data Definition Language

Defines and modifies the structure of database objects — tables, schemas, indexes. DDL statements auto-commit in most databases.

CREATE

Create a new table, database, view, or index

CREATE TABLE students (
  id INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  age INT CHECK (age > 0)
);

ALTER

Modify an existing table's structure

ALTER TABLE students ADD COLUMN email VARCHAR(100);

DROP

Permanently delete a table or database object

DROP TABLE students;

TRUNCATE

Remove all rows from a table instantly (keeps structure)

TRUNCATE TABLE students;

RENAME

Rename a table or column

ALTER TABLE students RENAME TO learners;

DML

Data Manipulation Language

Manages the data stored inside tables — inserting, updating, and deleting rows. DML changes can be rolled back inside a transaction.

INSERT

Add new row(s) into a table

INSERT INTO students (id, name, age)
VALUES (1, 'Asha', 21);

UPDATE

Modify existing rows that match a condition

UPDATE students SET age = 22 WHERE id = 1;

DELETE

Remove row(s) matching a condition

DELETE FROM students WHERE age < 18;

MERGE

Insert or update depending on whether a match exists (upsert)

MERGE INTO students t USING staging s
ON t.id = s.id
WHEN MATCHED THEN UPDATE SET t.name = s.name
WHEN NOT MATCHED THEN INSERT (id, name) VALUES (s.id, s.name);

DQL

Data Query Language

Retrieves data from the database. Some textbooks fold DQL into DML — either grouping is fine to know for interviews.

SELECT

Query rows and columns from one or more tables

SELECT name, age FROM students
WHERE age > 18
ORDER BY age DESC
LIMIT 5;

DCL

Data Control Language

Controls access and permissions — who can read, write, or administer database objects.

GRANT

Give a user specific privileges

GRANT SELECT, INSERT ON students TO 'analyst';

REVOKE

Remove previously granted privileges

REVOKE INSERT ON students FROM 'analyst';

TCL

Transaction Control Language

Manages transactions so a group of DML operations succeeds or fails as a single unit (see ACID below).

COMMIT

Permanently save all changes made in the current transaction

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

ROLLBACK

Undo changes made in the current, uncommitted transaction

BEGIN;
DELETE FROM orders WHERE id = 99;
ROLLBACK; -- undoes the delete

SAVEPOINT

Mark a point within a transaction to roll back to, without undoing everything

SAVEPOINT before_update;
UPDATE inventory SET qty = 0;
ROLLBACK TO before_update;

Detailed notes — topic by topic

Filtering with WHERE

Narrow results using comparison, logical, range, list, and pattern operators.

SELECT * FROM orders
WHERE status = 'shipped'
  AND total BETWEEN 100 AND 500
  AND country IN ('IN', 'US', 'DE')
  AND customer_name LIKE 'A%';

Sorting & Limiting

ORDER BY controls row order; LIMIT/OFFSET (or FETCH/TOP) controls pagination.

SELECT name, score FROM players
ORDER BY score DESC
LIMIT 10 OFFSET 20; -- page 3 of 10-row pages

Aggregate Functions

COUNT, SUM, AVG, MIN, MAX summarize many rows into one value — usually paired with GROUP BY.

SELECT department, COUNT(*) AS headcount, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING COUNT(*) > 5
ORDER BY avg_salary DESC;

Joins

Combine rows from two or more tables based on a related column. See the join diagram below.

SELECT o.id, c.name, o.total
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;

Subqueries

A query nested inside another — in SELECT, FROM, or WHERE — to compute an intermediate result.

SELECT name FROM employees
WHERE salary > (
  SELECT AVG(salary) FROM employees
);

Set Operations

UNION, UNION ALL, INTERSECT, and EXCEPT/MINUS combine the results of two compatible queries.

SELECT city FROM customers
UNION
SELECT city FROM suppliers;  -- duplicates removed automatically

Constraints

Rules enforced on columns: PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, DEFAULT.

CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT REFERENCES customers(id),
  total DECIMAL(10,2) CHECK (total >= 0),
  status VARCHAR(20) DEFAULT 'pending'
);

Indexes

A lookup structure (usually a B-tree) that speeds up reads on a column at the cost of slower writes and extra storage.

CREATE INDEX idx_customers_email ON customers(email);
-- speeds up: SELECT * FROM customers WHERE email = '...';

Views

A saved, reusable query that behaves like a virtual table — great for simplifying repeated logic.

CREATE VIEW active_customers AS
SELECT * FROM customers WHERE status = 'active';

SELECT * FROM active_customers;

Transactions & ACID

A transaction groups statements so they all succeed or all fail — guaranteeing Atomicity, Consistency, Isolation, Durability.

BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;

Stored Procedures & Functions

Precompiled, reusable blocks of SQL (with logic, parameters, and loops) stored on the server.

CREATE PROCEDURE give_raise(emp_id INT, pct DECIMAL)
AS $$
BEGIN
  UPDATE employees SET salary = salary * (1 + pct/100)
  WHERE id = emp_id;
END;
$$ LANGUAGE plpgsql;

Triggers

A block of SQL that runs automatically before/after an INSERT, UPDATE, or DELETE on a table.

CREATE TRIGGER trg_audit_update
AFTER UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION log_salary_change();

Window Functions

Perform calculations across a set of rows related to the current row, without collapsing them like GROUP BY does.

SELECT name, department, salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

Normalization

A design process that reduces redundancy: 1NF (atomic columns) → 2NF (no partial dependency) → 3NF (no transitive dependency) → BCNF.

-- Un-normalized: orders(id, customer_name, customer_email, item, qty)
-- Normalized:
-- customers(id, name, email)
-- orders(id, customer_id, item, qty)

SQL Injection & Security

Never concatenate raw user input into SQL strings — always use parameterized queries / prepared statements.

-- Unsafe:
-- "SELECT * FROM users WHERE name = '" + input + "'"

-- Safe (parameterized):
SELECT * FROM users WHERE name = $1;

Joins, visually

Shaded area = rows returned. Table A = left table, Table B = right table.

INNER JOIN

Only matching rows

LEFT JOIN

All of A + matches

RIGHT JOIN

All of B + matches

FULL OUTER JOIN

Everything

Learning roadmap

1
Beginner
Week 1–2
- SELECT, WHERE, ORDER BY, LIMIT
- Data types & basic constraints
- INSERT / UPDATE / DELETE
- Simple aggregate functions
2
Intermediate
Week 3–5
- All JOIN types
- GROUP BY / HAVING
- Subqueries & set operations
- Views & indexes basics
3
Advanced
Week 6–9
- Window functions
- Transactions & isolation levels
- Stored procedures, functions, triggers
- Query optimization & EXPLAIN plans
4
Expert
Ongoing
- Normalization & schema design
- Sharding, replication, partitioning
- NewSQL / distributed SQL engines
- Database-specific tuning (Postgres/MySQL/SQL Server)

Cheat sheet

WHERE col = value	Exact match
WHERE col BETWEEN a AND b	Inclusive range
WHERE col IN (a, b, c)	Match any in list
WHERE col LIKE 'A%'	Pattern match (% = any chars, _ = 1 char)
WHERE col IS NULL	Never use = NULL

Good side, bad side

Strengths

+Declarative — you describe *what* you want, the engine decides *how*.
+Mature, standardized (ANSI SQL) and portable across most relational databases.
+Extremely strong for structured, relational data with clear integrity rules (ACID).
+Huge ecosystem: tooling, ORMs, BI tools, and hiring pool all assume SQL literacy.
+Excellent for complex joins, aggregations, and reporting over structured data.

Trade-offs

−Rigid schema — structural changes can be costly at large scale.
−Not a natural fit for unstructured or rapidly-changing data (documents, graphs).
−Vertical scaling has limits; horizontal scaling/sharding needs extra engineering.
−Complex queries (deep joins, recursive CTEs) can be hard to read and optimize.
−Dialect differences (MySQL vs Postgres vs SQL Server) reduce true portability in practice.

Where SQL shows up

E-commerce

Product catalogs, orders, inventory, and payment records with strong consistency guarantees.

Banking & Finance

Ledgers and transactions where ACID compliance is non-negotiable.

Healthcare

Patient records, scheduling, and billing that require strict relational integrity.

Analytics & BI

Data warehouses (Snowflake, BigQuery, Redshift) all speak SQL for reporting.

SaaS backends

Multi-tenant application data — users, subscriptions, permissions.

Logistics

Fleet, route, and warehouse systems with many interrelated entities.

A short history — and where it's going

Where SQL came from

SQL traces back to IBM's early-1970s work on the relational model (inspired by Edgar F. Codd's 1970 paper) and the original SEQUEL language. It was standardized by ANSI in 1986 and has remained the dominant language for relational databases for over four decades — a rare feat in a field that reinvents itself every few years.

Why it refused to die

Every generation of "SQL killers" — object databases in the 90s, XML databases in the 2000s, NoSQL in the 2010s — ended up re-adding query languages that look a lot like SQL. The reason is simple: relational algebra is a genuinely good abstraction for structured data, and SQL is its most battle-tested syntax.

SQL today

Modern cloud warehouses (BigQuery, Snowflake, Redshift), NewSQL systems (CockroachDB, YugabyteDB), and even streaming engines (ksqlDB, Flink SQL) all standardized on SQL as their interface — because it's the one query language almost every engineer and analyst already knows.

Where SQL is heading

Expect deeper AI integration (natural-language-to-SQL copilots), native vector/similarity search inside relational engines, tighter SQL-on-lakehouse tooling, and continued convergence between OLTP and OLAP workloads in a single SQL surface.

Frequently asked questions

Is SQL a programming language?+

It's a declarative, domain-specific language for managing relational data — not a general-purpose language like Python or Java. You describe the result you want; the query optimizer decides how to get it.

SQL vs NoSQL — which should I learn first?+

Learn SQL first. Relational modeling and set-based thinking transfer well, and most NoSQL systems eventually reintroduce SQL-like query layers (e.g. MongoDB aggregation, Cassandra's CQL).

Is SQL case-sensitive?+

Keywords (SELECT, FROM) are conventionally uppercase but not case-sensitive. Table/column name case-sensitivity depends on the database and OS.

What's the difference between DELETE, TRUNCATE, and DROP?+

DELETE removes rows (can be filtered, logged, rolled back). TRUNCATE removes all rows instantly (minimal logging). DROP removes the entire table structure.

What is a primary key vs a foreign key?+

A primary key uniquely identifies each row in its own table. A foreign key is a column that references a primary key in another table, enforcing referential integrity.

Take these notes with you

Every section above, bundled into one Markdown file for offline revision.

Thanks for downloading! 🎉

Your SQL notes are saved. Happy querying!

All Categories