What is SQL?

SQL stands for Structured Query Language. It is used to communicate with relational databases.

With SQL you can create objects, insert, read, update, delete and analyze data, and manage relationships.

SELECT *
FROM employees;

Relational tables: rows and columns

What is a Database?

An organized collection of data — e.g., Company Database with employees, departments, jobs, job_history, customers, orders.

Tables — Rows & Columns

A table stores data in rows and columns. Example employees: employee_id | first_name | salary — 101 | John | 75000

Row = one record. Column = one attribute.

SQL vs NoSQL

Compare SQL and NoSQL databases across data model, schema, relationships, transactions, and scaling to choose the right tool for your workload.

Database Keys

Primary Key

Uniquely identifies a row. Unique, NOT NULL, one per table, can be composite.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(100),
    salary DECIMAL(10,2)
);

Foreign Key

Creates relationship: employees.department_id → departments.department_id

FOREIGN KEY (department_id) REFERENCES departments(department_id)

Candidate / Composite / Natural vs Surrogate

• Candidate: any column(s) capable of unique identification
• Composite: PRIMARY KEY (order_id, product_id)
• Natural: email, product_code
• Surrogate: customer_id = 10001 (generated)

Constraints

Constraints enforce data integrity rules like uniqueness, presence, and valid ranges directly at the database level.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    email VARCHAR(255) UNIQUE,
    first_name VARCHAR(100) NOT NULL,
    salary DECIMAL(10,2) CHECK (salary > 0)
);

Common: PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, DEFAULT

Practice Database

All examples use consistent tables: employees, departments, jobs, job_history

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    email VARCHAR(255),
    hire_date DATE,
    job_id VARCHAR(20),
    salary DECIMAL(10,2),
    department_id INT,
    manager_id INT
);

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100),
    location VARCHAR(100),
    manager_id INT
);

CREATE TABLE jobs (
    job_id VARCHAR(20) PRIMARY KEY,
    job_title VARCHAR(100),
    min_salary DECIMAL(10,2),
    max_salary DECIMAL(10,2)
);

CREATE TABLE job_history (
    employee_id INT,
    start_date DATE,
    end_date DATE,
    job_id VARCHAR(20),
    department_id INT,
    PRIMARY KEY (employee_id, start_date)
);