Question
Student
StudentId
First Name
Last Name
Address
City
State
Zip code
Semester
1256
Ahmad
Hassan
164 A block garden town
Lahore
Punjab
54000
Second
2435
Ali
Mirza
76 C officers colony
Lahore
Punjab
54000
Fourth
Employee
EmployeeId
First Name
Last Name
Address
City
State
Zip code
Designation
12345
Talha
Ali
267 F Defence
Lahore
Punjab
54000
Professor
32145
Hamza
umar
435 G Model Town
Lahore
Punjab
54000
Asst. Professor
65478
Salman
Ahmed
18 A cantt
Lahore
Punjab
54000
Lecturer
75674
Faiz
Hussain
16 B Defence
Lahore
Punjab
54000
Teacher Assistant
56783
Haneef
Raza
196D Shadman
Lahore
Punjab
54000
Registrar
87368
Kashif
Ali
231 A Gulberg
Lahore
Punjab
54000
Accountant
Write DDL statements to
a. Create view that showsstudent’s Id and their respective semesters
b. Grant the rights ofselecting and viewing the view, created in part a, to‘Teacher Assistants’
c. Create view that showsteachers Id, address, city, state, zipcode and designation.
d. Grant the access rights(which includes selection and updation) of the view, created inpart c, to the employee ‘Registrar’
StudentId
First Name
Last Name
Address
City
State
Zip code
Semester
1256
Ahmad
Hassan
164 A block garden town
Lahore
Punjab
54000
Second
2435
Ali
Mirza
76 C officers colony
Lahore
Punjab
54000
Fourth
Explanation / Answer
Dear, The SQL statements for the given problemare, a) Create VIEW V_Student AS SELECT StudentId,Semester FROM Student; To view this VIEW the sqlstatement is, SELECT * FROM V_Student; b) GRANT SELECT ON V_Student TO TeacherAssistants If you wish to givea user the option of granting a privilege to another user, add [WITH GRANT OPTION ] to the GRANT statement. c) Create VIEW V_Employee AS SELECT EmployeeId,address, city, state, zipcode, design FROM Employee wheredesignation = 'Teacher' To view thisVIEW the sql statement is, SELECT * FROM V_Employee; d) GRANT SELECT, UPDATE ON V_Employee TORegistrar " I hope this will help you "