Question

1 Approved Answer

Posted on Oct 06, 2024

04) (15pts) Show clients that have 3 or more properties for rent. Order by the number of properties for rent in descending order. first name

04) (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 Evelia Deon Elmer Reggio | Coffin 1 Hetherington Patty Bridgett | Hodak Sartor DOWS 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 (.e. client_no cannot be mull). Your query should also show by how much the rent is higher for each property. Order by the rent difference property_no i prop_type staff_noi client_no1 rooms state difference PR512 PR232 PR875 PR127 1PR655 I PRI31 PR810 PR343 PR431 | Flat Condo Condo Flat | House Flat Flat Flat House SG530 SL557 180537 NULL 1 SG508 NULL SQ574 NULL | 50551 | CR228 | CR191 | CR147 | CR14 I CR195 CR244 | CR170 I CR222 | CR183 15 5 12 B Oklahoma | Georgia I North Carolina 1 | Michigan Alaska | Florida Minnesota Maryland 1 Michigan 1.2600 19.2600 59.2600 85.2600 150.2600 173.2600 228.2600 255.2600 390.2600 41 cows in set 06) (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 prop type rooms PR200 I PR901 I PR173 | PR838 PR797 PR169 IPR575 I PR720 | PR656 1 PR254 | PR854 | PRES9 | Flat | Flat | House | Condo | Condo House Flat Flat I Flat Flat | Condo House rent Property State I star! no branchno | Branch State 24 Nevada 90523 B623 Pennsylvania 31 | Vermont | SL569 | 3647 Florida 59 Wyoming | SG570 B911 1. Colorado 72 | Minnesota SQ541 IB745 | New Mexico 79 Wyoming 1 SQ536 13623 Pennsylvania 88 Wyoming 190581 I 3714 | North Dakota 155 | Massachusetts S6535 | Louisiana 161 | West Virginia S0574 B647 | Florida 174 North Dakota ISL584 Kentucky 236 | Iowa | S0504 | B960 Rhode Island 265 Arizona SGS16 3190 Vermont 296 Rhode Island SG527 B647 Florida 1 L 114 FOWS staff staff_n0 VARCHAR(5) person_id INT position VARCHAR(45) salary INT branch_no VARCHAR(5) Todexes PRIMARY FK_STAFF_PERSON_IDX PERSON_ID_UNIQUE Ik staff branch1_idx branch branch_SO VARCHAR(5) address Id INT #Indexes PRIMARY FK_BRANCH_ADDRESS_IDX ADDRESS_ID_UNIQUE property_for_rent property. 10 VARCHAR(5) address_id INT prop_type VARCHAR(45) rooms VARCHAR(45) vent INT staff no VARCHAR(5) client_no VARCHAR(5) branch NO VARCHAR(5) Indexes PRIMARY IFK_PROPERTY_FOR_RENT_ADORESS_IDX FK_PROPERTY_FOR_RENT_STAFF_JDX FK_PROPERTY_FOR_RENT_CLIENT_IDX fk property_for_rent_branchi_de address address_id INT #street1 VARCHAR(45) street2 VARCHAR(45) city VARCHAR(45) zip VARCHAR(S) state_dINT Oh Indexes PRIMARY FK ADDRESS_STATE_JDX state state_id INT name VARCHAR(45) sbb VARCHAR(2) Todex PRIMARY abb_UNIQUE H person person I INT first name VARCHAR(45) fastame VARCHAR(45) sex VARCHAR(1) dob DATETIME address Id INT client client_no VARCHAR(S) personld INT telephone VARCHAR(13) emal VARCHAR(45) pref_type VARCHAR(16) maxrent INT Interes PRIMARY PK.CLIENT_PERSON JDK PRIMARY FK PERSON ADDRESS_IOX Figure 1: Database Schema MariaDB (assignment1]> select * from property_for_rent; I property_no | address_id property_type rooms | rent | staff_no | client_no | branch_no | PR123 PR127 PR131 PR134 PR138 PR140 PR146 PR153 PR157 PR161 PR162 PR166 PR169 PR171 PR173 PR185 PR188 PR189 PR194 PR200 PR202 PR284 PR206 PR207 PR210 PR214 PR217 PR227 PR232 PR248 PR249 PR254 PR265 PR268 PR282 PR298 181 House 299 Flat 167 Flat 277 | House 123 Flat 219 | House 135 Condo 165 | Condo 293 Condo 109 | Condo 155 | Flat 255 Flat 111 | House 137 Flat 109 | House 247 | Flat 137 | Condo 115 Flat 185 Flat 131 Flat 223 House 261 | Flat 127 House 181 House 229 Condo 105 Condo 215 House 275 House 239 Condo 177 Condo 215 Condo 225 Flat 259 House 137 Condo 101 House 213 House 1 5 8 | 3 | 5 | 5 | 3 8 4 4 6 3 | 5 | 4 6 7 2 2 1 3 1 1985 SG507 1588 | NULL 1676 | NULL 303 | SG588 413 | SQ544 2017 | NULL 3646 NULL 497 | SQ506 549 | SG560 477 | SQ566 4390 | SG555 1282 SQ547 88 | SQ581 382 | SL580 59 SG570 3613 | SG502 317 | SG516 3992 | NULL 946 | SG530 24 SQ523 2976 SG531 2660 SQ547 1459 SL545 3945 NULL 1043 SG593 399 SL590 533 SL569 1440 SL576 1522 SL557 941 SG508 3511 SL586 236 SQ504 555 | SG529 3608 SQ564 473 SQ573 586 SQ517 CR234 CR148 CR244 CR167 CR218 NULL NULL CR127 CR224 CR127 CR125 CR209 CR117 CR227 CR166 NULL NULL CR193 CR190 CR191 CR154 CR148 CR200 CR246 CR104 CR209 CR167 CR145 CR191 CR137 CR114 NULL CR246 NULL NULL | CR105 NULL NULL B714 B443 B947 NULL B424 NULL NULL B942 NULL B731 B911 B724 B632 B947 B443 B164 NULL B190 NULL B187 B187 B901 B901 NULL B960 B443 B911 B947 NULL B731 B623 B942 NULL | NULL 4 6 8 1 3 8 4 1 8 1 5 | 2 | 3 | 7 MariaDB (assignment1]> select * from client; | pref_type | max_rent | | client_no person_id | telephone | email CR100 766 500-484-9332 | Pearlie_Forcier@gmail.com CR101 1102 760-750-7498 | Gracia.Ozment@gmail.com CR102 577 | null | Tristan_Iglesia@yahoo.com CR103 1459 | 527-281-2067 | Chana_Ortman@gmail.com CR104 667 | 387-875-6915 | Johana_Ping@yahoo.com CR105 724 | 758-181-6632 Kip_Ausherman@gmail.com CR106 1105 null Benny_Livermore@aol.com CR107 1045 656-608-4483 Cornell. Aguinaldo@aol.com CR128 1576 null Romona_Mingus@icloud.com CR109 667 775-975-2854 Johana_Ping@gmx.com CR110 592 423-358-5041 Abdul.Nimmo@msn.com CR111 1546 447-947-8831 Morris. Ludwig@gmx.com CR112 1240 422-859-8522 Clara.Klawitter@aol.com CR113 1186 666-971-5690 Donovan_Roush@msn.com CR114 973 207-496-1315 Shara.Demario@icloud.com CR115 1306 630-861-6314 Rayford. Macky@icloud.com CR116 1399 207-584-8526 Jana_Jamieson@aol.com CR117 772 427-455-3697 | Alaina_Kenney@gmx.com CR118 1048 | 121-890-5911 | Ashlee. Days@msn.com CR119 793 | 720-371-1265 Heidi_coffin@yahoo.com CR120 1309 | 760-412-2957 Dino. Slovinsky@yahoo.com CR121 709 226-185-9326 Monica. Mailman@outlook.com CR122 784 767-342-5334 Jay_Hambleton@zoho.com CR123 688 null Mckinley_Pigott@zoho.com CR124 820 683-164-4775 Perla.Greaves@gmx.com CR125 577 374-655-9483 Tristan. Iglesia@outlook.com CR126 1078 | null Meaghan_Wimbishamsn.com CR127 898 | 529-406-5446 Nicolas. Hetherington@aol.com CR128 1474 | 186-858-9746 Adalberto. Quiroz@msn.com CR129 688 700-655-7876 | Mckinley_Pigott@outlook.com CR130 925 548-716-5083 Simonne_Kincade@icloud.com CR131 847 | null Sha_Kirkenazoho.com CR132 1024 | 218-188-3348 Lauren. Samms@gmail.com CR133 1039 null Maggie_Stclaire@outlook.com CR134 1582 | 303-956-7559 Katerine. Jepsen@aol.com | CR135 853 | 741-274-6406 Corrina.Mullett@zoho.com House House Flat Apartment | Condo | Condo House House | Condo House House | Condo Apartment Apartment House Condo Apartment House House Apartment Apartment | Condo Condo House | Condo Apartment Flat Apartment Condo | Flat | House Apartment Condo House House | Flat 1063 1038 1929 1032 1102 1341 1557 1633 1296 1696 1510 1250 1593 1172 1355 1316 1678 1315 1754 1235 1344 1328 1185 1088 1016 1547 1484 1656 1834 1983 1557 1575 1659 1544 1193 1400 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 04) (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 Evelia Deon Elmer Reggio | Coffin 1 Hetherington Patty Bridgett | Hodak Sartor DOWS 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 (.e. client_no cannot be mull). Your query should also show by how much the rent is higher for each property. Order by the rent difference property_no i prop_type staff_noi client_no1 rooms state difference PR512 PR232 PR875 PR127 1PR655 I PRI31 PR810 PR343 PR431 | Flat Condo Condo Flat | House Flat Flat Flat House SG530 SL557 180537 NULL 1 SG508 NULL SQ574 NULL | 50551 | CR228 | CR191 | CR147 | CR14 I CR195 CR244 | CR170 I CR222 | CR183 15 5 12 B Oklahoma | Georgia I North Carolina 1 | Michigan Alaska | Florida Minnesota Maryland 1 Michigan 1.2600 19.2600 59.2600 85.2600 150.2600 173.2600 228.2600 255.2600 390.2600 41 cows in set 06) (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 prop type rooms PR200 I PR901 I PR173 | PR838 PR797 PR169 IPR575 I PR720 | PR656 1 PR254 | PR854 | PRES9 | Flat | Flat | House | Condo | Condo House Flat Flat I Flat Flat | Condo House rent Property State I star! no branchno | Branch State 24 Nevada 90523 B623 Pennsylvania 31 | Vermont | SL569 | 3647 Florida 59 Wyoming | SG570 B911 1. Colorado 72 | Minnesota SQ541 IB745 | New Mexico 79 Wyoming 1 SQ536 13623 Pennsylvania 88 Wyoming 190581 I 3714 | North Dakota 155 | Massachusetts S6535 | Louisiana 161 | West Virginia S0574 B647 | Florida 174 North Dakota ISL584 Kentucky 236 | Iowa | S0504 | B960 Rhode Island 265 Arizona SGS16 3190 Vermont 296 Rhode Island SG527 B647 Florida 1 L 114 FOWS staff staff_n0 VARCHAR(5) person_id INT position VARCHAR(45) salary INT branch_no VARCHAR(5) Todexes PRIMARY FK_STAFF_PERSON_IDX PERSON_ID_UNIQUE Ik staff branch1_idx branch branch_SO VARCHAR(5) address Id INT #Indexes PRIMARY FK_BRANCH_ADDRESS_IDX ADDRESS_ID_UNIQUE property_for_rent property. 10 VARCHAR(5) address_id INT prop_type VARCHAR(45) rooms VARCHAR(45) vent INT staff no VARCHAR(5) client_no VARCHAR(5) branch NO VARCHAR(5) Indexes PRIMARY IFK_PROPERTY_FOR_RENT_ADORESS_IDX FK_PROPERTY_FOR_RENT_STAFF_JDX FK_PROPERTY_FOR_RENT_CLIENT_IDX fk property_for_rent_branchi_de address address_id INT #street1 VARCHAR(45) street2 VARCHAR(45) city VARCHAR(45) zip VARCHAR(S) state_dINT Oh Indexes PRIMARY FK ADDRESS_STATE_JDX state state_id INT name VARCHAR(45) sbb VARCHAR(2) Todex PRIMARY abb_UNIQUE H person person I INT first name VARCHAR(45) fastame VARCHAR(45) sex VARCHAR(1) dob DATETIME address Id INT client client_no VARCHAR(S) personld INT telephone VARCHAR(13) emal VARCHAR(45) pref_type VARCHAR(16) maxrent INT Interes PRIMARY PK.CLIENT_PERSON JDK PRIMARY FK PERSON ADDRESS_IOX Figure 1: Database Schema MariaDB (assignment1]> select * from property_for_rent; I property_no | address_id property_type rooms | rent | staff_no | client_no | branch_no | PR123 PR127 PR131 PR134 PR138 PR140 PR146 PR153 PR157 PR161 PR162 PR166 PR169 PR171 PR173 PR185 PR188 PR189 PR194 PR200 PR202 PR284 PR206 PR207 PR210 PR214 PR217 PR227 PR232 PR248 PR249 PR254 PR265 PR268 PR282 PR298 181 House 299 Flat 167 Flat 277 | House 123 Flat 219 | House 135 Condo 165 | Condo 293 Condo 109 | Condo 155 | Flat 255 Flat 111 | House 137 Flat 109 | House 247 | Flat 137 | Condo 115 Flat 185 Flat 131 Flat 223 House 261 | Flat 127 House 181 House 229 Condo 105 Condo 215 House 275 House 239 Condo 177 Condo 215 Condo 225 Flat 259 House 137 Condo 101 House 213 House 1 5 8 | 3 | 5 | 5 | 3 8 4 4 6 3 | 5 | 4 6 7 2 2 1 3 1 1985 SG507 1588 | NULL 1676 | NULL 303 | SG588 413 | SQ544 2017 | NULL 3646 NULL 497 | SQ506 549 | SG560 477 | SQ566 4390 | SG555 1282 SQ547 88 | SQ581 382 | SL580 59 SG570 3613 | SG502 317 | SG516 3992 | NULL 946 | SG530 24 SQ523 2976 SG531 2660 SQ547 1459 SL545 3945 NULL 1043 SG593 399 SL590 533 SL569 1440 SL576 1522 SL557 941 SG508 3511 SL586 236 SQ504 555 | SG529 3608 SQ564 473 SQ573 586 SQ517 CR234 CR148 CR244 CR167 CR218 NULL NULL CR127 CR224 CR127 CR125 CR209 CR117 CR227 CR166 NULL NULL CR193 CR190 CR191 CR154 CR148 CR200 CR246 CR104 CR209 CR167 CR145 CR191 CR137 CR114 NULL CR246 NULL NULL | CR105 NULL NULL B714 B443 B947 NULL B424 NULL NULL B942 NULL B731 B911 B724 B632 B947 B443 B164 NULL B190 NULL B187 B187 B901 B901 NULL B960 B443 B911 B947 NULL B731 B623 B942 NULL | NULL 4 6 8 1 3 8 4 1 8 1 5 | 2 | 3 | 7 MariaDB (assignment1]> select * from client; | pref_type | max_rent | | client_no person_id | telephone | email CR100 766 500-484-9332 | Pearlie_Forcier@gmail.com CR101 1102 760-750-7498 | Gracia.Ozment@gmail.com CR102 577 | null | Tristan_Iglesia@yahoo.com CR103 1459 | 527-281-2067 | Chana_Ortman@gmail.com CR104 667 | 387-875-6915 | Johana_Ping@yahoo.com CR105 724 | 758-181-6632 Kip_Ausherman@gmail.com CR106 1105 null Benny_Livermore@aol.com CR107 1045 656-608-4483 Cornell. Aguinaldo@aol.com CR128 1576 null Romona_Mingus@icloud.com CR109 667 775-975-2854 Johana_Ping@gmx.com CR110 592 423-358-5041 Abdul.Nimmo@msn.com CR111 1546 447-947-8831 Morris. Ludwig@gmx.com CR112 1240 422-859-8522 Clara.Klawitter@aol.com CR113 1186 666-971-5690 Donovan_Roush@msn.com CR114 973 207-496-1315 Shara.Demario@icloud.com CR115 1306 630-861-6314 Rayford. Macky@icloud.com CR116 1399 207-584-8526 Jana_Jamieson@aol.com CR117 772 427-455-3697 | Alaina_Kenney@gmx.com CR118 1048 | 121-890-5911 | Ashlee. Days@msn.com CR119 793 | 720-371-1265 Heidi_coffin@yahoo.com CR120 1309 | 760-412-2957 Dino. Slovinsky@yahoo.com CR121 709 226-185-9326 Monica. Mailman@outlook.com CR122 784 767-342-5334 Jay_Hambleton@zoho.com CR123 688 null Mckinley_Pigott@zoho.com CR124 820 683-164-4775 Perla.Greaves@gmx.com CR125 577 374-655-9483 Tristan. Iglesia@outlook.com CR126 1078 | null Meaghan_Wimbishamsn.com CR127 898 | 529-406-5446 Nicolas. Hetherington@aol.com CR128 1474 | 186-858-9746 Adalberto. Quiroz@msn.com CR129 688 700-655-7876 | Mckinley_Pigott@outlook.com CR130 925 548-716-5083 Simonne_Kincade@icloud.com CR131 847 | null Sha_Kirkenazoho.com CR132 1024 | 218-188-3348 Lauren. Samms@gmail.com CR133 1039 null Maggie_Stclaire@outlook.com CR134 1582 | 303-956-7559 Katerine. Jepsen@aol.com | CR135 853 | 741-274-6406 Corrina.Mullett@zoho.com House House Flat Apartment | Condo | Condo House House | Condo House House | Condo Apartment Apartment House Condo Apartment House House Apartment Apartment | Condo Condo House | Condo Apartment Flat Apartment Condo | Flat | House Apartment Condo House House | Flat 1063 1038 1929 1032 1102 1341 1557 1633 1296 1696 1510 1250 1593 1172 1355 1316 1678 1315 1754 1235 1344 1328 1185 1088 1016 1547 1484 1656 1834 1983 1557 1575 1659 1544 1193 1400 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