---
title: Database Systems Lab 5 (More Questions)
---
<h1 style='border: none'><center>Database Systems Lab 5</center></h1>
<h1 style='border: none'><center>Intermediate SQL(More Questions)</center></h1>
<h5><center>The Islamic University of Gaza<br>Engineering Faculty<br>Department of Computer Engineering</center></h5>
<h6>Authors: Usama R. Al Zayan & Rasha E. Kahil<span style="float:right">2023/03/17</span></h6>

---

### More Questions

1. Come up with a scenario in which you will need to revoke a privilege from `public`.

1. The university rules allow an F grade to be overridden by any pass grade (A, B, C, D).
a. 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).
b. 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.
2. Trying more view operations:
a. Create a view ‘faculty’ showing only the ID, name, and department of instructors.
b. Also create a view ‘CSinstructors’ showing all information about instructors from the Computer Science department.
c. Insert appropriate tuples into each of the views ‘faculty’ and ‘CSinstructors’, to see what updates your database allows on views; explain what happens.
3. Trying out authorization:
a. Create users for one of your friends, grant permission to him to view all data in your student relation.
b. Grant permission to all users to see all data in your faculty view.
c. Create a role to represent a ‘homework_helper’ who will help you with your current homework. Give this role the permissions to create views, and answer question 4 below.
d. Grant homework_helper to all teachers and friends.
e. Your supervisor forbids that students help each other at all. Do what is necessary to make your web_events table accessible only by yourself and instructors, others are not allowed.
4. Using the web_events dataset provided, answer the following questions.
a. Show the 3 categories that are viewed the least.
b. Show the product that is purchased the most on Fridays (المنتج المفضل يوم الجمعة)
c. For every product, count the views this product has got in the first day it ever got a view.
For example if product ‘23’ got it’s first view on 13-10 show only the view count of that day
d. For each category find the product that is sold the most (by count) and how much did it bring in sales.
e. For each week, count how many times a product got ‘cart’-ed then removed from cart, then purchased, all during that week (show the total count of how many times did this happen)
f. [bonus]For each product, find the average number of views (view events) a customer needs to make before he purchases that product.
We are not interested in views that do not lead to a purchase, or views that come after the purchase of the product. You have to count the views a customer makes before the purchase.
You should use all your tools to answer this one. Create any necessary views and review any necessary topic. You can only use functions and topics covered in the labs 3-5. No need to look further.

###### tags: `Database Systems` `IUG` `Computer Engineering`

<center>End Of Lab 5</center>