Transcript kroenke_dbp11e_ch02

David M. Kroenke and David J. Auer
Database Processing:
Fundamentals, Design and Implementation
Chapter Two:
Introduction to
Structured Query
Language
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-1
Chapter Objectives
• To understand the use of extracted data sets.
• To understand the use of ad-hoc queries.
• To understand the history and significance of Structured
Query Language (SQL).
• To understand the SQL SELECT/FROM/WHERE
framework as the basis for database queries.
• To be able to write queries in SQL to retrieve data from
• a single table.
• To be able to write queries in SQL to use the SQL
SELECT, FROM, WHERE, ORDER BY, GROUP BY,
and HAVING clauses.
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-2
Chapter Objectives
• To be able to write queries in SQL to use SQL
DISTINCT, AND, OR, NOT, BETWEEN, LIKE, and IN
keywords.
• To be able to use the SQL built-in functions of SUM,
COUNT, MIN, MAX, and AVG with and without the use
of a GROUP BY clause.
• To be able to write queries in SQL to retrieve data from a
single table but restricting the data based upon data in
another table (subquery).
• To be able to write queries in SQL to retrieve data from
multiple tables using an SQL join.
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-3
Structured Query Language
• Structured Query Language (SQL) was
developed by the IBM Corporation in the late
1970’s.
• SQL was endorsed as a United States national
standard by the American National Standards
Institute (ANSI) in 1992 [SQL-92].
• Newer versions exist, and incorporate XML and
some object-oriented concepts.
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-4
SQL as a Data Sublanguage
• SQL is not a full featured programming
language.
– C, C#, Java
• SQL is a data sublanguage for creating
and processing database data and
metadata.
• SQL is ubiquitous in enterprise-class
DBMS products.
• SQL programming is a critical skill.
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-5
SQL DDL and DML
• SQL statements can be divided into two
categories:
– Data definition language (DDL) statements
• Used for creating tables, relationships and other
structures.
• Covered in Chapter Seven.
– Data manipulation language (DML)
statements.
• Used for queries and data modification.
• Covered in this chapter (Chapter Two).
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-6
Cape Codd Outdoor Sports
• Cape Codd Outdoor Sports is a fictitious
company based on an actual outdoor retail
equipment vendor.
• Cape Codd Outdoor Sports:
– Has 15 retail stores in the US and Canada.
– Has a on-line Internet store.
– Has a (postal) mail order department.
• All retail sales recorded in an Oracle
database.
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-7
Cape Codd Retail Sales Structure
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-8
Cape Codd Retail Sales Data
Extraction
• The Cape Codd marketing department needs an
analysis of in-store sales.
• The entire database is not needed for this, only an
extraction of retail sales data.
• The data is extracted by the IS department from the
operational database into a separate, off-line
database for use by the marketing department.
• Three tables are used: RETAIL_ORDER,
ORDER_ITEM, and SKU_DATA (SKU = Stock Keeping
Unit).
• The extracted data is converted as necessary:
– Into a different DBMS – MS SQL Server
– Into different columns – OrderDate becomes OrderMonth and
OrderYear.
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-9
Extracted
Retail
Sales Data
Format
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-10
Retail Sales Extract Tables
[in MS SQL Server]
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-11
The SQL SELECT Statement
• The fundamental framework for SQL query
states is the SQL SELECT statement.
– SELECT
– FROM
– WHERE
{ColumnName(s)}
{TableName(s)}
{Conditions}
• All SQL statements end with a semi-colon
(;).
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-12
Specific Columns on One Table
SELECT Department, Buyer
FROM
SKU_DATA;
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-13
Specifying Column Order
SELECT Buyer, Department
FROM
SKU_DATA;
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-14
The DISTINCT Keyword
SELECT
DISTINCT Buyer, Department
FROM SKU_DATA;
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-15
Selecting All Columns:
The Asterisk (*) Keyword
SELECT *
FROM
SKU_DATA;
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-16
Specific Rows from One Table
SELECT
FROM
WHERE
*
SKU_DATA
Department = 'Water Sports';
NOTE: SQL wants a plain ASCII single quote: ' NOT ‘ !
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-17
Specific Columns and Rows from
One Table
SELECT
FROM
WHERE
SKU_Description, Buyer
SKU_DATA
Department = 'Climbing';
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-18
Using MS Access
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-19
Using MS Access (Continued)
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-20
Using MS Access (Continued)
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-21
Using MS Access (Continued)
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-22
Using MS Access (Continued)
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-23
Using MS Access - Results
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-24
Using MS Access
Saving the Query
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-25
Using MS SQL Server 2008
The Microsoft SQL Server Management Studio I
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-26
Using MS SQL Server 2008
The Microsoft SQL Server Management Studio II
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-27
Using Oracle Database 11g
SQL Developer I
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-28
Using Oracle Database 11g
SQL Developer II
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-29
Using MySQL 5.1
MySQL Query Browser I
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-30
Using MySQL 5.1
MySQL Query Browser II
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-31
Sorting the Results – ORDER BY
SELECT *
FROM
ORDER BY
ORDER_ITEM
OrderNumber, Price;
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-32
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.
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-33
WHERE Clause Options - AND
SELECT
FROM
WHERE
AND
*
SKU_DATA
Department = 'Water Sports'
Buyer = 'Nancy Meyers';
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-34
WHERE Clause Options - OR
SELECT
FROM
WHERE
OR
*
SKU_DATA
Department = 'Camping'
Department = 'Climbing';
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-35
WHERE Clause Options - IN
SELECT
FROM
WHERE
*
SKU_DATA
Buyer IN ('Nancy Meyers',
'Cindy Lo', 'Jerry Martin');
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-36
WHERE Clause Options – NOT IN
SELECT
FROM
WHERE
*
SKU_DATA
Buyer NOT IN ('Nancy Meyers',
'Cindy Lo', 'Jerry Martin');
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-37
WHERE Clause Options –
Ranges with BETWEEN
SELECT
FROM
WHERE
*
ORDER_ITEM
ExtendedPrice
BETWEEN 100 AND 200;
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-38
WHERE Clause Options –
Ranges with Math Symbols
SELECT
FROM
WHERE
AND
*
ORDER_ITEM
ExtendedPrice >= 100
ExtendedPrice <= 200;
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-39
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 zero or more characters
– MS Access (based on MS DOS)
•?
•*
= Exactly one character
= Any set of zero or more characters
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-40
WHERE Clause Options –
LIKE and Wildcards
SELECT *
FROM
SKU_DATA
WHERE Buyer LIKE 'Pete%';
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-41
WHERE Clause Options –
LIKE and Wildcards
SELECT
FROM
WHERE
*
SKU_DATA
SKU_Descripton LIKE '%Tent%';
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-42
WHERE Clause Options –
LIKE and Wildcards
SELECT *
FROM
SKU_DATA
WHERE SKU LIKE '%2__';
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-43
SQL Built-in Functions
• There are five SQL Built-in Functions:
– COUNT
– SUM
– AVG
– MIN
– MAX
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-44
SQL Built-in Functions
SELECT SUM (ExtendedPrice)
AS Order3000Sum
FROM
ORDER_ITEM
WHERE OrderNumber = 3000;
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-45
SQL Built-in Functions
SELECT
FROM
SUM (ExtendedPrice)
AVG (ExtendedPrice)
MIN (ExtendedPrice)
MAX (ExtendedPrice)
ORDER_ITEM;
AS
AS
AS
AS
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
OrderItemSum,
OrderItemAvg,
OrderItemMin,
OrderItemMax
2-46
SQL Built-in Functions
SELECT COUNT(*) AS NumberOfRows
FROM
ORDER_ITEM;
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-47
SQL Built-in Functions
SELECT COUNT
(DISTINCT Department)
AS DeptCount
FROM
SKU_DATA;
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-48
Arithmetic in SELECT Statements
SELECT Quantity * Price AS EP,
ExtendedPrice
FROM
ORDER_ITEM;
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-49
String Functions in SELECT
Statements
SELECT
FROM
DISTINCT RTRIM (Buyer)
+ ' in ' + RTRIM (Department)
AS Sponsor
SKU_DATA;
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-50
The SQL keyword GROUP BY
SELECT Department, Buyer,
COUNT(*) AS
Dept_Buyer_SKU_Count
FROM
SKU_DATA
GROUP BY
Department, Buyer;
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-51
The SQL keyword GROUP BY
• 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.
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-52
The SQL keyword GROUP BY
SELECT
Department, COUNT(*) AS
Dept_SKU_Count
FROM
SKU_DATA
WHERE
SKU <> 302000
GROUP BY
Department
ORDER BY
Dept_SKU_Count;
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-53
The SQL keyword GROUP BY
SELECT
Department, COUNT(*) AS
Dept_SKU_Count
FROM
SKU_DATA
WHERE
SKU <> 302000
GROUP BY
Department
HAVING
COUNT (*) > 1
ORDER BY
Dept_SKU_Count;
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-54
Querying Multiple Tables:
Subqueries
SELECT
FROM
WHERE
SUM (ExtendedPrice) AS Revenue
ORDER_ITEM
SKU IN
(SELECT
SKU
FROM
SKU_DATA
WHERE Department = 'Water Sports');
Note: The second SELECT statement is a subquery.
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-55
Querying Multiple Tables:
Subqueries
SELECT
FROM
WHERE
Buyer
SKU_DATA
SKU IN
(SELECT
FROM
WHERE
SKU
ORDER_ITEM
OrderNumber IN
(SELECT
OrderNumber
FROM
RETAIL_ORDER
WHERE OrderMonth = 'January'
AND OrderYear = 2004));
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-56
Querying Multiple Tables:
Joins
SELECT
FROM
WHERE
Buyer, ExtendedPrice
SKU_DATA, ORDER_ITEM
SKU_DATA.SKU = ORDER_ITEM.SKU;
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-57
Querying Multiple Tables:
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;
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-58
Querying Multiple Tables:
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;
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-59
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.
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-60
David Kroenke and David Auer
Database Processing
Fundamentals, Design, and Implementation
(11th Edition)
End of Presentation:
Chapter Two
KROENKE AND AUER - DATABASE PROCESSING, 11th Edition
© 2010 Pearson Prentice Hall
2-61