Question

Please implement this in Oracle sql

2.) Write a SELECT statement that answers this question: What is the total amount ordered for each product? Return these columns:

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)

Use the ROLLUP operator to include a row that gives the grand total.

Answer 1

>>Answer

>>Given

SELECT p_name,

round( SUM ( quantity * i.list_price * (1 - discount) ),/when discount is in percentage use (100-discount)/

FROM Product

INNER JOIN Order ON Orders.productID=Products.productID

GROUP BY ROLLUP(p_name);

Explanation:

The SELECT statement is used to select data from a database. We need to select data from two different tables, therefore, we use JOIN.

A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

Assuming that productID is the related column

The INNER JOIN keyword selects records that have matching values in both tables.

The ROLLUP is an extension of the GROUP BY clause. The ROLLUP option allows you to include extra rows that represent the subtotals, which are commonly referred to as super-aggregate rows, along with the grand total row. By using the ROLLUP option, you can use a single query to generate multiple grouping sets.