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