Transcript Kronek-DBP-e10-PPT-Chapter02

David M. Kroenke’s
Database Processing:
Fundamentals, Design, and
Implementation
Chapter Two:
Introduction to Structured Query Language
Part One
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-1
Structured Query Language
• Structured Query Language (SQL)
– developed by IBM in the late 70s
• It was endorsed as a national standard in 1992
– American National Standards Institute (ANSI) [SQL-92].
• A newer version [SQL3] exists
– It incorporates object-oriented concepts,
– It is not (yet) widely used in commercial DBMS
products.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-2
SQL as a Data Sublanguage
• SQL is not a full featured programming
language like C++ or Java.
• It is a data sublanguage
– Used to create and process database data
and metadata.
• SQL is widely used by enterprise-class
DBMS products.
• SQL programming is a critical skill.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-3
SQL DDL and DML
• SQL statements fall into two categories:
– Data definition language (DDL)
• Used for creating tables, relationships, and other
structures.
• Covered in Chapter Seven.
– Data manipulation language (DML)
• Used for queries and data modification
• Covered in this chapter (Chapter Two)
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-4
Cape Codd Outdoor Sports
• Cape Codd Outdoor Sports
– A fictitious company based on an actual
outdoor retail equipment vendor.
• Cape Codd Outdoor Sports:
– Has 15 retail stores in the United States and
Canada.
– Has an on-line Internet store.
– Has a (postal) mail order department.
• All retail sales recorded in a database.
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-5
Cape Codd Retail Sales Structure
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-6
Cape Codd Retail Sales Data
Extraction
• The Cape Codd marketing department must analyze instore sales.
– Only the retail sales data is required for this.
• The data is extracted 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
• The extracted data is converted as necessary:
– Into a different DBMS  MS SQL Server
– Into different columns  OrderDate becomes OrderMonth and
OrderYear
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-7
Extracted
Retail
Sales Data
Format
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-8
Retail Sales Extract Tables
[in MS SQL Server]
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-9
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
(;).
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-10
Specific Columns on One Table
SELECT Department, Buyer
FROM
SKU_DATA;
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-11
Specifying Column Order
SELECT Buyer, Department
FROM
SKU_DATA;
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-12
The DISTINCT Keyword
SELECT
FROM
DISTINCT Buyer, Department
SKU_DATA;
DISTINCT does not work in Access….
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-13
Selecting All Columns:
The Asterisk (*) Keyword
SELECT *
FROM
SKU_DATA;
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-14
Specified Rows from One Table
SELECT
FROM
WHERE
*
SKU_DATA
Department = 'Water Sports';
NOTE: SQL wants a plain ASCII single quote: ' NOT ‘ !
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-15
Specified Columns and Rows from
One Table
SELECT
FROM
WHERE
SKU_Description, Buyer
SKU_DATA
Department = 'Climbing';
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-16
David M. Kroenke’s
Database Processing
Fundamentals, Design, and Implementation
(10th Edition)
End of Presentation:
Chapter Two Part One
DAVID M. KROENKE’S DATABASE PROCESSING, 10th Edition
© 2006 Pearson Prentice Hall
2-17