SELECT Statement
Asst. Prof. Dr. Nuchjarin Intalar
SELECT Clause 33
• USE USE Database_name;
Choose database
• SELECT SELECT *
Display data you want FROM the table FROM table_name;
Select either all by using the asterisk (*)
wildcard character or particular columns
using column name with comma
SELECT column_name, column_name, column_name
FROM table_name;
Asst. Prof. Dr. Nuchjarin Intalar
SELECT Clause: Select All (*) 34
Query
Output
Asst. Prof. Dr. Nuchjarin Intalar
SELECT Clause: Select multiple columns 35
SELECT customer_id, first_name, last_name
Query FROM sql_store.customers;
Output
Asst. Prof. Dr. Nuchjarin Intalar
SELECT Clause: Limit Results 36
• You can limit the number of SELECT Columns you want to see
rows of data by using LIMIT FROM Table Name
LIMIT Number of records;
SELECT customer_id, first_name, last_name
FROM sql_store.customers
LIMIT 5;
Asst. Prof. Dr. Nuchjarin Intalar
Adding COMMENT to SQL 37
• Comments help you remember what you were doing and why.
• Mute the expression of code (commenting out code).
• Troubleshoot query issues systematically
Single Line (- -)
SELECT customer_id
,first_name
,last_name
FROM sql_store.customers
-- LIMIT 5;
Asst. Prof. Dr. Nuchjarin Intalar
Adding COMMENT to SQL 38
Section (/*….*/)
SELECT customer_id
/* ,first_name
,last_name
*/
FROM sql_store.customers;
Asst. Prof. Dr. Nuchjarin Intalar
DISTINCT 39
• Use to shown only unique values in column
SELECT DISTINCT order_id
FROM sql_store.order_items;
Asst. Prof. Dr. Nuchjarin Intalar
Basic of Filtering
with SQL
Asst. Prof. Dr. Nuchjarin Intalar
Learning Objectives 41
• Describe the basics of filtering your data
• Use the WHERE clause with common operators
• Use the BETWEEN clause
• Explain the concept of a NULL value
Asst. Prof. Dr. Nuchjarin Intalar
Why Filter? 42
• Be specific about the data you want to retrieve
• Reduce the number of records you retrieve
• Increase query performance
• Reduce the strain on the client application
• Governance limitations
Asst. Prof. Dr. Nuchjarin Intalar
WHERE Clause Operators 43
SELECT column_name, column_name, column_name
FROM table_name
WHERE column_name operator value;
Operator Description
= Equal
<> Not equal. (Note: In some versions of SQL this operator may be written as !=
> Greater than
< Less than
>= Greater than or equal to
<= Less than or equal to
BETWEEN Between an inclusive range
IS NULL Is a null value
Asst. Prof. Dr. Nuchjarin Intalar
WHERE Clause Operators 44
SELECT customer_id, first_name, last_name
FROM sql_store.customers
WHERE customer_id = 1;
Asst. Prof. Dr. Nuchjarin Intalar
WHERE Clause Operators 45
SELECT customer_id, first_name, last_name
FROM sql_store.customers
WHERE first_name = 'Ines';
Asst. Prof. Dr. Nuchjarin Intalar
WHERE Clause Operators 46
SELECT *
FROM sql_store.customers
WHERE state <> 'CA';
Asst. Prof. Dr. Nuchjarin Intalar
WHERE Clause Operators 47
SELECT *
FROM sql_store.customers
WHERE phone IS NULL;
Asst. Prof. Dr. Nuchjarin Intalar
BETWEEN Clause Operators 48
SELECT *
FROM sql_store.customers
WHERE points BETWEEN 200 AND 500;
Asst. Prof. Dr. Nuchjarin Intalar
Advanced Filtering:
IN, OR, and NOT
Asst. Prof. Dr. Nuchjarin Intalar
Learning Objectives 50
• Use the IN and OR operators to filter your data and get results you want
• Differentiate between use of the IN and BETWEEN operators
• Discuss importance of order of operations
• Explain how and when to use the NOT operator
Asst. Prof. Dr. Nuchjarin Intalar
IN Operator 51
• Specifies a range of conditions SELECT column_name
• If the conditions are strings, you need FROM table_name
single quote (‘..’) or double quotes (“..”) WHERE column_name IN (conditions);
SELECT *
FROM sql_store.customers
WHERE state IN ('MA','CA','TX');
Asst. Prof. Dr. Nuchjarin Intalar
OR Operator 52
• Select any rows matching the specific SELECT column_name
conditions
FROM table_name
• DMBS will not show the second WHERE column_name = (conditions) OR (conditions);
conditions if the first condition is met
SELECT *
FROM sql_store.customers
WHERE state = 'MA' OR 'CA';
Asst. Prof. Dr. Nuchjarin Intalar
Benefits of IN 53
• Long list of options
• IN executes faster than OR
• Don’t have to think about the order with IN
• Can contain another SELECT
Asst. Prof. Dr. Nuchjarin Intalar
OR with AND 54
SELECT *
FROM sql_store.customers
WHERE state = 'MA' OR 'CA'
AND points > 500;
Asst. Prof. Dr. Nuchjarin Intalar
NOT Operator 55
SELECT *
FROM sql_store.customers
WHERE NOT state = 'MA' AND
NOT state = 'CA';
Asst. Prof. Dr. Nuchjarin Intalar
Sorting with
ORDER BY
Asst. Prof. Dr. Nuchjarin Intalar
Why Sort Data? 57
• Data displayed appears in the order of the underlying tables
• Updated and deleted data can change this order
• Sequence of retrieved data cannot be assumed if order was not
specified
• Sorting data logically helps keep information you want on top
• ORDER BY clause allows user to sort data by particular columns
Asst. Prof. Dr. Nuchjarin Intalar
Rules for ORDER BY 58
• Takes the name of one or more columns
• Add a comma after each additional column name
• Can sort by a column not retrieved
• Must always be the last clause in a select statement
Asst. Prof. Dr. Nuchjarin Intalar
Sorting Direction 59
DESC descending order
(eg. Largest -> Smallest, Newest -> Oldest)
10 1, Z A
ASC ascending order
(eg. Smallest -> Largest, Oldest -> Newest)
1 10, A Z
Asst. Prof. Dr. Nuchjarin Intalar
ORDER BY Operators 60
SELECT *
FROM sql_store.customers
WHERE points > 500
ORDER BY state;
Asst. Prof. Dr. Nuchjarin Intalar
ORDER BY Operators 61
SELECT *
FROM sql_store.customers
WHERE points > 500
ORDER BY state DESC;
Asst. Prof. Dr. Nuchjarin Intalar
Using Wildcards in
SQL
Asst. Prof. Dr. Nuchjarin Intalar
Learning Objectives 63
• Explain the concept of wildcards, advantages, and disadvantages, usefulness
• Describe how to use the LIKE operator with wildcards
• Write appropriates syntax when using wildcards
Asst. Prof. Dr. Nuchjarin Intalar
What are Wildcards? 64
• Special character used to match parts of a value
• Search pattern made form literal text, wildcard character, or a combination
• Uses LIKE as an operator
• Can only be used with strings
• Cannot be used for non-text data types
Asst. Prof. Dr. Nuchjarin Intalar
Using % Wildcards 65
Wildcard Action
‘%Springs’ Grabs anything ending with the word Springs
‘Springs%’ Grabs anything after with the word Springs
‘%Springs%’ Grabs anything before and after the word Springs
Grabs anything that starts with “S” and ends with “P”
‘S%P’
(Like Soap)
‘s%@gmail.com’ Grabs email address that starts with “s”
• % wildcard will not match NULLs
Asst. Prof. Dr. Nuchjarin Intalar
Using % Wildcards 66
• Matches any number of characters
SELECT *
FROM sql_store.customers
WHERE address LIKE '%circle';
Asst. Prof. Dr. Nuchjarin Intalar
Using % Wildcards 67
• Matches a single character
SELECT *
FROM sql_store.customers
WHERE address LIKE '%c%';
Asst. Prof. Dr. Nuchjarin Intalar
Downsides of Wildcards 68
• Takes longer to run
• Better to use another operator (if possible): =, <, =>, and etc.
• Statements with wildcards will take longer to run if used at the end of search
patterns
• Placement of wildcards is important
Asst. Prof. Dr. Nuchjarin Intalar
Math Operations
Asst. Prof. Dr. Nuchjarin Intalar
Math Operators 70
Operator Description
+ Addition
- Subtraction
* Multiplication
/ Division
Asst. Prof. Dr. Nuchjarin Intalar
Multiplication Example 71
• Total units on order multiplied by the unit price to calculate the total order cost
SELECT order_id, product_id,
quantity * unit_price
AS Total_Order_Cost
FROM sql_store.order_items;
Asst. Prof. Dr. Nuchjarin Intalar
Aggregate
Functions
Asst. Prof. Dr. Nuchjarin Intalar
What are Aggregate Functions? 73
• Used to summarize data
Function Description
• Finding the highest and lowest
AVG() Average a column of values
values
• Finding the total number of rows COUNT() Counts the number of values
• Finding the average value MIN() Finds the minimum value
MAX() Finds the maximum value
SUM() Sums the column values
Asst. Prof. Dr. Nuchjarin Intalar
AVERAGE Function 74
• Rows containing NULL values are ignored by the AVERAGE function
SELECT AVG(unit_price) AS avg_price
FROM sql_store.order_items;
Asst. Prof. Dr. Nuchjarin Intalar
COUNT Function 75
• COUNT(*) – Counts all the rows in a table containing values or NULL
values
• COUNT(column) – Counts all the rows in a specific column ignoring NULL
values
SELECT COUNT(*) AS SELECT COUNT(customer_id) AS
total_customers total_customers
FROM sql_store.order_items;
FROM sql_store.customers;
Asst. Prof. Dr. Nuchjarin Intalar
MAX and MIN Functions 76
• MAX() – Find the maximum value. Columns with NULL values are ignored
• MIN() – Find the minimum value. Columns with NULL values are ignored
SELECT MAX(unit_price) AS SELECT MIN(unit_price) AS
max_price min_price
FROM sql_store.order_items; FROM sql_store.order_items;
Asst. Prof. Dr. Nuchjarin Intalar
SUM Aggregate Function 77
• SUM() – Sum all values in column
SELECT SUM(unit_price) AS total_price
FROM sql_store.order_items;
SELECT SUM(quantity*unit_price) AS
totatl_prod_price
FROM sql_store.order_items;
Asst. Prof. Dr. Nuchjarin Intalar
REGEXP
Operations
Asst. Prof. Dr. Nuchjarin Intalar
REGEXP Operator (Regular Expression) 79
• Powerful when search for strings
• Allow us to search for more complex pattern
Asst. Prof. Dr. Nuchjarin Intalar
^ Operator (Caret) 80
• ‘^xxx’
• Search for the word begin with xxx
Asst. Prof. Dr. Nuchjarin Intalar
$ Operator 81
• ‘xxx$’
• Search for the word end with xxx
Asst. Prof. Dr. Nuchjarin Intalar
| Operator (Pipe) 82
• ‘xxx|yyy’
• Search for the word that have xxx or yyy in the sentence
Asst. Prof. Dr. Nuchjarin Intalar
REGEXP Operators 83
• Search for the customers whose last name begin with Field, Mac, or Rose.
Asst. Prof. Dr. Nuchjarin Intalar
[] Operators (Square bracket) 84
• Search for any character in [ ]
• Show customers whose last name have ‘g’, ‘i’, or ‘m’ before e
Asst. Prof. Dr. Nuchjarin Intalar
[] Operators (Square bracket) 85
• Search for any character in [ ]
• Show customers whose last name have any character ranging from ‘a’ to ‘h’ before
‘e’
Asst. Prof. Dr. Nuchjarin Intalar
Grouping Function
Asst. Prof. Dr. Nuchjarin Intalar
Grouping Data 87
• Learn how to group data in order to summarize subsets of
data
• New clauses GROUP BY; HAVING
• How to aggregate on a particular value
Asst. Prof. Dr. Nuchjarin Intalar
GROUP BY Clause 88
• Counts customer after group on state rather than counting the whole table
Asst. Prof. Dr. Nuchjarin Intalar
Grouping Data 89
• GROUP BY clauses can contain multiple columns.
• Every column in your SELECT statement must be present in a
GROUP BY clause, except for aggregated calculations.
• NULLs will be grouped together if you GROUP BY column contains
NULLs.
Asst. Prof. Dr. Nuchjarin Intalar
HAVING Clause – Filtering for Groups 90
• WHERE does not work for groups
• WHERE filters on rows
• Instead use HAVING clause to filter for groups
Asst. Prof. Dr. Nuchjarin Intalar
HAVING Clause 91
• Show the state that have a total_customers greater than or
equal to 2.
Asst. Prof. Dr. Nuchjarin Intalar
WHERE vs. HAVING 92
• WHERE filters before data is grouped.
• HAVING filters after data is grouped.
• Rows eliminated by the WHERE clause will not be included
in the group.
Asst. Prof. Dr. Nuchjarin Intalar
ORDER BY with GROUP BY 93
• ORDER BY sorts data.
• GROUP BY does not sort data.
Asst. Prof. Dr. Nuchjarin Intalar
Putting it all together 94
• Filtering is useful
• Narrowing down your results
• Increasing query & application performance
• Understanding your data
• Finding specific values
• Finding a range of values
• Finding blank values
Asst. Prof. Dr. Nuchjarin Intalar
Key SQL Clauses 95
Clause Description Required
SELECT Columns or expressions to be Yes
shown
FROM Table you retrieve data from Only if selecting data
from a table
WHERE Row-level filtering No
GROUP BY Group specification Only if calculating
aggregate by group
HAVING Group-level filter No
ORDER BY Output sort order No
Asst. Prof. Dr. Nuchjarin Intalar
