Questions: (1) (10pts) Code conforms to the SQL standards and has comments before each query to indicate it's puropose. 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: 1. (7pts) Use the OR operator for the state predicates. IL (7pts) Use IN and make sure you do not use OR or AND. III. (pt) Use UNION and make sure you do not use WHILE I first_namel last_namo | sex i dob 1.streeti street2 city | Zack Thanh Randy | | Kelsie Skoglund IM | Soderstrom IM | Marlow IM Valenzula I Hickey Wardle Vankirk Vivanco IM Bryan | 1966-09-16 00:00:00 590 Broadway Av I 1979-01-02 00:00:00 | 590 Broadway Av | 1986-03-10 00:00:00 590 Broadway Av 1 1932-02-11 00:00:00 1 590 Broadway Av | | 1914-10-28 00:00:00 590 Broadway Av | 1905-10-20 00:00:00 | 135 Broadway in | 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 in | city1o New York | citylo New York I citylo | New York I city10 New York | City10 | New York | city17 | New Jersey | city10 New York | city10 | New York | city17 | New Jersey | Jasper | Essie Emory Delorse 9 rows in set (0.001 sec) staff staff_nO VARCHAR(5) persond INT position VARCHAR(45) salary INT branch_SO VARCHAR(5) Trudexes PRIMARY FK STAFF PERSON IDX PERSONID_UNIQUE Ik staff branch.dk property_for_rent property_NO VARCHAR(5) address_Id INT prop_type VARCHAR(45) rooms VARCHAR(45) rent INT staff_no VARCHAR(5) client_no VARCHAR(5) brancho VARCHAR(S) branch branch_SO VARCHAR(5) address d INT - Indee PRIMARY R_FRANCHADORESS_JOX ADDRESS_ID_UNIQUE PRIMARY IFK PROPERTY FOR RENT ADDRESS_ID FK PROPERTY FOR RENT STAFFJDX FK PROPERTY FOR RENT_CLIENT_IDX tk.property for rent branch state state_ INT VARCHAR(45) sbb VARCHAR(2) address address_id INT street VARCHAR(45) street2 VAROW(45) city VARCHAR(45) Z VARCHAR(5) stated INT Indene PRIMARY FK ADORESS_STATE_DX PRIMARY abb_UNIQUE person person I INT first rame VARCHAR(45) lastame VARCHAR(45) Sex VARCHAR(1) dob DATETIME address INT Indexes PRIMARY FK PERSON_ADDRESS_IDX client client_n0 VARCHAR(5) person. Jd INT telephone VARCHAR(13) emall VARCHAR(45) pref_type VARCHAR(16) max_cent INT PRIMARY FK_CLIENT_PERSON_IDX Figure 1: Database Schema MariaDB (assignment1]> select * from state; state_id name DE at 3. 4. NO 100 | Alabama AL 102 Alaska AK 104 Arizona AZ 106 Arkansas AR 108 California CA 110 Colorado CO 112 Connecticut | CT 114 Delaware 116 | Florida | FL 118 | Georgia | GA 120 Hawaii | HI 122 | Idaho ID 124 | Illinois | IL 126 Indiana IN 128 Iowa | IA 130 Kansas | KS 132 Kentucky | KY 134 Louisiana | LA 136 Maine ME 138 Maryland MD 140 Massachusetts | MA 142 Michigan MI 144 Minnesota MN 146 Mississippi | MS 148 Missouri MO 150 Montana MT 152 Nebraska NE 154 Nevada NV 156 New Hampshire NH 158 New Jersey NJ 160 New Mexico | NM 162 New York NY Oh MariaDB (assignment1]> select * from address; | address_id | street1 | street2 | city | zip | state_id Apt Apt G 101 952 Broadway Ct 103 | 459 Broadway Ct 105 | 266 Market AV 107 505 Fayetteville Ct 109 882 Rodeo Ln 111 270 Freedom Av 113 778 Sunset Rd 115 372 Lombard St 117 797 Freedom in 119 400 Freedom Rd 121 913 Fayetteville Ct 123 | 475 Rodeo Ln 125 137 Staunton St 127 189 Nantucket In 129 | 376 Freedom Av 131 967 Main Rd 133 521 Nantucket ct 135 | 254 Freedom Rd 137 354 Nantucket Ct 139628 Nantucket St 141 348 Lombard in 143 903 Market Ln 145 828 Fayetteville Av 147 869 Fayetteville Rd 149 953 Freedom St 151 | 864 Market St 153 471 Lombard Ct 155 602 Freedom Rd 157 921 Broadway AV 159 121 Main AV 161 | 917 Market Ct City13 | 74126 Cityle | 76847 | City18 33877 City17 | 97329 City2e 13625 City11 | 39409 City16 | 85366 City14 60763 City11 26125 City11 | 78161 City16 16479 City13 | 5e878 City18 | 34228 City17 69730 City18 | 19853 City13 48458 | City14 | 87262 City18 51423 City13 | 22427 City1564574 City11 | 70271 | City18 | 44259 City2e | 52522 City16 88466 | City14 | 61222 City19 90283 City11 52318 City17 | 23733 City13 61131 | City16 | 42132 | Cityne | 45473 140 132 124 124 198 198 116 140 174 178 140 152 166 196 140 154 102 1901 1ee 184 190 188 146 1ee 130 188 140 104 164 | 138 | 126 | Apt T Apt P Apt T ch ol Apt A Apt Z MariaDB (assignment1]> select * from branch; +- | branch_no | address_id | B424 B947 B942 B714 B901 B724 B936 B443 B164 B647 B623 B911 B632 B745 B266 B553 B960 B731 B190 B187 103 115 119 125 131 137 157 163 165 167 183 185 187 199 209 241 247 267 269 279 20 rows in set (0.001 sec) MariaDB (assignment1]> select * from person; I person_id | first_name last_name | sex | dob | address_id | | M M F F F M M | | F M F M IF 400 Bruno Rapozo 403 Mariano Caffey 406 Cherryl Klumpp 409 Jennie Hinesley 412 Vallie Snedden 415 Myron Hone 418 Ben Haygood 421 Houston Dimaggio 424 | Codi | Coles 427 | Natasha Douthitt 430 | Ivory Pittenger 433 Reinaldo Quintero 436 Dreama Zucker 439 Jonas Mangini 442 | Shaquana Schissler 445 Xenia Pasko 448 Lionel Stone 451 | Erin Sam 454 Latricia Poblete 457 Melissa Rhein 460 Leigh Donis 463 Nicky Oberle 466 Enrique Naranjo 469 Karina Cantin 472 Jerry Burchett 475 Drusilla Canale 478 Nadine Brumback 481 Mollie Marcoux 484 Monroe Hovis 487 | Carrol Paisley 490 Deangelo Blade 493 | Jacquline Bostic 1939-02-23 00:00:00 1926-12-09 00:00:00 1993-12-01 00:00:00 1984-10-06 00:00:00 1913-05-01 00:00:00 NULL 1983-10-30 00:00:00 1951-11-17 00:00:00 | 1974-03-05 00:00:00 NULL 1956-09-21 ee:ee:00 NULL NULL NULL 1900-11-13 00:00:00 1963-02-06 00:00:00 1990-10-02 00:00:00 1925-05-12 00:00:00 1930-07-18 ee:e0:00 NULL 1984-12-12 00:00:00 NULL 1985-08-20 00:00:00 1986-07-01 00:00:00 NULL 1931-01-18 00:00:00 NULL NULL NULL 1933-01-22 00:00:00 1982-10-11 00:00:00 1965-04-15 00:00:00 | 287 287 NULL 251 217 NULL NULL 293 255 NULL 271 257 NULL NULL 213 111 247 167 163 NULL NULL 267 269 231 233 131 299 253 109 189 NULL NULL | M M M M M M F F M MariaDB (assignment1]> select * from staff; | staff_no 1 person_id | position | salary | branch_no | SG501 1576 Supervisor 320000 B714 SG502 1411 Manager 180000 B187 SG505 1045 | Teller 80000 | B266 SG507 1231 Assistant 30000 B731 SG508 1531 Teller 80000 3647 SG512 1312 Teller 80000 B553 SG514 949 Assistant 3eeee B745 SG515 808 Manager 210000 B443 SG516 1102 Manager 18eeee B190 SG525 1252 | Teller 100000B266 SG527 910 Supervisor | 360000 B647 SG528 901 Manager 21eeee B424 SG529 1258 | Teller 1eeeee B164 SG530 442 Assistant 2eeee B553 SG531 1543 Assistant 30000 B623 SG535 1075 | Teller 8eeee B632 SG540 859 | Manager 210000 B745 SG542 1291 Supervisor | 36000e | B632 SG543 1417 Teller 100000 B731 SG550 913 Assistant 30000 | B724 SG555 997 Supervisor 360000 | B647 SG558 616 | Supervisor 36eeee B960 SG560 850 Supervisor 320000 | B960 SG562 1582 Assistant 2eeee | B164 SG570 421 Manager 21eeee B911 SG571 1165 Supervisor 32eeee B623 SG572 925 Assistant 20000 | B623 SG577 1435 Assistant 3eeee B942 SG578 1402 Teller 100000 B553 SG585 403 Assistant 30000 B424 SG587 1159 Assistant 20000 | B942 SG588 940 | Teller | 80000 | B424