I am using SQL and I need a code that will give me this outp

I am using SQL and I need a code that will give me this output. You can use the names of the columns and rows on these tables to write the code. I just don\'t know how to write them. Please SQL. Thank you :)

The table definitions are as follows...

Q.3 (15 points Write a query that replicates the result set below, matching all data and format aspects, including column headers. (Hint: This query lists records in which the value of the item total column is greater than 500.) Result Grid Filter Rows: a Search Export: item id product name Item Price discount amount quantity price total discount total item total 5 Gibson Les Paul 1,199.00 359.70 2 1,678.60 2,398.00 719.40 2,517.00 1,308.84 1,208.16 Gibson SG 2,517.00 1,308.84 359.70 1 1,199.00 359.70 Gibson Les Paul 1,199.00 839.30 11 Tama 5-Piece Drum Set with Cymbals 799.99 120.00 799.99 120.00 679.99 Fender Precision 559.99 799.99 240.000 799.99 240.00 Q.4) (20 points Write a query that replicates the result set below, matching all data and format aspects, including column headers: Result Grid Filter Rows: a Se order id order date ship status 2015-03-28 09:40:28 SHIPPED 2015-03-28 11:23:20 SHIPPED 2015-03-29 09:44:58 SHIPPED 2015-03-30 15:22:31 SHIPPED 2015-03-31 05:43:11 SHIPPED 2015-03-31 18:37:22 NOT SHIPPED 7 2015-04-01 23:11:12 SHIPPED 2015-04-02 11:26:38 NOT SHIPPED 2015-04-03 12:22:31 NOT SHIPPED

Solution

Following is SQL for question Q3:

SELECT oi.item_id,
p.product_name,
oi.item_price,
oi.discount_amount,
oi.quantity,
SUM(oi.quantity * p.list_price) AS price_total,
SUM(oi.discount_amount) AS discount_total,
SUM(oi.item_price) AS item_total
FROM order_items oi, product p
where p.product_id = oi.product_id
GROUP BY oi.item_id,oi.order_id,p.product_id

For question Q4 ship_status column is missing in orders table or let me know how to calculate ship status.

Please comment so I can provide you the solution.

Thanks.