Complete practice question #10 at the end of chapter 7. Join the product and the

Question

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.)

Explanation / Answer

Hi,

As, there is no reference given what is inside chapter 7 and what does question 10 hold, I will proceed with the questions given here.

Ans i) As table structure is not provided, so I assume that product table has productid column and it acts as a foreign key in OrderDetails table.
We can join these table and fetch the column needed-
Query--
select product_name,
product_unit_size,
product_unit_price,
total_charged_price
from order_detail od
join product p
on od.productid=p.productid
and orderid='1000';

Ans ii) As table structure is not provided, so I assume that Customer table has primary key customerid and it is available in order table as foreign key. Similarly, assume that order table has primary key orderid and it acts as a foregn key in order details table.
Query--
select o.*, od.*
from order o
join customer c
on o.customerid=c.customerid
join order_detail od
on o.orderid=od.orderid
and c.phone_number='(206) 555-6623';

Ans iii)
Update product set product_unit_price=3 where product_name='breadsticks';

Ans iv) Assuming that order table has columns orderid,customer_name,productid,order_date
Insert statements--
insert into order values('101','Sam','Pizza','2017-07-12');
insert into order values('102','Sally','Pizza','2017-07-12');
insert into order values('103','Sandy','Pizza','2017-07-12');

Regards,

Vinay Singh

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.