SQL Projects: School Course Enrollment System

School Course Enrollment System:

The School Course Enrollment System is designed to streamline the management of student course registrations, track grades, and maintain professor assignments. This system allows administrators to manage course availability, monitor student enrollments, and generate insightful reports. The project involves setting up a database with tables for students, courses, enrollments, professors, and grades, along with SQL queries for day-to-day operations and data analysis.

Creating the Database in MySQL or PostgreSQL:

Create the Database:

-- Create the database for the Movie Rental System
CREATE DATABASE SchoolCourseDB;
USE SchoolCourseDB;

Create the Tables:

Students Table:

The Students table holds key information about each student. It includes a unique student ID, the student's full name, a unique email address, and their date of birth. This table is essential for managing student records and ensuring accurate identification.

Structure:

Code:

CREATE TABLE Students (
    student_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,  -- Unique student identifier
    name VARCHAR(50) NOT NULL,                           -- Full name of the student
    email VARCHAR(50) UNIQUE,                            -- Unique email address
    dob DATE                                             -- Date of birth of the student
);

Courses Table :

The Courses table contains details about each course offered. It includes a unique course ID, the course name, and the number of credits associated with the course. This table is crucial for managing course offerings and credit requirements for students.

Structure:

Code:

-- Create the Courses table
CREATE TABLE Courses (
    course_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,   -- Unique course identifier
    course_name VARCHAR(100) NOT NULL,                   -- Name of the course
    credits INT                                          -- Number of credits for the course
);

Professors Table:

The Professors table stores information about each professor, including a unique professor ID, their full name, and the department they belong to. This table is essential for organizing faculty information and linking professors to their respective courses and departments.

Structure:

Code:

-- Create the Professors table
CREATE TABLE Professors (
    professor_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, -- Unique professor identifier
    name VARCHAR(50) NOT NULL,                            -- Full name of the professor
    department VARCHAR(50)                                -- Department the professor belongs to
);

Enrollments Table:

The Enrollments table records each student’s enrollment in specific courses, linking students and courses through a unique enrollment ID. It includes details such as the semester and year of enrollment, providing a structured view of students’ academic activities across terms. This table is key for managing and tracking course registrations for each student.

Structure:

Code:

-- Create the Enrollments table
CREATE TABLE Enrollments (
    enrollment_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, -- Unique identifier for each enrollment record
    student_id INT,                                        -- Foreign key referencing Students table
    course_id INT,                                         -- Foreign key referencing Courses table
    semester VARCHAR(10),                                  -- Semester of enrollment (e.g., Fall)
    year YEAR,                                             -- Year of enrollment
    FOREIGN KEY (student_id) REFERENCES Students(student_id), -- Linking to Students
    FOREIGN KEY (course_id) REFERENCES Courses(course_id)      -- Linking to Courses
);

Grades Table:

The Grades table stores the grade information for each course enrollment, linking grades to specific enrollments through a unique grade ID. Each record includes the enrollment ID and the grade awarded (e.g., A, B, C), allowing for efficient tracking and retrieval of students’ academic performance in individual courses.

Structure:

Code:

-- Create the Grades table
CREATE TABLE Grades (
    grade_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,      -- Unique identifier for each grade record
    enrollment_id INT,                                     -- Foreign key referencing Enrollments table
    grade CHAR(1),                                         -- Grade awarded (A, B, C, etc.)
    FOREIGN KEY (enrollment_id) REFERENCES Enrollments(enrollment_id) -- Linking to Enrollments
);

Inserting Data:

Add some sample data to the tables.

Inserting Data into Students Table:

-- Insert sample students
INSERT INTO Students (name, email, dob) VALUES
('Mitchel Hogg', '[email protected]', '2003-05-12'),
('Branden Scot', '[email protected]', '2002-11-30');

Inserting Data into Courses Table:

-- Insert sample courses
INSERT INTO Courses (course_name, credits) VALUES
('Mathematics', 3),
('Physics', 4);

Inserting Data into Professors Table:

-- Insert sample professors
INSERT INTO Professors (name, department) VALUES
('Dr. Eden Kerry', 'Mathematics'),
('Dr. Peter Birds', 'Physics');

Inserting Data into Enrollments Table:

-- Insert sample enrollments
INSERT INTO Enrollments (student_id, course_id, semester, year) VALUES
(1, 1, 'Fall', 2024),
(2, 2, 'Spring', 2024);

Inserting Data into Grades Table:

-- Insert sample grades
INSERT INTO Grades (enrollment_id, grade) VALUES
(1, 'A'),
(2, 'B');

Basic Functionalities:

• Register New Students: Allow administrators to add new students.
• Add Courses and Professors: Add new courses and assign professors.
• Enroll Students in Courses: Register students for courses each semester.
• Assign Grades: Record grades for each course a student is enrolled in.
• Check Course Availability: View available seats and course load.
• Generate Reports: Provide statistics on enrollments, grades, and professor assignments.

Writing Queries for Functionality:

Query-1: List all students

SELECT * FROM Students;  -- Retrieves all columns and records from the Students table

Explanation:

This query selects and displays every row and column from the Students table, showing details for each registered student. It retrieves all stored information, such as student IDs, names, emails, and dates of birth, allowing an overview of the entire student list.

Output:

student_id	name	email	dob
1	Mitchel Hogg	[email protected]	2003-05-12
2	Branden Scot	[email protected]	2002-11-30

Query-2: View all courses

SELECT * FROM Courses;  -- Retrieves all columns and records from the Courses table

Explanation:

This query fetches and displays all rows and columns from the Courses table, providing a full list of available courses. It includes each course's unique ID, name, and credit value, offering a quick overview of the courses offered in the system.

Output:

course_id	course_name	credits
1	Mathematics	3
2	Physics	4

Query-3: Get all professors by department

SELECT * FROM Professors WHERE department = 'Mathematics';  -- Retrieves professors in the Mathematics department

Explanation:

This query selects all columns from the Professors table for professors who are part of the Mathematics department. It enables filtering by department, making it easy to view professors assigned to a specific area within the institution.

Output:

professor_id	name	department
1	Dr. Eden Kerry	Mathematics

Query-4: Count of students in each course

SELECT Courses.course_name, COUNT(Enrollments.student_id) AS student_count  -- Selects course name and counts students per course
FROM Enrollments
JOIN Courses ON Enrollments.course_id = Courses.course_id  -- Joins Enrollments with Courses based on course_id
GROUP BY Courses.course_name;  -- Groups results by course name to get student count per course

Explanation:

This query provides the number of students enrolled in each course. By joining the Enrollments and Courses tables on their common course_id, it gathers data from both tables. Grouping by course_name allows for counting students enrolled in each specific course, giving an overview of enrollment distribution across courses.

Output:

course_name	student_count
Mathematics	1
Physics	1

Query-5: Display all enrollments for a particular semester

	SELECT * FROM Enrollments  -- Selects all columns from the Enrollments table
WHERE semester = 'Fall' AND year = 2024;  -- Filters results for enrollments in Fall semester of 2024

Explanation:

This query retrieves all enrollment records from the Enrollments table for students who enrolled in the Fall semester of 2024. By using the WHERE clause to specify both semester and year, it efficiently filters the records to show enrollments for a particular semester and year.

Output:

enrollment_id	student_id	course_id	semester	year
1	1	1	Fall	2024

Query-6: Check student grades for a course

SELECT Students.name,             -- Retrieves the name of the student
       Courses.course_name,       -- Retrieves the name of the course
       Grades.grade               -- Retrieves the grade awarded to the student
FROM Grades
JOIN Enrollments ON Grades.enrollment_id = Enrollments.enrollment_id  -- Links Grades to Enrollments based on enrollment ID
JOIN Students ON Enrollments.student_id = Students.student_id         -- Links Enrollments to Students based on student ID
JOIN Courses ON Enrollments.course_id = Courses.course_id             -- Links Enrollments to Courses based on course ID
WHERE Courses.course_name = 'Mathematics';                            -- Filters results to a specific course

Explanation:

This query displays each student’s name, the course name, and their corresponding grade for the course "Mathematics." By joining the Grades, Enrollments, Students, and Courses tables on their respective foreign keys, the query effectively associates each grade with the student and course details. The WHERE clause limits the results to only those students enrolled in the Mathematics course.

Output:

name	course_name	grade
Mitchel Hogg	Mathematics	A

Query-7: List courses a student is enrolled in

SELECT Courses.course_name               -- Retrieves the name of each course
FROM Enrollments
JOIN Courses ON Enrollments.course_id = Courses.course_id -- Links Enrollments to Courses by course ID
WHERE Enrollments.student_id = 1;                         -- Filters for courses of a specific student

Explanation:

This query retrieves the list of course names for which a particular student, identified by student_id = 1, is currently enrolled. By joining the Enrollments and Courses tables through the course_id, the query returns only the courses that match this student’s enrollment record.

Output:

course_name
Mathematics

Query-8: Retrieve the professor for each course

SELECT Courses.course_name, Professors.name AS professor -- Retrieves course name and associated professor's name
FROM Professors
JOIN Courses ON Professors.department = Courses.course_name; -- Links Professors to Courses by matching department and course name

Explanation:

This query lists each course along with the name of the professor assigned to it. By joining the Professors and Courses tables based on the assumption that the professor’s department matches the course name, the query identifies the assigned professor for each course. The results display the course name and the professor’s name as "professor."

Output:

course_name	professor
Mathematics	Dr. Eden Kerry
Physics	Dr. Peter Birds

Query-9: Find students with no grades assigned

SELECT Students.name -- Retrieves the names of students without grades
FROM Students
LEFT JOIN Enrollments ON Students.student_id = Enrollments.student_id -- Links Students to Enrollments based on student_id
LEFT JOIN Grades ON Enrollments.enrollment_id = Grades.enrollment_id -- Links Enrollments to Grades based on enrollment_id
WHERE Grades.grade IS NULL; -- Filters for students who do not have an assigned grade

Explanation:

This query identifies students who currently lack a grade in any enrolled course. By using left joins between Students, Enrollments, and Grades, it ensures that all students are included, even if they have no corresponding grade entry. The WHERE Grades.grade IS NULL clause specifically filters for cases where no grade has been assigned, returning only the names of those students.

Output:

Output not generated for insufficient data

Query-10: Count of students enrolled in each course.

SELECT Courses.course_name, -- Retrieves the name of each course
       COUNT(Enrollments.student_id) AS student_count -- Counts the students in each course
FROM Enrollments
JOIN Courses ON Enrollments.course_id = Courses.course_id -- Links Enrollments to Courses by course_id
GROUP BY Courses.course_name; -- Groups results by each course name

Explanation:

This query calculates the number of students enrolled in each course by joining the Enrollments and Courses tables. Using COUNT(Enrollments.student_id) and grouping by the course name, it returns a list of courses along with the total number of students enrolled in each one. This is useful for quickly assessing the enrollment levels .

Output:

course_name	student_count
Mathematics	1
Physics	1

Query-11: Get the average grade for each course

SELECT Courses.course_name, AVG(CASE 
            WHEN Grades.grade = 'A' THEN 4.0         -- Assigns a value of 4.0 for grade A
            WHEN Grades.grade = 'B' THEN 3.0         -- Assigns a value of 3.0 for grade B
            WHEN Grades.grade = 'C' THEN 2.0         -- Assigns a value of 2.0 for grade C
            WHEN Grades.grade = 'D' THEN 1.0         -- Assigns a value of 1.0 for grade D
            ELSE 0                                   -- Assigns a value of 0 for any other grade
        END) AS average_grade                       -- Calculates the average of the assigned values
FROM Grades
JOIN Enrollments ON Grades.enrollment_id = Enrollments.enrollment_id  -- Joins Grades with Enrollments to connect grades with student enrollments
JOIN Courses ON Enrollments.course_id = Courses.course_id               -- Joins Enrollments with Courses to associate enrollments with their respective courses
GROUP BY Courses.course_name;                                             -- Groups the results by course name to calculate the average for each course

Explanation:

This query calculates the average grade for each course using a weighted grading system, where letter grades are converted into numerical values. It joins the Grades, Enrollments, and Courses tables to associate each grade with the respective course. The result is grouped by course name, providing the average grade for each course based on student performance.

Output:

course_name	average_grade
Mathematics	4.00000
Physics	3.00000

Query-12: List the top 5 students with the highest average grades

SELECT Students.name, AVG(CASE 
            WHEN Grades.grade = 'A' THEN 4.0         -- Assigns a value of 4.0 for grade A
            WHEN Grades.grade = 'B' THEN 3.0         -- Assigns a value of 3.0 for grade B
            WHEN Grades.grade = 'C' THEN 2.0         -- Assigns a value of 2.0 for grade C
            WHEN Grades.grade = 'D' THEN 1.0         -- Assigns a value of 1.0 for grade D
            ELSE 0                                   -- Assigns a value of 0 for any other grade
        END) AS average_grade                       -- Calculates the average of the assigned values
FROM Grades
JOIN Enrollments ON Grades.enrollment_id = Enrollments.enrollment_id  -- Joins Grades with Enrollments to connect grades with student enrollments
JOIN Students ON Enrollments.student_id = Students.student_id          -- Joins Enrollments with Students to link grades to the correct student
GROUP BY Students.name                                                   -- Groups the results by student name to calculate their average grade
ORDER BY average_grade DESC                                             -- Orders the results in descending order of average grade
LIMIT 5;                                                                -- Limits the output to the top 5 students

Explanation:

This query identifies the top 5 students with the highest average grades across their enrolled courses. It converts letter grades into numerical values and calculates the average for each student by joining the Grades, Enrollments, and Students tables. The results are grouped by student name, ordered by their average grade in descending order, and limited to the top five students, showcasing those with the best academic performance.

Output:

name	average_grade
Mitchel Hogg	4.00000
Branden Scot	3.00000

Query-13: Get the total number of courses offered in each department

SELECT Professors.department, COUNT(DISTINCT Courses.course_id) AS total_courses
FROM Professors
JOIN Courses ON Professors.department = Courses.course_name  -- Joins Professors and Courses tables based on matching department names
GROUP BY Professors.department;                             -- Groups the results by department to count courses per department

Explanation:

This query determines the total number of distinct courses offered within each department. By joining the Professors and Courses tables through the department field, it groups the data by department name, then counts the unique course IDs to find the total number of courses per department. This output shows how many courses each academic department offers in the institution.

Output:

department	total_courses
Mathematics	1
Physics	1

Query-14: Retrieve students who are enrolled in more than one course

SELECT Students.name, COUNT(Enrollments.course_id) AS course_count
FROM Students
JOIN Enrollments ON Students.student_id = Enrollments.student_id  -- Joins Students and Enrollments tables by student_id to get each student's enrollments
GROUP BY Students.name                                           -- Groups results by student name
HAVING COUNT(Enrollments.course_id) > 1;                         -- Filters to show only students with more than one course enrollment

Explanation:

This query identifies students who are enrolled in multiple courses by counting the course enrollments for each student. After joining the Students and Enrollments tables, it groups the data by student name and uses the HAVING clause to filter results to show only those students with a course count greater than one. The output lists each eligible student along with the total number of courses they are enrolled in.

Output:

Output not generated for insufficient data

Query-15: Find the course with the highest enrollment

SELECT Courses.course_name, COUNT(Enrollments.student_id) AS student_count
FROM Enrollments
JOIN Courses ON Enrollments.course_id = Courses.course_id  -- Joins Enrollments with Courses to access course names
GROUP BY Courses.course_name                               -- Groups by course name to calculate enrollment per course
ORDER BY student_count DESC                                -- Orders results by student count in descending order to show the highest enrollment first
LIMIT 1;                                                   -- Limits result to only the course with the highest enrollment

Explanation:

This query finds the course with the highest enrollment by joining Enrollments and Courses tables, grouping by course name, and counting the students enrolled in each course. After calculating the student count for each course, it sorts the results in descending order and limits the output to the top result, thus displaying only the course with the highest enrollment and its total student count.

Output:

course_name	student_count
Mathematics	1

Query-16: Get the number of grades assigned for each course

SELECT Courses.course_name, COUNT(Grades.grade) AS grades_assigned
FROM Courses
LEFT JOIN Enrollments ON Courses.course_id = Enrollments.course_id   -- Joins Courses with Enrollments to link students with courses
LEFT JOIN Grades ON Enrollments.enrollment_id = Grades.enrollment_id -- Joins Enrollments with Grades to access assigned grades
GROUP BY Courses.course_name;                                        -- Groups by course name to count grades for each course

Explanation:

This query retrieves the number of grades assigned for each course by joining the Courses, Enrollments, and Grades tables. It uses LEFT JOIN to ensure all courses are included, even those without any assigned grades. The results are grouped by course name, and COUNT(Grades.grade) calculates the total number of grades given for each course, resulting in a list of courses with their corresponding grade counts.

Output:

course_name	grades_assigned
Mathematics	1
Physics	1

Query-17: List professors who teach more than one course

SELECT Professors.name, COUNT(Courses.course_id) AS course_count
FROM Professors
JOIN Courses ON Professors.department = Courses.course_name      -- Joins Professors with Courses based on department matching course name
GROUP BY Professors.name                                         -- Groups results by professor name to calculate the course count
HAVING COUNT(Courses.course_id) > 1;                             -- Filters to show only professors teaching more than one course

Explanation:

This query identifies professors who are responsible for teaching more than one course. By joining the Professors and Courses tables on the department and course name, it counts the number of courses each professor teaches. Grouping by professor name allows the query to summarize by individual professors, and the HAVING clause then filters for those with a course count greater than one. This output lists each qualifying professor alongside their total number of courses.

Output:

Output not generated for insufficient data

Query-18: Find students enrolled in courses taught by a specific professor

SELECT Students.name
FROM Students
JOIN Enrollments ON Students.student_id = Enrollments.student_id   -- Links each student to their enrollments
JOIN Courses ON Enrollments.course_id = Courses.course_id          -- Links enrollments to corresponding courses
JOIN Professors ON Professors.department = Courses.course_name     -- Links professors to courses based on department matching course name
WHERE Professors.name = 'John Doe';                                -- Filters for courses taught by the specified professor
-- Lists students who are enrolled in courses taught by the specified professor.

Explanation:

This query retrieves the names of students enrolled in courses taught by a specific professor. By joining Students, Enrollments, Courses, and Professors tables, the query links students to professors through the courses they are enrolled in. The WHERE clause filters the results to include only courses taught by the specified professor (replace 'John Doe' with the desired professor’s name). The output displays the names of students taking courses from that professor.

Output:

Output not generated for insufficient data

Query-19: Find courses that have never been enrolled in

SELECT Courses.course_name
FROM Courses
LEFT JOIN Enrollments ON Courses.course_id = Enrollments.course_id -- Left joins courses with enrollments
WHERE Enrollments.course_id IS NULL;                               -- Filters for courses without enrollments
-- Lists courses that have not had any student enrollments.

Explanation:

This query identifies courses that currently have no student enrollments by performing a LEFT JOIN between the Courses and Enrollments tables. The WHERE Enrollments.course_id IS NULL condition ensures only courses with no matching records in the Enrollments table (indicating no enrollments) are included in the result. This provides a list of courses that have not yet been selected by any student.

Output:

Output not generated for insufficient data

SQL Code Editor: