7 How Do We Retrieve Information from a Database Table?
5 Clauses of the SELECT Statement
| SELECT | Lists the columns in the result set |
| FROM | Specifies the table from where the data will be retrieved | WHERE | Filters the rows to only those that match the condition |
| ORDER BY | Specifies how to sort the rows |
| LIMIT | The number of rows to return |
SELECT and FROM Statements
The SELECT statement is used to retrieve data from a table. The SELECT statement lists the columns you want as part of the resulting data, or result set. The FROM statement tells what table you are getting the data or columns from.
The SELECT Statement Syntax
SELECT column_list
[FROM table_source]
There are 4 ways to code the column list in the SELECT statement.
1. All columns with an asterisk
SELECT * FROM products
| prod_id | prod_name | prod_category | prod_price |
|---|---|---|---|
| t12 | bouncy ball | toy | 2.99 |
| t15 | jacks | toy | 3.99 |
| t18 | doll | toy | 5.99 |
| t21 | top | toy | 1.99 |
| s10 | jump rope | null | 2.99 |
| s13 | hockey puck | sports | 1.99 |
| s17 | mitt | sports | 5.99 |
| s22 | bat | sports | 2.99 |
| s24 | baseball | sports | 1.99 |
The wildcard asterisk (*) is a fast way to list all the columns of the table during development but should be avoided when writing queries for production. Retrieving unnecessary columns slows down performance. It’s better to list only the columns you need for a given query.
2. By column name (each column separated with a comma)
SELECT prod_name, prod_price FROM products
| prod_name | prod_price |
|---|---|
| bouncy ball | 2.99 |
| jacks | 3.99 |
| doll | 5.99 |
| top | 1.99 |
| jump rope | 2.99 |
| hockey puck | 1.99 |
| mitt | 5.99 |
| bat | 2.99 |
| baseball | 1.99 |
3. Columns created with calculations
SELECT prod_name, prod_price + 2.00 FROM products
| prod_name | prod_price + 2.00 |
|---|---|
| bouncy ball | 4.99 |
| jacks | 5.99 |
| doll | 7.99 |
| top | 3.99 |
| jump rope | 4.99 |
| hockey puck | 3.99 |
| mitt | 7.99 |
| bat | 4.99 |
| baseball | 3.99 |
By default, the result set column header is the same name as the column, calculation or function. Notice “prod_price + 2.00” is the column header for the calculated column. You can rename a column to clarify the data in that column in the result set. The AS keyword allows you to rename the column. This is known as an alias. The string following the AS keyword replaces the column name, calculation or function. If there are spaces in the alias string you need to place quotes around it.
SELECT prod_name, prod_price + 2.00 AS ‘Markup Price’ FROM products
| prod_name | Markup Price |
|---|---|
| bouncy ball | 4.99 |
| jacks | 5.99 |
| doll | 7.99 |
| top | 3.99 |
| jump rope | 4.99 |
| hockey puck | 3.99 |
| mitt | 7.99 |
| bat | 4.99 |
| baseball | 3.99 |
When making calculations in the SELECT statement, you can use any of the following arithmetic operators:
* Multiplication / Division % Modulus + Addition - Subtraction
If more than one arithmetic operator is used they follow the order of precedence as they are listed in the table above.
4. Columns created with functions
Functions are not covered until a later lesson, but are mentioned here only to demonstrate that you can use functions in the SELECT statement.
SELECT prod_name, ROUND(prod_price) AS nearest_dollar FROM products
| prod_name | nearest_dollar |
|---|---|
| bouncy ball | 5 |
| jacks | 6 |
| doll | 8 |
| top | 4 |
| jump rope | 5 |
| hockey puck | 4 |
| mitt | 8 |
| bat | 5 |
| baseball | 4 |
The SELECT statement uses the ROUND function with price as the parameter to round the price to the nearest dollar. The alias is the string ‘nearest_dollar’.
The DISTINCT keyword can be used with SELECT to eliminate duplicate rows in a result set. For example without the DISTINCT keyword this SELECT statement results in all prices of each product listed.
SELECT prod_price FROM products
| prod_price |
|---|
| 2.99 |
| 3.99 |
| 5.99 |
| 1.99 |
| 2.99 |
| 1.99 |
| 5.99 |
| 2.99 |
| 1.99 |
With the DISTINCT keyword all duplicate prices are eliminated.
SELECT DISTINCT(prod_price) FROM products
| prod_price |
|---|
| 2.99 |
| 3.99 |
| 5.99 |
| 1.99 |
The WHERE Clause
The WHERE clause lets you retrieve just the rows you want. So only part of the table or column data shows up in the result set depending on the criteria in your WHERE clause. The WHERE clause filters the data.
The SELECT Statement Syntax with WHERE:
SELECT select_list
[FROM table_source]
[WHERE search_conditions]
You can use comparison operators to restrict information in the result set.
= Equal to < Less than <= Less than or equal to > Greater than >= Greater than or equal to <> or != Not equal to
If you wanted to restrict your result set to only those products that have a price greater than $5.00, you could use this WHERE clause.
SELECT prod_name, prod_price FROM products WHERE prod_price > 5.00
| prod_name | prod_price |
|---|---|
| doll | 5.99 |
| mitt | 5.99 |
Or those products priced less than or equal to 2.99.
SELECT prod_name, prod_price FROM products WHERE prod_price <= 2.99
| prod_name | prod_price |
|---|---|
| bouncy ball | 2.99 |
| top | 1.99 |
| jump rope | 2.99 |
| hockey puck | 1.99 |
| bat | 2.99 |
| baseball | 1.99 |
Or those products that are not priced equal to 2.99.
SELECT prod_name, prod_price FROM products WHERE prod_price <> 2.99
| prod_name | prod_price |
|---|---|
| jacks | 3.99 |
| doll | 5.99 |
| top | 1.99 |
| hockey puck | 1.99 |
| mitt | 5.99 |
| baseball | 1.99 |
You can also use multiple conditions with logical operators. Use AND and OR to combine two or more search conditions and NOT to negate a search condition.
AND Both conditions have to be true for it to show up in the result set OR One of the conditions has to be true for it to show up in the result set NOT Negates the condition.
If you write a WHERE clause with multiple conditions (or a compound condition), there is an order of precedence. First the NOT runs, then the AND, and then the OR. Use parenthesis to override this order of precedence.
Select all products and their price that have a price equal to 5.99 and also have the name of “doll”. There are two products that are 5.99 but only one with the name doll. Both conditions have to be true for the result to show up.
SELECT prod_name, prod_price FROM products WHERE prod_price = 5.99 AND prod_name = “doll”
| prod_name | prod_price |
|---|---|
| doll | 5.99 |
Select all products and their price that have a price equal to 5.99 or has a name equal to “bat”. We get both products that are equal to 5.99 and a product name equal to “bat”. So either condition can be true for the result to show up.
SELECT prod_name, prod_price FROM products WHERE prod_price = 5.99 OR prod_name = “bat”
| prod_name | prod_price |
|---|---|
| doll | 5.99 |
| mitt | 5.99 |
| bat | 2.99 |
Select all products and their price that do not have a price equal to 5.99. Reverses, or negates the condition and returns the opposite; all products with their prices that are not equal to 5.99.
SELECT prod_name, prod_price FROM products WHERE NOT prod_price = 5.99
| prod_name | prod_price |
|---|---|
| bouncy ball | 2.99 |
| jacks | 3.99 |
| top | 1.99 |
| jump rope | 2.99 |
| hockey puck | 1.99 |
| bat | 2.99 |
| baseball | 1.99 |
The IN operator tests if an expression is in a list.
Select all products and their prices that have a price of 2.99, 1.99 or 5.99.
SELECT prod_name, prod_price FROM products WHERE prod_price IN (2.99, 1.99, 5.99)
| prod_name | prod_price |
|---|---|
| bouncy ball | 2.99 |
| doll | 5.99 |
| top | 1.99 |
| jump rope | 2.99 |
| hockey puck | 1.99 |
| mitt | 5.99 |
| bat | 2.99 |
| baseball | 1.99 |
The BETWEEN operator compares an expression with a range of values. Select all products and their prices that have a price ranging between 1.99 and 3.99. It is inclusive and would also include 1.99 and 3.99.
SELECT prod_name, prod_price FROM products WHERE prod_price BETWEEN 1.99 AND 3.99
| prod_name | prod_price |
|---|---|
| bouncy ball | 2.99 |
| jacks | 3.99 |
| top | 1.99 |
| jump rope | 2.99 |
| hockey puck | 1.99 |
| bat | 2.99 |
| baseball | 1.99 |
The LIKE operator matches a string pattern to an expression. It uses two characters to represent characters. The _ underscore is a wild card to one character and the % percent is a wild card to any number of characters.
Select all products and their price that begin with ‘b’ and have any number of character after the ‘b’.
SELECT prod_name, prod_price FROM products WHERE prod_name LIKE “b%”
| prod_name | prod_price |
|---|---|
| bouncy ball | 2.99 |
| bat | 2.99 |
| baseball | 1.99 |
Select all products and their price that end with ‘ll’ and have any number of characters before the ‘ll’.
SELECT prod_name, prod_price FROM products WHERE prod_name LIKE “%ll”
| prod_name | prod_price |
|---|---|
| bouncy ball | 2.99 |
| doll | 5.99 |
| baseball | 1.99 |
Select all products and their price that have one character before an ‘o’ and any number of characters after the ‘o’.
SELECT prod_name, prod_price FROM products WHERE prod_name LIKE “_o%”
| prod_name | prod_price |
|---|---|
| bouncy ball | 2.99 |
| doll | 5.99 |
| top | 1.99 |
| hockey puck | 1.99 |
The REGEXP operator allows much more complex string patterns to test expressions. Regular Expressions have many different symbols and are beyond the scope of this course, but here is an example.
In this example the | bar pattern represents ‘matching any of the patterns’. So only product names that have a ‘ck’ or an ‘all’ string within them will show up.
SELECT prod_name, prod_price FROM products WHERE prod_name REGEXP 'ck|all';
| prod_name | prod_price |
|---|---|
| bouncy ball | 2.99 |
| jacks | 3.99 |
| hockey puck | 1.99 |
| baseball | 1.99 |
There are other patterns like:
^ matches the beginning of a string $ matches the end of a string […] any character inside the square brackets p1|p2 matches any of the patterns p1 or p2
A NULL value is when a value has been left blank when a row or record is added. It is different from a zero value or a field that contains spaces.
The IS NULL or IS NOT NULL will find all nulls or all values that are not null. We only had one null in our table; the product category of the jump rope row.
| prod_id | prod_name | prod_category | prod_price |
|---|---|---|---|
| t12 | bouncy ball | toy | 2.99 |
| t15 | jacks | toy | 3.99 |
| t18 | doll | toy | 5.99 |
| t21 | top | toy | 1.99 |
| s10 | jump rope | null | 2.99 |
| s13 | hockey puck | sports | 1.99 |
| s17 | mitt | sports | 5.99 |
| s22 | bat | sports | 2.99 |
| s24 | baseball | sports | 1.99 |
Because NULLs are not values (they have no value), it is not possible to use comparison operators (=, < or >) with NULL.
SELECT prod_name, prod_price
FROM products
WHERE prod_category IS NULL
| prod_name | prod_price |
|---|---|
| jump rope | 2.99 |
SELECT prod_name, prod_price FROM products WHERE prod_category IS NOT NULL
| prod_name | prod_price |
|---|---|
| bouncy ball | 2.99 |
| jacks | 3.99 |
| doll | 5.99 |
| top | 1.99 |
| hockey puck | 1.99 |
| mitt | 5.99 |
| bat | 2.99 |
| baseball | 1.99 |
The ORDER BY clause will sort your result set. You can specify one or more column names to sort the result set.
The SELECT Statement Syntax with ORDER BY:
SELECT select_list
[FROM table_source]
[WHERE search_conditions]
[ORDER BY order_by_list]
With this ORDER BY the result set is sorted alphabetically by product name.
SELECT prod_name, prod_price FROM products WHERE prod_price > 1.99 ORDER BY prod_name
| prod_name | prod_price |
|---|---|
| bat | 2.99 |
| bouncy ball | 2.99 |
| doll | 5.99 |
| jacks | 3.99 |
| jump rope | 2.99 |
| mitt | 5.99 |
With this ORDER BY the result set is sorted numerically by product price.
SELECT prod_name, prod_price FROM products ORDER BY prod_price
| prod_name | prod_price |
|---|---|
| top | 1.99 |
| hockey puck | 1.99 |
| baseball | 1.99 |
| jump rope | 2.99 |
| bat | 2.99 |
| jacks | 3.99 |
| doll | 5.99 |
| mitt | 5.99 |
With this ORDER BY the result set is sorted numerically by product price then within the product price it is also sorted by product name.
SELECT prod_name, prod_price FROM products ORDER BY prod_price, prod_name
| prod_name | prod_price |
|---|---|
| baseball | 1.99 |
| hockey puck | 1.99 |
| top | 1.99 |
| bat | 2.99 |
| bouncy ball | 2.99 |
| jump rope | 2.99 |
| jacks | 3.99 |
| doll | 5.99 |
| mitt | 5.99 |
You can reverse the sort from the default ascending (ASC) to descending using the keyword DESC. The ASC keyword is assumed unless DESC is used. Now the prices are sorted from largest price to smallest price.
SELECT prod_name, prod_price FROM products ORDER BY prod_price DESC
| prod_name | prod_price |
|---|---|
| mitt | 5.99 |
| doll | 5.99 |
| jacks | 3.99 |
| bat | 2.99 |
| jump rope | 2.99 |
| bouncy ball | 2.99 |
| baseball | 1.99 |
| hockey puck | 1.99 |
| top | 1.99 |
The LIMIT clause specifies the maximum number of rows that will be returned.
The SELECT Statement Syntax with LIMIT:
SELECT select_list
[FROM table_source]
[WHERE search_conditions]
[ORDER BY order_by_list]
[LIMIT row_limit]
In this statement, the result set is limited to 4 rows, even though there are 9 products in the table that would have been a part of the result set if there were no LIMIT clause.
SELECT prod_name, prod_price FROM products LIMIT 4
| prod_name | prod_price |
|---|---|
| bouncy ball | 2.99 |
| jacks | 3.99 |
| doll | 5.99 |
| top | 1.99 |
Remember we have to type the clauses in a specific order. SELECT, then FROM, then WHERE, then ORDER BY, and then LIMIT
This is the order of how you must write a select query.
SELECT
FROM
WHERE
ORDER BY
LIMIT
But the order in which the computer actually executes the clauses is different. This is referred to as the Order of Execution.
FROM
WHERE
SELECT
ORDER BY
LIMIT
The data from the entire table is retrieved, then that is filtered by the WHERE criteria. The columns, calculations, or functions that will show up are determined in SELECT. Then that resulting data is sorted and then limited.
The order of execution is why an alias that was set up in the SELECT clause can be used in an ORDER BY but not a WHERE.
SELECT prod_name, prod_price + 2.00 AS mark_up FROM products WHERE prod_price > 1.99 ORDER BY mark_up
| prod_name | mark_up |
|---|---|
| bouncy ball | 4.99 |
| jump rope | 4.99 |
| bat | 4.99 |
| jacks | 5.99 |
| doll | 5.99 |
| mitt | 7.99 |