# SQL Syntax

## Keywords

`|`: Use for designating different things that can be used as arguments for the keywords
`( )`: Optional keywords
`[ ]`: Used for values, like integers, or column or table names.

| Keyword | Explanation | Example |
| ------- | ----------- | ------- |
| `USE` | Specify the database to be used | `USE WideWorldImporters` |
| `SELECT {ColumnID \| *}`  | selects the column to return (or can use wildcard `*`) | `SELECT ProductID FROM Products` |
| `FROM` | specify the thing to be selected from |
| `GO {count}` | Runs everything before it `count` times minus 1 | `GO 5` (runs everything before it 4 more times) |
| `TOP {n} (PERCENT - optional)` | Returns the top *n* (or *n*%) results - must be an integer . Order is undefined, but in most implementations is listed in the order they were inserted - NOT guaranteed behavior, just how most implementations use it. | `SELECT TOP 10 PERCENT VendorName  FROM Vendors` |
| `ORDER BY {column1, column2 (1 required, can use infinitely many)}` | Orders by some column (alphanumerically sorted, letters first); `TOP` is useless without this | `SELECT TOP 50 InvoiceID, VendorID FROM Invoices ORDER BY VendorID` |
| `DISTINCT` | Goes before the column names (and before TOP in MS SQL server, at least); removes rows with duplicate values | `SELECT DISTINCT VendorName FROM Invoices` |

<!-- Needs to be redone into more detailed sub-headings -->

## `CASE`/`WHEN`/`THEN`/`ELSE`/`END`

## Other syntax

- Brackets `[ ]`: Delineates that something is a table, usually not needed but allows for stuff like using keywords as table names.

## Other notes

- `SELECT * FROM Table`: Shouldn't be used normally, only in learning or if you're not sure what you need yet.
  - Bad because: it's requests every column of data in the table, and that's rarely needed, wasting time and resources.
  - Most database systems will just skip all qury optimizations if you do this, wasting even more time and resources.
  - Column ordering is undefined - the columns could be returned in any random order.
- Should usually do one keyword per line, except for simple stuff like `SELECT ColumnID FROM Table`
- Column aliases: Useful for:
  - Multi-table connections and for "renaming" columns
  - Making it more convenient to use (e.g. `Person` -> `Name` as an alias of `Person` -> `PersonName`)
  - To name an unnamed column (e.g. `SELECT LEFT(VendorName, 10) VendorShortName FROM Vendors`)

## No

Don't:

- Put spaces in a table's name (why would you???)
- Use `SELECT * FROM Table`