---
title: Database Systems Lab 4 v0.4
---
<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 students (names and IDs), showing each student and the number of sections he takes. Make sure to show the number of sections as 0 for studennts who have not taken any section. Your query should use an outer join, and should not use subqueries.
2. Find the names of all students who have taken all the courses thought by Mr. ‘Einstein’.
3. Find the sections that had the maximum enrollment in Fall 2009.

### 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. Insert every student whose tot cred attribute is greater than 130 as an instructor in the same department, with a salary of $3,000.

### Task 3 (10 marks)

<p style="text-align:justify">
If you remember, in lab task 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>