---
title: Database Systems Lab 4 v0.1
---
<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 an outer join, and should not use subqueries.

2. Find the names of all students who have taken at least 2 courses taught by Mr. ‘Einstein’.
Insert the following rows to test your query.

insert into course values ('PHY-102','Physical Principles 2','Physics', 4);
insert into course values ('PHY-201','Fluids','Physics', 4); 
insert into "section" values ('PHY-102','1','Spring', 2010,'Painter','514','A');
insert into "section" values ('PHY-201','1','Summer', 2009,'Painter','514','B');
insert into teaches values ('22222','PHY-102','1','Spring', 2010);
insert into teaches values ('22222','PHY-201','1','Summer', 2009); 
insert into takes values ('44553','PHY-102','1','Spring', 2010);
insert into takes values ('44553','PHY-201','1','Summer', 2009);

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. Increase the salary of each instructor in the Comp. Sci. department by 10%.

### 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>