complex-query.sql

CREATE DATABASE practice_complex_query

USE practice_complex_query

CREATE TABLE DEPT (
 DEPTNO              NUMERIC(2) NOT NULL,
 DNAME               VARCHAR(14),
 LOC                 VARCHAR(13),
 CONSTRAINT DEPT_PRIMARY_KEY PRIMARY KEY (DEPTNO));


INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');


CREATE TABLE EMP (
 EMPNO               NUMERIC(4) NOT NULL,
 ENAME               VARCHAR(10),
 JOB                 VARCHAR(9),
 MGR                 NUMERIC(4),
 HIREDATE            DATE,
 SAL                 NUMERIC(7,2),
 COMM                NUMERIC(7,2),
 DEPTNO              NUMERIC(2) NOT NULL,
 CONSTRAINT EMP_FOREIGN_KEY FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO),
 CONSTRAINT EMP_PRIMARY_KEY PRIMARY KEY (EMPNO),
 CONSTRAINT EMP_SELF_KEY FOREIGN KEY (MGR) REFERENCES EMP (EMPNO));
 
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,'1-11-81',5000,NULL,10);
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,'1-05-81',2850,NULL,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,'9-06-81',2450,NULL,10);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,'2-04-81',2975,NULL,20);
INSERT INTO EMP VALUES (7654,'03TIN','SALESMAN',7698,'8-09-81',1250,1400,30);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,'12-02-81',1600,300,30);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,'8-09-81',1500,0,30);
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,'3-12-81',950,NULL,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,'2-02-81',1250,500,30);
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,'3-12-81',3000,NULL,20);
INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,'7-12-80',800,NULL,20);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,'09-12-82',3000,NULL,20);
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,'1-01-83',1100,NULL,20);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,'3-01-82',1300,NULL,10);
 
CREATE TABLE BONUS (
 ENAME               VARCHAR(10),
 JOB                 CHAR(9),
 SAL                 NUMERIC,
 COMM                NUMERIC);
 
CREATE TABLE SALGRADE (
 GRADE               NUMERIC,
 LOSAL               NUMERIC,
 HISAL               NUMERIC);
 
INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);

select * from EMP
select * from DEPT
select * from SALGRADE

--1
select distinct e.ename, e.job, d.loc from EMP e, DEPT d
where e.DEPTNO = 10

--2
select e.ename, e.job, e.sal from EMP e where SAL > (select MAX(sal) from EMP
where JOB = 'ANALYST') and e.JOB != 'CLERK'

--3
select e.ename, e.empno, e.job, d.loc, e.sal from EMP e,
DEPT d where LOC = 'CHICAGO'

--4
select e.empno, e.job, d.dname, e.hiredate from EMP e,
DEPT d where SAL > (select MAX(sal) from EMP where ENAME = 'BLAKE')

--5.1 LEFT OUTER JOIN
select e.empno, e.job, d.dname, e.sal from EMP e LEFT OUTER JOIN DEPT d
on e.DEPTNO = d.DEPTNO where SAL in (select SAL from EMP where SAL > 2000)
order by SAL desc

--5.2 RIGHT OUTER JOIN
select e.empno, e.job, d.dname, e.sal from EMP e RIGHT OUTER JOIN DEPT d
on e.DEPTNO = d.DEPTNO where SAL in (select SAL from EMP where SAL > 2000)
order by SAL desc