PER THIS QUERY

/* 1 =================================================================*/

CREATE TABLE EMPLOYEE ( FNAME CHAR (20) NOT NULL, MINIT CHAR (20) NOT NULL, LNAME CHAR (20) NOT NULL, SSN INT NOT NULL, BDATE DATE NOT NULL, ADDRESS VARCHAR (100) NOT NULL, SEX CHAR (1) NOT NULL, SALARY INT NOT NULL, SUPER_SSN INT , DNO INT NOT NULL, PRIMARY KEY(SSN) );

INSERT INTO EMPLOYEE VALUES ('JOHN' , 'B' , 'SMITH' , 123456789 ,TO_DATE('1965-01-09','YYYY-MM-DD') , '731 FONDREN,HOUSETON,TX' , 'M' , 30000 , 333445555 , 5 ); 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 ('ALICIA' , 'J' , 'ZELAYA' , 999887777 , TO_DATE(' 1968-01-19','YYYY-MM-DD') , '3321 CASSTLE, SPRING,TX' , 'F' , 25000 , 987654321 , 4 ); 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 ('RAMESH' , 'K' , 'Naraya' , 666884444 , TO_DATE('1962-09-15' ,'YYYY-MM-DD'), '975 FIREOAK ,HUMBLE ,TX' , 'M' , 38000 , 333445555 , 5 ); INSERT INTO EMPLOYEE VALUES ('JOYCE' , 'A' , 'English' , 453453453 , TO_DATE(' 1972-07-31' ,'YYYY-MM-DD') , '638 VOSS, HOUSTON,TX' , 'F' , 25000 , 333445555 , 5 ); INSERT INTO EMPLOYEE VALUES ('AHMED' , 'V' , 'Jabbar' , 987987987 , TO_DATE('1969-03-29' ,'YYYY-MM-DD') , '980 DALLAS, HOUSTON, TX' , 'M' ,25000 , 987654321 , 4 ); INSERT INTO EMPLOYEE VALUES ('JAMES','E' , 'Borg' , 888665555 , TO_DATE('1937-11-10' ,'YYYY-MM-DD') , '450 STONE, HOUSTON .TX' , 'M' ,55000 , NULL , 1 );

/* 2 =================================================================*/

CREATE TABLE DEPARTMENT ( DNAME CHAR (20) NOT NULL, DNUMBER INT NOT NULL, MGR_SSN INT NOT NULL, MGR_START_DATE DATE NOT NULL, PRIMARY KEY(DNUMBER), FOREIGN KEY (MGR_SSN) REFERENCES EMPLOYEE(SSN) );

INSERT INTO DEPARTMENT VALUES ( 'RESEARCH' , 5 , 333445555 , TO_DATE('1988-05-22' ,'YYYY-MM-DD')); INSERT INTO DEPARTMENT VALUES ( 'ADMINISTRATION' , 4 , 987654321 , TO_DATE('1995-01-01' ,'YYYY-MM-DD')); INSERT INTO DEPARTMENT VALUES ( 'HEADQUARTERS' , 1 , 888665555 , TO_DATE('1981-06-19' ,'YYYY-MM-DD'));

/* 3 =================================================================*/

CREATE TABLE DEPT_LOCATIONS ( DNUMBER INT NOT NULL, DLOCATION CHAR(20) NOT NULL, FOREIGN KEY (DNUMBER) REFERENCES DEPARTMENT(DNUMBER) );

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

/* 4 =================================================================*/

CREATE TABLE PROJECT ( PNAME CHAR (20) NOT NULL, PNUMBER INT NOT NULL, PLOCATION CHAR (20) NOT NULL, DNUM INT NOT NULL, PRIMARY KEY (PNUMBER) );

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 ('NEWBENFITS' ,30 , 'STAFFORD' ,4 );

/* 5 =================================================================*/

CREATE TABLE WORKS_ON ( ESSN INT NOT NULL, PNO INT NOT NULL, HOURS INT , FOREIGN KEY (ESSN) REFERENCES EMPLOYEE(SSN), 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 );

/* 6 =================================================================*/

CREATE TABLE DEPENDENT ( ESSN INT NOT NULL, DEPENDENT_NAME CHAR (20) NOT NULL, SEX CHAR(2) NOT NULL, BDATE DATE NOT NULL, RELATIONSHIP CHAR(20) NOT NULL, PRIMARY KEY (DEPENDENT_NAME, ESSN), FOREIGN KEY (ESSN) REFERENCES EMPLOYEE (SSN) );

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

SELECT * FROM EMPLOYEE; SELECT * FROM DEPARTMENT; SELECT * FROM PROJECT; SELECT * FROM WORKS_ON; SELECT * FROM DEPT_LOCATIONS; SELECT * FROM DEPENDENT;

17. For each department whose average employee salary is more than $40,000, retrieve the department name and the number of employees working for that department.