# SQL — Queries, Joins, and Data Modeling Essentials

## SELECT Basics

Fetch data with `SELECT`, `FROM`, and optionally `WHERE`:

```sql
SELECT * FROM users;
SELECT id, name, email FROM users WHERE status = 'active';
SELECT DISTINCT country FROM users;
```

## WHERE Clauses

Filter rows with conditions:

```sql
SELECT * FROM orders WHERE amount > 100;
SELECT * FROM users WHERE created_at >= '2024-01-01' AND status = 'active';
SELECT * FROM products WHERE name LIKE 'Widget%';  -- starts with Widget
SELECT * FROM users WHERE email IN ('a@x.com', 'b@x.com');
SELECT * FROM users WHERE phone IS NOT NULL;
```

## JOINs

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

### INNER JOIN

Only rows with matches in **both** tables:

```sql
SELECT orders.id, orders.amount, users.name
FROM orders
INNER JOIN users ON orders.user_id = users.id;
```

### LEFT JOIN

All rows from left table; matching rows from right (NULL if no match):

```sql
SELECT users.name, orders.id
FROM users
LEFT JOIN orders ON users.id = orders.user_id;
-- Users with no orders still appear; orders columns are NULL
```

### RIGHT JOIN

All rows from right table; matching from left. (Less common; often rephrased as LEFT JOIN.)

### FULL OUTER JOIN

All rows from both tables; NULL where no match.

## JOIN Quick Reference

| Join Type | Left Table | Right Table |
|-----------|------------|-------------|
| INNER | Only matches | Only matches |
| LEFT | All | Matches only |
| RIGHT | Matches only | All |
| FULL | All | All |

## GROUP BY and Aggregation

Aggregate rows by a column:

```sql
SELECT status, COUNT(*) as count FROM orders GROUP BY status;
SELECT user_id, SUM(amount) as total FROM orders GROUP BY user_id;
SELECT category, AVG(price) as avg_price FROM products GROUP BY category;
```

**HAVING** filters after aggregation:

```sql
SELECT user_id, SUM(amount) as total
FROM orders
GROUP BY user_id
HAVING SUM(amount) > 1000;
```

## ORDER BY

Sort results:

```sql
SELECT * FROM products ORDER BY price DESC;
SELECT * FROM users ORDER BY name ASC, created_at DESC;
```

## Subqueries

Use a query inside another:

```sql
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);
SELECT name, (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) as order_count
FROM users;
```

## Indexes

Speed up lookups. Create on columns used in WHERE, JOIN, ORDER BY:

```sql
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_id ON orders(user_id);
```

## Normalization

Reduce redundancy and update anomalies.

**1NF:** Atomic values; no repeating groups. Each cell has one value.

**2NF:** 1NF + no partial dependencies. All non-key attributes depend on the whole primary key.

**3NF:** 2NF + no transitive dependencies. Non-key attributes don't depend on other non-key attributes.

## ER Diagram Example

```mermaid
erDiagram
    users ||--o{ orders : places
    products ||--o{ order_items : "ordered in"
    orders ||--o{ order_items : contains
    users {
        int id PK
        string name
        string email
    }
    orders {
        int id PK
        int user_id FK
        date created_at
    }
    products {
        int id PK
        string name
        decimal price
    }
    order_items {
        int order_id FK
        int product_id FK
        int quantity
    }
```

## Common Patterns

| Need | SQL |
|------|-----|
| Pagination | `LIMIT 10 OFFSET 20` |
| Unique values | `DISTINCT` or `GROUP BY` |
| Top N per group | Window functions or subquery |
| Exists check | `EXISTS (SELECT 1 FROM ...)` |