Scribd
Upload
2 views3 pages

Student Management SQL Project

The document outlines a SQL practice project for a Student Management System, including steps to create a database, tables for students, courses, enrollments, and teachers, and insert sample data. It also provides examples of queries for data retrieval, joining tables, aggregation, updating records, and creating views. Additionally, it lists challenge tasks to further enhance the system with more students, courses, and functionalities.

Uploaded by

dhanyamanju1868
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
2 views3 pages

Student Management SQL Project

The document outlines a SQL practice project for a Student Management System, including steps to create a database, tables for students, courses, enrollments, and teachers, and insert sample data. It also provides examples of queries for data retrieval, joining tables, aggregation, updating records, and creating views. Additionally, it lists challenge tasks to further enhance the system with more students, courses, and functionalities.

Uploaded by

dhanyamanju1868
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Student Management System - SQL Practice Project

Step 1: Create Database

CREATE DATABASE StudentManagement;


GO

USE StudentManagement;
GO

Step 2: Create Tables

CREATE TABLE Students (


StudentID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Gender VARCHAR(10),
DateOfBirth DATE,
Email VARCHAR(100)
);

CREATE TABLE Courses (


CourseID INT PRIMARY KEY,
CourseName VARCHAR(100),
Credits INT
);

CREATE TABLE Enrollments (


EnrollmentID INT PRIMARY KEY,
StudentID INT,
CourseID INT,
FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

CREATE TABLE Teachers (


TeacherID INT PRIMARY KEY,
TeacherName VARCHAR(100),
Department VARCHAR(100)
);
Step 3: Insert Sample Data

INSERT INTO Students VALUES


(1,'John','Smith','Male','2003-01-15','john@[Link]'),
(2,'Mary','Johnson','Female','2002-05-10','mary@[Link]'),
(3,'David','Brown','Male','2004-07-22','david@[Link]');

INSERT INTO Courses VALUES


(101,'Database Systems',3),
(102,'Programming Fundamentals',4),
(103,'Web Development',3);

INSERT INTO Enrollments VALUES


(1,1,101),
(2,1,102),
(3,2,101),
(4,3,103);

INSERT INTO Teachers VALUES


(1,'Mr. Wilson','Computer Science'),
(2,'Ms. Taylor','Information Technology');

Step 4: Practice Queries

SELECT * FROM Students;

SELECT * FROM Students WHERE Gender='Female';

SELECT * FROM Students WHERE DateOfBirth > '2003-01-01';

SELECT * FROM Students ORDER BY FirstName;

Step 5: JOIN Example

SELECT
[Link],
[Link],
[Link]
FROM Students
INNER JOIN Enrollments
ON [Link] = [Link]
INNER JOIN Courses
ON [Link] = [Link];
Step 6: Aggregation

SELECT COUNT(*) AS TotalStudents FROM Students;

SELECT CourseID, COUNT(*) AS TotalEnrollments


FROM Enrollments
GROUP BY CourseID;

Step 7: Update

UPDATE Students
SET Email='newjohn@[Link]'
WHERE StudentID=1;

Step 8: View

CREATE VIEW StudentCourseView AS


SELECT [Link], [Link], [Link]
FROM Students S
JOIN Enrollments E ON [Link] = [Link]
JOIN Courses C ON [Link] = [Link];

SELECT * FROM StudentCourseView;

Challenge Tasks:
1. Add 5 more students.
2. Add 3 more courses.
3. Enroll students in multiple courses.
4. Find all students taking Database Systems.
5. Find students not enrolled in any course.
6. Find the course with the most enrollments.
7. Show the number of courses each student takes.
8. Add a Grades table.
9. Calculate average grades.
10. Create a stored procedure to add a student.

You might also like