Question

Use the Database “Company shown on pages 7 to 9 to answer the questions (a) to (g):

1. Retrieve both the names and social security numbers of employees who do not work on any one of projects with the project number 1, 2, and 3. (Show the query and output)

2. Retrieve the social security number and the name of each employee, and the corresponding social security number and the name of his/her supervisees if any. (Otherwise, just display the employee's social security number and name.) (Show the query and output)

3. Retrieve the social security numbers of all direct supervisees of James E.

Borg. (Show the query and output)

4. Retrieve all of direct and indirect supervisees of James E Borg in the employee table. (Show the query and output)

5. Find the social security numbers of employees who have no dependents. (Show the query and output)

6. Calculate the total number of employees, the average salary, and the total salary of department 5. (Show the query and output)

7. Retrieve the number of employees, the average salary, and the total salary of each

department that has more than one employee.

DDL Statements to Create Tables the Database “Company”

To create EMPLOYEE table:

create table EMPLOYEE

(

name varchar2(19) not null,

ssn char (9),

bdate date,

sex char(3),

salary number(8,2),

superssn char(9),

dno varchar(8),

constraint empPK

primary key (ssn),

constraint empsuperFRK

foreign key (superssn)

references employee(ssn) disable

);

To create DEPARTMENT table:

create table DEPARTMENT

(

dname varchar2(15) not null,

dnumber varchar(8),

mgrssn char(9),

mgrstardate date,

constraint departPK

primary key (dnumber),

constraint departUK

unique (dname),

constraint departFRK

foreign key (mgrssn)

references employee (ssn) on delete cascade disable

);

To create DEPTLOCATION table:

create table DEPTLOCATION

(

dnumber varchar(8),

dlocation varchar2(15),

constraint dlocPK

primary key (dnumber, dlocation),

constraint dlocnoFRK

foreign key (dnumber)

references department (dnumber) on delete cascade disable

);

To create PROJECT table:

create table project

(

pname varchar2(15) not null,

pnumber varchar(8),

plocation varchar2(15),

dnum varchar(8),

constraint projUK

unique (pname),

constraint projPK

primary key (pnumber),

constraint projFRK

foreign key (dnum)

references DEPARTMENT(dnumber)

);

To create WORKSON table:

create table WORKSON

(

essn char(9),

pno varchar(8),

hours number(5,1),

constraint workPK

primary key (essn, pno),

constraint workssnFRK

foreign key (essn)

references EMPLOYEE(ssn) on delete cascade disable,

constraint workpnoFRK

foreign key (pno)

references PROJECT(pnumber) on delete cascade disable

);

To create DEPENDENT table:

create table DEPENDENT

(

essn char(9),

dependentname varchar2(15),

sex char(3),

bdate date,

relationship varchar2(12),

constraint depenPK

primary key (essn, dependentname),

constraint depenFRK

foreign key (essn)

references EMPLOYEE (ssn) on delete cascade disable

);

DML Statements to Insert Data into Tables:

Insert the Data into the DEPARTMENT Table:

insert into DEPARTMENT values ('Research','5','333445555','22-MAY-78');

insert into DEPARTMENT values ('Administration','4','987654321','01-JAN-85');

insert into DEPARTMENT values ('Headquarters','1','888665555','19-JUN-71');

Insert the Data into the EMPLOYEE Table:

insert into EMPLOYEE values ('John B Smith','123456789','09-JAN-55','M',30000,'333445555','5');

insert into EMPLOYEE values ('Franklin T Wong','333445555','08-DEC-45','M',40000,'888665555','5');

insert into EMPLOYEE values ('Alicia J Zelaya','999887777','19-JUL-85','F',25000,'987654321','4');

insert into EMPLOYEE values ('Jennifer S Wallace','987654321','20-JUN-31','F',43000,'888665555','4');

insert into EMPLOYEE values ('Ramesh K Narayan','666884444','15-SEP-52','M',38000,'333445555','5');

insert into EMPLOYEE values ('Joyce A English','453453453','31-JUL-62','F',25000,'333445555','5');

insert into EMPLOYEE values ('Ahmad V Jabbar','987987987','29-MAR-59','M',25000,'987654321','4');

insert into EMPLOYEE values ('James E Borg','888665555','10-NOV-27','M',55000,' ','1');

Insert the Data into the DEPTLOACITON Table:

insert into deptlocation values ('1','Houston');

insert into deptlocation values ('4','Stafford');

insert into deptlocation values ('5','Bellaire');

insert into deptlocation values ('5','Sugarland');

insert into deptlocation values ('5','Houston');

Insert the Data into the PROJECT Table:

insert into project values ('ProductX','1','Bellaire','5');

insert into project values ('ProductY','2','Sugarland','5');

insert into project values ('ProductZ','3','Houston','5');

insert into project values ('Computerization','10','Stafford','4');

insert into project values ('Reorganization','20','Houston','1');

insert into project values ('Newbenefits','30','Stafford','4');

Insert the Data into the DEPENDENT Table:

insert into dependent values ('333445555','Alice','F','05-APR-76','Daughter');

insert into dependent values ('333445555','Theodore','M','25-OCT-73','Son');

insert into dependent values ('333445555','Joy','F','03-MAY-48','Spouse');

insert into dependent values ('987654321','Abner','M','29-FEB-32','Spouse');

insert into dependent values ('123456789','Michael','M','01-JAN-78','Son');

insert into dependent values ('123456789','Alice','F','31-DEC-78','Daughter');

insert into dependent values ('123456789','Elizabeth','F','05-MAY-57','Spouse');

Insert the Data into the WORKSON Table:

insert into workson values ('123456789','1',32.5);

insert into workson values ('123456789','2',7.5);

insert into workson values ('666884444','3',40.0);

insert into workson values ('453453453','1',20.0);

insert into workson values ('453453453','2',20.0);

insert into workson values ('333445555','2',10.0);

insert into workson values ('333445555','3',10.0);

insert into workson values ('333445555','10',10.0);

insert into workson values ('333445555','20',10.0);

insert into workson values ('999887777','30',30.0);

insert into workson values ('999887777','10',10.0);

insert into workson values ('987987987','10',35.0);

insert into workson values ('987987987','30',5.0);

insert into workson values ('987654321','30',20.0);

insert into workson values ('987654321','20',15.0);

insert into workson values ('888665555','20',NULL);

Answer 1

a.

Query:

select e.name , e.ssn from

((department d inner join employee e on e.dno= d.dnumber)

inner join project p on p.dnum = d.dnumber)

where p.pnumber not in (1,2,3)

Description: The query will fetch the name and ssn from table employee joining employee with department table based on department number and then department table is joined with the project table using department number to filter the project not in (1,2,3)

Output:

b.

Query:

SELECT e1.name, e1.ssn, e1.superssn , e2.name AS super_name

FROM Employee e1

Left JOIN Employee e2

ON e1.superssn = e2.ssn

Description:

The employee table is self-joined to get the name of the supervisee. It is a left join where first of all, all the employees will be fetched, then the corresponding supervisee will stored un super_name which will be retrieved based on superssn number.

Output:

c.

Query:

SELECT e1.name as supervisees_Name, e1.ssn as supervisees_SSN

FROM Employee e1

Left JOIN Employee e2

ON e1.superssn = e2.ssn

where e2.name = 'James E Borg'

Description: The query is using left join to find the employee where supervisee is ‘James E Borg’.

Output:

d.

Query:

SELECT e1.name , e1.ssn

FROM

((employee e1 Left JOIN Employee e2 ON e1.superssn = e2.ssn)

left join employee e3 on e2.superssn = e3.ssn)

where e3.name = 'James E Borg' OR e2.name = 'James E Borg'

Description: This is a selft join where the employee table is joined three times to fetch the supervisee name then the name of the employee which are being supervised by supervisee. The data is filtered based on ‘James E Borg’

Output:

e.

Query:

select e.ssn from employee e

left outer join dependent d on e.ssn = d.essn

where d.dependentname is null

Description: The employee and department table is joined to fetch the records where the dependentName is null for the fetched employee.

Output:

f.

Query:

select dno, count(*) as TotalEmployee, avg(salary) as AvgSalary, sum(salary) as TotalSal from employee group by dno

Description: The aggregate function is used on employee table which is group by department number dno column. Count(*) is used to count the number of employees.Sum is used to find the sum of salary and AVG is used to find the average of the salary.

Output:

g.

Query:

select dno, count(*) as TotalEmployee, avg(salary) as AvgSalary, sum(salary) as TotalSal from employee group by dno

having count(*) > 1

Description: The query is same as f, but having a where condition with count of employee is more than 1.

Output: