ch0nny_log

[빅데이터분석] SQL_TIL 12 (단일행 서브쿼리, 다중행 서브쿼리, NOT IN, EXISTS 와 NOT EXISTS, HAVING절, FROM절, select 절) 본문

빅데이터 분석(with 아이티윌)/SQL

[빅데이터분석] 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 );
문제 3. 부서 테이블에서 부서번호를 출력하는데 부서테이블에는 존재하는데 사원테이블에는 존재하지않는 부서번호를 출력하시오.
  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;

75. HAVING절의 서브쿼리

예제 75. 서브쿼리 사용하기 5. (HAVING 절의 서브쿼리)
Q1. 직업, 직업별 토탈월급을 출력하시오.
   select job, sum(sal)
        from emp
        group by job;

Q2.위의 결과를 다시 출력하는데 직업별 토탈월급이 5000이상인 것 만 출력하시오.

 select job, sum(sal)
        from emp
        group by job
        having sum(sal) >=5000;

 

문제 1. 직업, 직업별 인원 수를  출력하는데 직업별 인원수가 3명 이상인 것만 출력하시오.
        
 select job, count(*)
    from emp
        group by job
        having  count(*) >=3
문제 2. 직업, 직업별 인원수를 출력하는데 직업이 ANALYST의 인원수보다 더 많은 것 만 출력하시오
   select job , count (*)
            from emp 
            group by job
            having count(*) > (select count(*) from emp where job ='ANALYST');
문제 3. 직업, 직업별 토탈월급을 출력하는데 직업별 토탈월급이 직업 salesman 의  토탈월급보다 더 큰것만 출력하시오  
 select job, sum(sal)
                from emp
                group by job
                having sum(sal) > (select sum(sal) from emp where job = 'SALESMAN');
문제 4. 직업, 직업별 최대월급, 직업별 최소월급, 직업별 토탈월급을 출력하시오.
    select job, max(sal), min(sal), sum(sal)
        from emp
        group by job;

 

문제 5. 우리반 테이블에서 성별, 성별별 최대나이, 성별별 최소나이, 성별별 평균나이, 성별별 인원수를 출력하시오.
 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';