In: Computer Science
-- Creating table ProjDept:
create table ProjDept (
ProjDeptID NUMBER(10) primary key,
ProjDeptName varchar2(20),
OfficeLocation varchar2(20),
PhoneNumber varchar2(20)
);
INSERT INTO ProjDept (ProjDeptID, ProjDeptName, OfficeLocation,
PhoneNumber) VALUES
(1001, 'Accounting','ITCC01-400','888-285-8100');
(2001, 'Human Resources','ITCC01-200','888-285-8100');
(3001, 'Marketing','ITCC02-300','888-285-8100');
(4001, 'Information Techn','ITCC02-100','888-285-8100');
(5001, 'Legal','ITCC01-100','888-285-8100');
-- Creating table Employee:
create table Employee(
EmployeeID NUMBER(10) primary key,
FirstName varchar2(20),
LastName varchar2(20),
ProjDeptID NUMBER(10),
PhoneNumber varchar2(20)
);
INSERT INTO Employee (EmployeeID, FirstName, LastName,
ProjDeptID, PhoneNumber, Email) VALUES
(10, 'Mark','Columbus',1001, '888-285-8101',
'[email protected]');
(29, 'Elvin','Wahl', 2001, '888-285-8201', '[email protected]');
(38, 'Taylor','Noel', 3001, '888-285-8303',
'[email protected]');
(47, 'Ariel','Colby', 4001, '888-285-8401',
'[email protected]');
(56, 'Riley','Peterson', 4001, '888-285-8402',
'[email protected]');
(65, 'Terence','Ferdinand', 1001, '888-285-8102',
'[email protected]');
(74, 'Bryce','Daley', 5001, '888-285-8501',
'[email protected]');
(83, 'Eva Myers','Myers', 2001, '888-285-8203',
'[email protected]');
(92, 'Lyn Lorenzo','Lorenzo', 3001, '888-285-8305',
'[email protected]');
(11, 'Jamaal Holt','Holt', 3001, '888-285-8307',
'[email protected]');
-- Creating table Project:
create table Project (
ProjectID NUMBER(10) primary key,
ProjectName varchar2(20),
ProjDeptID NUMBER(10),
MaxHours varchar2(20),
StartDate date,
EndDate date
);
INSERT INTO Project (ProjectID, ProjectName, ProjDeptID,
MaxHours, StartDate, EndDate) VALUES
(901, 'Product Plan',3001,'135',TO_DATE
('10-May-12','DD-Mon-YYYY'),TO_DATE
('15-Sep-12','DD-Mon-YYYY'));
(902, 'Tax Preparation',1001,'120',TO_DATE
('05-Jul-12','DD-Mon-YYYY'),TO_DATE
('15-Oct-12','DD-Mon-YYYY'));
(903, 'Portfolio Analysis',5001,'145',TO_DATE
('10-Aug-12','DD-Mon-YYYY'),TO_DATE ('','DD-Mon-YYYY'));
-- Creating table ProjectTask:
create table ProjectTask (
ProjectID NUMBER(10),
EmployeeID NUMBER(10),
--TaskDetails varchar2(20),
HoursWorked varchar2(20),
primary key(ProjectID,EmployeeID)
);
INSERT INTO ProjectTask (ProjectID, EmployeeID, HoursWorked)
VALUES
(901,47, '30');
(901,56,75);
(901,38, 55);
(902,65, 40);
(902,10, 45);
(902,74, 25);
(903,74, 20);
(903,83, 45);
(903,29, 40);
(901,74, 35);
--ALTER table employee
ALTER table Employee
Add foreign key (ProjDeptID) references ProjDept(ProjDeptID);
--ALTER Table Project
ALTER table Project
Add foreign key (ProjDeptID) references ProjDept(ProjDeptID);
-- ALTERTable projectTask
ALTER table ProjectTask
Add foreign key (ProjectID) references Project(ProjectID);
ALTER table ProjectTask
Add foreign key (EmployeeID) references Employee(EmployeeID);
-- SQL statement to add a unique column that holds an email address
in Employee table is as follows-
ALTER TABLE Employee ADD Email varchar(255);
ALTER TABLE Employee ADD UNIQUE(Email);
INSERT INTO Employee (EmployeeID, FirstName, LastName, ProjDeptID, PhoneNumber, Email) VALUES
(10, 'Mark','Columbus',1001, '888-285-8101',
'[email protected]');
(29, 'Elvin','Wahl', 2001, '888-285-8201', '[email protected]');
(38, 'Taylor','Noel', 3001, '888-285-8303',
'[email protected]');
(47, 'Ariel','Colby', 4001, '888-285-8401',
'[email protected]');
(56, 'Riley','Peterson', 4001, '888-285-8402',
'[email protected]');
(65, 'Terence','Ferdinand', 1001, '888-285-8102',
'[email protected]');
(74, 'Bryce','Daley', 5001, '888-285-8501',
'[email protected]');
(83, 'Eva Myers','Myers', 2001, '888-285-8203',
'[email protected]');
(92, 'Lyn Lorenzo','Lorenzo', 3001, '888-285-8305',
'[email protected]');
(11, 'Jamaal Holt','Holt', 3001, '888-285-8307',
'[email protected]');
UPDATE Project
SET ProjectName = 'Product Plan' , MaxHours = 250,
WHERE LastName = 'Chris'
AND LastName = 'Martin';
How do i fix the issue because it is not running
create table ProjDept (
ProjDeptID NUMBER(10) primary key,
ProjDeptName varchar2(20),
OfficeLocation varchar2(20),
PhoneNumber varchar2(20));
INSERT INTO ProjDept (ProjDeptID, ProjDeptName, OfficeLocation,
PhoneNumber) VALUES (1001,
'Accounting','ITCC01-400','888-285-8100');
INSERT INTO ProjDept (ProjDeptID, ProjDeptName, OfficeLocation,
PhoneNumber) VALUES(2001, 'Human
Resources','ITCC01-200','888-285-8100');
INSERT INTO ProjDept (ProjDeptID, ProjDeptName, OfficeLocation,
PhoneNumber) VALUES(3001,
'Marketing','ITCC02-300','888-285-8100');
INSERT INTO ProjDept (ProjDeptID, ProjDeptName, OfficeLocation,
PhoneNumber) VALUES(4001, 'Information
Techn','ITCC02-100','888-285-8100');
INSERT INTO ProjDept (ProjDeptID, ProjDeptName, OfficeLocation,
PhoneNumber) VALUES(5001, 'Legal','ITCC01-100','888-285-8100');
-- Creating table Employee:
create table Employee(
EmployeeID NUMBER(10) primary key,
FirstName varchar2(20),
LastName varchar2(20),
ProjDeptID NUMBER(10),
PhoneNumber varchar2(20)
);
-- SQL statement to add a unique column that holds an email address in Employee table is as follows-
ALTER TABLE Employee ADD Email varchar(255);
ALTER TABLE Employee ADD UNIQUE(Email);
INSERT INTO Employee (EmployeeID, FirstName, LastName,
ProjDeptID, PhoneNumber, Email) VALUES
(10, 'Mark','Columbus',1001, '888-285-8101',
'[email protected]');
INSERT INTO Employee (EmployeeID, FirstName, LastName, ProjDeptID,
PhoneNumber, Email) VALUES(29, 'Elvin','Wahl', 2001,
'888-285-8201', '[email protected]');
INSERT INTO Employee (EmployeeID, FirstName, LastName, ProjDeptID,
PhoneNumber, Email) VALUES(38, 'Taylor','Noel', 3001,
'888-285-8303', '[email protected]');
INSERT INTO Employee (EmployeeID, FirstName, LastName, ProjDeptID,
PhoneNumber, Email) VALUES(47, 'Ariel','Colby', 4001,
'888-285-8401', '[email protected]');
INSERT INTO Employee (EmployeeID, FirstName, LastName, ProjDeptID,
PhoneNumber, Email) VALUES(56, 'Riley','Peterson', 4001,
'888-285-8402', '[email protected]');
INSERT INTO Employee (EmployeeID, FirstName, LastName, ProjDeptID,
PhoneNumber, Email) VALUES(65, 'Terence','Ferdinand', 1001,
'888-285-8102', '[email protected]');
INSERT INTO Employee (EmployeeID, FirstName, LastName, ProjDeptID,
PhoneNumber, Email) VALUES(74, 'Bryce','Daley', 5001,
'888-285-8501', '[email protected]');
INSERT INTO Employee (EmployeeID, FirstName, LastName, ProjDeptID,
PhoneNumber, Email) VALUES(83, 'Eva Myers','Myers', 2001,
'888-285-8203', '[email protected]');
INSERT INTO Employee (EmployeeID, FirstName, LastName, ProjDeptID,
PhoneNumber, Email) VALUES(92, 'Lyn Lorenzo','Lorenzo', 3001,
'888-285-8305', '[email protected]');
INSERT INTO Employee (EmployeeID, FirstName, LastName, ProjDeptID,
PhoneNumber, Email) VALUES(11, 'Jamaal Holt','Holt', 3001,
'888-285-8307', '[email protected]');
-- Creating table Project:
create table Project (
ProjectID NUMBER(10) primary key,
ProjectName varchar2(20),
ProjDeptID NUMBER(10),
MaxHours varchar2(20),
StartDate date,
EndDate date);
INSERT INTO Project (ProjectID, ProjectName, ProjDeptID,
MaxHours, StartDate, EndDate) VALUES
(901, 'Product Plan',3001,'135',TO_DATE
('10-May-12','DD-Mon-YYYY'),TO_DATE
('15-Sep-12','DD-Mon-YYYY'));
INSERT INTO Project (ProjectID, ProjectName, ProjDeptID, MaxHours,
StartDate, EndDate) VALUES(902, 'Tax
Preparation',1001,'120',TO_DATE ('05-Jul-12','DD-Mon-YYYY'),TO_DATE
('15-Oct-12','DD-Mon-YYYY'));
INSERT INTO Project (ProjectID, ProjectName, ProjDeptID, MaxHours,
StartDate, EndDate) VALUES(903, 'Portfolio
Analysis',5001,'145',TO_DATE ('10-Aug-12','DD-Mon-YYYY'),TO_DATE
('','DD-Mon-YYYY'));
-- Creating table ProjectTask:
create table ProjectTask (
ProjectID NUMBER(10),
EmployeeID NUMBER(10),
--TaskDetails varchar2(20),
HoursWorked varchar2(20),
primary key(ProjectID,EmployeeID)
);
INSERT INTO ProjectTask (ProjectID, EmployeeID, HoursWorked)
VALUES(901,47, 30);
INSERT INTO ProjectTask (ProjectID, EmployeeID, HoursWorked)
VALUES(901,56,75);
INSERT INTO ProjectTask (ProjectID, EmployeeID, HoursWorked)
VALUES(901,38, 55);
INSERT INTO ProjectTask (ProjectID, EmployeeID, HoursWorked)
VALUES(902,65, 40);
INSERT INTO ProjectTask (ProjectID, EmployeeID, HoursWorked)
VALUES(902,10, 45);
INSERT INTO ProjectTask (ProjectID, EmployeeID, HoursWorked)
VALUES(902,74, 25);
INSERT INTO ProjectTask (ProjectID, EmployeeID, HoursWorked)
VALUES(903,74, 20);
INSERT INTO ProjectTask (ProjectID, EmployeeID, HoursWorked)
VALUES(903,83, 45);
INSERT INTO ProjectTask (ProjectID, EmployeeID, HoursWorked)
VALUES(903,29, 40);
INSERT INTO ProjectTask (ProjectID, EmployeeID, HoursWorked)
VALUES(901,74, 35);
--ALTER table employee
ALTER table Employee
Add foreign key (ProjDeptID) references ProjDept(ProjDeptID);
--ALTER Table Project
ALTER table Project
Add foreign key (ProjDeptID) references ProjDept(ProjDeptID);
-- ALTERTable projectTask
ALTER table ProjectTask
Add foreign key (ProjectID) references Project(ProjectID);
ALTER table ProjectTask
Add foreign key (EmployeeID) references
Employee(EmployeeID);