---
title: Database Systems Lab 4 (More Questions)
---
<h1 style='border: none'><center>Database Systems Lab 4</center></h1>
<h1 style='border: none'><center>More 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/07</span></h6>

---

### More Questions

1. Display a list of all instructors (names and IDs), for each instructor show the number of physics courses he taught. Make sure to show the number of course as 0 for instructors who have not taught any physics courses.
* Do it without a subquery.
* Do it using a subquery.
2. Find the enrollment across all sections, but now also include sections with no students taking them; the enrollment for such sections should be treated as 0. Do this in two different ways (and create required data for testing).
3. Find all courses whose identifier starts with the string "CS-1" then:
* find instructors who have taught all the above courses
* Using the "not exists ... except ..." structure
* Try “matching of counts” i.e. make sure the count of two sets you are comparing is the same. (don't forget the distinct clause!).
4. Insert each instructor as a student, with tot_creds = 0, in the same department.
Now delete all the newly added "students" above (important note: already existing students who happened to have tot_creds = 0 should not get deleted).
5. Update the salary of each instructor to 10000 times the number of course sections they have taught.
6. 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 to simplify this query.
7. Create your own query: define what you want to do in English, then write the query in SQL. Make it as difficult as you wish, the harder the better.

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

<center>End Of Lab 4</center>