-- 테이블간의 관계(엔티티간의 관계)
-- ex1] 직원/프로젝트
-- 1. 담당 직원 테이블 생성
create table tblStaff
(
name nvarchar2(10) primary key, --직원명(PK, 생략x, 중복x)
salery number not null, --월급
address varchar2(100) not null --주소
);
insert into tblStaff (name, salery, address) values ('홍길동', 200, '서울시');
insert into tblStaff (name, salery, address) values ('아무게', 150, '인천시');
insert into tblStaff (name, salery, address) values ('하하하', 100, '부산시');
select * from tblStaff;
-- 2. 프로젝트 테이블 생성
create table tblProject
(
pID number primary key, --프로젝트 ID(일련번호, PK)
pName nvarchar2(100) not null, --프로젝트명
--staff nvarchar2(10) not null --******중요!(재직중인 직원의 이름을 대입), 담당 직원명
-- 참조키(foreign Key(FK), 외래키)
-- : 절대로 아무 데이터나 집어넣으면 안됨!
-- : 반드시 참조하는 컬럼에 이미 존재하는 값 중에 하나를 삽입(*********)
-- 1. 다른 테이블의 특정 컬럼을 참조
-- 2. 다른 테이블의 기본키(PK) 참조
-- 3. FK는 PK를 참조
-- 4. FK의 자료형은 PK의 자료형과 동일하게 선언(데이터가 동일하기 때문에.,)
staff nvarchar2(10) not null references tblStaff(name)
);
insert into tblProject (pID, pName, staff) values (1, '홍콩수출건', '홍길동');
insert into tblProject (pID, pName, staff) values (2, 'TV광고건', '아무게');
insert into tblProject (pID, pName, staff) values (3, '매출분석건', '아무게');
insert into tblProject (pID, pName, staff) values (4, '대리점분양건', '홍길동');
insert into tblProject (pID, pName, staff) values (5, '노조협상건', '하하하');
select * from tblProject;
-- 3. 상황설정
-- 1. 문제 case1] 신입 사원 입사 -> 신규 프로젝트 할당 : 직원과 프로젝트에 추가 삽입
-- a. 신입 사원 추가(tblStaff -> insert)
insert into tblStaff values ('호호호', 170, '서울시'); --0
-- b. 신규 프로젝트 추가(tblProject -> insert)
insert into tblProject values (6, '원자재매입건', '호호호');
-- b. 신규 프로젝트 추가
-- 물리적인 insert 작업은 성공했지만., 업무상(논리적)으로 문제 발생 -> 시스템적으로 발견 불가**
insert into tblProject values (7, '고객상담건', '헤헤헤');
-- 2. 문제 case2] 홍길동 퇴사
delete from tblStaff where name = '홍길동'; --o, 업무상 불가능
-- 정상적인 퇴사 절차
-- a. 프로젝트 인수인계(위임) -> 담당자명 수정(update)
update tblProject set staff = '아무게' where staff = '홍길동';
-- b. 홍길동 퇴사
delete from tblStaff where name = '홍길동'; --o, 업무상 가능
-- 무결성 유지 : 유요한 데이터로만 시스템 유지
-- -> 데이터서버(오라클)가 내부적으로 관리
drop table tblStaff;
drop table tblProject;
-- 참조(제약사항) 후 테이블 새로 생성
--생성(staff->project)참조 당하는 것부터 -> 삭제(project->staff)
-- ex2] 고객 & 판매(매출)
-- ******참조시 기준
-- : 나중에 생기는 테이블(FK)이 먼저 생기는 테이블(PK)을 참조
create table tblCustomer
(
name varchar2(10) primary key, --고객명(PK)
tel varchar2(15) not null, --연락처
address varchar2(100) not null --주소
--orderNo number not null references tblSales(orderNo) 불안정한 참조
);
create table tblSales
(
orderNo number primary key, --주문번호
item varchar2(50) not null, --상품명
num number not null, --판매수량
regDate date default sysdate not null, --판매시간
--참조키
customer varchar2(10) not null references tblCustomer(name) --고객명(PK), 안정적인 참조
);
create sequence salesSeq;
insert into tblCustomer values ('홍길동', '010-222-3333', '서울시');
insert into tblCustomer values ('아무게', '010-333-4444', '부산시');
insert into tblCustomer values ('하하하', '010-555-6666', '인천시');
insert into tblSales values (salesSeq.nextVal, '전화기', 1, default, '홍길동');
insert into tblSales values (salesSeq.nextVal, '다이어리', 1, default, '홍길동');
insert into tblSales values (salesSeq.nextVal, '노트', 3, default, '아무게');
insert into tblSales values (salesSeq.nextVal, '볼펜', 5, default, '아무게');
insert into tblSales values (salesSeq.nextVal, '전지우개', 2, default, '아무게');
insert into tblSales values (salesSeq.nextVal, '마우스', 1, default, '아무게');
insert into tblSales values (salesSeq.nextVal, '키보드', 1, default, '하하하');
--비회원 구매 불가능
insert into tblSales values (salesSeq.nextVal, '키보드', 1, default, '크크크');
-- 회원 탈퇴
--x, 판매내역이 남아있기 때문.,
delete from tblCustomer where name = '홍길동';
select * from tblCustomer;
select * from tblSales; --확인
-- ex3] 비디오 대여점
-- : 회원, 비디오(상품), 장르, 대여(장부)
-- 1. 장르
create table tblGenre
(
name varchar2(10) primary key, --장르명(PK)
price number not null, --대여가격
period number not null --대여기간
);
-- 2. 비디오
create table tblVideo
(
pk number primary key, --비디오 번호(PK)
name varchar2(50) not null, --제목
num number not null, --보유수량
company varchar2(50) null, --제작사
director varchar2(20) null, --감독
major varchar2(20) null, --주연배우
Genre varchar2(10) not null references tblGenre(name) --장르 PK 참조 --> FK
);
-- 3. 회원
create table tblMember
(
pk number primary key, --회원번호(PK)
name varchar2(10) not null, --이름
grade number(1) not null, --회원등급(1=준, 2=정, 3=우수.,)
bYear number(4) not null, --생년(성인인증)
tel varchar2(15) not null,--연락처
address varchar2(100) null, --주소
money number not null --예치금
);
-- 4. 대여
create table tblRent
(
pk number primary key, --대여번호(PK)
who number not null references tblMember(pk), --회원번호, 대여회원 참조
what number not null references tblVideo(pk), --비디오번호 참조--> FK
rentDate date default sysdate not null, --대여시간
retDate date null, --반남시간
remark varchar2(100) null
);
-- 시퀀스
create sequence tblMember_seq;
create sequence tblVideo_seq;
create sequence tblRent_seq;
-- 조인, Join
-- : 2개 이상의 테이블 내용을 한꺼번에 가져오는 기술
-- : 관계를 맺고 있는 테이블끼리.,
-- 1. 내부조인(inner join) : *****
-- : 각 테이블에 조건으로 들어간 컬럼값(무보테이블(PK) -자식테이블(FK)을 비교해서
-- 양쪽 모두 만족하는 행(PK와 FK가 동일한 값을 가지는)을 조합해서 결과셋으로 반환
-- : 단순 조인의 결과에서 의미있는 행만 추출 -> 내부조인
-- : on -> 조인에서의 where절 역할
-- select 컬럼리스트 from 테이블A inner join 테이블B on 테이블A.컬럼 = 테이블B.컬럼;
-- 2. 외부조인(outer join) : *****
-- : 내부조인 + a
-- 3. 셀프조인(self join) : **
-- : 1개의 테이블을 가지고 조인
-- 4. 크로스조인(corss join) 단순조인 : X
-- 조인 vs 서브쿼리
-- 1. 서브쿼리
-- : 결과셋이 1개의 테이블에서 나온다. (나머지 테이블은 중간 과정에서 사용되지만 최종 출력 제외)
-- 2. 조인
-- : 결과셋이 1개 이상의 테이블에서 나온다.
select * from insa;
select name from insa where basicpay = (select max(basicpay) from insa);
select * from employees;
select * from jobs;
--최고 급여?
select max(max_salary) from jobs;
--최고급여 받는 직책?
select * from jobs where max_salary = (select max(max_salary) from jobs);
--최대급여 받는 직원의 이름?
select * from employees
where job_id = (select job_id from jobs where max_salary = (select max(max_salary) from jobs));
-- --ex1] 크로스조인(corss join) 단순조인
select * from tblCustomer, tblSales;
-- --ex2] 내부조인(inner join)
select * from tblcustomer, tblSales;
-- ANSI SQL**
select * from tblCustomer
inner join tblSales
on tblCustomer.name = tblSales.customer; --PK=FK, 두컬럼이 같은 것만 가져오겠다!
-- Oracle전용
select * from tblCustomer, tblSales
where tblCustomer.name = tblSales.customer;
-- 언제 사용?
-- 노트를 사간(tblSales) 회원의 전화번호(tblCustomer)? -> 전화번호
-- 1. 서브쿼리
-- 2. 조인
select * from tblCustomer;
select * from tblSales;
select tel from tblCustomer where name = (select customer from tblSales where item = '노트'); --서브쿼리
select tblCustomer.tel from tblCustomer
inner join tblSales
on tblCustomer.name = tblSales.customer
where tblSales.item = '노트'; --조인, 조인에는 항상 테이블명을 명시
-- 노트를 사간 사람의 전화번호(tblCustomer)와 몇권(tblSales)을 사갔는지? -> 두 테이블의 정보를 한 테이블에 가져오기
select * from tblCustomer where name = (select customer from tblSales where item = '노트'); --서브쿼리, x
select tblCustomer.tel, tblSales.num from tblCustomer
inner join tblSales
on tblCustomer.name = tblSales.customer
where tblSales.item = '노트'; --조인, 서로 다른 테이블에 있는 정보를 한테이블에
-- 부산시에 사는 직원이 어떤 프로젝트를 담당했는지 프로젝트명을 출력하시오.
select * from tblStaff where address = '부산시';
select * from tblProject where staff = '하하하';
select pName from tblProject
where staff = (select name from tblStaff where address = '부산시'); --서브쿼리
--회사의 직원 명단과 함께 프로젝트 정보도 같이 출력하시오.
select * from tblProject
inner join tblStaff
on tblProject.staff = tblStaff.name; -- 조인, FK <-> PK
--최종**
select tblProject.pName from tblProject
inner join tblStaff
on tblProject.staff = tblStaff.name
where address = '부산시';
-- 부산시(tblStaff)에 사는 직원이 어떤 프로젝트를 담당했는지
-- 프로젝트명(tblProject)과 담당자의 월급(tblStaff)
select tblProject.pName, tblStaff.salery from tblProject
inner join tblStaff
on tblProject.staff = tblStaff.name
where address = '부산시';
--
select * from tblSales;
select * from regions;
select * from tblSales, regions; --크로스 조인
select * from tblSales
inner join regions
on tblSales.orderNo = regions.region_id; --조인
--별칭, Alias
select p.pName, s.salery from tblProject p
inner join tblStaff s
on p.staff = s.name
where s.address = '부산시';
-- --ex3] 외부조인(outer join)
select * from tblCustomer; --3명(구매이력o) -> 신규 회원 추가 -> 4명(구매이력o 3명, 구매이력x 1명)
select * from tblSales; --7건 판매이력
insert into tblCustomer values ('호호호', '010-666-7777', '서울시');
-- 내부조인의 특징
-- 판매이력 1건 이상인 회원 정보와 판매내역을 출력하시오.
-- : 양쪽 테이블에 모두 동시에 존재하는 내용만을 가져오기
-- *** 구매이력없는 회원 정보는 제외
select * from tblCustomer c
inner join tblSales s
on c.name = s.customer; --이너조인(추가한 호호호가 보이지 않음)
-- 외부조인
-- 판매이력 1건 이상인 회원 정보와 판매내역을 출력하시오. + 구매이력이 1건도 없는 회원 정보도 같이 출력하시오.
-- ANSI SQL
select * from tblCustomer c
left outer join tblSales s
on c.name = s.customer;
-- Oracel
select * from tblCustomer c, tblSales s
where c.name = s.customer(+);
-- --ex3] 셀프조인(self join)
-- 직원 정보
create table tblSelf
(
seq number primary key, --직원번호
name nvarchar2(5) not null, --직원명
depart nvarchar2(10) not null, --부서명
--super number not null --직속상사(***)
super number null references tblSelf(seq) --자기 자신을 참조(FK), 반드시 null
);
insert into tblSelf values (1, '홍사장', '총무부', null);
insert into tblSelf values (2, '김부장', '총무부', 1);
insert into tblSelf values (3, '최부장', '엽업부', 1);
insert into tblSelf values (4, '박과장', '엽업부', 3);
insert into tblSelf values (5, '유대리', '영업부', 4);
insert into tblSelf values (6, '이사원', '영업부', 5);
select * from tblSelf;
-- 직원 명단을 출력하시오.
select * from tblSelf;
select s2.name as "직원명", s1.name as "상사명" from tblSelf s1
inner join tblSelf s2
on s1.seq = s2.super; -- PK=FK, 상사가 없는 홍사장은 제외
select s2.name as "직원명", nvl(s1.name, '상사없음') as "상사명" from tblSelf s1
right outer join tblSelf s2
on s1.seq = s2.super; -- 홍사장 포함 출력
-- Union
-- 1. join
-- : 1개 이상의 테이블 컬럼을 하나의 결과셋으로 반환 -> 컬럼합치기
-- 2. union
-- : 2개 이상의 결과셋을 하나의 결과셋으로 반환 -> 레코드 합치기
select * from tblCountry where cont = 'AS'; --행 4개
select * from tblCountry where cont = 'EU'; --행 3개
select * from tblCountry where cont = 'AS'
union
select * from tblCountry where cont = 'EU';
-- 게시판 운영
-- : 3년간(2013 - 30000건, 2014 - 50000건, 2015 - 100건)
create table board2013
(
seq number primary key, --글번호
name nvarchar2(10) not null, --글쓴이
content varchar2(2000) not null --글내용
);
create table board2014
(
seq number primary key, --글번호
name nvarchar2(10) not null, --글쓴이
content varchar2(2000) not null --글내용
);
create table board2015
(
seq number primary key, --글번호
name nvarchar2(10) not null, --글쓴이
content varchar2(2000) not null --글내용
);
insert into board2013 values (1, '홍길동', '2013년 글입니다.1');
insert into board2013 values (2, '아무게', '2013년 글입니다.2');
insert into board2013 values (3, '호호호', '2013년 글입니다.3'); --2013, 3만건
insert into board2014 values (4, '홍길동', '2014년 글입니다.1');
insert into board2014 values (5, '아무게', '2014년 글입니다.2');
insert into board2014 values (6, '호호호', '2014년 글입니다.3'); --2014, 5만건
insert into board2015 values (3, '호호호', '2015년 글입니다.3'); --2015, 1백건
select * from board2015
select * from board2014
select * from board2013;
-- 년도에 상관없이 모든 게시물을 한꺼번에 보고싶다!!!
select * from board2015
union
select * from board2014
union
select * from board2013;
--문제
-- 1. 입사월별로 인원수를 구하시오. (몇월, 몇명 입사?) group by, count, to_char
-- 출력
-- [월] [인원수]
-- 1월 20
-- 2월 25
-- 3월 7
select to_char(ibsadate, 'MM') as 월, count(*) as 인원수
from insa group by to_char(ibsadate, 'MM')
order by to_char(ibsadate, 'MM') asc;
-- 2. 기본급+수당 가장 많은 사람이 누구? (이름, 기본급+수당), 서브쿼리
select name, basicpay+sudang from insa where basicpay+sudang in (select max(basicpay + sudang) from insa);
-- 3. 부서별 급여합을 구하시오.
select buseo, sum(basicpay) from insa group by buseo;
-- 3. 직위별 급여합을 구하시오.
select jikwi, sum(basicpay) from insa group by jikwi;
-- 4. 부서별 인원수를 구하되.. 총 인원이 5명 이상인 결과만 출력하시오. group by, having
select buseo, count(*) from insa group by buseo having count(*) >= 5;
-- 5. 성씨가 1자리 : 성씨별 인원수 출력(성, 인원수) group by
select distinct substr(name, 1, 1), count(*) from insa group by substr(name, 1, 1);
-- 6. 성별 인원수? (남자 몇명? 여자 몇명?)
-- 7. 부서별 남자직원수가 5명 이상인 부서와 그 인원수? (4문제와 비교)
-- 8. 입사년도별 인원수(ex) 2000년 5명, 2001년 6명, 2002년 2명..)
select to_char(ibsadate, 'YYYY'), count(*) from insa group by ibsadate; --ing
-- 9. 기본급여 평균 이하인 사원 출력(이름, 기본급)
-- 10. 전체인원수, 2000년, 1999년, 1998년도에 입사한 직원수를 다음의 형식으로 출력. decode, count
-- [전체] [2000] [1999] [1998]
-- 60 12 8 4