Use the Pizza database created in the last chapter’s practices and write SQL sta
ID: 3856332 • Letter: U
Question
Use the Pizza database created in the last chapter’s practices and write SQL statements to answer these questions:
Complete practice question #10 at the end of chapter 7.
Join the product and the Order Detail table so that the result contains the product name, product unit size, and product unit price as well as the charged price. Do it for order 1000.
Complete practice questions #11, #12, and #13 at the end of chapter 7.
List all the order and order details for each order made by the customer with the phone number (206) 555-6623.
Change the price of breadsticks to 3.00. Process a pizza order for a new customer. (This will involve 3 INSERT statements.)
2065552123
Lamont
Null
161 South Western Ave
NULL
NULL
98001
2065553252
Johnson
Apt. 304
1215 Terrance Avenue
Seattle
WA
98001
2065552963
Lewis
NULL
520 East Lake Way
NULL
NULL
98002
2065553213
Anderson
Apt. 10
222 Southern Street
NULL
NULL
98001
2065522217
Wong
NULL
2832 Washington Ave
Seattle
WA
98002
2065556623
Jimenez
Apt. 13 B
1200 Norton Way
NULL
NULL
98003
Customer
Employee Table
soda
Soda Bottle
2 Liter Bottle
3.75
brdsticks
Breadstick
8 per pack
2.50
BasicS
Basic Pizza Small
8 inch
5.35
basicM
Basic Pizza Medium
12 inch
7.35
basicL
Basic Pizza Large
18 inch
13.50
specialS
Specialty Small
8 inch
6.35
specialM
Special Medium
12 inch
9.25
SpecialL
Special Large
18 inch
15.00
top
Additional Toppings
1 cup
1.00
Customer Order Table
1000
10/8/2014
14:15:00
2065552963
cmanning
1001
10/8/2014
14:25:00
2065556623
cmanning
1002
10/8/2-14
14:30:00
2065552963
cmanning
1003
10/8/2014
15:15:00
2065552123
skristoph
1004
10/10/2014
11:15:00
2065522217
btaylor
1005
10/10/2014
12:02:00
2065556623
btaylor
1
1000
soda
2
7.25
2
1000
brdstick
1
2.50
3
1000
specialM
1
7.35
4
1001
specialL
1
15.00
5
1002
soda
2
7.25
6
1002
basicM
3
20.00
7
1003
basicM
1
7.35
8
1004
top
4
4.00
9
1004
basicL
1
13.50
10
1005
basicM
2
14.70
Use the Pizza database created in the last chapter’s practices and write SQL statements to answer these questions:
Complete practice question #10 at the end of chapter 7.
Join the product and the Order Detail table so that the result contains the product name, product unit size, and product unit price as well as the charged price. Do it for order 1000.
Complete practice questions #11, #12, and #13 at the end of chapter 7.
List all the order and order details for each order made by the customer with the phone number (206) 555-6623.
Change the price of breadsticks to 3.00. Process a pizza order for a new customer. (This will involve 3 INSERT statements.)
2065552123
Lamont
Null
161 South Western Ave
NULL
NULL
98001
2065553252
Johnson
Apt. 304
1215 Terrance Avenue
Seattle
WA
98001
2065552963
Lewis
NULL
520 East Lake Way
NULL
NULL
98002
2065553213
Anderson
Apt. 10
222 Southern Street
NULL
NULL
98001
2065522217
Wong
NULL
2832 Washington Ave
Seattle
WA
98002
2065556623
Jimenez
Apt. 13 B
1200 Norton Way
NULL
NULL
98003
Explanation / Answer
Join the product and the Order Detail table so that the result contains the product name, product unit size, and product unit price as well as the charged price. Do it for order 1000.
select Product.ProductName, Product.ProductUnitSize, Product.ProductunitPrice
from OrderDetail, Product
where OrderDetail.orderKey = 1000 and Product.ProductKey = OrderDetail.ProductKey
List all the order and order details for each order made by the customer with the phone number (206) 555-6623.
select CustomerOrder.OrderDate, CustomerOrder.OrderTime, CustomerOrder.CustomerPhoneKey, Product.ProductName, OrderDetail.OrderDetailQuantity, OrderDetail.OrderDetailPriceChanged
from CustomerOrder, OrderDetail, Product
where CustomerOrder.CustomerPhoneKey = '(206) 555-6623'
and CustomerOrder.OrderKey = OrderDetail.OrderDetailKey
and Product.ProductKey = OrderDetail.ProductKey
Change the price of breadsticks to 3.00.
update Product set ProductUnitPrice = 3.00 where ProductName='Breadsticks'
Process a pizza order for a new customer. (This will involve 3 INSERT statements.)
Insert into Customer('(206) 555-1234', 'L1', 'A1', 'A2', 'C1', 'S1', 93323);
Insert into CustomerOrder(1006, '10/8/2017', '2:15 PM', '32432432432', 'btaylor');
Insert into OrderDetail(11, 1006, 'specialM', 1, 16.25);
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.