[빅데이터분석] SQL_TIL 12 (단일행 서브쿼리, 다중행 서브쿼리, NOT IN, EXISTS 와 NOT EXISTS, HAVING절, FROM절, select 절)
chonny
2024. 5. 28. 17:24
★ 점심 시간 문제
select d.loc, sum(e.sal)
from emp e, dept d
where e.deptno = d.deptno and d.loc <>'NEW YORK'
group by d.loc
having sum(e.sal) >=3000
order by 2 desc;
[TIL 12]240528
[집합연산자 4가지]
1. 합집합 연산자: union all 과 union
2. 교집합 연산자: intersect
3. 차집합 연산자: minus
** 여러 테이블 합치기
create table test01
as
select * from 테이블명1
union all
select * from 테이블명2
union all
select * from 테이블명3;
71. 단일행 서브쿼리
예제 71. 서브쿼리 사용하기 1.(단일행 서브쿼리) Q1. JONES 보다 더 많은 월급을 받는 사원들의 이름과 월급을 출력하시오 이 문제를 풀려면 먼저 JONES 의 월급이 얼마인지 알아야합니다. 그래야 JONES 보다 더 많은 월급을 받는 사원들의 이름과 월급을 출력할 수 있습니다.
select sal
from emp
where ename ='JONES';
select sal
from emp
where SAL > 2975;
두단계를 거쳤던 SQL문을 서브쿼리를 사용하여한문장으로 나타낼 수 있음.
select ename, sal
from emp
where sal > ( select sal
from emp
where ename = 'JONES');
문제1. 우리반에서 손주영 학생보다 더 나이가 많은 학생들의 이름과 나이를 출력하시오.
select ename, age
from emp19
where age > (select age
from emp19
where ename = '손주용');
문제 2. scott과 같은 월급을 받는 사원들의 이름과 워급을 출력하시오.
select ename, sal
from emp
where sal = (select sal
from emp
where ename = 'SCOTT');
문제 3. 위 결과에서 SCOTT은 제외하고 출력하시오.
select ename, sal
from emp
where sal = (select sal
from emp
where ename = 'SCOTT')
and ename! ='SCOTT';
문제 4. 이름이 손주용 학생과 나이가 같은 학생들의 이름과 나이를 출력하는데 손주용학생은 제외하고 출력하시오.
select ename, age
from emp19
where age = (select age from emp19
where ename = '손주용')
and ename <> '손주용';
문제5. 우리반에서 최소 나이를 출력하시오,
select ename, min(age)
from emp19
group by ename
order by 2 asc;
문제 6. 우리반에서 최ㅅ나이인 학생의 이름과 나이를 출력하는데 서브쿼리를 사용하시오.
select ename , age
from emp19
where age= (select min(age) from emp19);
메인 쿼리에 그룹함수 사용하면 에러뜸
문제 7. 위의 결과를 order by fetch 문법으로 출력하시오.
select ename ,age
from emp19
where age is not null
order by age asc fetch first 1 rows only;
문제 8. 서울시에서 가장 비싼 농축산물의 이름과 가격과 파는곳을 출력하시오
1)
select a_name, a_price, m_name
from price_2022
order by a_price desc fetch first 1 rows only;
2)
select a_name, a_price, m_name
from price_2022
where a_price = (select max(a_price) from price_2022);
문제 9. allen 보다 늦게 입사한 사원들의 이름과 입사일을 출력하시오.
select ename, hiredate
from emp
where hiredate > (select hiredate from emp where ename = 'ALLEN');
72. 다중행 서브쿼리
예제 72. 서브쿼리 사용하기 2.(다중행 서브쿼리) - 단일행 서브쿼리: 서브쿼리에서 메인쿼리로 한개의 행의 값이 리턴되는경우 (= , !=, >,<,>=,<=) - 다중행 서브쿼리: 서브쿼리에서 메인쿼리로 여러개 행의 값이 리턴되는경우 (in, not in, >any, <any, >all, <all)
문제 1. (복습) 월급이 1250, 3000인 사원들의 이름과 월급을 출력하시오.
select ename, sal
from emp
where sal in (1250, 3000);
한개의 값을 비교할 때는 이퀄(=) 을 사용하는데 여러개의 값을 비교할 때는 in 을 사용해야합니다.
문제 2. 직업이 salesman인 사원들과 월급이 같은 사원들의 이름을 출력하시오.
select ename, sal
from emp
where sal in (select sal from emp where job = 'SALESMAN');
설명: 위와 같이 서브쿼리에서 메인 쿼리로 리턴되는 값이 여러개인 서브쿼리를 다중행 서브쿼리라고 함.
문제 3. 우리반에서 통신사가 lg인 학생들과 나이가 같은 학생들의 이름과 나이와 통신사를 출력하시오.
select ename, age, lower(telecom)
from emp19
where age in (select age from emp19 where lower(telecom) ='lg');
문제 4. 직업이 salesman인 사원들과 월급이 같지 않은 사원들의 이름과 월급을 출려하시오.
select ename, job
from emp
where sal not in(select sal from emp where job='SALESMAN');
문제 5. 우리반에서 서울에서 거주하는 학생들과 나이가 같지 않은 학생들의 이름과 나이와 주소를 출력하시오.
select ename, age, address
from emp19
where age not in (select age from emp19 where address like '서울%');
73. NOT IN
예제 73. 서브쿼리사용하기 3 (not in) Q1. KING의 사원번호를 출력하시오
select empno
from emp
where ename = 'KING';
Q2. 7839가 MGR 번호인 사원들의 이름을 출력하시오.
select ename
from emp
where mgr = 7839;
문제1. king의 직속 부하인 사원들의 이름과 월급을 출력하시오.
select ename, sal
from emp
where mgr = (select empno
from emp
where ename ='KING');
문제2. king의 직속 부하가 아 사원들의 이름과 월급을 출력하시오.
select ename, sal
from emp
where mgr <> (select empno
from emp
where ename ='KING');
문제 3. 관리자 번호를 출력하는데 중복을 제거해서 출력하시오.
select distinct mgr
from emp;
문제 4. 사원 번호가 관리자 번호인 사원들의 이름을 출력하시오.
select ename
from emp
where empno in (select mgr from emp);
설명: mgr 번호가 여러개이므로 =(이퀄) 을 쓰면 안되고 in 을 써야합니다. 위의 SQL은 아래의 SQL과 똑같습니다.
select ename
from emp
where empno = 7788 or empno = 7902 or empno = 7698 or
empno = 7566 or empno = 7839 or empno = 7782 or
empno = null ;
설명 : in 은 or 로 연결되어서 하나라도 false 여도 값을 출력한다.
문제5. 관리자가 아닌 사원들의 이름을 출력하시오. 자기 밑에 직속부하가 한명도 없는 사원들을 출력하시오. mgr번호가 empno가 아닌 사원들.
select ename
from emp
where empno not in (select mgr from emp);
설명: 위에서 in을 썼을 경우에는 결과가 잘 출력 되었는데 not in을 사용했더니 결과가 출력되지 않음 -> inner query에서 반환되는 값 중 하나가 null 값이기 때문에 전체 query가 행을 반환하지 않습니다. 왜냐하면 null 값을 비교하는 모든 조건은 결과가 null이기 때문입니다. 따라서 subquery의 결과 집합의 일부가 null 값이 될 것으로 예상되는 경우 NOT IN 연산자를 사용하지 마십시오. NOT IN 연산자는 <> ALL과 같습니다.
select ename
from emp
where empno != 7788 and empno != 7902 and empno != 7698 and
empno != 7566 and empno != 7839 and empno != 7782 and
empno != null ;
문제 6. (신한카드 sql) 위의 결과가 유효한 값이 나오게 출력하시오.
1)
select ename
from emp
where empno not in (select mgr from emp
where mgr is not null );
2)
select ename
from emp
where empno not in (select nvl(mgr,0) from emp);
문제 7. (복습) 우리반의 평균나이보다 더 나이가 많은 학생들의 이름과 나이를 출력하시오.
select ename, age
from emp19
where age > (select avg(age) from emp19);
문제 8. (복습) 위의 결과에서 나이가 높은 순으로 출력하시오.
select ename, age
from emp19
where age > (select avg(age) from emp19)
order by 2 desc;
문제 9. (복습) 직업과 직업별 인원수를 출력하는데 직업별 인원수가 3명 이상인 것만 출력하시오.
select job, count(*)
from emp
group by job
having count(*) >= 3;
설명: 그룹함수로 검색조건을 줄 때는 반드시 having 절을 사용해야한다
문제10. 위의 결과에서 직업이 salesman은제외하고 출력하시오.
select job, count(*)
from emp
where job <> 'SALESMAN'
group by job
having count(*) >= 3;
설명: having 절에 job <> 'SALESMAN' 넣으면 속도가 떨어짐
74. EXISTS 와 NOT EXISTS
예제 74. 서브쿼리 사용하기 4. (EXISTS 와 NOT EXISTS) 1) exist문은 무조건 메인쿼리부터 수행이된다. 2) 메인쿼리의 테이블의 데이터를 읽으면서 서브쿼리에 해당하는 데이터가 존재하는지 찾아봅니다. 3) 존재하면 찾기를 멈추고 다음 데이터가 존재하는지 찾아 본다.
Q1. 부서테이블에서 부서번호를 출력하시오.
select deptno
from dept;
Q2. 부서테이블에서 부서번호를 출력하는데 사워테이블에 존재하는 부서번호에 대한것만 출력하시오.
select deptno
from dept d
where exists(select deptno from emp e
where e.deptno =d.deptno );
* SQL 튜너들이 서브쿼리문의 SQL튜닝을 할 때 EXIST 문을 사용한다 1) 튜닝전
select deptno
from dept
where deptno in (select deptno from emp);
2) 튜닝후
select deptno
from dept d
where exists(select deptno from emp e
where e.deptno = d.deptno);
설명: 튜닝 전 SQL이 메인쿼리부터 수행된다면 부서번호 10번을 서브쿼리에서 찾기위해서 EMP테이블을 전부 스캔하게 된다. EXISTS 를 사용하여 튜닝하면 DEPT테이블의 10번 부서번호를 EMP 테이블에서 찾을때 하나라도 발견되었을 경우 스캔을 멈춘다.
문제1. telecom_table 에서 통신사를 출력하시오
select telecom
from telecom_table;
문제2 telecom_table에서 통신사를 출력하는데 emp19에 존재하는 통신사만출력되게하시오
select telecom
from telecom_table t
where exists(select telecom from emp19 e
where lower(e.telecom)=t.telecom );
select deptno
from dept d
where not exists(select deptno from emp e
where d.deptno =e.deptno);
문제 4. telecom_table에는 존재하는데 emp19에는 존재하지않는 통신사를 출력하시오.
select telecom
from telecom_table t
where not exists (select telecom
from emp19 e
where t.telecom =lower(e.telecom) );
문제 5. price_2022에서 m_name(상호명) 을 출력하는데 m_type_name 이 대형마트인것을 출력하시오.
select m_name
from price_2022
where m_type_name = '대형마트';
문제 6. price_2013과 price_2022 를 이용해서 not exists문을 사용하는데 2013년에는 존재했던 대형마트인데 2020년에는 존재하지 않는 대형마트의 상호명을 출력하시오.
1) minus사용
select distinct m_name
from price_2013
where m_type_name = '대형마트'
minus
select distinct m_name
from price_2022
where m_type_name = '대형마트';
2) not exists 사용
select distinct m_name
from price_2013 a
where not exists (select m_name
from price_2022 b
where b.m_name = a.m_name
and b.m_type_name = '대형마트')
and a.m_type_name = '대형마트';
둘다 좋은 SQL
문제 7. (복습) 직업이 SALESMAN인 사원들의 이름과 월급, 직업을 출력하는데 월급을 출력할 때 천단위 를 붙여서 출력하시오.
select ename, to_char(sal,'999,999')as sal, job
from emp
where job ='SALESMAN';
문제 8. (복습) 위의 결과에서 월급이 높은 것 부터 출력하시오.
select ename, to_char(sal,'999,999')as sal, job
from emp
where job ='SALESMAN'
order by 2 desc;
select gender, max(age), min(age), round(avg(age)) , count(*)
from emp19
where gender is not null
group by gender;
76. FROM절의 서브쿼리
예제 75. 서브쿼리 사용하기 6. (FROM 절의 서브쿼리) Q1. 이름, 월급, 월급에 대한 순위를 출력하는데 DENSE_RANK 를 이용하시오
select ename, sal, dense_rank() over (order by sal desc) 순위
from emp;
Q2. 위의 결과에서 월급의 순위가 2등인 사원들만 출력하시오.
select ename, sal, 순위
from (
select ename, sal, dense_rank() over ( order by sal desc ) 순위
from emp
)
where 순위 = 2;
문제 1. 우리반에서 나이가 2번째로 나이가 많은 학생의 이름과 나이를출력하시오.
select ename, age
from (
select ename, age, dense_rank () over (order by age desc nulls last) 순위
from emp19
)
where 순위 =2;
문제 2. 사원테이블의 평균월급을 출력하는데 소수점이하는 안나오게 반올림하시오
select round(avg(sal))
from emp;
문제 3. 이름, 월급 , 사원테이블 평균 월급 출력하시오.
select ename, sal ,round(avg(sal) over () )
from emp;
문제 4. 위의 결과에서 자기월급이 사원테이블의 평균보다 더큰사원들만 출력하시오,
select ename, sal ,평균월급
from (select ename, sal ,round(avg(sal) over () ) as 평균월급
from emp )
where sal> 평균월급;
문제 5. 부서번호, 이름, 월급, 평균월급을 출력하는데 평균월급이 부서번호별로 각각 자기 자신의 부서번호의 평균월급이 출력되게하시오
select deptno, ename, sal, round(avg(sal) over (partition by deptno))부서평균
from emp;
문제 6. 위의 결과를 다시 출력하는데 자기 월급이 자기가 속한 부서번호의 평균월급보다 더 큰 사원들만 출력하시오.
select deptno ,ename, sal ,부서평균
from (
select deptno, ename, sal, round(avg(sal) over (partition by deptno))부서평균
from emp
)
where sal> 부서평균;
문제 7. 우리반 테이블에서 통신사, 이름, 나이, 자기가 속한 통신사의 평균나이를 출력하는데 자기의 나이가 자기가 속한 통신사의 평균 나이보다 더 큰 학생들만 출력하시오.
select lower(telecom), ename, age, 평균나이
from (
select telecom, ename, age,
round(avg(age) over (partition by lower(telecom))) 평균나이
from emp19
)
where age > 평균나이;
문제 8. crime_loc에서 범죄유형 , 범죄장소, 건수, 순위를 출력하는데 순위가 범죄유형별로 각각 partition by 해서 순위가 부여되게하시오.
select * from crime_loc;
select c_type, c_loc, c_cnt, dense_rank ()over(partition by c_type order by c_cnt desc ) 순위
from crime_loc;
문제9. 위의 결과에서 순위가 1-3위만 출력되게하시오.
select c_type, c_loc, c_cnt, 순위
from
(select c_type, c_loc, c_cnt, dense_rank ()over(partition by c_type order by c_cnt desc) 순위
from crime_loc )
where 순위 in (1,2,3);
77. SELECT절의 서브쿼리
예제 77. 서브 쿼리 사용하기 7(select 절의 서브쿼리)
(sqlp를 위한 사전문제)
문제 1. 사원 테이블의 토탈월급을 출력하시오
select sum(sal)
from emp;
문제 2. 이름, 월급, 사원 테이블의 토탈월급 출력하시오.
1)
select ename, sal, sum(sal)
from emp
group by ename, sal;
2)
select ename, sal, sum(sal) over()토탈월급
from emp;
3)
select ename, sal, (select sum(sal)from emp) 토탈월급
from emp;
설명: select 절의 서브쿼리를 이용해서 출력을 하고 있음. (scular subquery)
문제 3. 이름, 월급, 사원 테이블의 토탈월급, 사원테이블 최대월급, 사원테이블의 최소월급을 출력하시오.
select ename, sal, (select sum(sal) from emp) 토탈월급,
(select max(sal) from emp) 최대월급,
(select min(sal) from emp) 최소월급
from emp;
문제 4. 이름 ,월급, 직업이 salesman인 사원들의 토탈월급과 최대월급, 최소월급을 출력하시오.
select ename, sal, (select sum(sal) from emp where job='SALESMAN') 토탈월급,
(select max(sal) from emp where job='SALESMAN') 최대월급,
(select min(sal) from emp where job='SALESMAN') 최소월급
from emp;
문제 5. 이름, 월급, 직업이 SALESMAN 인 사원들의 토탈월급을 출력하고직업이 SALESMAN 인 사원들의 최대월급을 출력하고 직업이 SALESMAN 인 사원들의 최소월급을 출력하시오 !
select ename, sal, (select sum(sal) from emp where job='SALESMAN') 토탈월급,
(select max(sal) from emp where job='SALESMAN') 최대월급,
(select min(sal) from emp where job='SALESMAN') 최소월급
from emp
where job ='SALESMAN';
문제 6. 위의 결과에서 직업이 salesman 인사원들만 출력하시오.
select job, ename, sal, (select sum(sal) from emp where job='SALESMAN') 토탈월급,
(select max(sal) from emp where job='SALESMAN') 최대월급,
(select min(sal) from emp where job='SALESMAN') 최소월급
from emp
where job ='SALESMAN';
= 악성 sql = 스칼라 서브쿼리의 특징! 값을 1개의 컬럼값만 출력할 수 있음.
문제 7. 직업이 SALESMAN인 사원들의 토탈월급, 최대월급, 최소월급을 출력하시오
select sum(sal), max(sal), min(sal)
from emp
where job='SALESMAN';
★ 문제 8. 문제 6번을 연결연산자로 나타내시오.
select ename, sal, job,
( select sum(sal)||','|| max(sal)||','|| min(sal) from emp where job='SALESMAN') as 통합
from emp
where job='SALESMAN';
★ 문제9. 위의 결과를 다음과 같이 출력되게하시오.
select ename, sal, job, substr(A, 1,4) 토탈, substr(A,5,4) 최소, substr(A, 9,4) 최대
from (
select ename, sal, job, (
select sum(sal) || max(sal) || min(sal)
from emp where job='SALESMAN'
) as A
from emp
)
where job='SALESMAN';
