Please use SQL Developer to write and run the queries below. Copy and paste your
ID: 3919498 • Letter: P
Question
Please use SQL Developer to write and run the queries below. Copy and paste your query code into the submission box. This assignment should be completed by July 19th.
Write a query that shows the total sales for an order date in 2009. Label the data "Total Sales in 2009".
Write a query that shows largest profit for books in the Computer category.
Write a query that displays each shipstate along with the number of orders shipped to that state. Display the results in descending order by state
Write a query that displays order number and the total paid for each order. Format the amount using dollars and cents. Label the total amount "Order Total."
Write a query to show the retail price of the most expensive book written by Lisa White.
Explanation / Answer
You would need to provide me the table descriptions and structure so that I can answer your queries correctly. Meanwhile, I am providing you sample queries for the problems.
a) SELECT sum(sale) AS "Total Sales in 2009" FROM Order WHERE order_date LIKE '%2009%';
b) SELECT max(profit) FROM Book WHERE category = 'Computer';
c) SELECT ship_state, count(*) FROM Order GROUP BY ship_state ORDER BY ship_state DESC;
d) SELECT order_num, '$' + convert(VARCHAR, sum(price),1) AS 'Order Total' FROM Order GROUP BY order_num;
e) SELECT max(retail_price) FROM Book WHERE author = 'Lisa White';
NOTE: Once you provide me the table structure, if I am allowed to, I will update the answers as per your schema.