Transcript Kroenke-DBP-e10-PPT-Chapter02

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 (Continued)
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-3
Using MS Access (Continued)
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 Oracle
[Quest Software’s TOAD]
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-8
Using MySQL
[MySQL Command Line Client]
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-9
Using MySQL
[MySQL Query Browser]
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
SQL Built-in Functions (Continued)
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
SQL Built-in Functions (Continued)
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
SQL Built-in Functions (Continued)
SELECT COUNT(*) AS NumRows
FROM
ORDER_ITEM;
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-26
SQL Built-in Functions (Continued)
SELECT COUNT
(DISTINCT Department)
AS DeptCount
FROM
SKU_DATA;
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-27
Arithmetic in SELECT Statements
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 in SELECT
Statements
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
The SQL keyword 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-30
The SQL keyword GROUP BY
(Continued)
• In general, place WHERE before GROUP BY.
Some DBMS products do not require that
placement, but to be safe, always put WHERE
before GROUP BY.
• The HAVING operator restricts the groups that
are presented in the result.
• There is an ambiguity in statements that include
both WHERE and HAVING clauses. The results
can vary, so to eliminate this ambiguity SQL
always applies WHERE before HAVING.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-31
The SQL keyword GROUP BY
(Continued)
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
The SQL keyword GROUP BY
(Continued)
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
Department, COUNT(*) AS
Dept_SKU_Count
SKU_DATA
SKU <> 302000
Department
COUNT (*) > 1
Dept_SKU_Count;
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
Querying Multiple Tables:
Subqueries (Continued)
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
Querying Multiple Tables:
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
Querying Multiple Tables:
Joins (Continued)
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
Querying Multiple Tables:
Joins (Continued)
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, including the “top table” of the
subquery.
• In Chapter 7, we will study the correlated
subquery. That kind of subquery can do work
that is not possible with joins.
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