Transcript Single Table Queries

Single-Table
Queries 1: Basics
CS 320 Online
Review: SQL Command Types
 Data Definition Language (DDL)
 Used to create and modify database objects
 Data Manipulation Language (DML)
 Used to insert, update, delete, and view the
data in database objects
Database Queries
 Query: question represented in a way
a database can understand
 SQL query components:
 SELECT: columns whose values you want
to retrieve
 FROM: tables that contain the columns in
the SELECT clause
 WHERE: optional search condition to
restrict which records are retrieved
Programming Note:
Before you can run SELECT queries, your
database must contains data
 Run the scripts to insert the records in the
CANDY database

CANDY database tables (with revised field names)
CANDY_CUSTOMER
CUST_ID CUST_NAME
CUST_TYPE CUST_ADDR
CUST_ZIP CUST_PHONE CUST_USERNAME CUST_PASSWORD
1 Jones, Joe
P
1234 Main St.
91212 434-1231
jonesj
2 Armstrong,Inc.
R
231 Globe Blvd.
91212 434-7664
armstrong
3 Sw edish Burgers
R
1889 20th N.E.
91213 434-9090
sw edburg
4 Pickled Pickles
R
194 CityView
91289 324-8909
pickpick
5 The Candy Kid
W
2121 Main St.
91212 563-4545
kidcandy
6 Waterman, Al
P
23 Yankee Blvd.
91234
w ateral
7 Bobby Bon Bons
R
12 Nichi Cres.
91212 434-9045
bobbybon
8 Crow sh, Elias
P
7 77th Ave.
91211 434-0007
crow el
9 Montag, Susie
P
981 Montview
91213 456-2091
montags
10 Columberg Sw eets W
239 East Falls
91209 874-9092
columsw e
1234
3333
2353
5333
2351
8900
3011
1033
9633
8399
CANDY_PURCHASE
CANDY_CUST_TYPE
CUST_TYPE_ID
CUST_TYPE_DESC
PURCH_ID
PROD_ID
CUST_ID
PURCH_
DATE
PURCH_
DELIVERY_ PURCH_
DATE
POUNDS
PURCH_
STATUS
P
Private
1
1
5 28-Oct-04
28-Oct-04
3.5 PAID
R
Retail
2
2
6 28-Oct-04
30-Oct-04
15 PAID
W
Wholesale
3
1
9 28-Oct-04
28-Oct-04
2 PAID
3
3
9 28-Oct-04
28-Oct-04
3.7 PAID
4
3
2 28-Oct-04
5
1
7 29-Oct-04
29-Oct-04
3.7 NOT PAID
5
2
7 29-Oct-04
29-Oct-04
1.2 NOT PAID
29-Oct-04
4.4 NOT PAID
CANDY_PRODUCT
PROD_ID
PROD_DESC
PROD_COSTPROD_PRICE
3.7 PAID
1 Celestial Cashew Crunch
$
7.45
$
10.00
5
3
7 29-Oct-04
2 Unbrittle Peanut Paradise
$
5.75
$
9.00
6
2
7 29-Oct-04
3 Mystery Melange
$
7.75
$
10.50
7
2
10 29-Oct-04
14 NOT PAID
4 Millionaire’s Macadamia Mix
$
12.50
$
16.00
7
5
10 29-Oct-04
4.8 NOT PAID
5 Nuts Not Nachos
$
6.25
$
9.50
8
1
4 29-Oct-04
8
5
4 29-Oct-04
9
5
4 29-Oct-04
3 PAID
29-Oct-04
1 PAID
7.6 PAID
29-Oct-04
3.5 NOT PAID
Retrieving Data From a Single
Table

Basic syntax:
SELECT field1, field2, …
FROM tablename
WHERE search_condition
SELECT cust_id, cust_name
FROM candy_customer
WHERE cust_id = 1
Retrieving All Fields or All Records
 To retrieve all fields in the table: use the
"*" wildcard character
SELECT *
FROM tablename
WHERE search_condition
 To retrieve all records in a table: omit the
search condition
Examples of Queries that Retrieve all
Fields and all Records
SELECT *
FROM candy_purchase;
SELECT *
FROM candy_cust_type;
Search Conditions

General format:
FieldName Operator TargetValue
 Operators:

=, <, >, <=, >=, <> or !=,
BETWEEN, IN, LIKE
Examples:
prod_id = 1
purch_pounds > 5
prod_cost >= 9.99
purch_status != 'PAID'
purch_pounds BETWEEN 5 AND 10
prod_id IN (1, 3, 5)
SearchTarget Values

Numbers
 Just

type the number
Text strings
 Enclose
in single or
double quotes
 Search for strings with
embedded single quotes
by typing \' or \"

prod_id = 1
prod_cost >= 9.99
Dates
 Enter
as a text string in
'yyyy-mm-dd' format
cust_name = 'Jones, Joe'
prod_desc =
'Millionaire\'s Macadamia
Mix'
purch_date = '2004-10-28'
Using IN and BETWEEN
IN: retrieves all matching values within a
set of values
 BETWEEN: retrieves all matching values
within a range of values (inclusive)

WHERE cust_zip IN ('91211', '91212')
WHERE prod_id BETWEEN 1 AND 3
Partial-Text Searches Using
LIKE

Partial-text search: searches for part of a string
within a character field
 Use
the % wildcard character to match 0 or more
characters
 Examples:
WHERE cust_zip LIKE '9121%'
WHERE cust_name LIKE '%s'
WHERE cust_name LIKE '%s%'
Searching for NULL Values
NULL: undefined
 Search conditions for NULL and nonNULL values:

WHERE fieldname IS NULL
WHERE fieldname IS NOT NULL
WHERE cust_phone IS NULL
WHERE purch_delivery_date IS NOT NULL
Compound Search Conditions
 Formed by connecting two or more search
conditions using the AND or OR operator
 AND: query only retrieves records for
which both conditions are true
 OR: query retrieves records for which
either condition is true
Example Compound Search
Conditions
WHERE Condition1 AND Condition2
WHERE Condition1 OR Condition2
WHERE prod_id = 1
AND purch_date = '2008-10-28'
WHERE prod_id = 1
OR prod_id = 3
Using AND and OR in Search
Conditions

Every expression must be well-formed:
 Do
this:
WHERE purch_date > '2004-10-28'
AND purch_date < '2004-11-1'
 Not
this:
WHERE purch_date > '2004-10-28'
AND < '2004-11-1'
Order of AND/OR Evaluation


MySQL evaluates AND expressions first,
then OR expressions
To force a specific evaluation order, place
conditions to be evaluated first in
parentheses!
SELECT cust_id
FROM candy_customer
WHERE cust_type = 'W'
AND (cust_zip = '91209'
OR cust_zip = '91212')
Test Yourself: How many fields and
how many records will the following
query retrieve?
SELECT *
FROM candy_purchase;
a.
b.
c.
d.
7 fields and 14 records
14 fields and 7 records
7 fields and 9 records
None of the above
Test Yourself: How many fields and
how many records will the following
query retrieve?
SELECT *
FROM candy_purchase;
a.
b.
c.
d.
7 fields and 14 records
14 fields and 7 records
7 fields and 9 records
None of the above
Test Yourself: How many
CANDY_CUSTOMER records will the
following query retrieve?
SELECT cust_id
FROM candy_customer
WHERE cust_name LIKE '%s'
a.
b.
c.
d.
e.
4
8
9
10
None of the above
Test Yourself: How many
CANDY_CUSTOMER records will the
following query retrieve?
SELECT cust_id
FROM candy_customer
WHERE cust_name LIKE '%s'
a.
b.
c.
d.
e.
4
8
9
10
None of the above
Test Yourself: How many records
will the following query retrieve?
SELECT *
FROM candy_purchase
WHERE purch_status LIKE '%Paid%'
a.
b.
c.
d.
0
8
14
None of the above
Test Yourself: How many records
will the following query retrieve?
SELECT *
FROM candy_purchase
WHERE purch_status LIKE '%Paid%'
a.
b.
c.
d.
0
8
14
None of the above
Test Yourself: How many records will
the following query retrieve?
SELECT *
FROM candy_purchase
WHERE purch_delivery_date = NULL
AND purch_status = 'PAID'
a.
b.
c.
d.
e.
0
3
5
8
None of the above
Test Yourself: How many records will
the following query retrieve?
SELECT *
FROM candy_purchase
WHERE purch_delivery_date = NULL
AND purch_status = 'PAID'
a.
b.
c.
d.
e.
0
3
5
8
None of the above