Transcript Single Table Queries

Single-Table
Queries 2:
Advanced Topics
CS 320
Review: Retrieving Data From
a Single Table

Syntax:
SELECT field1, field2, …
FROM tablename
WHERE search_condition(s)

Limitation: Retrieves "raw" data
We can use the DBMS to further
filter and manipulate data
Suppress duplicates
 Sort
 Format characters, numbers, & dates
 Perform arithmetic operations
 Summarize data


Why not just do it in your program?
Suppressing Duplicate Outputs

Use the DISTINCT qualifier
 Ensures
that only distinct rows are returned
SELECT DISTINCT fieldname
FROM tablename
WHERE search_condition(s);
SELECT DISTINCT cust_zip
FROM candy_customer;
Sorting Query Output

Use the ORDER BY clause:
SELECT field1, field2, …
FROM tablename
WHERE search_condition(s)
ORDER BY field_a;

Always appears as the last item in a
SELECT query
SELECT cust_id, cust_phone
FROM candy_customer
WHERE cust_type = 'P'
ORDER BY cust_name;
Sort Order

Default sort order is ascending
 Numbers:
smallest to largest
 Characters: alphabetical
 Dates: oldest to newest

To force a descending sort order, add the
DESC modifier:
SELECT purch_id, purch_date
FROM candy_purchase
ORDER BY purch_date DESC
Multiple Sort Fields

You can sort output by multiple fields
SELECT purch_id, purch_date
FROM candy_purchase
ORDER BY purch_date DESC, purch_id
 Only
makes sense when first sort field has
repeating values
Modifying Default Data Formats

Default formats:
 Floating
point numbers: shows values exactly
as entered

1.1, 2.25
 Dates
and times: default formats
DDDD-MM-DD
 HH:MI:SS

Formatting Retrieved Data

Floating point number fields: Use the
FORMAT function
FORMAT(fieldname, decimal_places)
SELECT purch_id, FORMAT(purch_pounds, 2)
FROM candy_purchase;
Formatting Number Output as
Currency

Use the CONCAT and FORMAT functions
together
 CONCAT
joins two strings to create a single string
CONCAT
('$', FORMAT(fieldname, decimal_places))
SELECT prod_id,
CONCAT('$', FORMAT(prod_cost, 2))
FROM candy_product;
Formatting Date Output

Use the DATE_FORMAT function

Returns a binary data type, so you have to use the CONVERT
function to convert it to a text string
CONVERT(DATE_FORMAT(fieldname, 'format')
USING latin1)
blank
spaces
SELECT purch_id,
CONVERT(DATE_FORMAT(purch_date, '%b %e, %Y')
USING latin1)
FROM candy_purchase;
%b – abbreviated month name
%e – day of the month, numeric,
suppresses leading zeroes
%Y – year, numeric, 4 digits
comma
Predefined format specifiers
Performing Arithmetic Calculations
in Queries

Applications often perform arithmetic
operations on retrieved data
 You
can perform basic arithmetic operations
on numbers and dates in a SQL query
SELECT clause

Rationale:
 DBMS
makes it easy to perform the operation
 Network needs to transmit only the data you
need
Performing Arithmetic Operations
on Number Data

Operators: +, -, *, /
 Order
of evaluation:
*, /
 +,  To force a different order, use parentheses

SELECT prod_desc, prod_price – prod_cost
FROM candy_product;
Performing Arithmetic Operations
on Date Data

To retrieve a date that is a specific number
of days from a retrieved date, add/subtract
the number of days:
SELECT purch_id, purch_date,
purch_date + 2, purch_date – 2
FROM candy_purchase;
Performing Arithmetic Operations
on Date Data

Another way to calculate the number of
days between two known dates is to use
DATEDIFF
SELECT purch_id, purch_date,
purch_delivery_date,
DATEDIFF(purch_delivery_date, purch_date)
FROM candy_purchase;
Retrieving the Current Date

Use the CURRENT_DATE() function
SELECT CURRENT_DATE();
Calculating someone’s age from
their date of birth:
SELECT (DATEDIFF(CURRENT_DATE(), '1986-11-20'))
/ 365.25;
Formatting Column Names

By default:
 Column
names are database field names
 Calculated column names are the formula
Column Aliases
 Provide an alternate column name
 Uses:


You can use them in the ORDER BY clause
You can reference them in server-side programs
SELECT fieldname AS alias …
SELECT purch_id, purch_date, purch_delivery_date,
DATEDIFF(purch_delivery_date, purch_date)
AS delivery_days
FROM candy_purchase
ORDER BY delivery_days;
SQL Group Functions

Performs an operation on values from a
group of retrieved records
 AVG
(average of all retrieved values)
 COUNT (number of records retrieved)
 MAX (maximum value retrieved)
 MIN (minimum value retrieved)
 SUM (sum of all retrieved values)
AVG, MAX, MIN, and SUM
Examples
SELECT MAX(prod_cost), MIN(prod_cost),
AVG(prod_cost), SUM(prod_cost)
FROM candy_product;
COUNT Group Function

Displays the number of records that a query will
retrieve


Can be used on a column of any data type
Forms:
– displays total number of records,
regardless if the record has fields that contain NULL
values
 COUNT(fieldname) – displays the number of
retrieved records in which the specified field is NOT
NULL
 COUNT(*)
COUNT Function Examples
SELECT COUNT(*)
FROM candy_customer;
SELECT COUNT(cust_phone)
FROM candy_customer;
Using the GROUP BY Clause

Whenever you use a group function:
 If
any of the columns in the SELECT clause
involve a group function, then columns not in
group functions must be listed in a GROUP
BY clause
SELECT purch_status, MAX(purch_pounds)
FROM candy_purchase
GROUP BY purch_status
Using the GROUP BY Clause

Another example:
SELECT purch_date, MAX(purch_pounds),
MIN(purch_pounds), AVG(purch_pounds)
FROM candy_purchase
GROUP BY purch_date;
Using the HAVING Clause
Sometimes you want to use the result of a
group function in a search condition
 To do this, use HAVING instead of
WHERE:

SELECT prod_id, AVG(purch_pounds)
FROM candy_purchase
GROUP BY prod_id
HAVING AVG(purch_pounds) > 5;
Using the HAVING Clause

NOTE: if you use a group function in the
HAVING clause and retrieve a non-groupfunction value in the SELECT clause, you
must group the output by the non-groupfunction field:
SELECT prod_id
FROM candy_purchase
GROUP BY prod_id
HAVING AVG(purch_pounds) > 5
Test Yourself: In which order does the following
query retrieve the CUST_ID values?
SELECT cust_id
FROM candy_customer
WHERE cust_id <= 5
ORDER BY cust_type DESC, cust_name
a.
b.
c.
d.
5, 2, 4, 3, 1
5, 2, 3, 4, 1
1, 2, 4, 3, 5
None of the above
Test Yourself: In which order does the following
query retrieve the CUST_ID values?
SELECT cust_id
FROM candy_customer
WHERE cust_id <= 5
ORDER BY cust_type DESC, cust_name
a.
b.
c.
d.
5, 2, 4, 3, 1
5, 2, 3, 4, 1
1, 2, 4, 3, 5
None of the above
Test Yourself: What does the
following query retrieve?
SELECT (purch_id + cust_id)/prod_id – purch_pounds
FROM candy_purchase
WHERE purch_id = 1
a.
b.
c.
d.
3.5
2.5
-2.4
None of the above
Test Yourself: What does the
following query retrieve?
SELECT (purch_id + cust_id)/prod_id – purch_pounds
FROM candy_purchase
WHERE purch_id = 1
a.
b.
c.
d.
3.5
2.5
-2.4
None of the above
Test Yourself: What does the
following query retrieve?
SELECT count(*)
FROM candy_purchase;
a.
b.
c.
d.
9
14
8
None of the above
Test Yourself: What does the
following query retrieve?
SELECT count(*)
FROM candy_purchase;
a.
b.
c.
d.
9
14
8
None of the above
Test Yourself: How many records will
the following query retrieve?
SELECT purch_delivery_date, SUM(purch_pounds)
FROM candy_purchase
GROUP BY purch_delivery_date;
4
b. 9
c. 14
d. None of the above
a.
Test Yourself: How many records will
the following query retrieve?
SELECT purch_delivery_date, SUM(purch_pounds)
FROM candy_purchase
GROUP BY purch_delivery_date;
4
b. 9
c. 14
d. None of the above
a.
Test Yourself: What does the
following query retrieve?
SELECT count(purch_delivery_date)
FROM candy_purchase;
a.
b.
c.
d.
1
10
9
None of the above
Test Yourself: What does the
following query retrieve?
SELECT count(purch_delivery_date)
FROM candy_purchase;
a.
b.
c.
d.
1
10
9
None of the above
Your Turn: Create a new MySQL script
file, then create the following queries:
Retrieve the purchase ID, purchase date, delivery date, and
pounds of all purchases that have been delivered (delivery date is not
NULL). Sort the output first by delivery date (ascending) and then by
pounds (descending).
2. Retrieve the CUST_ID of every customer who has made a
purchase. Do not display duplicate ID values.
3. Retrieve the purchase ID and the number of days between the
purchase date and current system date for every purchase.
4. Retrieve the purchase ID, purchase date, and delivery date for
every order. Display the dates in numeric ‘MM.DD.YYYY’ format (for
example 10.06.2007). Create descriptive column aliases of your choice
for the outputs.
5. Retrieve the purchase ID, status, and sum of the total pounds of
each purchase. Sort the output by the status values.
1.