---
title: Database Systems Lab 3 v0.1
---
<h1 style='border: none'><center>Database Systems Lab 3</center></h1>
<h1 style='border: none'><center>Basic 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/02/07</span></h6>

---

## Expecteed outcomes
1. To be introduced to the supported and most used data types in PostgreSQL.
2. To be familiar with syntax and semantic rules of PostgreSQL constructs.
3. To practice SQL basics.
4. To build different structured queries for given natural language queries.

## Lab 2: Time and Plan

| Tasks | Timing |
| -------- | -------- |
| Quiz 2 | 10 min |
| Task 1 | 15 min |
| Task 2 | 20 min |

## Lab Tasks

### Task 1 (5 marks)

1. Create a movie database. Create three tables: one for actors (AID, name), one for movies (MID, title), and one for actor_role (MID, AID, rolename).
Use appropriate data type for each attribute, and add appropriate primary/foreign key constraints.
2. Insert data to the above tables (approx. 3 to 6 rows in each table), including data for actor "Charlie Chaplin", and for yourself (using your roll number as ID).

### Task 2 (5 marks)

1. Update the budget of `Biology` department to 272574.50.
2. Find names of all students & instructors who take/teach any course in Fairchild building.
(remove duplicates)
3. Find the lowest budget of any department. (without aggregation)
4. For all students who have taken some courses, display their names, IDs and the course ID of all courses they have taken, the semester & year of each course. In descending years order, then ascending alphabetical order for names with descending IDs order.

## Bonus
1. Display the names of all instructors who taught for 2 consecutive semesters in any time period.
2. Insert every student, who has finished at least 130 credits with grade of ‘A+’ in more than 80% of his courses, as an instructor in the “Outstanding” department, with a salary of $14,000.

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

<center>End Of Lab 3</center>