Please use SQL Developer to write and run the queries below. Copy and paste your

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 March 5th.

Write a query that shows the total number of orders shipped in 2009. Label the data "Orders Shipped in 2009".

Write a query that shows the latest publication date of 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 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

Answer is as follows:

a)

SELECT count(Total_no_orders) AS Orders_Shipped_in_2009 FROM Orders WHERE year = 2009 ;

Here Table name is order and year is attribute used to select year 2009.

b)

SELECT Date FROM Books WHERE Category = "Computer" AND Date = maxDate;

Here maxDate is function to get latest date. Books is table name.

c)

SELECT Shipstate, count(orders) FROM Ship ORDER BY ShipState ;

Ship is table name here and count function is used to count no of orders.

d)

SELECT OrderNo , Total_Paid AS Order_Total FROM Orders WHERE Total_Paid = Substr(FormatNumber([number];"0");1;Length(FormatNumber([number];"0"))-2) + "$ " + Right(FormatNumber([number];"0");2) + " cents" ;

here Orders is table name and where condition is used to format the amount into dollars and cents.

e) SELECT MAX(Retail_Price) AS retail_price FROM Books WHERE Author = "Lisa White" ;

here books is table name and Where clause is used find author Lisa White and Max is used to find maximum retail price.

if there is any query please ask in comments........

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects