날짜 : 2021-07-01
태그 : SQL 서브쿼리 복수행
메모
용어설명
deptno : 사원번호
ename : 사원이름
mgr : 사원의관리자번호
job : 직업
sal : 연봉
hiredate : 고용일자
emp : 사원정보 테이블
dept : 부서정보 테이블
dname : 부서의이름
loc : 부서의위치
salgrade : 연봉 등급테이블
grade : 등급
losal : 연봉 하한
hisal : 연봉 상한
1.1 복수행서브쿼리
서브쿼리의 실행결과가 하나의 행 이상일대 사용하는 서브쿼리임.
종류는 IN , ANY , ALL , EXIST 가 있다.
1.1.1 IN 연산자
select empno,ename,job,hiredate,sal,deptno
from emp
where sal in (select min(sal)
from emp
group by job);
--서브쿼리문은 행결과가 5개다, 따라서 복수행 연산자를 사용해야함
select ename , hiredate
from emp
where hiredate >
(select hiredate
from emp
where ename = 'FORD' );
--ford의 입사일보다 늦게 입사한사원의 이름,입사일
select ename , sal
from emp
where sal =
(select sal
from emp
where ename= 'FORD')
or
sal =
(select sal
from emp
where ename= 'WARD');
--처음시도한 SQL문 . 또는이라는 문장이들어가서 OR로 시도해보려다 실패했다.
select ename, sal
from emp
where sal in
(select ename,sal
from emp
where ename in('WARD','FORD') );
--올바른 정답 . ward 또는 ford 와 월급이 같은사람의 데이터출력
--간단하게 IN을 사용하여 복수조건들을 WHERE 절에 포함시켜주었다.1.1.2 ALL 연산자
ALL 연산자는 복수행의 결과를 < , > 대소 비교하고싶을때 사용하는연산자로 . 부등연산자는 단일행연산자이지만 ALL을 사용하게되면 여러개의 모든조건을 만족할때 < > 비교 할 수 있게된다.
select empno, ename, sal
from emp
where sal < all (
select sal
from emp
where job = 'MANAGER');
-- all 연산자 사용 , 여기서 직업이 매니저인 사람의 연봉이 여러개가나오는데 그 여러사람의 연봉보다 전부다 작은 사람의 연봉을 조건으로 사원정보를 조회하는 SQL문1.1.3 ANY 연산자
ANY 연산자는 ALL과 비슷한데 모든조건을 만족할필요없이 하나의 조건만 만족해도 사용할수있는 서브쿼리이다.
select empno, ename, sal
from emp
where sal < any
(select sal
from emp
where job = 'MANAGER');
-- 서브쿼리문 에서 JOB이 MANAGER인 사원들의연봉(복수개) 중에서 어느하나의값보다
--작은 연봉의 사원정보를 조회. 1.1.4 EXISTS 연산자
EXISTS 연산자는 서브쿼리에서 검색된결과가 하나라도 존재하면 메인쿼리를 실행하고 서브쿼리에서 검색된것이 아무것도 존재하지않으면 메인쿼리를 실행하지않는다.
select *
from emp
where exists (select empno
from emp
where empno is null);
-- EXISTS 는 서브쿼리가 거짓이면 메인쿼리도 아무것도 출력하지않는다.
-- 홈페이지 마이페이지 회원정보 꺼내오기로 사용될수있다.
--여기서 EMPNO는 고유기본값이기때문에(PK) NULL 값이 존재할수없다 따라서 참이고, 따라서 메인쿼리가 실행되어 모든 EMP 사원의 정보를 가져온다.1.2 다중컬럼 서브쿼리
서브쿼리에서 여러개의 컬럼값을 검색하여 메인쿼리의 조건절과 비교하는 서브쿼리임.
메인쿼리의 조건절은 서브쿼리의 컬럼과 일대일 매칭이되어야함.
컬럼을 묶어 동시비교하는 pairwise 방식과 컬럼별로나누어 비교하고 나중에 and 연산으로처리하는 unpairwise 방식이있음
select deptno,empno,ename,sal
from emp
where (deptno,sal) in
(select deptno, max(sal)
from emp
group by deptno);
--pairwise 방식이다. deptno와 sal의 모든조건이 서브쿼리와 일치해야 실행된다.
--여러개의부서(3개)에서에대해서 가장많은 월급을받는사람들
select deptno,empno,ename,sal
from emp
where deptno in
(select deptno
from emp
group by deptno)
and sal in
(select max(sal)
from emp
group by deptno);
--부서별로 가장많은 sal을 받는 사원정보출력
--unpairwise 이용한경우 컬럼별로 나누어 비교후 and 연산1.3 인라인 뷰(in-line view)
앞서 배운 서브쿼리는 메인쿼리의 where절에 사용된 서브쿼리이다. 하지만 from 절에도 서브쿼리가 사용가능하며 이렇게 from 절에서 사용된 서브쿼리를 ‘인라인 뷰 ‘라고 부른다.
기본문법은 FROM (서브쿼리) alias 이며 인라인뷰를사용하면 더 적은 데이터로 조인에참여하기때문에 매우효율적임.
select d.deptno , total_sum, total_avg, cnt, dname , loc
from (select deptno,sum(sal) total_sum , avg(sal) total_avg , count(*) cnt
from emp group by deptno) e , dept d
where e.deptno = d.deptno;
--인라인 뷰 (from)
--테이블에서 필요한컬럼만 뽑아서 가상의 테이블을만들고 그것으로부터 데이터를뽑아내는것.1.4 상관서브쿼리
책에없는 내용이고, 간단하게만 보고 넘어가자.
select e.empno , e.ename, e.sal ,
(select dname from dept where dept.deptno = e.deptno) "부서명"
from emp e
order by 1;
-- select 절에서 하나의 컬럼처럼 동작되는 서브쿼리.실습문제
--실습문제 1 사원테이블에서 blake 보다 급여가 많은 사원들의 사번이름급여 검색
select empno,ename, sal
from emp
where sal >
(select sal
from emp
where ename ='BLAKE');
--실습문제 2 MILLER 보다 늦게 입사한 사원의 정보를 검색하시오
select empno,ename,sal
from emp
where to_char(hiredate,'YYYY') <
(select to_char(hiredate,'YYYY')
from emp
where ename = 'MILLER');
--실습문제3 사원테이블에서 사원전체평균급여보다 급여가많은 사원들의..
select empno , ename , sal
from emp
where sal >
(select avg(sal)
from emp);
--실습문제4 사원테이블에서 부서별 최대 급여를 받는 사원들의 사번,이름,부서코드,급여검색
select empno,ename,deptno,sal
from emp
where sal in
(select max(sal)
from emp
group by deptno);
**--실습문제 5 salgrade 2등급인 / 사원들의 평균급여/ 보다 적게받는 사원정보를 검색하시오**
select ename ,sal
from emp
where sal <
(select avg(e.sal)
from emp e , salgrade s
where e.sal between losal and hisal and grade=2);
select ename ,sal
from emp
where sal <
(select avg(e.sal)
from emp e , salgrade s
where e.sal between losal and hisal and grade=2);
-- 처음 시도한 SQL문 실패원인 > 조인사용못함;
select ename ,sal
from emp
where sal <
(select avg(e.sal)
from emp e join salgrade s
on e.sal between losal and hisal
where grade=2);
-- 조인을사용한 선생님방법
**--실습문제6 SMITH의 급여등급과 / 같은등급의 사원이름과/ 등급을 검색하라**
select e.ename , s.grade
from emp e , salgrade s
where
(select grade
from emp e join salgrade s
on e.sal between losal and hisal)
=
(select s.grade
from emp e join salgrade s
on e.sal between losal and hisal
where ename='SMITH');
--처음 생각한코드
select e.ename , s.grade
from emp e join salgrade s
on e.sal between s.losal and s.hisal
where s.grade =
(select s.grade
from emp e join salgrade s
on e.sal between losal and hisal
where ename='SMITH');
--정답 .. join이 두개가들어가야한다 . 어렵다
**--실습문제7 SALES 부서의 인원수 / 보다 인원수가 작은 부서 의 부서명의 인원수를 검색하시오**
select d.dname , count(*)
from emp e, dept d
where e.deptno = d.deptno
and
(select count(*)
from emp e, dept d
where e.deptno=d.deptno
group by d.dname)
<
(select count(*)
from emp e ,dept d
where e.deptno = d.deptno
and d.dname = 'SALES')
group by d.dname ;
--내가시도한 답 틀린답.
select d.dname , count(*)
from emp e, dept d
where e.deptno=d.deptno
group by d.dname
having count (*) <
(select count(*)
from emp e ,dept d
where e.deptno = d.deptno
and d.dname = 'SALES');
--정답.
--실습문제8 WARD와 입사년도 같은 사원 이름과 입사년도를 검색
select ename ,to_char(hiredate,'YYYY') 입사년도
from emp
where to_char(hiredate,'YYYY') =
(select to_char(hiredate,'YYYY')
from emp
where ename ='WARD');
--실습문제 9 SMITH의 부서이름 부서지역 검색 (조인쓰지말고)
select dname , loc
from dept
where deptno = (select deptno
from emp
where ename='SMITH');
select dname , loc
from dept
where deptno in
(select emp.deptno
from emp join dept
on emp.ename='SMITH');
--조인이용
생각 (질문)
출처 (문헌)
학원에서 준 바인딩 책임