Lab 9: Database & Queries

Hey, welcome back! Hope you guys had a great break :) 

Here is your last assignment. Yaay :) (I know how you guys feel now, I have been there too!) So let's go ahead and start.

Initial set up

1. You have a file, 'BeachRentals.accdb' in your ICON. Download that. Double-click to open that.
2. This will be your starting point. 

Know the basic queries

• OR: To display names of the users from Colorado or Florida, you use OR this way => "Colorado" or "Florida" in the respective state column when you design the query. Make sure you give the appropriate names in the columns within the double quotes.
• AND:  Similarly, to display names of the users from both Colorado and Florida, you use AND this way => "Colorado" and "Florida" in the respective state column when you design the query.  
• Basic SQL queries: Link for reference

Let's start

• Query-1: From the Owner table: display the first and last names of all owners whose first names contain the string jo. Hint: This is similar to Query 3, in Lab 8. You should use Like. 

• Query-2: From the Client table: display the Names and the State of all clients from Colorado or Florida. Hint: Look at the OR example above.

• Query-3: From all three tables: display owner’s Last and First Name, Client Name, Current Due and Unit Number of all units that are rented by a client from Iowa or Colorado who owes more than 300$ . Hint:
• Splitting works! "display owner’s Last and First Name, Client Name, Current Due and Unit Number" => Get all the corresponding columns (Totally 5) from each table (that are given explicitly).
• "of all units that are rented" => One more column to add here.
• "by a client from Iowa or Colorado" => Get the states using Like
• "who owes more than 300$" => This is similar to Query 1 in Lab 8. 

• Query-4: From the Client table: display the Names and the State of all clients from a state that the user enters. Hint:
• To write a SQL query, you have to switch the view to "SQL View" by clicking the top-left "View" button.
• To match a parameter, say we want the "State"  value to hold a value entered by the user. For this, you'll have a query like this: SELECT <the columns you want> FROM <table> WHERE <table>.State = [Enter state];
• The values given within < >, should be replaced by appropriate values without the angle brackets.

• Query-5: From the Condo Unit table: display Weekly Rate and number of bathrooms of all units with rates greater than or equal to $800 and less than $1000. Hint: Split it up and use "AND". 

• Query-6: From the Condo Unit table: display Unit Number, Bedrooms, Rented & Next Available (this is a column) of all 2 bedroom units which are rented (Yes) and also display Unit Number, Bedrooms, Rented & Next Available of  three bedroom units available after January 1st 2015. Hint: Split it up. Query should be like this: (2 bedrooms and rented) or (3 bedrooms, you don't have to check for rentals). Dates can be compared as numbers and must have # symbol at the beginning and end like this #3/1/2015#.

• Query-7: From all three tables: display owner’s Last and First Name, Client Name and Current Due of all units that have current due greater than $300. Hint: This is similar to Query 1 in Lab 8 and Query 3 in Lab 9. 

• Query-8: From the Condo Unit table: display average weekly rates grouped by number of bedrooms. Hint: From Design on the ribbon click Totals(right-top) to display the Total option on the query. Split it up. Two things: Use AVG function to find average and GROUP BY.  

• Query-9: From the Condo Unit table: display Unit Number, Current Due and Amount Paid. Right-click the Field row in the next open column in the design grid to display a shortcut menu. Click "Zoom" on the shortcut menu to display the Zoom dialog box. Type Total Amount: [Amount Paid] + [Current Due] and click OK.