(DBLab12) [SubQuery]

CREATE DATABASE lab12;

USE lab12;

CREATE TABLE employee
(
EMPLOYEE_ID INT PRIMARY KEY,
FIRST_NAME VARCHAR(50),
LAST_NAME VARCHAR(50),
HIRE_DATE DATETIME,
JOB_ID VARCHAR(50),
SALARY INT,
MANAGER_ID INT,
DEPARTMENT_ID INT
);

CREATE TABLE department
(
DEPARTMENT_ID INT PRIMARY KEY,
DEPARTMENT_NAME VARCHAR(50),
MANAGER_ID INT,
LOCATION_ID INT
);

INSERT INTO employee
VALUES(100,'Steven','King','2003-06-17','AD_PRES',24000,0,90);
INSERT INTO employee
VALUES(101,'Neena','Kochhar','2003-06-17','AD_VP',17000,100,90);
INSERT INTO employee
VALUES(102,'Lex','De Haan','2003-06-17','AD_VP',17000,100,90);
INSERT INTO employee
VALUES(103,'Alexander','Hunold','2003-06-17','IT_PROG',9000,102,60);
INSERT INTO employee
VALUES(104,'Steven','Kin','2003-06-17','IT_PROG',6000,103,60);
INSERT INTO employee
VALUES(105,'Sn','King','2003-06-17','IT_PROG',4800,103,60);
INSERT INTO employee
VALUES(106,'Steven','King','2003-06-17','IT_PROG',4800,103,60);
INSERT INTO employee
VALUES(107,'Steven','Bruce','2003-06-17','IT_PROG',4200,103,60);
INSERT INTO employee
VALUES(108,'Bruce','King','2003-06-17','FI_MGR',12008,101,100);
INSERT INTO employee
VALUES(109,'Steven','King','2003-06-17','FI_ACCOUNT',9000,108,100);
INSERT INTO employee
VALUES(110,'Stevn','King','2003-06-17','FI_ACCOUNT',8200,108,100);
INSERT INTO employee
VALUES(111,'Sen','King','2003-06-17','FI_ACCOUNT',7700,108,100);
INSERT INTO employee
VALUES(112,'Steven','King','2003-06-17','FI_ACCOUNT',7800,108,100);

INSERT INTO department
VALUES(10,'AdministratiON',200,1700);
INSERT INTO department
VALUES(20,'Marketing',200,1800);
INSERT INTO department
VALUES(30,'PurchASing',200,1700);
INSERT INTO department
VALUES(40,'Human Resources',200,2400);
INSERT INTO department
VALUES(50,'Shipping',200,1500);
INSERT INTO department
VALUES(60,'IT',200,1400);
INSERT INTO department
VALUES(70,'Public RelatiONs',200,2700);
INSERT INTO department
VALUES(80,'Sales',200,2500);
INSERT INTO department
VALUES(90,'Executive',200,1700);
INSERT INTO department
VALUES(100,'Finance',200,1700);
INSERT INTO department
VALUES(110,'Accounting',0,1700);
INSERT INTO department
VALUES(120,'TreASury',0,1700);
INSERT INTO department
VALUES(130,'CORpORate Tax',0,1700);
INSERT INTO department
VALUES(140,'CONtrol And Credit',0,1700);

SELECT * FROM
(SELECT employee.FIRST_NAME,employee.LAST_NAME,employee.EMPLOYEE_ID,employee.JOB_ID
FROM employee
WHERE employee.DEPARTMENT_ID=100)
AS newTABLE;

SELECT * FROM
(SELECT employee.DEPARTMENT_ID,SUM(employee.SALARY) AS totalSalary
FROM employee
GROUP BY employee.DEPARTMENT_ID)
AS newTABLE;

SELECT * FROM
(SELECT employee.EMPLOYEE_ID,employee.FIRST_NAME,employee.LAST_NAME
FROM employee
WHERE employee.FIRST_NAME LIKE '%T%' OR employee.LAST_NAME LIKE '%T%')
AS newTABLE;

SELECT * FROM
(SELECT employee.DEPARTMENT_ID,department.DEPARTMENT_NAME
FROM employee
JOIN department
ON employee.DEPARTMENT_ID=department.DEPARTMENT_ID)
AS newTABLE;

SELECT * FROM
(SELECT employee.FIRST_NAME
FROM employee
WHERE employee.DEPARTMENT_ID=90 AND employee.SALARY>(SELECT avg(employee.SALARY) FROM employee))
AS newTABLE;