VIEWS: 0 PAGES: 1 POSTED ON: 6/16/2012
CBIS 3214 SQL #2 Spring 06 Goette Due 3/23 You need to use Oracle SQL Plus to do this assignment (use the version available in the specified computer lab). All the commands/code must be run using the specified version of SQL. Instructions on how to get started and how to save your work have been provided in class - refer to the related Power Point lecture for additional instructions/reference. You will need the userid and password provided to access Oracle SQL. Save your work and turn in: 1. A printout of your spooled output file(s). 2. A printout of your code file (notepad file). 3. Electronic copy of your spooled output file(s) and code file by submitting it through WebCT. You should be familiar with the material covered in chapters 1 through 4 of your SQL book prior to attempting this assignment. 1. Run the script file to create all the tables and insert all the records for Alexamara Marina Group. (Get the script file from WebCT for this assignment.) 2. List all the records in every table. 3. List the first and last name of every owner located in Bowton in alphabetical order. 4. List the boat name for each boat located in a slip whose length is between 25 & 30 feet. 5. Labor is billed at the rate of $60.00 per hour. List the slip ID, category number, estimated hours, and estimated labor cost for every service request. To obtain the estimated labor cost, multiply the estimated hours by 60. Use the column name Estimated_Cost for the estimated labor cost. 6. Calculate the total rental fees Alexamara receives each year based on the length of the slip. 7. For every boat, list the marina number, slip number, boat name, owner number, owner’s first name, and owner’s last name in alphabetical order by the owner’s name. 8. For every service request for routine engine maintenance, list the slip ID, marina number, slip number, estimated hours, spent hours, owner number, and owner’s last name. 9. List the boat name, owner number, owner last name, owner first name for each boat in marina 1 that is in a 30-foot slip. List is alphabetical order by boat name. 10. List the marina number, slip number, and boat name for boats whose owner lives in Glander Bay but whose type is not Sprite 4000. 11. Find the service ID and slip ID for each service request whose estimated hours is greater than the number of estimated hours of at least one service request on which the category number is 3.
Pages to are hidden for
"material covered in chapters 1 through 4 of your SQL book"Please download to view full document