Farhad Rahimi Klie

Posted on Nov 12 • Edited on Dec 21

SQL Explained Completely — Architecture, Internals, and Full SQL Code Examples

#data #database #sql #beginners

SQL (Structured Query Language) is the standard interface between humans and relational databases. To truly master SQL, you must understand what happens internally, how queries are executed, and how to write correct, efficient SQL code.

This article explains everything step by step, with working SQL examples for every concept.

1. What SQL Is (With a Minimal Example)

SQL is a declarative language. You describe what you want, not how to get it.

SELECT name, email
FROM users
WHERE active = TRUE;

You do not tell the database:

which index to use
how to scan data
how to allocate memory

That is the database engine’s responsibility.

2. Relational Model (Tables, Rows, Columns)

A table represents an entity.

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    email TEXT,
    age INTEGER
);

Table → relation
Row → tuple
Column → attribute

3. SQL Architecture (Logical View)

SQL execution flow:

Client
  ↓
SQL Parser
  ↓
Query Optimizer
  ↓
Execution Engine
  ↓
Storage Engine

You interact only with SQL; everything else is internal.

4. SQL Query Internal Lifecycle (With Example)

Query:

SELECT * FROM users WHERE age > 18;

Internally:

Parser validates syntax
Semantic analyzer checks table/column existence
Optimizer chooses index or full scan
Executor reads data pages
Result set returned

5. SQL Language Categories (With Code)

6. DDL – Data Definition Language

Create Database

CREATE DATABASE company_db;

Create Table

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    salary NUMERIC(10,2),
    hired_at DATE
);

Alter Table

ALTER TABLE employees
ADD COLUMN department TEXT;

Drop Table

DROP TABLE employees;

DDL changes structure, not data.

7. DML – Data Manipulation Language

Insert Data

INSERT INTO employees (name, salary, department)
VALUES ('Ali', 1200.00, 'IT');

Update Data

UPDATE employees
SET salary = salary + 200
WHERE department = 'IT';

Delete Data

DELETE FROM employees
WHERE salary < 500;

8. DQL – Data Query Language (SELECT)

Basic Query

SELECT * FROM employees;

Filtering

SELECT name, salary
FROM employees
WHERE salary > 1000;

Sorting

SELECT * FROM employees
ORDER BY salary DESC;

Limiting

SELECT * FROM employees
LIMIT 5 OFFSET 10;

9. Logical Execution Order (With Example)

SELECT department, COUNT(*)
FROM employees
WHERE salary > 1000
GROUP BY department
HAVING COUNT(*) > 3
ORDER BY COUNT(*) DESC;

Actual execution order:

FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY

10. Constraints (Data Integrity)

CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    email TEXT UNIQUE NOT NULL,
    balance NUMERIC CHECK (balance >= 0)
);

Types:

PRIMARY KEY
FOREIGN KEY
UNIQUE
NOT NULL
CHECK
DEFAULT

11. Relationships and Foreign Keys

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

This enforces referential integrity.

12. Joins (With Code)

Inner Join

SELECT users.name, orders.id
FROM users
INNER JOIN orders ON users.id = orders.user_id;

Left Join

SELECT users.name, orders.id
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

13. Aggregation Functions

SELECT
    COUNT(*) AS total,
    AVG(salary),
    MAX(salary),
    MIN(salary)
FROM employees;

14. Indexes (Performance Internals)

CREATE INDEX idx_salary
ON employees(salary);

Indexes:

Speed up SELECT
Slow down INSERT/UPDATE
Use B-Tree internally (usually)

15. Transactions (ACID)

BEGIN;

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

COMMIT;

Rollback example:

ROLLBACK;

16. Isolation Levels

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Levels:

READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE

17. Views and CTEs

View

CREATE VIEW high_salary_employees AS
SELECT * FROM employees WHERE salary > 2000;

CTE

WITH rich_employees AS (
    SELECT * FROM employees WHERE salary > 3000
)
SELECT * FROM rich_employees;

18. Stored Procedures (Example – PostgreSQL)

CREATE FUNCTION get_employee_salary(emp_id INT)
RETURNS NUMERIC AS $$
BEGIN
    RETURN (SELECT salary FROM employees WHERE id = emp_id);
END;
$$ LANGUAGE plpgsql;

19. SQL Storage Internals (Conceptual)

Internally databases use:

Pages (4KB–8KB)
WAL / Redo Logs
Buffer Cache
Index Trees

You do not control this with SQL directly, but SQL triggers it.

20. Security and Permissions

CREATE USER analyst WITH PASSWORD 'securepass';

GRANT SELECT ON employees TO analyst;
REVOKE DELETE ON employees FROM analyst;

21. SQL Dialects

Same SQL, different engines:

PostgreSQL (advanced, strict)
MySQL (popular web)
SQLite (embedded)
SQL Server (enterprise)
Oracle (corporate)

Core SQL is shared; extensions differ.

22. SQL in Real Systems

SQL is used in:

Web backends
Banking systems
Analytics platforms
AI pipelines
Distributed databases

Even NoSQL systems often emulate SQL behavior.

Final Conclusion

SQL is:

A data modeling language
A query language
A transaction control system
A security layer
A performance-sensitive interface

If you understand SQL internally and practically, you understand how real-world software handles data.