Question

In: Computer Science

-- Creating table ProjDept: create table ProjDept ( ProjDeptID NUMBER(10) primary key, ProjDeptName varchar2(20), OfficeLocation varchar2(20),...

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

Solutions

Expert Solution

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

venereology answered 1 year ago
Next > < Previous

Related Solutions

create table node( node_id integer primary key, node_color varchar(10)); create table edge( edge_id integer primary key,...
create table node( node_id integer primary key, node_color varchar(10)); create table edge( edge_id integer primary key, origin_id integer, destination_id integer, foreign key (origin_id) references node(node_id), foreign key (destination_id) references node(node_id)); write an SQL query that lists all those nodes that have edges with a destination node that has color 'red'.
Consider the following table definitions create table node( node_id integer primary key, node_color varchar(10)); create table...
Consider the following table definitions create table node( node_id integer primary key, node_color varchar(10)); create table edge( edge_id integer primary key, origin_id integer, destination_id integer, foreign key (origin_id) references node(node_id), foreign key (destination_id) references node(node_id)); What is the result of the following query? select node_id, node_color, destination_id from node, edge; An inner join of the tables node and edge that lists origin node_id and node_color together with the node_id of the destination node for all those nodes that have outgoing...
Create table, create primary and foreign key constraints. Create index on the table to satisfy a...
Create table, create primary and foreign key constraints. Create index on the table to satisfy a query with aggregate functions.
This is the database CREATE TABLE AIRCRAFT ( AC_NUMBER varchar(5) primary key, MOD_CODE varchar(10), AC_TTAF double,...
This is the database CREATE TABLE AIRCRAFT ( AC_NUMBER varchar(5) primary key, MOD_CODE varchar(10), AC_TTAF double, AC_TTEL double, AC_TTER double ); INSERT INTO AIRCRAFT VALUES('1484P','PA23-250',1833.1,1833.1,101.8); INSERT INTO AIRCRAFT VALUES('2289L','DC-90A',4243.8,768.9,1123.4); INSERT INTO AIRCRAFT VALUES('2778V','MA23-350',7992.9,1513.1,789.5); INSERT INTO AIRCRAFT VALUES('4278Y','PA31-950',2147.3,622.1,243.2); /* -- */ CREATE TABLE CHARTER ( CHAR_TRIP int primary key, CHAR_DATE date, AC_NUMBER varchar(5), CHAR_DESTINATION varchar(3), CHAR_DISTANCE double, CHAR_HOURS_FLOWN double, CHAR_HOURS_WAIT double, CHAR_TOT_CHG double, CHAR_OIL_QTS int, CUS_CODE int, foreign key (AC_NUMBER) references AIRCRAFT(AC_NUMBER) ); INSERT INTO CHARTER VALUES(10001,'2008-02-05','2289L','ATL',936,5.1,2.2,354.1,1,10011); INSERT INTO CHARTER VALUES(10002,'2008-02-05','2778V','BNA',320,1.6,0,72.6,0,10016);...
Create following table. CREATE TABLE Registration (Reg_ID number(5), Name Varchar2(20), Address Varchar2(20), create_date date, created_by varchar2(10)...
Create following table. CREATE TABLE Registration (Reg_ID number(5), Name Varchar2(20), Address Varchar2(20), create_date date, created_by varchar2(10) ); Create an audit trial report on Employee table for all insert, update and delete operations on given table. You have to create audit table first with Current Date, Operation and User to record the information.
CREATE TABLE Hotel ( roomNumber     INTEGER         PRIMARY KEY, type                  CHAR(1
CREATE TABLE Hotel ( roomNumber     INTEGER         PRIMARY KEY, type                  CHAR(10)         NOT NULL, rate                   INTEGER         NOT NULL, -- CONSTRAINT IC1 CHECK (type IN ('suite', 'king', 'queen')), CONSTRAINT IC2 CHECK (type <> 'suite' OR rate > 200), CONSTRAINT IC3 CHECK (NOT (type = 'king' AND (rate < 80 OR rate > 220))), CONSTRAINT IC4 CHECK (NOT (type = 'queen' AND rate >= 100)) ); which 8 of these inserts will be rejected only 8 are rejected 1. INSERT INTO Hotel VALUES (21, 'king', 90); 2. INSERT INTO Hotel...
Consider the following SQL DDL statements: CREATE TABLE DEPT ( did INTEGER, dname VARCHAR(20), PRIMARY KEY(did));...
Consider the following SQL DDL statements: CREATE TABLE DEPT ( did INTEGER, dname VARCHAR(20), PRIMARY KEY(did)); CREATE TABLE EMP( eid INTEGER, name VARCHAR(20), did INTEGER, PRIMARY KEY(eid), FOREIGN KEY(did) REFERENCES DEPT); In the database created by above statements, which of the following operations may cause violation of referential integrity constraints? Question 1 options: UPDATE on DEPT INSERT into DEPT DELETE on EMP Both DELETE on EMP and INSERT into DEPT
1. Use SQL to create a polyinstantiated table including a primary key and a unique constraint...
1. Use SQL to create a polyinstantiated table including a primary key and a unique constraint 2.Use SQL to insert multiple records for each security classification with the same ID. You must have 4 classifications. 3.Use SQL to create 4 schemas, one for each security classification 4.Use SQL to create a view in each schema that restricts the records to those belonging to a particular security classification and restricts the columns to only those columns that have relevant data. 5.Select...
Please convert this to work in Oracle SQL: CREATE TABLE GUEST ( SSN number(10), name varchar2(20),...
Please convert this to work in Oracle SQL: CREATE TABLE GUEST ( SSN number(10), name varchar2(20), DOB date, roomnumber number(10) ); INSERT INTO guest VALUES (1,'Lucy','2019-1-8',301); INSERT INTO guest VALUES (2,'John','2019-11-18',302); INSERT INTO guest VALUES (3,'Smith','2019-2-6',303); INSERT INTO guest VALUES (4,'Tom','2019-2-7',304); INSERT INTO guest VALUES (5,'Harry','2019-10-9',305); ) CREATE TABLE INVENTORY ( itemnumber number(10), itemneeded number(10), iteminstock number(10), dateofpurchase date ); INSERT INTO INVENTORY VALUES (21,10,60,'2019-1-8'); INSERT INTO INVENTORY VALUES (22,20,70,'2019-1-11'); INSERT INTO INVENTORY VALUES (23,30,75,'2019-2-4'); INSERT INTO INVENTORY VALUES (24,20,55,'2019-3-7');...
create table candidate ( cand_id   varchar(12) primary key,   -- cand_id name       varchar(40)           --...
create table candidate ( cand_id   varchar(12) primary key,   -- cand_id name       varchar(40)           -- cand_nm ); create table contributor ( contbr_id   integer primary key, name       varchar(40),           -- contbr_nm city     varchar(40),           -- contbr_city state       varchar(40),           -- contbr_st zip       varchar(20),           -- contbr_zip employer   varchar(60),           -- contbr_employer occupation   varchar(40)           -- contbr_occupation ); create table contribution ( contb_id   integer primary key, cand_id   varchar(12),           --...
ADVERTISEMENT
Subjects
ADVERTISEMENT
Latest Questions
ADVERTISEMENT