David M Kroenke and David J Auer Database
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, 11 th 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, 11 th 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, 11 th 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, 11 th 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, 11 th 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, 11 th 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, 11 th Edition © 2010 Pearson Prentice Hall 2 -7
Cape Codd Retail Sales Structure KROENKE AND AUER - DATABASE PROCESSING, 11 th 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 – Order. Date becomes Order. Month and Order. Year. KROENKE AND AUER - DATABASE PROCESSING, 11 th Edition © 2010 Pearson Prentice Hall 2 -9
Extracted Retail Sales Data Format KROENKE AND AUER - DATABASE PROCESSING, 11 th Edition © 2010 Pearson Prentice Hall 2 -10
Retail Sales Extract Tables [in MS SQL Server] KROENKE AND AUER - DATABASE PROCESSING, 11 th 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 {Column. Name(s)} {Table. Name(s)} {Conditions} • All SQL statements end with a semi-colon (; ). KROENKE AND AUER - DATABASE PROCESSING, 11 th Edition © 2010 Pearson Prentice Hall 2 -12
Specific Columns on One Table SELECT Department, Buyer FROM SKU_DATA; KROENKE AND AUER - DATABASE PROCESSING, 11 th Edition © 2010 Pearson Prentice Hall 2 -13
Specifying Column Order SELECT Buyer, Department FROM SKU_DATA; KROENKE AND AUER - DATABASE PROCESSING, 11 th Edition © 2010 Pearson Prentice Hall 2 -14
The DISTINCT Keyword SELECT DISTINCT Buyer, Department FROM SKU_DATA; KROENKE AND AUER - DATABASE PROCESSING, 11 th Edition © 2010 Pearson Prentice Hall 2 -15
Selecting All Columns: The Asterisk (*) Keyword SELECT * FROM SKU_DATA; KROENKE AND AUER - DATABASE PROCESSING, 11 th 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, 11 th 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, 11 th Edition © 2010 Pearson Prentice Hall 2 -18
Using MS Access KROENKE AND AUER - DATABASE PROCESSING, 11 th Edition © 2010 Pearson Prentice Hall 2 -19
Using MS Access (Continued) KROENKE AND AUER - DATABASE PROCESSING, 11 th Edition © 2010 Pearson Prentice Hall 2 -20
Using MS Access (Continued) KROENKE AND AUER - DATABASE PROCESSING, 11 th Edition © 2010 Pearson Prentice Hall 2 -21
Using MS Access (Continued) KROENKE AND AUER - DATABASE PROCESSING, 11 th Edition © 2010 Pearson Prentice Hall 2 -22
Using MS Access (Continued) KROENKE AND AUER - DATABASE PROCESSING, 11 th Edition © 2010 Pearson Prentice Hall 2 -23
Using MS Access - Results KROENKE AND AUER - DATABASE PROCESSING, 11 th Edition © 2010 Pearson Prentice Hall 2 -24
Using MS Access Saving the Query KROENKE AND AUER - DATABASE PROCESSING, 11 th Edition © 2010 Pearson Prentice Hall 2 -25
Using MS SQL Server 2008 The Microsoft SQL Server Management Studio I KROENKE AND AUER - DATABASE PROCESSING, 11 th Edition © 2010 Pearson Prentice Hall 2 -26
Using MS SQL Server 2008 The Microsoft SQL Server Management Studio II KROENKE AND AUER - DATABASE PROCESSING, 11 th Edition © 2010 Pearson Prentice Hall 2 -27
Using Oracle Database 11 g SQL Developer I KROENKE AND AUER - DATABASE PROCESSING, 11 th Edition © 2010 Pearson Prentice Hall 2 -28
Using Oracle Database 11 g SQL Developer II KROENKE AND AUER - DATABASE PROCESSING, 11 th Edition © 2010 Pearson Prentice Hall 2 -29
Using My. SQL 5. 1 My. SQL Query Browser I KROENKE AND AUER - DATABASE PROCESSING, 11 th Edition © 2010 Pearson Prentice Hall 2 -30
Using My. SQL 5. 1 My. SQL Query Browser II KROENKE AND AUER - DATABASE PROCESSING, 11 th Edition © 2010 Pearson Prentice Hall 2 -31
Sorting the Results – ORDER BY SELECT * FROM ORDER BY ORDER_ITEM Order. Number, Price; KROENKE AND AUER - DATABASE PROCESSING, 11 th Edition © 2010 Pearson Prentice Hall 2 -32
Sort Order: Ascending and Descending SELECT * FROM ORDER_ITEM ORDER BY Price DESC, Order. Number ASC; NOTE: The default sort order is ASC – does not have to be specified. KROENKE AND AUER - DATABASE PROCESSING, 11 th 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, 11 th 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, 11 th 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, 11 th 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, 11 th Edition © 2010 Pearson Prentice Hall 2 -37
WHERE Clause Options – Ranges with BETWEEN SELECT FROM WHERE * ORDER_ITEM Extended. Price BETWEEN 100 AND 200; KROENKE AND AUER - DATABASE PROCESSING, 11 th Edition © 2010 Pearson Prentice Hall 2 -38
WHERE Clause Options – Ranges with Math Symbols SELECT FROM WHERE AND * ORDER_ITEM Extended. Price >= 100 Extended. Price <= 200; KROENKE AND AUER - DATABASE PROCESSING, 11 th 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, 11 th 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, 11 th 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, 11 th 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, 11 th 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, 11 th Edition © 2010 Pearson Prentice Hall 2 -44
SQL Built-in Functions SELECT SUM (Extended. Price) AS Order 3000 Sum FROM ORDER_ITEM WHERE Order. Number = 3000; KROENKE AND AUER - DATABASE PROCESSING, 11 th Edition © 2010 Pearson Prentice Hall 2 -45
SQL Built-in Functions SELECT FROM SUM (Extended. Price) AVG (Extended. Price) MIN (Extended. Price) MAX (Extended. Price) ORDER_ITEM; AS AS KROENKE AND AUER - DATABASE PROCESSING, 11 th Edition © 2010 Pearson Prentice Hall Order. Item. Sum, Order. Item. Avg, Order. Item. Min, Order. Item. Max 2 -46
SQL Built-in Functions SELECT COUNT(*) AS Number. Of. Rows FROM ORDER_ITEM; KROENKE AND AUER - DATABASE PROCESSING, 11 th Edition © 2010 Pearson Prentice Hall 2 -47
SQL Built-in Functions SELECT COUNT (DISTINCT Department) AS Dept. Count FROM SKU_DATA; KROENKE AND AUER - DATABASE PROCESSING, 11 th Edition © 2010 Pearson Prentice Hall 2 -48
Arithmetic in SELECT Statements SELECT Quantity * Price AS EP, Extended. Price FROM ORDER_ITEM; KROENKE AND AUER - DATABASE PROCESSING, 11 th 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, 11 th 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, 11 th 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, 11 th 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, 11 th 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, 11 th Edition © 2010 Pearson Prentice Hall 2 -54
Querying Multiple Tables: Subqueries SELECT FROM WHERE SUM (Extended. Price) 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, 11 th 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 Order. Number IN (SELECT Order. Number FROM RETAIL_ORDER WHERE Order. Month = 'January' AND Order. Year = 2004)); KROENKE AND AUER - DATABASE PROCESSING, 11 th Edition © 2010 Pearson Prentice Hall 2 -56
Querying Multiple Tables: Joins SELECT Buyer, Extended. Price FROM SKU_DATA, ORDER_ITEM WHERE SKU_DATA. SKU = ORDER_ITEM. SKU; KROENKE AND AUER - DATABASE PROCESSING, 11 th Edition © 2010 Pearson Prentice Hall 2 -57
Querying Multiple Tables: Joins SELECT FROM WHERE GROUP BY ORDER BY Buyer, SUM(Extended. Price) AS Buyer. Revenue SKU_DATA, ORDER_ITEM SKU_DATA. SKU = ORDER_ITEM. SKU Buyer. Revenue DESC; KROENKE AND AUER - DATABASE PROCESSING, 11 th Edition © 2010 Pearson Prentice Hall 2 -58
Querying Multiple Tables: Joins SELECT FROM WHERE AND Buyer, Extended. Price, Order. Month SKU_DATA, ORDER_ITEM, RETAIL_ORDER SKU_DATA. SKU = ORDER_ITEM. SKU ORDER_ITEM. Order. Number = RETAIL_ORDER. Order. Number; KROENKE AND AUER - DATABASE PROCESSING, 11 th 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, 11 th Edition © 2010 Pearson Prentice Hall 2 -60
David Kroenke and David Auer Database Processing Fundamentals, Design, and Implementation (11 th Edition) End of Presentation: Chapter Two KROENKE AND AUER - DATABASE PROCESSING, 11 th Edition © 2010 Pearson Prentice Hall 2 -61
- Slides: 61