# 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.

    ```sql
    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.&#x20;

    ```sql
    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. &#x20;

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

## The SELECT “recipe”

```sql
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&#x20;

#### 1) Basic filter

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

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

```sql
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

```sql
-- 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

```sql
-- 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)

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