For the queries below provide screenshots along with the code in your MS word response document

a$.$ List the students who have not enrolled in any course. Include their names and student IDs.

b$.$ Find out the average grade for each course. Display the course name and its average grade.

c$.$ Write a query to find the students who have not enrolled in any course offered by Professor Dr$.$ Smith. Display the student name and ID$.$

d$.$ Write a query to find the students who have the highest grade in each department. Display the student name, department name, and the grade. If there are multiple students with the highest grade in a department, include all of them.

e$.$ Write a query to list the courses that have at least one student with a grade of $\text{'}$A$\text{'}$ and at least one student with a grade of $\text{'}$B$\text{'}.$ Display the course name and the department name.

f$.$ Find the students who have the highest grade in each course. Display the student name, course name, and the grade.

g$.$ Write a query to display the courses with enrollment counts greater than the average enrollment count. Display the course name and the number of students enrolled in each course.

Following are the data corresponding to the tables in the Database.

STUDENT

StudentID FirstName LastName DateOfBirth Address Email PhoneNumber

$1$ John Doe $1990-05-15$ Cityville john.doe@email.com

$555-123-4567$

$2$ Jane Smith $1992-08-22$ Townsville jane.smith@email.com

$555-987-6543$

$3$ Mark Johnson $1988-12-03$ Villagetown mark.johnson@email.com

$555-555-5555$

$4$ Emily Davis $1991-07-18$ Hamletville emily.davis@email.com

$555-222-3333$

$5$ David Wilson $1993-04-30$ Riverside david.wilson@email.com

$555-777-8888$

$6$ Sarah Turner $1989-09-12$ Mountainville sarah.turner@email.com

$555-444-9999$

$7$ Alex Brown $1992-02-28$ Valleytown alex.brown@email.com

$555-666-1111$

COURSE:

CourseID CourseName Credits DepartmentID

$101$ Introduction to DB $31$

$201$ Advanced SQL $42$

$301$ Data Modeling $31$

$401$ Database Security $32$

$501$ Big Data Analytics $41$

$601$ NoSQL Databases $32$

$701$ Data Warehousing $41$

PROFESSOR:

ProfessorID FirstName LastName DepartmentID Email PhoneNumber

$1001$ Prof Johnson $1$ prof.johnson@email.com

$555-111-2222$

$1002$ Dr$.$ Smith $2$ dr$.$smith@email.com

$555-333-4444$

$1003$ Prof Anderson $1$ prof.anderson@email.com

$555-555-5555$

$1004$ Dr$.$ Davis $3$ dr$.$davis@email.com

$555-222-3333$

$1005$ Prof Miller $1$ prof.miller@email.com

$555-777-8888$

$1006$ Dr$.$ Turner $2$ dr$.$turner@email.com

$555-444-9999$

$1007$ Prof White $1$ prof.white@email.com

$555-666-1111$

DEPARTMENT:

DepartmentID DepartmentName Location

$1$ Computer Science Building A

$2$ Information Tech Building B

$3$ Management Building D

$4$ Finance Building C

ENROLLMENT:

EnrollmentID StudentID CourseID EnrollmentDate Grade

$1000111012023-01-15$ A

$1000212012023-02-20$ B

$1000321012023-01-15$ A

$1000433012023-03-10$ C

$1000542012023-02-22$ B$+$