SQL Introduction: The Language of Databases

Key capabilities

• Data definition (DDL): create and alter tables, constraints, indexes.
• Data manipulation (DML): select, insert, update, delete rows.
• Data control: permissions & security (not covered in this page).
• Transactions: grouped operations that commit or rollback together.

For data science

• Pull only the columns and rows you need (reduce memory).
• Pre-aggregate at the source (faster analytics downstream).
• Join reference tables to enrich features cleanly.
• Keep lineage: reproducible, auditable data extracts.

DDL — Data Definition Language

Defines schema (tables, columns, constraints, indexes).

• CREATE TABLE — make a table
• ALTER TABLE — modify structure
• DROP TABLE — delete table

DML — Data Manipulation Language

Reads/writes data inside tables.

• SELECT — query rows
• INSERT — add rows
• UPDATE — change rows
• DELETE — remove rows

Other categories

• TCL (transactions): BEGIN, COMMIT, ROLLBACK
• DCL (security): GRANT, REVOKE

Employees

• EmployeeID — integer primary key
• FirstName, LastName — text
• Department — text (e.g., IT, HR, Finance)
• Salary — real number

Projects

• ProjectID — integer primary key
• ProjectName — text
• Department — text (owner)
• Budget — real number

Primary Key (PK)

Uniquely identifies each row. Usually a single integer column or a stable business key.

CREATE TABLE Departments (
  DeptID     INTEGER PRIMARY KEY,  -- PK
  DeptName   TEXT UNIQUE NOT NULL  -- also unique
);

In SQLite, INTEGER PRIMARY KEY auto-rows behave like an alias for the internal rowid.

Foreign Key (FK)

Links child rows to a parent table PK. Enforces referential integrity.

CREATE TABLE Employees (
  EmployeeID  INTEGER PRIMARY KEY,
  FirstName   TEXT NOT NULL,
  LastName    TEXT NOT NULL,
  DeptID      INTEGER,
  Salary      REAL CHECK (Salary >= 0),
  FOREIGN KEY (DeptID) REFERENCES Departments(DeptID)
    ON UPDATE CASCADE
    ON DELETE SET NULL
);

ON UPDATE/DELETE actions (CASCADE, RESTRICT, SET NULL) are crucial for lifecycle rules.

Common types

• INTEGER/INT — whole numbers
• REAL/DECIMAL — floating or fixed-precision
• TEXT/VARCHAR — strings
• DATE/TIME/TIMESTAMP — temporal
• BLOB — binary

NULL behavior

Comparisons with NULL are not true/false—they’re unknown.

SELECT 1 = NULL;    -- NULL (unknown)
SELECT 1 < NULL;    -- NULL
SELECT NULL IS NULL; -- TRUE
SELECT NULL = NULL; -- NULL (not TRUE)

Guarding against NULL

Use COALESCE to substitute defaults; use IS NULL/IS NOT NULL in predicates.

SELECT FirstName, COALESCE(Department, 'Unknown') AS Dept
FROM Employees
WHERE Department IS NULL OR Salary IS NOT NULL;

1NF — Atomic columns

No repeating groups or arrays; each cell holds a single value.

-- Anti-pattern: comma-separated skills in one column
-- Better: a separate Skills table linked by EmployeeID

2NF — No partial dependencies

If the PK is composite, each non-key attribute depends on the whole key.

-- Orders( OrderID, ProductID, ProductName )  -- ProductName depends only on ProductID
-- Move ProductName to Products, keep Orders(OrderID, ProductID, ...)

3NF — No transitive dependencies

Non-key attributes shouldn’t depend on other non-keys.

-- Employees( EmpID, DeptID, DeptName )
-- DeptName depends on DeptID, not EmpID → split to Departments table

Style checklist

• Uppercase keywords (SELECT, FROM, WHERE).
• One clause per line; indent nested queries.
• Use AS for clear aliases; avoid single-letter aliases unless short-lived.
• Prefer JOIN ... ON over legacy comma joins.
• Comment tricky logic (-- why, not just -- what).

Before vs After

-- ❌ Hard to read
select e.firstname, e.lastname, p.projectname
from employees e, projects p
where e.department = p.department and e.salary > 60000;

-- ✅ Clear & maintainable
SELECT
  e.FirstName,
  e.LastName,
  p.ProjectName
FROM Employees AS e
JOIN Projects  AS p
  ON e.Department = p.Department
WHERE e.Salary > 60000;