(Solved) : Retrieve First Last Name Employees Names Project Work List Projects Controlled Administra Q44135271 . . .
Retrieve first and last name of employees and the names of theproject they work for. Only list projects that are controlled byAdministration department.
TABLE
CREATE TABLE employee ( fname varchar(15) not null, minit varchar(1), lname varchar(15) not null, ssn char(9), bdate date, address varchar(50), sex char, salary decimal(10,2), Super_ssn char(9), dno char(4), primary key (ssn));CREATE TABLE department ( dname varchar(25) not null, dnumber char(4), Mgr_ssn char(9) not null, Mgr_start_date date, primary key (dnumber));CREATE TABLE dept_locations ( dnumber char(4), dlocation varchar(15), primary key (dnumber,dlocation));CREATE TABLE project ( pname varchar(25) not null, pnumber char(4), plocation varchar(15), dnum char(4) not null, primary key (pnumber), unique (pname));CREATE TABLE works_on ( essn char(9), pno char(4), hours decimal(4,1), primary key (essn,pno));CREATE TABLE dependent ( essn char(9), dependent_name varchar(15), sex char, bdate date, relationship varchar(8), primary key (essn,dependent_name));//STEP #2INSERT INTO employee VALUES (‘John’,’B’,’Smith’,’123456789′,TO_DATE(’09-JAN-1965′,’DD-MON-YYYY’),’731 Fondren, Houston, TX’,’M’,30000,333445555,5);INSERT INTO employee VALUES (‘Franklin’,’T’,’Wong’,’333445555′,TO_DATE(’08-DEC-1955′,’DD-MON-YYYY’),’638 Voss, Houston, TX’,’M’,40000,’888665555′,5);INSERT INTO employee VALUES (‘Alicia’,’J’,’Zelaya’,’999887777′,TO_DATE(’19-JUL-1968′,’DD-MON-YYYY’),’3321 Castle, Spring, TX’,’F’,25000,’987654321′,4);INSERT INTO employee VALUES (‘Jennifer’,’S’,’Wallace’,’987654321′,TO_DATE(’20-JUN-1941′,’DD-MON-YYYY’),’291 Berry, Bellaire, TX’,’F’,43000,’888665555′,4);INSERT INTO employee VALUES (‘Ramesh’,’K’,’Narayan’,’666884444′,TO_DATE(’15-SEP-1962′,’DD-MON-YYYY’),’975 Fire Oak, Humble, TX’,’M’,38000,’333445555′,5);INSERT INTO employee VALUES (‘Joyce’,’A’,’English’,’453453453′,TO_DATE(’31-JUL-1972′,’DD-MON-YYYY’),’5631 Rice, Houston, TX’,’F’,25000,’333445555′,5);INSERT INTO employee VALUES (‘Ahmad’,’V’,’Jabbar’,’987987987′,TO_DATE(’29-MAR-1969′,’DD-MON-YYYY’),’980 Dallas, Houston, TX’,’M’,25000,’987654321′,4);INSERT INTO employee VALUES (‘James’,’E’,’Borg’,’888665555′,TO_DATE(’10-NOV-1937′,’DD-MON-YYYY’),’450 Stone, Houston, TX’,’M’,55000,null,1);INSERT INTO department VALUES (‘Research’, 5, ‘333445555’, TO_DATE(’22-MAY-1988′,’DD-MON-YYYY’));INSERT INTO department VALUES (‘Administration’, 4, ‘987654321’, TO_DATE(’01-JAN-1995′,’DD-MON-YYYY’));INSERT INTO department VALUES (‘Headquarters’, 1, ‘888665555’, TO_DATE(’19-JUN-1981′,’DD-MON-YYYY’));INSERT INTO project VALUES (‘ProductX’,1,’Bellaire’,5);INSERT INTO project VALUES (‘ProductY’,2,’Sugarland’,5);INSERT INTO project VALUES (‘ProductZ’,3,’Houston’,5);INSERT INTO project VALUES (‘Computerization’,10,’Stafford’,4);INSERT INTO project VALUES (‘Reorganization’,20,’Houston’,1);INSERT INTO project VALUES (‘Newbenefits’,30,’Stafford’,4);INSERT INTO dept_locations VALUES (1,’Houston’);INSERT INTO dept_locations VALUES (4,’Stafford’);INSERT INTO dept_locations VALUES (5,’Bellaire’);INSERT INTO dept_locations VALUES (5,’Sugarland’);INSERT INTO dept_locations VALUES (5,’Houston’);INSERT INTO dependent VALUES (‘333445555′,’Alice’,’F’,TO_DATE(’05-APR-86′,’DD-MON-YY’),’Daughter’);INSERT INTO dependent VALUES (‘333445555′,’Theodore’,’M’,TO_DATE(’25-OCT-83′,’DD-MON-YY’),’Son’);INSERT INTO dependent VALUES (‘333445555′,’Joy’,’F’,TO_DATE(’03-MAY-58′,’DD-MON-YY’),’Spouse’);INSERT INTO dependent VALUES (‘987654321′,’Abner’,’M’,TO_DATE(’28-FEB-42′,’DD-MON-YY’),’Spouse’);INSERT INTO dependent VALUES (‘123456789′,’Michael’,’M’,TO_DATE(’01-JAN-78′,’DD-MON-YY’),’Son’);INSERT INTO dependent VALUES (‘123456789′,’Alice’,’F’, TO_DATE(’31-DEC-88′,’DD-MON-YY’),’Daughter’);INSERT INTO dependent VALUES (‘123456789′,’Elizabeth’,’F’,TO_DATE(’05-MAY-67′,’DD-MON-YY’),’Spouse’);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);//STEP#3ALTER TABLE EMPLOYEEADD CONSTRAINT employee_superssn_fk FOREIGN KEY (Super_ssn) REFERENCES employee (ssn);ALTER TABLE EMPLOYEEADD CONSTRAINT employee_dno_fk FOREIGN KEY (Dno) REFERENCES department (dnumber);ALTER TABLE departmentADD CONSTRAINT department_mgrssn_fk FOREIGN KEY (Mgr_ssn) REFERENCES employee (ssn);ALTER TABLE dept_locationsADD CONSTRAINT deptlocations_dnumber_fk foreign key (dnumber) references department(dnumber);ALTER TABLE projectADD CONSTRAINT project_dnum_fk foreign key (dnum) references department(dnumber);ALTER TABLE works_onADD CONSTRAINT workson_essn_fk foreign key (essn) references employee(ssn);ALTER TABLE works_onADD CONSTRAINT workson_pno_fk foreign key (pno) references project(pnumber);ALTER TABLE dependentADD CONSTRAINT dependent_essn_fk foreign key (essn) references employee(ssn);
Expert Answer
Answer to Retrieve first and last name of employees and the names of the project they work for. Only list projects that are contro…
OR