#1 Using the BOOKS and PUBLISHER tables, create a query using the traditional join method that will return a list containing the book title, publisher contact person, and publisher phone number for all publishers whose publisher name starts with an ‘R’.
#2 Using the BOOK_CUSTOMER and BOOK_ORDER tables, create a query using the NATURAL JOIN method that will return a list containing the customer first name and last name (concatenated together with a space between the names) and the order number for all orders that have been shipped. Give the customer name column an alias of “Customer Name” and order the output by the customer number in the BOOK_ORDER table in ascending order.
#3 Using the BOOK_CUSTOMER, BOOK_ORDER, ORDERITEMS, and BOOKS tables, create a query using traditional join conditions based on comparisons between primary and foreign keys that will list the customer number, first and last name, and book title. Limit your listing to only those books in the ‘FITNESS’ category.
#4 Using the correct tables in your schema, create a query using either join operation you wish that will list the book title and number of authors for all books that have been written by more than one author. Give the title column and alias of “Book Title” and the column showing the number of authors an alias of “Number of Authors”.
#5 Using the BOOKAUTHER and AUTHOR tables for the upper most query, create a sub query that will return the ISBN, and the first and last name of the author(s) who have written the book(s) that have been purchased the most. To successfully accomplish this you will need to first find out the maximum total number of copies (based on quantity) sold of a book (based on isbn) in the ORDERITEMS table. This will be done in the mot bottom query. The return from this query will be used to be compared against the ISBN in the ORDERISTEMS table that occurs an equal number of times in the middle query. The return of this query will then be used to compare to an ISBN related to an Author in the tables of the top most query. (HINT: Be careful, you will need both GROUP BY and HAVING clauses for your solution.)
#6 Using the correct tables, create a sub query using either join operation you wish that will list the customer number, first and last name concatenated together, and city for all customers who have placed an order for the most expensive book JustLee Books carries (based on retail price). Give the combined customer names column and alias of “Customer Name”.
#7 Using the correct tables, create a query using either join operation you wish that will list the order#, order date, quantity ordered, and retail price for every book that has been ordered. Format the date as MM/DD/YYYY with an alias of “Order Date” and format the retail price column using the correct function to show dollars and cents with a dollar sign ( $ ) and a column alias of “Retail”.
#8 Using the correct tables, create a query using the traditional join operation that will list the customer first and last name concatenated together, book title, and order date (formatted as MM/DD/YYYY with an alias of “Order Date”) for all the customers who have purchased books published by ‘PRINTING IS US’.
#9 Using the BOOK_ORDER, ORDERITEMS, and BOOKS tables, create a query using an OUTER JOIN operation that will list the book title, order date and order number for all books in the BOOKS table. Order you output in descending order by book ISBN. There are three books that have never been ordered which should show up at the top of your listing.
#10 Using the correct tables, create a query using the JOIN method to display a listing of all customers who have purchased more than 2 books. List the customer’s last name, the customer’s city and state, and the number of books they have purchased. Give the number purchased column and alias of “Number Purchased”.
#11 Using only the BOOK table for the upper most statement, create a sub query that will update the retail price of all books published by PRINTING IS US by 5% (retail * 1.05). Remember, all you have is the publisher “name”. Use the sub query to find the publisher ID. To verify your update, provide a select statement on the BOOKS table before your update statement, and then a second select on the BOOKS table after the update. Once you have verified the results, you can issue a ROLLBACK statement to return the data to the original state.
#12 Using the BOOK_CUSTOMER table, write a sub query that will return the names of those customers who were referred by the same individual who referred Jorge Perez. Do not display Jorge Perez’s name in the returned results (Hint: you should only have two customers displayed and the exclusion for Jorge Perez must be associated with the upper query.)
#13 Using the BOOKS and ORDERITEMS table, write a query using the correct Relational Set Operator that will show all of the ISBN numbers in the BOOKS table that have not been ordered.