Course's Name: Database Palestine Technical University-Kadoorie Instructor’s Name: Eng.
Haya
Systems Lab Abu-Raed
Course's Number: 12140314 Student’s Name: ______________
Exam’s Period: 2 hours Student’s Number: ____________
Number of Pages: 3 Section’s Number: 1
Midterm Exam
Total Mark: 35 First Semester 2024-2025 Exam’s Date: 18/11/2024
Question 1: using the following tables, answer the following sections with the
suitable SQL statements:
Table 1 Sample Data - Students Table
ID FirstName LastName EnrollmentDate GPA
202311424 Noor Mohammad 2023-09-01 3.7
202312589 Ali Hasan 2023-09-01 3.9
202311689 Fatima Khalid 2023-09-01 3.5
202311248 Ahmad Omar 2023-09-02 4.0
Table 2 Sample Data - Courses Table
ID Name Credits
1 Database Systems 3
2 C++ 4
3 Operating Systems 3
Table 3 Sample Data - Enrollments Table
ID StudentID CourseID EnrollDate
1 202311424 1 2024-09-05
2 202311248 3 2024-09-06
3 202311689 3 2024-09-09
Section A:
1- Create a user named as student_ [your name] (1 mark)
2- Give your user all the privileges you might need. (1 mark)
Page 1 of 3
Section B:
1- Create the previous three tables (STUDENT, COURSES, and ENROLLMENTS) with appropriate
data types, constraints, and relationships between them, ensuring the following: (10 marks)
o The student’s ID does not exceed 9 digits as shown in the sample data.
o The student’s first name and course’s name cannot be empty.
o The student’s last name must allow a maximum of 100 characters.
o There must be no duplicates in the courses’ names.
o The student’s GPA consists of two digits as shown in the sample date.
2- Add a new column Email (max 100 characters) to the STUDENTS table. (1 mark)
3- Rename the EnrollmentDate column to JoinDate in the STUDENTS table. (1 mark)
4- Remove the EnrollDate column from the ENROLLMENTS table. (1 mark)
5- Change the LastName column to allow a maximum of 50 characters instead of 100. (2 marks)
6- Add a default value of 3 to the Credits column in the Courses table. (1 mark)
7- Add a CHECK constraint named CHK_GPA that ensures GPA is between 0.0 and 4.0 (2 marks)
8- Rename the table ENROLLMENTS to STUDENTS_ COURSES. (1 mark)
9- Remove the constraint CHK_GPA from the STUDENTS table. (1 mark)
Page 2 of 3
10- Require the JoinDate column (originally EnrollmentDate) to automatically store the
current date if no value is provided. (1 mark)
11- Create a new user from your account named registerer and give him the suitable privileges
to retrieve, modify and add new students. (2 marks)
12- Remove the privilege to modify existing students from registerer. (1 mark)
Section C:
1- Remove the registerer user. (1 mark)
2- Change the password to password456 of your student_user account then lock it. (2 marks)
Question 2: answer the following questions
1- In your own words, explain the difference between the DROP and TRUNCATE commands
and their impact on database. (2 marks)
2- There is a DML command that can be used to achieve a similar effect as
the TRUNCATE command. Identify this command and explain the difference between them?
(2 marks)
3- What SQL statement would you advise Omar to use if he is sure that he created a table called
Authors, but he still gets the error: table or view does not exist? (1 mark)
Page 3 of 3
