7월26일 - SQL활용 6 : 학사관리프로그램

학사관리프로그램 

 

1. 키워드 : 학생 교과목 수강신청

  

2. 기본 설계 과정

  - 엑셀로 학사관리프로그램 테이블시나리오 작성

  - 학생테이블 --- 수강 --- 과목테이블

          1                              n

          n                              1

          n                              m

    두 테이블을 이어주는 교차테이블인 수강테이블을 만들어주어서 연결시켜줘야한다.

    교차테이블이 없으면 테이블 설계가 복잡해진다. 

 

  - 회원테이블 --- 예매 --- 영화테이블  

  - 회원테이블 --- 구매 --- 상품테이블

  - 회원테이블 --- 구매 --- 도서테이블

 

3. 테이블 시나리오

  - 과목테이블의 과목이름은 pk로 줄 수 없다.  과목도 세분화되어질 수도 있기 때문. 

    또한 한글로 표기되어있으면 문제소지가 있을 수 있음.

 

 

 

4. 모델링 하기 

 

1:1관계

예) 직원과 부서, 학교와 학생, 시립

 

1) 학생테이블 

 - 테이블생성 

 

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

 

  - 행 추가

 

insert into tb_student(hakno, uname, email, address, phone) values('g1001','청포도','11@naver.com','서울','111-2222'); insert into tb_student(hakno, uname, email, address, phone) values('g1002','화이트','22@naver.com','제주','222-3333'); insert into tb_student(hakno, uname, email, address, phone) values('g1003','개나리','33@naver.com','서울','333-4444'); insert into tb_student(hakno, uname, email, address, phone) values('g1004','복숭아','44@naver.com','부산','444-5555'); insert into tb_student(hakno, uname, email, address, phone) values('g1005','진달래','55@naver.com','서울','555-6666'); insert into tb_student(hakno, uname, email, address, phone) values('g1006','아이스','66@naver.com','제주','666-7777'); insert into tb_student(hakno, uname, email, address, phone) values('g1007','복숭아','77@naver.com','부산','777-8888'); insert into tb_student(hakno, uname, email, address, phone) values('g1008','개나리','88@naver.com','서울','888-9999');

 

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

 

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

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

 

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

 

2) 과목테이블 

  - 테이블생성

 

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

 

  - 행 추가

 

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('c001','실내건축',3); insert into tb_gwamok(gcode, gname, ghakjum) values('p004','JavaScript',2);

 

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

 

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

 

문제) 주소별 인원수가 3명미만 행을 조회하시오 

 

select address, count(*) as cnt  from tb_student group by address having count(*)<3;

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

 

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

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

 

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

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

 

select * from tb_gwamok where ghakjum < (select avg(ghakjum)                  from tb_gwamok                   where gcode like 'p%')       and gcode like 'p%';

 

3) 수강테이블 

  - 테이블 생성

 

create table tb_sugang(   sno      number  --일련번호    ,hakno   char(5) --학번   ,gcode   char(5) --과목코드   ,primary key(sno)   ,foreign key(hakno) references tb_student(hakno)   ,foreign key(gcode) references tb_gwamok(gcode) );

 

  - 수강테이블 일련번호 시퀀스 생성

 

create sequence sugang_seq;

 

  - 행 추가 

 

insert into tb_sugang(sno, hakno, gcode) values(sugang_seq.nextval, 'g1002', 'p001'); insert into tb_sugang(sno, hakno, gcode) values(sugang_seq.nextval, 'g1003', 'p002'); insert into tb_sugang(sno, hakno, gcode) values(sugang_seq.nextval, 'g1004', 'd001'); insert into tb_sugang(sno, hakno, gcode) values(sugang_seq.nextval, 'g1005', 'd003'); insert into tb_sugang(sno, hakno, gcode) values(sugang_seq.nextval, 'g1006', 'd003'); insert into tb_sugang(sno, hakno, gcode) values(sugang_seq.nextval, 'g1007', 'p004'); insert into tb_sugang(sno, hakno, gcode) values(sugang_seq.nextval, 'g1008', 'p004'); insert into tb_sugang(sno, hakno, gcode) values(sugang_seq.nextval, 'g1008', 'd001'); insert into tb_sugang(sno, hakno, gcode) values(sugang_seq.nextval, 'g1006', 'c001'); insert into tb_sugang(sno, hakno, gcode) values(sugang_seq.nextval, 'g1004', 'p003');

 

4) 서로의 테이블 연결 (JOIN) 하기

  - 수강테이블 & 학생테이블 

     -> 

select tb_sugang.hakno, tb_sugang.gcode, tb_student.uname from tb_sugang join tb_student on tb_sugang.hakno=tb_student.hakno;

 

 

  - alias (as 별칭) 을 줘서 테이블 이름 간단히 하기

     -> 위의 식과 같다, 테이블 명을 각각 SU, STU적어주어 간단히 했다.

select SU.hakno, SU.gcode, STU.uname  from tb_sugang SU join tb_student STU on SU.hakno=STU.hakno;

 

  - 수강테이블 & 과목테이블 

 

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

  - 수강테이블을 기준으로 학생테이블 & 과목테이블 조인

    -> 수강테이블과 먼저 연결되는 학생을 조인한 후 과목을 조인한다. 

 

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

 

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

 

select hakno, uname, address from (       select SU.hakno, STU.uname, STU.address       from tb_sugang SU join tb_student STU       on SU.hakno=STU.hakno) AA where AA.address='제주';

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

 

select address, count(gname) as cnt from (       select SU.gcode, GW.gname, STU.address       from tb_sugang SU join tb_student STU       on SU.hakno=STU.hakno join tb_gwamok GW       on SU.gcode=GW.gcode) AA group by AA.address;

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

 

select count(gname) as cnt, gcode, gname from (       select SU.gcode, GW.gname       from tb_sugang SU join tb_student STU       on SU.hakno=STU.hakno join tb_gwamok GW       on SU.gcode=GW.gcode) AA group by AA.gcode, AA.gname;

     -> 선생님 풀이

select AA.gcode, AA.cnt, GW.gname from(      select gcode, count(gcode) as cnt      from tb_sugang       group by gcode) AA join tb_gwamok GW on AA.gcode=GW.gcode;