[SQL] 3. 条件文～サブクエリ

1.条件式

-case-when-then条件文

select mgr,
case nvl(mgr,0) when 0 then 'n' --case 변수 when 값1 then 결과1
else 'y' 
end as mgr_yn 
from emp;

-条件比較

select sal,
case when (sal between 1000 and 2000) then '1' 
     when (sal between 1000 and 2000) then '2' 
     when (sal between 1000 and 2000) then '3' 
else '5' 
end as kr 
from emp;

<注意事項>
-条件文と条件文の間にカンマ(,)は使用されません.
-CASE文はENDで終わる必要があります.
-CASE式ではANSI SQL形式もサポートされています.
-decode関数(Oracleのみ)

select deptno,
decode(deptno,10,'십',20,'이십',30,'삼십','기타') as kr 
from emp;

:deptnoは10=10、20=20、30=30で、残りはその他です.

2.グループ関数

-max(),min(),avg(),sum(),count(1)
グループ関数はselectキーワードの後に配置されます.複数のグループ関数をカンマで区切って使用できます.そして関数を重ねて使うことができます.ex) max(avg( ) )
-distict():重複キーワードの削除

select count(distinct department_id)
from empoyees;

3.GROUP BY節

groupbyは行をグループ化します.まず、行を部門番号でグループ化し、次に行を部門番号グループから役職IDでグループ化します.

**group by columnはselectにコピーされます.
group by colum 1,colum 2->select colum 1,colum 2,つまりgroup byの後ろに書かれたcolumnはselectの後ろにあるはずです.一方、グループ関数(5個)はgroupbyに関係なくselectの後に使用でき、groupbyを使用する場合は、目的に合ったグループ関数をselectの後に入れる必要がある.
*whereセクションは制限グループには使用できませんので、グループを制限するにはhaveセクションを使用します.グループ関数はwhereセクションでは使用できません.

SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id **
HAVING MAX(salary)>10000 ; **

SELECT job_id, SUM(salary) PAYROLL
FROM employees
WHERE job_id NOT LIKE '%REP%'
GROUP BY job_id **
HAVING SUM(salary) > 13000 **
ORDER BY SUM(salary):

4. Join

1) inner join

各テーブルは、結合条件を満たすデータのみを取得します.

select * 
from emp,dept --2개 이상의 테이블이 from절에 있을 때 join을 사용한다.
where emp.deptno = dept.deptno --join & 조건 추가 가능 (and~)
order by emp.deptno asc, dept.deptno asc;

2つのテーブルが重なるカラムを使用する場合は、テーブル名を使用する必要があります.コラムを明確に記載しなければならない.
-ANSI構文条件

select dname,e.deptno,ename
from dept d 
join emp e
on d.deptno=e.deptno --join
where d.deptno = 10; --연결한 후의 조건

2) self join

自分を別のテーブルとして、お互いにサインします.

select e1.empno,e1.ename,e1.mgr,e2.ename,e1.deptno
from emp e1, emp2
where e1.mgr= e2.empno and e1.deptno = 10; --join & 조건

-ANSI構文条件

select e1.empno,e1.ename,e1.mgr,e2.ename,e1.deptno
from emp e1
join emp e2
on e1.mgr= e2.empno 
where e1.deptno = 10; --조건

3) outer join

結合条件に一致するデータと一致しないデータはSELECTであり、結合条件に一致するデータがなければNULLをインポートする.

select *
from emp e, dept d
where e.deptno (+) = d.deptno;

(+)をつまらない継ぎ手に入れます.すなわち,empテーブルではdeptnoが10,20,30であり,deptテーブルでは10,20,30,40のデータ値がある.
Empのdeptnoの数は少ないので,ここに(+)を加える.
-ANSI構文条件
deptnoの数は、より多くの種類のページに準拠しています.=right

select *
from emp e 
right outer join dept d --오른쪽 기준
on e.deptno = d.deptno;

左を基準にすると?

select * 
from dept d 
left outer join emp e -- 왼쪽 기준으로 outer join
on e.deptno = d.deptno;

5. Subquery

-1行サブクエリ:サブクエリの結果は1行
-複数行サブクエリ:サブクエリの結果が複数行
*1行サブクエリでは不等号を使用できますが、複数行サブクエリでは不等号を単独で使用することはできません.そのため、in、all、anyなどを使用する必要があります.(inは単一または複数のクエリーで使用可能)
-NY:そのうちの1つは満足、ALL:すべて満足
-サブクエリがfromセクションから来た場合:inline view(行内ビュー)
-サブクエリがselectセクションにある場合:スカラー量子クエリ
-通常、サブクエリはwhereセクションによく表示されます.

--10번 부서 사람이랑 직업이 같은 사람
select * 
from emp 
where job in (select job from emp where deptno = 10);

--EMP 테이블에서 가장 많은 사원을 갖는 MGR번호 출력
select mgr 
from emp 
group by mgr 
having count(1) = (select max(count(1)) from emp group by mgr);

--dual 테이블에서 부서번호가 10인 사원수와 부서번호가 30인 사원수를 각각 출력
select (select count(1) from emp where deptno = 10) as CNT10,
(select count(1) from emp where deptno = 30) as CNT30 from dual;

--EMP 테이블에서 각 부서 별 입사일이 가장 오래된 사원을 한 명씩 선별해 사원번호, 사원명, 부서번호, 입사일을 출력

select empno,ename,deptno,min(hiredate) 
from emp
group by deptno,empno,ename,deptno
having min(hiredate) in (select min(hiredate) from emp group by deptno);

--CHICAGO 지역에 근무중인 사원 중 BLAKE가 직속상관인 사원들의 사원번호(empno), 이름(ename), 직무(job) 출력
select empno,ename,job 
from emp
where deptno = (select deptno from dept where loc='CHICAGO')
and mgr = (select empno from emp where ename = 'BLAKE')

--3000 이상의 급여를 받는 사원들과 같은 부서에 근무하고 있는 사원의 사원번호(empno), 이름(ename), 급여(sal)출력
select empno,ename,sal 
from emp
where deptno in (select deptno from emp where sal>=3000);