Review: SQL Sakila Example

To retrieve information from a database using SQL, the **SELECT** statement is used. This example is going to use Sakila and referencing the **city** table. The fields in the **city** table are: **city_id**, **city**, **country_id**, **last_update** SELECT * from city WHERE city LIKE 'Ab%';
SQL WHERE Clause - The WHERE clause in a SELECT (or other) SQL statements allows us to restrict the information that is returned from a database. For example, say we want all of the cities that are stored in the database that start with the letters "Ab" (like above). The WHERE clause will compare the *city* field in the *city* table, to see if it starts with "Ab" using the LIKE operator and a wildcard operator '%'. There are a number of different options for the WHERE clause. Here are a few additional options for use in a WHERE clause.. - Logical Operators: `<` `>` `<=` `>=` `=` `<>` - `BETWEEN`: inclusive range - `IN`: testing group membership - `NOT`: used for negation - `LIKE`: allows comparison with wildcards `%`, `_` - Logical `AND` and `OR`
SQL LIMIT Clause - To retrieve only a few records from a table in a database, use the LIMIT Clause. If the question asks for the first five records, then `LIMIT 5` would be added to the end of the query. The following example would retrieve the first 5 cities stored in the city table. SELECT * FROM city LIMIT 5; Imagine that you would only want to retrieve the city names (which are stored in the column city). The SELECT statement would look like this: SELECT city FROM city LIMIT 5;
Review: Aggregate Functions - SQL provides a number of functions that can be used within an SQL Query. Aggregate functions are allowed in two places in SQL. IN a SELECT clause or in the HAVING clause. Below we will ask for a total number of rows in a table. The appropriate function to use there will be `COUNT()`. Additional Aggregates include `MAX()`, `MIN()`, `AVG()`, and `SUM()`. Of note is the need to have a `GROUP BY` clause with the use of some of these functions.
Review: Aliases - **SQL aliases** are used to give a table, or a column in a table, a temporary name. The goal of **SQL aliases** is to make a query more readable. In particular, **aliases** are often used to make column names more readable. An alias will only exist for the duration of that query, and is created by using the SQL `AS` keyword. This keyword is used for both **table aliases** and **column aliases**. Use the SQL keyword `AS` to allow a different way to reference a table, or to display a different title for a column. Compare the two `SELECT` statements below using **column aliases**, in the first query, the columns will be printed with the headers `first_name` and `last_name`. In the second query, they headers will be `First Name` and `Last Name`. **Column aliases** enhance readability of the results. -- This one will display the column headers as first_name and last_name SELECT first_name, last_name FROM actor; -- This one will display the column headers as First Name and Last Name SELECT first_name AS "First Name", last_name AS "Last Name" FROM actor;
*It is now time to use the SQL `SELECT` statement. This lesson is a little different than the previous lessons. Each question will ask you to use one SQL statement. There are 20 SQL Coding Questions, all using the **Sakila** database. Refer back to the information shared to assist you in this lesson.* -

Week 7 - MySQL Part 2