material covered in chapters 1 through 4 of your SQL book by HC12061607512


									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

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
    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.

To top