o 테이블 모델링
-작성하고자 하는 칼럼과 데이터 확인
-데이터의 속성, 자료형 확인
////////////////////////////////////////////////////////////////////////
[ncs 학습모듈] - 데이터 입출력 구현
[관계형 db 모델링 연습]
- 업무분석
- ER-WIN : 관계형 db 모델링을 도식화할 수 있는 프로그램
[학사관리 업무분석]
- 학생이 수강신청 하고, 수강신청 과목 목록, 수강신청 과목의 총학점
- 해당 과목을 수강신청한 학생 목록
- 학생, 과목, 수강신청
예) 다 : 다 (N:M) 관계 -> 반드시 교차테이블이 있어야 함
학생----------------------과목
1 N
N 1
학생--------수강--------과목
회원--------예매--------영화
회원--------구매--------상품
1-----------n-----------1
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
1. 테이블 생성
--unique는 한 번은 null값 허용
---------------------- 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) --기본키
);
---------------------- 2) 과목테이블
create table tb_gwamok(
gcode char(4) not null --과목코드 (p:프로그램교과목, d:디자인교과목)
,gname varchar(20) not null --과목이름
,ghakjum number(2) default 1 --학점 -99~99
,regdt date default sysdate --등록일
,primary key(gcode) --기본키
);
----------------------- 3) 수강테이블
create table tb_sugang(
sno number not null --일련번호
,hakno char(5) not null --학번
,gcode char(4) not null --과목코드
,primary key(sno) --기본키
);
2. 수강테이블 시퀀스 생성
create 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');
insert into tb_sugang(sno,hakno,gcode) values(sugang_seq.nextval,'g1001','p005');
select * from tb_student;
select * from tb_gwamok;
select * from tb_sugang;
select count(*) from tb_student; --6
select count(*) from tb_gwamok; --9
select count(*) from tb_sugang; --14
commit;
////////////////////////////////////////////////////////////////////////////////
문1)학생테이블에서 지역별 인원수를 인원수순으로 조회하시오
select address, count(address)
from tb_student
group by address
order by count(address)
문2)학생테이블에서 동명이인이 각 몇명인지 조회하시오
select uname, count(uname)
from tb_student
group by uname
문3)학생테이블의 학번, 이름, 주소를 조회하시오 (주소는 영문으로 출력)
select hakno, uname, address, case when address = '서울' then 'Seoul'
when address = '부산' then 'Busan'
when address = '제주' then 'Jeju'
end as juso
from tb_student
문4)학생테이블에서 주소별 인원수가 3명미만 행을 조회하시오
select address, count(address) from tb_student
group by address
having count(address) < 3
문5)과목테이블에서 프로그램 교과목만 조회하시오
select * from tb_gwamok
where gcode like 'p%'
문6)과목테이블에서 디자인 교과목중에서 3학점만 조회하시오
select * from tb_gwamok
where gcode like 'd%' and ghakjum = 3
문7)과목테이블에서 프로그램 교과목의 학점 평균보다 낮은 프로그램 교과목을 조회하시오
--프로그램 교과목의 학점 평균 구하기
select avg(ghakjum) from tb_gwamok where gcode like 'p%'
--프로그램 교과목의 학점 평균(2.6)보다 낮은 교과목을 조회
from tb_gwamok
where ghakjum <(select avg(ghakjum) from tb_gwamok where gcode like 'p%'
)
--오류, avg안에 쿼리문을 넣으면 안 됨
select gname
from tb_gwamok
where ghakjum < avg(select ghakjum from tb_gwamok
where gcode like 'p%') and like 'p%'
--내가한것, 성공
select gname, ghakjum
from tb_gwamok
where ghakjum < (
select avg(ghakjum)
from tb_gwamok
where gcode like 'p%'
)--2.6
and gcode like 'p%'
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
[테이블 조인]
- 여러 테이블을 하나의 테이블처럼 사용하는 조인
- 두개이상의 테이블을 결합하여 데이터를 추출하는 기법
- 두테이블의 공통값을 이용하여 컬럼을 조합하는 수단
형식)
select 칼럼명
from 테이블1 join 테이블2
on 조건절; --ANSI(표준) SQL문
select 칼럼명
from 테이블1, 테이블2
on 조건절; --Oracle DB SQL문
select T1.*, T2.* --T1.모든칼럼, T2.모든칼럼
from T1 inner join T2
on T1.x = T2.x; --테이블명.칼럼명 (T1과 T2의 교집합 찾기)
select T1.*, T2.*, T3.*
from T1 join T2
on T1.x = T2.x join T3
on T1.y = T3.y; --3개 테이블 조인
select T1.*, T2.*, T3.*, T4.*
from T1 join T2
on T1.x = T2.x join T3
on T1.y = T3.y join T4
on T1.z = T4.z;
조건절 : where 조건절, having 조건절, on 조건절
-물리적 테이블(실제로 creat한 테이블)과 논리적 테이블은 서로 동등한 관계
-논리적 테이블 : SQL문에 의해 가공된 테이블
/////////////////////////////
● inner join 연습
select * from tb_sugang
--학번 기준으로 수강테이블과 학생테이블 조인
select tb_sugang.*, tb_student.*
from tb_sugang inner join tb_student
on tb_sugang.hakno = tb_student.hakno;
--두테이블간의 교집합 조인, inner생략가능, 가장 기본
select tb_sugang.*, tb_student.*
from tb_sugang join tb_student
on tb_sugang.hakno = tb_student.hakno;
--수강신청한 학생들의 학번, 과목코드, 이름, 이메일 조회
select tb_sugang.*, tb_student.uname, tb_student.email
from tb_sugang inner join tb_student
on tb_sugang.hakno = tb_student.hakno
select * from tb_sugang
--과목코드를 기준으로 수강테이블과 과목테이블 조인
select tb_sugang.*, tb_gwamok.gname, tb_gwamok.ghakjum
from tb_sugang inner join tb_gwamok
on tb_sugang.gcode = tb_gwamok.gcode
--3개 테이블 조인 : (기준) 수강테이블 + 학생테이블(이름) + 과목테이블(과목명)
select tb_sugang.*, tb_student.uname, tb_gwamok.gname
from tb_sugang join tb_student
on tb_sugang.hakno = tb_student.hakno join tb_gwamok
on tb_sugang.gcode = tb_gwamok.gcode
--테이블의 alias(별칭)도 가능, 별칭은 대소문자 구분 없음, as는 칼럼명에서만 사용(여기서는 사용X)
select SU.*, ST.uname, ST.email
from tb_sugang SU join tb_student ST
on SU.hakno = ST.hakno
select su.*, gw.gname, gw.ghakjum
from tb_sugang su join tb_gwamok gw
on su.gcode = gw.gcode
select *
from tb_sugang su join tb_student st
on su.hakno = st.hakno join tb_gwamok gw
on su.gcode = gw.gcode
--조회시 테이블간에 중복되지 않은 칼럼명은 테이블명을 생략할 수 있다
select su.*, uname, email, gname, ghakjum
from tb_sugang su join tb_student st
on su.hakno = st.hakno join tb_gwamok gw
on su.gcode = gw.gcode
--중복되는 칼럼명은 소속 테이블명을 정확히 명시해야 함
--ORA-00918: 열의 정의가 애매합니다
select hakno, gcode, uname, email, gname, ghakjum
from tb_sugang su join tb_student st
on su.hakno = st.hakno join tb_gwamok gw
on su.gcode = gw.gcode
--hakno와 gcode가 속한 테이블명 명시하여 에러 해결
select su.hakno, su.gcode, uname, email, gname, ghakjum
from tb_sugang su join tb_student st
on su.hakno = st.hakno join tb_gwamok gw
on su.gcode = gw.gcode
///////////////////////////////////////////////////////////////////
[테이블 조인 연습 문제]
문1) 수강신청을 한 학생들 중에서 '제주'에 사는 학생들만 학번, 이름, 주소를 조회하시오
--수강신청한 학생들 목록 보기
select * from tb_sugang
--'제주'만 조회하기
select st.hakno, uname, address
from tb_student st join tb_sugang su
on su.hakno = st.hakno
where address = '제주'
select st.hakno, uname, address
from tb_sugang su join tb_student st
on su.hakno = st.hakno
--가공된 논리적 테이블의 이름을 AA라고 지정하고, 다시 재가공 할 수 있다.
select AA.hakno, AA.gcode, AA.uname, AA.address
from (
select st.hakno, su.gcode, uname, address
from tb_sugang su join tb_student st
on su.hakno = st.hakno
) AA
where AA.address='제주'
--가공된 논리적 테이블 AA에 없는 칼럼 email을 호출하면 에러
--ORA-00904: "AA"."EMAIL": 부적합한 식별자
select AA.hakno, AA.gcode, AA.uname, AA.address, AA.email
from (
select st.hakno, su.gcode, uname, address
from tb_sugang su join tb_student st
on su.hakno = st.hakno
) AA
where AA.address='제주'
--AA별칭 생략가능
select hakno, gcode, uname, address
from (
select st.hakno, su.gcode, uname, address
from tb_sugang su join tb_student st
on su.hakno = st.hakno
) AA
where AA.address='제주'
--AA테이블의 모든 칼럼
select * --하단에 칼럼명이 모두 노출되어 있으므로 *사용해도 무관(그러나 가급적 쓰지 않는 것이 좋음)
from (
select st.hakno, su.gcode, uname, address
from tb_sugang su join tb_student st
on su.hakno = st.hakno
) AA
where AA.address='제주'
'수업노트' 카테고리의 다른 글
| 23.3.7 (0) | 2023.03.07 |
|---|---|
| 23.3.6 (0) | 2023.03.06 |
| 23.2.28(화) (0) | 2023.02.28 |
| 23.2.27 (월) (0) | 2023.02.27 |
| 23.2.23 (목) (0) | 2023.02.23 |
