ITC c201 - Database Fundamentals

Data Query Language

Prepared by:
Mengvi P. Gatpandan, DIT
 Learning Path
• Database Programming SQL
Topic

Mastery • List (describe) and use (apply)

Level

•Create a basic SELECT statement

•Use the correct syntax to display all rows in a table
•Use of Boolean operators
•Use range qualification
•Use of distinct values
Learning •Use of List
Objective •Use of Like
•Use of Arithmetic Operators
•Use of Group by and Having clause

• List and use basic SQL statement to create, manipulate, and query database; and
Learning
construct one working system using relational database and a front-end language based
Outcome on the requirements provided by the faculty
Today’s Menu
Everyday life is like
programming, I guess.
Tweet your self reaction in the topic If you love something,
140 – 280 characters tweet you can put beauty into
Using this format: [Your own word of it.
wisdom]
#dqlSELECT
[Question] [Answer] Donald Knuth
 Learning Objectives
• We are learning to.....
– Create a basic SELECT statement
– Use the correct syntax to display all rows in a table
– Use of Boolean operators
– Use range qualification
– Use of distinct values
– Use of List
– Use of Like
– Use of Arithmetic Operators
– Use of Group by and Having clause
 SELECT STATEMENT
• The SELECT statement allows you to search for specific data.
• The SELECT statement retrieves information from the
database.
• The syntax for a SELECT statement is as follows:

• In its simplest form, a SELECT statement must include the

following:
• A SELECT clause, which specifies the columns to be displayed
• A FROM clause, which specifies the table containing the
columns listed in the SELECT clause
 SELECT Statement
• Used for queries on single or multiple tables
• Clauses of the SELECT statement:
– SELECT
• List the columns (and expressions) that should be returned from the
query
– FROM
• Indicate the table(s) or view(s) from which data will be obtained
– WHERE
• Indicate the conditions under which a row will be included in the
result
– GROUP BY
• Indicate categorization of results
– HAVING
• Indicate the conditions under which a category (group) will be
included
– ORDER BY
• Sorts the result according to specified criteria

6
 SELECT Example
• Find products with standard price less than $275

SELECT PRODUCT_NAME, STANDARD_PRICE

FROM PRODUCT_T
WHERE STANDARD_PRICE < 275;

Table 1 - Operators in SQL

7
 SELECT Example
SELECT PRODUCT_DESCRIPTION, STANDARD_PRICE
FROM PRODUCT_T
WHERE STANDARD_PRICE < 275;

Output:
PRODUCT_DESCRIPTION STANDARD_PRICE
End Table 175
Computer Desk 250
Coffee Table 200

8
SELECT Example–Boolean Operators
• AND, OR, and NOT Operators for customizing
conditions in WHERE clause

SELECT PRODUCT_DESCRIPTION, PRODUCT_FINISH,

STANDARD_PRICE
FROM PRODUCT_T
WHERE (PRODUCT_DESCRIPTION LIKE ‘%Desk’
OR PRODUCT_DESCRIPTION LIKE ‘%Table’)
AND UNIT_PRICE > 300;
Note: the LIKE operator allows you to compare strings using wildcards.
For example, the % wildcard in ‘%Desk’ indicates that all strings that
have any number of characters preceding the word “Desk” will be allowed
9
 Venn Diagram from Previous Query

10
 SELECT PRODUCT_DESCRIPTION, PRODUCT_FINISH,
STANDARD_PRICE
FROM PRODUCT_T
WHERE (PRODUCT_DESCRIPTION LIKE ‘%Desk’
OR PRODUCT_DESCRIPTION LIKE ‘%Table’)
AND UNIT_PRICE > 300;

Output:

PRODUCT_DESCRIPTION PRODUCT_FINISH STANDARD_PRICE

Computer Desk Natural Ash 375

Writer’s Desk Cherry 325
8-Drawer Desk White Ash 750
Dining Table Natural Ash 800

11
 Using Range for Qualification
• The comparison operators < and > are used to
establish a range of values. The keywords
BETWEEN or NOT BETWEEN can also be used.

Which products in the product table have a

standard price between $200 and 300?

SELECT PRODUCT_DESCRIPTION, STANDARD_PRICE

FROM PRODUCT_T
WHERE STANDARD_PRICE > 199 AND
STANDARD_PRICE < 301;

12
 Using Range for Qualification
Result:

PRODUCT_DESCRIPTION STANDARD_PRICE
Coffee Table 200
Computer Desk 250

The same result will be returned by this query

SELECT PRODUCT_DESCRIPTION, STANDARD_PRICE

FROM PRODUCT_T
WHERE STANDARD_PRICE BETWEEN 200 AND 300;

13
 Using DISTINCT Values
• Sometimes when returning rows,
duplicate rows will be returned.
Example:
SELECT ORDER_ID
FROM ORDER_LINE_T;
Output:
Example: ORDER_ID
1001
1001
1001
SELECT ORDER_ID 1002
1003
1004
FROM ORDER_LINE_T; 1004
1005
1006
1006
1006
1007
1007
1008
1008
1009
1009
1010

14
Example: Output:
SELECT DISTINCT(ORDER_ID)
ORDER_ID
FROM ORDER_LINE_T;
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010

15
 Using IN and NOT IN with Lists
• To match a list of values, consider using IN

List all customers who live in warmer states. Warmer states

are Florida (FL), Texas (TX), California (CA), and Hawaii
(HI).

SELECT CUSTOMER_NAME, CITY, STATE

FROM CUSTOMER_T
WHERE STATE IN (‘FL’, ‘TX’, ‘CA’, ‘HI’)

Note: the IN operator in this example allows you to include rows whose
STATE value is either FL, TX, CA, or HI. It is more efficient than separate
OR conditions

16
• SELECT CUSTOMER_NAME, CITY, STATE
FROM CUSTOMER_T
WHERE STATE IN (‘FL’, ‘TX’, ‘CA’, ‘HI’)

Output:

CUSTOMER_NAME CITY STATE

Contemporary Casuals Gainesville FL
Value Furniture Plano TX
Impressions Sacramento CA
California Classics Santa Clara CA
M and H Casual Furniture Clearwater FL
Seminole Interiors Seminole FL
Kaneohe Homes KaneoheHI

17
 LIKE
• The percent (%) symbol is used to represent any
sequence of zero or more characters.
• The underscore (_) symbol is used to represent a
single character.
• In the example shown below, all employees with
last names beginning with any letter followed by
an "o" and then followed by any other number of
letters will be returned.
LIKE
 LIKE
• One additional option that is important: When you need to
have an exact match for a string that has a % or _ character in
it, you will need to indicate that the % or the _ is not a
wildcard but is part of the item you're searching for.

• The ESCAPE option can be used to indicate that the _ or % is

part of the name, not a wildcard value.
• For example, if we wanted to retrieve an employee JOB_ID
from the employees table containing the pattern _R, we
would need to use an escape character to say we are
searching for an underscore, and not just any one character.
• This example uses the backslash '\' as the escape character,
but any character can be used.
LIKE
 Using Arithmetic Operators
• The example shown uses the
addition operator to calculate
a salary increase of 300 for all
employees and displays a new
SALARY + 300 column in the
output.

• Putting in blank spaces before

and after an arithmetic
operator will not affect the
output.
Precedence in Arithmetic
Operators
Sorting Results: the ORDER BY Clause
• ORDER BY – Sorts the final results in
ascending order or descending order
• GROUP BY – Group rows in an
intermediate results table where the
values in those rows are the same for
one or more columns
• HAVING – Can only be used following a
GROUP BY and act as a secondary
WHERE clause

24
 SELECT Example –
Sorting Results with the ORDER BY Clause
• List customer, city and state for all customers
whose address is Florida, Texas, California or
Hawaii. List the customers alphabetically by
state, and alphabetically by customer within
each state.

SELECT CUSTOMER_NAME, CITY, STATE

FROM CUSTOMER_T
WHERE STATE IN (‘FL’, ‘TX’, ‘CA’, ‘HI’)
ORDER BY STATE, CUSTOMER_NAME;

25
SELECT CUSTOMER_NAME, CITY, STATE
FROM CUSTOMER_T
WHERE STATE IN (‘FL’, ‘TX’, ‘CA’, ‘HI’)
ORDER BY STATE, CUSTOMER_NAME

Output:

CUSTOMER_NAME CITY STATE

California Classics Santa Clara CA
Impressions Sacramento CA
Contemporary Casuals Gainesville FL
M and H Casual Furniture Clearwater FL
Seminole Interiors Seminole FL
Kaneohe Homes Kaneohe HI
Value Furniture Plano TX

26
 SELECT Example–
Categorizing Results Using the GROUP BY Clause
• For use with aggregate functions
– Scalar aggregate: single value returned from SQL query with aggregate
function
– Vector aggregate: multiple values returned from SQL query with aggregate
function (via GROUP BY)

Count the number of customers with addresses in each state .

SELECT STATE, COUNT(STATE)

FROM CUSTOMER_T
GROUP BY STATE;

Note: you can use single-value fields with aggregate functions if they are
included in the GROUP BY clause

27
SELECT STATE, COUNT(STATE)
FROM CUSTOMER_T
GROUP BY STATE;

STATE COUNT(STATE)
CA 2
CO 1
FL 3
HI 1
MI 1
NJ 2
NY 1
PA 1
TX 1
UT 1
WA 1

28
 Qualifying Results by Categories
Using the HAVING Clause

• For use with GROUP BY

Find only the states with more than one customer

SELECT STATE, COUNT(STATE)

FROM CUSTOMER_T
GROUP BY STATE
HAVING COUNT(STATE) > 1;

Like a WHERE clause, but it operates on groups (categories), not on individual

rows. Here, only those groups with total numbers greater than 1 will be
included in final result

29
SELECT STATE, COUNT(STATE)
FROM CUSTOMER_T
GROUP BY STATE
HAVING COUNT(STATE) > 1;

STATE COUNT(STATE)
CA 2
FL 3
NJ 2

30
• Answer the excel worksheet and save it as
LastNameFirstname_code&section_Assessment9
and submit to canvas
• Capture your tweet and save it as
LastNameFirstname_code&section_Assessment10
and submit to canvas

Tweet your self reaction in the topic

140 – 280 characters tweet
Using this format: [Your own word of wisdom]
#dqlSELECT
[Question] [Answer]
• Laboratory Activity 9 to Activity 11
• Use the naming conventions
• LastNameFirstname_code&section_Act9,
……
LastNameFirstname_code&section_Act11
• And submit it to canvas
 Thank you

Have a nice day everyone. Stay safe and

Best of health to all of us.