> For the complete documentation index, see [llms.txt](https://dailyjournal.gitbook.io/notes/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://dailyjournal.gitbook.io/notes/languages/sql.md).

# SQL

Structured Query Language

An **entity-relationship diagram** (ERD) is a common way to view how data is structured in a database.

<figure><img src="https://1151231797-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MQAaqgCwmb83T8T0x2j%2Fuploads%2FAkvYKZbxKPFjWM0wWCF6%2Fimage.png?alt=media&amp;token=7e4dd5dc-b04f-46b1-9b24-570929e01e87" alt=""><figcaption><p><a href="https://video.udacity-data.com/topher/2020/May/5eb5533b_parch-and-posey/parch-and-posey.sql">Parch &#x26; Posey Database ERD</a></p></figcaption></figure>

**Structured Query Language** (SQL) is a language that allows us to access data stored in a database.

## Types of SQL Databases <a href="#types-of-databases" id="types-of-databases"></a>

1. MySQL
2. Access
3. Oracle
4. Microsoft SQL Server
5. Postgres

{% embed url="<https://www.digitalocean.com/community/tutorials/sqlite-vs-mysql-vs-postgresql-a-comparison-of-relational-database-management-systems>" %}

## Clauses

1. `SELECT`: This clause specifies the columns from the data source that should be included in the query results.
2. `FROM`: This clause specifies the data source(s) that should be queried. This can be a table, a view, or the results of another query.
3. `WHERE`: This clause specifies conditions that must be met for a row to be included in the query results.
4. `GROUP BY`: This clause groups the rows of the query results by the values in one or more columns, allowing aggregate functions to be applied to each group.
5. `HAVING`: This clause is used to filter the groups in the query results, based on the values of aggregate functions applied to each group.
6. `ORDER BY`: This clause specifies the order in which the rows of the query results should be returned.
7. `LIMIT`: This clause limits the rows returned.

## Query Execution Order

<table><thead><tr><th width="92.33333333333331">Order</th><th width="122">Clause</th><th>Function</th></tr></thead><tbody><tr><td>1</td><td><code>FROM</code></td><td>Tables are joined to get the base data.</td></tr><tr><td>2</td><td><code>WHERE</code></td><td>The base data is filtered.</td></tr><tr><td>3</td><td><code>GROUP BY</code></td><td>The filtered base data is grouped.</td></tr><tr><td>4</td><td><code>HAVING</code></td><td>The grouped base data is filtered.</td></tr><tr><td>5</td><td><code>SELECT</code></td><td>The final data is returned.</td></tr><tr><td>6</td><td><code>ORDER BY</code></td><td>The final data is sorted.</td></tr><tr><td>7</td><td><code>LIMIT</code></td><td>The returned data is limited to row count.</td></tr></tbody></table>

{% embed url="<https://www.essentialsql.com/what-is-a-query-plan/>" %}

{% hint style="info" %}
It is common and best practice to capitalize all SQL commands, like **`SELECT`** and **`FROM`**, and keep everything else in your query lower case.
{% endhint %}

## Resources

{% file src="/files/yEPxOJicyVSCWEj6SNMM" %}

{% embed url="<https://datalemur.com/>" %}

{% embed url="<https://mystery.knightlab.com/>" %}

{% embed url="<https://selectstarsql.com/>" %}

{% embed url="<https://sqlpd.com/>" %}

{% embed url="<https://sql-island.informatik.uni-kl.de/>" %}

{% embed url="<https://mode.com/sql-tutorial/>" %}

{% embed url="<https://www.techonthenet.com/index.php>" %}