DB USED = STUDENT_MANAGEMENT
-- ======================================
-- DATABASE: StudentManagement (Full Script)
-- ======================================
-- ======================================
-- 1️⃣ Departments Table
-- ======================================
CREATE TABLE Departments (
DepartmentID INT IDENTITY(1,1) PRIMARY KEY,
DepartmentName VARCHAR(50) NOT NULL
);
INSERT INTO Departments (DepartmentName) VALUES
('Computer Science'),
('Electrical Engineering'),
('Mechanical Engineering'),
('Business Administration'),
('Biotechnology');
-- ======================================
-- 2️⃣ Students Table
-- ======================================
CREATE TABLE Students (
StudentID INT IDENTITY(1,1) PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Gender VARCHAR(10),
DOB DATE,
Email VARCHAR(100),
Phone VARCHAR(20),
DepartmentID INT,
Year INT,
FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);
-- 50 Sample Students
INSERT INTO Students (FirstName, LastName, Gender, DOB, Email, Phone, DepartmentID, Year)
VALUES
('Ali','Khan','Male','2002-01-15','ali.khan1@[Link]','03001230001',1,2),
('Sara','Ahmed','Female','2001-05-22','sara.ahmed2@[Link]','03001230002',1,3),
('Bilal','Qureshi','Male','2000-03-10','bilal.qureshi3@[Link]','03001230003',2,4),
('Hina','Riaz','Female','2002-07-18','hina.riaz4@[Link]','03001230004',2,2),
('Omar','Farooq','Male','2003-02-12','omar.farooq5@[Link]','03001230005',3,1),
('Ayesha','Saleem','Female','2001-09-05','ayesha.saleem6@[Link]','03001230006',3,3),
('Usman','Khalid','Male','2000-11-30','usman.khalid7@[Link]','03001230007',4,4),
('Zara','Shah','Female','2002-12-25','zara.shah8@[Link]','03001230008',4,2),
('Hamza','Iqbal','Male','2003-03-03','hamza.iqbal9@[Link]','03001230009',5,1),
('Mariam','Khan','Female','2001-06-15','mariam.khan10@[Link]','03001230010',5,3),
('Ahmed','Rashid','Male','2002-02-10','ahmed.rashid11@[Link]','03001230011',1,1),
('Fatima','Yousaf','Female','2001-07-21','fatima.yousaf12@[Link]','03001230012',1,2),
('Rehan','Malik','Male','2000-04-05','rehan.malik13@[Link]','03001230013',2,3),
('Hira','Nawaz','Female','2002-08-30','hira.nawaz14@[Link]','03001230014',2,1),
('Adeel','Saeed','Male','2003-01-22','adeel.saeed15@[Link]','03001230015',3,2),
('Sana','Javed','Female','2001-10-11','sana.javed16@[Link]','03001230016',3,3),
('Tariq','Hussain','Male','2000-05-17','tariq.hussain17@[Link]','03001230017',4,4),
('Laila','Aslam','Female','2002-03-28','laila.aslam18@[Link]','03001230018',4,2),
('Naveed','Shah','Male','2003-06-06','naveed.shah19@[Link]','03001230019',5,1),
('Rabia','Khan','Female','2001-11-19','rabia.khan20@[Link]','03001230020',5,3),
DB USED = STUDENT_MANAGEMENT
('Kamran','Iqbal','Male','2002-09-01','kamran.iqbal21@[Link]','03001230021',1,2),
('Mona','Rashid','Female','2001-02-14','mona.rashid22@[Link]','03001230022',1,3),
('Faisal','Malik','Male','2000-12-23','faisal.malik23@[Link]','03001230023',2,4),
('Sobia','Nawaz','Female','2002-06-05','sobia.nawaz24@[Link]','03001230024',2,2),
('Shahzad','Saeed','Male','2003-03-17','shahzad.saeed25@[Link]','03001230025',3,1),
('Maryam','Javed','Female','2001-08-12','maryam.javed26@[Link]','03001230026',3,3),
('Arslan','Hussain','Male','2000-07-08','arslan.hussain27@[Link]','03001230027',4,4),
('Noor','Aslam','Female','2002-04-09','noor.aslam28@[Link]','03001230028',4,2),
('Zain','Shah','Male','2003-05-25','zain.shah29@[Link]','03001230029',5,1),
('Hania','Khan','Female','2001-09-30','hania.khan30@[Link]','03001230030',5,3),
('Aftab','Rashid','Male','2002-11-11','aftab.rashid31@[Link]','03001230031',1,2),
('Sadia','Yousaf','Female','2001-01-25','sadia.yousaf32@[Link]','03001230032',1,3),
('Rizwan','Malik','Male','2000-08-20','rizwan.malik33@[Link]','03001230033',2,4),
('Huma','Nawaz','Female','2002-05-15','huma.nawaz34@[Link]','03001230034',2,2),
('Shahid','Saeed','Male','2003-02-28','shahid.saeed35@[Link]','03001230035',3,1),
('Aiza','Javed','Female','2001-03-05','aiza.javed36@[Link]','03001230036',3,3),
('Noman','Hussain','Male','2000-09-18','noman.hussain37@[Link]','03001230037',4,4),
('Mehwish','Aslam','Female','2002-12-01','mehwish.aslam38@[Link]','03001230038',4,2),
('Shayan','Shah','Male','2003-01-07','shayan.shah39@[Link]','03001230039',5,1),
('Sana','Khan','Female','2001-07-17','sana.khan40@[Link]','03001230040',5,3),
('Taimoor','Rashid','Male','2002-03-21','taimoor.rashid41@[Link]','03001230041',1,2),
('Aiman','Yousaf','Female','2001-11-09','aiman.yousaf42@[Link]','03001230042',1,3),
('Shafqat','Malik','Male','2000-02-19','shafqat.malik43@[Link]','03001230043',2,4),
('Iqra','Nawaz','Female','2002-08-22','iqra.nawaz44@[Link]','03001230044',2,2),
('Danish','Saeed','Male','2003-05-12','danish.saeed45@[Link]','03001230045',3,1),
('Hania','Javed','Female','2001-09-27','hania.javed46@[Link]','03001230046',3,3),
('Ammar','Hussain','Male','2000-12-14','ammar.hussain47@[Link]','03001230047',4,4),
('Zoya','Aslam','Female','2002-04-19','zoya.aslam48@[Link]','03001230048',4,2),
('Adil','Shah','Male','2003-06-30','adil.shah49@[Link]','03001230049',5,1),
('Hira','Khan','Female','2001-01-12','hira.khan50@[Link]','03001230050',5,3);
-- ======================================
-- 3️⃣ Courses Table
-- ======================================
CREATE TABLE Courses (
CourseID INT IDENTITY(1,1) PRIMARY KEY,
CourseCode VARCHAR(10),
Title VARCHAR(100),
Credits INT,
DepartmentID INT,
FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);
INSERT INTO Courses (CourseCode, Title, Credits, DepartmentID) VALUES
('CS101','Intro to Programming',3,1),
('CS201','Data Structures',3,1),
('CS301','Algorithms',3,1),
('EE101','Circuit Analysis',3,2),
('EE201','Digital Systems',3,2),
('ME101','Thermodynamics',3,3),
('ME201','Fluid Mechanics',3,3),
('BA101','Principles of Management',3,4),
('BA201','Marketing Basics',3,4),
('BT101','Molecular Biology',3,5);
-- ======================================
-- 4️⃣ Enrollments Table
-- ======================================
DB USED = STUDENT_MANAGEMENT
CREATE TABLE Enrollments (
EnrollmentID INT IDENTITY(1,1) PRIMARY KEY,
StudentID INT,
CourseID INT,
EnrollmentDate DATE DEFAULT GETDATE(),
Grade CHAR(2),
FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);
-- 50 Sample Enrollments
INSERT INTO Enrollments (StudentID, CourseID, Grade) VALUES
(1,1,'A'),(1,2,'B'),(2,1,'A'),(2,3,'B'),(3,3,'C'),(3,4,'B'),(4,4,'A'),(4,5,'B'),(5,5,'C'),
(5,6,'A'),(6,6,'B'),(6,7,'A'),(7,7,'C'),(7,8,'B'),(8,8,'A'),(8,9,'B'),(9,9,'C'),(9,10,'A'),
(10,10,'B'),(10,1,'A'),(11,2,'B'),(11,3,'C'),(12,4,'A'),(12,5,'B'),(13,6,'C'),(13,7,'A'),
(14,8,'B'),(14,9,'C'),(15,10,'A'),(15,1,'B'),(16,2,'A'),(16,3,'B'),(17,4,'C'),(17,5,'A'),
(18,6,'B'),(18,7,'C'),(19,8,'A'),(19,9,'B'),(20,10,'C'),(20,1,'A'),(21,2,'B'),(21,3,'C'),
(22,4,'A'),(22,5,'B'),(23,6,'C'),(23,7,'A'),(24,8,'B'),(24,9,'C'),(25,10,'A'),(25,1,'B');
Basic SELECT Queries
1. Sab students ke first name aur last name fetch karo.
2. Sab students ke email aur phone list karo.
3. Sab departments ka naam list karo.
4. Sab courses ka title aur credits list karo.
5. Students ki total count nikal kar dikhao.
6. Sab students ke distinct departments show karo.
7. Sab male students ka naam aur email show karo.
8. Sab female students ka naam aur email show karo.
9. Students ka average year nikal kar dikhao.
10. Courses ki total count per department nikal kar dikhao.
Filtering & Sorting
11. Students jo 3rd year mein hain, unke naam fetch karo.
12. Students jo Computer Science mein hain, unke emails show karo.
13. Courses jinke credits = 3 hain, unka title show karo.
14. Courses jinke title mein 'Data' hai, unko fetch karo.
15. Students ka dob ascending order mein show karo.
16. Students ka last name descending order mein list karo.
17. Departments jahan id > 2, unka naam list karo.
18. Students jo year = 2 aur male hain, unko fetch karo.
19. Courses jinke departmentID = 1, show karo.
20. Courses jinka title starting with 'B' hai, show karo.
JOIN Queries
21. Students ke saath unka department name show karo.
22. Students ke saath unke enrolled courses show karo.
DB USED = STUDENT_MANAGEMENT
23. Courses ke saath unke department name show karo.
24. Students aur unke grades ke saath courses show karo.
25. Departments aur students count per department show karo.
26. Students aur courses ke average grade per student show karo.
27. Students aur unke latest enrolled course show karo.
28. Students ke saath unke total courses count show karo.
29. Departments ke saath total credits offered per department show karo.
30. Students aur courses ke saath grades = 'A' filter karo.
AGGREGATE FUNCTIONS
31. Students ka average age calculate karo.
32. Students per department ka count show karo.
33. Courses ka average credits per department nikal kar dikhao.
34. Maximum aur minimum student year show karo.
35. Total number of courses per department show karo.
36. Average grade per course show karo (use ASCII conversion if letters).
37. Students ke saath total credits enrolled calculate karo.
38. Students ke saath count of 'A' grades show karo.
39. Departments ke saath sum of course credits show karo.
40. Courses ke saath number of students enrolled show karo.
SUBQUERIES
41. Students jo most courses enrolled kar rahe hain, show karo.
42. Courses jo least students enrolled hain, show karo.
43. Students jo Computer Science aur year 3 mein hain, fetch karo.
44. Students jo average grade 'B' se upar le rahe hain, show karo.
45. Courses jo max credits wale hain, show karo.
46. Students ke liye department ke average year compare karo aur jo above average ho,
show karo.
47. Students ke liye latest DOB wale student ka naam fetch karo.
48. Students ke liye first enrolled course show karo.
49. Departments ke liye courses count > 2 show karo.
50. Students ke liye grade = 'A' ka percentage calculate karo
📌 ALTER / UPDATE / DELETE / INSERT Tasks
ALTER TABLE Tasks
1. Student table mein new column 'EmailVerified' add karo (BIT type).
2. Users table mein column 'MiddleName' add karo (VARCHAR(50)).
3. Course table mein column 'Description' add karo (VARCHAR(MAX)).
4. Teacher table mein column 'IsActive' add karo (BIT, default 1).
5. Exam table mein column 'DurationMinutes' add karo (INT).
6. Room table mein column 'BuildingName' add karo (VARCHAR(50)).
DB USED = STUDENT_MANAGEMENT
7. CourseOffering table mein column 'MaxStudents' add karo (INT).
8. Section table mein rename column 'NAME' to 'SectionName'.
9. Student table mein rename column 'YEAR' to 'CurrentYear'.
10. ExamMCQ table mein change column 'MARKS' type to FLOAT.
UPDATE Tasks
11. Sab students ka CGPA +0.2 karo.
12. Teacher jinka experience > 5, unka designation 'Senior Lecturer' set karo.
13. Sab courses jinke credits = 3, unko credits = 4 set karo.
14. Sab students ka Intake = '2022' update karo jinka department = 'CS'.
15. Exam jiska TITLE = 'Midterm AI', uska STATUS = 'Completed' set karo.
16. Room jiska name 'LT1', uska SeatsPerRow = 12 update karo.
17. Course jiska title 'Machine Learning', uska Category = 'Advanced' set karo.
18. Students jinka CGPA < 2.5, unka STATUS = 'At Risk' update karo.
19. Teacher jiska qualification = 'PhD', unka EXPERIENCE_IN_YEARS +1 karo.
20. Student jiska ID = 1, uska EmailVerified = 1 set karo.
DELETE Tasks
21. Delete student jiska CGPA < 2.0 ho.
22. Delete course jiska CourseCode = 'SE201' ho.
23. Delete teacher jiska experience < 2 years ho.
24. Delete department jiska ID = 3 ho.
25. Delete all exams jiska STATUS = 'Cancelled' ho.
26. Delete course enrollment jiska StudentID = 2 ho.
27. Delete sections jiska department = 2 ho.
28. Delete all rooms jinka TotalRows < 5 ho.
29. Delete all proctoring events jinka EventType = 'Screen' ho.
30. Delete students jo kisi course mein enrolled nahi hain.
INSERT Tasks
31. Naya student add karo jiska name, gender, dob, email, phone, section, CGPA, Intake,
Year fill ho.
32. Naya teacher add karo jiska userID, designation, qualification, experience fill ho.
33. Naya course add karo jiska CourseCode, Title, Credits, Category fill ho.
34. Nayi department add karo jiska name = 'IT' ho.
35. Naya room add karo jiska RoomName, TotalRows, SeatsPerRow fill ho.
36. Nayi section add karo jiska name, department fill ho.
37. Naya exam add karo jiska A_ID, Title, TotalQuestions, Date, StartTime, EndTime,
E_TYPE, Status fill ho.
38. Nayi course offering add karo jiska CourseID, Semester, Department, Year, Session
fill ho.
39. Nayi course allocation add karo jiska TeacherID, OfferingID, Section fill ho.
40. Nayi exam room assign karo jiska ExamID, RoomID, E_Date, E_Time fill ho.
DB USED = STUDENT_MANAGEMENT
Complex ALTER/UPDATE/DELETE Tasks
41. Sab students ka CurrentYear +1 karo (promotion).
42. Sab exams jinka E_DATE < GETDATE(), unka STATUS = 'Completed' set karo.
43. Delete sab voice monitoring events jo Transcript IS NULL ho.
44. Add column PhoneCountryCode in Users aur default '+92' set karo.
45. Sab courses jinke title mein 'AI' hai, unka Category = 'AI Core' update karo.
46. Delete sab students jo breaks > 3 ho (StudentBreak table).
47. Insert new proctoring event jiska EX_ID, S_ID, EventType fill ho.
48. Update all students ka CGPA = CGPA * 1.05 (extra credit adjustment).
49. Alter table ExamDescQues aur column MARKS ka default 5 set karo.
50. Delete all CameraMonitoring entries jahan IsStudentPresent = 0 ho.