how to do this on SQL
i) 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. ii) QUERY 2: Which consultants serve customers in the state of Michigan? List the customers' (contact) names and the consultants' last names. Do not show the field of state. Do not show redundant records. Order by the consultants' last names. iii) QUERY 3: Teleworks wants to find which customers have issues of which the priority level is critical and contact them to ask for their opinions. List their contact names, phone numbers, Issue description, and corresponding consultants' last name. Do not show the field of Priority ID or PriorityLevel. iv) QUERY 4: Teleworks wants to figure out some metrics on their customers and what is outstanding. They would like to list all customers (contact name and phone number) and the total number of open critical priority issues and closed critical priority issues. The count for open issues will be on a separate line from the count of closed issues. The list needs to include all customers whether or not they have any critical issues. Include the field with the status description (open or closed) in the output. Sort by the customer contact name and then status description (both alphabetically). v) QUERY 5: It costs Teleworks $20 for each customer support call processed. These costs reflect processing time and labor. How much has each customer cost Teleworks by making support calls (Issue) that have been closed? For each customer, list their contact name, state, and costs of customer. Sort from the most expensive customers to the least expensive. Label the column in the results with the costs of the customer as Costs of Support categoryID category 1. Hardware/Phone 2 Software/Voicemail 3 Internet/Web A D LLLLLLLLLS consultantID firstName 4 Yoshio 3 Dean 10 Nicole 6 Prescott 11 Deborah 2 Claire 1 Tyrone 8 Nell 7 Hiram 5 Jameson 9 Deacon lastName email Long lylong@teleworks.com Conner T dconner@teleworks.com Williams nwilliams@teleworks.com Martin pmartin@teleworks.com Zimmerman dzimmerman@teleworks.com Cameron ccameron@teleworks.com Banks tbanks@teleworks.com Simon nsimon@teleworks.com Paul hpaul@teleworks.com Lyons jlyons@teleworks.com Solis dsolis@teleworks.com job Title business Phone mobilePhone Software Engineer (643) 812-5568 (255) 547-3298 Senior Consultant (490) 177-7685 (419) 523-5750 Hardware Engineer (879) 640-0050 (436) 678-2363 Hardware Engineer (691) 215-6715 (546) 511-3490 Hardware Engineer (930) 773-1006 (560)595-1719 Senior Consultant (332) 237-8327 (637) 299-3143 Senior Consultant (249) 578-8770 (640)133-3203 Software Engineer (831) 625-9022 (712) 608-9289 Junior Consultant (732) 297-4554 (745) 720-5002 Junior Consultant (653) 746-9275 (835) 674-7730 Senior Consultant (836)329-0317 (997) 266-1131 city Albuquerque Kirkland state postalCode NM 87110 WA 98034 phone (206) 555-4116 (206) 555-4113 49506 49757 MI MI VA MI 22305 NY MI MI LA IN customer D contactName address 11 Alexandra N. Elliott 105-134 Nec, Road 2 Amal K. Travis 112-1471 Dui. Avenue Cherokee B. 3 Melendez 1901 Magna. Rd. 4 Cyrus R. Brooks 1975 Uma. Avenue 5 Dana X. Rosario 217-8515 Imperdiet Avenue 6 Deacon Q. Hart 2866 Dictum Av. 7 Dominic O. Bailey 336-7939 Accumsan Rd. 8 Gary H. Blanchard 4264 Dignissim St. 9 Gray V. Clements 493-8715 Condimentum St. 10 Hedda E. Lancaster 5052 Eu, Road 11 Jamalia A. Byrd 547 Ultrices St. 12 Kyla Y. Howe 6173 Hendrerit Rd. 13 Leandra Q. Foreman 665-9171 Erat. Ave 14 Maggie N. Davenport 709-6860 Quam Rd. 15 Maggy F. Miles 7135 Libero. Road 16 Megan J. Atkinson 957-8312 ld, St. 17 Miriam H. Roberts Ap #143-5881 Vulputate Rd. 18 Oleg J. Knowles Ap #202-8507 Quis Av. 19 Olympia Z. Hester Ap #205-6032 Montes, Rd. 20 Salvador E. Stafford Ap #244-6969 Sed Av. 21 Sean A. Reilly Ap #548-3814 Auctor Street 22 Stewart Z. Hyde Ap #579-598 Convallis Rd. 23 Sybil J. Rush Ap #830-2087 Tortor St. 24 Tashya S. Chan Ap #934-4273 Egestas. Ave 25 Taylor V. Dawson P.O. Box 216, 7778 Dui St. 26 Vaughan B. Buckley P.O. Box 430, 2975 Gravida Street 27 Velma B. Mcmillan P.O. Box 622, 4358 Orci Rd. 28 Veronica R. Page P.O. Box 720, 1178 Ullamcorper. St. 29 Wang F. Duke P.O. Box 896, 8407 Risus. St. 30 Willa Q. Harrington P.O. Box 971, 9801 Ut Rd. 31 Xenos G. Compton P.O. Box 992, 7355 Non St. Grand Rapids Mackinac Island Alexandria Grosse Pointe Birmingham Syosset East Michigan Benton Harbor Michigan Wherever Lander Seattle Traverse City Walla Walla San Francisco Anchorage Saint Louis Beverly Hills Washington Seattle Albany Beverly Hills Santa Fe Traverse City Ames Lansing Detroit Butte Shelby WA MI WA CA AK MO CA DC WA NY CA CA 48230 48009 11791 48823 49022 44445 77777 82520 98128 49684 99362 94117 99508 63104 90210 20016 98128 12054 90210 92067 49684 98938 48917 48214 59801 49455 (616) 555-4123 (906) 555-4118 (703) 555-1843 (313) 555-1724 (248) 555-7560 (212) 528-5833 (517) 555-4119 (313) 555-0033 (444) 344-2553 (217) 334-4553 (206) 555-4117 (465) 555-3829 (231) 555-3729 (206) 555-4124 (206) 555-4121 (206) 555-4122 (314) 555-0594 (323) 555-8762 (202) 555-3145 (206) 555-4112 (517) 555-6097 (323) 555-8762 (619) 555-4578 (231) 555-4115 (224) 339-3232 (517) 555-4120 (313) 555-2841 (206) 555-4114 (231) 774-2000 MI OH MI MI MT MI statusID categoryID priorityID customerID consultantin ONS 23 30 13 issueID issue Description 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 tuming 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. date Opened 1/1/19 1/3/19 1/3/19 1/6/19 1/8/19 1/8/19 1/11/19 1/12/19 1/13/19 1/15/19 1/16/19 1/17/19 1/19/19 1/21/19 1/22/19 1/24/19 1/26/19 1/26/19 1/30/19 1/31/19 2/1/19 2/3/19 2/6/19 2/7/19 2/7/19 2/7/19 2/9/19 2/9/19 2/10/19 2/11/19 2/11/19 2/13/19 2/14/19 2/18/19 2/21/19 2/21/19 2/22/19 2/24/19 2/26/19 2/28/19 3/2/19 3/3/19 3/3/19 3/3/19 3/4/19 3/9/19 3/11/19 3/12/19 3/14/19 3/14/19 3/14/19 3/14/19 3/18/19 3/20/19 3/21/19 3/21/19 3/25/19 3/26/19 3/26/19 NEWANNENWNNNNNW WNW WNNN NNWNW W WNNNNNNN- WWNOW WNWNWON ANNANO Ooon on AAAAAAAWw wWw wWwNNNNNNNNNN 21 24 WAANNN- ooon N N 212 statusID status 2lOpen 1 Closed priority ID priority Level 5. Critical 4 High 3 Moderate 2 Standard 1 Low
