[Oracle] Join 연습문제

1. 테이블 생성

 

1. 테이블 생성
----------------------------- 학생테이블
create table tb_student(
   hakno    char(5)     not null            -- 학번
  ,uname    varchar(20) not null            -- 이름
  ,email    varchar(20) unique              -- 이메일
  ,address  varchar(20) not null            -- 주소
  ,phone    varchar(20)                     -- 전화번호
  ,regdt    date        default sysdate     -- 등록일
  ,primary key(hakno)                       -- 기본키
);

drop table tb_student;
delete table tb_student;

----------------------------- 과목테이블
create table tb_gwamok(
   gcode    char(4)      not null           -- 과목코드 (p:프로그램교과목, d:디자인교과목)
  ,gname    varchar(20)  not null           -- 과목이름
  ,ghakjum  number(2)    default 1          -- 학점
  ,regdt    date         default sysdate    -- 등록일    
  ,primary key(gcode)                       -- 기본키
);

drop table tb_gwamok;

----------------------------- 수강테이블
create table tb_sugang(
   sno    number(3)  not null               -- 일련번호
  ,hakno  char(5)    not null               -- 학번
  ,gcode  char(4)    not null               -- 과목코드
  ,primary key(sno)                         -- 기본키
);

drop table tb_sugang;


2. 수강테이블 시퀀스 생성
create sequence sugang_seq;

drop sequence sugang_seq;


3. 행추가

----------------------------- tb_student테이블에 행 추가하기
insert into tb_student(hakno,uname,address,phone,email)
values('g1001','홍길동','서울','111-5558','11@naver.com');

insert into tb_student(hakno,uname,address,phone,email)
values('g1002','홍길동','제주','787-8877','33@daum.net');

insert into tb_student(hakno,uname,address,phone,email)
values('g1003','개나리','서울','554-9632','77@naver.com');

insert into tb_student(hakno,uname,address,phone,email)
values('g1004','홍길동','부산','555-8844','88@daum.net');

insert into tb_student(hakno,uname,address,phone,email)
values('g1005','진달래','서울','544-6996','33@nate.com');

insert into tb_student(hakno,uname,address,phone,email)
values('g1006','개나리','제주','777-1000','66@naver.com');


----------------------------- tb_gwamok테이블에 행 추가하기
insert into tb_gwamok(gcode,gname,ghakjum) values('p001','JAVA',3);
insert into tb_gwamok(gcode,gname,ghakjum) values('p002','Oracle',3);
insert into tb_gwamok(gcode,gname,ghakjum) values('p003','JSP',2);
insert into tb_gwamok(gcode,gname,ghakjum) values('d001','HTML',1);
insert into tb_gwamok(gcode,gname,ghakjum) values('d002','포토샵',5);
insert into tb_gwamok(gcode,gname,ghakjum) values('d003','일러스트',3);
insert into tb_gwamok(gcode,gname,ghakjum) values('d004','CSS',1);
insert into tb_gwamok(gcode,gname,ghakjum) values('p004','Python',3);
insert into tb_gwamok(gcode,gname,ghakjum) values('p005','AJAX',2);


----------------------------- tb_sugang테이블에 행 추가하기
insert into tb_sugang(sno,hakno,gcode) values(sugang_seq.nextval,'g1001','p001');
insert into tb_sugang(sno,hakno,gcode) values(sugang_seq.nextval,'g1002','p002');
insert into tb_sugang(sno,hakno,gcode) values(sugang_seq.nextval,'g1002','p001');
insert into tb_sugang(sno,hakno,gcode) values(sugang_seq.nextval,'g1002','p003');
insert into tb_sugang(sno,hakno,gcode) values(sugang_seq.nextval,'g1001','p003');
insert into tb_sugang(sno,hakno,gcode) values(sugang_seq.nextval,'g1001','p004');
insert into tb_sugang(sno,hakno,gcode) values(sugang_seq.nextval,'g1005','p001');
insert into tb_sugang(sno,hakno,gcode) values(sugang_seq.nextval,'g1005','d001');
insert into tb_sugang(sno,hakno,gcode) values(sugang_seq.nextval,'g1005','d002');
insert into tb_sugang(sno,hakno,gcode) values(sugang_seq.nextval,'g1005','d003');
insert into tb_sugang(sno,hakno,gcode) values(sugang_seq.nextval,'g1001','d001');
insert into tb_sugang(sno,hakno,gcode) values(sugang_seq.nextval,'g1001','p002');
insert into tb_sugang(sno,hakno,gcode) values(sugang_seq.nextval,'g1006','p001');
/////////////////////////////////////////////////////////////////////////////////

commit;

-- tb_student 테이블 전체 레코드 갯수
select count(*) from tb_student;    -- 6

-- tb_gwamok 테이블 전체 레코드 갯수
select count(*) from tb_gwamok;    -- 9

-- tb_sugang 테이블 전체 레코드 갯수
select count(*) from tb_sugang;    -- 13
/////////////////////////////////////////////////////////////////////////////////

 

 

2. 문제

 

문1)수강신청을 한 학생들 중에서 '제주'에 사는 학생들만 학번, 이름, 주소를 조회하시오

 

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

 

문3) 과목별 수강 신청 인원수, 과목코드, 과목명를 조회하시오 d001 HTML 2명 d002 포토샵 1명 p001 OOP 2명

 

문4) 학번별 수강신청과목의 총학점을 학번별순으로 조회하시오 g1001 홍길동 9학점 g1002 홍길동 6학점 g1005 진달래 9학점

 

문5) 학번 g1001이 수강신청한 과목을 과목코드별로 조회하시오 g1001 p001 OOP g1001 p003 JSP g1001 d001 HTML

 

문6)수강신청을 한 학생들의 학번, 이름 조회

 

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

 

 

3. 풀이

 

눌러서 확인하세요 

문1)수강신청을 한 학생들 중에서 '제주'에 사는 학생들만 학번, 이름, 주소를 조회하시오

select SU.hakno, uname, address
from tb_sugang SU join tb_student ST
on SU.hakno = ST.hakno and ST.address = '제주';


-- AA 별칭 만들기
select AA.hakno, AA.uname, AA.address
from (
        select SU.hakno, uname, address
        from tb_sugang SU join tb_student ST
        on SU.hakno = ST.hakno
    ) AA
where AA.address = '제주';


-- AA 별칭 생략하기
select hakno, uname, address
from (
        select SU.hakno, uname, address
        from tb_sugang SU join tb_student ST
        on SU.hakno = ST.hakno
    ) AA
where AA.address = '제주';


-- 칼럼명이 노출되었을 때는 *을 쓸 수 있다.
select *
from (
        select SU.hakno, uname, address
        from tb_sugang SU join tb_student ST
        on SU.hakno = ST.hakno
    ) AA
where AA.address = '제주';


문2) 지역별로 수강신청 인원수, 지역을 조회하시오
     서울 2명
     제주 2명  

select AA.address, count(*) || '명'
from(
    select SU.hakno, address
    from tb_sugang SU join tb_student ST
    on SU.hakno = ST.hakno
    group by SU.hakno, address
     ) AA
group by AA.address;    

select ST.address, count(distinct(ST.hakno))
from tb_sugang SU join tb_student ST
on SU.hakno=ST.hakno
group by address;


-- 1) 수강테이블 조회
select * from tb_sugang;


-- 2) 수강 신청한 학생들의 명단(학번)
select hakno from tb_sugang order by hakno;
select distinct(hakno) from tb_sugang order by hakno;
select hakno from tb_sugang group by hakno order by hakno;


-- 3) 

select AA.hakno, ST.address
from (
        select hakno from tb_sugang group by hakno order by hakno
    ) AA join tb_student ST
on AA.hakno = ST.hakno;


-- 3)의 결과를 BB 테이블로 만든 후 주소별 그룹 후 행 갯수 구하기

select BB.address, count(*) || '명'
from (
        select AA.hakno, ST.address
        from (
                select hakno from tb_sugang group by hakno order by hakno
            ) AA join tb_student ST
        on AA.hakno = ST.hakno
    ) BB
group by BB.address;


문3) 과목별 수강 신청 인원수, 과목코드, 과목명를 조회하시오 
     d001 HTML   2명 
     d002 포토샵   1명
     p001 OOP    2명
     
select SU.gcode, gname, count(*) || '명'
from tb_sugang SU join tb_gwamok GW
on SU.gcode = GW.gcode
group by SU.gcode, gname
order by SU.gcode;
     
     
문4) 학번별 수강신청과목의 총학점을 학번별순으로 조회하시오
     g1001  홍길동  9학점
     g1002  홍길동  6학점
     g1005  진달래  9학점

select SU.hakno, uname, sum(ghakjum) || '학점'
from tb_sugang SU join tb_student ST
on SU.hakno = ST.hakno join tb_gwamok GW
on SU.gcode = GW.gcode
group by SU.hakno, uname
order by SU.hakno;


문5) 학번 g1001이 수강신청한 과목을 과목코드별로 조회하시오
     g1001  p001  OOP
     g1001  p003  JSP  
     g1001  d001  HTML

-- 논리적 테이블 AA 만들어서 SQL문 사용하기
select AA.*
from(     
    select SU.hakno, SU.gcode, gname
    from tb_sugang SU join tb_student ST
    on SU.hakno = ST.hakno join tb_gwamok GW
    on SU.gcode = GW.gcode
    ) AA
where hakno = 'g1001'
order by gcode; 
        
-- where 조건절 사용하기
select SU.hakno, SU.gcode, gname
from tb_sugang SU join tb_student ST
on SU.hakno = ST.hakno join tb_gwamok GW
on SU.gcode = GW.gcode
where SU.hakno = 'g1001'
order by SU.gcode; 

-- and로 on 조건절에 추가하기
select SU.hakno, SU.gcode, gname
from tb_sugang SU join tb_student ST
on SU.hakno = ST.hakno join tb_gwamok GW
on SU.gcode = GW.gcode and SU.hakno = 'g1001'
order by SU.gcode;


문6)수강신청을 한 학생들의 학번, 이름 조회

-- 논리적 테이블 AA 만들어서 SQL문 사용하기
select *
from (
    select SU.hakno, uname
    from tb_sugang SU join tb_student ST
    on SU.hakno = ST.hakno join tb_gwamok GW
    on SU.gcode = GW.gcode
    ) AA
group by hakno, uname
order by hakno;

-- group by 직접 사용하기
select SU.hakno, uname -- count(uname) 됌
from tb_sugang SU join tb_student ST
on SU.hakno = ST.hakno join tb_gwamok GW
on SU.gcode = GW.gcode
group by SU.hakno, uname
order by SU.hakno;

-- distinct
select distinct(SU.hakno), uname -- count(uname) 안됌
from tb_sugang SU join tb_student ST
on SU.hakno = ST.hakno join tb_gwamok GW
on SU.gcode = GW.gcode
order by SU.hakno;


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

select ST.hakno, ST.uname
from (
    select SU.hakno, uname
    from tb_sugang SU join tb_student ST
    on SU.hakno = ST.hakno join tb_gwamok GW
    on SU.gcode = GW.gcode
    ) AA -- 수강신청한 학생
    right join tb_student ST -- 전체 학생
on AA.hakno = ST.hakno
where AA.hakno is null -- 수강신청한 학생의 코드와 전체 학생 코드 중 겹치는 게 있으면 null로 만들어라
                       -- 전체 학생 중 수강신청한 학생을 제외한 값만 남는다(수강신청 안한 학생)
order by ST.hakno;