Database Reference
In-Depth Information
18. Use an update query to change the price of each book in the FictionCopies table with a current price of $14.00
to $14.50.
19. Use a delete query to delete all books in the FictionCopies table whose quality is poor.
70
Alexamara Marina Group Case
In the following exercises, you will use the data in the Alexamara database shown in Figures 1-20 through 1-24 in
Chapter 1. (If you use a computer to complete these exercises, use a copy of the Alexamara database so you will
still have the original data when you complete Chapter 3.) In each step, use QBE to obtain the desired results. You
can use the query feature in a DBMS to complete the exercises using a computer, or you can simply write a
description of how you would complete the task. Check with your instructor if you are uncertain about which
approach to take.
1. List the owner number, last name, and first name of every boat owner.
2. List the complete Marina table (all rows and all columns).
3. List the last name and first name of every owner located in Bowton.
4. List the last name and first name of every owner not located in Bowton.
5. List the marina number and slip number for every slip whose length is equal to or less than 30 feet.
6. List the marina number and slip number for every boat with the type Dolphin 28.
7. List the slip number for every boat with the type Dolphin 28 that is located in marina 1.
8. List the boat name for each boat located in a slip whose length is between 25 and 30 feet.
9. List the slip number for every slip in marina 1 whose annual rental fee is less than $3,000.
10. Labor is billed at the rate of $60 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 “EstimatedCost” for the estimated labor cost.
11. List the marina number and slip number for all slips containing a boat with the type Sprite 4000, Sprite 3000, or
Ray 4025.
12. List the marina number, slip number, and boat name for all boats. Sort the results by boat name within the
marina number.
13. How many Dolphin 25 boats are stored at both marinas?
14. Calculate the total rental fees Alexamara receives each year based on the length of the slip.
15. For every boat, list the marina number, slip number, boat name, owner number, owner's first name, and owner's
last name.
16. For every completed or open service request for routine engine maintenance, list the slip ID, description, and
status.
17. For every service request for routine engine maintenance, list the slip ID, marina number, slip number, esti-
mated hours, spent hours, owner number, and owner's last name.
18. Create a new table named LargeSlip using the data in the MarinaNum, SlipNum, RentalFee, BoatName, and
OwnerNum columns in the MarinaSlip table for slips with lengths of 40 feet.
19. Use an update query to change the rental fee of any slip in the LargeSlip table whose fee is currently $3,800 to
$3,900.
20. Use a delete query to delete all rows in the LargeSlip table in which the rental fee is $3,600.
