---
title: Database Systems Lab 4 v0.2
---
<h1 style='border: none'><center>Database Systems Lab 4</center></h1>
<h1 style='border: none'><center>More SQL</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>

---

## Expecteed outcomes
* To be familiar with all types of joins in SQL.
* To practice writing and understanding queries that employ subqueries.
* To use your newly acquired knowledge to write rich and complex DML.
* To familiarize yourself with Dates in SQL.

## Lab 4: Time and Plan

| Tasks | Timing |
| -------- | -------- |
| Quiz 4 | 10 min |
| Task 1 | 30 min |
| Task 2 | 30 min |
| Task 3 | 30 min |

## Lab Tasks
All the following tasks should run on `uni-space` schema.

### Task 1 (10 marks)

1. Display a list of all instructors (names and IDs), showing each instructor and the number of sections he taught. Make sure to show the number of sections as 0 for instructors who have not taught any section. Your query should use a scalar subquery, not using outer join.
2. Find students who have more tot_cred than all ‘Finance’ students.
3. Find the department that has students with the highest “Avg total credits”.

### Task 2 (10 marks)

4. Some of you may have noticed that the tot_creds value for students did not match the credits from courses they have taken. Write and execute query to update tot_creds based on the credits passed, to bring the database back to consistency. (This query is provided in the slides and the book.)

5. Delete all courses that have never been offered (i.e., do not occur in the section relation).

### Task 3 (10 marks)

<p style="text-align:justify">
If you remember, in lab work 4 of our pervious lab (Lab3) we created a new relation grade_points(grade, points) that provided a conversion from letter grades in the takes relation to numeric scores. We used it then to calculate the GPA for our students.
</p>

With this relation in hand, and with your good knowledge of SQL queries, subqueries, and DML. Write the SQL necessary to do the following tasks:

1. Oh! We found out that the mapping of letters to points in our lab3 answers here was wrong. We should add 5 to all points so that A+ maps to 100, A to 95, and so on. I guess you should do that for us using SQL. By the way; F should map to 40, no need to change that one.
2. Create a new relation graduate(ID, name, GPA, general_grade) and insert all students who finished 100 credits or more into this relation, calculating their correct GPA and assigning them the correct general_grade based on this GPA. A general_grade can be ( ‘Excellent’, ‘Very Good’, ‘Good’, ‘Acceptable’ and ‘Not Acceptable’ ) assigned in the usual way.
3. Find the percentage of graduates with each grade.
4. Find the top graduate(s) of each department.

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

<center>End Of Lab 4</center>