For an instructor lead, in-depth look at learning SQL click below.
Welcome to this comprehensive tutorial on how to build a Student Course Registration and Enrollment System using SQL. We will explore various SQL concepts through this hands-on approach.
Creating Tables
We start by creating the necessary tables – Students, Courses and Registrations.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
$sql " CREATE TABLE Students( ID INT PRIMARY KEY, Name NVARCHAR(100) NOT NULL, Email NVARCHAR(50) ); CREATE TABLE Courses ( ID INT PRIMARY KEY, Name NVARCHAR(100) NOT NULL, Professor NVARCHAR(50) ); CREATE TABLE Registrations( Student INT FOREIGN KEY REFERENCES Students(ID), Course INT FOREIGN KEY REFERENCES Courses(ID), RegistrationTime DATETIME DEFAULT CURRENT_TIMESTAMP ); " |
Inserting Data
Once our tables are set up, we populate them with some initial data.
|
1 2 3 4 |
$sql " INSERT INTO Students (ID, Name, Email) VALUES (1, 'John Doe', <a href="mailto:'john.doe@gmail.com'" >'john.doe@gmail.com'</a>); INSERT INTO Courses (ID, Name, Professor) VALUES (1, 'SQL Basics', 'Prof. Smith'); " |
Registering Students to Courses
We can now register a student to a course by inserting an entry in the Registrations table.
|
1 2 3 |
$sql " INSERT INTO Registrations (Student, Course) VALUES (1, 1); " |
Querying Student Registrations
To view all the courses a student has registered for, we can query the Registrations table.
|
1 2 3 4 5 6 7 |
$sql " SELECT c.Name FROM Registrations r INNER JOIN Courses c ON r.Course = c.ID WHERE r.Student = 1; " |
Closing Thoughts
For more complex database systems, we would need to consider more factors such as, handling time conflicts between courses, student maximum course loads, and others. However, this basic system serves as a great starting point for anyone interested in database management in SQL. Happy coding!