[SQL] 학사관리프로그램 만들기

[학사관리 프로그램만들기]

 1) 시나리오 작성

  ⊙ 학생, 교과목, 수강신청 등의 컬럼들을 필요로 함

과목테이블의 과목코드는 p : 프로그램, d : 디자인 

 

다대다 관계	학생테이블  --  수강  --  과목테이블   → 1명의 수강생이 여러 개를 수강신청 할 수 있음        1                               n        n                               1        n                               m     회원테이블   --  예매  --   영화테이블         1                                n         n                                1         회원테이블   --  구매  --   상품테이블 (한 종류의 물건을 판매하는 사이트정도면 할만 함)    회원테이블   --  구매  --   도서테이블
1대1 관계	직원과 부서, 학생과 학생, 시립유치원과 원생

 

 2) 학생테이블 만들기

create table tb_student(     hakno    char(5)          primary key     ,uname   varchar(20)    not null     ,email     varchar(50)    unique     ,address  varchar(20)    not null     ,phone    varchar(20)     ,regdt     date             default sysdate       -- 등록일   );

 

  3) 학생 테이블 행추가

  insert into tb_student(hakno, uname, email, address, phone)
  values('g1001', '홍길동', '1hong1@naver.com', '서울', '111-2222'); 
  insert into tb_student(hakno, uname, email, address, phone )
  values('g1002', '홍길동', '2gil2@daum.com', '제주', '331-2223'); 
  insert into tb_student(hakno, uname, email, address, phone)
  values('g1003', '개나리', '3gae3@naver.com', '서울', '111-2224'); 
  insert into tb_student(hakno, uname, email, address, phone)
  values('g1004', '홍길동', '4dong4@google.com', '부산', '222-2225'); 
  insert into tb_student(hakno, uname, email, address, phone)
  values('g1005', '진달래', '5jin5@naver.com', '서울', '445-2226'); 
  insert into tb_student(hakno, uname, email, address, phone)
  values('g1006', '개나리', '6dog6@google.com', '제주', '311-2227'); 

 

 

 

 

 

문1) 지역별 인원수를 인원수순으로 조회하시오

select address, count(*)  from tb_student  group by address  order by count(*);

 

 

문2) 동명이인이 몇 명인지 조회하시오

select uname, count(*)  from tb_student  group by uname;

 

 

 

문3) 학생테이블의 학번, 이름, 주소를 조회하시오 (주소는 영문으로 출력)

select hakno, uname, CASE when address='서울' then 'SEOUL'                                      when address='제주' then 'JEJU'                                      when address='부산' then 'BUSAN'                                      END as JUSO  from tb_student;

 

 

문4) 주소별 인원수가 3명 미만 인원수순으로 내림차순으로 조회하시오

select address, count(*)  from tb_student  group by address  having count(*)<3  order by count(*) desc ;

 

 

 4) 과목테이블 만들기

create table tb_gwamok(     gcode    char(5)     primary key         --과목코드     ,gname   varchar(20) not null            --과목명     ,ghakjum number(2)   default 1           --학점         ,regdt   date        default sysdate     --등록일  );

 

 5) 과목테이블 행추가

insert into tb_gwamok(gcode, gname, ghakjum)
 values ('p001', 'OOP', 3);
 insert into tb_gwamok(gcode, gname, ghakjum)
 values ('p002', 'Oracle', 2);
 insert into tb_gwamok(gcode, gname, ghakjum)
 values ('p003', 'JSP', 3);
 insert into tb_gwamok(gcode, gname, ghakjum)
 values ('d001', '웹표준', 1);
 insert into tb_gwamok(gcode, gname, ghakjum)
 values ('d002', '포토샵', 3); 
 insert into tb_gwamok(gcode, gname, ghakjum)
 values ('d003', 'HTML', 1);
 insert into tb_gwamok(gcode, gname, ghakjum)
 values ('c011', '실내건축', 3);
 insert into tb_gwamok(gcode, gname, ghakjum)
 values ('p004', 'JavaScript', 2); 

 

 

 

 

 

 

문5) 프로그램 교과목만 조회하시오

select gcode, gname  from tb_gwamok  where gcode like 'p%';

 

 

문6) 디자인 교과목 중 3학점만 조회하시오

select gname, ghakjum  from tb_gwamok  where gcode like 'd%' and ghakjum=3;

 

 

 

 

문7) 프로그램 교과목의 학점 평균보다 낮은 프로그램 교과목을 조회하시오

① 프로그램 교과목의 학점 평균구하기    select avg(ghakgum)    from tb_gwamok    where gcode like 'p%';              --프로그램 교과목의 학점평균 2.5

② 프로그램 교과목의 학점 평균보다 낮은 프로그램 교과목을 조회    select gcode, gname, ghakjum    from tb_gwamok    where ghakjum< (                             select avg(ghakgum)                             from tb_gwamok                             where gcode like 'p%')       -- 미리 만들어 둔 프로그램 교과목의 학점평균을 넣어줌    and gcode like 'p%';                                   -- 프로그램 교과목을 조회하라고 함

 

 6) 수강테이블 생성

create table tb_sugang(     sno number                                                     -- 일련번호    ,hakno  char(5)                                                  --학번 (fk 이기 때문에 부모칼럼과 동일하게 들어가야 함)    ,gcode  char(5)                                                  --과목코드 (fk 이기 때문에 부모칼럼과 동일하게 들어가야 함)    ,primary key(sno)                                               --제약조건을 뒤에 따로 , 를 달아서 붙여줘도 됨    ,foreign key(hakno) references tb_student(hakno)       ,foreign key(gcode) references tb_gwamok(gcode)    );

 

<foreign key에 달 수 있는 옵션>
 ⊙ NO ACTION(자료형을 남기겠다), SET DEFAULT, SET NULL, ON DELETE CASCADE, CASCADE
 ⊙ ON UPDATE CASCADE       : 부모테이블이 수정되면 자식테이블도 같이 수정 되도록
 ⊙ ON DELETE NO ACTION     : 부모테이블이 삭제되더라도 자식테이블은 남아 있도록
 ⊙ ON DELETE CASCADE        : 부모컬럼이 지워지면 같이 지워짐

 

 7) 수강테이블의 일련번호 시퀀스생성

create sequence sugang_seq;

 

 8) 수강테이블 행추가

 insert into tb_sugang(sno, hakno, gcode) values (sugang_seq.nextval, 'g1002', 'p001');
 insert into tb_sugang(sno, hakno, gcode) values (sugang_seq.nextval, 'g1004', 'p001');
 insert into tb_sugang(sno, hakno, gcode) values (sugang_seq.nextval, 'g1004', 'p002');
 insert into tb_sugang(sno, hakno, gcode) values (sugang_seq.nextval, 'g1002', 'p004');
 insert into tb_sugang(sno, hakno, gcode) values (sugang_seq.nextval, 'g1006', 'd001');
 insert into tb_sugang(sno, hakno, gcode) values (sugang_seq.nextval, 'g1006', 'd003');
 insert into tb_sugang(sno, hakno, gcode) values (sugang_seq.nextval, 'g1002', 'd003');

 

 

 

 

 

 

 

 9) 수강테이블(tb_sugang)과 학생테이블(tb_student) 조인

① 테이블 별칭 사용 X  select tb_student.hakno, tb_sugang.gcode, tb_student.uname  from tb_sugang join tb_student  on tb_sugang.hakno=tb_student.hakno;

② 테이블 별칭 사용   select SU.hakno, SU.gcode, STU.uname  from tb_sugang SU join tb_student STU  on SU.hakno=STU.hakno;

 

 10) 수강테이블과 과목테이블 조인

select SU.hakno, GW.gcode, GW.gname, GW.ghakjum  from tb_sugang SU join tb_gwamok GW  on SU.gcode=GW.gcode;

 

 

 

 

 

 11) 수강테이블을 기준으로 학생테이블, 과목테이블 조인

select SU.hakno, SU.gcode, STU.uname, GW.gname, GW.ghakejum  from tb_sugang SU join tb_student STU  on SU.hakno=STU.hakno join tb_gwamok GW  on SU.gcode=GW.gcode;

 

 

 

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

① 테이블 별칭 사용  select STU.hakno, STU.uname, STU.address  from tb_student STU join tb_sugang SU  on STU.hakno=SU.hakno  where address='제주';   ② 수강신청을 한 학생들의 학번, 이름, 주소를 조회 →  테이블 전체를 AA로 칭하고 수행  select hakno, uname, address   from (select STU.hakno, STU.uname, STU.address        from tb_student STU join tb_sugang SU        on STU.hakno=SU.hakno        ) AA  where AA.address='제주';

 

문2) 지역별로 수강신청 인원수, 지역을 조회하시오 (제주 5, 부산 2)

select STU.address, count(*)  from tb_studetn STU join tb_sugang SU  on STU.hakno=SU.hakno  group by address  order by count(*) desc;    select address, count(*)  from(           select SU.hakno, STU.uname, STU.address           from tb_sugang SU join tb_student STU           on SU.hakno=STU.hakno         ) group by AA.address, AA.hakno;

 

 

  

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

- gcode만 카운트  select gcode, count(gcode)  from tb_sugang   group by gcode;    - AA 테이블로 만들고 과목테이블을 조인  select gcode, cnt, gname  from (            select gcode, count(gcode) cnt            from tb_sugang             group by gcode   ) AA join tb_gwamok GW   AA.gcode=GW.gcode;

 

 

 

문4) 지역별로 수강신청 인원수, 지역을 조회하시오 ('문2'의 확장판)

select STU.hakno, STU.address  from tb_sugang SU join tb_student STU  on SU.hakno=STU.hakno  order by STU.address;

 

 

 

 

문4-1) 문4의 데이터를 학번이 같은 동일인물끼리 묶어서 부산 1 / 제주 2로 만들기

select AA.address, count(aa.address)  from (             select STU.hakno, STU.address             from tb_sugang SU join tb_student STU             on SU.hakno=STU.hakno             group by STU.address, STU.hakno         ) AA  group by AA.address;