you will use the my_guitar_shop database to answer the questions in this assignm

Question

you will use the my_guitar_shop databaseto answer the questions in this assignment. Please download the script file https://www.dropbox.com/s/o966abkh72nc3sn/create_my_guitar_shop.sql?dl=0 and build the database on your server before starting your work on the questions if you have not already done so. Please contact the instructors if you encounter problems downloading or running the database script.

You may lose points if you use more than one query for each of questions 1, 2, 3, 5, and 6, and more than two queries for question 4.

For this assignment, you are not allowed to use the LIMIT keyword to filter result rows in any of yourSELECT queries. You may lose points if you do so.

For this assignment, you are not allowed to use exact match conditions to filter result rows in any of your SELECT queries. For example, you cannot use
        ... WHERE last_name=”Smith”... in a SELECT query. You may lose points if you do so.

Q.6) (20 points) Write a SELECT statement that answers this question: What is the total amount ordered for each product? Return these columns as shown in the screenshot: The product name from the Products table The total amount for each product in the Order Items (Hint: You can calculate the total amount by subtracting the discount amount from the item price and then multiplying it by the quantity) Include a row that gives the grand total over all products. Result GridFilter Rows Q Search product name Fender Precision Fender Stratocaster Gibson Les Pau Gibson SG Ludwig 5-piece Drum Set with Cymbals 489.99 Rodriguez Caballero 11 Tama 5-Piece Drum Set with Cymbals 679.99 Washburn D10S Yamaha FG700S product total 559.99 978.60 2517.90 208.16 253.15 598.00 303.79 7589.57

Explanation / Answer

Hi,

As you have not shared the columns list for the Product and order_Items table, I assume we have a product_id in both of these tables. Please replace the column names with the exact table names of your database.

Query-

SELECT P.PRODUCT_NAME,SUM((ITEM_PRICE-DISCOUNT)*QUANTITY) AS PRODUCT_TOTAL FROM PRODUCT P JOIN ORDER_ITEMS OI
ON P.PRODUCT_ID=OI.PRODUCT_ID
GROUP BY P.PRODUCT;

UNION ALL

SELECT NULL AS PRODUCT_NAME, SUM(PRODUCT_TOTAL) AS PRODUCT_TOTAL FROM(SELECT P.PRODUCT_NAME,SUM((ITEM_PRICE-DISCOUNT)*QUANTITY) AS PRODUCT_TOTAL FROM PRODUCT P JOIN ORDER_ITEMS OI
ON P.PRODUCT_ID=OI.PRODUCT_ID
GROUP BY P.PRODUCT);

Related Questions

Navigate

• Browse (All)
• Browse Y
• Subjects

---

---

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