Download - Database management chapter 2 power point
![Page 1: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/1.jpg)
David M. Kroenke and David J. AuerDatabase Processing:
Fundamentals, Design, and Implementation
Chapter Two:
Introduction to Structured Query
Language
2-1KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 2: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/2.jpg)
Chapter Objectives
• To understand the use of extracted data sets in business intelligence (BI) systems
• To understand the use of ad-hoc queries in business intelligence (BI) systems
• 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 create SQL queries to retrieve data from a single table
2-2KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 3: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/3.jpg)
Chapter Objectives
• To create SQL queries that use the SQL SELECT, FROM, WHERE, ORDER BY, GROUP BY, and HAVING clauses
• To create SQL queries that use the SQL DISTINCT, AND, OR, NOT, BETWEEN, LIKE, and IN keywords
• To create SQL queries that use the SQL built-in functions of SUM, COUNT, MIN, MAX, and AVG with and without the use of a GROUP BY clause
2-3KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 4: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/4.jpg)
Chapter Objectives
• To create SQL queries that retrieve data from a single table but restrict the data based upon data in another table (subquery)
• To create SQL queries that retrieve data from multiple tables using an SQL join operation
2-4KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 5: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/5.jpg)
Business Intelligence (BI) Systems
• Business intelligence (BI) systems are information systems that assist managers and other professionals:– Assessment– Analysis– Planning– Control
2-5KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 6: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/6.jpg)
Ad-Hoc Queries
• Ad-hoc queries:– Questions that can be answered using
database data– Example: “How many customers in Portland,
Oregon, bought our green baseball cap?”– Created by the user as needed, instead of
programmed into an application– Common in business
2-6KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 7: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/7.jpg)
Components of a Data Warehouse
2-7KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 8: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/8.jpg)
Structured Query Language
• Structured Query Language (SQL) was developed by the IBM Corporation in the late 1970’s.
• SQL was endorsed as a U.S. national standard by the American National Standards Institute (ANSI) in 1992 [SQL-92].
• Newer versions exist, and they incorporate XML and some object-oriented concepts.
2-8KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 9: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/9.jpg)
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.
2-9KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 10: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/10.jpg)
SQL DDL, DML, and SQL/PSM
• SQL statements can be divided into three categories:– Data definition language (DDL) statements
• Used for creating tables, relationships, and other structures
• Covered in Chapter 7
– Data manipulation language (DML) statements
• Used for queries and data modification• Covered in this chapter (Chapter 2)
2-10KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 11: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/11.jpg)
SQL DDL, DML, and SQL/PSM
– SQL/Persistent Stored Modules (SQL/PSM) statements
• Add procedural programming capabilities– Variables– Control-of-flow statements
• Covered in Chapters:– 7 (general introduction)– 10 (SQL Server 2008 R2)– 10A (Oralce Database 11g)– 10B (MySQL 5.5)
2-11KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 12: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/12.jpg)
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 United States and
Canada.– Has an online Internet store.– Has a (postal) mail order department.
• All retail sales are recorded in an Oracle database.
2-12KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 13: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/13.jpg)
Cape Codd Retail Sales Structure
2-13KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 14: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/14.jpg)
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—Microsoft SQL Server– Into different columns—OrderDate becomes OrderMonth and
OrderYear.
2-14KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 15: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/15.jpg)
Extracted Retail
Sales Data Format
2-15KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 16: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/16.jpg)
Retail Sales Extract Tables[in Microsoft Access 2010]
2-16KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 17: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/17.jpg)
The SQL SELECT Statement
• The fundamental framework for an SQL query is the SQL SELECT statement.– SELECT {ColumnName(s)}– FROM {TableName(s)}– WHERE {Condition(s)}
• All SQL statements end with a semi-colon (;).
2-17KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 18: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/18.jpg)
Specific Columns on One Table
SELECT Department, Buyer
FROM SKU_DATA;
2-18KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 19: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/19.jpg)
Specifying Column Order
SELECT Buyer, Department
FROM SKU_DATA;
2-19KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 20: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/20.jpg)
The DISTINCT Keyword
SELECT DISTINCT Buyer, Department
FROM SKU_DATA;
2-20KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 21: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/21.jpg)
Selecting All Columns: The Asterisk (*) Wildcard Character
SELECT *
FROM SKU_DATA;
2-21KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 22: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/22.jpg)
Specific Rows from One Table
SELECT *FROM SKU_DATAWHERE Department = 'Water Sports';
NOTE: SQL wants a plain ASCII single quote: ' NOT ‘ !
2-22KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 23: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/23.jpg)
Specific Columns and Rows from One Table
SELECT SKU_Description, Buyer
FROM SKU_DATA
WHERE Department = 'Climbing';
2-23KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 24: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/24.jpg)
Using Microsoft Access I
2-24KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 25: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/25.jpg)
Using Microsoft Access II
2-25KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 26: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/26.jpg)
Using Microsoft Access III
2-26KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 27: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/27.jpg)
Using Microsoft Access IV
2-27KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 28: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/28.jpg)
Using Microsoft Access V
2-28KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 29: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/29.jpg)
Using Microsoft Access—Results
2-29KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 30: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/30.jpg)
Using Microsoft AccessSaving the Query
2-30KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 31: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/31.jpg)
Using Microsoft AccessThe Named and Saved Query
2-31KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 32: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/32.jpg)
Using Microsoft SQL Server 2008 R2The Microsoft SQL Server Management Studio I
2-32KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 33: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/33.jpg)
Using Microsoft SQL Server 2008 R2The Microsoft SQL Server Management Studio II
2-33KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 34: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/34.jpg)
Using Oracle Database 11gSQL Developer I
2-34KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 35: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/35.jpg)
Using Oracle Database 11gSQL Developer II
2-35KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 36: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/36.jpg)
Using MySQL 5.5MySQL Workbench I
2-36KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 37: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/37.jpg)
Using MySQL 5.5MySQL Workbench II
2-37KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 38: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/38.jpg)
Sorting the Results—ORDER BY
SELECT *
FROM ORDER_ITEM
ORDER BY OrderNumber, Price;
2-38KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 39: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/39.jpg)
Sort Order:Ascending and Descending
SELECT *FROM ORDER_ITEMORDER BY Price DESC, OrderNumber ASC;NOTE: The default sort order is ASC—does not have to be specified.
2-39KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 40: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/40.jpg)
WHERE Clause Options—AND
SELECT *
FROM SKU_DATA
WHERE Department = 'Water Sports'
AND Buyer = 'Nancy Meyers';
2-40KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 41: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/41.jpg)
WHERE Clause Options—OR
SELECT *
FROM SKU_DATA
WHERE Department = 'Camping'
OR Department = 'Climbing';
2-41KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 42: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/42.jpg)
WHERE Clause Options—IN
SELECT *
FROM SKU_DATA
WHERE Buyer IN ('Nancy Meyers',
'Cindy Lo', 'Jerry Martin');
2-42KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 43: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/43.jpg)
WHERE Clause Options—NOT IN
SELECT *
FROM SKU_DATA
WHERE Buyer NOT IN ('Nancy Meyers',
'Cindy Lo', 'Jerry Martin');
2-43KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 44: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/44.jpg)
WHERE Clause Options—Ranges with BETWEEN
SELECT *
FROM ORDER_ITEM
WHERE ExtendedPrice
BETWEEN 100 AND 200;
2-44KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 45: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/45.jpg)
WHERE Clause Options—Ranges with Math Symbols
SELECT *
FROM ORDER_ITEM
WHERE ExtendedPrice >= 100
AND ExtendedPrice <= 200;
2-45KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 46: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/46.jpg)
WHERE Clause Options—LIKE and Wildcards I
• The SQL keyword LIKE can be combined with wildcard symbols:– SQL 92 Standard (SQL Server, MySQL, etc.):
• _ = exactly one character• % = any set of one or more characters
– Microsoft Access (based on MS DOS)• ? = exactly one character• * = any set of one or more characters
2-46KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 47: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/47.jpg)
WHERE Clause Options—LIKE and Wildcards II
SELECT *
FROM SKU_DATA
WHEREBuyer LIKE 'Pete%';
2-47KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 48: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/48.jpg)
WHERE Clause Options—LIKE and Wildcards III
SELECT *
FROM SKU_DATA
WHEREBuyer LIKE '%Tent%';
2-48KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 49: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/49.jpg)
WHERE Clause Options—LIKE and Wildcards IV
SELECT *
FROM SKU_DATA
WHERESKU LIKE '%2__';
2-49KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 50: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/50.jpg)
SQL Built-In Functions I
• There are five SQL built-in functions:– COUNT– SUM– AVG– MIN– MAX
2-50KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 51: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/51.jpg)
SQL Built-In Functions II
SELECT SUM(ExtendedPrice)
AS Order3000Sum
FROM ORDER_ITEM
WHEREOrderNumber = 3000;
2-51KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 52: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/52.jpg)
SQL Built-In Functions IIISELECT SUM(ExtendedPrice) AS OrderItemSum,
AVG(ExtendedPrice) AS OrderItemAvg,MIN(ExtendedPrice) AS OrderItemMin,MAX(ExtendedPrice) AS OrderItemMax
FROM ORDER_ITEM;
2-52KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 53: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/53.jpg)
SQL Built-In Functions IV
SELECT COUNT(*) AS NumberOfRows
FROM ORDER_ITEM;
2-53KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 54: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/54.jpg)
SQL Built-In Functions V
SELECT COUNT
(DISTINCT Department)
AS DeptCount
FROM SKU_DATA;
2-54KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 55: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/55.jpg)
Arithmetic in SELECT Statements
SELECT Quantity * Price AS EP,
ExtendedPrice
FROM ORDER_ITEM;
2-55KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 56: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/56.jpg)
String Functions in SELECT Statements
SELECT DISTINCT RTRIM (Buyer)
+ ' in ' + RTRIM (Department) AS Sponsor
FROM SKU_DATA;
2-56KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
NOTE: This SQL statement uses SQL Server 2008 R2 syntax—other DBMS products use different concatenation and character string operators.
![Page 57: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/57.jpg)
The SQL Keyword GROUP BY I
SELECT Department, Buyer,COUNT(*) ASDept_Buyer_SKU_Count
FROM SKU_DATAGROUP BY Department, Buyer;
2-57KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 58: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/58.jpg)
The SQL Keyword GROUP BY II
• 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.
2-58KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 59: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/59.jpg)
The SQL Keyword GROUP BY III
SELECT Department, COUNT(*) AS
Dept_SKU_Count
FROM SKU_DATA
WHERE SKU <> 302000
GROUP BY Department
ORDER BY Dept_SKU_Count;
2-59KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 60: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/60.jpg)
The SQL Keyword GROUP BY IV
SELECT Department, COUNT(*) ASDept_SKU_Count
FROM SKU_DATAWHERE SKU <> 302000GROUP BY DepartmentHAVING COUNT (*) > 1ORDER BY Dept_SKU_Count;
2-60KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 61: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/61.jpg)
Querying Multiple Tables: Subqueries I
SELECT SUM (ExtendedPrice) AS RevenueFROM ORDER_ITEMWHERE SKU IN
(SELECT SKU FROM SKU_DATA WHERE Department = 'Water Sports');
Note: The second SELECT statement is a subquery.
2-61KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 62: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/62.jpg)
Querying Multiple Tables: Subqueries II
SELECT BuyerFROM SKU_DATAWHERE SKU IN
(SELECT SKU FROM ORDER_ITEM WHERE OrderNumber IN
(SELECT OrderNumber FROM RETAIL_ORDER WHEREOrderMonth = 'January' AND OrderYear = 2011));
2-62KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 63: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/63.jpg)
Querying Multiple Tables:Joins I
SELECT Buyer, ExtendedPrice
FROM SKU_DATA, ORDER_ITEM
WHERE SKU_DATA.SKU = ORDER_ITEM.SKU;
2-63KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 64: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/64.jpg)
Querying Multiple Tables:Joins II
SELECT Buyer, SUM(ExtendedPrice)
AS BuyerRevenue
FROM SKU_DATA, ORDER_ITEM
WHERE SKU_DATA.SKU = ORDER_ITEM.SKU
GROUP BY Buyer
ORDER BY BuyerRevenue DESC;
2-64KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 65: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/65.jpg)
Querying Multiple Tables:Joins III
SELECT Buyer, ExtendedPrice, OrderMonth
FROM SKU_DATA, ORDER_ITEM, RETAIL_ORDER
WHERE SKU_DATA.SKU = ORDER_ITEM.SKU
AND ORDER_ITEM.OrderNumber =
RETAIL_ORDER.OrderNumber;
2-65KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 66: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/66.jpg)
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.
2-66KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 67: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/67.jpg)
David Kroenke and David Auer Database Processing
Fundamentals, Design, and Implementation(11th Edition)
End of Presentation:Chapter Two
2-67KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall
![Page 68: Database management chapter 2 power point](https://reader035.vdocuments.us/reader035/viewer/2022070304/54c689cd4a7959a2128b468d/html5/thumbnails/68.jpg)
All rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted, in any form or by any means, electronic,
mechanical, photocopying, recording, or otherwise, without the prior written permission of the publisher. Printed in the United States of America.
Copyright © 2012 Pearson Education, Inc. Copyright © 2012 Pearson Education, Inc. Publishing as Prentice HallPublishing as Prentice Hall
2-68KROENKE AND AUER - DATABASE PROCESSING, 12th Edition © 2012 Pearson Prentice Hall