Q1: Create the following tables with the given attribute data types and constraints: (20 marks)

Student

id	Number	Primary Key
name	Variable character of 15	Not null
major	Variable character of 20
DOB	Date
Address	Variable character of 20

Course

id	Number	Primary Key
name	Variable character of 15	Not null
Credits	Number	Should be larger than 0 and less than 4

Enrolled

Stid	Number	Foreign Key to student id	Both define primary key for enrolled Table
Cid	Number	Foreign Key to course id
Grade	Number	Should be larger than or equal 0 and less than or equal 100

Q2: Write SQL Commands to do the following: (3 marks each, 12 marks)

1. Add column Tel to student table, which should contain 12 numbers, starts with 962 and be unique.
2. Add column PreRequest to Course table, which should be a foreign key to course id column from the same table.
3. Add constraint on Grade column in table Enrolled to ensure the value should be between 0 - 100.
4. Delete address column from student table.

Q3: Write SQL commands to insert the following records in each table: (4 marks each, 12 marks)

Student

Stid	name	major	DOB	Tel
123	Ahmad	CS	1/1/1995	9627912345678
456	Mona	SE	5/5/1995	962775566778
789	Omar	CS		962781231231
1212	Sara	Engineering	4/4/1997	962799630000

Course

Cid	name	Credits	PreRequest
201	Skills	1
202	C++	3
203	Data Structure	4	202
204	DB	4	202

Enrolled

Stid	Cid	Grade
123	201	90
123	202	80
456	201	90
789	201	70
789	202	80
789	203	70

Q4: Write the SQL commands to get the following queries:

1. Get the student id, and his/her grade, the grade should be in the following format:

Grade >= 90, print A (4 marks)

Grade >= 80 and Grade < 90, print B

Grade <80, print C

1. Retrieve all course information for all courses that has a preRequest course number, show course names in capital letters and order the results descending based on course id. (6 marks)

1. Retrieve the student id, name, major, and GPA (average grades) for all students. (4 marks)

1. Retrieve students information for all students in the following format: (6 marks)

Student Ahmad is born on January and is studying CS

1. Create a view to list student id, student name, course id, course name, student major, and student grade. (6 marks)

Q5: Write the SQL commands to get the following queries: (6 marks each, 30 marks)

1. Retrieve course name and credits, and its preRequests name and credits for courses that has preRequest courses only.

1. Retrieve student name and course name for all courses he/she finished and the grade larger than 75.

1. Retrieve student name and major, course id, and grade for all students whether they finished courses or not.

1. Retrieve course name and credits, and its preRequests name and credits for all courses whether it has a preRequest course or not.
2. Retrieve course id and course name for all courses that has students enroll on them (you must use EXIST keyword).