Retrieving Data from a Single Table

5 Clauses of the SELECT Statement

SELECT Describes the columns in the result set
FROM The table you are getting the data from
WHERE Filters the rows, only those that match the condition
ORDER BY How to sort the rows
LIMIT The number of rows to return

SELECT and FROM Statements

The SELECT statement is used to retrieve table data. To retrieve data you need what you want to select and from where you want to select it. 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 select_list
[FROM table_source]

There are 4 ways to code the select 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 replaced the column name, calculation or function. If there is 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

 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

SELECT statement uses the ROUND function with price as the parameter to round the price up 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 would result 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 would be 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 data 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 who 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 that are 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 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 condition and NOT to negate a search condition.

If you have 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.

 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. 

Select all products with their prices 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 with their prices 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 with their prices 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 can test an expression to a list of expressions in an IN phrase.

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 can compare an expression with a range of values. Select all products and their prices that have a price ranging between 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 string patterns to a test 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 prices 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 prices that end with ‘ll’ and have any number of character 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 prices that have one character before an ‘o’ and any number of character 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 

The IS NULL or IS NOT NULL will find all nulls or all that are not null as well. We only had one null in our table; the product category of the jump rope row.

Because NULLs are not values (they have no value), it is not possible to use comparison operators (=, < or >) with NULL.

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
 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

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 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]

So 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 was no LIMIT.

 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 in 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 the code.

SELECT
FROM
WHERE
ORDER BY
LIMIT

But the order in which the system 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 then limited.

This is why an alias that was set up in the SELECT clause can used in an ORDER BY but not a WHERE.

 SELECT prod_name, prod_price + 2.00 AS mark_up
 FROM products
 WHERE prod_price > 3.99
 ORDER BY mark_up
prod_name prod_price
bouncy ball 4.99
jump rope 4.99
bat 4.99
jacks 5.99
doll 5.99
mitt 7.99