SQL Essentials: The Complete Developer's Reference Guide

Everything you need to read, write, and reason about SQL — from first queries to CTEs, window functions, subqueries, normalisation, transactions, and security. Every section includes working syntax you can use immediately.

1. Database fundamentals

What is a database?

A database is an organised collection of related data stored to serve a specific purpose. A DBMS (Database Management System) is the software that allows users to organise, restore, and retrieve that data efficiently — MySQL, Oracle, SQL Server, and Sybase are common examples.

A Relational DBMS (RDBMS) stores data in separate tables linked through shared columns. Data is accessed using SQL (Structured Query Language).

Core vocabulary

Relationship types

• One-to-one: Each primary key in Table A relates to at most one record in Table B.
• One-to-many: One record in Table A relates to none, one, or many records in Table B. The most common type.
• Many-to-many: Records in both tables can relate to many records in the other. Usually implemented via a junction table.

Entity, entity type & entity set

• Entity — anything with an independent existence in the real world (a place, class, or object).
• Entity type — a set of entities that share similar attributes.
• Entity set — a collection of entities of a particular entity type stored in the database.

Functional dependency

A relation has a functional dependency when one attribute uniquely determines another. Example: employee_id → employee_name — knowing the ID uniquely tells you the name.

Advantages of DBMS

• Data stored in a structured way — redundancy is controlled.
• Validates data entered and restricts unauthorised access.
• Provides backup and recovery when required.
• Supports multiple user interfaces simultaneously.

Advantages of SQL

• Simple queries retrieve large amounts of data quickly and efficiently.
• Easy to learn — almost every DBMS supports it.
• No large amount of coding is required to manage a database.

OLAP vs OLTP

Data warehousing

The storage and access of data derived from transactions and other sources — centralised in one location for the purpose of analysis. It feeds OLAP systems.

---

2. SQL command categories

• COMMIT — permanently saves changes.
• ROLLBACK — undoes changes back to the last commit or savepoint.
• SAVEPOINT — creates a temporary restore point within a transaction.

---

3. SELECT & filtering

Basic SELECT

SELECT * FROM customers;
SELECT customer_name, city FROM customers;
SELECT DISTINCT country FROM customers;

-- Fetch rows where ID is even
SELECT DISTINCT city FROM station WHERE (id % 2) = 0;

WHERE — AND / OR / NOT

SELECT * FROM customers WHERE country = 'Mexico';
SELECT * FROM customers WHERE customer_id = 1;

SELECT * FROM customers WHERE country = 'USA' AND city = 'NY';
SELECT * FROM customers WHERE country = 'USA' OR country = 'Germany';
SELECT * FROM customers WHERE NOT country = 'Germany';
SELECT * FROM customers
WHERE NOT country = 'Germany' AND NOT country = 'USA';

ORDER BY

SELECT * FROM customers ORDER BY country DESC;
SELECT * FROM customers ORDER BY country ASC, customer_name DESC;

INSERT INTO

INSERT INTO customers (name, phone, email)
VALUES ('Sundeep', '0302', 'so10@gmail.com');

-- Copy rows from another table
INSERT INTO customers (customer_name, city, country)
SELECT supplier_name, city, country FROM suppliers;

-- Copy all rows to a new table
SELECT * INTO customers_backup_2024 FROM customers;

-- Copy only German customers
SELECT * INTO customers_germany
FROM customers WHERE country = 'Germany';

-- Copy into a table in a different database
SELECT * INTO customers_backup IN 'Backup.mdb' FROM customers;

UPDATE & DELETE

UPDATE customers
SET customer_name = 'John', phone = '0392'
WHERE customer_id = 1;

UPDATE customers SET contact_name = 'Juan' WHERE country = 'Mexico';

DELETE FROM customers WHERE customer_id = 1;
DELETE FROM customers;    -- removes all rows, keeps structure

SELECT TOP / LIMIT / ROWNUM

-- SQL Server / MS Access
SELECT TOP 3 * FROM customers;
SELECT TOP 50 PERCENT * FROM customers;

-- MySQL / PostgreSQL
SELECT * FROM customers LIMIT 3;

-- Oracle
SELECT * FROM customers WHERE ROWNUM <= 3;

NULL & ISNULL

SELECT customer_name, address FROM customers WHERE address IS NULL;
SELECT customer_name, address FROM customers WHERE address IS NOT NULL;

-- ISNULL: return an alternative value when expression is NULL (SQL Server)
SELECT product_name,
  unit_price * (units_in_stock + ISNULL(units_on_order, 0))
FROM products;

LIKE & wildcards

SELECT * FROM customers WHERE customer_name LIKE 'a%';     -- starts with a
SELECT * FROM customers WHERE customer_name LIKE '%a';     -- ends with a
SELECT * FROM customers WHERE customer_name LIKE '%or%';   -- contains 'or'
SELECT * FROM customers WHERE customer_name LIKE '_r%';    -- r is 2nd char
SELECT * FROM customers WHERE contact_name  LIKE 'a%o';    -- starts a, ends o
SELECT * FROM customers WHERE customer_name NOT LIKE 'a%';
SELECT * FROM customers WHERE city LIKE '[bsp]%';          -- starts b, s, or p
SELECT * FROM customers WHERE city LIKE '[!bsp]%';         -- not b/s/p

IN & BETWEEN

SELECT * FROM countries WHERE country_name IN ('US', 'Japan', 'China');

SELECT * FROM products WHERE price BETWEEN 10 AND 20;
SELECT * FROM products
WHERE price BETWEEN 10 AND 20
AND category_id NOT IN (1,2,3);

SELECT * FROM products
WHERE product_name BETWEEN 'Carnarvon Tigers' AND 'Chef Anton Cajun'
ORDER BY product_name;

SELECT * FROM orders
WHERE order_date BETWEEN #01/07/1996# AND #31/07/1996#;

CASE (if/then/else)

SELECT order_id, quantity,
  CASE
    WHEN quantity > 30 THEN 'Above 30'
    WHEN quantity = 30 THEN 'Exactly 30'
    ELSE 'Below 30'
  END AS quantity_label
FROM order_details;

EXISTS / ANY / ALL

-- EXISTS: true if subquery returns at least one row
SELECT supplier_name FROM suppliers
WHERE EXISTS (
  SELECT product_name FROM products
  WHERE products.supplier_id = suppliers.supplier_id
    AND price < 20
);

-- ANY: true if any subquery value meets the condition
SELECT product_name FROM products
WHERE product_id = ANY (
  SELECT product_id FROM order_details WHERE quantity = 10
);

-- ALL: true only if ALL subquery values meet the condition
SELECT product_name FROM products
WHERE product_id = ALL (
  SELECT product_id FROM order_details WHERE quantity = 10
);

Comments

-- Single-line comment
SELECT * FROM customers;   -- inline comment

/* Multi-line
   comment */

---

4. Aliases

Aliases give a table or column a temporary name for the duration of a query — useful for readability and shorter JOIN references.

-- Column aliases
SELECT customer_id AS id, customer_name AS customer
FROM customers;

-- Table aliases
SELECT o.order_id, o.order_date, c.customer_name
FROM customers AS c, orders AS o
WHERE c.customer_name = 'Around the Horn'
  AND c.customer_id = o.customer_id;

---

5. JOINs

-- INNER JOIN
SELECT o.order_id, c.customer_name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id;

-- LEFT JOIN
SELECT c.customer_name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
ORDER BY c.customer_name;

-- RIGHT JOIN
SELECT o.order_id, e.last_name, e.first_name
FROM orders o
RIGHT JOIN employees e ON o.employee_id = e.employee_id
ORDER BY o.order_id;

-- FULL OUTER JOIN
SELECT c.customer_name, o.order_id
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id;

-- SELF JOIN (manager / staff)
SELECT a.employee_name AS staff, b.employee_name AS manager
FROM employees a, employees b
WHERE a.manager_id = b.employee_id;

---

6. Aggregates, GROUP BY & HAVING

SELECT COUNT(customer_id), country
FROM customers
GROUP BY country;

-- HAVING filters after grouping
SELECT country, COUNT(customer_id) AS total
FROM customers
GROUP BY country
HAVING COUNT(customer_id) > 5
ORDER BY total DESC;

UNION & UNION ALL

SELECT city FROM customers
UNION           -- removes duplicates
SELECT city FROM suppliers
ORDER BY city;

SELECT city FROM customers
UNION ALL       -- keeps all rows including duplicates
SELECT city FROM suppliers;

---

7. Subqueries

A subquery is a query nested inside another query. The inner query executes first and its result is passed to the outer query. They can appear in SELECT, FROM, or WHERE clauses.

Subquery in WHERE

-- Customers who placed an order for product 42
SELECT customer_name FROM customers
WHERE customer_id IN (
  SELECT customer_id FROM orders WHERE product_id = 42
);

Correlated subquery

References a column from the outer query — re-runs for every outer row.

-- Employees earning above their department average
SELECT employee_name, salary, department_id
FROM employees e
WHERE salary > (
  SELECT AVG(salary) FROM employees
  WHERE department_id = e.department_id
);

Subquery in FROM (derived table)

SELECT dept_summary.department_id, dept_summary.avg_sal
FROM (
  SELECT department_id, AVG(salary) AS avg_sal
  FROM employees
  GROUP BY department_id
) AS dept_summary
WHERE dept_summary.avg_sal > 50000;

---

8. Common Table Expressions (CTEs)

A CTE defines a named temporary result set using the WITH keyword. It exists only for that query, can be referenced multiple times, and makes complex queries far more readable than nested subqueries.

Basic syntax

WITH cte_name AS (
  SELECT ...
  FROM   ...
  WHERE  ...
)
SELECT * FROM cte_name WHERE ...;

Single CTE — department salary comparison

WITH dept_avg AS (
  SELECT department_id, AVG(salary) AS avg_salary
  FROM   employees
  GROUP  BY department_id
)
SELECT e.employee_name, e.salary, d.avg_salary,
       e.salary - d.avg_salary AS diff_from_avg
FROM   employees e
JOIN   dept_avg d ON e.department_id = d.department_id
ORDER  BY diff_from_avg DESC;

Chained (multiple) CTEs

WITH
  active_customers AS (
    SELECT customer_id FROM customers WHERE status = 'active'
  ),
  recent_orders AS (
    SELECT customer_id, COUNT(*) AS order_count
    FROM   orders
    WHERE  order_date >= DATEADD(month, -3, GETDATE())
    GROUP  BY customer_id
  )
SELECT ac.customer_id, ro.order_count
FROM   active_customers ac
JOIN   recent_orders    ro ON ac.customer_id = ro.customer_id
ORDER  BY ro.order_count DESC;

Recursive CTE — org hierarchy

WITH RECURSIVE org_chart AS (
  -- Anchor: top-level (no manager above them)
  SELECT employee_id, employee_name, manager_id, 0 AS level
  FROM   employees WHERE manager_id IS NULL

  UNION ALL

  -- Recursive: each employee's direct reports
  SELECT e.employee_id, e.employee_name, e.manager_id, oc.level + 1
  FROM   employees e
  JOIN   org_chart oc ON e.manager_id = oc.employee_id
)
SELECT level, employee_name
FROM   org_chart ORDER BY level, employee_name;

---

9. Window functions

Window functions calculate across a set of rows related to the current row without collapsing them like GROUP BY does. Every window function requires an OVER() clause.

Syntax

function_name()
  OVER (
    [PARTITION BY column, ...]   -- split into groups (optional)
    [ORDER BY column, ...]       -- order within each group
    [ROWS/RANGE frame_clause]    -- limit the window frame (optional)
  )

Ranking functions

SELECT employee_name, department_id, salary,
  ROW_NUMBER() OVER (PARTITION BY department_id
                     ORDER BY salary DESC) AS row_num,
  RANK()       OVER (PARTITION BY department_id
                     ORDER BY salary DESC) AS rnk,
  DENSE_RANK() OVER (PARTITION BY department_id
                     ORDER BY salary DESC) AS dense_rnk,
  NTILE(4)     OVER (PARTITION BY department_id
                     ORDER BY salary DESC) AS quartile
FROM employees;

Aggregate window functions — running totals

SELECT order_id, order_date, amount,
  SUM(amount) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total,
  AVG(amount) OVER (PARTITION BY customer_id) AS customer_avg,
  COUNT(*)    OVER (PARTITION BY customer_id) AS order_count
FROM orders;

Offset functions — LAG / LEAD

SELECT sale_date, amount,
  LAG(amount,  1, 0) OVER (ORDER BY sale_date) AS prev_day,
  LEAD(amount, 1, 0) OVER (ORDER BY sale_date) AS next_day,
  amount - LAG(amount, 1, 0) OVER (ORDER BY sale_date) AS day_change,
  FIRST_VALUE(amount) OVER (
    PARTITION BY MONTH(sale_date)
    ORDER BY sale_date
  ) AS first_of_month
FROM daily_sales;

Classic pattern — top N per group

WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY department_id
      ORDER BY salary DESC
    ) AS rn
  FROM employees
)
SELECT * FROM ranked WHERE rn <= 3;   -- top 3 per department

---

10. DDL — tables, constraints & indexes

CREATE / DROP DATABASE

CREATE DATABASE northwind;
DROP DATABASE northwind;    -- permanently removes entire database

CREATE TABLE

CREATE TABLE persons (
  person_id   INT           NOT NULL PRIMARY KEY,
  last_name   VARCHAR(255)  NOT NULL,
  first_name  VARCHAR(255),
  age         INT           CHECK (age >= 18),
  email       VARCHAR(255)  UNIQUE
);

-- With foreign key, auto-increment, and default
CREATE TABLE orders (
  order_id     INT  NOT NULL AUTO_INCREMENT PRIMARY KEY,
  order_number INT  NOT NULL,
  person_id    INT,
  order_date   DATE DEFAULT GETDATE(),
  FOREIGN KEY (person_id) REFERENCES persons(person_id)
);

-- Create table from an existing table
CREATE TABLE test_table AS
SELECT customer_name, contact_name FROM customers;

ALTER TABLE

ALTER TABLE customers ADD email VARCHAR(255);
ALTER TABLE customers DROP COLUMN email;
ALTER TABLE customers ALTER COLUMN email TEXT;    -- SQL Server
ALTER TABLE customers MODIFY COLUMN email TEXT;   -- MySQL

TRUNCATE

TRUNCATE TABLE customers;   -- deletes all data, keeps table structure

SQL constraints

CREATE INDEX

CREATE INDEX idx_last_name ON persons (last_name);
-- Only index frequently-searched columns.
-- Indexes speed up reads but slow INSERT/UPDATE/DELETE.

Clustered vs non-clustered index

• Clustered index — physically reorders rows in the table to match the index. Faster retrieval. Only one per table.
• Non-clustered index — creates a separate object pointing to the data rows. Does not reorder the table. Multiple allowed per table.

BACKUP DATABASE

BACKUP DATABASE northwind TO DISK = 'C:\backups\northwind.bak';

-- Differential backup (only changes since last full backup)
BACKUP DATABASE northwind TO DISK = 'C:\backups\northwind_diff.bak'
WITH DIFFERENTIAL;

---

11. Views & triggers

A view is a virtual table based on a SQL query. It has no physical storage — data is pulled from underlying base tables each time. Views provide logical data independence: restructuring base tables doesn't break the view's consumers.

Advantages & disadvantages

CREATE VIEW brazil_customers AS
  SELECT customer_name, contact_name
  FROM customers
  WHERE country = 'Brazil';

SELECT * FROM brazil_customers;   -- query it like a table

DROP VIEW brazil_customers;

Database triggers

A trigger is SQL that automatically executes when a specific event occurs on a table: Before Insert, After Insert, On Update, or On Delete.

CREATE TRIGGER before_employee_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
  SET NEW.created_at = NOW();
END;

---

12. Stored procedures

Reusable SQL code saved in the database. Write once, call many times. Parameters make it dynamic.

-- No parameters
CREATE PROCEDURE select_all_customers
AS SELECT * FROM customers
GO;

EXEC select_all_customers;

-- One parameter
CREATE PROCEDURE get_customers_by_city @city NVARCHAR(30)
AS SELECT * FROM customers WHERE city = @city
GO;

EXEC get_customers_by_city @city = 'London';

-- Multiple parameters
CREATE PROCEDURE get_orders_in_range
  @start_date DATE,
  @end_date   DATE
AS
  SELECT * FROM orders
  WHERE order_date BETWEEN @start_date AND @end_date
GO;

---

13. Built-in functions

String functions

-- TRIM: removes leading and trailing spaces
SELECT TRIM('   Hello World   ');         -- 'Hello World'

-- COALESCE: returns first non-NULL value in a list
SELECT COALESCE(NULL, NULL, 'first non-null', 'ignored');

-- TRANSLATE: replaces characters (string, from_chars, to_chars)
SELECT TRANSLATE('Hello', 'He', 'Ja');    -- 'Jallo'

Numeric functions

-- TRUNCATE: cut to specified decimal places
SELECT TRUNCATE(345.156, 0);    -- 345
SELECT TRUNCATE(345.156, 2);    -- 345.15

Date functions

-- DATEDIFF: days between two dates
SELECT DATEDIFF('2024-12-31', '2024-01-01');    -- 365

-- DATE_ADD: add an interval
SELECT DATE_ADD('2017-06-15', INTERVAL 10 DAY);    -- 2017-06-25

-- DATE_SUB: subtract an interval
SELECT DATE_SUB('2017-06-15', INTERVAL 10 DAY);    -- 2017-06-05

-- DAYNAME: name of the day
SELECT DAYNAME('2017-06-15');    -- Thursday

-- EXTRACT: pull out part of a date
SELECT EXTRACT(MONTH FROM '2017-06-15');    -- 6
SELECT EXTRACT(YEAR  FROM '2017-06-15');    -- 2017
SELECT EXTRACT(DAY   FROM '2017-06-15');    -- 15

---

14. Backup types

---

15. Normalisation (1NF → 4NF)

Normalisation removes redundant data by splitting tables into well-structured smaller ones to maintain data integrity. Denormalisation deliberately adds redundancy back to speed up complex read queries.

---

16. Transactions, locks & concurrency

What is a transaction?

A sequence of operations treated as one atomic unit — either all succeed (COMMIT) or all are undone (ROLLBACK).

BEGIN TRANSACTION;
  UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
  UPDATE accounts SET balance = balance + 500 WHERE account_id = 2;
COMMIT;
-- ROLLBACK;  -- would undo both changes if an error occurred

Concurrency problems

Lock types

• Binary lock — simple on/off. Only one transaction can hold it at a time. Too restrictive for practical use.
• Shared (read) lock — multiple transactions can hold it simultaneously. No writes allowed while shared locks exist.
• Exclusive (write) lock — only one transaction can hold it. Blocks all other reads and writes.

Lock protocols

Timestamp-based concurrency control

Every transaction has a timestamp. Older transactions get higher priority. If two transactions conflict, the younger one may be rolled back and restarted. Example: a transaction at time 0002 is older than one at 0004 — if they conflict, 0002 proceeds.

---

17. SQL injection & security

SQL injection inserts malicious SQL through an input field to manipulate the database — bypassing logins, reading all data, or deleting records.

Three layers of defence

1. Parameterised queries / prepared statements — the most important defence. Query structure is fixed; user input is passed as data, never as executable SQL.

   txtSQL = "SELECT * FROM users WHERE user_id = @0";
   db.Execute(txtSQL, txtUserId);   -- input is data, not code

2. Input validation — verify type, length, and format before the input reaches the database. Reject anything that doesn't conform.
3. Escape special characters — use the driver's escape function (e.g. MySQL's mysql_real_escape_string()) as a secondary layer. Treat all user input as potentially malicious.