Database System Concepts
- Avi Silberschatz
- Henry F. Korth
- S. Sudarshan
In this assignment, you will write more complex SQL queries, using the University schema by default. Again,
you may have to add required data to test your queries.
- 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).
- 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.
- 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.
- 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
- Create a view faculty showing only the ID, name, and department of instructors.
- Create a view CSinstructors, showing all information about instructors from the Comp. Sci.
- Insert appropriate tuple into each of the views faculty and CSinstructors, to see what updates
your database allows on views; explain what happens.
- Grant permission to one of your friends to view all data in your student relation.
- 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.