오라클 테이블 관계(PK,FK,JOIN)

* 테이블간의 관계
 - 무결성 유지

* 참조키(foreign ky, FK)
 - 반드시 참조하는 컬럼에 이미 존재하는 값 중에 하나를 삽입** 
 - FK는 PK를 참조
 - 참조시 관계를 맺는 기준? -> 나중에 생기는 테이블(FK)이 먼저 생기는 테이블(PK)을 참조****
* Join (옆으로 합)
 - 내부조인 : 쓸모있는 데이터만 남겨두는., *****연습
 - 외부조인 ***** 
 - 셀프조인, 크로스조인 
 - 조인 vs 서브쿼리 : 결과테이블이 같은 테이블에서 나오는지? 다른 테이블에서 나오는지?를 생각.,
* union (아래로 합)


 
-- 테이블간의 관계(엔티티간의 관계)
 
-- 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