패스트캠퍼스 챌린지 40일차

SQL 분기문(case-when), 집합연산 (union, union all), 서브쿼리 (column,from, where) 

 

분기문 CASE-WHEN 예제

tProduction 테이블은 직원들이 제품을 생산한 정보가 들어있다

판매 가능한 제품들의 재고를 파악하기 위해 2020년 2월에 생산된 양 확인

문구류 제품 먼저 파악, 해당 제품명과 제품들의 총 생산량 출력

제품코드 : INumber, I1001 = 가위, I1002 = 풀 I1003 = 공책 I1004 = 볼펜 I1005 = 지우개

 

정답

select

  case

    when tpr.INumber = 'I1001' then '가위'

    when tpr.INumber = 'I1002' then '풀'

    when tpr.INumber = 'I1003' then '공책'

    when tpr.INumber = 'I1004' then '볼펜'

    when tpr.INumber = 'I1004' then '지우개'

from tProduction as 총_생산량

where to_char(tpr.pDate,'YYYYMM') = '202002'

  and substring(tpr.INumber,1,2) = 'l1'

group by tpr.INumber

 

---

UNION 예제

tOrder 테이블은 주문 정보가 들어있는 테이블이다

tReturn 테이블은 주문을 한 고객이 반품을 진행한 정보들이 들어있는 테이블이다

제품이 한번이라도 주문됬거나, 반품이 한 번이라도 발생한 월의 정보를 알바보려고한다

위 조건에 해당하는 월들을 중복을 제거하고 출력하시오

 

정답

select to_char(tor.Odate, 'MM') as 주문_및_반품월

from tOrder as tor

group by to_char(tor.Odate,'MM')

union

select to_char(tre.Rdate,'MM')

from tReturn as tre

group by to_char(tre.Rdate,'MM)

 

---

UNION ALL 예제

tEmployee 테이블은 직원들의 정보가 들어있는 테이블이다

현재까지 회사에 입사했던 사람들의 총 인원 수와 연도별 입사한 직원 수를 출력하시오.

 

정답

select '총 인원수 :' as 입사년도, count(tem1.*) as 입사한_직원_수 // 첫번째 칼럼에 총 인원수 : 로 데이터가 들어감

from tEmployee as tem1

union all

select to_char(tem2.StartDate, 'YYYY'), count(tem2.*)

from tEmployee as tem2

group by to_char(tem2.StartDate,'YYYY')

 

---

서브쿼리 예제 1 (Column)

 tDepartment 직원 부서 정보 테이블, tEmployee 직원 정보 테이블, tRank 회사 내 직급 테이블

 회사에서 제공해주는 기숙사에 머무를 수 있는 인원의 제한을 위해 부서와 직급, 그리고 현재 거주지를 따져 제한하려고 한다

 이에 따라 부서명과 직원명, 직급명 그리고 현재 직원의 주소를 출력하시오 (부서명, 직급명 으로 출력 되야 한다)

 

 정답

 select ( select tde.DName from tDepartment as tde where tde.DNumber = tem.DNumber) as 부서명

              , tem.EName as 직원명

              , (select tra.RName from tRank as tra where tra.RNumber = tem.RNumber) as 직급명

              , tem.EAddr as 직원주소

 from tEmployee as tem

 

---

서브쿼리 예제 2 (Where 절)

tProduction : 제품 정보 테이블, tEmployee : 직원 정보 테이블

회사 내 전 직원들의 평균 생산량보다 한번이라도 많이 생산한 직원들에게는 상여금 부여 이에 해당하는 직원명 출력

(생산량은 tProduction 테이블의 PCount 이다)

 

 정답

 select tem.EName as 직원명

 from tEmployee as tem

 where tem.ENumber in ( select tpr.Enumber

                                            from tProduction as tpr1

                                            where tpr1.PCount > ( select avg(tpr2.PCount)

                                                                                   from tProduction as tpr2 )

                                           )

---

서브쿼리 예제 2 (From 절)

tProduction :  제품 정보 테이블

2021년 부터 판매가 시작됨에 따라 1년동안 생산된 제품들의 제품번호와 총 생산량을 생산량이 많은 순서대로 출력

 

 정답

select tBase.INumber as 제품번호, tBase.PCount as 총_생산량

from ( select  tpr.INumber, sum(tpr.PCount) as PCount

           from tProdcution as tpr

           where tpr.PDate between cast('2020-01-01' as timestamp) and cast('2020-01-01' as timestamp)

           group by tpr.INumber

          ) as tBase  // from 절에 서브쿼리 사용 시 반드시 as로 별칭을 부여해야 한다.

 order by tBase.PCount desc 

 

 

 

40일차 인증

본 포스팅은 패스트캠퍼스 환급 챌린지 참여를 위해 작성되었습니다.

https://bit.ly/37BpXiC

패스트캠퍼스 [직장인 실무교육]