David M Kroenkes Database Processing Fundamentals Design and

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, 10 th Edition © 2006 Pearson Prentice Hall 1

Structured Query Language • Structured Query Language (SQL) – developed by IBM in the late 70 s • It was endorsed as a national standard in 1992 – American National Standards Institute (ANSI) [SQL-92]. • A newer version [SQL 3] exists – It incorporates object-oriented concepts, – It is not (yet) widely used in commercial DBMS products. DAVID M. KROENKE’S DATABASE PROCESSING, 10 th Edition © 2006 Pearson Prentice Hall 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, 10 th Edition © 2006 Pearson Prentice Hall 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, 10 th Edition © 2006 Pearson Prentice Hall 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, 10 th Edition © 2006 Pearson Prentice Hall 5

Cape Codd Retail Sales Structure DAVID M. KROENKE’S DATABASE PROCESSING, 10 th Edition ©

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 Order. Date becomes Order. Month and Order. Year DAVID M. KROENKE’S DATABASE PROCESSING, 10 th Edition © 2006 Pearson Prentice Hall 7

Extracted Retail Sales Data Format DAVID M. KROENKE’S DATABASE PROCESSING, 10 th Edition ©

Retail Sales Extract Tables [in MS SQL Server] DAVID M. KROENKE’S DATABASE PROCESSING, 10

The SQL SELECT Statement • The fundamental framework for SQL query states is the SQL SELECT statement: – SELECT – FROM – WHERE {Column. Name(s)} {Table. Name(s)} {Conditions} • All SQL statements end with a semi-colon (; ). DAVID M. KROENKE’S DATABASE PROCESSING, 10 th Edition © 2006 Pearson Prentice Hall 10

Specific Columns on One Table SELECT Department, Buyer FROM SKU_DATA; DAVID M. KROENKE’S DATABASE

Specifying Column Order SELECT Buyer, Department FROM SKU_DATA; DAVID M. KROENKE’S DATABASE PROCESSING, 10

The DISTINCT Keyword SELECT FROM DISTINCT Buyer, Department SKU_DATA; DISTINCT does not work in

Selecting All Columns: The Asterisk (*) Keyword SELECT * FROM SKU_DATA; DAVID M. KROENKE’S

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, 10 th Edition © 2006 Pearson Prentice Hall 15

Specified Columns and Rows from One Table SELECT FROM WHERE SKU_Description, Buyer SKU_DATA Department = 'Climbing'; DAVID M. KROENKE’S DATABASE PROCESSING, 10 th Edition © 2006 Pearson Prentice Hall 16

David M. Kroenke’s Database Processing Fundamentals, Design, and Implementation (10 th Edition) End of Presentation: Chapter Two Part One DAVID M. KROENKE’S DATABASE PROCESSING, 10 th Edition © 2006 Pearson Prentice Hall 17