Database Management System Lab
CIS · Final · Fall 24
Batch 17_B
Read the questions as text
Read off the PDF by AI, so it may have mistakes. The PDF is the original.
Lab Final Examination: Fall-2024
Course Code: CIS 222L (Batch: 17_B)
Course Title: Database Management System Lab
Date: 12/12/2024
Time: 1 Hour
Total Marks: 40
Consider a University Enrollment System.
Student ID | Student Name | Course ID | Course Name | Teacher | Department | Department Head
231-16-001 | Fahim | CIS 222 | DBMS | Mr. Mehedi | CIS | Md. Sarwar Hossain Mollah
231-16-001 | Fahim | CIS122 | DS | Mr. Himel | CIS | Md. Sarwar Hossain Mollah
231-16-002 | Maruf | CIS 222 | DBMS | Mr. Mehedi | CIS | Md. Sarwar Hossain Mollah
231-16-002 | Maruf | CIS 131 | CAO | Mr. Israfil | CSE | Dr. Sheak Rashed Haider Noori
231-16-003 | Parvez | CIS 313 | AI | Mr. Mizan | CSE | Dr. Sheak Rashed Haider Noori
231-16-004 | Naim | IoT325 | EDC | Mr. Nafees | SWE | Dr. Imran Mahmud
Final Structure After Normalization (Up to 3NF)
Table: Enrollment
Student ID | Student Name | Course ID
231-16-001 | Fahim | CIS 222
231-16-001 | Fahim | CIS122
231-16-002 | Maruf | CIS 222
231-16-002 | Maruf | CIS 131
231-16-003 | Parvez | CIS 313
231-16-004 | Naim | IoT325
Table: Departments
Department | Department Head
CIS | Md. Sarwar Hossain Mollah
CSE | Dr. Sheak Rashed Haider Noori
SWE | Dr. Imran Mahmud
Table: Courses
Course ID | Course Name | Teacher | Department
CIS 222 | DBMS | Mr. Mehedi | CIS
CIS122 | DS | Mr. Himel | CIS
CIS 131 | CAO | Mr. Israfil | CSE
CIS 313 | AI | Mr. Mizan | CSE
IoT325 | EDC | Mr. Nafees | SWE
Task 1 – Basic Structure (16 Marks)
Q1. Create the table and insert data as per the normalized table mentioned above under the "University Enrollment System" database.
Task 2 – SQL Query (6x4=24 Marks)
Q2. Find the students whose names have 'r' as the third character.
Q3. Count the total number of students
Q4. Retrieve the number of courses taught by each teacher
Q5. Count the Number of Students Enrolled in Each Course
Q6. Find the Course with the Most Enrollments
Q7. Find students enrolled in courses with IDs starting with 'CIS'