---
title: Database Systems Lab 3 v0.2
---
<h1 style='border: none'><center>Database Systems Lab 4</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 3 | 10 min |
| Task 3 | 15 min |
| Task 4 | 30 min |

## Lab Tasks

### Task 3 (10 marks)

1. Display course title and credit concatenated as a format of "Course _ Credit".
2. Find the longest department name which is a single word.
3. Find total credits of all courses that contain ‘programming’ in its titles.
4. Find name of each student or instructor who didn’t take/teach any course in Fairchild building.

### Task 4 (10 marks)

1. For each department, find the number of instructors who teach any course in spring semesters in years between 2007 and 2010.
2. Find the name of the department whose budget is greater than total instructors-salaries by $150000 or more.
3. Suppose you are given a relation grade_points(grade, points) that provides a conversion from letter grades in the takes relation to numeric scores; for example, an “A” grade could be specified to correspond to 90 points, an “A−” to 85 points, a “B+” to 80 points, a “B” to 75 points, and so on. The grade_points earned by a student for a course offering (section) is defined as the number of credits for the course multiplied by the numeric points for the grade that the student received.
Given the preceding relation, and our university schema, write each of the following queries in SQL. [You may assume for simplicity that no takes tuple has the null value for grade.]
* Find the total grade points earned by the student with ID '12345', across all courses taken by the student.
* Find the grade point average (GPA) for the above student, that is, the total grade points divided by the total credits for the associated courses.
* Find the ID and the grade-point average of each student.

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