Note: The database relational schema for this assignment is shown in Figure 1. Study the tables and their relations carefully before attempting to solve the questions below. The source code for most of the tables is provided in the file A1_Tables.sql. Test data is provided in the file A1_Data.sql. To get started, create the test database as follows: 1. Login into MariaDB/MySQL using an authorized account (e.g.root). 2. Create a new database (e.g, cis495). If the database already exists, drop it first. 3. Copy the contents of the file Al Tables.sq/ and paste them into your SQL command line window. 4. Copy the contents of the file Al_Data.sq/' and paste them into your SQL Command line window. I had an issue with direct copy-paste of the contents of A1_Data.sql. Alternatively, I used the following SQL commands: source source Change the correct path on your OS The test data for the last two tables is commented out. Once you have completed Q1 below, uncomment these lines and paste them into your SQL command line window. Questions: QI) (10pts) Code conforms to the SQL standards and has comments before each query to indicate it's purpose. Q2) (20pts) Write the SQL statements to create the tables client and property for rent. Once done, you can load the last portion of the test data in A1_Data.Sql Q3) (20pts) Show all persons who live in New York or New Jersesy. Order by first name (descending) then by state name (ascending). Write the query using three different approaches as follows: (7pts) Use the OR operator for the state predicates, II. (7pts) Use IN and make sure you do not use OR or AND. (6pts) Use UNION and make sure you do not use WHILE. 1. | first_name | last_name | sex | dob | streeti | street 2 | city | name | Zack Thanh | Randy | Kelsie | Kasi | Jasper Essie | Emory | Delorse | Skoglund IM | Soderstrom | M | Marlow IM | Valenzula IF | Hickey IF | Wardle IM | Vankirk IF | Vivanco IM | Bryan | 1966-09-16 00:00:00 | 590 Broadway Av | 1979-01-02 00:00:00 | 590 Broadway Av| | 1986-03-10 00:00:00 | 590 Broadway Av | | 1932-02-11 00:00:00 | 590 Broadway Av | | 1914-10-28 00:00:00 | 590 Broadway Av | 1905-10-20 00:00:00 | 135 Broadway Ln | | 1982-01-18 00:00:00 | 590 Broadway Av | | 1966-01-07 00:00:00 | 590 Broadway Av | 1998-11-04 00:00:00 | 135 Broadway Ln | Citylo | New York | city10 l New York | Citylo i New York | city10 | New York | City10 | New York | cityi? | New Jersey | City10 | New York | Citylo | New York | city17 | New Jersey I 9 rows in set (0.001 sec) Q4) (15pts) Show clients that have 3 or more properties for rent. Order by the number of properties for rent in descending order. first_name | last_name | client_properties | Heidi | Nicolas | Evella | Deon | Elmer | Reggie | Coffin | Hetherington | Patty | Bridgett Hodak | Sartor 6 rows in set 05) (20pts) Show the properties for rent if that property's rent is (1) larger than the average rent for all other properties combined. (2) Limit the result to properties that have a client number (i.e. client_no cannot be null). Your query should also show by how much the rent is higher for each property. Order by the rent difference property_no | prop_type staff_noi client_no rooms state difference | PR512 | PR232 | PRB75 | PR127 | PR 655 | PR131 | PRBID | PR343 | PR431 I | Flat | Condo | Condo | Flat | House | Flat | Flat | Flat | House | SG530 | SL557 | SQ537 | NULL | SG5DB | NULL | SQ574 | NULL | 50551 | 5 15 | 2 | CR22B | CR191 | CR147 | CR14B | CR1 95 | CR244 | CR170 | CR222 | CR1B3 T | Oklahoma | Georgia | North Carolina | | Michigan | Alaska | Florida | Minnesota | Maryland I | Michigan I 1.2600L 19.2600 59.2600 65.2 600 150.260DL 173.2600 228.2600 255.2600L 390.2600 Ti 41 rows in set Q6) (15pts) Show properties for sale, the property's state, the property's branch, and the state of that branch. Order by the rent from highest to lowest. Note that the branch and property states may be different addresses. To distinguish between the addresses, the query must use two independent copies of the address and state tables. The following query uses myTable twice as two separate copies. SELECT FROM myTable t1, myTable t2.... WHERE ... Bonus Question: (5pts) if you wrote Q6 using JOIN, rewrite using WHERE. If you used WHERE, rewrite using JOIN. | property_no i prop_type | rooms | rent | Property State | staff_no | branch_no | Branch State I | PR200 | PR 901 | PR173 | PR838 | PR797 | PR169 IPR575 | PR720 | PR656 I PR254 | PR854 | PRB59 I | Flat | Flat | House | Condo 1 Condo | House | Flat | Flat | Flat | Flat | Condo | House 1 24 | Nevada 31 | Vermont 59 | Wyoming 72 | Minnesota 79 | Wyoming BB | Wyoming 155 | Massachusetts 161 | West Virginia 174 | North Dakota 236 Iowa 265 | Arizona 296 | Rhode Island | SQ523 | SL569 | 56570 | 50541 | SQ5 36 | SQ5B1 | 56535 | 905 74 | SL5B4 | 90504 | 33516 | SG527 | B623 | 3647 | B911 | B745 | B623 | 3714 13632 | 3647 | B424 | B960 IB190 | B647 | Pennsylvania | Florida | Colorado | New Mexico | Pennsylvania | North Dakota | Louisiana | Florida | Kentucky | Rhode Island | Vermont | Florida 114 TONS Figures: staff staff_no VARCHAR(5) person_id INT position VARCHAR(45) salary INT brancho VARCHAR(5) Indexes PRIMARY FK_STAFF_PERSON_IDX PERSON_ID_UNIQUE Ik_staff_branchi_idx branch brancho VARCHAR(5) address_id INT Indexes PRIMARY FK_BRANCH ADDRESS IDX ADDRESS_ID_UNIQUE property_for_rent property.J VARCHAR(5) address_id INT prop_type VARCHAR(45) rooms VARCHAR(45) rent INT staff_no VARCHAR(5) client no VARCHAR(5) branch no VARCHAR(5) Indexes PRIMARY IFK_PROPERTY_FOR_RENT_ADDRESS_IDX FK_PROPERTY_FOR_RENT_STAFF_IDX FK_PROPERTY_FOR_RENT_CLIENT_IDX fk_property_for_Jent_branchi_idx state state_id INT name VARCHAR(45) abb VARCHAR(2) H Indexes PRIMARY abb_UNIQUE address address_id INT street1 VARCHAR(45) street2 VARCHAR(45) city VARCHAR(45) zip VARCHAR(5) state Id INT OH Indexes PRIMARY FK_ADDRESS_STATE_IDX person personId INT first_name VARCHAR(45) last_name VARCHAR(45) sex VARCHAR(1) dob DATETIME address Id INT Indexes PRIMARY FK_PERSON_ADDRESS_IDX client client_no VARCHAR(5) person_id INT telephone VARCHAR(13) email VARCHAR(45) pref_type VARCHAR(16) mak_rent INT Indexes PRIMARY FK_CLIENT_PERSON_IDX Figure 1: Database Schema