CREATE TABLE  "PROJECTS" 
   (	"PROJECTID" NUMBER(11,0) NOT NULL ENABLE, 
	"PROJECTNAME" VARCHAR2(50), 
	"PROJECTDESCRIPTION" CLOB, 
	"PROJECTSTATUS" VARCHAR2(50), 
	 CONSTRAINT "PK_PROJECTS" PRIMARY KEY ("PROJECTID") ENABLE
   )
/


insert into projects values (1, 'Oracle 10g Upgrade', 'Upgrade all instances of Oracle to 10g', 'Open')
/
insert into projects values (2, 'Evaluate Next release of Windows', 'Once released, evaluate Longhorn', 'Open')
/
insert into projects values (3, 'Migrate Project App to HTML DB', 'Move this application to Oracle HTML DB', 'Open')
/
insert into projects values (4, 'Update Virus Definitions', 'Update all 1200 PCs with new Virus Definitions', 'Closed')
/

CREATE TABLE  "TASKS" 
   (	"TASKID" NUMBER(11,0) NOT NULL ENABLE, 
	"PROJECTID" NUMBER(11,0), 
	"EMPLOYEEID" NUMBER(11,0), 
	"ACTIVITYDATE" DATE, 
	"HOURS" FLOAT(126) DEFAULT 0, 
	"NOTES" VARCHAR2(255), 
	 CONSTRAINT "PK_TASKS" PRIMARY KEY ("TASKID") ENABLE, 
	 CONSTRAINT "REFERENCE" FOREIGN KEY ("PROJECTID")
	  REFERENCES  "PROJECTS" ("PROJECTID") ON DELETE CASCADE ENABLE
   )
/

insert into tasks values(1, 1, 1, '15-AUG-05', 2, 'Download Software from OTN')
/
insert into tasks values(2, 1, 1, '15-AUG-05', 2, 'Install Linux on development server')
/
insert into tasks values(3, 1, 1, '30-AUG-05', 2, 'Testing')
/
insert into tasks values(4, 1, 2, '16-SEP-05', 1, 'Review pre-install notes')
/
insert into tasks values(5, 2, 1, '14-SEP-05', 1, 'Latest release date - 2006')
/
insert into tasks values(6, 3, 1, '19-SEP-05', 1, 'Evaluate application requirements')
/
insert into tasks values(7, 3, 1, '21-SEP-05', 1, 'Perform Migration & go live with HTML DB')
/
insert into tasks values(8, 4, 1, '12-AUG-05', 8, 'Updated Marketing')
/
insert into tasks values(9, 4, 2, '12-AUG-05', 8, 'Updated Sales')
/
insert into tasks values(10, 4, 1, '11-OCT-05', 8, 'Updated Executives')
/
insert into tasks values(11, 4, 2, '11-OCT-05', 8, 'Updated M&D')
/
insert into tasks values(12, 4, 1, '12-SEP-05', 8, 'Updated CIO Office')
/
insert into tasks values(13, 4, 2, '12-SEP-05', 8, 'Updated HQ')
/



CREATE INDEX  "PROJECTNAME" ON  "PROJECTS" ("PROJECTNAME")
/
CREATE INDEX  "PROJECTID" ON  "TASKS" ("PROJECTID")
/
CREATE SEQUENCE   "S_294_1_TASKS"  MINVALUE 1 MAXVALUE 1.00000000000000E+27 INCREMENT BY 1 START WITH 14 CACHE 20 NOORDER  NOCYCLE
/
CREATE SEQUENCE   "S_293_1_PROJECTS"  MINVALUE 1 MAXVALUE 1.00000000000000E+27 INCREMENT BY 1 START WITH 5 CACHE 20 NOORDER  NOCYCLE
/

CREATE OR REPLACE TRIGGER  "TR_S_294_1_TASKS" 
 BEFORE INSERT ON projtrak.TASKS
 FOR EACH ROW 
 BEGIN 
 SELECT projtrak.S_294_1_TASKS.nextval
 INTO :new.TASKID
 FROM dual;
 END;
/
ALTER TRIGGER  "TR_S_294_1_TASKS" ENABLE
/
CREATE OR REPLACE TRIGGER  "TR_S_293_1_PROJECTS" 
 BEFORE INSERT ON projtrak.PROJECTS
 FOR EACH ROW 
 BEGIN 
 SELECT projtrak.S_293_1_PROJECTS.nextval
 INTO :new.PROJECTID
 FROM dual;
 END;
/
ALTER TRIGGER  "TR_S_293_1_PROJECTS" ENABLE
/

CREATE OR REPLACE FORCE VIEW  "PROJECTS_BY_HOURS" ("PROJECTNAME", "SUMOFHOURS") AS 
  SELECT  Projects.ProjectName, SUM(Tasks.Hours) SumOfHours
 FROM Projects INNER JOIN Tasks
ON Projects.ProjectID = Tasks.ProjectID 
	GROUP BY Projects.ProjectName 
ORDER BY SUM(Tasks.Hours) DESC
/