Company DDL

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 DML
 
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; 
  Log in to Oracle SQL Developer/Oracle Live SQL and answer the following questions. 1) For the EMPLOYEE table, use the rank() function to rank the employees by salary like this: (3 points) ENAME LNAME SALARY_RANK 1 2 3 James Jennifer Franklin Ramesh John Ahmad 4 Borg Wallace Wong Narayan Smith Jabbar Zelaya English 5 6 6 Alicia Joyce 6 Insert your answer between the two horizontal lines: 2) Repeat the previous question by ranking the employees within each department like this: (2 points) ENAME LNAME DNO SALARY_RANK James Borg 1 1 Jennifer Wallace 4 1 Ahmad Jabbar 4 2 Alicia Zelaya 4 2 Franklin Wong 5 1 Ramesh Narayan 5 2 John Smith 5 3 Joyce English 5 4 Insert your answer between the two horizontal lines: 3) Create the following table, where EMP_ID is the primary key, and then answer the questions that follow: (2 points) EMP NAME EMP_ID SALES($) Mike 230823567 1500 Peter 440876567 2500 Peter 921126821 950 810015710 2500 Angelo Robert 672899012 1950 3.1 Using the rank() function, produce a table showing the employee id, name, and sales rank like this: (2 points) EMP_ID EMP_NAME SALES_RANK 440876567 Peter 1 810015710 Angelo 1 672899012 Robert 3 230823567 Mike 4 921126821 Peter 5 Insert your answer between the two horizontal lines