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 6 months 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.
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...
CREATE TABLE Branch ( branchNo VARCHAR(4), address VARCHAR(50), city VARCHAR(30), state VARCHAR(2), phone VARCHAR(20), PRIMARY KEY...
CREATE TABLE Branch ( branchNo VARCHAR(4), address VARCHAR(50), city VARCHAR(30), state VARCHAR(2), phone VARCHAR(20), PRIMARY KEY (branchNo)); INSERT INTO Branch VALUES('B001','366 Tiger Ln','Los Angeles','CA','213-539-8600'); INSERT INTO Branch VALUES('B002','18 Harrison Rd','New Haven','CT','203-444-1818'); INSERT INTO Branch VALUES('B003','55 Waydell St','Essex','NJ','201-700-7007'); INSERT INTO Branch VALUES('B004','22 Canal St','New York','NY','212-055-9000'); INSERT INTO Branch VALUES('B005','1725 Roosevelt Ave','Queens','NY','718-963-8100'); INSERT INTO Branch VALUES('B006','1471 Jerrold Ave','Philadelphia','PA','267-222-5252'); CREATE TABLE Staff ( staffNo VARCHAR(4), fName VARCHAR(20), lName VARCHAR(20), position VARCHAR(20), sex VARCHAR(1), age INTEGER, salary NUMBER(8,2), phone VARCHAR(20), address VARCHAR(50), city VARCHAR(20),...
There is a table t with a column salary which is the primary key. Write a...
There is a table t with a column salary which is the primary key. Write a query to get the nth minimum salary without using multi query or limit. You will be awarded no points if you use multi query or limit or rownum or rowid. no subquery are allowed. please solve it with joins
Research and analyze the critical needs of the hospital by creating a list of 10 key...
Research and analyze the critical needs of the hospital by creating a list of 10 key areas that would be addressed in the RFP. Examine and evaluate HIPAA, Electronic Health Records, and Quality Improvement initiatives. Examine and evaluate Information Technology and how IT works within a health care setting. Explain how IT and Administration can work in a collaborative environment Evaluate the policies and Human Resource guidelines of the hospital.
ADVERTISEMENT
Subjects
ADVERTISEMENT
Latest Questions
ADVERTISEMENT