| layout |
|---|
reference |
See [this cheat sheet]({{ page.root }}{% link files/sql-cheat-sheet.md %}) for an list of the commands covered in this lesson.
| Keyword | Definition | Example | Description |
|---|---|---|---|
| SELECT | Select data from database or table | SELECT * | Selects the entire dataset |
| SELECT column1 | Selects a particular column | ||
| SELECT 1 + 2 | Performs a calculation | ||
| FROM | Indicates the table from which the data is selected or deleted | SELECT year FROM surveys |
Query will display the desired column from the table |
| WHERE | Filter the result set so that only the values satisfying the condition are included | SELECT * FROM surveys WHERE year == 1990 |
Query will display all values from the table for which the year is 1990 |
| LIMIT | Retrieves the number of records from the table up to the limit value | SELECT * FROM surveys LIMIT 5 |
Query will will return only the first 5 rows from the table |
| DISTINCT | Select distinct values | SELECT DISTINCT year FROM surveys |
Query will display the distinct years present on the table |
| AS | Used as an alias to rename the column or table | SELECT 1 + 2 AS calc | Column will be renamed to "calc" |
| GROUP BY | Groups the result set | SELECT MAX(weight) FROM surveys GROUP BY year |
Query will display the max weight per year |
| HAVING | Used to filter grouped rows when using aggregate functions | SELECT MAX(weight) FROM surveys GROUP BY year HAVING MAX(weight) > 100 |
Filter the results by the years that have a maximum weight greater than 100g |
| JOIN | Joins tables | SELECT * FROM surveys JOIN species ON surveys.species_id = species.species_id |
Query will display all the columns from both tables where the condition is met |