UNIVERSITY OF WEST ATTICA
SCHOOL OF ENGINEERING
DEPARTMENT OF COMPUTER ENGINEERING AND INFORMATICS

University of West Attica · Department of Computer Engineering and Informatics

---

Databases I

Classification and Suggestions - GROUP BY, AND, HAVING, JOIN

Vasileios Evangelos Athanasiou
Student ID: 19390005

GitHub · LinkedIn

---

Supervision

Supervisor: Periklis Andritsos, Professor

UNIWA Profile · LinkedIn

Co-supervisor: Anastasios Tsolakidis, Assistant Professor

UNIWA Profile · LinkedIn

---

Athens, June 2023

---

---

README

Classification and Suggestions - GROUP BY, AND, HAVING, JOIN

The objective of this task is to strengthen practical skills in SQL querying, focusing on data classification, aggregation, and relational joins using a structured personnel database.

---

Table of Contents

Section	Folder / File	Description
1	assign/	Assignment material
1.1	assign/assignment_03.pdf	Assignment description (English)
1.2	assign/εργασία_03.pdf	Assignment description (Greek)
2	docs/	Theoretical documentation
2.1	docs/Classification-Join-Tables.pdf	Table classification and JOIN operations (English)
2.2	docs/Ταξινόμηση-Join-Συνδέσεις.pdf	Table classification and JOIN operations (Greek)
3	README.md	Project documentation
4	INSTALL.md	Usage instructions

---

1. Database Schema

The project utilizes a database named new_personnel, which consists of four primary tables:

• DEPT
  Stores department information, including department number, name, and location.

• EMP
  Contains employee records such as job titles, hire dates, salaries, commissions, and manager IDs.

• PROJ
  Holds project codes and project descriptions.

• ASSIGN
  A junction table linking employees to projects, including the time spent on each project.

---

2. Key SQL Operations Included

The assignment covers a wide range of essential SQL functionalities:

2.1 Data Selection & Sorting

• Use of ORDER BY to organize employee lists based on:
  • Commission
  • Job position
  • Salary

---

3. Aggregation

• Application of GROUP BY and HAVING to:
  • Calculate average salaries per department
  • Filter results based on employee count conditions

---

4. Date Functions

• Calculation of employee service years using:
  • DATEDIFF
  • FORMAT
• Reference date used: 2020-04-15

---

5. Table Joins

• Equi-Joins
  Linking employees to their respective departments and projects.

• Self-Joins
  Joining the EMP table to itself to identify employee–manager relationships.

• Multiple Joins
  Connecting EMP, ASSIGN, and PROJ tables to identify employees working more than 50 hours on specific projects.

---

6. Sample Result Set

As an example, the self-join operation that maps employees to their managers produces the following structure:

Department	Manager	Employee
ACCOUNTING	ELMASRI	CODD
ACCOUNTING	ELMASRI	DATE
ACCOUNTING	ELMASRI	ELMASRI
SALES	NAVATHE	NAVATHE