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

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

Database Queries

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)

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 12342 Armstrong,Inc. R 231 Globe Blvd. 91212 434-7664 armstrong 33333 Sw edish Burgers R 1889 20th N.E. 91213 434-9090 sw edburg 23534 Pickled Pickles R 194 CityView 91289 324-8909 pickpick 53335 The Candy Kid W 2121 Main St. 91212 563-4545 kidcandy 23516 Waterman, Al P 23 Yankee Blvd. 91234 w ateral 89007 Bobby Bon Bons R 12 Nichi Cres. 91212 434-9045 bobbybon 30118 Crow sh, Elias P 7 77th Ave. 91211 434-0007 crow el 10339 Montag, Susie P 981 Montview 91213 456-2091 montags 9633

10 Columberg Sw eets W 239 East Falls 91209 874-9092 columsw e 8399

PURCH_ID PROD_ID CUST_IDPURCH_ DATE

PURCH_ DELIVERY_DATE

PURCH_ POUNDS

PURCH_ STATUS

1 1 5 28-Oct-04 28-Oct-04 3.5 PAID2 2 6 28-Oct-04 30-Oct-04 15 PAID3 1 9 28-Oct-04 28-Oct-04 2 PAID3 3 9 28-Oct-04 28-Oct-04 3.7 PAID4 3 2 28-Oct-04 3.7 PAID5 1 7 29-Oct-04 29-Oct-04 3.7 NOT PAID5 2 7 29-Oct-04 29-Oct-04 1.2 NOT PAID5 3 7 29-Oct-04 29-Oct-04 4.4 NOT PAID6 2 7 29-Oct-04 3 PAID7 2 10 29-Oct-04 14 NOT PAID7 5 10 29-Oct-04 4.8 NOT PAID8 1 4 29-Oct-04 29-Oct-04 1 PAID8 5 4 29-Oct-04 7.6 PAID9 5 4 29-Oct-04 29-Oct-04 3.5 NOT PAID

PROD_ID PROD_DESC PROD_COSTPROD_PRICE

1 Celestial Cashew Crunch 7.45$ 10.00$

2 Unbrittle Peanut Paradise 5.75$ 9.00$

3 Mystery Melange 7.75$ 10.50$

4 Millionaire’s Macadamia Mix 12.50$ 16.00$

5 Nuts Not Nachos 6.25$ 9.50$

CUST_TYPE_IDCUST_TYPE_DESC

P Private

R Retail

W Wholesale

CANDY_CUSTOMER

CANDY_PURCHASECANDY_CUST_TYPE

CANDY_PRODUCT

Retrieving Data From a Single Table Basic syntax:

SELECT field1, field2, …FROM tablenameWHERE search_condition

SELECT cust_id, cust_nameFROM candy_customerWHERE cust_id = 1

Retrieving All Fields or All Records

To retrieve all fields in the table: use the "*" wildcard character

To retrieve all records in a table: omit the search condition

SELECT *FROM tablenameWHERE 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:

Operators: =, <, >, <=, >=, <> or !=, BETWEEN, IN, LIKE

Examples:

FieldName Operator TargetValue

prod_id = 1purch_pounds > 5prod_cost >= 9.99purch_status != 'PAID'purch_pounds BETWEEN 5 AND 10prod_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 \"

Dates Enter as a text string in

'yyyy-mm-dd' format

prod_id = 1prod_cost >= 9.99

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 search: searches for part of a string within a character field Use the % wildcard character to match 0 or more

characters

Examples:

Partial-Text Searches Using LIKE

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

NULL values:

WHERE fieldname IS NULLWHERE fieldname IS NOT NULL

WHERE cust_phone IS NULLWHERE purch_delivery_date IS NOT NULL

Compound Search Conditions

Formed by connecting two or more search conditions using the AND or OR operatorAND: query only retrieves records for

which both conditions are trueOR: 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:

Not this:

WHERE purch_date > '2004-10-28'AND purch_date < '2004-11-1'

WHERE purch_date > '2004-10-28'AND < '2004-11-1'

MySQL evaluates AND expressions first, then OR expressions

To force a specific evaluation order, place conditions to be evaluated first in parentheses!

Order of AND/OR Evaluation

SELECT cust_idFROM candy_customerWHERE 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?

a. 7 fields and 14 records

b. 14 fields and 7 records

c. 7 fields and 9 records

d. None of the above

SELECT * FROM candy_purchase;

Test Yourself: How many fields and how many records will the following query retrieve?

a. 7 fields and 14 records

b. 14 fields and 7 records

c. 7 fields and 9 records

d. None of the above

SELECT * FROM candy_purchase;

a. 4b. 8c. 9d. 10e. None of the above

Test Yourself: How many CANDY_CUSTOMER records will the following query retrieve?

SELECT cust_idFROM candy_customerWHERE cust_name LIKE '%s'

a. 4b. 8c. 9d. 10e. None of the above

Test Yourself: How many CANDY_CUSTOMER records will the following query retrieve?

SELECT cust_idFROM candy_customerWHERE cust_name LIKE '%s'

Test Yourself: How many records will the following query retrieve?

a. 0

b. 8

c. 14

d. None of the above

SELECT *FROM candy_purchaseWHERE purch_status LIKE '%Paid%'

Test Yourself: How many records will the following query retrieve?

a. 0

b. 8

c. 14

d. None of the above

SELECT *FROM candy_purchaseWHERE purch_status LIKE '%Paid%'

Test Yourself: How many records will the following query retrieve?

a. 0

b. 3

c. 5

d. 8

e. None of the above

SELECT *FROM candy_purchaseWHERE purch_delivery_date = NULLAND purch_status = 'PAID'

Test Yourself: How many records will the following query retrieve?

a. 0

b. 3

c. 5

d. 8

e. None of the above

SELECT *FROM candy_purchaseWHERE purch_delivery_date = NULLAND purch_status = 'PAID'