Description
This assignment explores some of the inline functions in SQL. Most of the problems involve using more than one function in a single select. They are not easy, but they are doable. Consider them little puzzles.
To Do
Write and test the SQL that would provide answers to the problems below. All problems use AptManagement2 (or your equivalent).
Alias all derived columns.
1. You are looking at the leases. You want to see what adding 5% (times 1.05 )would do to each rent for when the leases are renewed.
2. You want a phone list for all the tenants. You want to list it as a single column that looks like: Lastname, Firstname--Phone
3. You weren't really pleased with the last report (2). Create a new one where the phone is a seperate column and the phone number is formated like (###)###-####. (Use a combination of concatinations and substring functions)
4. Run all the Rents paid for April 2006. You want the payment to be formatted as $####.00 and the date to look like "Apr 1 2006"
5. Get the lease number and all the months and years for the lease start dates
6. You want to make a report on the maximum capacity of the Apartments. Where the number of bedrooms is 1 you want a maximum capacity of 3; for 2 bedrooms a capacity of 5 and for 3 a capacity of 7.
7. Create a report for the MaintenanceReqDetails table. Where the Request has not been resolved return the string "Still to be done".
To turn in
Turn in the SQL code