Use ORACLE For each of the following, Write the SQL & RA (RELATIONAL ALGEBRA) statements that satisfy the requirements using given database COMPANY. Don't use Multi Table select in SQL and Cross Product in Relational Algebra for these queries.

1-List the names sex, and salary of the employees who earn more than the average salary of the female employees. 2-List the names and salaries of the employees along with the total number of hours they have spent on all project. 3-List the names and salaries of the employees along with the total number of hours spent on projects if they have spent less than 20 hours total. 4-List the name and salaries of all the employees along with the total number of their dependents. (You must show data for all the employees even if they do not have any dependent) 5-For each department list the department name and the average salary of the employees working in that department.

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','1965-01-09','731 Fondren, Houston, TX','M',30000,'333445555',5); INSERT INTO Employee VALUES ('Franklin', 'T', 'Wong', 333445555, '1955-12-08', '638 Voss, Houston, TX', 'M', 40000, 888665555, 5); INSERT INTO Employee VALUES ('Alicia','J','Zelaya','999887777','1968-01-19','3321 Castle, Spring, TX','F',25000,'987654321',4); INSERT INTO Employee VALUES ('Jennifer', 'S', 'Wallace', 987654321, '1941-06-20', '291 Berry, Bellaire, TX', 'F', 43000, 888665555, 4); INSERT INTO Employee VALUES ('Ramesh','K','Narayan','666884444','1962-09-15','971 Fire Oak, Humble, TX','M',38000,'333445555',5); INSERT INTO Employee VALUES ('Joyce','A','English','453453453','1972-07-31','5631 Rice Oak, Houston, TX','F',25000,'333445555',5); INSERT INTO Employee VALUES ('Ahmad','V','Jabbar','987987987','1969-03-29','980 Dallas, Houston, TX','M',25000,'987654321',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', '1988-05-22'); INSERT INTO Department VALUES ('Administration', 4, '987654321', '1995-01-01'); INSERT INTO Department VALUES ('Headquarters', 1, '888665555', '1981-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 (ssn) REFERENCES employee(ssn) ON DELETE cascade);

INSERT INTO Dependent VALUES ('333445555','Alice','F','1986-04-05','Daughter'); INSERT INTO Dependent VALUES ('333445555','Theodore','M','1983-10-25','Son'); INSERT INTO Dependent VALUES ('333445555','Joy','F','1958-05-03','Spouse'); INSERT INTO Dependent VALUES ('987654321','Abner','M','1942-02-28','Spouse'); INSERT INTO Dependent VALUES ('123456789','Michael','M','1988-01-04','Son'); INSERT INTO Dependent VALUES ('123456789','Alice','F', '1988-12-30','Daughter'); INSERT INTO Dependent VALUES ('123456789','Elizabeth','F','1967-05-05','Spouse');

SELECT * FROM DEPENDENT;