Tab 1

## DBMS Class Test (20 marks)

*Instructions:* Write the correct DDL query for each of the following statements.

1. Create a database named "University". (1 mark)

2. Create a table named "Students" with columns "StudentID" (int), "Name" (varchar(50)),
and "Age" (int). (1 mark)
3. Alter the "Students" table to add a new column "Email" (varchar(100)). (1 mark)
4. Drop the "Students" table. (1 mark)
5. Create a table named "Courses" with columns "CourseID" (int), "CourseName"
(varchar(50)), and "Duration" (int). (1 mark)
6. Rename the "Courses" table to "CourseDetails". (1 mark)
7. Alter the "CourseDetails" table to modify the "Duration" column to "CourseDuration"
(varchar(20)). (1 mark)
8. Create a table named "StudentGrades" with a foreign key constraint referencing the
"StudentID" column in the "Students" table. (1 mark)
9. Create a table named "Departments" with a primary key constraint on the "DepartmentID"
column and a unique constraint on the "DepartmentName" column. (1 mark)
10. Create an index named "idx_StudentID" on the "StudentID" column of the "Students"
table. (1 mark)
11. Create a table named "Faculty" with columns "FacultyID" (int), "FacultyName"
(varchar(50)), and "Department" (varchar(50)). (1 mark)
12. Alter the "Faculty" table to add a check constraint to ensure the "Department" column
only accepts specific values. (1 mark)
13. Create a table named "Subjects" with columns "SubjectID" (int), "SubjectName"
(varchar(50)), and "Credits" (int). (1 mark)
14. Alter the "Subjects" table to add a default value constraint for the "Credits" column. (1
mark)
15. Drop the "Subjects" table. (1 mark)
16. Create a table named "StudentAttendance" with a composite primary key consisting of
"StudentID" and "Date". (1 mark)
17. Alter the "StudentAttendance" table to add a foreign key constraint referencing the
"StudentID" column in the "Students" table. (1 mark)
18. Create an index named "idx_SubjectID" on the "SubjectID" column of the "Subjects"
table. (1 mark)
19. Alter the "CourseDetails" table to add a not null constraint on the "CourseName" column.
(1 mark)
20. Drop the index "idx_StudentID" from the "Students" table. (1 mark)
Tab 2
## DBMS Class Test (20 marks)
*Instructions:* Write the correct DDL query for each of the following statements.

1. Create a database named "University". (1 mark)

2. Create a table named "Students" with columns "StudentID" (int), "Name" (varchar(50)),
and "Age" (int). (1 mark)
3. Alter the "Students" table to add a new column "Email" (varchar(100)). (1 mark)
4. Drop the "Students" table. (1 mark)
5. Create a table named "Courses" with columns "CourseID" (int), "CourseName"
(varchar(50)), and "Duration" (int). (1 mark)
6. Rename the "Courses" table to "CourseDetails". (1 mark)
7. Alter the "CourseDetails" table to modify the "Duration" column to "CourseDuration"
(varchar(20)). (1 mark)
8. Create a table named "StudentGrades" with a foreign key constraint referencing the
"StudentID" column in the "Students" table. (1 mark)
9. Create a table named "Departments" with a primary key constraint on the "DepartmentID"
column and a unique constraint on the "DepartmentName" column. (1 mark)
10. Create an index named "idx_StudentID" on the "StudentID" column of the "Students"
table. (1 mark)
11. Create a table named "Faculty" with columns "FacultyID" (int), "FacultyName"
(varchar(50)), and "Department" (varchar(50)). (1 mark)
12. Alter the "Faculty" table to add a check constraint to ensure the "Department" column
only accepts specific values. (1 mark)
13. Create a table named "Subjects" with columns "SubjectID" (int), "SubjectName"
(varchar(50)), and "Credits" (int). (1 mark)
14. Alter the "Subjects" table to add a default value constraint for the "Credits" column. (1
mark)
15. Drop the "Subjects" table. (1 mark)
16. Create a table named "StudentAttendance" with a composite primary key consisting of
"StudentID" and "Date". (1 mark)
17. Alter the "StudentAttendance" table to add a foreign key constraint referencing the
"StudentID" column in the "Students" table. (1 mark)
18. Create an index named "idx_SubjectID" on the "SubjectID" column of the "Subjects"
table. (1 mark)
19. Alter the "CourseDetails" table to add a not null constraint on the "CourseName" column.
(1 mark)
20. Drop the index "idx_StudentID" from the "Students" table. (1 mark)