More SQL Features

1. More SQL Features • Alter Table • Grant • Revoke • Outer Join

2. Alter Table • Add a column called major to the Student Table. The data type for major is varchar(20) • alter table Student add column major varchar(20) • Remove a column called password from the Student Table • Alter table Student drop column password

3. Grant • Give insert and delete privileges on the Student table to a user named janeDoe. • grant insert, delete on Student to janeDoe • Give alter table privileges on the Student table to a user named johnDoe. • grant alter on Student to johnDoe • Allow a user named johnSmith to grant privileges on the Student table to other users • Grant grant option on Student to johnSmith

4. Revoke • Remove delete privileges on the Student table from user janeDoe • Revoke delete on Student from janeDoe • Remove alter table privileges on the Student table from user johnDoe • Revoke alter on Student from johnDoe

5. Outer Join • For each each faulty member find the courses that the faculty member taught in Spring 2012. The result should be tuples of the form (ID, Name, CrsCode) include faculty who did not teach any classes in Spring 2012. • Select F.Id, F.Name, CrsCode from Faculty F left join Class C on ( F.Id = C.InstructorId) where Semester = ‘Spring’ and Year = 2012

6. Outer Join • Right join • Full join