Search for boats for rent under $200 per day, show Boat Unique Identifier, Boat Type, Boat Length, Price per Day to rent the boat and Location (where boat is parked for pickup (please include name of marina, not just the PrimaryKey). Create a query that shows the rentals that had different pickup and different drop off location, show Boat Unique identifier, Renter unique identifier, Start Date, End Date, Pickup Location and Drop Off Location and drop off fee of the Marina Drop Off Location(please include name of marina, not just the PrimaryKey). From Renter and Rental table:create a query that shows Renter unique identifier, Renter name, boat unique identifier, boat name, startdate, enddate of a rental and number of days rented (name column duration of rental). This will ensure you are counting your days right for some of the following queries. NOTE: Duration of Rental column will need to use DATEDIFF function shown at top of this assignment. 10. Create a query (from BOAT and Rental tables) that shows rentals longer than 1 day, this should list Boat Owner Unique Identifier, Boat Name, Boat Length, pickup location (Unique Identifier and Name of pickup location) and # of days rented. 11. Same as above, show rentals longer than a day, but this time show Boat type, start date of rental, end date of rental and marina name of pickup and number days rented. 12. Create a query that shows how much each boat earned in Rental income. This is a calculated query. You need to compute number of days boats were rented and multiply by price of rental per day. Output columns should be as follows: Income = #of days * priceperday if #of days = 2 and price per day =100 Income = $200 Boat Name Income 13. Similar to the above query that shows how much each boat earned in Owner Rental Income (what is the owners percentage), compute owners Rental Income. You have to comput income as in #12 above and then mulitply by the owner rent percent for that boat that is in the boat table. Note if you didn't store rent percent as a decimal, you have to adjust calculation. For example Income (as calculated above) = 200 and ownerPercentage = .30 (30%) 200*.3 = $60 is owner rental income ( a portion of the total boat rental income). If you have ownerrentpercent stored as whole number you have to divide the whole number by 100. For example, boatincome * (ownerPercentage/100) = 200*(30/100)= 200*.3=60 This query should have the following output columns. Boat Name Owner Rental Income Now show both of the above to appear in one query. The following columns: Boat Name Boat Rental Income Owner Rental Income Create a listing of Owners and Boats they have for rent on the app, include Owner ID, Owner Name and Boat Name, price per day, owner rent percent, Boat Type and Boat Location. Create a listing of what boat had the most rentals include boat name and number of rentals. Include boat name and number of rentals. You can do this with descending order The columns for this question is, hint, you need to count, not sum. Its okay if you don't just have top one, you can have all of them. Boat Name # of Rentals 17. Now add number of days rented to the above so it shows BoatName, number of rentals and number of days rented? BoatName Number of Rentals NumberOfDaysRented
- Search for boats for rent under $200 per day, show Boat Unique Identifier, Boat Type, Boat Length, Price per Day to rent the boat and Location (where boat is parked for pickup (please include name of marina, not just the PrimaryKey).
- Create a query that shows the rentals that had different pickup and different drop off location, show Boat Unique identifier, Renter unique identifier, Start Date, End Date, Pickup Location and Drop Off Location and drop off fee of the Marina Drop Off Location(please include name of marina, not just the PrimaryKey).
- From Renter and Rental table:create a query that shows Renter unique identifier, Renter name, boat unique identifier, boat name, startdate, enddate of a rental and number of days rented (name column duration of rental). This will ensure you are counting your days right for some of the following queries. NOTE: Duration of Rental column will need to use DATEDIFF function shown at top of this assignment.
10. Create a query (from BOAT and Rental tables) that shows rentals longer than 1 day, this should list Boat Owner Unique Identifier, Boat Name, Boat Length, pickup location (Unique Identifier and Name of pickup location) and # of days rented.
11. Same as above, show rentals longer than a day, but this time show Boat type, start date of rental, end date of rental and marina name of pickup and number days rented.
12. Create a query that shows how much each boat earned in Rental income. This is a calculated query. You need to compute number of days boats were rented and multiply by price of rental per day. Output columns should be as follows:
Income = #of days * priceperday if #of days = 2 and price per day =100 Income = $200
Boat Name Income
13. Similar to the above query that shows how much each boat earned in Owner Rental Income (what is the owners percentage), compute owners Rental Income. You have to comput income as in #12 above and then mulitply by the owner rent percent for that boat that is in the boat table. Note if you didn't store rent percent as a decimal, you have to adjust calculation. For example
Income (as calculated above) = 200 and ownerPercentage = .30 (30%) 200*.3 = $60 is owner rental income ( a portion of the total boat rental income). If you have ownerrentpercent stored as whole number you have to divide the whole number by 100. For example, boatincome * (ownerPercentage/100) = 200*(30/100)= 200*.3=60
This query should have the following output columns.
Boat Name Owner Rental Income
- Now show both of the above to appear in one query. The following columns:
Boat Name Boat Rental Income Owner Rental Income
- Create a listing of Owners and Boats they have for rent on the app, include Owner ID, Owner Name and Boat Name, price per day, owner rent percent, Boat Type and Boat Location.
- Create a listing of what boat had the most rentals include boat name and number of rentals. Include boat name and number of rentals. You can do this with descending order The columns for this question is, hint, you need to count, not sum. Its okay if you don't just have top one, you can have all of them.
-
- Boat Name # of Rentals 17. Now add number of days rented to the above so it shows BoatName, number of rentals and number of days rented?
BoatName Number of Rentals NumberOfDaysRented
Trending now
This is a popular solution!
Step by step
Solved in 2 steps