DATABSAE I

1. Find the first name, middle initial and last name of all employees who work on exactly 2 projects. (2.5 points)

2. Produce a relation showing the department name and the name of the project controlled by that department. (2 points)

3. Show how you can decrease the salaries of all female employees by 5% using SQL.

Company Schema DDL Script:

create table DEPARTMENT ( DNAME VARCHAR(30), DNUMBER NUMBER(4) PRIMARY KEY, MGR_SSN CHAR(9), MGR_START_DATE DATE); create table EMPLOYEE ( FNAME VARCHAR(30), MINIT CHAR(1), LNAME VARCHAR(30), SSN CHAR(9) PRIMARY KEY, BDATE DATE, ADDRESS VARCHAR(30), SEX CHAR(1), SALARY NUMBER(5), SUPER_SSN CHAR(9), DNO NUMBER(4)); ALTER TABLE EMPLOYEE ADD CONSTRAINT SEXCHECK CHECK (SEX IN ('M','F')); ALTER TABLE EMPLOYEE ADD CONSTRAINT SALARYCHECK CHECK (SALARY >= 1000 AND SALARY  
 
 
Company Schema DML Script:
 
INSERT INTO DEPARTMENT VALUES('Research',5,null,to_date('1988-05-22','yyyy-mm-dd')); INSERT INTO DEPARTMENT VALUES('Administration',4,null,to_date('1995-01-01','yyyy-mm-dd')); INSERT INTO DEPARTMENT VALUES('Headquarters',1,null,to_date('1981-06-19','yyyy-mm-dd')); INSERT INTO EMPLOYEE VALUES('James','E','Borg','888665555',to_date('1937-11-10', 'yyyy-mm-dd'), '450 Stone,Houston,TX','M',55000,null,'1'); INSERT INTO EMPLOYEE VALUES('Franklin','T','Wong','333445555',to_date('1955-12-08','yyyy-mm-dd'),'638 Voss,Houston,TX','M',40000,'888665555','5'); INSERT INTO EMPLOYEE VALUES('John','B','Smith','123456789',to_date('1965-01-09','yyyy-mm-dd'),'731 Fondren,Houston,TX','M',30000,'333445555','5'); INSERT INTO EMPLOYEE VALUES('Jennifer','S','Wallace','987654321',to_date('1941-06-20', 'yyyy-mm-dd'),'291 Berry,Bellaire,TX','F',43000,'888665555','4'); INSERT INTO EMPLOYEE VALUES('Alicia','J','Zelaya','999887777',to_date('1968-01-19', 'yyyy-mm-dd'),'3321 Castle,Spring,TX','F',25000,'987654321','4'); INSERT INTO EMPLOYEE VALUES('Ramesh','K','Narayan','666884444',to_date('1962-09-15', 'yyyy-mm-dd'),'975 Fire Oak,Humble,TX','M',38000,'333445555','5'); INSERT INTO EMPLOYEE VALUES('Joyce','A','English','453453453',to_date('1972-07-31', 'yyyy-mm-dd'),'5631 Rice,Houston,TX','F',25000,'333445555','5'); INSERT INTO EMPLOYEE VALUES('Ahmad','V','Jabbar','987987987',to_date('1969-03-29', 'yyyy-mm-dd'),'980 Dallas,Houston,TX','M',25000,'987654321','4'); UPDATE DEPARTMENT SET MGR_SSN='333445555' WHERE DNUMBER=5; UPDATE DEPARTMENT SET MGR_SSN='987654321' WHERE DNUMBER=4; UPDATE DEPARTMENT SET MGR_SSN='888665555' WHERE DNUMBER=1; 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 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 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); INSERT INTO WORKS_ON VALUES('987654321',20,15.0); INSERT INTO WORKS_ON VALUES('888665555',20,null); INSERT INTO DEPENDENT VALUES('333445555','Alice','F',to_date('1986-04-05','yyyy-mm-dd'),'Daughter'); INSERT INTO DEPENDENT VALUES('333445555','Theodore','M',to_date('1983-10-25','yyyy-mm-dd'),'Son'); INSERT INTO DEPENDENT VALUES('333445555','Joy','F',to_date('1958-05-03','yyyy-mm-dd'),'Spouse'); INSERT INTO DEPENDENT VALUES('987654321','Abner','M',to_date('1942-02-28','yyyy-mm-dd'),'Spouse'); INSERT INTO DEPENDENT VALUES('123456789','Michael','M',to_date('1988-01-04','yyyy-mm-dd'),'Son'); INSERT INTO DEPENDENT VALUES('123456789','Alice','F',to_date('1988-12-30','yyyy-mm-dd'),'Daughter'); INSERT INTO DEPENDENT VALUES('123456789','Elizabeth','F',to_date('1967-05-05','yyyy-mm-dd'),'Spouse'); commit; 
  Figure 5.6 One possible database state for the COMPANY relational database schema. EMPLOYEE Minit Dno B 5 Fname John Franklin Alicia 5 T J 4 Jennifer S Lname Ssn Bdate Address Sex Salary Super_ssn Smith 1234567891965-01-09 731 Fondren, Houston, TX M 30000 333445555 Wong 333445555 1955-12-08 638 Voss, Houston, TX M 40000 888665555 Zelaya 999887777 1968-01-19 3321 Castle, Spring, TX F 25000 987654321 Wallace 987654321 1941-06-20 291 Berry, Bellaire, TX F 43000 888665555 Narayan 666884444 1962-09-15 975 Fire Oak, Humble, TX M 38000 333445555 English 453453453 1972-07-31 5631 Rice, Houston, TX F 25000 333445555 Jabbar 987987987 1969-03-29 980 Dallas, Houston, TX M 25000 987654321 Borg 888665555 1937-11-10 450 Stone, Houston, TX M 55000 NULL 4 K 5 Ramesh Joyce Ahmad A 5 V 4 James E 1 DEPARTMENT DEPT_LOCATIONS Dname Dnumber Mgr_start_date Dnumber Dlocation Research 5 Mgr_ssn 333445555 987654321 1988-05-22 1 Houston 4 1995-01-01 4 Administration Headquarters 1 888665555 1981-06-19 5 Stafford Bellaire Sugarland Houston 5 5 WORKS ON PROJECT Essn Pno Hours Pnumber Plocation Dnum 1 1 5 123456789 123456789 32.5 25 2 2 Bellaire Sugarland Houston 5 3 40.0 Pname ProductX ProductY Productz Computerization Reorganization Newbenefits 3 666884444 453453453 453453453 5 1 20.0 10 4 2 20.0 20 Stafford Houston Stafford 1 333445555 2 10.0 30 4 3 10.0 333445555 333445555 10 10.0 DEPENDENT 333445555 20 10.0 Essn Dependent_name Sex Bdate 999887777 30 30.0 Alice 999887777 10 10.0 F M F 987987987 10 1986-04-05 1983-10-25 1958-05-03 1942-02-28 Theodore Joy Abner 35.0 5.0 333445555 333445555 333445555 987654321 123456789 123456789 123456789 Relationship Daughter Son Spouse Spouse Son Daughter Spouse 987987987 30 M 987654321 30 20.0 1 M 1988-01-04 Michael Alice 987654321 20 15.0 F F 1988-12-30 1967-05-05 F 888665555 20 NULL Elizabeth