CS 2500 SQL Assignment Part 1 Plant Database

Create a query (SQL statement) for each of the following based on the tables (Plant, Skill, Mechanic, Qualifications) within the CS2500 database.

1. List all mechanic names and their salaries for all those that make over $60,000. Sort by salary in descending order.

2. List City, State, and Size for all cities that start with the letter M. Sort by city name.

3. List the plant number, city, and state for which YearOpenedis missing.

4. List all states and the number of plants in each state.

5. List the mechanic number and their average proficiency rating (ProfRate). Sort by mechanic number.

6. List any and all duplicate mechanic names and number of times the name is duplicated. Sort by mechanic name.

121 % Results 1 2 3 4 5 6 7 8 Plant Num CH01 CHO2 DA01 IN01 LA01 ME01 MIO1 MO01 MOO2 NY01 NYO2 ONO1 PS01 SA01 SD01 SF01 SJ01 Messages City Chicago Chicago Dallas Indianapolis Los Angeles Memphis Miami Modesto Monterey New York New York Ontario Palm Springs Sacramento San Diego San Francisco San Jose State Size Illinois 15 Illinois 50 Texas 60 Indiana 25 California 55 Tennessee 40 Florida Califomia Califomica 15 New York 45 New York 35 Califomia 45 Califomia 18 Califomia 55 California 40 Califomia Califomia 47 YearOpened 1925 1925 1950 NULL 1925 1932 1930 1938 1948 1940 1930 1946 1932 1940 1928 1930 1937 9 10 11 12 13 14 15 16 17 1062125 1 121 % E Results 1 2. 3 2 3 4 5 SkillNum 1837 2244 2695 2800 2854 2855 3721 4394 6238 8361 9237 Messages SkillName SkillCat Engine Replacement Engine Tire Change Exterior Fuel System Engine Electrical Instruments Instruments Cooling System Engine Computer Diagnosites Instruments Fan Blade Replacement Engine Air Conditioning Engine Body Repair Exterior Seat Repair Interior Glass Repair Exterior 6 7 8 9 10 11 From Mechanic 02.125 (SQL Server 15.0.2000.5 - Jatabases System Databases Database Snapshots Adam DB Adrian DB Austin DB Bandar_DB Bookstore Brad DB Brendan DB CS2500 Daniel DB Fahad DB Feras_DB Grant DB Hussain_DB Database Diagrams Tables Views External Resources Synonyms Programmability Service Broker Storage Security Isaac DB Jamir_DB Jarvis_DB Jay DB John DB Jordan DB Kristin DB Mason DB Mauricio DB Osman_DB Owen DB Rashed DB Riley DB Russell DB Saleh DB Samuel_OB Tarek DB Taylor DB Turki DB Tyler DB Zachary DB Eurity 121% Results Messages MechNum MechName Salary Plant Num 1 3817 Rose 71900.00 MO01 2 4187 Singh 52900.00 SF01 3 11299 Young 42900.00 DA01 4 13829 Stevens 68200.00 LA01 5 15289 Baker 42800.00 MO01 6 28392 Smith 65000.00 SF01 7 29246 Smith 50000.00 LA01 8 29517 Norton 47300.00 LA01 9 31387 Baker 61800.00 SJO1 10 32781 Perez 52800.00 SF01 11 38110 Adams 63500.00 PS01 12 39481 Vincent 52800.00 SJO1 13 41287 Kirby 52000.00 ON01 14 42728 Johnson 52900.00 ON01 15 44100 Walters 62900 00 16 49128 Taylor 58300.00 PS01 17 52822 Caldwell 51500.00 MO02 18 62928 Martin 47300.00 CHO2 19 62999 Lorimer 42900.00 SA01 20 67183 Schwartz 62700.00 NY01 21 71919 Gonzalez 62900.00 SD01 22 77289 Edwards 49500.00 ME01 23 83710 Willars 67000.00 SA01 24 84728 Jackson 45000.00 SD01 25 90274 Cheri 5200000 NYD2 26 91838 Vega 47200 00 MOO2 27 92729 Martin 58200.00 LAD1 28 92731 Garner 48500.00 DA01 MIO1 Query executed successfully Select YO L Server 15.0.2000.5 - A From Qualification Databases se Snapshots DB DB DB ore 3 DB DB B B _DB abase Diagrams 3721 JS mal Resources Snyms grammability ice Broker age unity B B B Prof Rate 6 7 7 2 2 4 3 5 4 6 4 2 121 % Results Messages MechNum SkilNum 1 3817 1837 2 3817 3 3817 4394 4 15289 2854 5 15289 3721 6 28392 2855 7 29246 3721 8 29517 2695 9 31387 3721 10 32781 3721 11 38110 3721 12 39481 3721 13 39481 9237 14 41287 2800 15 42728 2855 16 42728 8361 17 44100 3721 18 44100 19 52822 1837 20 62928 3721 21 62999 2244 22 67183 3721 23 3721 24 90274 6238 25 91838 3721 26 92729 2800 27 92729 3721 28 92731 2854 4 9237 DB DB DB oDB DB DB B DB B DB B DB B 3 DB on on N. 77289 4 5