CIT 111: Introduction to Databases

Prepare 04: Data Retrieval

This week you will...

• create single table queries using the SQL SELECT statement.
• explore clauses, keywords and operators that define what data is retrieved.
• learn what funcions are and how to use them in queries.

Instructions

This material will help you build a foundation for this week's topic. Test your understanding by taking this week's prepare quiz in Canvas. The quiz is open book and open note. You may retake the quiz up to 3 times to improve your score.

Terms to Know

SELECT:

A statement that describes the columns in the result set, used to retrieve table data.

FROM:

A command that indicated the table you are getting data from.

ORDER BY:

A keyword used to sort the rows of a result set.

LIMIT:

A clause used to show the number of rows to return in the result set.

DISTINCT:

A keyword used to eliminate duplicate rows in the result set.

WHERE:

A clause used to retrieve just the data you want; filters the data.

Arithmetic Operators:

Used to make calculations. Examples are multiplication (*), division (/), modulus (%), addition (+), and subtraction (-).

Comparison Operators:

Used to make comparisons to restrict information in the result set. Examples are equal to(=), less than(<), less than or equal to(<=), greater than (>), greater than or equal to(>=), and not equal to(<> or !=).

Logical Operators:

Use with multiple conditions to combine or negate search conditions. Examples are both conditions have to be true (AND), one of the conditions have to be true (OR) and negates the condition (NOT).

IN:

An operator that can test an expression to a list of expressions.

BETWEEN:

An operator that can compare an expression with a range of values.

LIKE:

Operator that matches string patterns to a test expression letting you filter the results according to certain string patterns.

REGEXP:

A regular expressions operator that allows much more complex string patterns to test expressions.

Null Value:

No value, blank. It is different from a zero value or a field that contains spaces, null values have been left blank when the row was added.

IS NULL (IS NOT NULL):

Constraints to test for null values. IS NULL for all nulls. IS NOT NULL for all that are not nulls.

ASC:

Keyword used with ORDER BY to sort the results in ascending order (smallest to largest).

DESC:

Keyword used with ORDER BY to sort the results in descending order (largest to smallest).

Order of Execution:

The order the system actually executes clauses. This is a different order from how the clauses are written in SQL code.

Function:

A stored program, or operation that when performed on data will returns a value or manipulated or convert the data.

Scalar Function:

A function that works on a single entity of data, not a group of data together.

Parameter:

Used to pass data values to a function. SQL function parameters are inside parentheses following the function name and if there is more than one parameter they are separated by a comma.

Concatenate:

Combining values, by appending them to each other, to form a single longer value.

Index numbering:

The number related to each character of a string. SQL index numbering starts at 1 for the first character.

Retrieving Data

Watch the single table queries video:

(16:38 mins, "Single Table Queries" Transcript)

The SELECT Statement

As you have learned, the SELECT statement is used to retrieve data from a database. Now you will study the clauses that make up SELECT statement.

5 Clauses of the SELECT Statement

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

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

3. Columns created with calculations

SELECT prod_name, prod_price + 2.00 
FROM products

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

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

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

With the DISTINCT keyword all duplicate prices are eliminated.

      SELECT DISTINCT(prod_price)
FROM products

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]

       =	   	   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 

Or those products priced less than or equal to 2.99.

SELECT prod_name, prod_price
FROM products
WHERE prod_price <= 2.99 

Or those products that are not priced equal to 2.99.

      SELECT prod_name, prod_price
FROM products
WHERE prod_price <> 2.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'

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' 

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 

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)

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

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

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' 

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

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

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.

SELECT prod_name, prod_price
FROM products
WHERE prod_category IS NULL

SELECT prod_name, prod_price
       FROM products
       WHERE prod_category IS NOT NULL
      

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:

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

With this ORDER BY the result set is sorted numerically by product price.

SELECT prod_name, prod_price
FROM products
ORDER BY prod_price

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

You can reverse the sort from the default ascending (ASC) to descending using the keyword DESC. The 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

The LIMIT clause specifies the maximum number of rows that will be returned.

The SELECT Statement Syntax with 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

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

Watch the functions video:

(11:36 mins, "Functions" transcript)

Using Functions

In MySQL, a function is a stored program, or operation that when performed on data will returns a value or manipulate or covert the data. This allows us to perform manipulations on the data before retrieving or saving it. Functions have a name that you refer to and can also accept one or more parameters that can be passed into the function.

In this lesson, we will be looking at scalar functions that operate on the individual rows of a table. Scalar means the function will work on a single entity of data, not a group of data together. So if the function is used with a table with many rows the function will work on each single value or entity of data in each row.

Types of Functions:

We will be going over some of the more common text, numeric, and date & time functions.

Commonly used Text-manipulation or String Functions

Let's use the keyword table to demonstrate some of the functions.

SELECT * FROM keyword

SELECT CONCAT('The primary key of  ', keyword, ' is ', keyword_id) AS new_string, 
FROM keyword
WHERE keyword_id = 1

The result is made up of string literals inside quotes and column values all combined into one new string.

SELECT  LEFT(keyword, 4), LENGTH(keyword), UPPER(keyword)
FROM key
WHERE keyword_id = 2

The first column takes the string 'landscape' and returns 4 characters from the left.
The second column adds up all the characters in the string 'landscape'
The third column converts all the letters in the string 'landscape' to upper case.

Index numbering of the characters in the string 'landscape'

SELECT LOCATE( 'and', keyword), SUBSTRING(keyword, 1, 5), SUBSTRING(keyword, 7)
FROM key
WHERE keyword_id  = 2

The first column searches for the substring 'and' inside of the string 'landscape' and returns the index number of where it was found. the 2nd character in is where the 'a' in 'and' starts.
The second column starts at the first character and returns all character from there to the fifth character.
The third column starts at the seventh character and returns the rest of the string from there.

Commonly used Numeric Functions

Lets use the magazine table to demonstrate some of the numeric functions

 SELECT * FROM magazine

SELECT ROUND(magPrice), ROUND(magPrice, 1)
FROM magazine

The first column rounds to the nearest whole number.
The second column rounds to one decimal place.

SELECT magPrice, FLOOR(magPrice), CEILING(magPrice)
FROM magazine

The number 13.95 has the next lower whole number of 13 and next larger whole number of 14.
The number 15.45 has the next lower whole number of 15 and the next larger whole number of 16.
The number 17.50 has the next lower whole number of 17 and the next larger whole number of 18.
The number 18.00 has the next lower whole number of 18 and the next larger whole number of 18.
The number 25.00 has the next lower whole number of 25 and the next larger whole number of 25.

SELECT SQRT(magPrice)
FROM magazine
WHERE magKey = 5

The square root of 25 is 5.

If you wanted to manipulate your magazine prices to look more like currency you can use the FORMAT function. The first parameter is the number, the second the decimal places and the third is the locale which determines the thousands separator. Ours will be 'en_US' to show US English formatting. 10,000 dollars will be added to each magazine price so we can see the commas placed at each thousands grouping. The CONCAT function will also be used to add the '$' dollar sign to the number.

SELECT CONCAT('$', FORMAT(magPrice + 10000, 2, 'en_US')) 
FROM magazine

Commonly used Date and Time Functions

Let's use the employee table to demonstrate some of the date and time functions.

SELECT * FROM employee

SELECT hire_date, YEAR(hire_date), MONTH(hire_date) 
FROM employee

The YEAR function takes the hire date and extracts only the year and the MONTH function only extracts out the month from the date.

SELECT NOW()

The NOW function returns the systems current date. When you run this function it will be the current date and time of your system.

SELECT hire_date AS Hire Date, DATE_ADD(hire_date, INTERVAL 6 MONTH) AS 'Six Months Later'
FROM employee

SELECT DATEDIFF(hire_date, '2013-01-01') AS 'Days Between'
FROM employee

This gives the difference in days between January 1st 2013 and the hire date. If you want years, you would need to divide it by 365.

SELECT DATEDIFF(hire_date, '2013-01-01') / 365 AS 'Years Between'
FROM employee

The DATE_FORMAT function allows you to format the date so it isn't always in the format of yyyy-mm-dd.

SELECT hire_date, DATE_FORMAT(hire_date, '%M %D, %Y') AS 'hire date formatted'
FROM employee

The second parameter is a format string that includes codes for formatting the different parts of the date. Here are some of the common codes for formatting dates:

There are also format codes for formatting times with the function TIME_FORMAT. But we won't be using any of those in this course.

There are many more functions than we have not covered here, but for the depth of this course, these are the text, numeric, and date & time functions we will be focusing on.

Submission

Return to Canvas and take the Week 03 Prepare Quiz.

Submission

Take the Week Prepare quiz in Canvas

You may use this quizlet to help you study the terms.

Useful Links:

• Next Activity: Querying Databases
• Go to: Week Index • Course Home • Canvas