HERE IS THE DATA /CODE (ORACLE) drop table workon; drop table employee; drop tab
ID: 3595801 • Letter: H
Question
HERE IS THE DATA /CODE (ORACLE)
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),
salary float,
did 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,
constraint project_pid_pk primary key (pid),
constraint project_did_fk foreign key (did) references division(did)
);
create table workon
(pid integer references project(pid),
empID integer references employee(empID),
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 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);
B. Formulate the following queries using SQL and show the results of execution.
1. List the name of employee whose salary is higher than 'chen' (use subquery)
2. List the name of employee who do not work on project 'web development' (use subquery)
3. List the name of employee whose name is unique
4. List the project that 'chen' works on and also has higher budget than average project budget of the company.
Explanation / Answer
1. List the name of employee whose salary is higher than 'chen' (use subquery)
2. List the name of employee who do not work on project 'web development' (use subquery)
3. List the name of employee whose name is unique
4. List the project that 'chen' works on and also has higher budget than average project budget of the company.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.