SQL

Chapter 1 — Database Basics

What is a Database?

A database is a structured system to store, manage, and retrieve data efficiently.

SQL (Relational Databases)

• Data stored in tables (rows & columns)
• Fixed schema
• Uses SQL
• Strong data integrity and relationships
• Best for structured data

Examples:

• PostgreSQL
• MySQL
• SQLite

NoSQL (Non-Relational Databases)

• Flexible data formats (JSON, key-value, graph)
• No fixed schema
• Designed for scalability and speed
• Can cause data duplication

Examples:

• MongoDB
• Redis
• Cassandra

Chapter 2 — Tables

Creating Tables

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

• Table → collection of records
• Column → data attribute
• Row → single entry

Altering Tables

Rename Table

ALTER TABLE users
RENAME TO customers;

Rename Column

ALTER TABLE users
RENAME COLUMN name TO username;

Add Column

ALTER TABLE users
ADD COLUMN email TEXT;

Drop Column

ALTER TABLE users
DROP COLUMN age;

Chapter 3 — Migrations

What is a Migration?

A migration is a controlled change to the database structure.

• Applied step-by-step

• Tracks schema history

• Prevents breaking changes

Example:

ALTER TABLE users ADD COLUMN email TEXT;

Up vs Down

• Up → apply changes

• Down → rollback changes

Rule: schema changes and code updates must happen together.

Chapter 4 — Data Types (SQLite)

• NULL → no value
• INTEGER → whole numbers
• REAL → decimal numbers
• TEXT → strings
• BLOB → binary data
• BOOLEAN → 0 (false), 1 (true)

SQLite is dynamically typed, but consistency matters.

Chapter 5 — Constraints (Core Concept)

Constraints protect data correctness.

Common Constraints

• PRIMARY KEY

id INTEGER PRIMARY KEY

• UNIQUE

email TEXT UNIQUE

• NOT NULL

name TEXT NOT NULL

• DEFAULT

balance INTEGER DEFAULT 0

• CHECK

CHECK (age > 0)

• FOREIGN KEY

FOREIGN KEY (user_id) REFERENCES users(id)

Chapter 6 — Adding Constraints (After Table Creation)

Add UNIQUE Constraint

ALTER TABLE users
ADD CONSTRAINT unique_email UNIQUE (email);

Add CHECK Constraint

ALTER TABLE users
ADD CONSTRAINT age_check CHECK (age >= 18);

Add FOREIGN KEY

ALTER TABLE orders
ADD CONSTRAINT fk_user
FOREIGN KEY (user_id) REFERENCES users(id);

Chapter 7 — Modifying & Removing Constraints

Drop Constraint

ALTER TABLE users
DROP CONSTRAINT unique_email;

Change Constraint Logic

You cannot directly modify a constraint.
Steps:

1. Drop old constraint
2. Add new constraint

Example:

ALTER TABLE users DROP CONSTRAINT age_check;
ALTER TABLE users ADD CONSTRAINT age_check CHECK (age >= 16);

SQLite Limitation

SQLite does not fully support dropping constraints.
Common workaround:

1. Create new table

2. Copy data

3. Drop old table

4. Rename new table

Chapter 8 — SQL Queries (READ)

SELECT

SELECT * FROM users;
SELECT name, age FROM users;

WHERE (Filtering)

SELECT * FROM users WHERE age > 18;

Operators:

• = != > < >= <=u

• AND, OR, NOT12

LIMIT & OFFSET

SELECT * FROM users LIMIT 10 OFFSET 20;

ORDER BY

SELECT * FROM users ORDER BY age DESC;

DISTINCT

SELECT DISTINCT country FROM users;

Chapter 9 — INSERT, UPDATE, DELETE

INSERT

INSERT INTO users (name, age)
VALUES ('Ahmed', 20);

UPDATE

UPDATE users
SET age = 21
WHERE id = 1;

Never use UPDATE without WHERE.

DELETE

DELETE FROM users WHERE id = 1;

Delete all rows:

DELETE FROM users;

Chapter 10 — Aggregations

COUNT / SUM / AVG

SELECT COUNT(*) FROM users;
SELECT AVG(age) FROM users;
SELECT SUM(balance) FROM accounts;

GROUP BY

SELECT country, COUNT(*)
FROM users
GROUP BY country;

HAVING

SELECT country, COUNT(*)
FROM users
GROUP BY country
HAVING COUNT(*) > 5;

Chapter 11 — Transactions

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

Rollback:

ROLLBACK;

Used for critical operations.

Chapter 12 — Indexes

• Speed up read operations

• Slow down inserts and updates

CREATE INDEX idx_users_email ON users(email);

Use indexes for:

• Large tables

• Frequent search columns