Notes15 min readChathura Devinda

SQL: Complete Step-by-Step Foundation & Advanced Reference

Comprehensive step-by-step SQL reference covering PostgreSQL basics, CRUD, DDL/DML, constraints, string & date functions, joins, subqueries, window functions, and CTEs.

SQLPostgreSQLDatabaseBackendArchitecture

Complete SQL With Notes: Beginner to Advanced

1. Introduction to SQL & Databases

What is SQL?

SQL (Structured Query Language) is the standard language used to store, manipulate, and retrieve data from Relational Database Management Systems (RDBMS).

What is a Database?

A database is an organized collection of structured information or data stored electronically in a computer system.

SQL Applications: The CRUD Model

•

CREATE → INSERT

•

READ → SELECT

•

UPDATE → UPDATE

•

DELETE → DELETE

SQL (Relational) vs. NoSQL (Non-Relational)

Feature	Relational (SQL)	Non-Relational (NoSQL)
Data Format	Tables (structured rows & columns)	Key-Value, Document (JSON), Graph, Wide-Column
Schema	Static / Predefined strict schema	Dynamic / Flexible schema
Scaling	Vertical scaling (compute/storage upgrade)	Horizontal scaling (distributed clusters)
Performance	Slower with massive unstructured data	Easily scales with massive data volumes
Examples	PostgreSQL, MySQL, MS SQL Server, Oracle	MongoDB, Cassandra, HBase, Redis

Excel vs. SQL Database

Feature	Excel Spreadsheet	SQL Database
Target User	General office / untrained users	Trained developers & data professionals
Data Capacity	Limited data capacity	Built to store massive amounts of data
Integrity & Automation	Low data integrity due to manual entry	High data integrity with automated tasks
Search Performance	Basic search and filter capabilities	Advanced, high-speed search and filtering

Types of SQL Commands

•

DDL (Data Definition Language): Defines structure → CREATE, ALTER, DROP, TRUNCATE

•

DML (Data Manipulation Language): Modifies records → INSERT, UPDATE, DELETE

•

DQL (Data Query Language): Queries data → SELECT

•

DCL (Data Control Language): Manages user access permissions → GRANT, REVOKE

2. Data Types, Keys & Constraints

Common Data Types

•

Numeric: int / int4 (standard integer), bigint / int8 (large integers), float / numeric (decimal values), bool (TRUE/FALSE).

•

String: char(n) (fixed length), varchar(n) (variable length text up to n characters).

•

Date & Time: date (YYYY-MM-DD), time (HH:MI:SS), timestamp / datetime (YYYY-MM-DD HH:MI:SS), timestamptz (timestamp with timezone offset).

Primary Key vs. Foreign Key

•

Primary Key (PK): A unique column used to identify and locate records in a table. It must be unique, cannot contain NULL values, and a table can only have one primary key.

•

Foreign Key (FK): A column used to link two or more tables together by referencing another table's primary key. A table can contain multiple foreign keys with duplicate and NULL values.

SQL Constraints

•

NOT NULL: Ensures that a column cannot store NULL values.

•

UNIQUE: Guarantees all values in a column are different.

•

PRIMARY KEY: Combines NOT NULL and UNIQUE.

•

FOREIGN KEY: Enforces referential integrity across linked tables.

•

CHECK: Validates that column values satisfy a custom condition.

•

DEFAULT: Applies a fallback value when none is explicitly provided.

•

CREATE INDEX: Optimizes data retrieval speeds on indexed columns.

3. Database & Table Management (DDL)

Creating Tables

sqlCode Snippet

CREATE TABLE customer (
    CustID int8 PRIMARY KEY,
    CustName varchar(50) NOT NULL,
    Age int NOT NULL,
    City char(50),
    Salary numeric
);

Altering Table Structures

sqlCode Snippet

-- Add a new column
ALTER TABLE customer ADD COLUMN Gender varchar(10);

-- Modify data type
ALTER TABLE customer ALTER COLUMN Gender TYPE char(10);

-- Drop a column
ALTER TABLE customer DROP COLUMN Gender;

Drop vs. Truncate Table

•

TRUNCATE TABLE customer; → Deletes all records inside the table while preserving table structure.

•

DROP TABLE customer; → Permanently deletes the entire table and all of its data.

4. Manipulating Records (DML)

Inserting Data

sqlCode Snippet

INSERT INTO customer (CustID, CustName, Age, City, Salary)
VALUES
    (1, 'Amal', 30, Colombo', 500000),
    (2, 'Kamal', 19, 'Kandy', 220000),
    (3, 'Nimal', 35, 'Anuradhapura', 65000),
    (4, 'Sunil', 40, 'Galle', 100000);

Updating Data

sqlCode Snippet

UPDATE customer
SET CustName = 'Bimal', Age = 32
WHERE CustID = 3;

Deleting Data

sqlCode Snippet

DELETE FROM customer
WHERE CustID = 1;

5. Querying, Filtering & Sorting

SELECT & WHERE Basics

sqlCode Snippet

-- Query unique values with a filter
SELECT DISTINCT house 
FROM classroom
WHERE grade = 'A';

Query Execution Order

FROM ⟶ WHERE ⟶ GROUP BY ⟶ HAVING ⟶ SELECT ⟶ ORDER BY ⟶ LIMIT

Sorting and Limiting

sqlCode Snippet

SELECT column_name 
FROM table_name
ORDER BY column_name ASC   -- Use DESC for descending
LIMIT 5;                  -- Caps output to 5 rows

6. Built-In Functions

String Functions

•

UPPER(str) / LOWER(str): Converts text casing.

•

LENGTH(str): Returns character count.

•

SUBSTRING(str, start, length): Extracts a substring.

•

CONCAT(s1, s2): Combines multiple strings.

•

REPLACE(str, from, to): Replaces occurrences of a substring.

•

TRIM(str): Removes leading and trailing whitespace.

Aggregate Functions

•

COUNT(col): Returns count of rows/values.

•

SUM(col): Returns total sum of values.

•

AVG(col): Calculates average value.

•

MIN(col) / MAX(col): Returns minimum / maximum value.

•

ROUND(num, dec): Rounds a number to a specified decimal point.

Date & Timestamp Functions

•

NOW() / CURRENT_DATE / CURRENT_TIME: Returns current date, time, or timestamp.

•

EXTRACT(part FROM date_field): Extracts components (YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, DOW, DOY).

sqlCode Snippet

SELECT EXTRACT(MONTH FROM payment_date) AS payment_month
FROM payment;

7. Aggregation with GROUP BY & HAVING

•

GROUP BY: Groups rows with matching values into summary rows.

•

HAVING: Applies filtering conditions directly to aggregated groups.

sqlCode Snippet

SELECT mode, COUNT(amount) AS total_count, SUM(amount) AS total_revenue
FROM payment
WHERE payment_date >= '2020-01-01'
GROUP BY mode
HAVING COUNT(amount) >= 3
ORDER BY total_revenue DESC;

8. SQL Joins & Set Operations

Types of Joins

•

INNER JOIN: Returns matching records present in both tables.

•

LEFT JOIN: Returns all records from the left table and matched records from the right table.

•

RIGHT JOIN: Returns all records from the right table and matched records from the left table.

•

FULL OUTER JOIN: Returns all records when a match exists in either table.

sqlCode Snippet

SELECT c.customer_id, c.first_name, p.amount, p.mode, p.payment_date
FROM customer AS c
INNER JOIN payment AS p
ON c.customer_id = p.customer_id;

Self Join

A table joined with itself to resolve hierarchical/recursive relationships (such as employees to managers):

sqlCode Snippet

SELECT T2.empname AS Employee, T1.empname AS Manager
FROM emp AS T1
JOIN emp AS T2
ON T1.empid = T2.manager_id;

UNION vs. UNION ALL

•

UNION: Combines result sets and removes duplicate rows.

•

UNION ALL: Combines result sets and retains all duplicate rows.

sqlCode Snippet

SELECT cust_name, cust_amount FROM custA
UNION ALL
SELECT cust_name, cust_amount FROM custB;

9. Subqueries & Window Functions

Subqueries (Nested Queries)

An inner query nested inside an outer SELECT statement:

sqlCode Snippet

SELECT * 
FROM payment
WHERE amount > (
    SELECT AVG(amount) 
    FROM payment
);

Window Functions

Calculates aggregate, ranking, or analytic values across a specified window of rows without collapsing rows into a single output:

sqlCode Snippet

SELECT 
    new_id,
    new_cat,
    -- Aggregate Window Functions
    SUM(new_id) OVER(PARTITION BY new_cat ORDER BY new_id) AS "Total",
    AVG(new_id) OVER(PARTITION BY new_cat ORDER BY new_id) AS "Average",
    -- Ranking Window Functions
    ROW_NUMBER() OVER(ORDER BY new_id) AS "ROW_NUMBER",
    RANK() OVER(ORDER BY new_id) AS "RANK",
    DENSE_RANK() OVER(ORDER BY new_id) AS "DENSE_RANK",
    PERCENT_RANK() OVER(ORDER BY new_id) AS "PERCENT_RANK",
    -- Analytic / Value Window Functions
    FIRST_VALUE(new_id) OVER(ORDER BY new_id) AS "FIRST_VALUE",
    LAST_VALUE(new_id) OVER(ORDER BY new_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS "LAST_VALUE",
    LEAD(new_id, 2) OVER(ORDER BY new_id) AS "LEAD_by2",
    LAG(new_id, 2) OVER(ORDER BY new_id) AS "LAG_by2"
FROM test_data;

10. CASE Expressions & CTEs

CASE Expression

Conditional branching logic within SQL queries:

sqlCode Snippet

SELECT customer_id, amount,
    CASE
        WHEN amount > 100 THEN 'Expensive product'
        WHEN amount = 100 THEN 'Moderate product'
        ELSE 'Inexpensive product'
    END AS ProductStatus
FROM payment;

Common Table Expressions (CTEs)

A temporary named result set defined using the WITH clause to simplify complex multi-step queries:

sqlCode Snippet

WITH PaymentSummary AS (
    SELECT mode, MAX(amount) AS highest_price, SUM(amount) AS total_price
    FROM payment
    GROUP BY mode
)
SELECT p.*, ps.highest_price, ps.total_price
FROM payment p
JOIN PaymentSummary ps ON p.mode = ps.mode
ORDER BY p.mode;

Back to All Notes Explore Projects & Case Studies →