Description
Sub queries are a big topic and we are kind of giving them short shrift here, but hopefully it will help you get the gist of them. We will also create a couple of views.
To Do
Provide the SQL that would answer the question. For the first 5 use subqueries even where joins might work.
Subqueries
1. Use a subquery to determine which Apartments have never been rented?
2. Return the aptnumber, the average lease amount, and the monthly rent for those apartments that have a monthly rent less than the average rent.
3. List all the tenants first and last name who have roomates Do with all subqueries, no inner joins.
4.Get which tenants have unresolved maintanence requests use only subqueries (it will require 3 inner queries)
Views
5. create a view that shows the tenant name (first and last), any roomates names,the leasenumber, the start and end dates and the montly rent for each lease.
6. create a view that shows the total rent collected each year)
Extra Credit
For a challenge create a view that gets the grand total amount of rent collected --this requires a column as subquery, and divide the annual total (the number from the last query( by the grand total to get what percent each year is of the grand total. Additionally you can try to use cast and substrings to format the whole thing.
To turn in
Turn in the code