SOLUTION AVAILABLE AT: http://libraay.com/downloads/ecet-450-practice-exercise-6-oracle-joins-and-sub-queries/

Problem:

QUERIES

1. Using the traditional join method, write a query using the ORDERITEMS and BOOK tables to list the order number, book title, and retail amount of the book ordered. Format the retail amount to reflect dollars and cents using the correct function and give the retail amount column and alias of “Cost.”
2. Rewrite the query in #1, only this time, use a NATURAL JOIN operator to create the join. Your output should be the same.
3. Write a query using the BOOKS and PUBLISHER tables that will show all of the publishers’ names and related book titles, even those publishers who do not currently have any books listed in the BOOKS table.  HINT: this will require an OUTER JOIN
4. Write a sub query using the BOOKS and PUBLISHER table to list the ISBN, book title, and the retail price for every book in the BOOKS table published by AMERICAN PUBLISHING. Format the book price to show a dollar sign and two decimal places by using the correct conversion function.
5. Write a sub query using the ORDERITEMS table that will return only the ISBN of the book that has been bought most often. This process will require you to obtain the maximum total number of books ordered (based on ISBN) and then compare that to total books ordered, grouped by ISBN. Remember that sub queries work from the bottom up so the process described here is starting with the second query, not the first (remember this exercise when doing #5 in the lab).

Turn the following in for a grade:

1. This lab sheet as a cover sheet with your name on it
2. Your script file with the five queries in it
3. The output file showing both the query and result set
4. Add this assignment (all three documents) to the zip file containing all assignments for this week.

SOLUTION AVAILABLE AT: http://libraay.com/downloads/ecet-450-practice-exercise-6-oracle-joins-and-sub-queries/