Basics

SQL lets you create, read, update, and delete data stored in tables (rows = records, columns = fields).

Core building blocks

• Tables: Defined by a schema (column names + data types).

• Keys & relationships:

  • Primary key (PK): unique ID in a table (e.g., ClientID).

  • Foreign key (FK): column that points to a PK in another table (e.g., Projects.ClientID references Clients.ClientID).

Main SQL categories

• DDL (Data Definition Language): structure

  • This allows you to create what's known as a table.

    CREATE TABLE Clients (
      ClientID    INT PRIMARY KEY,
      Name        VARCHAR(100) NOT NULL,
      ZipCode     CHAR(5)
    );

• DML (Data Manipulation Language): data

  • This allows you to directly change data in the SQL database.

    INSERT INTO Clients (ClientID, Name, ZipCode)
    VALUES (1, 'Acme Co', '60601');
    
    UPDATE Clients SET ZipCode = '60602' WHERE ClientID = 1;
    
    DELETE FROM Clients WHERE ClientID = 1;

• DQL (Data Query Language): reading data (mostly SELECT)

  • This is allowing you to look for a certain criterion in the data and search for it without changing the data.

    SELECT Name, ZipCode
    FROM Clients
    WHERE ZipCode = '60601'
    ORDER BY Name ASC;

The SELECT “recipe”

SELECT      -- which columns or expressions
FROM        -- which table(s)
JOIN        -- how to connect tables
WHERE       -- row filters
GROUP BY    -- make groups
HAVING      -- filters on groups/aggregates
ORDER BY    -- sort results
LIMIT/OFFSET-- restrict count / paginate

Examples

1) Basic filter

SELECT Title, Budget
FROM Projects
WHERE Budget > 50000
ORDER BY Budget DESC;

2) JOIN (parent/child via PK↔FK)

SELECT c.Name AS Client, p.Title, p.Budget
FROM Clients c
JOIN Projects p ON c.ClientID = p.ClientID;  -- parent Clients to child Projects

3) WHERE logic

-- Therapies that include 'bath', 'hot', or 'electrical' (case-insensitive patterns vary by DB)
SELECT Name, UnitOfTime
FROM Therapies
WHERE Name ILIKE '%bath%' OR Name ILIKE '%hot%' OR Name ILIKE '%electrical%';  -- PostgreSQL
-- In MySQL/SQL Server, use LOWER(Name) LIKE '%bath%' etc.

4) Calculations & grouping

-- Count projects per client, only show clients with 3+ projects
SELECT c.Name, COUNT(*) AS ProjectCount, SUM(p.Budget) AS TotalBudget
FROM Clients c
JOIN Projects p ON p.ClientID = c.ClientID
GROUP BY c.Name
HAVING COUNT(*) >= 3
ORDER BY TotalBudget DESC;

5) Concatenation & filtering by zip (therapists not in 72511)

-- Dialect-specific string ops; this works in many DBs:
SELECT (FirstName || ' ' || LastName) AS FullName
FROM Therapists
WHERE ZipCode <> '72511';
-- In MySQL: CONCAT(FirstName, ' ', LastName)