Must be able to use on Oracle, thumbs up and points. Please help me. this is the

Question

Must be able to use on Oracle, thumbs up and points. Please help me.

this is the code:

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);

Part I (6 SQL questions)

1. Create a trigger that when a workon record is inserted (namely, an employee workon a new project) the trigger will increase the salary of THIS employee who work on THIS project by 1% if this employees' salary is less than 90000. (for example, when your insert a row (1, 100, 20) into workon table (Using INSERT statement), assuming employee 100 exists, you need to find out (use Where clause) if the employee's salary is less than 90000, if so, increase his salary.( Hint: use After trigger, and use :new to refer the workon table after the change ) see the notes of trigger samples in Canvas (DISCUSSION).

show me:
(1). trigger code.

(2) the table contents of employee and workon before triggering event (insert).

(3) table contents after triggering event. This way I can see if the trigger works or not. Also show your INSERT statement.

2. Use alter statement to add a field (fieldname: total_employee, datatype: number) into division table. Then use “select * from division” to show division table.

3. Use an update statement to update values of total_employee.

If your last name’s initial is from A to K, you MUST answer questions 4, 6, 8. If your last name’s initial is from L to Z you MUST answer questions 5,7, 9. You must number and copy each question even you do not have answer. No credit will be given to your work if you do not following this rule. You MUST do the work independently.

5. Increase the salary of employees if they workon project that has high budget than average budget.

7. List the name of employees who work on more than one projects that has higher budget than its divisional average budget.

9. List the name of project that has higher budget than all projects 'chen' works on (note if you compare one values with a set of values returned by a subqury, use ALL or ANY. e.g. ... budget >= ALL (select budget from ...)

10. (Bonus question) List the name employees who work on all projects. (Hint: there is a very similar example of in text, which uses NOT EXISTS. The logic is to find an employee that there is NO project that this employee does NOT work on)

Explanation / Answer

2. ) alter table division add column total_employee number;

select * from divison;

3. update division set total_employee=100;

4. select empId from employee join workon where employee.empId=workon.empidwhere select avg(budget) from

project

5.)

Related Questions

Navigate

• Browse (All)
• Browse M
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.