---
title: Database Systems Lab 6 Part I
---
<h1 style='border: none'><center>Database Systems Lab 6 Part I</center></h1>
<h1 style='border: none'><center>Functions & procedures</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/24</span></h6>

---

## Expecteed outcomes
* To be introduced to Database Functions commands and concepts.
* To learn how to create and delete different types of Functions in PostgreSQL.
* To learn how to use Functions in PostgreSQL.

## Lab 5: Time and Plan

| Tasks | Timing |
| -------- | -------- |
| Quiz 6 | 10 min |
| Task 1 | 40 min |
| Task 2 | 40 min |

## Lab Tasks

### Task 1: SQL functions (10 marks)

1. Write a function [more_sal_inst] that takes a department name and returns names of all instructors that earn more than the instructors in that department.
* using return setof
* using return table
2. Write a function [emp_studnet] that takes the department name, number of credit hours, salary and makes all the students in that department who finished more than the number of credit hours as instructor with the entered salary.
3. Write a function [dep_avg_sal] that takes the department name as input, and return the average salary for that department.
Then write another function [dep_higher_avg_sal] that uses the past function which also takes the department name and returns all the departments which the avg salary of their instructors is more than the avg salary for instructors for the entered department.

### Task 2: PL/pgSQL functions (5 marks)

* Write a function that take student id and return in which level he is, depending on his total credits, [fifth >= 120, fourth >= 90, third >= 60 ,second >= 30, else first]
Then Alter student table and add another column [Level varchar].
Then write another function that uses the first function and takes the department name and for each student in that department it inserts his level.

### (More Questions)

1. Write a function [teatching_load] that takes an instructor id as an argument and finds the sum of all course hours(credits in our case)that instructor taught.
Then write another function that uses the first one, to update instructor salary with this equation past salary+ [past salary * sum of his course hours *0.001].

2. Write your own question and answer it to create a PL/pgSQL function, which should have expression and loop.

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

<center>End Of Lab 6 Part I</center>