Here is the company database for the problem --comments can be added DROP TABLE
ID: 3799369 • Letter: H
Question
Here is the company database for the problem
--comments can be added DROP TABLE works_on; DROP TABLE dependent; DROP TABLE project; DROP TABLE dept_location; ALTER TABLE department DROP CONSTRAINT fk_mgrssn CASCADE; ALTER TABLE employee DROP CONSTRAINT fk_dno CASCADE; DROP TABLE department; DROP TABLE employee;
CREATE TABLE employee (fnameVARCHAR2(12), minitCHAR(1), lnameVARCHAR2(12), ssnCHAR(9) NOT NULL, bdateDATE, addressVARCHAR2(40), sexCHAR(1), salaryNUMBER(7) NOT NULL, superssnCHAR(9) references employee(ssn), /*superssnCHAR(9),*/ /*either way, but need to know superssn is from ssn*/ CONSTRAINT pk_ssn PRIMARY KEY (SSN));
ALTER TABLE employee ADD (dno NUMBER(2) NOT NULL);
INSERT INTO employee VALUES ('Wolons', 'E', 'Aimee', '888665555', '10-NOV-1927', '450 Stone, edsion, NJ', 'F', 55000, null,1);
INSERT INTO employee VALUES ('Ramirez', '', 'Damian', '987654321', '20-JUN-1931', '291 Berry, albany, NY', 'M', 43000, '888665555',4);
INSERT INTO employee VALUES ('Hall', '', 'Nathan', '333445555', '09-DEC-1945', '638 bruce, Oneonta, NY', 'M', 40000, '888665555',5); INSERT INTO employee VALUES ('Kelly', '', 'David', '999778888', '19-JUL-1958', '3321 central ave, New York, NY', 'M', 25000, '987654321',4);
INSERT INTO employee VALUES ('Philip', '', 'Dolensek', '123456789', '09-JAN-1955', '731 clinton, Oneonta, NY', 'M', 30000, '333445555',5);
INSERT INTO employee VALUES ('Reale', '', 'Michael', '666884444', '15-SEP-1952', '975 Fire Oak, Newark, NJ', 'M', 38000, '333445555',5); INSERT INTO employee VALUES ('Smith', '', 'Jason', '453453453', '31-JUL-1962', '5631 Rice, Harrison, NJ', 'F', 25000, '333445555',5); INSERT INTO employee VALUES ('Warren', 'V', 'Samantha', '987987987', '29-MAR-1959', '908 Dallas, albany, NY', 'F', 25000, '987654321',4);
select e.fname, e.lname, s.fname, s.lname from employee e, employee s where e.superssn=s.ssn;
CREATE TABLE department (dnameVARCHAR2(20) NOT NULL, dnumberNUMBER(2) NOT NULL, mgrssnCHAR(9) NOT NULL, mgrstartdateDATE, CONSTRAINT pk_dnumber PRIMARY KEY (DNUMBER), CONSTRAINT fk_mgrssn FOREIGN KEY (mgrssn) REFERENCES employee (ssn));
INSERT INTO department VALUES ('Research',5 , '333445555', '22-MAY-1978'); INSERT INTO department VALUES ('Administration',4 , '987654321', '01-JAN-1985'); INSERT INTO department VALUES ('Computing Service', 1, '888665555', '19-JUN-1971');
ALTER TABLE employee ADD (CONSTRAINT fk_dno FOREIGN KEY (dno) REFERENCES department(dnumber));
CREATE TABLE dept_location (dnumberNUMBER(2) , dlocationVARCHAR2(20), CONSTRAINT pk_num_loc PRIMARY key (dnumber, dlocation), CONSTRAINT fk_dnumber FOREIGN KEY (dnumber) REFERENCES department(dnumber));
INSERT INTO dept_location VALUES (1,'Fitzelle'); INSERT INTO dept_location VALUES (4,'Hunt union'); INSERT INTO dept_location VALUES (5,'Natza'); INSERT INTO dept_location VALUES (5,'Hunt union'); INSERT INTO dept_location VALUES (5,'Fitzelle');
CREATE TABLE project (pnameVARCHAR2(15), pnumberNUMBER(2) NOT NULL, plocationVARCHAR2(15) , dnumNUMBER(2), CONSTRAINT pk_pnumber PRIMARY KEY (PNUMBER), CONSTRAINT fk_dnum FOREIGN KEY (dnum) REFERENCES department(dnumber));
INSERT INTO project VALUES ('ProductX', 1,'library',5); INSERT INTO project VALUES ('ProductY', 2,'Fitzelle hall',5); INSERT INTO project VALUES ('ProductZ', 3,'Library',5); INSERT INTO project VALUES ('Computerization', 10,'IRC',4); INSERT INTO project VALUES ('Reorganization', 20,'IRC',1); INSERT INTO project VALUES ('Newbenefits', 30,'Hunt Union',4);
CREATE TABLE dependent (essnCHAR(9) NOT NULL, dependent_name VARCHAR(9) NOT NULL, sexVARCHAR2(1), bdateDATE, relationshipVARCHAR(8), CONSTRAINT pk_essn PRIMARY KEY (essn, dependent_name), CONSTRAINT fk_essn FOREIGN KEY (essn) REFERENCES employee(ssn) ON DELETE CASCADE);
INSERT INTO dependent VALUES ('333445555','Alice', 'F','05-APR-1976','Daughter'); INSERT INTO dependent VALUES ('333445555','Theodore', 'M','10-OCT-1973','Son'); INSERT INTO dependent VALUES ('333445555','Joy', 'F','03-MAY-1948','Spouse'); INSERT INTO dependent VALUES ('987654321','Abner', 'M','29-FEB-1932','Spouse'); INSERT INTO dependent VALUES ('123456789','John', 'M','01-JAN-1978','Son'); INSERT INTO dependent VALUES ('123456789','Alice', 'M','31-DEC-1978','Daughter'); INSERT INTO dependent VALUES ('123456789','Elizabeth', 'F','05-MAY-1957','Spouse');
CREATE TABLE works_on (essnCHAR(9) NOT NULL, pnoNUMBER(2) NOT NULL, hoursNUMBER(3,1), CONSTRAINT pk_essn_pno PRIMARY KEY (essn, pno), CONSTRAINT fk_pno FOREIGN KEY (pno) REFERENCES project(pnumber));
INSERT INTO works_on VALUES ('123456789',1,32.5); INSERT INTO works_on VALUES ('123456789',2,7.5); INSERT INTO works_on VALUES ('666884444',3,40.0); INSERT INTO works_on VALUES ('453453453',1,20.0); INSERT INTO works_on VALUES ('453453453',2,20.0); INSERT INTO works_on VALUES ('333445555',2,10.0); INSERT INTO works_on VALUES ('333445555',3,10.0); INSERT INTO works_on VALUES ('333445555',10,10.0); INSERT INTO works_on VALUES ('333445555',20,10.0); INSERT INTO works_on VALUES ('999887777',30,30.0); INSERT INTO works_on VALUES ('999887777',10,10.0); INSERT INTO works_on VALUES ('987987987',10,35.0); INSERT INTO works_on VALUES ('987987987',30,5.0); INSERT INTO works_on VALUES ('987654321',30,20.0); INSERT INTO works_on VALUES ('987654321',20,15.0); INSERT INTO works_on VALUES ('888665555',20,null); --comments can be added DROP TABLE works_on; DROP TABLE dependent; DROP TABLE project; DROP TABLE dept_location; ALTER TABLE department DROP CONSTRAINT fk_mgrssn CASCADE; ALTER TABLE employee DROP CONSTRAINT fk_dno CASCADE; DROP TABLE department; DROP TABLE employee;
CREATE TABLE employee (fnameVARCHAR2(12), minitCHAR(1), lnameVARCHAR2(12), ssnCHAR(9) NOT NULL, bdateDATE, addressVARCHAR2(40), sexCHAR(1), salaryNUMBER(7) NOT NULL, superssnCHAR(9) references employee(ssn), /*superssnCHAR(9),*/ /*either way, but need to know superssn is from ssn*/ CONSTRAINT pk_ssn PRIMARY KEY (SSN));
ALTER TABLE employee ADD (dno NUMBER(2) NOT NULL);
INSERT INTO employee VALUES ('Wolons', 'E', 'Aimee', '888665555', '10-NOV-1927', '450 Stone, edsion, NJ', 'F', 55000, null,1);
INSERT INTO employee VALUES ('Ramirez', '', 'Damian', '987654321', '20-JUN-1931', '291 Berry, albany, NY', 'M', 43000, '888665555',4);
INSERT INTO employee VALUES ('Hall', '', 'Nathan', '333445555', '09-DEC-1945', '638 bruce, Oneonta, NY', 'M', 40000, '888665555',5); INSERT INTO employee VALUES ('Kelly', '', 'David', '999778888', '19-JUL-1958', '3321 central ave, New York, NY', 'M', 25000, '987654321',4);
INSERT INTO employee VALUES ('Philip', '', 'Dolensek', '123456789', '09-JAN-1955', '731 clinton, Oneonta, NY', 'M', 30000, '333445555',5);
INSERT INTO employee VALUES ('Reale', '', 'Michael', '666884444', '15-SEP-1952', '975 Fire Oak, Newark, NJ', 'M', 38000, '333445555',5); INSERT INTO employee VALUES ('Smith', '', 'Jason', '453453453', '31-JUL-1962', '5631 Rice, Harrison, NJ', 'F', 25000, '333445555',5); INSERT INTO employee VALUES ('Warren', 'V', 'Samantha', '987987987', '29-MAR-1959', '908 Dallas, albany, NY', 'F', 25000, '987654321',4);
select e.fname, e.lname, s.fname, s.lname from employee e, employee s where e.superssn=s.ssn;
CREATE TABLE department (dnameVARCHAR2(20) NOT NULL, dnumberNUMBER(2) NOT NULL, mgrssnCHAR(9) NOT NULL, mgrstartdateDATE, CONSTRAINT pk_dnumber PRIMARY KEY (DNUMBER), CONSTRAINT fk_mgrssn FOREIGN KEY (mgrssn) REFERENCES employee (ssn));
INSERT INTO department VALUES ('Research',5 , '333445555', '22-MAY-1978'); INSERT INTO department VALUES ('Administration',4 , '987654321', '01-JAN-1985'); INSERT INTO department VALUES ('Computing Service', 1, '888665555', '19-JUN-1971');
ALTER TABLE employee ADD (CONSTRAINT fk_dno FOREIGN KEY (dno) REFERENCES department(dnumber));
CREATE TABLE dept_location (dnumberNUMBER(2) , dlocationVARCHAR2(20), CONSTRAINT pk_num_loc PRIMARY key (dnumber, dlocation), CONSTRAINT fk_dnumber FOREIGN KEY (dnumber) REFERENCES department(dnumber));
INSERT INTO dept_location VALUES (1,'Fitzelle'); INSERT INTO dept_location VALUES (4,'Hunt union'); INSERT INTO dept_location VALUES (5,'Natza'); INSERT INTO dept_location VALUES (5,'Hunt union'); INSERT INTO dept_location VALUES (5,'Fitzelle');
CREATE TABLE project (pnameVARCHAR2(15), pnumberNUMBER(2) NOT NULL, plocationVARCHAR2(15) , dnumNUMBER(2), CONSTRAINT pk_pnumber PRIMARY KEY (PNUMBER), CONSTRAINT fk_dnum FOREIGN KEY (dnum) REFERENCES department(dnumber));
INSERT INTO project VALUES ('ProductX', 1,'library',5); INSERT INTO project VALUES ('ProductY', 2,'Fitzelle hall',5); INSERT INTO project VALUES ('ProductZ', 3,'Library',5); INSERT INTO project VALUES ('Computerization', 10,'IRC',4); INSERT INTO project VALUES ('Reorganization', 20,'IRC',1); INSERT INTO project VALUES ('Newbenefits', 30,'Hunt Union',4);
CREATE TABLE dependent (essnCHAR(9) NOT NULL, dependent_name VARCHAR(9) NOT NULL, sexVARCHAR2(1), bdateDATE, relationshipVARCHAR(8), CONSTRAINT pk_essn PRIMARY KEY (essn, dependent_name), CONSTRAINT fk_essn FOREIGN KEY (essn) REFERENCES employee(ssn) ON DELETE CASCADE);
INSERT INTO dependent VALUES ('333445555','Alice', 'F','05-APR-1976','Daughter'); INSERT INTO dependent VALUES ('333445555','Theodore', 'M','10-OCT-1973','Son'); INSERT INTO dependent VALUES ('333445555','Joy', 'F','03-MAY-1948','Spouse'); INSERT INTO dependent VALUES ('987654321','Abner', 'M','29-FEB-1932','Spouse'); INSERT INTO dependent VALUES ('123456789','John', 'M','01-JAN-1978','Son'); INSERT INTO dependent VALUES ('123456789','Alice', 'M','31-DEC-1978','Daughter'); INSERT INTO dependent VALUES ('123456789','Elizabeth', 'F','05-MAY-1957','Spouse');
CREATE TABLE works_on (essnCHAR(9) NOT NULL, pnoNUMBER(2) NOT NULL, hoursNUMBER(3,1), CONSTRAINT pk_essn_pno PRIMARY KEY (essn, pno), CONSTRAINT fk_pno FOREIGN KEY (pno) REFERENCES project(pnumber));
INSERT INTO works_on VALUES ('123456789',1,32.5); INSERT INTO works_on VALUES ('123456789',2,7.5); INSERT INTO works_on VALUES ('666884444',3,40.0); INSERT INTO works_on VALUES ('453453453',1,20.0); INSERT INTO works_on VALUES ('453453453',2,20.0); INSERT INTO works_on VALUES ('333445555',2,10.0); INSERT INTO works_on VALUES ('333445555',3,10.0); INSERT INTO works_on VALUES ('333445555',10,10.0); INSERT INTO works_on VALUES ('333445555',20,10.0); INSERT INTO works_on VALUES ('999887777',30,30.0); INSERT INTO works_on VALUES ('999887777',10,10.0); INSERT INTO works_on VALUES ('987987987',10,35.0); INSERT INTO works_on VALUES ('987987987',30,5.0); INSERT INTO works_on VALUES ('987654321',30,20.0); INSERT INTO works_on VALUES ('987654321',20,15.0); INSERT INTO works_on VALUES ('888665555',20,null); 3. (20 points) In Oracle, tables, procedures etc. are called objects. Each object has an owner. For example, if you create a table, you are the owner of the table. Therefore, by default, your account is the owner of all the objects created by you your account. Whenever you retrieve information from a table named as tablename, Oracle will automatically append your account name as the prefix to the table name ending with imiter. Then check table tablen exists or not. yes Oracle certainly allows you to retrieve information from that table. For example, when I say select from employee: it actually is equivalent to saying that select from sl4zhangs. employee: where my account name. therwise, if you want to use a table owned by another account, you have to explicitly use owner account of the table. For example, your account name is tom, but you want to retrieve information from a table ti created by Kathy in the same database, you have to use select from Kathy. ti instead of select from t1 a. List the result of the select from zhangsfallo5. employee: hint you will be shown that ORA-00942: table or view does not exist, but the reason is that you have no privilege to do so b. st the result of select from employee: if you have no employee table, create that table by running the company script, then redo this query c. list the result of select from youraccount. employee: note that you need to use your oracle account in the query. The result should be the same as b) d. show script how to create a synonym for employee table, and then select all from both the original table and the synonym just created by you to compare the result. The result should be the same as b and c create synonym s for employee e. show script how to create a public synonym for employee table. Hint, you will find that you have no privilege to create public synonym. create public synonym ps for employee ERROR at line 1: ORA-00942: table or view does not exist you need explicit GRANT select privilege grant select on ps to yourpartnerid
Explanation / Answer
Output of select * from employee is below :
fname minit lname ssn bdate address sex salary superssn
Wolons E Aimee 888665555 10-NOV-1927 450 Stone, edsion, NJ F 55000 null
Ramirez Damian 987654321 20-JUN-1931 291 Berry, albany, NY M 43000 8886655550
Hall Nathan 333445555 09-DEC-1945 638 bruce, Oneonta, NY M 40000 888665555
Kelly David 999778888 19-JUL-1958 3321 central ave, New York, NY M 25000 987654321
Philip Dolensek 123456789 09-JAN-1955 731 clinton, Oneonta, NY M 30000 333445555
Reale Michael 666884444 15-SEP-1952 975 Fire Oak, Newark, NJ M 38000 333445555
Smith Jason 453453453 31-JUL-1962 5631 Rice, Harrison, NJ F 25000 333445555
Warren V Samantha 987987987 29-MAR-1959 908 Dallas, albany, NY F 25000 987654321
Please let me know what else is required.