urgent help in some queries

set echo on
/***************************************************************************
* Changes
* 
* 11.02.05
*
* manager of employee on a project included in the employee on project table
*
*
*
***************************************************************************/

drop table org_unit cascade constraints;
drop table project cascade constraints;
drop table employee cascade constraints;
drop table employee_on_activity cascade constraints;




/********************************************************************
* org_unit
*
* type - for example in lmu might be FACULTY, or SCHOOL
********************************************************************/
CREATE TABLE org_unit
(
 ou_id        NUMBER(4) 	CONSTRAINT ou_pk PRIMARY KEY,
 ou_name   VARCHAR2(40)	  NOT NULL  UNIQUE,
ou_type 	VARCHAR2(30)    CONSTRAINT ou_type_nn NOT NULL,
ou_parent_org_id         NUMBER(4)	 CONSTRAINT ou_parent REFERENCES org_unit
);

/**************************************************************************
* project 
***************************************************************************/

CREATE TABLE project
(
 proj_id NUMBER(5)	CONSTRAINT project_pk PRIMARY KEY,
 proj_name VARCHAR2(40) NOT NULL 	UNIQUE,
 proj_budget NUMBER(8,2)	CONSTRAINT proj_budget_nn NOT NULL,
 proj_ou_id NUMBER(4)	CONSTRAINT proj_ou_fk REFERENCES org_unit,
 proj_planned_start_dt DATE,
 proj_planned_finish_dt DATE,
 proj_actual_start_dt   DATE
);

/**************************************************************************
* employee
***************************************************************************/
CREATE TABLE employee
(emp_id NUMBER(6)	CONSTRAINT emp_pk PRIMARY KEY,
emp_name VARCHAR2(40)	CONSTRAINT emp_name_nn NOT NULL,
emp_hiredate DATE	CONSTRAINT emp_hiredate_nn NOT NULL,
ou_id NUMBER(4) 	CONSTRAINT emp_ou_fk REFERENCES org_unit
);


/**************************************************************************
* employee on activity
* type - for example in lmu might be FACULTY, or SCHOOL
**************************************************************************/
CREATE TABLE employee_on_activity
( ea_emp_id NUMBER(6),
 ea_proj_id NUMBER(5),
 ea_act_id NUMBER(6),  	
 ea_planned_hours  NUMBER(3)	CONSTRAINT ea_planned_hours_nn NOT NULL,
 CONSTRAINT ea_pk PRIMARY KEY(ea_emp_id, ea_proj_id, ea_act_id),
 CONSTRAINT ea_ep_fk FOREIGN KEY (ea_proj_id) REFERENCES project (proj_id),
 CONSTRAINT ea_emp_fk FOREIGN KEY (ea_emp_id) REFERENCES employee (emp_id)
);

-- INSERT INTO DATA ORG_UNIT TABLE 

insert into org_unit ( ou_id, ou_name, ou_type, ou_parent_org_id)
values ( 1000 , 'Ledds Met' , 'UNIVERSITY', null);
insert into org_unit ( ou_id, ou_name, ou_type, ou_parent_org_id)
values ( 1100 , 'Innovation North' , 'FACULTY',  1000);
insert into org_unit( ou_id, ou_name, ou_type, ou_parent_org_id)
values ( 1110 , 'COMPUTING', 'SCHOOL' , 1100);
insert into org_unit ( ou_id, ou_name, ou_type, ou_parent_org_id)
values ( 1120 , 'TECHNOLOGY', 'SCHOOL' , 1100);
insert into org_unit ( ou_id, ou_name, ou_type, ou_parent_org_id)
values ( 1130 , 'INFORMATION MANAGEMENT', 'SCHOOL' , 1100);


insert into org_unit ( ou_id, ou_name, ou_type, ou_parent_org_id)
values ( 1200 , 'HEALTH AND ENVIRONMENT' ,'FACULTY',1000);





-- INSERT INTO DATA PROJECT TABLE 

insert into project values (1, 'PROJECT 1', 300000, 1110, '01-AUG-04', '20-dec-04', '01-AUG-04');
insert into project values (2, 'PROJECT 2', 200000, 1110, '01-JAN-04', '01-JUN-04', '01-JUL-04');
insert into project values (3, 'PROJECT 3', 300000, 1110, '01-DEC-04', '01-DEC-05', NULL);
 


-- INSERT INTO DATA EMPLOYEE TABLE

-- into LMU
insert into employee (emp_id, emp_name, emp_hiredate, ou_id) values
(000001, 'NAME: 000001' , '01-MAY-99', 1000);
insert into employee (emp_id, emp_name, emp_hiredate, ou_id) values
(000002, 'NAME: 000002' , '01-MAY-88', 1000);
insert into employee (emp_id, emp_name, emp_hiredate, ou_id) values
(000003, 'NAME: 000003' , '01-SEP-93', 1000);
insert into employee (emp_id, emp_name, emp_hiredate, ou_id) values
(000004, 'NAME: 000004' , '21-AUG-00', 1000);
insert into employee (emp_id, emp_name, emp_hiredate, ou_id) values
(000005, 'NAME: 000005' , '01-SEP-00', 1000);

-- into FIES
insert into employee (emp_id, emp_name, emp_hiredate, ou_id) values
(000006, 'NAME: 000007' , '21-MAY-99', 1100);
insert into employee (emp_id, emp_name, emp_hiredate, ou_id) values
(000007, 'NAME: 000007' , '13-SEP-89', 1100);
insert into employee (emp_id, emp_name, emp_hiredate, ou_id) values
(000008, 'NAME: 000008' , '01-MAR-99', 1100);
 


-- INSERT INTO DATA EMPLOYEE ON ACTIVITY TABLE

 -- employee no 1 on project 1
insert into employee_on_activity values (1,1,1,20);
insert into employee_on_activity values (1,1,8,20);


-- employee no 2 on project 1
insert into employee_on_activity values (2,1,1,20);
insert into employee_on_activity values (2,1,2,20);
insert into employee_on_activity values (2,1,3,20);
insert into employee_on_activity values (2,1,4,20);
insert into employee_on_activity values (2,1,5,20);
insert into employee_on_activity values (2,1,6,20);
insert into employee_on_activity values (2,1,7,20);
insert into employee_on_activity values (2,1,8,20);
 
-- employee no 3 on project 1
insert into employee_on_activity values (3,1,2,20);
insert into employee_on_activity values (3,1,3,20);
insert into employee_on_activity values (3,1,4,20);
insert into employee_on_activity values (3,1,5,20);

-- employee no 4 on project 1
insert into employee_on_activity values (4,1,5,20);
insert into employee_on_activity values (4,1,6,20);

-- employee no 5 on project 1
insert into employee_on_activity values (5,1,6,20);
insert into employee_on_activity values (5,1,7,20);

-- employee no 6 on project 1
insert into employee_on_activity values (6,1,6,20);
insert into employee_on_activity values (6,1,7,20);



commit;

SELECT employee.emp_name
FROM employee, org_unit
WHERE employee.ou_id = org_unit.ou_id
AND org_unit.ou_name = "INFORMATION MANAGMENT";

SELECT project.project_id, AVG(project.project_budget) AS Average Budget
FROM project
GROUP BY project.project_id;

SELECT project.project_name
FROM project
ORDER BY project.project_name;

SELECT project.project_id, project.project_name, employee.emp_id, employee.emp_name
FROM employee, project
WHERE project.proj_ou_id = employee.ou_id
AND project.proj_planned_start_dt BETWEEN LIKE %40 AND LIKE %50;

SELECT employee.emp_name, "is an employee in the Information Management OU"
FROM employee, org_unit
WHERE employee.ou_id = org_unit.ou_id
AND org_unit.ou_name = "INFORMATION MANAGEMENT";

SELECT employee.emp_id, employee.emp_name
FROM employee, employee_ou_activity, project, org_unit
WHERE employee.emp_id = employee_on_activity.ea_emp_id
AND project.proj_id = employee_on_activity.ea_proj_id
AND employee.ou_id = org_unit.ou_id
AND project.proj_ou_id = org_unit.ou_id
AND project.proj_name = "PROJECT 1"
AND org_unit.ou_name = "COMPUTING";

CREATE VIEW employee_project
AS SELECT employee.emp_name, employee.emp_hiredate, project.project_name
FROM employee, project
WHERE project.proj_actual_start_dt = "01-JUN-04";