Oracle 실습문제 (8)

-- [1] 테이블 작성
-- 1. sung 테이블 작성
create table sung(
 hak number(1) not null
  , ban number(2) not null
  , beonho number(2) not null
  , name varchar2(20) not null
  , com number(3) not null
  , excel number(3) not null
  , word number(3) not null
  , constraint sung_id_PK primary key(hak, ban, beonho)
  , constraint sung_id_CK check ((hak between 1 and 3) and (ban between 1 and 10) and (beonho between 1 and 50))
);

-- 2. sung_tot 테이블 작성
create table sung_tot(
  hak number(1) not null
  , ban number(2) not null
  , com number
  , excel number
  , word number
  , constraint sung_tot_id_PK primary key(hak, ban)
);

/*
[2] 뷰 작성
sung 테이블을 이용하여 다음의 필드를 갖는 viewSung라는 이름으로 view를 작성한다.

?출력할 필드
  hak, ban, beonho, name, com, excel, word, tot, ave, banrank, hakrank
?처리 조건
  tot = com+excel+word
  ave = (com+excel+word) / 3
  banrank : 반별 석차
  hakrank : 학년별 석차
*/
select * from sung_tot;
select * from sung;
-----------------------데이터 입력--------------------------------
insert into sung(hak, ban, beonho, name, com, excel, word) values(1, 1, 1, '홍길동', 100, 90, 80);
insert into sung(hak, ban, beonho, name, com, excel, word) values(1, 1, 2, '김길동', '90', '80', '70');
insert into sung(hak, ban, beonho, name, com, excel, word) values(1, 1, 3, '이길동', '80', '70', '60');
insert into sung(hak, ban, beonho, name, com, excel, word) values(1, 1, 4, '권길동', '70', '60', '50');
insert into sung(hak, ban, beonho, name, com, excel, word) values(1, 2, 5, '박길동', '60', '50', '40');
insert into sung(hak, ban, beonho, name, com, excel, word) values(1, 2, 6, '최길동', '50', '40', '30');
insert into sung(hak, ban, beonho, name, com, excel, word) values(1, 2, 7, '국길동', '40', '30', '20');
commit;
------------------------------------------------------------------
create or replace view viewSung
as
select hak, ban, beonho, name, word, (com+excel+word) as tot, trunc((com+excel+word)/3, 1) as ave,
rank() over(partition by ban order by com+excel+word desc) banrank,
rank() over(partition by hak order by com+excel+word desc) hakrank
from sung;

select * from viewsung;
/*
[3] 트리거 작성
sung 테이블에 자료가 추가되거나 변경되는 경우 각 학년, 반별 각 과목의
합을 sung_tot 테이블에 계산되도록 트리거를 작성한다.
(추가, 수정, 삭제 트리거 작성)*/

create or replace trigger insert_sung_tot
after insert on sung
for each row
declare
vcount number;
begin
select count(*) into vcount from sung_tot where hak=:new.hak and ban=:new.ban;
if vcount=0 then
insert into sung_tot(hak, ban, com, excel, word) values(:new.hak, :new.ban, :new.com, :new.excel, :new.word); 
else
update sung_tot set com=com+:new.com, excel=excel+:new.excel, word=word+:new.word where hak=:new.hak and ban=:new.ban; 
end if;
end;

--프로시져 insert_sung_tot 테스트
insert into sung (hak, ban, beonho, name, com, excel, word) values (1,1,1,'a1',1,2,3);
insert into sung (hak, ban, beonho, name, com, excel, word) values (1,1,2,'a2',4,5,5);
insert into sung (hak, ban, beonho, name, com, excel, word) values (1,1,3,'a3',15,5,4);
insert into sung (hak, ban, beonho, name, com, excel, word) values (1,1,4,'a4',1,1,1);
commit;

select * from sung;
select * from sung_tot;
select * from viewSung;
-- 수정 트리거
create or replace trigger update_sung_tot
after update on sung
for each row
begin
update sung_tot set com=com+:new.com-:old.com, excel=excel+:new.excel-:old.excel, word=word+:new.word-:old.word 
where hak=:old.hak and ban=:old.ban;
end;

--프로시져 update_sung_tot 테스트
update sung set com=2, excel=2, word=2 where hak=1 and ban=1 and beonho=4;
commit;
select * from sung;
select * from sung_tot;
-- 삭제 트리거
create or replace trigger delete_sung_tot
after delete on sung
for each row
begin
update sung_tot set com=com-:old.com, excel=excel-:old.excel, word=word-:old.word
where hak=:new.hak and ban=:new.ban;
end; 

--프로시져 delete_sung_tot 테스트
delete from sung where hak=1 and ban=1 and beonho=1;
commit;
select * from sung_tot;

--[4] 프로시저 작성
/*
1. viewSung 를 이용하여 다음과 같은 결과를 출력하는 프로시저를 작성한다.
 ?출력 필드
   학년 반 번호 이름 총점 평균 학급석차 학년석차 판정
   (단, 학년과 반은 인수를 넘겨받아 해당 학년/반만 출력되도록 한다.)
?판정
  세 과목 모두 40점 이상 이고, 평균 60점 이상 : 합격
  세 과목 중 한과목이라도 40점 미만이고 ,평균 60점 이상이면 : 과락
  그렇지 않으면 불합격으로 처리 한다.
*/
create or replace procedure pro_sung
(
  phak viewSung.hak%type
  ,pban viewSung.ban%type
)
is
vpan varchar2(10);

cursor sung_list is select * from viewSung where hak=phak and ban=pban; 
begin
for rec in sung_list loop
if rec.ave>=60 and (rec.com>=40 and rec.excel>=40 and rec.word>=40) then vpan:='합격';
elsif rec.ave>=60 and (rec.com<40 or rec.excel<40 or rec.word<40) then vpan:='과락';
else vpan:='불합격';
end if;
dbms_output.put_line(rec.hak||' '||rec.ban||' '||rec.beonho||' '||rec.name||' '||rec.tot||' '||rec.ave||' '||rec.hakrank||' '||rec.banrank||' '||vpan);  
end loop;
end;

-- 프로시져 pro_sung 테스트
exec pro_sung(1,1);

-- 2. sung 테이블에 자료를 추가하는 프로시저 작성

create or replace procedure insert_sung
(
  phak sung.hak%type
  ,pban sung.ban%type
  ,pbeonho sung.beonho%type
  ,pname sung.name%type
  ,pcom sung.com%type
  ,pexcel sung.excel%type
  ,pword sung.word%type
)
is
begin
insert into sung(hak,ban,beonho,name,com,excel,word) values(phak,pban,pbeonho,pname,pcom,pexcel,pword);
commit;
end;   

--프로시져 insert_sung 테스트
exec insert_sung(1,2,5,'문성수',100,100,100);
select * from sung;
-- 3. sung 테이블에 자료를 수정하는 프로시저 작성

create or replace procedure update_sung
(
  phak sung.hak%type
  ,pban sung.ban%type
  ,pbeonho sung.beonho%type
  ,pname sung.name%type
  ,pcom sung.com%type
  ,pexcel sung.excel%type
  ,pword sung.word%type
)
is
begin 
update sung set name=pname,com=pcom,excel=pexcel,word=pword where hak=phak and ban=pban and beonho=pbeonho;
commit;
end;   

--프로시져 update_sung 테스트
exec update_sung (1,2,5,'허현무',95,95,95);
select * from sung;
-- 4. 학년, 반, 번호를 인수로 넘겨받아 sung 테이블에 자료를 삭제하는 프로시저 작성

create or replace procedure delete_sung
(
   phak sung.hak%type
  ,pban sung.ban%type
  ,pbeonho sung.beonho%type
)
is
begin
    delete from sung where hak=phak and ban=pban and beonho=pbeonho;
    commit;
end;

--프로시져 delete_sung 테스트
exec delete_sung(1,2,5);
select * from sung;
-- [5] insa 테이블
-- 1. 서울 사람 중 근무 개월 수가 60개월 이상인 사람만 출력(이름, 입사일)
select name 이름, ibsadate 입사일 from insa
where months_between(sysdate, ibsadate)>='60';

-- 2. 기본급 10 만원 당 *를 하나씩 출력 한다.(이름, 기본급, 그래프)
select name 이름, basicpay 기본급, lpad('*', basicpay/100000, '*') 그래프
from insa;

-- 3. 부서별 인원수를 출력 한다.
select buseo 부서, count(*) 인원수 from insa group by buseo;

-- 4. 부서별 인원수가 가장 많은 부서명 및 인원수를 출력 한다.
select * from (select buseo 부서명, count(*) 인원수 from insa group by buseo order by 인원수 desc)
where rownum = 1;

-- 5. 부서별 여자 인원수를 출력 한다.
select buseo 부서, count(*) as "여자 인원수" from insa where substr(ssn, 8, 1) in (2, 4)
group by buseo;

-- 6. 부서별 여자 인원수가 가장 많은 부서명 및 여자 인원수를 출력 한다.
select * from (select buseo 부서명, count(*) "여자 인원수" from insa
where substr(ssn, 8, 1) in (2, 4)
group by buseo order by "여자 인원수" desc) where rownum=1;

-- 7. 평균 급여(기본급)를 출력 한다.
select trunc(avg(basicpay)) 평균급여 from insa;

-- 8. 평균 급여(기본급)보다 적은 사람을 출력 한다.(이름, 기본급)
select name 이름, basicpay 기본급 from insa
where basicpay < (select avg(basicpay) from insa) order by 기본급;

-- 9. 평균 급여(기본급)보다 크거나 같은 사람의 인원수를 출력 한다.
select count(*) "평균급여 이상 인원수" from insa
where basicpay >= (select avg(basicpay) from insa);

-- 10. 80~89 년생의 인원수를 구하여라.
select count(*) "80~89년생 인원수" from insa
where substr(ssn, 1, 1)=8;

-- [6] JOIN 실습
-- 테이블 작성 book
create table book (
  b_id varchar2(10) not null
  , title varchar2(100) not null -- 책명
  , c_name varchar2(100) not null -- 출판사명
  , constraint book_id_PK primary key(b_id)
);

-- 테이블 작성 danga
create table danga (
  b_id varchar2(10) not null
  , price number(7) not null -- 단가
  , constraint danga_id_PK primary key(b_id)
  , constraint danga_id_FK foreign key(b_id)
  references book(b_id)
);

-- 테이블 작성 gogaek
create table gogaek (
  g_id number(5) -- 서점 코드
  , g_name varchar2(20) not null -- 서점 명
  , g_tel varchar2(20) -- 전화번호
  , constraint gogaek_id_PK primary key(g_id)
);

-- 테이블 작성 panmai
create table panmai (
  id number(5) not null
  , g_id number(5) not null
  , b_id varchar2(10) not null
  , p_date date -- 판매일자
  , p_su number(5) not null -- 판매수량
  , constraint panmai_id_PK primary key(id)
  , constraint panmai_id_FK1 foreign key(g_id)
  references gogaek(g_id)
  , constraint panmai_id_FK2 foreign key(b_id)
  references book(b_id)
);

/*
2. book 테이블, panmai 테이블, danga 테이블, gogaek 테이블을 b_id 필드와 g_id 필드를 기준으로 조인하여
다음을 출력 한다.(EQUI 조인)
  -- 책이름(title) 고객명(g_name) 수량(p_su) 단가(price) 금액(p_su*price)
*/
book : b_id
danga : b_id
gogaek : g_id
panmai : g_id, b_id

select b.title 책이름, g.g_name 고객명, p.p_su 수량, d.price 단가, p.p_su*d.price 금액
from panmai p join book b on p.b_id=b.b_id
join danga d on p.b_id=d.b_id
join  gogaek g on p.g_id=g.g_id;

/*
3. panmai 테이블, danga 테이블, gogaek 테이블을 b_id 필드와 g_id 필드를 기준으로 조인하여 고객별 판매 금액을
출력 한다.(EQUI 조인)
  -- g_name, '수량 * 단가'의 합
*/
select g.g_name 고객명, sum(p.p_su*d.price) 판매금액
from panmai p join danga d on p.b_id=d.b_id
join gogaek g on p.g_id=g.g_id
group by g.g_name;

/*
4. book 테이블, panmai 테이블, b_id 필드를 기준으로 조인하여 다음의 필드 출력 한다. 단, book 테이블의 모든 행은
출력 되도록 한다.(OUTER 조인)
  -- b_id, title, p_su 필드 출력
*/
select b.b_id, b.title, p_su
from book b left outer join panmai p on b.b_id=p.b_id;

/*
5. book 테이블, panmai 테이블, gogaek 테이블을 b_id 필드와 g_id 필드를 기준으로 조인하여 다음의 필드 출력 한다.
단, book 테이블의 모든 행은 출력 되도록 한다.(OUTER 조인)
  -- b_id, title, g_name, p_su 필드 출력
*/
select b.b_id, b.title, g.g_name, p.p_su
from panmai p join book b on p.b_id=b.b_id
join gogaek g on p.g_id=g.g_id;

/*
6. 다음의 처리 조건에 따라 2000년도 판매 현황을 출력 한다.
  -출력 형식
    책명(title) 서점명(g_name) 금액
  -처리 조건
   1) 금액 = "수량 * 단가" 의 합계
     합계는 title, g_name에 의해 그룹화 한다. 즉, title별 서점의 판매 금액에 대한 합계이다.
   2) panmai.p_date 가 2000년인 자료의 판매 현황만 출력 한다.
*/
select b.title 책명, g.g_name 서점명, sum(p.p_su*d.price) 금액
from panmai p join book b on p.b_id=b.b_id
join gogaek g on p.g_id=g.g_id
join danga d on p.b_id=d.b_id
where to_char(p_date, 'yyyy')='2000'
group by b.title, g.g_name;