07월 26일 금 | SQL활용 07 - SQL 연습 Ⅰ

 

SQL Relation

학사관리 시스템

학사관리 프로그램을 만들어 sql 예제를 만들어 본다.

우선 프로그램을 짜기에 앞서 엑셀로 시나리오를 정리해보았다.

 

 

1. 학생테이블 만들기

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

 

1-1. 학생테이블에 데이터 삽입

-- 학생테이블 데이터 삽입 insert into tb_student  (hakno, uname, email, address, phone) values ('g1001','홍길동','mail11@email.com','서울','010-1234-5678'); insert into tb_student  (hakno, uname, email, address, phone) values ('g1002','김나라','mail12@email.com','부산','010-1234-5679'); insert into tb_student  (hakno, uname, email, address, phone) values ('g1003','개나리','mail13@email.com','대전','010-1234-5680'); insert into tb_student  (hakno, uname, email, address, phone) values ('g1004','진달래','mail14@email.com','강릉','010-1234-5681'); insert into tb_student  (hakno, uname, email, address, phone) values ('g1005','무궁화','mail15@email.com','전주','010-1234-5682'); insert into tb_student  (hakno, uname, email, address, phone) values ('g1006','박지성','mail16@email.com','서울','010-1234-5683'); insert into tb_student  (hakno, uname, email, address, phone) values ('g1007','손흥민','mail17@email.com','부산','010-1234-5684'); insert into tb_student  (hakno, uname, email, address, phone) values ('g1008','김연아','mail18@email.com','대전','010-1234-5685'); insert into tb_student  (hakno, uname, email, address, phone) values ('g1009','김연경','mail19@email.com','강릉','010-1234-5686'); insert into tb_student  (hakno, uname, email, address, phone) values ('g1010','김아랑','mail20@email.com','전주','010-1234-5687'); insert into tb_student  (hakno, uname, email, address, phone) values ('g1011','심석희','mail21@email.com','서울','010-1234-5688'); insert into tb_student  (hakno, uname, email, address, phone) values ('g1012','진선유','mail22@email.com','부산','010-1234-5689'); insert into tb_student  (hakno, uname, email, address, phone) values ('g1013','박승희','mail23@email.com','대전','010-1234-5690'); insert into tb_student  (hakno, uname, email, address, phone) values ('g1014','박인비','mail24@email.com','강릉','010-1234-5691'); insert into tb_student  (hakno, uname, email, address, phone) values ('g1015','김연경','mail25@email.com','서울','010-2234-5686');

 

 

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

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

 

 

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

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

 

 

 

2. 과목테이블 만들기

-- 과목테이블 생성 create table tb_subject(  gcode    char(5) primary key ,gname    varchar(20) not null ,ghakjum  number(2) default 1 ,regdt    date default sysdate );

 

 

2-1. 과목데이터 삽입하기

insert into tb_subject  (gcode, gname, ghakjum) values ('p001','OOP',3); insert into tb_subject  (gcode, gname, ghakjum) values ('p002','Oracle',2); insert into tb_subject  (gcode, gname, ghakjum) values ('p003','JSP',3); insert into tb_subject  (gcode, gname, ghakjum) values ('p004','웹표준',1); insert into tb_subject  (gcode, gname, ghakjum) values ('p005','Javascript',2); insert into tb_subject  (gcode, gname, ghakjum) values ('d001','HTML',1); insert into tb_subject  (gcode, gname, ghakjum) values ('d002','CSS',2); insert into tb_subject  (gcode, gname, ghakjum) values ('d003','CAD',3);

 

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

select hakno, uname,         case when address='서울' then 'Seoul'             when address='부산' then 'Busan'             when address='대전' then 'Daejeon'              when address='강릉' then 'Gangneung'             when address='전주' then 'Jeonju'             else '없음'             end as 주소지             from tb_student;

 

케이스문을 사용하여 해결해야하는 복습문제이다.

 

 

 

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

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

일반구문에서 조건을 달때는 where을 사용하지만 group을 사용할 때 조건은 having임을 잊지 말기.

 

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

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

 

 

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

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

 

 

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

select * from tb_subject where ghakjum < (select avg(ghakjum) from tb_subject 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_subject(gcode) );

기본키와 외래키를 지정해준다.

 

옵션설정

on update cascade   -- 부모테이블이 수정되면 같이 수정(설정을 상속)
on delete no action -- 부모테이블이 삭제되면 자식은 남아있음

 

 

3.1 수강데이터 삽입

--수강데이터 삽입 --g1001 insert into tb_sugang (sno, hakno, gcode) values(tb_sugang_seq.nextval,'g1001','p001'); insert into tb_sugang (sno, hakno, gcode) values(tb_sugang_seq.nextval,'g1001','p003'); insert into tb_sugang (sno, hakno, gcode) values(tb_sugang_seq.nextval,'g1001','p004'); insert into tb_sugang (sno, hakno, gcode) values(tb_sugang_seq.nextval,'g1001','p005'); --g1002 insert into tb_sugang (sno, hakno, gcode) values(tb_sugang_seq.nextval,'g1002','p001'); insert into tb_sugang (sno, hakno, gcode) values(tb_sugang_seq.nextval,'g1002','p002'); insert into tb_sugang (sno, hakno, gcode) values(tb_sugang_seq.nextval,'g1002','p003'); insert into tb_sugang (sno, hakno, gcode) values(tb_sugang_seq.nextval,'g1002','p005'); --g1003 insert into tb_sugang (sno, hakno, gcode) values(tb_sugang_seq.nextval,'g1003','p001'); insert into tb_sugang (sno, hakno, gcode) values(tb_sugang_seq.nextval,'g1003','p002'); insert into tb_sugang (sno, hakno, gcode) values(tb_sugang_seq.nextval,'g1003','p004'); insert into tb_sugang (sno, hakno, gcode) values(tb_sugang_seq.nextval,'g1003','p005');

 

 

 

 

hakno(학번)을 기준으로 수강테이블과 학생테이블을 조인

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

 

 

 

gcode(과목코드)를 기준으로 수강테이블과 과목테이블을 조인

select SU.gcode, SUB.gname from tb_sugang SU join tb_subject SUB on SU.gcode = SUB.gcode order by SUB.gcode;

 

 

 

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

select SU.hakno, SU.gcode, STU.uname, SUB.gname from tb_sugang SU join tb_student STU on SU.hakno = STU.hakno join tb_subject SUB on SU.gcode = SUB.gcode order by SU.hakno, SU.gcode;

 

 

 

 

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

select SU.hakno, STU.uname, STU.address from tb_sugang SU join tb_student STU on SU.hakno = STU.hakno where STU.address = '강릉';

 

 

 

 

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

select STU.address, count(SU.gcode) from tb_sugang SU join tb_student STU on SU.hakno = STU.hakno join tb_subject SUB on SU.gcode = SUB.gcode  group by STU.address;

이거는 그런데 결과가 학생이 4개과목 신청했을 때, 4명이 신청한 것으로 합계되기 때문에 정확한 결과가 아니다

이렇게 해야한다.

select STU.address, count(distinct(SU.hakno)) from tb_sugang SU join tb_student STU on SU.hakno = STU.hakno join tb_subject SUB on SU.gcode = SUB.gcode  group by STU.address;

이렇게 distinct를 사용해서 중복을 제거해주었다.

 

 

 

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

select SUB.gcode,SUB.gname,count(SU.gcode) from tb_sugang SU join tb_subject SUB on SU.gcode = SUB.gcode group by SUB.gcode, SUB.gname order by SUB.gcode;

-- 선생님 풀이 select AA.gcode, AA.cnt, SUB.gname from (     select gcode, count(gcode) as cnt     from tb_sugang     group by gcode ) AA join tb_subject SUB on AA.gcode = SUB.gcode;