Transcript csis115Lecture23x

CSIS 115
Database Design and
Applications for Business
Dr. Meg Fryling
“Dr. Meg”
Fall 2012
© 2012 Meg Fryling
@SienaDrMeg
#csis115
Agenda
• HW4 Quick Review
• Chapter 2: Advanced SQL
• Next Quiz (Weds, 12/5)
– In-Class
– Can have cheat sheet
– Advanced SQL
• Project Part IV
– Due Monday, December 10th by midnight
Rest of Semester Agenda
• 12/3: Advanced SQL
• 12/5: Forms and Reports Workshop
– In-class quiz on SQL
• 12/10: Review
• Final Exam
– Thursday, December 13th, 8:30-10:30am
– RB350 (Open Lab)
A-3
Student Evaluations
• Available starting tomorrow
• Have until midnight on December 11th
(Reading Day) to complete
• If I get at least a 95% response rate, I will
drop the lowest quiz grade for everyone!
CS/Physics/Math Holiday Party
• December 11th (Reading Day) @ 1pm
• Everyone is welcome!
• Please see Piazza for announcement and
RSVP instructions
HW4 – Part II
A-6
HW4 – Part III
A-7
Advanced SQL In-Class
Activity
Blackboard > Assignments >
In-Class Activity >
8 – Advanced SQL
Parts I & II Only
A-8
Querying Multiple Tables:
Joins
What if the information I
seek is in multiple tables?
For example, I want to know
the buyer name and extended
price for each item ordered?
2-9
Querying Multiple Tables:
Joins
SELECT
FROM
WHERE
Buyer, ExtendedPrice
SKU_DATA, ORDER_ITEM
SKU_DATA.SKU = ORDER_ITEM.SKU;
What if I want the total of all ExtendedPrice values for each buyer?
Also, sort the results by total ExtendedPrice in descending order.
KROENKE AND AUER - DATABASE PROCESSING, 12th Edition
© 2012 Pearson Prentice Hall
2-10
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, 12th Edition
© 2012 Pearson Prentice Hall
2-11
From last week…
SELECT OrderNumber, ORDER_ITEM.SKU, buyer, warehouse
FROM ORDER_ITEM, SKU_DATA, INVENTORY
WHERE ORDER_ITEM.SKU = SKU_DATA.SKU ;
This returns 224 rows but it is mismatching and you can see it if include
the SKU from the inventory table in the select
SELECT OrderNumber, ORDER_ITEM.SKU, SKU_DATA.SKU,
INVENTORY.SKU, buyer, warehouse
FROM ORDER_ITEM, SKU_DATA, INVENTORY
WHERE ORDER_ITEM.SKU = SKU_DATA.SKU ;
A-12
Cartesian Join – VERY BAD!
A-13
Correct SQL
SELECT OrderNumber, ORDER_ITEM.SKU,
SKU_DATA.SKU, INVENTORY.SKU, buyer, warehouse
FROM ORDER_ITEM, SKU_DATA, INVENTORY
WHERE ORDER_ITEM.SKU = SKU_DATA.SKU
AND ORDER_ITEM.SKU = INVENTORY.SKU;
Rule of thumb: If x is the number of tables in your FROM
clause, you need x-1 PK/FK joins in your WHERE clause
A-14
That’s better!
A-15
Write a Query
• That will return the Buyer, ExtendedPrice
and OrderMonth of all orders
• What fields will be in my select clause?
• What tables do I need?
A-16
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, 12th Edition
© 2012 Pearson Prentice Hall
2-17
Advanced SQL In-Class
Activity
Blackboard > Assignments >
In-Class Activity >
8 – Advanced SQL
Part III & IV
A-18