Question

USE ORACLE - SQL

1. List the name and salary of employees who work for division 3.
2. List the name of project whose budget is between 5000-7000
3. List the total number of employee whose initial of name is 's'. (hint, using LIKE operator and wildcard character)
4. List the total number of employee whose initial of name is NOT 's' for each division, including division ID
5. List the total project budget for each division, including division ID.
6. List the ID of the division that has two or more projects with budget over $6000.
7. List the ID of division that sponsors project "Web development", List the project budget too.
8. List the total number of employee whose salary is above $40000 for each division, list division ID.
9. List the total number of project and total budget for each division, show division ID
10. List the ID of employee that worked on more than three projects.
11. List the ID of each division with its highest salary..
12. List the total number of project each employee works on, including employee's ID and total hours an employee spent on project.
13. List the total number of employees who work on project 1.
14. List names that are shared by more than one employee and list the number of employees who share that name.
15. List the total number of employee and total salary for each division, including division name (hint: use JOIN operation, read the text for join operation)

My table is below:

drop table workon;
drop table employee;
drop table project;
drop table division;
create table division
(did integer,
dname varchar (25),
managerID integer,
constraint division_did_pk primary key (did) );

create table employee
(empID integer,
name varchar(30),
alary float,
id integer,
constraint employee_empid_pk primary key (empid),
constraint employee_did_fk foreign key (did) references division(did)
);
create table project
(pid integer,
pname varchar(25),
budget float,
did integer,
onstraint project_pid_pk primary key (pid),
constraint project_did_fk foreign key (did) references division(did)
);
create table workon
(pid integer,
empID integer,
hours integer,
constraint workon_pk primary key
(pid, empID)
);
/* loading the data into the database */
insert into division
Values (1,'engineering', 2);
insert into division
values (2,'marketing', 1);
insert into division
values (3,'human resource', 3);
insert into division
values (4,'Research and development', 5);
insert into division
values (5,'accounting', 4);

insert into project
values (1, 'DB development', 8000, 2);
insert into project
values (2, 'network development', 6000, 2);
insert into project
values (3, 'Web development', 5000, 3);
insert into project
values (4, 'Wireless development', 5000, 1);
insert into project
values (5, 'security system', 6000, 4);
insert into project
values (6, 'system development', 7000, 1);

insert into employee
values (1,'kevin', 32000,2);
insert into employee
values (2,'joan', 42000,1);
insert into employee
values (3,'brian', 37000,3);
insert into employee
values (4,'larry', 82000,5);
insert into employee
values (5,'harry', 92000,4);
insert into employee
values (6,'peter', 45000,2);
insert into employee
values (7,'peter', 68000,3);
insert into employee
values (8,'smith', 39000,4);
insert into employee
values (9,'chen', 71000,1);
insert into employee
values (10,'kim', 46000,5);
insert into employee
values (11,'smith', 46000,1);
insert into employee
values (12,'joan', 48000,1);
insert into employee
values (13,'kim', 49000,2);
insert into employee
values (14,'austin', 46000,1);
insert into employee
values (15,'sam', 52000,3);

insert into workon
values (3,1,30);
insert into workon
values (2,3,40);
insert into workon
values (5,4,30);
insert into workon
values (6,6,60);
insert into workon
values (4,3,70);
insert into workon
values (2,4,45);
insert into workon
values (5,3,90);
insert into workon
values (3,3,100);
insert into workon
values (6,8,30);
insert into workon
values (4,4,30);
insert into workon
values (5,8,30);
insert into workon
values (6,7,30);
insert into workon
values (6,9,40);
insert into workon
values (5,9,50);
insert into workon
values (4,6,45);
insert into workon
values (2,7,30);
insert into workon
values (2,8,30);
insert into workon
values (2,9,30);
insert into workon
values (1,9,30);
insert into workon
values (1,8,30);
insert into workon
values (1,7,30);
insert into workon
values (1,5,30);
insert into workon
values (1,6,30);
insert into workon
values (2,6,30);
insert into workon
values (2,12,30);
insert into workon
values (3,13,30);
insert into workon
values (4,14,20);
insert into workon
values (4,15,40);

Answer 1

1.List the name and salaries of employees who work for division 3.

Ans: Query:

select name, salary

from employee

where did=3;

Output:
brian|37000.0
peter|68000.0
sam|52000.0

2. List the name of the project whose budget is between 5000-7000.

Ans:Query:

select pname

from project

where budget>=5000 and budget<=7000;

Output:
network development
Web development
Wireless development
security system
system development

3.List the total number of employee whose initial of name is 's'. (hint, using LIKE operator and wildcard character).

Ans:Query:

select count(name)

from employee

where name like 's%';

Output:
3

Query:

select name

from employee

where name like 's%';

Output:
smith
smith
sam

4. List the total number of an employee whose initial the name is NOT 's' for each division, including division ID.

Ans: Query:

select name

from employee

where name not like 's%';

Output:
kevin
joan
brian
larry
harry
peter
peter
chen
kim
joan
kim
austin

Query:

select count(name)

from employee

where name not like 's%';

Output:
12

5. List the total project budget for each division, including division ID.

Ans: Query:

select did,sum(budget)

from project

group by did;

Output:
1|12000.0
2|14000.0
3|5000.0
4|6000.0

6.List the ID of the division that has two or more projects with budget over $6000.

Ans: Query:

select did, sum(budget), count(*) as NumberProjects

from project

where budget>6000

group by did

having count(*)>=2;

Output:

did|budget|NumberProjects

2|14000.0|2

Alternate Answers:

(a)

select dname, sum(budget), count(*) "NoOfPro"

from project p, division d

where p.did=d.did and budget>6000

group by dname

having count(*)>=2;

(b)

select d.did,sum(budget), count(*) as NoOfPro

from project p, division d where p.did=d.did and budget>6000

group by d.did

having count(p.did)>=2;

7.List the ID of division that sponsors project "Web development", List the project budget too.

Ans:

Query:

select did,budget

from project

where pname="Web development";

Output:
3|5000.0

8.List the total number of employee whose salary is above $40000 for each division, list division ID.

Ans:

Query:

select did, count(*) name

from employee

where salary>40000

group by did;

Output:
1|5
2|2
3|2
4|1
5|2

9.List the total number of project and total budget for each division, show division ID.

Ans:

Query:

select did,count(*) pname,sum(budget)

from project

group by did;

Output:
1|2|12000.0
2|2|14000.0
3|1|5000.0
4|1|6000.0

10.List the ID of an employee that worked on more than three projects.

Ans:

Query:

select e.name, w.pid
from employee e inner join workon w on e.empid = w.empid
where e.empid in

(select empID

from workon

group by empID

having count(empID) > 3);

Output:

peter|1
smith|1
chen|1
brian|2
peter|2
smith|2
chen|2
brian|3
brian|4
peter|4
brian|5
smith|5
chen|5
peter|6
smith|6
chen|6

11.List the ID of each division with its highest salary.

Ans:

Query:

select d.did, max(e.salary)
from division d
left outer join employee e on(e.did = d.did)
group by d.did;

Output:

1|71000.0
2|49000.0
3|68000.0
4|92000.0
5|82000.0

12.List the total number of project each employee works on, including employee's ID and total hours an employee spent on project.

Ans:

Query:

select name, sum (hours)
from employee e, workon w
where e.empID = w.empID
group by e.empID , name;

Output:

kevin|30
brian|300
larry|105
harry|30
peter|165
peter|90
smith|120
chen|150
joan|30
kim|30
austin|20
sam|40

13.List the total number of employees who work on project 1.

Ans:

Query:

select count(*) empID

from workon

where pid=1;

Output:
5

14.List names that are shared by more than one employee and list the number of employees who share that name.

Ans:

Query:

select e1.name
from employee e1, employee e2
where e1.name = e2.name;

Output:

kevin
joan
joan
brian
larry
harry
peter
peter
peter
peter
smith
smith
chen
kim
kim
smith
smith
joan
joan
kim
kim
austin
sam

15.List the total number of employee and total salary for each division, including division name (hint: use JOIN operation, read the text for join operation).

Ans:

Query:
select count(*)employee, sum(salary), dname
from employee e join division d
on e.did = d.did
group by dname;

Output:

2|131000.0|Research and development
2|128000.0|accounting
5|253000.0|engineering
3|157000.0|human resource
3|126000.0|marketing