Database Fundementals October, 2023

SQL Intake 44

Company Database Schema

Here is the schema of a company database, please implement it on any RDBMS

you like and then try to create the following requests (queries):

Employee:
Fname Lname SSN BDATE Addresss Gender Salary Superssn Dno
Ahmed Ali 112233 1/1/1965
15 Ali fahmy M 1300 223344 10
St.Giza
Kamel Mohamed 223344 15/10/1970 38 Mohy el dien M 1800 321654 10
abo el Ezz
St.Cairo
Hanaa Sobhy 123456 18/3/1973 38 Abdel Khalik F 800 223344 10
Tharwat St.
Downtown.Cairo
Amr Omran 321654 14/9/1963 44 M 2500 null null
Hilopolis.Cairo
Noha Mohamed 968574 1/2/1975 55 Orabi St. El F 1600 321654 20
Mohandiseen .Cai
ro
Edward Hanna 512463 19/8/1972 18 Abaas El M 1500 321654 30
3akaad St. Nasr
City.Cairo
Mariam Adel 669955 12/6/1982 269 El-Haram st. F 750 512463 20
Giza
Maged Raoof 521634 6/4/1980 18 Kholosi M 1000 968574 30
st.Shobra.Cairo

Department
Dname DNumber MGRSSN MGRStartdate

DP1 10 223344 1/1/2005

DP2 20 968574 1/3/2006
DP3 30 512463 1/6/2006
Database Fundementals October, 2023
SQL Intake 44

Works for
ESSN Pno Hours
223344 100 10
223344 200 10
223344 300 10
112233 100 40
968574 400 15
968574 700 15
968574 300 10
669955 400 20
223344 500 10
669955 700 7
669955 300 10
512463 500 10
512463 600 25
521634 500 10
521634 600 20
521634 300 6
521634 400 4

Project
Pname Pnumber Plocation City Dnum
AL Solimaniah 100 Cairo_Alex Road Alex 10
Al Rabwah 200 6th of October Giza 10
City
Al Rawdah 300 Zaied City Giza 10
Al Rowad 400 Cairo_Faiyom Giza 20
Road
Al Rehab 500 Nasr City Cairo 30
Pitcho american 600 Maady Cairo 30
Ebad El 700 Ring Road Cairo 20
Rahman

Dependent
ESSN Dependent_name Gender Bdate
123456 Hala Saied Ali F 18/10/1970
223344 Ahmed Kamel M 27/3/1998
Shawki
223344 Mona Adel F 25/4/1975
Mohamed
321654 Ramy Amr M 26/1/1990
Omran
321654 Omar Amr Omran M 30/3/1993
321654 Sanaa Gawish F 16/5/1973
512463 Sara Edward F 15/9/2001
512463 Nora Ghaly F 22/6/1976
Database Fundementals October, 2023
SQL Intake 44

Queries:
1. Display the employee First name, last name, Salary and
Department number in department 30 whose salary from 1000
to 2000 LE monthly

2. Display all the projects names, locations and the department

number which is managed by it.

3. If you know that the company policy is to pay an annual

commission for each employee which specific percent equals 10% of
his/her annual salary .
Display each employee full name and his annual commission in
an ANNUAL COMM column (alias).

4. Display the employees Id, fname who earns more than

1000 LE.

5. Display the names and salaries of the female employees

Database Fundementals October, 2023
SQL Intake 44

6. Display each department id, name which managed by a

manager with id equals 968574.

7. Display the project id, name and location of the projects

which
controlled with department 10 or 20,
and location of the projects in Cairo or Alex city

8. Display the Projects name, locations of the projects with a

project name starts with "p" letter.

Data Definition Language:

Create the dependent table and create the relationship with employee table
Then Insert 3 rows only
Database Fundementals October, 2023
SQL Intake 44

Data Manipulating Language:

1. Insert new employee with your personal data, you will be in
department number 30, your SSN = 666666 & Superssn =
112233.

2. Insert another employee with personal data your friend as

new employee in department number 30 , SSN = 555555,
but don’t enter any value for salary or manager number to
him.

3. In the department table insert new department called "DEPT

IT" , with id 100, MgrSSN = 112233 as a manager for this
department. The start date for this manager is
'08/12/2006'

4. Do what is required if you know that : Mrs.Noha Mohamed

moved to be the manager of the new department (id =
100), and they give her position to your friend
Steps:
a. Your friend will be the manager of Dept 20 (her previous
department)
b. Update her record in your database to be a manager of
department 100
c. Update your friend data, add yourself as a supervisor
for him (supervised by you)
Database Fundementals October, 2023
SQL Intake 44

5. Unfortunately the company ended the contract with Mr.Kamel

Mohamed so try to delete his data from your database, in
case you need, you can be temporary in his position.

Upgrade your salary by 20 present of its last value.