ALL IS SQL DESIGN VIEW IN ACCESS

just number 13

8. Write a SQL query to display Last, First, and State (in that order) for all students from OH, MI, KY, IN. Sort by State then by Last Name. Name the query SQL_6_Regional. 9. Copy SQL_6 and name it SQL_7_NotRegional. Modify the query to display the same information for all students not from OH, MI, KY or IN. 10. Write a SQL query to display Last, First, State and Major (in that order) for all students whose Major is ACC, MKT, ECO or ISA and who are from OH, KY, MI, IL. Sort by Major then by state and then by Last Name. Name the query SQL_8_MajorsAndStates. 11. Write a SQL query to display Last, First, Gender, Entryyear, State and Majorld for all students who either have an entryyear in 2020 or are from Kentucky, and where the student is either Male or majoring in ISA. Name the query SQL_9_Students. 12. Write a SQL query to display Last, First, Birth and State for all students born in January, February or March of 1991 who are from either IL or OH. Name the query SQL10_1991states. 13. The following three queries will require the use of two tables that are joined. a. Write a SQL query to display Department and CourseNumber from the Course table, and Semester, ClassSection, Instructor, Location and Time from the Class table. Sort the display by Semester, then Department and then by CourseNumber. Name the query SQL11_Class History b. Write a SQL query to count the number of courses offered by semester (display as CourseCount), and to add up the total credit hours from those courses (display as TotalCredits). Name the query SQL12_CountCredits. c. Write a SQL query to display the first and last name along with MajorDept of all students who have a MajorDept of Info. Sys. & Analyti or Marketing. Sort the query by MajorDept and then Last name. Name the query SQL13_ISA_MKT. 14. Write a SQL query to display a list of states (in reverse alphabetical order) that have at least one student attending the university. [NOTE: This is not an aggregate query since we are not "counting, summing, etc., so you should NOT use GROUP BY). Name the query SQL14_States State - Click to Add EntryYear USResider - 2017 2016 Majorld ACC IN IL MKT 2014 IL ACC 2015 PA ISA 2016 OH MGT 2015 OH ACC 2013 OH ECO 2017 OH ACC 2013 IL MGT OH Course Major Class Student StudentID Last First Gender #100574 Kanaga Giles M # 101017 Thompson Ceara F + 101401 Aultman Collyn F + 101402 Wile Wat M + 101480 Haverrman Nicholas M + 101842 Zoucks Jenna F + 102993 Wiggins Peppi F + 104110 West Octavia F + 104837 Bonner Geoff M + 106191 Swabey Lindsie M + 106386 Higgens Brittney F + 107281 James France F + 107446 Munshower Keturah M + 107660 Hurst Allannah M + 107826 Hutton Ella F + 108316 Adams Ingram M + 109460 Frankenberger Tilly F + 109691 Braun Reannon F + 109952 Chauvin Jaylen M + 110871 Coates Bettie F + 111042 Pycroft Joi F + 111461 Ullman Benjamin M + 111680 Pearson Hattie F 2015 2017 2014 MKT MGT Birth 01/14/98 07/06/90 03/08/90 10/30/96 10/06/96 03/18/91 10/12/98 09/08/95 09/14/99 03/09/91 09/20/95 05/28/96 12/29/97 04/29/92 05/28/94 08/23/93 01/01/90 09/19/97 04/16/96 01/27/96 10/27/92 08/12/93 05/15/90 OH IA MGT ISA KY 2017 2014 IL MGT ECO IL 2016 2013 PA ISA 2014 OH FIN 2013 OH MKT FIN 2016 IL 2014 MI ISA 2013 PA ECO 2016 IL ECO 2014 OH FIN En MaxEnrollment - Click to Add 30 30 30 30 30 30 30 30 30 30 Course Major Class Student ClassID Coursels Semester - ClassSection - + 20108-13FA 20108 2013Fa A + 20108-13SP 20108 2013Sp # 20108-14FA 20108 2014Fa A + 20108-14SP 20108 2014Sp + 20108-15FA 20108 2015Fa + 20108-15SP 20108 2015Sp # 20108-16FA 20108 2016Fa A = 20108-16SP 20108 2016Sp A + 20108-17FA 20108 2017Fa A + 20108-17SP 20108 2017Sp A + 20108-18FA 20108 2018Fa A + 20108-18SP 20108 2018Sp A + 20108-19FA 20108 2019Fa A # 20108-19SP 20108 2019Sp A + 20108-20FA 20108 2020Fa A # 20108-20SP 20108 2020Sp A + 20108-21FA 20108 2021Fa A + 20108-21SP 20108 20218p A + 20108-22FA 20108 2022Fa A + 20108-22SP 20108 20225p A + 20145-13FA 20145 2013Fa A + 20145-13SP 20145 2013Sp + 20145-14FA 20145 2014Fa A 30 Instructor Brewer, Peter Brewer, Peter Brewer, Peter Brewer, Peter Brewer, Peter Brewer, Peter Brewer, Peter Brewer, Peter Brewer, Peter Brewer, Peter Brewer, Peter Brewer, Peter Brewer, Peter Brewer, Peter Brewer, Peter Brewer, Peter Brewer, Peter Brewer, Peter Brewer, Peter Brewer, Peter Hock, Clayton Hock, Clayton Hock, Clayton Location - Time LWS 018 MWF 11:00am LWS 018 MWF 11:00am LWS 018 MWF 11:00am LWS 018 MWF 11:00am LWS 018 MWF 11:00am LWS 018 MWF 11:00am LWS 018 MWF 11:00am LWS 018 MWF 11:00am LWS 018 MWF 11:00am LWS 018 MWF 11:00am LWS 018 MWF 11:00am LWS 018 MWF 11:00am LWS 018 MWF 11:00am LWS 018 MWF 11:00am LWS 018 MWF 11:00am LWS 018 MWF 11:00am LWS 018 MWF 11:00am LWS 018 MWF 11:00am LWS 018 MWF 11:00am LWS 018 MWF 11:00am LWS 303 MWF 11:00am LWS 303 MWF 11:00am LWS 303 MWF 11:00am 30 30 30 30 30 30 30 30 30 30 30 30 Click to Add Course Major Class Student Majorld MajorDept Advisor + Aca Accountancy Celia Havisham + ECO Economics John Cripe + FIN Finance Lisa Goetz + ISA Info. Sys. & Anal Kimberly Sullivan + MGT Management Chris Roeth + MKT Marketing Tyler Wassel Credits MPCategory Technical Click to Add Course Major Class CourselD Department + 20725 ECO + 20727 ISA + 20735 ECO 3 3 3 + 20742 ECO 3 + 21197 ENG 3 + 21261 FIN 3 + 21283 FIN 3 + 21286 FIN 3 + 21296 FIN 3 + 21297 FIN 3 + 21424 GEO Student CourseNumber - Description 321 Eco Institutions & Comp Systms 365 Statistical Quality Control 385 Government And Business 462 Economics Of Compensation 313 Intro To Technical Writing 302 Intermed Financial Management 351 Principles of Insurance 403 Portfolio Management 451 Risk Management And Insurance 452 Life Ins&Adv Pers Fin Planning 121 Earth's Physical Environments 111 Survey of American History 112 Survey Of American History 122 Western Civilization 302 Intro Operatns & Supply Chain M 303 Human Resource Management 402 Employment Law 415 Leadership and Learning 463 Employee Benefits 474 Human Capital Metrics 475 Organizational Change Managmnt 495 Strategic Management 291 Principles of Marketing 4 IV HST 3 III + 21551 + 21552 HST 3 III + 21567 3 III + 21768 3 + 21786 HST MGT MGT MGT MGT 3 + 21793 3 + 21803 3 + 21810 MGT 3 3 + 21814 MGT + 21815 MGT 3 + 21818 MGT 3 + 21853 MKT 3 Course Major Class customerid - firstname 110101 John 10298 Leroy 10299 Elroy 10315 Lisa 10325 Ginger 10329 Kelly 10330 Shawn 10338 Michael 10339 Anthony 10408 Elroy 10410 Mary Ann 10413 Donald 10419 Linda 10429 Sarah 10438 Kevin 10439 Conrad 10449 Isabela Student lastname Gray Brown Keller Jones Schultz Mendoza Dalton Howell Sanchez Cleaver Howell Davids Sakahara Graham Smith Giles customers city state Lynden Washington Pinetop Arizona Snoqualmie Washington Oshkosh Wisconsin Pocatello Idaho Kailua Hawaii Cannon Beach Oregon Tillamook Oregon Winslow Arizona Globe Arizona Charleston South Carolina Gila Bend Arizona Nogales Arizona Greensboro North Carolina Durango Colorado Telluride Colorado Yuma Arizona Moore