# SQL Essentials for Fundamentals — Database Fundamentals

Source: https://www.skillbyai.com/en/database-fundamentals/r-sql

> Write core SQL: DDL, DML, joins, grouping and subqueries.

## The standard relational language

**SQL** is the declarative language of relational databases: you state **what** you want and the optimiser decides **how**. It has several parts. **DDL** (data definition) creates and changes schema objects: `CREATE TABLE`, `ALTER TABLE`, `DROP`, constraints and indexes. **DML** (data manipulation) reads and changes data: `SELECT`, `INSERT`, `UPDATE`, `DELETE`. **DCL** controls access: `GRANT`, `REVOKE`. **TCL** controls transactions: `BEGIN`, `COMMIT`, `ROLLBACK`, `SAVEPOINT`. A `SELECT` is evaluated logically in this order: **FROM** (and joins), **WHERE** (filter rows), **GROUP BY**, **HAVING** (filter groups), **SELECT** (compute columns), **ORDER BY**, then **LIMIT/OFFSET**, which explains why a column alias defined in SELECT cannot be used in WHERE. Joins come in INNER, LEFT, RIGHT and FULL OUTER forms. **NULL** means unknown, so comparisons with NULL yield UNKNOWN and you must use `IS NULL`. Subqueries, `EXISTS` and common table expressions (`WITH`) build complex queries from simple pieces.

## Core SQL on the university schema

Joins, grouping, HAVING and a correlated subquery.

```sql
-- average score per department, only departments with more than 50 enrolments
SELECT s.dept, ROUND(AVG(m.score), 1) AS avg_score, COUNT(*) AS enrolments
FROM students s
JOIN marks m ON m.roll_no = s.roll_no
GROUP BY s.dept
HAVING COUNT(*) > 50
ORDER BY avg_score DESC;

-- students with no marks recorded yet (LEFT JOIN + IS NULL)
SELECT s.roll_no, s.name
FROM students s
LEFT JOIN marks m ON m.roll_no = s.roll_no
WHERE m.roll_no IS NULL;

-- students scoring above their department average (correlated subquery)
SELECT s.name, m.subject, m.score
FROM students s JOIN marks m ON m.roll_no = s.roll_no
WHERE m.score > (SELECT AVG(m2.score) FROM marks m2
                 JOIN students s2 ON s2.roll_no = m2.roll_no
                 WHERE s2.dept = s.dept);
```

## WHERE filters rows, HAVING filters groups

A condition on an aggregate such as `COUNT(*) > 50` must go in HAVING, because WHERE runs before grouping. Conditions on plain columns belong in WHERE, where they reduce work earlier.

**Quiz:** Why can a column alias defined in SELECT not be used in the WHERE clause?

- [ ] Aliases are not allowed in SQL
- [ ] WHERE only accepts numbers
- [x] WHERE is evaluated logically before SELECT computes the aliased columns
- [ ] Aliases are only valid in GROUP BY

*Answer:* WHERE is evaluated logically before SELECT computes the aliased columns. Logical processing order is FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY.