**Structured Query Language**
A mainstay of modern business and technology since 1974 when it was designed by Donald Chamberlain and Raymond Boyce. Used to access and work with [[Relational Databases]]. Introduced the concepts of [[Primary Key]] and the other types of keys.
An [[ISO]] Standard (63555).
## Dialects
Different SQL implementations are called *dialects* - and each dialect may extend upon (or change) the standard to add its own features. Common dialects include [[SQLite]], [[Postgres]], and [[MySQL]], amongst (dozens of) others.
## Basic Constructs
### Data Manipulation Language
There is a small set of "main" shared *verb* keywords across most (all?) dialects of SQL. They are typically (always?) in this order:
| Keyword | Intuitive Meaning |
| -------- | -------------------------------------------- |
| SELECT | What data/calculations you want to see? |
| FROM | Where are the data from? |
| WHERE | What conditions must the data meet? |
| GROUP BY | How do you want to aggregate data? |
| HAVING | What conditions apply to those aggregations? |
| ORDER BY | Which rows should be at the top? |
| LIMIT | How many rows do you want to see? |
t
> [!note]
> While that's the order in which the language wants you to *specify* your query, the **execution** order is:
> 1. **`FROM`** and **`JOIN`s**: Determine the raw dataset.
> 2. **`WHERE`**: Filter rows.
> 3. **`GROUP BY`**: Group the filtered rows.
> 4. **`HAVING`**: Filter the grouped rows.
> 5. **`SELECT`**: Determine which columns/expressions to output.
> 6. **`DISTINCT`**: Remove duplicate rows from the selected set.
> 7. **`ORDER BY`**: Sort the final result set.
> 8. **`LIMIT` / `OFFSET`**: Restrict the number of rows returned.
#### Joins
The basic JOIN syntax is:
```plaintext
FROM table_A A
(jointype) JOIN table_B B
ON (common criteria expression)
```
Joins follow this [[Venn Diagram]]:
![[Pasted image 20250608211623.png]]
### Data Definition Language
As opposed to *manipulating* the data, there's data *descriptions*. These, in my experience, are slightly more dependent on **dialect**, and typically easy to *read*, although they are difficult to *write* without looking things up or using [[Large Language Models|LLMs]]. These definition keywords include things like:
- (data types like) INT, BOOLEAN, etc
- PRIMARY KEY
- FOREIGN KEY
- NOT NULL
- UNIQUE
- DEFAULT value
- CHECK (condition)
And table-level verbs like
- CREATE TABLE
- ALTER TABLE
- DROP TABLE
### Common Table Expressions (CTEs)
This is the term for creating a temporary table in your query, then querying against that. This is different from subqueries, but has a similar effect. I think these are more readable. Example:
```sql
WITH SalesByRegion AS (
SELECT region, SUM(amount) AS total_sales
FROM orders
GROUP BY region
)
SELECT region, total_sales FROM SalesByRegion WHERE total_sales > 100000;
```
****
# More
## Source
- [[Wikipedia]]
- [SQL](https://en.wikipedia.org/wiki/SQL?wprov=sfti1)
- Grad school
- https://stackoverflow.com/questions/13997365/sql-joins-as-venn-diagram
- ChatGPT
## Related
- [[Entity-Relationship Diagrams]]
- [[Relational Databases]]
- [[Primary Key]]
- [[Foreign Key]]