Chapter 11 Working in SQL
In this section, we will cover and practive the basic skills associated with SQL.
SQL stands for structered query langauage and is a language that allows us to access and manipulate relational databases. It is common pratice to have structured data stored in databases, and SQL derives information of the data through queries.
11.1 SQL Fundamentals
11.1.1 SELECT statement
The SELECT statement is a common start to many queries. It is used when retrieving data from a table. The returned data will be in a table format.
The FROM statement denotes from which table to perform the query. In each of these example, the table is called 'nyc' because the data is New York City weather data.
The * denotes to select all columns from the table.
SELECT * FROM nyc| date | actual_mean_temp | actual_min_temp | actual_max_temp | average_min_temp | average_max_temp | record_min_temp | record_max_temp | record_min_temp_year | record_max_temp_year | actual_precipitation | average_precipitation | record_precipitation |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 2014-7-1 | 81 | 72 | 89 | 68 | 83 | 52 | 100 | 1943 | 1901 | 0.00 | 0.12 | 2.17 |
| 2014-7-2 | 82 | 72 | 91 | 68 | 83 | 56 | 100 | 2001 | 1966 | 0.96 | 0.13 | 1.79 |
| 2014-7-3 | 78 | 69 | 87 | 68 | 83 | 54 | 103 | 1933 | 1966 | 1.78 | 0.12 | 2.80 |
| 2014-7-4 | 70 | 65 | 74 | 68 | 84 | 55 | 102 | 1986 | 1949 | 0.14 | 0.13 | 1.76 |
| 2014-7-5 | 72 | 63 | 81 | 68 | 84 | 53 | 101 | 1979 | 1999 | 0.00 | 0.12 | 3.07 |
| 2014-7-6 | 75 | 66 | 84 | 68 | 84 | 54 | 103 | 1979 | 2010 | 0.00 | 0.13 | 1.97 |
| 2014-7-7 | 81 | 72 | 90 | 68 | 84 | 56 | 100 | 1914 | 2010 | 0.04 | 0.13 | 3.13 |
| 2014-7-8 | 81 | 71 | 91 | 69 | 84 | 56 | 100 | 1894 | 1993 | 0.39 | 0.14 | 1.80 |
| 2014-7-9 | 80 | 71 | 88 | 69 | 84 | 54 | 106 | 1963 | 1936 | 0.09 | 0.14 | 1.09 |
| 2014-7-10 | 78 | 72 | 83 | 69 | 84 | 55 | 102 | 1890 | 1993 | 0.00 | 0.15 | 1.79 |
We can also denote specific columns to select by using the column names.
SELECT date, actual_max_temp FROM nyc| date | actual_max_temp |
|---|---|
| 2014-7-1 | 89 |
| 2014-7-2 | 91 |
| 2014-7-3 | 87 |
| 2014-7-4 | 74 |
| 2014-7-5 | 81 |
| 2014-7-6 | 84 |
| 2014-7-7 | 90 |
| 2014-7-8 | 91 |
| 2014-7-9 | 88 |
| 2014-7-10 | 83 |
11.1.2 WHERE clause
To perfrom conditional queries, we can use the WHERE clause. The WHERE clause acts similar to an if statement by denoting which conditions must be upheld in order for the row to be selected in the query.
For example, we can find the dates on which the maximum temperature was greater than 75 degrees:
SELECT date, actual_max_temp FROM nyc WHERE actual_max_temp > 75| date | actual_max_temp |
|---|---|
| 2014-7-1 | 89 |
| 2014-7-2 | 91 |
| 2014-7-3 | 87 |
| 2014-7-5 | 81 |
| 2014-7-6 | 84 |
| 2014-7-7 | 90 |
| 2014-7-8 | 91 |
| 2014-7-9 | 88 |
| 2014-7-10 | 83 |
| 2014-7-11 | 86 |
11.1.3 AND operator
Often times, more than one conditional statement is neccessary when writing queries. The AND clause allows for more than one conditional statement that must eb uphelp for the row to be selected.
Let's Find the dates on which the maximum temperature was greater than 75 degrees but also less than or equal to 90 degrees:
SELECT date, actual_max_temp FROM nyc WHERE actual_max_temp > 75 AND actual_max_temp <= 90| date | actual_max_temp |
|---|---|
| 2014-7-1 | 89 |
| 2014-7-3 | 87 |
| 2014-7-5 | 81 |
| 2014-7-6 | 84 |
| 2014-7-7 | 90 |
| 2014-7-9 | 88 |
| 2014-7-10 | 83 |
| 2014-7-11 | 86 |
| 2014-7-12 | 85 |
| 2014-7-13 | 83 |
11.1.4 OR operator
Similiar to the AND operator, the OR operator assists with conditional statements. The OR operator will allows for the selection of rows that satisfy at least one of the provided conditions
To select the days where maximum temperature equaled 75 or 90, we can do the following:
SELECT date, actual_max_temp FROM nyc WHERE actual_max_temp = 75 OR actual_max_temp = 90| date | actual_max_temp |
|---|---|
| 2014-7-7 | 90 |
| 2014-8-5 | 90 |
| 2014-8-27 | 90 |
| 2014-8-31 | 90 |
| 2014-9-8 | 75 |
| 2014-9-20 | 75 |
| 2014-9-21 | 75 |
| 2015-5-15 | 75 |
| 2015-5-16 | 75 |
| 2015-5-18 | 75 |
11.1.5 ORDER BY keyword
SQL offers a great keyword for sorting queries. The ORDER BY keywword will sort the rows by the provided column in the query followed by another keyword- most commonly DESC or ASC. DESC will order the rows from greatest to least and ASC does the opposite.
For example, we can sort max temperature from least to greatest:
SELECT date, actual_max_temp FROM nyc ORDER BY actual_max_temp ASC;| date | actual_max_temp |
|---|---|
| 2015-2-20 | 19 |
| 2015-1-8 | 21 |
| 2015-2-13 | 21 |
| 2015-2-16 | 21 |
| 2015-1-6 | 22 |
| 2015-1-7 | 23 |
| 2015-1-10 | 23 |
| 2015-2-24 | 24 |
| 2015-2-15 | 25 |
| 2015-1-31 | 26 |