1. Data Definition Language (DDL)

DDL statements are used to define, modify, and delete database structures.

Database Operations

-- List all databases
SHOW DATABASES;

-- Create a new database
CREATE DATABASE inventory_db;
CREATE DATABASE IF NOT EXISTS inventory_db;

-- Remove a database
DROP DATABASE inventory_db;
DROP DATABASE IF EXISTS inventory_db;

-- Select and use a database
SELECT DATABASE(); -- Identify current database
USE inventory_db;   -- Switch to a specific database

-- Display tables in the current database
SHOW TABLES;

-- Describe the structure of a table
DESCRIBE products;

Table Creation

CREATE TABLE employees (
    employee_id INT,
    first_name VARCHAR(30),
    last_name VARCHAR(30),
    hire_date DATE
);
-- Note: The last column definition does not end with a comma.

2. Data Manipulation Language (DML)

DML commands handle the ensertion, updating, and deletion of records within tables.

-- Insert data into specific columns
INSERT INTO employees (first_name, last_name) VALUES ('Jane', 'Doe');

-- Insert data into all columns (must match table order)
INSERT INTO employees VALUES (1, 'John', 'Smith', '2023-01-15');

-- Insert multiple rows in a single statement
INSERT INTO employees (first_name, last_name) VALUES
('Alice', 'Johnson'),
('Bob', 'Williams'),
('Charlie', 'Brown');

INSERT INTO employees VALUES
(2, 'David', 'Lee', '2023-02-20'),
(3, 'Eva', 'Green', '2023-03-10');

3. Data Query Language (DQL)

DQL is centered around the SELECT statement for retrieving data.

Basic Queries

-- Select specific columns
SELECT product_name, unit_price FROM products;

-- Select all columns
SELECT * FROM products;

-- Eliminate duplicate values in results
SELECT DISTINCT category FROM products;

-- Assign aliases to columns
SELECT unit_price AS price FROM products;
SELECT unit_price price FROM products; -- AS is optional

Conditional Filtering with WHERE

-- Basic syntax
SELECT product_name FROM products WHERE conditions;

-- Common operators and conditions
> < >= <= = <> or !=
BETWEEN 10 AND 50
IN ('Electronics', 'Books', 'Clothing')
LIKE 'Appl%'  -- % matches any sequence, _ matches a single character
IS NULL
IS NOT NULL
AND  or &&
OR   or ||
NOT  or !

Sorting Results with ORDER BY

SELECT product_name, unit_price FROM products
ORDER BY unit_price DESC, product_name ASC;
-- ASC (ascending) is default, DESC is descending.

Grouping Data and Aggregate Functions

Aggregate functions perform calculations on a set of rows. NULL values are ignored.

-- Common aggregate functions
COUNT(column_name)  -- Number of non-null entries
MAX(column_name)
MIN(column_name)
SUM(column_name)
AVG(column_name)

-- Using aggregate functions
SELECT AVG(salary) FROM employees;

-- GROUP BY clause
-- After grouping, selected columns should be aggregate functions or the grouping column.
SELECT department_id, AVG(salary), COUNT(*) FROM employees
WHERE hire_date > '2020-01-01'   -- Filter before grouping
GROUP BY department_id
HAVING COUNT(*) > 5;             -- Filter after grouping

-- Example: Find departments with an average salary over 50000 and more than 3 employees hired after 2019.
SELECT department_id, AVG(salary), COUNT(*)
FROM employees
WHERE hire_date > '2019-12-31'
GROUP BY department_id
HAVING AVG(salary) > 50000 AND COUNT(*) > 3;

-- Execution order: WHERE -> GROUP BY -> Aggregate Functions -> HAVING

Limiting Results with Pagination (LIMIT)

-- Syntax: LIMIT [offset,] row_count
-- Offset starts from 0.

-- Fetch the first 5 records
SELECT * FROM orders LIMIT 5;

-- Skip 10 records, then fetch the next 5 (e.g., Page 3 if 5 per page)
SELECT * FROM orders LIMIT 10, 5;

-- General pagination formula:
-- Starting Index = (Current Page Number - 1) * Records Per Page

4. Common Data Types

• Integer Types: INT, SMALLINT, BIGINT
• Decimal Types: DECIMAL(M, N), FLOAT, DOUBLE
• String Types: CHAR(N), VARCHAR(N), TEXT
• Date/Time Types: DATE, TIME, DATETIME, TIMESTAMP
• Boolean: BOOLEAN or BOOL

5. Table Constraints

Constraints enforce rules on table data.

• NOT NULL: Column cannot contain NULL values.
• UNIQUE: All values in the column must be different.
• PRIMARY KEY: Uniquely identifies each row (implies NOT NULL and UNIQUE).
• FOREIGN KEY: Ensures referential integrity to a primary key in another table.
• DEFAULT: Provides a default value for the column.
• CHECK: Validates data against a logical condition (support varies by DBMS).

6. Multi-Table Relationships and Implementation

Relationship Types

• One-to-One: A single record in Table A relates to one record in Table B.
• One-to-Many / Many-to-One: A single record in Table A relates to many records in Table B.
• Many-to-Many: Records in Table A relate to many records in Table B, and vice versa.

Implementation

• One-to-Many: Add a foreign key column in the "many" table referencing the primary key of the "one" table.
• Many-to-Many: Create a junction/link table with at least two foreign key columns, each referencing the primary key of one of the related tables.

7. Multi-Table Queries

Joins

Joins combine rows from two or more tables based on a related column.

Inner Join

-- Implicit Inner Join (using WHERE)
SELECT e.first_name, e.last_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;

-- Explicit Inner Join (using JOIN ... ON)
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id; -- INNER is optional

Outer Joins

-- Left Outer Join: Returns all records from the left table, and matched records from the right.
SELECT e.first_name, d.department_name
FROM employees e
LEFT OUTER JOIN departments d ON e.department_id = d.department_id; -- OUTER is optional

-- Right Outer Join: Returns all records from the right table, and matched records from the left.
SELECT e.first_name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;

Subqueries

A subquery is a query nested inside another query.

-- Single-row, single-column subquery (returns one value)
SELECT * FROM products
WHERE unit_price > (SELECT AVG(unit_price) FROM products);

-- Multi-row, single-column subquery (returns a list, used with IN)
SELECT * FROM customers
WHERE region_id IN (SELECT region_id FROM regions WHERE country = 'USA');

-- Multi-row, multi-column subquery (returns a virtual table)
SELECT *
FROM (
    SELECT customer_id, SUM(order_total) AS total_spent
    FROM orders
    GROUP BY customer_id
) AS customer_totals
WHERE total_spent > 10000;

8. Database Transactions

A transaction is a single logical unit of work that comprises one or more SQL statements.

ACID Properties

• Atomicity: All operations in a transaction succeed or fail as a unit.
• Consistency: A transaction brings the database from one valid state to another.
• Isolation: Concurrent transactions execute independently without interference.
• Durability: Once committed, the changes made by a transaction are permanent.

Transaction Control

-- Start a transaction explicitly
START TRANSACTION;
-- or BEGIN;

-- Execute one or more DML statements
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

-- Permanently save the changes
COMMIT;

-- Or, discard all changes made since the transaction started
ROLLBACK;