Create the SQL script for the following questions using the JustLee Books database. Create a spool file that includes all of your SQL commands along with the results. Submit the script AND spool file.
1. Add the following lines to your script:
set echo on
set linesize 132
set pagesize 66
2. LIST each customer order number and the total amount to be paid per order. Include the customer firstname, customer lastname and order#. Sort by last name and first name. Use the PAIDEACH column to determine the price for each book. Expected output:
First Last ORDER# Order Ttl Kenneth Kenneth Tammy Tammy Cindy Cindy Kenneth Jasmine Jake Jake Reese Greg Greg Bonita Bonita Becca Thomas Steve Jennifer Leila Leila Falah Falah Giana Giana Girard Girard Jones Lee Lucas Lucas Mcgovern Montiasa Montiasa Morales Morales Nelson Pierson Schell Smith Smith Smith 1015 1004 1014 1007 1000 1009 1020 1013 1011 1001 1002 1019 1005 1003 1018 1012 1008 1017 1010 1006 1016 19.95 170.9 44 335.85 19.95 41.95 19.95 55.95 85.45 117.4 111.9 22 39.95 106.85 75.9 166.4 39.9 17.9 55.95 54.5 85.45 21 rows selected. SOL> 10:27 CH Th 7 20 thg 2 53% learn.sait.ca 3. What is the average number of books published per author? (Hint: Start by exploring the number of books published for each author). There are 1.67 books/author. 4. Create a report showing the average retail price of books in each category but only include the category if it has at least 2 books in it. Sort by category name. Include the category name, # of books in the category and average price in the report. Show the price with a $ and 2 digits of precision through the use of a single row function. Expected output: Category Num of Books Average Children Computer Cooking 2 $34.45 4 $52.85 2 $24.35 2 $55.98 Family Life 4 rows selected 5. List the authors that have sold at least 10 books based on orderitems. Show the total number of books they have sold. Sort by author name. Use the heading Number of books Sold" on the report. Expected output: First Last # of Books James Jack Robert Austin Baker Robinson 10 14 10 3 rows selected 10:27 CH Th 7 20 thg 2 53% learn.sait.ca 6. Produce the following report using Oracle enhanced aggregation features. The value for each order item is based on the quantity ordered and the price paid for each item. Expected output: FIRST LAST ORDER# Value 1004 1015 170.9 19.95 1007 1014 190.85 335.85 44 379.85 1000 1009 19.95 41.95 1020 1013 1001 1011 Kenneth Kenneth Kenneth Tammy Tammy Tammy Cindy Cindy Cindy Kenneth Kenneth Jasmine Jasmine Jake Jake Jake Reese Reese Greg Greg Greg Bonita Bonita Bonita Becca Becca Thomas Thomas Steve Steve Jennifer Jennifer Leila Leila Leila 1002 Falah Falah Falah Giana Giana Giana Girard Girard Girard Jones Jones Lee Lee Lee Lucas Lucas Lucas M Mcgovern Mentiasa Montiasa Montiasa Morales M Morales Morales Nelson NO Nelson Pierson Pierson Schell Schell Smith Smith Smith Smith Smith 61.9 19.95 19.95 55.95 55.95 117.4 85.45 202.85 111.9 29 95 39.95 30 6105 106.85 26 OF Mcgovern 111.9 Montiasa 1005 1019 1003 1018 75.9 1012 1008 1017 182.75 166.4 166.4 39.9 39.9 17.9 17.9 55.95 55.95 54.5 85.45 139.95 1688.65 1010 1006 1016 36 rows selected. Database Design and Programming CPRG 250 Page 3
