1. The university rules allow an F grade to be overridden by any pass grade (A, B, C, D). Now, create a view that lists information about all fail grades that have not been overridden (the view should contain all attributes from the takes relation).

2. Find all students who have 2 or more non-overridden F grades as per the takes relation, and list them along with the F grades.

3. Grades are mapped to a grade point as follows: A:10, B:8, C:6, D:4 and F:0. Create a table to store these mappings and write a query to find the CPI of each student, using this table. Make sure students who have not got a non-null grade in any course are displayed with a CPI of null.

4. Find all rooms that have been assigned to more than one section at the same time. Display the rooms along with the assigned sections; I suggest you use a with clause or a view to simplify this query.

5. Create a view faculty showing only the ID, name, and department of instructors.

6. Create a view CSinstructors, showing all information about instructors from the Comp. Sci. department.

7. Insert appropriate tuple into each of the views faculty and CSinstructors, to see what updates your database allows on views; explain what happens.

8. Grant permission to one of your friends to view all data in your student relation.

9. Now grant permission to all users to see all data in your faculty view. Conversely, find a friend who has granted you permission on their faculty view, and execute a select query on that view

These are the relevant tables:

 
 

create table classroom (building room_number city primary key (building, room_number) ); varchar (15), varchar (7), numeric(4,0), create table department (dept_name building budget primary key (dept_name) ); varchar (20), varchar (15), numeric(12,2) check (budget > 0), create table course varchar (8), varchar (50), varchar (20), numeric(2,0) check (credits > 0), (course_id title dept_name credits primary key (course_id), foreign key (dept_name) references department on delete set null ); create table instructor (ID varchar(5), varchar (20) not null, varchar (20), numeric(8,2) check (salary > 29000), name dept_name salary primary key (ID), foreign key (dept_name) references department on delete set null ); create table section (course_id sec_id varchar(8), varchar (8), varchar (6) semester check (semester in ('Fall', 'Winter', 'Spring', 'Summer')), year numeric(4,0) check (year > 1701 and year < 2100), building room_number time_slot_id varchar (15), varchar(7), varchar (4), primary key (course_id, sec_id, semester, year), foreign key (course_id) references course on delete cascade, foreign key (building, room_number) references classroom on delete set null