the query logic itself. Scenario: The same manufacturing company from Assignment 1, Teleworks, has created and starting using the database for recording call center information. Now that they have the database with information in it, they need some queries created that give specific information they need to analyze. Task: You have been hired to develop the queries that they need for their daily monitoring and operational needs. 1) Import the SQL script with the tables and data posted with the assignment to D2L into MySQL. Refer to Appendix A for import instructions. 2) Alter each table to have the name prefixed with your initials + your last name. Example: tjdixoncustomers To alter the table, right click on the table and then click alter table. Rename the table in the field that appears and then hit apply. After renaming the table, you need to refresh the schema view in the navigator to the left. Refer to Appendix B for details 3) Develop the following five queries. Be sure to keep a copy of the SQL logic and screenshots in the provided answer sheet on D2L. You can also save queries as .sql files to open later using the menu option File - > Save Script As. QUERY 1: List the number of closed issues for each consultant Include their consultant ID, first and last names, email addresses, and the number of their closed issues. Do not show the status ID or status description (if you're using any one of them for the criteria purpose). Have the output column labeled Number of Closed Issues. Order by the consultants with the most closed. ITM 209 Spring 2020 Assignment #2 Answer Sheet: Database Querying This sheet is for you to enter your results to the assignments. It should be submitted with the export of your database schema. It should include the following in the spaces below: Your queries copied from the MySQL view pasted into the answer sheet (MS Word) Screenshots of the query result when you open the query. Query 1: SQL Statement Used Query 1: Screenshot of the Query Results PROTECTED VIEW Be carefulfiles from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. G25 X fic i in inn mom B C consultantID firstName lastName email jobTitle businessPhone mobilePhone 4 Yoshio Long ylong@teleworks.com Software Engineer (643) 812-5568 (255) 547-3298 3 Dean Conner dconner@teleworks.com Senior Consultant (490) 177-7685 (419) 523-5750 10 Nicole Williams nwilliams@teleworks.com Hardware Engineer (879) 640-0050 (436) 6 Prescott Martin pmartin@teleworks.com Hardware Engineer (691) 215-6715 (546) 511-3490 11 Deborah Zimmerma dzimmerman@teleworks.com Hardware Engineer (930) 773-1006 (560) 595-1719 2 Claire Cameron ccameron@teleworks.com Senior Consultant (332) 237-8327 (637) 299-3143 1 Tyrone Banks tbanks@teleworks.com Senior Consultant (249) 578-8770 (640) 133-3203 8 Nell Simon nsimon@teleworks.com Software Engineer (831) 625-9022 (712) 608-9289 7 Hiram Paul hpaul@teleworks.com Junior Consultant (732) 297-4554 (745) 720-5002 5 Jameson Lyons jlyons@teleworks.com Junior Consultant (653) 746-9275 (835) 674-7730 9 Deacon Solis dsolis@teleworks.com Senior Consultant (836) 329-0317 (997) 266-1131 mom town t i ssueCategory consultants customers issues status priority eady I J K SNN a . 8 NMODENYNORSKANSIONS o N a 1 issueID issueDescription 1 Wifi not connecting. 2 Cannot download apps. 3 Alarm clock does not work 4 Ringer does not work. 5 Camera is not working. 6 One key is broken. 7 Screen-brightness not adjusting. 8 Cannot delete apps. 9 Locked out of account. 10 Wifi not connecting. 11 Wifi not connecting. 12 Can not access menu on phone. 13 Wifi not connecting. 14 Browser not loading. 15 Browser not loading. 16 Cannot delete apps. 17 Can not dial out of area. 18 Locked out of phone. 19 Alarm clock does not work 20 Browser not loading. 21 Cannot download apps. 22 Browser not loading. 23 Camera is not working. 24 Locked out of phone. 25 Cannot download apps. 26 Phone automatically turning off. 27 Can not access Web from phone. 28 Cannot access Web from phone. 29 Screen is frozen. 30 Can not access Web from phone. 31 Wifi not connecting. 32 Can not access menu on phone. 33 Ringer does not work. 34 Can not access Web from phone. 35 Phone will not turn on. 36 Can not access menu on phone. 37 Cannot delete apps. 38 Screen is frozen. 39 Locked out of phone. 40 Lost password. 41 Lost password. 42 Camera is not working. 43 Wifi not connecting. 44 No light 45 Screen is frozen. 46 Cannot delete apps. 47 Locked out of account. 48 Cannot download apps. 49 Locked out of account. 50 Screen is frozen. 51 Cannot download apps. 52 Cannot delete apps. 53 Screen-brightness not adjusting. 54 Screen-brightness not adjusting. 55 Phone will not turn on. 56 Cannot access voicemail. 57 Screen-brightness not adjusting. 58 Very bad 59 Can not access voicemail. 60 No light 61 Phone will not turn on. 62 Phone will not turn on. 63 Can not dial out of area. 64 Wifi not connecting 65 Camera is not working. 66 Locked out of phone. 67 Can not access menu on phone. 68 Screen-brightness not adjusting. C D E F G H dateOpened statuslDcategoryID priorityIDcustomerl.consultant ID 1/1/2019 1/3/2019 1/3/2019 1/6/2019 1/8/2019 1/8/2019 1/11/2019 1/12/2019 1/13/2019 1/15/2019 1/16/2019 1/17/2019 1/19/2019 1/21/2019 1/22/2019 1/24/2019 1/26/2019 1/26/2019 1/30/2019 1/31/2019 2/1/2019 2/3/2019 2/6/2019 2/7/2019 2/7/2019 2/7/2019 3 2/9/2019 2/9/2019 2/10/2019 2/11/2019 2/11/2019 2/13/2019 2/14/2019 2/18/2019 2/21/2019 2/21/2019 2/22/2019 2/24/2019 2/26/2019 2/28/2019 3/2/2019 3/3/2019 3/3/2019 3/3/2019 3/4/2019 3/9/2019 3/11/2019 3/12/2019 3/14/2019 3/14/2019 3/14/2019 3/14/2019 1 3/18/2019 3/20/2019 3/21/2019 3/21/2019 3/25/2019 3/26/2019 3/26/2019 3/30/2019 5/13/2019 5/30/2019 6/1/2019 6/17/2019 7/5/2019 7/5/2019 7/20/2019 8/25/2019 20 88 9648848884668486 OG 70 issueCategory consultants customers issues status priority Ready the query logic itself. Scenario: The same manufacturing company from Assignment 1, Teleworks, has created and starting using the database for recording call center information. Now that they have the database with information in it, they need some queries created that give specific information they need to analyze. Task: You have been hired to develop the queries that they need for their daily monitoring and operational needs. 1) Import the SQL script with the tables and data posted with the assignment to D2L into MySQL. Refer to Appendix A for import instructions. 2) Alter each table to have the name prefixed with your initials + your last name. Example: tjdixoncustomers To alter the table, right click on the table and then click alter table. Rename the table in the field that appears and then hit apply. After renaming the table, you need to refresh the schema view in the navigator to the left. Refer to Appendix B for details 3) Develop the following five queries. Be sure to keep a copy of the SQL logic and screenshots in the provided answer sheet on D2L. You can also save queries as .sql files to open later using the menu option File - > Save Script As. QUERY 1: List the number of closed issues for each consultant Include their consultant ID, first and last names, email addresses, and the number of their closed issues. Do not show the status ID or status description (if you're using any one of them for the criteria purpose). Have the output column labeled Number of Closed Issues. Order by the consultants with the most closed. ITM 209 Spring 2020 Assignment #2 Answer Sheet: Database Querying This sheet is for you to enter your results to the assignments. It should be submitted with the export of your database schema. It should include the following in the spaces below: Your queries copied from the MySQL view pasted into the answer sheet (MS Word) Screenshots of the query result when you open the query. Query 1: SQL Statement Used Query 1: Screenshot of the Query Results PROTECTED VIEW Be carefulfiles from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. G25 X fic i in inn mom B C consultantID firstName lastName email jobTitle businessPhone mobilePhone 4 Yoshio Long ylong@teleworks.com Software Engineer (643) 812-5568 (255) 547-3298 3 Dean Conner dconner@teleworks.com Senior Consultant (490) 177-7685 (419) 523-5750 10 Nicole Williams nwilliams@teleworks.com Hardware Engineer (879) 640-0050 (436) 6 Prescott Martin pmartin@teleworks.com Hardware Engineer (691) 215-6715 (546) 511-3490 11 Deborah Zimmerma dzimmerman@teleworks.com Hardware Engineer (930) 773-1006 (560) 595-1719 2 Claire Cameron ccameron@teleworks.com Senior Consultant (332) 237-8327 (637) 299-3143 1 Tyrone Banks tbanks@teleworks.com Senior Consultant (249) 578-8770 (640) 133-3203 8 Nell Simon nsimon@teleworks.com Software Engineer (831) 625-9022 (712) 608-9289 7 Hiram Paul hpaul@teleworks.com Junior Consultant (732) 297-4554 (745) 720-5002 5 Jameson Lyons jlyons@teleworks.com Junior Consultant (653) 746-9275 (835) 674-7730 9 Deacon Solis dsolis@teleworks.com Senior Consultant (836) 329-0317 (997) 266-1131 mom town t i ssueCategory consultants customers issues status priority eady I J K SNN a . 8 NMODENYNORSKANSIONS o N a 1 issueID issueDescription 1 Wifi not connecting. 2 Cannot download apps. 3 Alarm clock does not work 4 Ringer does not work. 5 Camera is not working. 6 One key is broken. 7 Screen-brightness not adjusting. 8 Cannot delete apps. 9 Locked out of account. 10 Wifi not connecting. 11 Wifi not connecting. 12 Can not access menu on phone. 13 Wifi not connecting. 14 Browser not loading. 15 Browser not loading. 16 Cannot delete apps. 17 Can not dial out of area. 18 Locked out of phone. 19 Alarm clock does not work 20 Browser not loading. 21 Cannot download apps. 22 Browser not loading. 23 Camera is not working. 24 Locked out of phone. 25 Cannot download apps. 26 Phone automatically turning off. 27 Can not access Web from phone. 28 Cannot access Web from phone. 29 Screen is frozen. 30 Can not access Web from phone. 31 Wifi not connecting. 32 Can not access menu on phone. 33 Ringer does not work. 34 Can not access Web from phone. 35 Phone will not turn on. 36 Can not access menu on phone. 37 Cannot delete apps. 38 Screen is frozen. 39 Locked out of phone. 40 Lost password. 41 Lost password. 42 Camera is not working. 43 Wifi not connecting. 44 No light 45 Screen is frozen. 46 Cannot delete apps. 47 Locked out of account. 48 Cannot download apps. 49 Locked out of account. 50 Screen is frozen. 51 Cannot download apps. 52 Cannot delete apps. 53 Screen-brightness not adjusting. 54 Screen-brightness not adjusting. 55 Phone will not turn on. 56 Cannot access voicemail. 57 Screen-brightness not adjusting. 58 Very bad 59 Can not access voicemail. 60 No light 61 Phone will not turn on. 62 Phone will not turn on. 63 Can not dial out of area. 64 Wifi not connecting 65 Camera is not working. 66 Locked out of phone. 67 Can not access menu on phone. 68 Screen-brightness not adjusting. C D E F G H dateOpened statuslDcategoryID priorityIDcustomerl.consultant ID 1/1/2019 1/3/2019 1/3/2019 1/6/2019 1/8/2019 1/8/2019 1/11/2019 1/12/2019 1/13/2019 1/15/2019 1/16/2019 1/17/2019 1/19/2019 1/21/2019 1/22/2019 1/24/2019 1/26/2019 1/26/2019 1/30/2019 1/31/2019 2/1/2019 2/3/2019 2/6/2019 2/7/2019 2/7/2019 2/7/2019 3 2/9/2019 2/9/2019 2/10/2019 2/11/2019 2/11/2019 2/13/2019 2/14/2019 2/18/2019 2/21/2019 2/21/2019 2/22/2019 2/24/2019 2/26/2019 2/28/2019 3/2/2019 3/3/2019 3/3/2019 3/3/2019 3/4/2019 3/9/2019 3/11/2019 3/12/2019 3/14/2019 3/14/2019 3/14/2019 3/14/2019 1 3/18/2019 3/20/2019 3/21/2019 3/21/2019 3/25/2019 3/26/2019 3/26/2019 3/30/2019 5/13/2019 5/30/2019 6/1/2019 6/17/2019 7/5/2019 7/5/2019 7/20/2019 8/25/2019 20 88 9648848884668486 OG 70 issueCategory consultants customers issues status priority Ready