Using the tables below, answer the following questions in a Word document. Show both the SQL statement and the results (output and messages). Edit the final exam file and put your answers in the file with each question. Be sure to use Oracle Academy to work with the live Oracle database to prepare the solutions. Execute each solution using Oracle Academy and cut and paste your solution and the output and/or messages that are produced by Oracle when the statement is executed. This file is to be submitted via the assignment link on blackboard. Be sure to show both the SQL statement and the results (output and messages).

1. Write a query that displays the title and publication date for all books whose title begins with the letter B. Display the date using the format Monday, May 5, 2008. No data

2. Write a query that displays the publisher name, contact, book title, and publication date for all books.

3. Modify the previous query to include publishers even if they have not published any books (note: there are not any additional publishers, but show the command that would display them if there were).

4. Create a view called CUSTPROFIT with three columns. include the

1) customer number,

2) customer lastname, and

3) total profit ((retail-cost)*quantity) of all orders for each customer. Use a column alias, totalprofit, for the total profit column.

5. Write a query from your view CUSTPROFIT that returns the customer name and total profit of all orders for customer number 1005. $4.50

6. Write a query that displays the book title, customer name, and customer region for all books ordered from customers located in the region N or NE. Instead of displaying N, display North, and instead of displaying NE, display Northeast. Sort the output by region and then title.

7. Determine the average retail price of books by publisher name. Round the average price to 2 decimal places.

8. Modify #5 so that you include only publishers who have published at least 2 books.

9. Determine which book titles cost less than the average cost of all books from the same publisher. Sort your output on the book title.

10. Write a query to return the first character of the first name concatenated with a period and space and then concatenated to the last name of all authors that sold books to customers in the state of Texas, California, or New York. Display the last name with an uppercase first letter and the remainder lower case like J. Smith. Give the expression an alias of Name. Order the results by last name.