helpCase Study: University Database

Tables:

Students:

StudentID $($Primary Key$)$

FirstName

LastName

Gender

DateOfBirth

DepartmentID $($Foreign Key$)$

Departments:

DepartmentID $($Primary Key$)$

DepartmentName

Courses:

CourseID $($Primary Key$)$

CourseName

Credits

DepartmentID $($Foreign Key$)$

Enrollments:

EnrollmentID $($Primary Key$)$

StudentID $($Foreign Key$)$

CourselD $($Foreign Key$)$

Semester

Grade

General Requirements:

$[$Marks: $15]$

Present the University Database tables into ERD

Create the University Database tables and fill in at least $15$ data rows

Write Queries for the following data outputs

Retrieve the list of all students with their first and last names.

Display the names of all departments.

Get the names of all courses.

List all students with their course enrollments $($include course name and semester$).$

Display the names of students along with the department they belong to$.$

Find the total number of students in each department.

Calculate the average grade for each course.

Retrieve the names of students who have the highest grade in a particular course.

Find the courses with the highest enrollment.

List all distinct courses across all departments.

Identify the courses that have no enrollments.

List all students who have enrolled in at least one course.

Display the courses with enrollments in the current semester.

Note: Attach the screenshots in all the answers