MYSQL VIEW QUERY Hi, I need some help with some mysql view queries, any help wou

Question

MYSQL VIEW QUERY

Hi, I need some help with some mysql view queries, any help would be greatly appreciated!

Create a view named product_summary. This view should return summary information
about each product. Each row should include product_id, order_count (the number of times the
product has been ordered) and order_total (the total sales for the product). Write a SELECT
statement that returns all the columns from the product_summary view.

Create a view named order_items that returns columns from the ORDERS, ORDERITEMS,
and PRODUCT tables. This view should return these columns from the ORDERS table: order_id,
order_date, tax_amount, and ship_date. This view should return these columns from the
ORDERITEMS table: item_price, discount_amount, final_price (the discount amount subtracted
from the item price), quantity, and item_total (the calculated total for the item).
This view should return the product_name column from the PRODUCT Table.
Write a SELECT statement that returns all the columns from the order_items view.

disabled store id store id region_id region_name order id customer id employee_id store_id order_date ship_amount tax_amount ship_date shipping_address_idINT(4) card type card_number card_expires billing_address_id orderid VARCHAR(10) VARCHAR(3) CHAR(5) CHAR(5) CHAR(25) INT(4) INT(4) INT(4) VARCHAR(3) DATETIME DECIMAL(10,2) DECIMAL(10,2) DATETIME STORE PK PK FK FK FK ORDERS CUSTOMER EMPLOYEE STORE VARCHAR(50) CHAR(16) VARCHAR(7) INT(4) INT(4) ORDERITEMS PK ORDERS FK itemid product_id quantit item_price discount amount employee_id store_id first name last name phone email salary mgr INT(4) INT(4) INT(3) DECIMAL(10,2) DECIMAL(10,2) INT(4) VARCHAR(3) CHAR(25) CHAR(25) CHAR(12) VARCHAR(100) DECIMAL(10,2) INT(4) INT VARCHAR(255) CHAR(255) CHAR(255) CHAR(255) FK PRODUCT EMPLOYEE PK FK STORE UNIQUE ADMINISTRATORS admin id PK email_address password first name last_name

Explanation / Answer

Create view product_summary as Select Product.product_id,count(order_id) as order_count ,sum(quantity*item_price) as order_total from Product inner join OrderItems on Product.product_id = OrderItems.product_id group by Product.product_id;

Select * from product_summary;

Create view order_items as Select Orders.order_id, order_date,tax_amount, ship_date , OrderItems.item_price, discount_amount, (item_price - discount_amount) as final_price , quantity, item_price * quantity as item_total ,product_name from Product inner join OrderItems on Product.product_id = OrderItems.product_id inner join Orders on Orders.order_id = OrderItems.order_id;

Select * from order_items;

Do ask if any doubt. Please upvote.

Related Questions

Navigate

• Browse (All)
• Browse M
• Subjects

---

---

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