Reference no: EM13936810

Write SQL queries that meet the following specifications. Do not show output. All questions are worth three marks.

a) Display the order number and customer number for all orders. Sort by order number within customer number.
b) Display full details for all rows where the shipping street contains the string MAIN.
c) Display the title for each book and the profit margin for that book (profit = retail - cost). Your query should produce output in the following format. The book <title> earned a profit of <profit>, for example: The book Easy SQL earned a profit of $30.
d) Display full details for all books that cost less than thirty dollars and are in the FITNESS category OR cost more than thirty dollars and are in the SELF HELP category.
e) Display the customer id and the average length of time elapsed between an order being placed and the actual shipment of that order (shipment time) for all orders placed by that customer. Display the average length of time in months rounded to the nearest month.
f) Display complete details for the order/s with the earliest shipdate.
g) Using a set operator, display the customer number for all customers who placed an order during March, 2002 and who were referred by another customer. The column called referred may contain null values.
h) For each order item, display the order#, customer last name, item# and quantity.
i) Using a set operator, display the order number for all orders that have no items.

j) Display the order number and total quantity ordered for all orders that have a total quantity greater than 120.

Question 2

Write ONE PL/SQL program (anonymous block) that provides a report of all books published by specific publishers that have sold more than one thousand copies. The report should insert into an existing table the publisher name and the total number of copies of all books sold for that publisher (subject to specific business rules). The detailed specification is as follows:

· Using an explicit cursor, retrieve all books from the books table that were published by publishers 2, 6 or 7.
· Books with a retail price greater than $1 should be selected for further processing. This operation must be done using the IF statement.
· Using a second explicit cursor (with appropriate parameters), count the number of copies that the book has sold. Store the total count in a local variable named BOOKCOUNT and the total count of all books for a particular publisher in PUBCOUNT.
· If the book has sold one thousand or less copies, move to the next book. If no book has sold one thousand or more copies, exit the program and display the following message: There are no high sales books for these publishers.
· If a book has sold one thousand copies or more, retrieve details about the publisher. Use an explicit cursor.
· Into a table named HIGHCOUNT, insert the publisher name and the total count of all books sold for that publisher. This table has three columns as follows: publisher name, pubcount and rundate.
· Create an exception handler which fires when no rows are found. The exception handler should output the following message to the screen: No rows found.