Beginner Oracle SQL Programming

Table script:

CREATE TABLE VS_Department01 (

DEPARTMENT_ID NUMBER(5),

DEPARTMENT_NAME VARCHAR2(50),

MANAGER_ID NUMBER(5),

LOCATION_ID NUMBER(5)

);

INSERT INTO VS_Department01 VALUES (10,'Administration',200,1700);

INSERT INTO VS_Department01 VALUES (20,'Marketing',201,1800);

INSERT INTO VS_Department01 VALUES (30,'Purchasing',114,1700);

INSERT INTO VS_Department01 VALUES (40,'Human Resources',203,2400);

INSERT INTO VS_Department01 VALUES (50,'Shipping',121,1500);

INSERT INTO VS_Department01 VALUES (60,'IT',103,1400);

INSERT INTO VS_Department01 VALUES (70,'Public Relations',204,2700);

INSERT INTO VS_Department01 VALUES (80,'Sales',145,2500);

INSERT INTO VS_Department01 VALUES (90,'Executive',100,1700);

INSERT INTO VS_Department01 VALUES (100,'Finance',108,1700);

INSERT INTO VS_Department01 VALUES (110,'Accounting',205,1700);

COMMIT;

select * from VS_Department01;

CREATE TABLE VS_Employee01 (

EMPLOYEE_ID NUMBER(5),

FIRST_NAME VARCHAR2(30),

LAST_NAME VARCHAR2(30),

EMAIL VARCHAR2(30),

PHONE_NUMBER NUMBER(20),

HIRE_DATE date,

JOB_ID VARCHAR2(10),

SALARY NUMBER(10,2),

COMMISSION_PCT NUMBER(3,2),

MANAGER_ID NUMBER(5),

DEPARTMENT_ID NUMBER(5)

);

INSERT INTO VS_Employee01 VALUES (100,'Steven','King','SKING','5151234567','17-JUN-2006','AD_PRES',24000.00,0.00,0,90);

INSERT INTO VS_Employee01 VALUES (101,'Neena','Kochhar','NKOCHHAR','5151234568','21-SEP-2005','AD_VP',17000.00,0.00,100,90);

INSERT INTO VS_Employee01 VALUES (102,'Lex','De Haan','LDEHAAN','5151234569 ','13-JAN-2001','AD_VP',17000.00,0.00,100,90);

INSERT INTO VS_Employee01 VALUES (103,'Alexander','Hunold','AHUNOLD','5904234567','03-JAN-2006','IT_PROG',9000.00,0.00,102,60);

INSERT INTO VS_Employee01 VALUES (104,'Bruce','Ernst','BERNST','5904234568','21-MAY-2007','IT_PROG',6000.00,0.00,103,60);

INSERT INTO VS_Employee01 VALUES (105,'David','Austin','DAUSTIN','5904234569','25-JUN-2005','IT_PROG',4800.00,0.00,103,60);

INSERT INTO VS_Employee01 VALUES (106,'Valli','Pataballa','VPATABAL','5904234560','05-FEB-2006','IT_PROG',4800.00,0.00,103,60);

INSERT INTO VS_Employee01 VALUES (107,'Diana','Lorentz','DLORENTZ','5904235567','07-FEB-2007','IT_PROG',4200.00,0.00,103,60);

INSERT INTO VS_Employee01 VALUES (108,'Nancy','Greenberg','NGREENBE','5151244569','17-AUG-2002','FI_MGR',12008.00,0.00,101,100);

INSERT INTO VS_Employee01 VALUES (109,'Daniel','Faviet','DFAVIET','5151244169','16-AUG-2002','FI_ACCOUNT',9000.00,0.00,0,100);

INSERT INTO VS_Employee01 VALUES (110,'John','Chen','SKING','5151234567','28-SEP-2005','FI_ACCOUNT',8200.00,0.00,108,100);

INSERT INTO VS_Employee01 VALUES (111,'Ismael','Sciarra','ISCIARRA','5151244369','30-SEP-2005','FI_ACCOUNT',7700.00,0.00,108,100);

INSERT INTO VS_Employee01 VALUES (112,'Jose Manuel','Urman','JMURMAN','5151244469','07-MAR-2006','FI_ACCOUNT',7800.00,0.00,108,100);

INSERT INTO VS_Employee01 VALUES (113,'Luis','Pop','LPOPP','5151244567','07-DEC-2007','FI_ACCOUNT',6900.00,0.00,108,100);

SELECT * FROM VS_Employee01;

Create table JOB_GRADE

(GRADE_LEVEL varchar2(5),

LOWEST_SAL NUMBER(6),

HIGHEST_SAL NUMBER(6));

INSERT INTO JOB_GRADE VALUES ('A',1000 ,2999);

INSERT INTO JOB_GRADE VALUES ('B',3000 ,5999);

INSERT INTO JOB_GRADE VALUES ('C',6000 ,9999);

INSERT INTO JOB_GRADE VALUES ('D',10000 ,14999);

INSERT INTO JOB_GRADE VALUES ('E',15000 ,24999);

INSERT INTO JOB_GRADE VALUES ('F',25000 ,40000);

c) Write a query in SQL to display the first and last name and salary for those employees who earn less than the employee earn whose number is 110. d) Use the group by clause to group the salaries by department. Be sure to list the department name and the salary totals for each department. [4 Points] e) Use the group by clause to group the salaries by department. Be sure to list the department name and all departments that have salaries of $10000 or more