9. Retrieve all employees whose address is in Houston, Texas.

10. Find all employees who were born during the 1950s.

11. Show the resulting salaries if every employee working on the ProductX project is given a 10 percent raise.

12. Retrieve the name of each employee who works on all the projects controlled by department number 5.

12. Retrieve the names of employees who have no dependents.

13. For each project on which more than two employees work, retrieve the project number, the project name, and the number of employees who work on the project.

14. For each department that has more than five employees, retrieve the department number and the number of its employees who are making more than $40,000.

15. Add an attribute for keeping track of jobs of employees to the employee table

create database COMPANY; use COMPANY;

CREATE TABLE EMPLOYEE ( fname varchar(15) DEFAULT NULL, minit varchar(1) DEFAULT NULL, lname varchar(15) DEFAULT NULL, ssn char(9) NOT NULL, bdate date DEFAULT NULL, address varchar(50) DEFAULT NULL, sex char DEFAULT NULL, salary decimal(10,2) DEFAULT NULL, superssn char(9) DEFAULT NULL, dno int(4) DEFAULT NULL, PRIMARY KEY (ssn)); INSERT INTO Employee VALUES ('John','B','Smith','123456789','1955-01-09','731 Fondren, Houston, TX','M',30000,'333445555',5); INSERT INTO Employee VALUES ('Alicia','J','Zelaya','999887777','1958-07-19','3321 Castle, Spring, TX','F',25000,'987654321',4); INSERT INTO Employee VALUES ('Ramesh','K','Narayan','666884444','1952-09-15','971 Fire Oak, Humble, TX','M',38000,'333445555',5); INSERT INTO Employee VALUES ('Joyce','A','English','453453453','1983-07-31','5631 Rice Oak, Houston, TX','F',25000,'333445555',5); INSERT INTO Employee VALUES ('Ahmad','V','Jabbar','987987987','1979-03-29','980 Dallas, Houston, TX','M',25000,'987654321',4); INSERT INTO Employee VALUES ('Franklin', 'T', 'Wong', 333445555, '1955-12-08', '638 Voss, Houston, TX', 'M', 40000, 888665555, 5); INSERT INTO Employee VALUES ('Jennifer', 'S', 'Wallace', 987654321, '1941-06-20', '291 Berry, Bellaire, TX', 'F', 43000, 888665555, 4); INSERT INTO Employee VALUES ('James', 'E', 'Borg', 888665555, '1937-11-10', '450 Stone, Houston, TX', 'M', 55000, NULL, 1); ALTER TABLE Employee ADD ( FOREIGN KEY (dno) REFERENCES department(dnumber), FOREIGN KEY (superssn) REFERENCES employee(ssn) on delete cascade);

SELECT * FROM EMPLOYEE;

CREATE TABLE DEPARTMENT ( dname varchar(25) DEFAULT NULL, dnumber int(4) NOT NULL, mgrssn char(9) DEFAULT NULL, mgrstartdate date DEFAULT NULL, PRIMARY KEY (dnumber), FOREIGN KEY (mgrssn) REFERENCES employee(ssn)on delete cascade); INSERT INTO Department VALUES ('Research', 5, '333445555', '1978-05-22'); INSERT INTO Department VALUES ('Administration', 4, '987654321', '1985-01-01'); INSERT INTO Department VALUES ('Headquarters', 1, '888665555', '1971-06-19');

SELECT * FROM DEPARTMENT;

CREATE TABLE DEPT_LOCATIONS ( dnumber int(4) NOT NULL, dlocation varchar(15) NOT NULL, PRIMARY KEY (dnumber, dlocation), FOREIGN KEY (dnumber) REFERENCES department(dnumber)on delete cascade);

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

SELECT * FROM DEPT_LOCATIONS;

CREATE TABLE WORKS_ON ( essn char(9) NOT NULL, pno int(4) NOT NULL, hours decimal(4,1) DEFAULT NULL, PRIMARY KEY (essn, pno), FOREIGN KEY (essn) REFERENCES employee(ssn), FOREIGN KEY (pno) REFERENCES project(pnumber)on delete cascade);

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

SELECT * FROM WORKS_ON;

CREATE TABLE PROJECT ( pname varchar(25) DEFAULT NULL, pnumber int(4) NOT NULL, plocation varchar(15) DEFAULT NULL, dnum int(4) NOT NULL, PRIMARY KEY (pnumber), FOREIGN KEY (dnum) REFERENCES department(dnumber)on delete cascade) ;

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

SELECT * FROM PROJECT;

CREATE TABLE DEPENDENT( essn char(9) NOT NULL, dependent_name varchar(15) NOT NULL, sex char DEFAULT NULL, bdate date DEFAULT NULL, relationship varchar(8) DEFAULT NULL, PRIMARY KEY (essn, dependent_name), FOREIGN KEY (essn) REFERENCES employee(ssn) ON DELETE cascade);

INSERT INTO Dependent VALUES ('333445555','Alice','F','1996-03-01','Daughter'); INSERT INTO Dependent VALUES ('333445555','Theodore','M','1993-05-12','Son'); INSERT INTO Dependent VALUES ('333445555','Joy','F','1970-07-08','Spouse'); INSERT INTO Dependent VALUES ('987654321','Abner','M','1958-02-25','Spouse'); INSERT INTO Dependent VALUES ('123456789','Michael','M','1978-01-01','Son'); INSERT INTO Dependent VALUES ('123456789','Alice','F', '1978-12-08','Daughter'); INSERT INTO Dependent VALUES ('123456789','Elizabeth','F','1957-05-07','Spouse');

SELECT * FROM DEPENDENT;