[SQL] SQL delete문 및 쿼리 연습

CREATE TABLE DEPT (
    DEPTNO DECIMAL(2),
    DNAME VARCHAR(14),
    LOC VARCHAR(13),
    CONSTRAINT PK_DEPT PRIMARY KEY (DEPTNO) 
);
CREATE TABLE EMP (
    EMPNO DECIMAL(4),
    ENAME VARCHAR(10),
    JOB VARCHAR(9),
    MGR DECIMAL(4),
    HIREDATE DATE,
    SAL DECIMAL(7,2),
    COMM DECIMAL(7,2),
    DEPTNO DECIMAL(2),
    CONSTRAINT PK_EMP PRIMARY KEY (EMPNO),
    CONSTRAINT FK_DEPTNO FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO)
);
CREATE TABLE SALGRADE ( 
    GRADE TINYINT,
    LOSAL SMALLINT,
    HISAL SMALLINT 
);
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');

INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,STR_TO_DATE('17-12-1980','%d-%m-%Y'),800,NULL,20);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,STR_TO_DATE('20-2-1981','%d-%m-%Y'),1600,300,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,STR_TO_DATE('22-2-1981','%d-%m-%Y'),1250,500,30);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,STR_TO_DATE('2-4-1981','%d-%m-%Y'),2975,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,STR_TO_DATE('28-9-1981','%d-%m-%Y'),1250,1400,30);
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,STR_TO_DATE('1-5-1981','%d-%m-%Y'),2850,NULL,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,STR_TO_DATE('9-6-1981','%d-%m-%Y'),2450,NULL,10);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,STR_TO_DATE('13-7-1987','%d-%m-%Y')-85,3000,NULL,20);
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,STR_TO_DATE('17-11-1981','%d-%m-%Y'),5000,NULL,10);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,STR_TO_DATE('8-9-1981','%d-%m-%Y'),1500,0,30);
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,STR_TO_DATE('13-7-1987', '%d-%m-%Y'),1100,NULL,20);
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,STR_TO_DATE('3-12-1981','%d-%m-%Y'),950,NULL,30);
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,STR_TO_DATE('3-12-1981','%d-%m-%Y'),3000,NULL,20);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,STR_TO_DATE('23-1-1982','%d-%m-%Y'),1300,NULL,10);

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

select * from dept;
select * from emp;
select * from salgrade;
-- 1정의 및 일반적인 세팅 완료 --

-- 1 사원 테이블의 모든 레코드 조회하기 --
select * from emp;

-- 1 mgr 이란 해당 사원의 매니저 사원 번호 --
select empno, ename, job, mgr, hiredate, sal, comm, deptno
from emp;

-- 2 사원명과 입사일을 조회 --
select empno, hiredate
from emp;

-- 3 사원번호와 이름 ---
select empno, ename
from emp;

-- 4 사원 테이블에 있는 직책의 목록 조회 --
select distinct job
from emp;

-- 5 총 사원수를 구하시오 --
select count(*)
from emp;

-- 6 부서 번호가 10인 사원 --
select *
from emp
where deptno =  10;

-- 7 월급여가 2500 이상 되는 사원 조회 --
select *
from emp
where sal >= 2500;

-- 8 이름이 king 인 사원 조회 --
select *
from emp
where ename = 'KING';

-- 9 사원 중 이름이 s 로 시작하는 사원의 사원번호와 이름을 조회하시오 --
select empno, ename
from emp
where ename like 'S%';

-- 10 사원 이름에 T 가 포함된 사원의 사원번호와 이름을 조회 --
select empno, ename
from emp
where ename like '%T%';

-- 11 커미션이 300, 500, 1400 인 사원의 사번, 이름, 커미션을 조회하기 --
select empno, ename, comm
from emp
where comm =300 or comm = 5000 or comm = 1400;

select empno, ename, comm
from emp
where comm in (300,500,1400);


-- 12 월 급여가 1200 에서 3500 사이의 사원의 사번, 이름, 월급여를 조회 --
select empno, ename, sal
from emp
where sal between 1200 and 3500;

-- 13 직급이 매니저이고, 부서번호가 30인 사원의 이름,사번,직급,부서번호를 조회하기 --
select ename,empno,job,deptno
from emp
where job ='MANAGER' and deptno=30;

-- 14 커미션이 300, 500, 1400 이 모두 아닌 사원의 사번, 이름, 커미션을 조회 --
select empno, ename, comm
from emp
where comm not in (300, 500, 1400);

-- 15 이름에 S 가 포함되지 않는 사원의 사번, 이름을 조회 --
select empno, ename
from emp
where ename not like '%S%';

-- 16 급여가 1200 보다 미만이거나 3700 초과하는 사원의 사번, 이름, 월급여를 조회 --
SELECT empno, ename, sal
FROM emp
WHERE sal < 1200 or sal > 3700;

-- 17 직속상사가 null 인 사원의 이름과 직급을 조회 -- 
select ename, job
from emp
where mgr is null;

-- 18 group by 구문 --
select deptno, avg(sal)
from emp;

-- 19 부서별 평균월급여를 구하는 쿼리 --
select deptno, avg(sal)
from emp
group by deptno;

-- 20 부서별 전체 사원수와 커미션을 받는 사원들의 수를 구하는 쿼리 --
select deptno, count(*), count(comm)
from emp
group by deptno;

-- 21 부서별 최대 급여와 최소 급여를 구하는 쿼리 -- 
select deptno, max(sal), min(sal)
from emp
group by deptno;

-- 22 부서별로 급여 평균(단, 부서별로 급여 평균이 2000 이상만) , 에러 구문 첫번째, 두번째 가능--
select deptno, avg(sal)
from emp
where avg(sal) >= 2000;
group by deptno;

select deptno, avg(sal)
from emp
group by deptno
having avg(sal) >= 2000;

select deptno, avg(sal)
from emp
where sal >= 1000
group by deptno
having avg(sal) >= 2000;

-- 23 급여가 높은 순으로 조회하되 급여가 같을 경우 이름의 철자가 빠른 사원 순으로 사번, 이름, 월급여를 조회 --
select empno, ename, sal
from emp
order by sal desc, ename asc;

-- 24 조인은 2개 이상의 테이블에서 데이터를 조회할 때 사용, 테이블 n개를 조인할 때 n-1 개의 조인 조건이 필요--
-- 카테시안 곱, 단순 조인, 앞으로 나오는 조인 예제는 이러한 단순 조인 결과를 머리속에 그리면서 싨ㅂ, 총 56개의 행은 emp 테이블에 존재하는 14개의 레코드와 dept 테이블에 존재하는
-- 4개의 레코드의 곱으로 생성  --
select emp.ename, dept.dname
from emp, dept; 

-- 25 사원명과 부서명을 조회하시오, 기존의 emp의 부서번호가 부서이름으로 변경됨 --
select e.ename, d.dname
from emp e, dept d
where e.deptno = d.deptno;

-- 26 mgr --
select e.ename, m.ename
from emp e
inner join emp m
on e.mgr = m.empno;

-- 27 이름, 월급여, 월급여 등급을 조회 --
select e.ename, e.sal, s.grade
from emp e, salgrade s
where e.sal >= s.losal and e.sal <= s.hisal;

select e.ename, e.sal, s.grade
from emp e, salgrade s
where e.sal between s.losal and s.hisal;

-- 28 이름, 부서명, 월급여 등급을 조회(난이도 ★★)-- 
select e.ename, d.dname, s.grade
from emp e, dept d, salgrade s
where e.deptno = d.deptno
and e.sal between s.losal and s.hisal;

-- 29 직원의 이름, 직속 상사 이름을 조회 --
select e.ename, b.ename
from emp e, emp b
where e.mgr = b.empno; -- 직원의 상사(우측)  = 일반 테이블에서 해당 사원 번호, 구하고자 하는 것이 왼쪽

-- 29번과의 차이점은? --
select e.name, b.ename
from emp e, emp b
where e.empno = b.mgr;

-- 29 번은 회장(president) 에 대한 레코드가 배제됨, mgr 컬럼이 null이고 사원번호가 null인 사원은 없기 때문에 조인조건에 만족하지 않는다.
-- 그럼에도 불구하고, 결과에 회장 레코드를 보여야 한다면 아래처럼 질의해야 한다. 난이도 : ★★
select e.ename, m.mgr
from emp e
left join emp m
on e.mgr = m.empno; -- outer join : 외부 조인이라고 하는데, A left join B : 조인 조건에 만족하지 못해도 왼쪽 테이블의 A의 행을 나타내고 싶을때 사용한다.


-- 30. 이름, 부서명을 조회하시오 -- 
-- 단 사원 테이블에 부서번호가 40에 속한 사원이 없지만 부서번호 40인 부서명도 출력되도록 하시오 --

select e.ename, d.dname
from dept d 
left join emp e
on d.deptno = e.deptno;


-- 31 이름 부서번호 부서이름을 조회 --
-- from 에 dept 가 먼저 오면, 사원은 null 이지만, 부서번호가 보이는 경우이므로, 부족한 쪽인 emp가 left join에서 먼저 오게 된다
select e.ename, d.deptno, d.dname
from emp e 
left join dept d
on d.deptno = e.deptno; 

-- 32 부서번호가 30번인 사원들의 이름, 직급, 부서번호, 부서위치를 조회, 일부 겹친 내용을 조회하는 경우, 난이도 : ★★--
select e.ename, job, d.deptno, loc
from emp e
inner join dept d
on e.deptno = d.deptno
where d.deptno = 30;

-- 32 inner join 말고 다른 풀이
select e.ename, job, d.deptno, loc
from emp e, dept d
where e.deptno = d.deptno and d.deptno = 30;

-- 33 커미션을 받는 사원의 이름, 커미션, 부서이름, 부서위치를 조회 --
select ename, comm, dname, loc
from emp e, dept d
where e.deptno = d.deptno
and e.comm is not null and comm <> 0;


select ename, comm, dname, loc
from emp e, dept d
where e.deptno = d.deptno
and e.comm is not null and e.comm != 0;

select ename, comm, dname, loc
from emp e
inner join dept d
on e.deptno = d.deptno
where e.comm is not null and e.comm not in (0);

-- 34 Dallas 에서 근무하는 사원의 이름, 직급, 부서번호, 부서명을 조회 --
select e.ename, e.job, d.deptno, d.dname
from emp e
inner join dept d
on e.deptno = d.deptno
where d.loc = 'Dallas';

-- 34 의 다른 버전 --
select e.ename, e.job, d.deptno, d.dname
from emp e, dept d
where e.deptno = d.deptno 
and d.loc = 'Dallas';


-- 35 이름에 a가 들어가는 사워느이 이름, 부서명을 조회 --
select e.ename, d.dname
from emp e
inner join dept d
on e.deptno = d.deptno
where e.ename like '%A%';

-- 35의 다른 버전 --
select e.ename, d.dname
from emp e, dept d
where e.deptno = d.deptno and e.ename like '%A%';

-- 36 이름, 직급, 월급여, 월급여등급을 조회 --
select e.ename, e.job, e.sal, s.grade
from emp e, salgrade s
where e.sal between s.losal and s.hisal;


-- 37 allen 과 같은 부서에 근무하는 사원의 이름, 부서번호를 조회 --,--e : 엘런, c: 엘런을 제외한 같은 부서에 근무하는 사람들
-- 즉, e.ename 이 아닌, c.ename 이 되어야 한다
select c.ename, e.deptno
from emp e, emp c
where e.empno <> c.empno
and e.deptno = c.deptno
and e.ename = 'ALLEN'
order by c.ename;
-- 헤깔린다 . --

-- 37 inner join 활용 -- 
select b.ename, a.deptno
from emp a
inner join emp b
on a.deptno = b.deptno
where a.empno <> b.empno
and a.ename = 'ALLEN'
order by b.ename;


-- 38 서브 쿼리 --
-- select 문 안에서 () 로 둘러싸인 select 문을 말하며, 쿼리문의 결과를 메인 쿼리로 전달하기 위해 사용

select dname
from dept
where deptno = (select deptno from emp where ename = 'JONES');


-- 39 10번 부서에서 근무하는 사원의 이름과 10번 부서의 부서명을 조회 --

select e.ename, d.dname
from emp e, 
(
	select deptno, dname
	from dept
	where deptno = 10
) d
where e.deptno = d.deptno;

-- 39번 쉬운 풀이 --
select e.ename, d.dname
from emp e, dept d
where e.deptno = d.deptno
and d.deptno =10;

-- 40 평균 월급여보다 더 많은 월급여를 받는 사원의 사원번호, 이름, 월급여 조회 --
select e.empno, e.ename, e.sal
from emp e
where sal > (select avg(sal)
			from emp)
order by sal desc;

-- 41 부서번호가 10인 사원중에서 최대급여를 받는 사원과 동일한 급여를 받는 사원의 사원번호, 이름을 조회 --
select e.empno, e.ename
from emp e
where sal = (select max(sal)
			from emp
			where deptno = 10);
            
            
-- 42 insert 문 --
desc emp;
select * from emp;

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) 
values(1567 '홍길동','SALESMAN',7698,sysdate(),2000,1000,30);

-- 일부 열 데이터값 누락 --
insert into emp (empno, ename, job, mgr, hiredate, sal, deptno) 
values(1697, '장보고','MANAGER',7839,sysdate()-1,3000,30);

-- 42 사원테이블 장보고 월급여 2800으로 변경 --
UPDATE emp
SET sal = 2800
where ename = '장보고';

select * from emp;

-- 43 사원테이블에서 홍길동 월급여 300 인상 및 comm은 500으로 수정 --
update emp
set sal = sal + 300, comm = 500
where ename = '홍길동';

commit; -- update 하고 내용 저장하고 싶은 경우, commit 실행하기
select * from emp;

-- 44. delete 문, 홍길동,장보고 삭제 --
delete from emp
where ename in ('홍길동','장보고');