#10-2. 10일차 퀴즈에 대한 문제풀이

1. 학생의 시험성적이 높은 순서대로 상위 10%에 해당하는 학생의 학번, 이름, 학년, 성적출력

[내 답안]

 

select *
  from student;
  
select *
  from exam_01;
  
select *
  from student s, exam_01 e 
 where s.STUDNO = e.STUDNO;
 
select s.studno, s.name, s.grade, e.total,
       cume_dist() over(order by e.total)
  from student s, exam_01 e 
 where s.STUDNO = e.STUDNO;

select 학번, 이름, 학년, 성적
  from(select s.studno as 학번,
              s.name as 이름,
              s.grade as 학년,
              e.total as 성적,       
              cume_dist() over(order by e.total) as c_total
         from student s, exam_01 e 
        where s.STUDNO = e.STUDNO) 
 where c_total <= 0.1
 order by 4 desc; 

 

 

[문제풀이]

 

select *
  from(select s.studno, s.name, s.grade, e.total,
              cume_dist() over(order by e.total desc) as ratio
         from student s, exam_01 e 
        where s.studno = e.studno)
 where ratio <= 0.1 ;

 

 

 

 

 * 왜 동일한 문제해결방법을 택했는데 결과가 다른지 다시 살펴보니 '성적이 높은 순서대로' 상위 10%인데, 내림차순 설정을 빼고 쿼리를 작성하며 오름차순 정렬 기준으로의 10%가 추출되어 버렸다! 

2. movie 데이터를 사용하여 지역시도별 영화이용비율 총합에 대한 순위 출력(높은 순서대로)

[내 답안]

 

select *
  from movie;
  
select 지역시도, sum(이용_비율) as 시도별총계
  from movie
 group by 지역시도;

select 지역시도, sum(이용_비율) as 시도별총계,
       rank() over(order by sum(이용_비율)) as 순위1_rank,
       dense_rank() over(order by sum(이용_비율)) as 순위2_dense_rank,
       row_number()over(order by sum(이용_비율)) as 순위3_row_number
  from movie
 group by 지역시도;

 

 

[문제 풀이]

 

select 지역시도, sum(이용_비율) as total,
       rank() over(order by sum(이용_비율)) as 순위
  from movie
 group by 지역시도;

3. order_detail, product 테이블을 사용하여 각 상품의 총판매량과 각 상품의 판매량이 전체 판매량에서의 차지 비율을 상품명과 함께  출력

[내 답안]

 

select *
  from order_detail;
  
select *
  from product;

select *
  from order_detail o, product p 
 where o.PRODUCT_ID = p.PRODUCT_ID;

select p.PRODUCT_name as 상품명, 
       sum(o.QUANTITY) as 상품별총판매량
  from order_detail o, product p 
 where o.PRODUCT_ID = p.PRODUCT_ID
 group by p.PRODUCT_NAME;


select *
  from(select p.PRODUCT_name, sum(o.QUANTITY) as 상품별총판매량
         from order_detail o, product p 
        where o.PRODUCT_ID = p.PRODUCT_ID
        group by p.PRODUCT_NAME);
        
select o.ORDER_ID, p.PRODUCT_NAME, o.QUANTITY as 상품별판매량,
       sum(o.QUANTITY) over(partition by p.product_name) as 상품별총판매량,
       round(ratio_to_report(o.QUANTITY) over() * 100, 2) as "상품별판매량/전체 비율",
       round(sum(o.QUANTITY) over(partition by p.product_name) / sum(o.QUANTITY) over() * 100, 2) as "상품별총판매량/전체 비율"
  from order_detail o, product p 
 where o.PRODUCT_ID = p.PRODUCT_ID
 order by o.ORDER_ID;    

 

 

[문제 풀이]

 

select p.PRODUCT_NAME, sum(o.QUANTITY) as total,
       round(ratio_to_report(sum(o.QUANTITY)) over() * 100, 2) as "판매비율(%)"
  from order_detail o, product p 
 where o.PRODUCT_ID = p.PRODUCT_ID
 group by p.PRODUCT_NAME;

4. movie 데이터를 사용하여 요일별 이용비율이 가장 높은 성별과 이용비율 총합 동시 출력

[내 답안]

 

select to_char(to_date(년||'/'||월||'/'||일, 'yyyy/mm/dd'), 'day') as 요일,
       성별
       , sum(이용_비율) as 이용비율
  from movie
 group by to_char(to_date(년||'/'||월||'/'||일, 'yyyy/mm/dd'), 'day'),
          성별;

 

[문제 풀이]

 

-- step1) 문자열 결합 및 날짜 파싱
select to_date(년||'/'||월||'/'||일, 'yyyy/mm/dd'),
       to_date(년||lpad(월,2,0)||lpad(일,2,0), 'yyyymmdd')
  from movie;

--step2) 요일출력
select to_char(to_date(년||'/'||월||'/'||일, 'yyyy/mm/dd'), 'day')
  from movie;
  
--step3) 요일별 성별 요약
select to_char(to_date(년||'/'||월||'/'||일, 'yyyy/mm/dd'), 'day') as 요일,
       성별, sum(이용_비율) as total
  from movie m
group by to_char(to_date(년||'/'||월||'/'||일, 'yyyy/mm/dd'), 'day'), 성별;

--step4) 요일별 최대이용비율 출력
select to_char(to_date(년||'/'||월||'/'||일, 'yyyy/mm/dd'), 'day') as 요일,
       성별, sum(이용_비율) as 이용비율,
       max(sum(이용_비율)) 
       over(partition by to_char(to_date(년||'/'||월||'/'||일, 'yyyy/mm/dd'), 'day')) as 최대이용비율
  from movie m
group by to_char(to_date(년||'/'||월||'/'||일, 'yyyy/mm/dd'), 'day'), 성별;

--step5) 최대이용비율 출력
select *
  from (select to_char(to_date(년||'/'||월||'/'||일, 'yyyy/mm/dd'), 'day') as 요일,
               성별, sum(이용_비율) as 이용비율,
               max(sum(이용_비율)) 
               over(partition by to_char(to_date(년||'/'||월||'/'||일, 'yyyy/mm/dd'), 'day')) as 최대이용비율
          from movie m
        group by to_char(to_date(년||'/'||월||'/'||일, 'yyyy/mm/dd'), 'day'), 성별)
 where 이용비율 = 최대이용비율;  

 

 

 

--**with 문을 사용한 풀이

with movie3 as(
               select to_char(to_date(년||'/'||월||'/'||일, 'yyyy/mm/dd'), 'day') as 요일,
                      성별, sum(이용_비율) as 이용비율
                 from movie m
               group by to_char(to_date(년||'/'||월||'/'||일, 'yyyy/mm/dd'), 'day'), 성별)
select *
  from movie3
 where (요일, 이용비율) in (select 요일, max(이용비율)
                          from movie3
                         group by 요일);

5. 다음 테이블 생성 후 subway.csv 파일 데이터를 테이블에 적재 -> 지하철 라인별로 승차가 가장 많은 시간대 출력

[내 답안]

 

select *
  from subway;

  
select 노선번호, 시간, 승차, sum(승차) over(partition by 노선번호)
  from subway;
  

select 노선번호, 시간, 승차, 최대승차
  from (select 노선번호, 시간, 승차, max(승차) over(partition by 노선번호) as 최대승차
          from subway)
 where 승차 = 최대승차 ;
          

[문제 풀이]

 

 

6. 다음 테이블 생성 후 delivery.csv 파일 데이터를 테이블에 적재 -> 시간대별 가장 주문이 많은 음식업종 출력

[내 답안]

 

select *
  from delivery;
  
select to_char(to_date(일자, 'yyyy/mm/dd')) as 날짜, 업종, 시간대, max(통화건수) as 최대통화
  from delivery
 group by to_char(to_date(일자, 'yyyy/mm/dd')), 업종, 시간대
 order by 1;
 

select *
  from(select to_char(to_date(일자, 'yyyy/mm/dd')) as 날짜, 업종, 시간대, sum(통화건수) as 시간별총통화
         from delivery
        group by to_char(to_date(일자, 'yyyy/mm/dd')), 업종, 시간대);

select 업종, 시간대, 시간별총통화, max(시간별총통화) over(partition by 시간대)
  from(select to_char(to_date(일자, 'yyyy/mm/dd')) as 날짜, 업종, 시간대, sum(통화건수) as 시간별총통화
         from delivery
        group by to_char(to_date(일자, 'yyyy/mm/dd')), 업종, 시간대);
 
select 업종, 시간대, 시간별총통화
  from (select 업종, 시간대, 시간별총통화, max(시간별총통화) over(partition by 시간대) as max통화
         from(select to_char(to_date(일자, 'yyyy/mm/dd')) as 날짜, 업종, 시간대, sum(통화건수) as 시간별총통화
                from delivery
                group by to_char(to_date(일자, 'yyyy/mm/dd')), 업종, 시간대))
 where  max통화 = 시간별총통화;
 

 

[문제 풀이]

 

--step1) 시간대별, 업종별 통화건수 총합 요약
select 시간대, 업종, sum(통화건수) as 총통화건수
  from delivery
 group by 시간대, 업종;
 
--step2) 시간대별 총통화건수 최대 출력
select 시간대, 업종, sum(통화건수) as 총통화건수,
       max(sum(통화건수)) over(partition by 시간대) as 최대통화건수
  from delivery
 group by 시간대, 업종;
 
--step3) 시간대별 가장 주문이 많은 업종 출력
select *
  from (select 시간대, 업종, sum(통화건수) as 총통화건수,
               max(sum(통화건수)) over(partition by 시간대) as 최대통화건수
          from delivery
         group by 시간대, 업종)
 where 총통화건수 = 최대통화건수;