# SQL Cheatsheet

Core SQL for L9 and Lab 5. Examples use SQLite/PostgreSQL syntax.

## DDL — schema

```sql
CREATE TABLE students (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  name        TEXT NOT NULL,
  email       TEXT UNIQUE NOT NULL,
  age         INTEGER CHECK (age >= 0),
  created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE enrollments (
  student_id  INTEGER NOT NULL,
  course_id   INTEGER NOT NULL,
  grade       TEXT,
  PRIMARY KEY (student_id, course_id),
  FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
  FOREIGN KEY (course_id)  REFERENCES courses(id)
);

ALTER TABLE students ADD COLUMN phone TEXT;
DROP TABLE students;
```

## CRUD

```sql
-- CREATE
INSERT INTO students (name, email) VALUES ('Asha', 'asha@x.com');

-- READ
SELECT id, name FROM students WHERE age > 18 ORDER BY name LIMIT 10;

-- UPDATE
UPDATE students SET age = 23 WHERE id = 5;

-- DELETE
DELETE FROM students WHERE id = 5;
```

## SELECT — clauses in order

```sql
SELECT   col1, col2
FROM     table
JOIN     other ON ...
WHERE    condition
GROUP BY col1
HAVING   aggregate_condition
ORDER BY col1 DESC
LIMIT    10 OFFSET 20;
```

## Filtering

```sql
WHERE age BETWEEN 18 AND 25
WHERE name LIKE 'A%'              -- starts with A
WHERE name LIKE '%a%'             -- contains a
WHERE id IN (1, 2, 3)
WHERE phone IS NULL
WHERE NOT (active = 1)
```

## Joins

```sql
-- Inner: only rows that match both sides
SELECT s.name, c.title
FROM students s
JOIN enrollments e ON e.student_id = s.id
JOIN courses c ON c.id = e.course_id;

-- Left: all from left, NULL on right when no match
SELECT s.name, c.title
FROM students s
LEFT JOIN enrollments e ON e.student_id = s.id
LEFT JOIN courses c ON c.id = e.course_id;
```

| Join type | Returns |
|-----------|---------|
| `INNER JOIN` | rows in **both** sides |
| `LEFT JOIN`  | all from left + matches |
| `RIGHT JOIN` | all from right + matches |
| `FULL OUTER` | union of left + right |

## Aggregation

```sql
SELECT COUNT(*) AS total FROM students;
SELECT AVG(age), MIN(age), MAX(age), SUM(age) FROM students;

SELECT course_id, COUNT(*) AS n
FROM enrollments
GROUP BY course_id
HAVING COUNT(*) > 5
ORDER BY n DESC;
```

## Subqueries & CTEs

```sql
SELECT name FROM students
WHERE id IN (SELECT student_id FROM enrollments WHERE course_id = 7);

-- Common Table Expression (cleaner)
WITH busy AS (
  SELECT student_id, COUNT(*) AS n
  FROM enrollments GROUP BY student_id
)
SELECT s.name, b.n
FROM students s JOIN busy b ON b.student_id = s.id
WHERE b.n > 3;
```

## Transactions

```sql
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;        -- or ROLLBACK on error
```

ACID = Atomicity, Consistency, Isolation, Durability.

## Indexes

```sql
CREATE INDEX idx_students_email ON students(email);
CREATE UNIQUE INDEX idx_students_email ON students(email);
DROP INDEX idx_students_email;
```

Add indexes on columns you frequently `WHERE`, `JOIN`, or `ORDER BY` on.

## SQL injection — DON'T

```js
// ✗ NEVER concatenate user input into SQL
const q = `SELECT * FROM users WHERE email = '${email}'`;

// ✓ Use parameterised queries
db.run("SELECT * FROM users WHERE email = ?", [email]);

// ✓ Or named params (Spring / Django ORMs)
em.createQuery("FROM User u WHERE u.email = :email")
  .setParameter("email", email);
```

## Tools & references

- SQLBolt interactive tutorial: <https://sqlbolt.com/>
- PostgreSQL docs: <https://www.postgresql.org/docs/>
- SQLite docs: <https://www.sqlite.org/docs.html>
- DB Fiddle: <https://www.db-fiddle.com/>
- DBeaver (free GUI client): <https://dbeaver.io/>