Transcript Kroenke-DBP-e10-PPT-Chapter02-Part02

David M. Kroenke’s
Database Processing:
Fundamentals, Design, and Implementation
Chapter Two:
Introduction to
Structured Query Language
Part Two
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-1
Using MS Access
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-2
Using MS Access (Ctd)
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-3
Using MS Access (Ctd)
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-4
Using MS Access - Results
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-5
Using MS SQL Server
[SQL Query Analyzer]
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-6
Using Oracle
[SQL*Plus]
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-7
Using MySQL
[MySQL Command Line Client]
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-8
Using MySQL
[MySQL Query Browser]
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-9
Sorting the Results: ORDER BY
SELECT
FROM
ORDER BY
OrderNumber
*
ORDER_ITEM
OrderNumber;
SKU
Quantity
Price
ExtendedPrice
1000
201000
1
$300.00
$300.00
1000
202000
1
$130.00
$130.00
2000
101100
4
$50.00
$200.00
2000
101200
2
$50.00
$100.00
3000
100200
1
$300.00
$300.00
3000
101100
2
$50.00
$100.00
3000
101200
1
$50.00
$50.00
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-10
Sorting the Results: ORDER BY
SELECT
FROM
ORDER BY
*
ORDER_ITEM
OrderNumber, Price;
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-11
Sort Order:
Ascending and Descending
SELECT
*
FROM
ORDER_ITEM
ORDER BY Price DESC, OrderNumber ASC;
NOTE: The default sort order is ASC – does not have to be specified.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-12
WHERE Clause Options: AND
SELECT
FROM
WHERE
AND
*
SKU_DATA
Department = 'Water Sports'
Buyer = 'Nancy Meyers';
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-13
WHERE Clause Options: OR
SELECT
FROM
WHERE
OR
*
SKU_DATA
Department = 'Camping'
Department = 'Climbing';
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-14
WHERE Clause Options: IN
SELECT
FROM
WHERE
*
SKU_DATA
Buyer IN ('Nancy Meyers',
'Cindy Lo', 'Jerry Martin');
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-15
WHERE Clause Options: NOT IN
SELECT
FROM
WHERE
*
SKU_DATA
Buyer NOT IN ('Nancy Meyers',
'Cindy Lo', 'Jerry Martin');
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-16
WHERE Clause Options:
Ranges with BETWEEN
SELECT
FROM
WHERE
*
ORDER_ITEM
ExtendedPrice
BETWEEN 100 AND 200;
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-17
WHERE Clause Options:
Ranges with Math Symbols
SELECT
FROM
WHERE
AND
*
ORDER_ITEM
ExtendedPrice >= 100
ExtendedPrice <= 200;
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-18
WHERE Clause Options:
LIKE and Wildcards
• The SQL keyword LIKE can be combined
with wildcard symbols:
– SQL 92 Standard (SQL Server, Oracle, etc.):
• _ = Exactly one character
• % = Any set of one or more characters
– MS Access (based on MS DOS)
•?
•*
= Exactly one character
= Any set of one or more characters
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-19
WHERE Clause Options:
LIKE and Wildcards (Continued)
SELECT *
FROM
SKU_DATA
WHERE Buyer LIKE 'Pete%';
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-20
WHERE Clause Options:
LIKE and Wildcards (Continued)
SELECT
FROM
WHERE
*
SKU_DATA
SKU_Description LIKE '%Tent%';
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-21
WHERE Clause Options:
LIKE and Wildcards
SELECT *
FROM
SKU_DATA
WHERE SKU LIKE '%2__';
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-22
SQL Built-in Functions
• There are five SQL Built-in Functions:
– COUNT
– SUM
– AVG
– MIN
– MAX
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-23
SUM
SELECT SUM (ExtendedPrice)
AS Order3000Sum
FROM
ORDER_ITEM
WHERE OrderNumber = 3000;
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-24
Multiple Built-in Functions
SELECT
FROM
SUM (ExtendedPrice)
AVG (ExtendedPrice)
MIN (ExtendedPrice)
MAX (ExtendedPrice)
ORDER_ITEM;
AS
AS
AS
AS
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
OrderItemSum,
OrderItemAvg,
OrderItemMin,
OrderItemMax
2-25
COUNT
SELECT COUNT(*) AS NumRows
FROM
ORDER_ITEM;
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-26
COUNT DISTINCT
SELECT COUNT
(DISTINCT Department)
AS DeptCount
FROM
SKU_DATA;
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-27
Arithmetic
SELECT Quantity * Price AS EP,
ExtendedPrice
FROM
ORDER_ITEM;
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-28
String Functions
SELECT
FROM
DISTINCT RTRIM (Buyer)
+ ' in ' + RTRIM (Department)
AS Sponsor
SKU_DATA;
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-29
GROUP BY
SELECT
FROM
GROUP BY
Department, COUNT(*)AS
Dept_SKU_Count
SKU_DATA
Department;
Department
Dept_SKU_Count
Camping
2
Climbing
2
Water Sports
4
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-30
GROUP BY
SELECT
FROM
GROUP BY
Department, Buyer,
COUNT(*) AS
Dept_Buyer_SKU_Count
SKU_DATA
Department, Buyer;
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-31
GROUP BY and ORDER BY
SELECT
FROM
WHERE
GROUP BY
ORDER BY
Department, COUNT(*) AS
Dept_SKU_Count
SKU_DATA
SKU <> 302000
Department
Dept_SKU_Count;
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-32
HAVING
SELECT
FROM
WHERE
GROUP BY
HAVING
Department, COUNT(*) AS
Dept_SKU_Count
SKU_DATA
SKU <> 302000
Department
COUNT (*) > 1;
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-33
Querying Multiple Tables:
Subqueries
SELECT SUM (ExtendedPrice)
FROM
ORDER_ITEM
WHERE
SKU IN
(SELECT
FROM
WHERE
AS Revenue
SKU
SKU_DATA
Department = 'Water Sports');
Note: The second SELECT statement is a subquery.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-34
Nested Subqueries
SELECT Buyer
FROM
SKU_DATA
WHERE SKU IN
(SELECT
FROM
WHERE
SKU
ORDER_ITEM
OrderNumber IN
(SELECT
OrderNumber
FROM
RETAIL_ORDER
WHERE
OrderMonth = 'January'
AND
OrderYear = 2004));
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-35
Joins
SELECT
FROM
WHERE
Buyer, ExtendedPrice
SKU_DATA, ORDER_ITEM
SKU_DATA.SKU = ORDER_ITEM.SKU;
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-36
Joins
SELECT
FROM
WHERE
GROUP BY
ORDER BY
Buyer, SUM(ExtendedPrice)
AS BuyerRevenue
SKU_DATA, ORDER_ITEM
SKU_DATA.SKU = ORDER_ITEM.SKU
Buyer
BuyerRevenue DESC;
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-37
Two Joins
SELECT
FROM
WHERE
AND
Buyer, ExtendedPrice, OrderMonth
SKU_DATA, ORDER_ITEM, RETAIL_ORDER
SKU_DATA.SKU = ORDER_ITEM.SKU
ORDER_ITEM.OrderNumber =
RETAIL_ORDER.OrderNumber;
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-38
Subqueries versus Joins
• Subqueries and joins both process
multiple tables.
• A subquery can only be used to retrieve
data from the top table.
• A join can be used to obtain data from any
number of tables.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-39
David M. Kroenke’s
Database Processing
Fundamentals, Design, and Implementation
(10th Edition)
End of Presentation:
Chapter Two Part Two
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-40