Structured Query Language (SQL)

Structured Query Language (SQL) is the standard declarative programming language used to define, manipulate, and query relational databases.

Unlike procedural languages (like Java or Python) where you must write loops to find data, SQL is *declarative*. You simply tell the database exactly *what* you want (e.g., SELECT Name FROM Users), and the database engine figures out *how* to get it.

SQL Sub-Languages

SQL commands are massive in scope, so they are neatly categorized into four distinct sub-languages based on their administrative and operational purpose:

Data Definition Language (DDL): Deals with the database schema and structural descriptions. These commands define HOW data should reside. Commands: CREATE, ALTER, DROP, TRUNCATE, RENAME.
Data Manipulation Language (DML): Deals with the actual data manipulation inside the tables. This includes retrieving, inserting, modifying, and deleting records. Commands: SELECT, INSERT, UPDATE, DELETE, MERGE.
Data Control Language (DCL): Manages access privileges, user permissions, and security controls. Commands: GRANT, REVOKE.
Transaction Control Language (TCL): Manages the execution and persistence of transactions within the database. Commands: COMMIT, ROLLBACK, SAVEPOINT.

SQL Sub-Language	Purpose	Primary Commands
DDL	Defines, alters, and drops schema structures	CREATE, ALTER, DROP, TRUNCATE, RENAME
DML	Queries, inserts, updates, and deletes records	SELECT, INSERT, UPDATE, DELETE, MERGE
DCL	Manages security privileges and access rights	GRANT, REVOKE
TCL	Manages boundaries and execution of transactions	COMMIT, ROLLBACK, SAVEPOINT

Placement Trap: TRUNCATE vs DELETE

Interviewers LOVE asking if TRUNCATE is DDL or DML. TRUNCATE is DDL, while DELETE is DML. Why? Because DELETE removes rows one by one and logs them (slow, but you can rollback). TRUNCATE structurally drops the table and recreates an empty one (lightning fast, but you CANNOT rollback).

Retrieving Data (The SELECT Statement)

The SELECT statement is the fundamental DML command used to retrieve data from a database. Let's look at a sample Customers table to see how queries affect the data.

Customers (Our Sample Database)

ID	CustomerName	City	Country
1	Alfreds Futterkiste	Berlin	Germany
2	Ana Trujillo	México D.F.	Mexico
3	Antonio Moreno	México D.F.	Mexico
4	Around the Horn	London	UK
5	Blauer See Delikatessen	Mannheim	Germany

To retrieve all columns from a table, use the asterisk (*) wildcard:

sql

SELECT *
FROM Customers;

To retrieve specific columns, just list them out:

sql

SELECT CustomerName, City
FROM Customers;

Filtering Duplicates (DISTINCT)

If you run SELECT Country FROM Customers;, you will get 5 rows back, including two 'Germany' and two 'Mexico' entries.

The DISTINCT keyword filters out duplicate rows, returning only unique values.

sql

SELECT DISTINCT Country
FROM Customers;

Query Result

Country
Germany
Mexico
UK

Filtering Rows (WHERE Clause)

The WHERE clause acts as a filter, returning ONLY the rows that satisfy a specified condition.

sql

SELECT *
FROM Customers
WHERE Country = 'Mexico';

Query Result (Filtered)

ID	CustomerName	City	Country
2	Ana Trujillo	México D.F.	Mexico
3	Antonio Moreno	México D.F.	Mexico

The WHERE clause can combine multiple conditions using the logical operators AND, OR, and NOT.

- AND: Returns true only if ALL conditions are true. - OR: Returns true if ANY condition is true. - NOT: Reverses the truth value.

sql

SELECT *
FROM Customers
WHERE Country = 'Germany'
AND City = 'Berlin';

Query Result (Logical AND)

ID	CustomerName	City	Country
1	Alfreds Futterkiste	Berlin	Germany

Sorting Data (ORDER BY)

The ORDER BY clause sorts your query results. By default, it sorts in ascending order (A to Z, 1 to 10). You can explicitly type ASC for ascending, or DESC for descending.

You can even sort by multiple columns!

sql

SELECT *
FROM Customers
ORDER BY Country ASC, CustomerName DESC;

Sort the Concepts

Test your knowledge! Drag the SQL command into its correct Sub-Language bucket:

DDL (Definition)

DML (Manipulation)

DCL (Control)

TCL (Transaction)

Unsorted Items:

SELECT

CREATE

GRANT

COMMIT

TRUNCATE