write SQL to perform the following queries :

1.How many students have not received a grade? If students have not received a grade in multiple course sections they are enrolled in, count them only once. So, if student 3 has no grade in course sections 12 and 13, count them only once

2.How many sections of each course are there and what is the maximum combined enrollments are there for each course? List call id, the course name and the number of sections and the total enrollments. Sort them by call id.

3. Generate transcript data for student Sarah Miller. That is, list by the term, include the term description, all the courses that they have taken, and the grade received. Include the call id and the course name.

4.

• List the names (first and last) of all faculty who have taught MIS 101. The answer should look like this:

+----------------+

| Instructor |

+----------------+

| Kim Cox |

| Jerry Williams |

+----------------+

• Add the rank so your answer looks like this:

+---------------------+

| Instructor |

+---------------------+

| ASSO Kim Cox |

| ASST Jerry Williams |

+---------------------+

• Make it better so it looks like this:

+----------------+---------------------+

| Instructor | RANK |

+----------------+---------------------+

| Kim Cox | Associate Professor |

| Jerry Williams | Assistant Professor |

+----------------+---------------------+

F.MI String FACULTY ID F LAST Number String 1 Cox 2 Blanchard 3 Williams 4 Sheng 5 Brown F FIRST String Kim John Jerry Laura Phillip R F M E LOC_ID Number 9 10 12 11 13 F_PHONE String 7155551234 7155559087 7155555412 7155556409 7155556082 FRANK String ASSO FULL ASST INST ASSO F PINF IMAGE String Binary 1181 cox.jpg 1075 blanchard.jpg 8531 Williams.jpg 1690 sheng.jpg 9899 brown.jpg STUDENT SID S LAST Number String 1 Miller 2 Umato 3 Black 4 Mobley 5 Sanchez 6 Cormoly S MI String M D S_FIRST String Sarah Brian Daniel Amanda Ruben Michael S ADDRESS String 144 Windridge Blvd. 454 St. John's Place 8921 Circle Drive 1716 Summit St. 1780 Samantha Court 1818 Silver Street S CITY String Eau Claire Eau Claire Bloomer Eau Claire Eau Claire Elk Mound S. STATE String WI WI WI WI WI WI S ZIP String 54703 54702 54715 54703 54701 54712 J R S STUDENT (continued) S PHONE S. CLASS String String 7155559876 SR 7155552345 SR 7155553907 JR 7155556902 SO 7155558899 SO 7155554944 FR S DOB Date/Time 07/14/85 08/19/85 10/10/82 9/24/86 11/20/B6 12/4/87 S PIN String 8891 1230 1613 1841 4420 9188 F.ID Number 1 1 1 2 TIME ENROLLED Interval 3 YEARS 2 MONTHS 4 YEARS 6 MONTHS 3 YEARS O MONTHS 2 YEARS 2 MONTHS 1 YEAR 11 MONTHS O YEARS 4 MONTHS 4 3 TERM TERM_ID TERM_DESC Number String 1 Fall 2005 2 Spring 2006 3 Summer 2006 4 Fall 2006 5 Spring 2007 6 Summer 2007 LOCATION LOC_ID BLDG_CODE Number String 1 CR 2. CR 3 CR 4 CR 5 BUS 6 BUS 7 BUS 8 BUS 9 BUS 10 BUS 11 BUS 12 LIB 13 LIB CAPACITY Number 150 40 35 35 42 35 35 55 1 STATUS String CLOSED CLOSED CLOSED CLOSED CLOSED OPEN ROOM String 101 202 103 105 105 404 421 211 424 402 433 217 222 1 1 2 COURSE 1 DIE COURSE COURSE ID CALL_ID Number String 1 MIS 101 2 MIS 301 3 MIS 441 4 CS 155 5 MIS 451 COURSE NAME String Intro to Info. Systems Systems Analysis Database Management Programming in C++ Web-Based Systems CREDITS Number 3 3 3 3 3 COURSE_SECTION C SEC_ID COURSE ID TERM_ID SEC_NUM FID C SEC DAY Number Number Number Number Number String 1 1 4 1 2 MWF 2 1 4 2 3 TR 1 4 3 3 MWF 4 2 4 1 4 TR 5 2 5 2 4 TR 6 3 5 1 1 MWF 7 3 5 2 1 MWF 8 4 5 1 5 TR 9 5 5 1 2 MWF 5 5 2 2 MWF 11 1 6 1 1 MTWRF 12 2 6 1 2 MTWRF 13 6 1 3 MTWRF CSEC TIME Date/Time 10.00 AM 9:30 AM 8:00 AM 11:00 AM 2:00 PM 9:00 AM 10:00 AM 8:00 AM 2:00 PM 3:00 PM 8:00 AM 8:00 AM 9:00 AM CSEC DURATION Interval 50 MINUTES 1 HOUR 15 MINUTES 50 MINUTES 1 HOUR 15 MINUTES 1 HOUR 15 MINUTES 50 MINUTES 50 MINUTES 1 HOUR 15 MINUTES 50 MINUTES 50 MINUTES 1 HOUR 30 MINUTES 1 HOUR 30 MINUTES 1 HOUR 30 MINUTES LOC ID Number 1 7 2 6 6 5 5 3 5 5 1 6 5 MAX ENRL Number 140 35 35 35 35 30 30 35 35 35 50 35 35 10 GRADE String B B A ENROLLMENT SID C SEC_ID Number Number 1 1 1 4 1 6 1 9 2 1 2 5 2 6 2 9 3 1 3 12 3 13 4 11 4 12 5 1 5 5 5 9 5 11 5 13 5 11 6 12 B