SQL 예제(3)

JOIN 연습문제

tb_student table

tb_gwamok table

tb_sugang table

문제1) 지역별로 수강신청 인원수, 지역을 조회하시오

select address, count(*) || '명' as cnt
from (select a.hakno, address 
      from (select hakno
            from tb_sugang
            group by hakno) a 
      inner join tb_student b
      on a.hakno = b.hakno)
group by address;

 
문제2) 과목별 수강 신청 인원수, 과목코드, 과목명를 조회하시오 

select a.gcode, gname, concat(count(*), '명') as "인원수"
from tb_sugang a inner join tb_gwamok b
on a.gcode = b.gcode
group by gname, a.gcode
order by a.gcode;

 
문제3) 학번별 수강신청과목의 총학점을 학번별순으로 조회하시오

select hakno, uname, sum(ghakjum) as "총학점"
from (select a.hakno, b.uname, c.ghakjum
      from tb_sugang a inner join tb_student b
      on a.hakno = b.hakno
      inner join tb_gwamok c
      on a.gcode = c.gcode)
group by hakno, uname;

 
문제4) 학번 g1001이 수강신청한 과목을 과목코드별로 조회하시오

select a.hakno, b.gcode, b.gname
from tb_sugang a 
inner join tb_gwamok b
on a.gcode = b.gcode
where a.hakno = 'g1001'
order by gcode;

 
문제5) 수강신청을 한 학생들의 학번, 이름을 조회하시오

select hakno, uname
from tb_student
where hakno in (select hakno from tb_sugang)
order by hakno;

-- join 이용하기 ---------------------------
select a.hakno, b.uname
from tb_sugang a inner join tb_student b
on a.hakno = b.hakno
group by a.hakno, b.uname
order by hakno;

 
문제6) 수강신청을 하지 않은 학생들의 학번, 이름을 조회하시오

select hakno, uname
from tb_student
where hakno not in (select hakno from tb_sugang)
order by hakno;

-- join 이용하기 -------------------------------
select a.hakno, a.uname
from tb_student a left outer join tb_sugang b
on a.hakno = b.hakno
where gcode is null
group by a.hakno, a.uname
order by hakno;

 
문제7) 디자인 교과목중에서 학점이 제일 많은 교과목을 수강신청한 명단을 조회하시오 (학번, 이름, 과목코드)

select a.hakno, b.uname, c.gcode --3.학번, 이름, 과목코드 조회
from tb_sugang a join tb_student b --1.3개 테이블 조인
on a.hakno = b.hakno
join tb_gwamok c
on a.gcode = c.gcode
where ghakjum in (select max(ghakjum) 
                  from tb_gwamok
                  where gcode like ('d%')) --2.디자인 과목중 학점이 높은 교과목 찾기
and a.gcode like ('d%')
order by ghakjum desc; --4.학점순으로 정렬하기

 
문제8) 학번별 수강신청한 총학점을 구하고 학번순으로 정렬해서 줄번호 4~6행 조회하시오 (학번, 이름, 총학점) (단, 수강신청하지 않은 학생의 총학점도 0으로 표시) 

select hakno, uname, "총학점" --9.학번, 이름, "총학점"조회
from (select D.*, rownum as rnum --6.D테이블 전체칼럼 조회, rownum 붙이기
      from(select a.hakno, sum(nvl(ghakjum,0)) as "총학점", a.uname --3.학번, 이름, "총학점" 조회          
          from tb_student a left join tb_sugang b --1.테이블 3개 조인
          on a.hakno = b.hakno
          left join tb_gwamok c
          on b.gcode = c.gcode          
          group by a.hakno, a.uname --2.학번, 이름으로 그룹화
          order by hakno) D) --4.학번순으로 정렬하기 --5.from D테이블 --7.from rownum고정테이블
where rnum BETWEEN 4 and 6; --8.rownum의 4~6열 가져오기

 
문제9) 학번별로 수강신청 총학점을 구하고, 총학점순으로 내림차순 정렬후 위에서 부터 1건만 조회하시오 (학번, 이름, 총학점)

select hakno, uname, "총학점" --9.학번, 이름, "총학점"조회
from (select D.*, rownum as rnum --6.D테이블 전체칼럼 조회, rownum 붙이기
      from(select a.hakno, sum(nvl(ghakjum,0)) as "총학점", a.uname --3.학번, 이름, "총학점" 조회
          from tb_student a left join tb_sugang b --1.테이블 3개 조인
          on a.hakno = b.hakno
          left join tb_gwamok c
          on b.gcode = c.gcode
          group by a.hakno, a.uname --2.학번, 이름으로 그룹화
          order by "총학점" desc) D) --4.총학점 내림차순 정렬하기 --5.from D테이블 --7.from rownum고정테이블
where rnum = 1; --8.rownum의 첫번째열 가져오기