DB 프로젝트_행사관리시스템_개발일정, 개발동기, 시스템소개, 기능, 문항정리(SQL, PLSQL)

(1조)DB설계 세미프로젝트 상세보고서_(진짜최종)_20231205.hwp

14.79MB

1조-EASY행사관리.pptx

6.86MB

raw 데이터.xlsx

1.90MB

insert 용도 데이터.xlsx

3.20MB

설계도 한글 Logical.jpg

0.78MB

설계도 영문 Physical.jpg

0.74MB

4차 최종 설계도_1128-physical.erwin

0.26MB

 

[개발 일정]

 

4차 최종 설계도_1128-physical.erwin

0.26MB

 

설계도 한글 Logical

설계도 영문 Physical

 
[개발 동기]

<문제점>
1. 실제 현업에 종사했던 행사 관리자의 고충을 통해, 
행사 진행 과정에서 발생했던 다양한 데이터를 효율적으로 관리할 필요성을 느꼈습니다.
2. 행사의 규모와 예산이 점점 증가하면서 행사 관리의 중요성이 커지고 있습니다.
3. 따라서 행사의 진행 상황을 실시간으로 모니터링하고, 의사결정을 지원할 수 있는 시스템이 필요 했습니다.

<활용도>
행사 관리 시스템은 행사의 기획 -> 진행 -> 종료에 이르는 전 과정을 관리하는 시스템으로, 
기업, 단체, 정부 등 다양한 조직에서 활용되고 있습니다.

<이점>
효율적인 행사 관리를 위해 반드시 필요한 
핵심적이고도 다양한 기능을 구현함으로써
이를 통해 행사 주최자 및 관리자 에게 
다양한 편의와 혜택을 제공할 수 있습니다.

<다양한 요구 충족>
1. 행사의 진행 현황 및 실적을 파악하기 위한 요구
2. 행사의 예산 및 비용 관리를 위한 요구
3. 행사의 품질 및 효율성 향상을 위한 요구
4. 행사의 안전 및 사고 예방을 위한 요구
5. 행사 관리 효율성 제고
6. 행사 관리의 투명성 확보
7. 행사 관련 데이터의 통합 관리
8. 행사 관련 의사결정 지원
 
---------------------------------------------------------------------------------
 
[시스템 소개]
 
이러한 개발 동기를 바탕으로, 해당 시스템은 다음과 같은 특징을 갖도록 설계 하였습니다.
 
<특징>
1. 행사의 기획, 진행, 종료에 대한 전반적인 관리
2. 행사의 효율적인 운영
3. 행사의 투명성 확보

행사 관리 시스템의 주요 기능은 다음과 같습니다.

<주요 기능>
1. 행사 정보 관리 (행사 생성/수정/삭제/조회)
2. 행사 진행 현황 관리 (행사 장소/규모/내용 관리)
3. 행사 비용 관리 (행사 예산/비용/실적 관리)
4. 행사 관련 데이터 통합 관리 (참가자/계약/결제/물품/인력 관리)
5. 행사 관련 의사결정 지원 (행사의 진행여부, 예산 집행 등을 분석)

구체적인 기능은 다음과 같습니다.

<상세 기능>
1. 행사명, 행사일시, 행사장소, 행사규모, 행사내용, 행사예산, 행사비용, 행사실적 등의 정보를 입력/수정/삭제/조회.
2. 행사의 예산, 비용, 실적을 입력/수정/조회할 수 있으며, 이를 통해 행사의 진행 현황을 파악.
3. 행사의 계약, 결제, 장소, 규모, 내용을 입력/수정/조회.
4. 행사의 진행 현황을 모니터링하여 의사결정을 지원.
5. 총 예산 안에서 비용과 수익을 모니터링 함으로써 회계 사고를 사전에 예방하는 기능을 제공. 

<마무리>
이러한 기능들은 행사의 효율적인 관리를 위해 필요하며, 
이에 필요한 의사결정을 지원할 수 있을 것으로 기대 됩니다.
이를 통해 행사 주최자 및 관리자에게 다양한 편의와 혜택을 제공할 수 있습니다.
또한 기업, 단체, 정부 등 다양한 조직에서 주최하는 행사 관리를
효율적이고, 투명하며, 행사 이익을 극대화 시키는 것에 기여할 수 있을 것으로 기대 됩니다.
 
---------------------------------------------------------------------------------
 
[문항 정리]
 
<SQL>

1. 최근 3개월 내 진행한 행사의 행사명, 행사일자, 행사 총 예산, 행사규모등급, 내부투입인원수를 조회하시오.
2. 행사 진행여부가 '종료'인 행사 중 위치별 행사개최횟수가 가장 높은 TOP10과 행사진행여부를 조회하시오.
3. 진행여부가 ‘보류’인 행사 중 총예산이 가장 높은 행사 TOP3 조회
4. 행사 분야별 평균총예산, 평균 행사규모등급, 진행횟수를 조회하시오.
5. 행사규모등급별 이익률이 높은 행사 TOP3를 추출하고 행사명, 행사연도, 주최기관, 총예산, 행사위치를 조회하시오.
6. 진행여부가 ‘보류’인 행사 중 총 예산이 가장 높은 행사 TOP3를 조회하시오.
6-1. 진행여부가 ‘보류’인 행사 중 블랙리스트에 존재하는 주최 기관이 주최하는 행사 정보 조회하시오.
7. 최근 2년간 주최기관별 주최년도, 주최기관명, 행사진행 횟수, 평균총예산을 조회하시오.
8. 행사코드가 10001~10020 사이의 행사별 주관기관을 조회하시오.
9. 주최기관과 주관기관이 동일한 행사를 조회하시오.
10. 사원별 투입된 행사 횟수를 조회하시오.
11. 행사 담당자별 최다 담당분야 및 해당분야 행사담당 횟수를 조회하시오.
12. 가장 최근에 진행 종료된 IT 분야 행사에서 투입된 렌탈 물품 항목 정보를 조회하시오.
13. ‘리드커리어’ 인력업체에서 ‘철거인력’ 항목의 평균인건비용이 3% 증가할 시, 변경된 총인건비를 계산하시오.
14. 렌탈 및 인력 결제에서 결제 여부가 N인 결제건 중 금일 기준 견적요청일이 2개월 이상 지난 건에 대한 거래처와 거래처담당자 정보, 견적요청일자를 조회하시오.
15. 거래처별 견적요청일자와 실제 견적결제일자 사이의 기간에 대한 평균값을 계산하시오.
16. 금년도(2023) 거래처별 총 결제횟수 및 총 결제금액을 조회하시오.
17. 행사분야가 ‘환경’인 행사의 총예산 대비 총지출비용과 기타사업비 비율 조회
18. 이번달(2023/12)에 진행될 행사의 행사진행기간 및 총예산, 총지출비용, 이익률 조회
19. 연도별 행사진행횟수 및 평균총예산, 평균총비용, 평균총수익 조회

<PLSQL>

1. 사원 이름을 입력 시, 해당 사원이 가장 최근에 진행한 행사명 및 행사시작일자를 출력하는 프로시저
2. 행사정보 테이블에서 총예산 정보 변경할 수 없으며, 변경 시도 시 오류 메시지를 반환하는 트리거
3. 이익률별 평균 수용인원과 특정 행사에 대한 수용인원을 비교하여 평균보다 초과일 경우 ‘초과’, 미만일 경우 ‘여유’를 출력하는 함수
4. 현재 보류 상태인 행사에 대해 총예산이 ‘8천만원’ 이하인 행사는 진행 취소로 변경하는 커서
5. 관련기관 신규 입력 시, 기존 행사관련기관 테이블에 중복될 경우 오류 메시지를 반환하는 트리거
6. 행사 관련기관별 기관 전화번호를 입력 시, 해당 기관의 위치를 출력하는 함수
7. 주최기관별 행사개최 횟수에 따른 기관 VIP 등급(A등급~D등급)을 출력하는 함수
8. 계약 성사 여부가 N으로 변경 시 계약파기일을 오늘로 변경, 행사 진행상태는 '취소'로 변경하는 트리거
9. 행사시작 2주전, 계약을 파기한 계약건을 블랙 리스트로 등록해두는 트리거
10. 거래처 정보 신규 입력 시, 기존 거래처 테이블에 중복될 경우 오류 메시지를 반환하는 트리거
11. 행사명 입력 시, 지출금액이 가장 높은 렌탈거래처 및 인력거래처 와 해당 금액 출력하는 프로시저
12. ‘스피드렌탈’ 렌탈업체의 전체 물품 단가를 모두 10% 인상하는 커서
13. 렌탈 견적 요청 수량이 변경될 시, 렌탈 견적 요청일자를 오늘로 변경하는 트리거
14. 인력 견적 요청 인원이 변경될 시, 인력 결제 테이블의 총 결제 금액을 변경하는 트리거
15. 신규 결제 건 입력 시, 해당 행사의 총지출비용이 총수익을 초과할 경우, 오류 메시지를 반환하는 트리거
16. 결제년월 입력 시, 렌탈 물품 및 인력 물품의 총 지출 비용을 출력하는 프로시저
17. 행사명 입력 시, 총수익 대비 총 지출비용의 비율을 출력하는 함수
 
---------------------------------------------------------------------------------
 
<SQL>

1. 최근 3개월 내 진행한 행사의 행사명, 행사일자, 행사 총 예산, 행사규모등급, 내부투입인원수를 조회하시오.

select a.e_name "행사명", 
to_char(a.e_s_date, 'yyyy/mm') "행사일자", 
a.e_tot_budget "행사총예산",
(select b.es_g_code from es_g@dbdb b 
where a.e_capa<=b.maxi_capa and a.e_capa>=b.mini_capa) "행사규모등급", 
count(d.ct_code)||'명' "내부투입인원수"
from e_info@dbdb a, ct_info@dbdb c, emp_input@dbdb d
where a.e_code=c.e_code
and c.ct_code=d.ct_code
and a.e_s_date>=add_months(sysdate,-3) and a.e_s_date<=sysdate
and d.emp_code is not null
group by a.e_name, to_char(a.e_s_date, 'yyyy/mm'), a.e_tot_budget, a.e_capa, d.ct_code
order by 2
;

 
2. 행사 진행여부가 '종료'인 행사 중 위치별 행사개최횟수가 가장 높은 TOP10과 행사진행여부를 조회하시오.

select * from 
(select a.e_loc "행사위치", count(a.e_code) "행사개최횟수", b.go_ckck "행사진행여부"
from e_info@dbdb a, go_ck@dbdb b
where b.go_ckck=(select go_ckck from go_ck@dbdb where go_ckck='종료' and a.go_ck_code=b.go_ck_code)
group by a.e_loc, b.go_ckck
order by 2 desc)
where rownum<=10
;

 
3. 진행여부가 ‘보류’인 행사 중 총예산이 가장 높은 행사 TOP3 조회

select e_name "행사명", e_tot_budget "총예산"
from
(select e.e_name, e.e_tot_budget from e_info@dbdb e
join go_ck@dbdb g on e.go_ck_code=g.go_ck_code
where g.go_ckck='보류'
order by e.e_tot_budget desc)
where rownum<=3;

 
4. 행사 분야별 평균총예산, 평균 행사규모등급, 진행횟수를 조회하시오.

select 행사분야, 평균총예산, eg.es_g_code 평균행사규모등급, 행사진행횟수
from 
(select fc.ef_class_name "행사분야", 
floor(avg(ei.e_tot_budget)) "평균총예산", 
floor(avg(ei.e_capa)) "평균행사수용인원",
count(*) "행사진행횟수"
from e_info@dbdb ei, ed_class@dbdb dc, ef_class@dbdb fc
where fc.ef_class_code=dc.ef_class_code
and dc.ed_class_code=ei.ed_class_code
group by fc.ef_class_name) a, es_g@dbdb eg
where 평균행사수용인원>=eg.mini_capa and 평균행사수용인원<=eg.maxi_capa
order by 1
;

 
5. 행사규모등급별 이익률이 높은 행사 TOP3를 추출하고 행사명, 행사연도, 주최기관, 총예산, 행사위치를 조회하시오.

select 행사명, 행사연도, 주최기관, 총예산, 위치 
from
(select e_info.e_name "행사명", 
to_char(e_info.e_s_date, 'yyyy') "행사연도", 
ro_info.ro_name "주최기관", 
e_info.e_tot_budget "총예산", 
e_info.e_loc "위치"
from e_info@dbdb e_info
join ro_info@dbdb on e_info.ro_code=ro_info.ro_code
join es_g@dbdb on e_info.e_capa between es_g.mini_capa and es_g.maxi_capa
order by e_info.e_tot_budget desc)
where rownum<=3
;

 
6. 진행여부가 ‘보류’인 행사 중 총 예산이 가장 높은 행사 TOP3를 조회하시오.

select e_name "행사명", e_tot_budget "총예산"
from
(select e.e_name, e.e_tot_budget from e_info@dbdb e
join go_ck@dbdb g on e.go_ck_code=g.go_ck_code
where g.go_ckck='보류'
order by e.e_tot_budget desc)
where rownum<=3
;

 
6-1. 진행여부가 ‘보류’인 행사 중 블랙리스트에 존재하는 주최 기관이 주최하는 행사 정보 조회하시오.

select e.e_code "행사코드", e.e_name "행사명", r.ro_name "주최기관", e.e_tot_budget "총예산", 
e.e_s_date "행사시작일", e.e_e_date "행사종료일"
from e_info@dbdb e, go_ck@dbdb g, ro_info@dbdb r
where g.go_ck_code=e.go_ck_code
and r.ro_code=e.ro_code
and g.go_ckck='보류'
and r.ro_name in 
(select r.ro_name 
from bl@dbdb b, ct_info@dbdb c, e_info@dbdb e, ro_info@dbdb r
where c.ct_code=b.ct_code
and e.e_code=c.e_code
and r.ro_code=e.ro_code);

 
7. 최근 2년간 주최기관별 주최년도, 주최기관명, 행사진행 횟수, 평균총예산을 조회하시오.

select
to_char(e_info.e_s_date, 'yyyy') "주최연도", 
ro_info.ro_name "주최기관명", 
count(e_info.e_code) "행사진행횟수",
avg(e_info.e_tot_budget) "평균총예산"
from e_info@dbdb
join ro_info@dbdb on ro_info.ro_code=e_info.ro_code
where e_info.go_ck_code=204 -- 행사진행여부가 종료인 행사코드는 204
and e_info.e_s_date>=add_months(trunc(sysdate),-24) 
group by to_char(e_info.e_s_date, 'yyyy'), ro_info.ro_name
order by 1, 2
;

 
8. 행사코드가 10001~10020 사이의 행사별 주관기관을 조회하시오.

select b.e_code "행사코드", b.e_name "행사명", c.eh_code "기관코드", a.ro_name "주관기관명"
from ro_info@dbdb a, e_info@dbdb b, eh_info@dbdb c
where b.e_code=c.e_code 
and a.ro_code=c.ro_code
and b.e_code>=10001 and b.e_code<=10020
group by b.e_name, a.ro_name, b.e_code, c.eh_code
order by 1, 3
;

 
9. 주최기관과 주관기관이 동일한 행사를 조회하시오.

/*
a.e_code = c.e_code and b.ro_code = c.ro_code 
행사 정보와 주최 기관 정보, 
그리고 행사와 주최 기관 간의 관계를 
행사 코드와 주최 기관 코드를 기준으로 조인

c.ro_code = d.ro_code
주최 기관 정보와 주관 기관 정보를 
주관 기관 코드를 기준으로 조인

group by a.e_name, b.ro_name, d.ro_name
행사명, 주최 기관명, 주관 기관명으로 그룹화. 
이는 동일한 행사에 대한 중복된 결과를 방지하는 역할
*/

select a.e_name "행사명", b.ro_name "주최기관명", d.ro_name "주관기관명"
from e_info@dbdb a 
join ro_info@dbdb b on a.ro_code=b.ro_code    
join eh_info@dbdb c on a.e_code=c.e_code and b.ro_code=c.ro_code
join ro_info@dbdb d on c.ro_code=d.ro_code 
group by a.e_name, b.ro_name, d.ro_name
order by 1
;

 
10. 사원별 투입된 행사 횟수를 조회하시오.

select emp_info.emp_name "사원명", dept.dept_name "부서", count(emp_input.emp_code) "투입행사횟수"
from emp_input@dbdb emp_input
join emp_info@dbdb emp_info on emp_input.emp_code=emp_info.emp_code
join dept@dbdb dept on dept.dept_code=emp_info.dept_code
group by dept.dept_name, emp_info.emp_name
order by 3 desc
;

 
11. 행사 담당자별 최다 담당분야 및 해당분야 행사담당 횟수를 조회하시오.

select 행사담당자, 부서, 최다담당분야, 담당횟수
from
(select emp.emp_name "행사담당자", d.dept_name "부서", ef.ef_class_name "최다담당분야", 
count(ef.ef_class_name) "담당횟수",
rank() over(partition by emp.emp_name order by count(ef.ef_class_name) desc) "순위"
from e_info@dbdb ei, emp_info@dbdb emp, dept@dbdb d, ed_class@dbdb ed, ef_class@dbdb ef 
where emp.emp_code=ei.emp_code
and d.dept_code=emp.dept_code
and ed.ed_class_code=ei.ed_class_code
and ef.ef_class_code=ed.ef_class_code
group by emp.emp_name, d.dept_name, ef.ef_class_name)
where 순위=1
order by 4 desc;

 
12. 가장 최근에 진행 종료된 IT 분야 행사에서 투입된 렌탈 물품 항목 정보를 조회하시오.

select ee.ef_class_name "분야", ee.ed_class_name "상세분류", ee.e_name "행사명", 
cm.cm_name "렌탈거래처", ri.ri_index "물품항목", rd.ri_qt "투입수량"
from (select * from
(select ef.ef_class_name, ed.ed_class_name, ei.e_code, e_name, e_s_date, e_e_date
from e_info@dbdb ei, ed_class@dbdb ed, ef_class@dbdb ef, go_ck@dbdb gc
where ef.ef_class_code=ed.ef_class_code
and ed.ed_class_code=ei.ed_class_code
and gc.go_ck_code=ei.go_ck_code
and ef.ef_class_name='IT'
and gc.go_ckck='종료'
order by 4 desc)
where rownum=1) ee, 
ct_info@dbdb ci, rs_d@dbdb rd, ri_info@dbdb ri, cm@dbdb cm
where ee.e_code=ci.e_code
and ci.ct_code=rd.ct_code
and ri.ri_code=rd.ri_code
and cm.cm_code=ri.cm_code
order by 4 asc, 6 desc
;

 
13. ‘리드커리어’ 인력업체에서 ‘철거인력’ 항목의 평균인건비용이 3% 증가할 시, 변경된 총인건비를 계산하시오.

select ei.e_name "행사명", lp.l_pay_code "결제코드", c.cm_name "거래처명", li.ld_index "인력항목", 
li.ld_avg_cost "기존 평균인건비용", lp.l_tot_pay "기존 총인건비", 
li.ld_avg_cost*1.03 "인상된 평균인건비용", li.ld_avg_cost*1.03*ld.l_qt "변경된 총인건비"
from cm@dbdb c, l_pay@dbdb lp, ls_d@dbdb ld, ld_info@dbdb li, ct_info@dbdb ci, e_info@dbdb ei
where ld.ls_d_code=lp.ls_d_code
and li.ld_code=ld.ld_code
and c.cm_code=li.cm_code
and ci.ct_code=ld.ct_code
and ei.e_code=ci.e_code
and lp.l_pay_ck='N'
and c.cm_name='리드커리어'
and li.ld_index='철거 인력'
;

 
14. 렌탈 및 인력 결제에서 결제 여부가 N인 결제건 중 금일 기준 견적요청일이 2개월 이상 지난 건에 대한 거래처와 거래처담당자 정보, 견적요청일자를 조회하시오.

select l.l_pay_ck "결제여부", ei.e_name "행사명", o.cm_1st_name "거래처구분", 
c.cm_name "거래처명", c.cm_repre_name "담당자", c.cm_repre_tel "담당자전화번호", d.ls_s_date "견적요청일자"
from l_pay@dbdb l, ls_d@dbdb d, ld_info@dbdb i, 
cm@dbdb c, cm_2nd_class@dbdb t, cm_1st_class@dbdb o, 
ct_info@dbdb ci, e_info@dbdb ei
where l_pay_ck='N'
and d.ls_d_code=l.ls_d_code
and i.ld_code=d.ld_code
and c.cm_code=i.cm_code
and t.cm_2nd_code=c.cm_2nd_code
and o.cm_1st_code=t.cm_1st_code
and ci.ct_code=d.ct_code
and ei.e_code=ci.e_code
and d.ls_s_date<add_months(sysdate,-2)
group by 
ei.e_name, c.cm_name, c.cm_repre_name, 
c.cm_repre_tel, d.ls_s_date, l.l_pay_ck, o.cm_1st_name
union all
select r.ri_pay_ck "결제여부", ei.e_name "행사명", o.cm_1st_name "거래처구분", 
c.cm_name "거래처명", c.cm_repre_name "담당자", c.cm_repre_tel "담당자전화번호", rd.rs_s_date "견적요청일자"
from ri_pay@dbdb r, rs_d@dbdb rd, ri_info@dbdb ri, 
cm@dbdb c, cm_2nd_class@dbdb t, cm_1st_class@dbdb o, 
ct_info@dbdb ci, e_info@dbdb ei
where ri_pay_ck='N'
and rd.rs_d_code=r.rs_d_code
and ri.ri_code=rd.ri_code
and c.cm_code=ri.cm_code
and t.cm_2nd_code=c.cm_2nd_code
and o.cm_1st_code=t.cm_1st_code
and ci.ct_code=rd.ct_code
and ei.e_code=ci.e_code
and rd.rs_s_date<add_months(sysdate,-2)
group by ei.e_name, c.cm_name, c.cm_repre_name, 
c.cm_repre_tel, rd.rs_s_date, r.ri_pay_ck, o.cm_1st_name
order by 2, 7
;

 
15. 거래처별 견적요청일자와 실제 견적결제일자 사이의 기간에 대한 평균값을 계산하시오.

select o.cm_1st_name "거래처구분", c.cm_name "거래처명", 
floor(avg(rp.ri_pay_date-rd.rs_s_date))||'일' "평균결제기간"
from ri_pay@dbdb rp, rs_d@dbdb rd, ri_info@dbdb ri, 
cm@dbdb c, cm_2nd_class@dbdb t, cm_1st_class@dbdb o
where rd.rs_d_code=rp.rs_d_code
and ri.ri_code=rd.ri_code
and c.cm_code=ri.cm_code
and t.cm_2nd_code=c.cm_2nd_code
and o.cm_1st_code=t.cm_1st_code
group by c.cm_name, o.cm_1st_name
union all
select o.cm_1st_name "거래처구분", c.cm_name "거래처명", 
floor(avg(lp.l_pay_date-ld.ls_s_date))||'일' "평균결제기간"
from cm@dbdb c, l_pay@dbdb lp, ls_d@dbdb ld, ld_info@dbdb li, 
cm_2nd_class@dbdb t, cm_1st_class@dbdb o
where ld.ls_d_code=lp.ls_d_code
and li.ld_code=ld.ld_code
and c.cm_code=li.cm_code
and t.cm_2nd_code=c.cm_2nd_code
and o.cm_1st_code=t.cm_1st_code
group by c.cm_name, o.cm_1st_name
order by 3 desc
;

 
16. 금년도(2023) 거래처별 총 결제횟수 및 총 결제금액을 조회하시오.

select c1.cm_1st_name "거래처분야", cm.cm_name "거래처명", 
count(cm.cm_name) "2023 총결제 횟수", sum(lp.l_tot_pay) "2023 총결제금액"
from l_pay@dbdb lp, ls_d@dbdb ld, ld_info@dbdb li, 
cm@dbdb cm, cm_2nd_class@dbdb c2, cm_1st_class@dbdb c1
where ld.ls_d_code=lp.ls_d_code
and li.ld_code=ld.ld_code
and cm.cm_code=li.cm_code
and c2.cm_2nd_code=cm.cm_2nd_code
and c1.cm_1st_code=c2.cm_1st_code
and to_char(l_pay_date, 'yyyy')=2023 and l_pay_ck='Y' 
group by cm_name, c1.cm_1st_name
union all
select c1.cm_1st_name, cm.cm_name, 
count(cm.cm_name), sum(rp.ri_tot_pay)
from ri_pay@dbdb rp, rs_d@dbdb rd, ri_info@dbdb ri, 
cm@dbdb cm, cm_2nd_class@dbdb c2, cm_1st_class@dbdb c1
where rd.rs_d_code=rp.rs_d_code
and ri.ri_code=rd.ri_code
and cm.cm_code=ri.cm_code
and c2.cm_2nd_code=cm.cm_2nd_code
and c1.cm_1st_code=c2.cm_1st_code
and to_char(ri_pay_date, 'yyyy')=2023 and ri_pay_ck='Y' 
group by cm_name, c1.cm_1st_name
order by 1, 3 desc
;

 
17. 행사분야가 ‘환경’인 행사의 총예산 대비 총지출비용과 기타사업비 비율 조회

with etab as (select 분야, 행사명, 총예산, sum(총결제금액) 총지출비용, 기타사업비
from (select fc.ef_class_name "분야", ei.e_name "행사명", ei.e_tot_budget "총예산", 
sum(ri_tot_pay) "총결제금액", oc.other_cost "기타사업비", rp.ri_pay_ck "결제여부"
from e_info@dbdb ei, ct_info@dbdb ci, rs_d@dbdb rd, 
ri_pay@dbdb rp, ed_class@dbdb dc, ef_class@dbdb fc, 
other_cost_info@dbdb oc
where ei.e_code=ci.e_code
and ci.ct_code=rd.ct_code
and rd.rs_d_code=rp.rs_d_code
and fc.ef_class_code=dc.ef_class_code
and dc.ed_class_code=ei.ed_class_code
and ci.ct_code=oc.ct_code
and fc.ef_class_name='환경'
and rp.ri_pay_ck='Y'
group by ei.e_name, fc.ef_class_name, rp.ri_pay_ck, ei.e_tot_budget, oc.other_cost
union all
select fc.ef_class_name "분야", ei.e_name "행사명", ei.e_tot_budget "총예산", 
sum(l_tot_pay) "총결제금액", oc.other_cost "기타사업비", lp.l_pay_ck "결제여부"
from e_info@dbdb ei, ct_info@dbdb ci, ls_d@dbdb ld, 
l_pay@dbdb lp, ed_class@dbdb dc, ef_class@dbdb fc, 
other_cost_info@dbdb oc
where ei.e_code=ci.e_code
and ci.ct_code=ld.ct_code
and ld.ls_d_code=lp.ls_d_code
and fc.ef_class_code=dc.ef_class_code
and dc.ed_class_code=ei.ed_class_code
and ci.ct_code=oc.ct_code
and fc.ef_class_name='환경'
and lp.l_pay_ck='Y'
group by ei.e_name, fc.ef_class_name, lp.l_pay_ck, ei.e_tot_budget, oc.other_cost
order by 1)
group by 행사명, 분야, 총예산, 기타사업비)
select e.*, floor((e.총지출비용/e.총예산)*100)||'%' "총예산대비 총지출비율", 
floor((e.기타사업비/e.총예산)*100)||'%' "총예산대비 기타사업비율"
from etab e;

18. 이번달(2023/12)에 진행될 행사의 행사진행기간 및 총예산, 총지출비용, 이익률 조회

with event_info as 
(select ei.e_name "행사명", ei.e_s_date "행사시작일", ei.e_e_date "행사종료일", 
ei.e_e_date-ei.e_s_date+1||'일' "진행기간", 
e_tot_budget "총예산", pr_g_code "이익률"
from e_info@dbdb ei, pr_g@dbdb pr, go_ck@dbdb gc
where to_char(ei.e_s_date, 'yy/mm')='23/12'
and ei.e_tot_budget>=mini_tot_b and ei.e_tot_budget<=maxi_tot_b
and gc.go_ck_code=ei.go_ck_code
and gc.go_ckck!='보류'
order by 1),
event_pay as 
(select "행사명", sum("총결제금액") "총지출비용"
from (select ei.e_name "행사명", sum(l_tot_pay) "총결제금액", lp.l_pay_ck "결제여부"
from e_info@dbdb ei, ct_info@dbdb ci, ls_d@dbdb ld, l_pay@dbdb lp
where ei.e_code=ci.e_code
and ci.ct_code=ld.ct_code
and ld.ls_d_code=lp.ls_d_code
and to_char(ei.e_s_date, 'yy/mm')='23/12'
group by ei.e_name, lp.l_pay_ck
union all
select ei.e_name "행사명", sum(ri_tot_pay) "총결제금액", rp.ri_pay_ck "결제여부"
from e_info@dbdb ei, ct_info@dbdb ci, rs_d@dbdb rd, ri_pay@dbdb rp
where ei.e_code=ci.e_code
and ci.ct_code=rd.ct_code
and rd.rs_d_code=rp.rs_d_code
and to_char(ei.e_s_date, 'yy/mm')='23/12'
group by ei.e_name, rp.ri_pay_ck
order by 1)
group by "행사명")
select evi.*, evp.총지출비용
from event_info evi, event_pay evp
where evi.행사명=evp.행사명;

 

19. 연도별 행사진행횟수 및 평균총예산, 평균총비용, 평균총수익 조회

with avg_cost as (select 연도, floor(sum(평균지출비용)) 평균총비용
from (select to_char(ei.e_s_date, 'yyyy') "연도", avg(l_tot_pay) "평균지출비용"
from e_info@dbdb ei, ct_info@dbdb ci, ls_d@dbdb ld, l_pay@dbdb lp, go_ck@dbdb gc
where ei.e_code=ci.e_code
and ci.ct_code=ld.ct_code
and ld.ls_d_code=lp.ls_d_code
and gc.go_ck_code=ei.go_ck_code
and gc.go_ckck in ('진행중', '종료')
and lp.l_pay_ck='Y'
group by to_char(ei.e_s_date, 'yyyy')
union all
select to_char(ei.e_s_date, 'yyyy') "연도", avg(ri_tot_pay) "평균지출비용"
from e_info@dbdb ei, ct_info@dbdb ci, rs_d@dbdb rd, ri_pay@dbdb rp, go_ck@dbdb gc
where ei.e_code=ci.e_code
and ci.ct_code=rd.ct_code
and rd.rs_d_code=rp.rs_d_code
and gc.go_ck_code=ei.go_ck_code
and gc.go_ckck in ('진행중', '종료')
and rp.ri_pay_ck='Y'
group by to_char(ei.e_s_date, 'yyyy'))
group by 연도
order by 1),
avg_mon as (select eavg.*, pg.pr_g_code 평균이익률
from (select to_char(ei.e_s_date, 'yyyy') "연도", count(*) "행사횟수", 
floor(avg(ei.e_tot_budget)) "평균총예산"
from e_info@dbdb ei, ct_info@dbdb ci, go_ck@dbdb gc
where ei.e_code=ci.e_code
and gc.go_ckck in ('진행중', '종료')
and gc.go_ck_code=ei.go_ck_code
group by to_char(ei.e_s_date, 'yyyy')) eavg, pr_g@dbdb pg
where eavg.평균총예산>=pg.mini_tot_b and eavg.평균총예산<=pg.maxi_tot_b
order by 1)
select m.*, c.평균총비용, floor((평균총예산*평균이익률)) "평균총수익"
from avg_cost c, avg_mon m
where c.연도=m.연도;

---------------------------------------------------------------------------------
 
<PLSQL>
 
1. 사원 이름을 입력 시, 해당 사원이 가장 최근에 진행한 행사명 및 행사시작일자를 출력하는 프로시저

create or replace procedure p_event(
v_saname in varchar2,
v_event out varchar2)
is
v_ename e_info.e_name%type; 
v_es_date e_info.e_s_date%type; 
begin
select e_name, e_s_date into v_ename, v_es_date
from (select e.e_name, ei.emp_name, e.e_s_date
from ct_info c, e_info e, emp_input ep, emp_info ei
where e.e_code=c.e_code
and c.ct_code=ep.ct_code
and ei.emp_code=ep.emp_code
and e.go_ck_code=204
and ei.emp_name=v_saname
union all
select e.e_name, ei.emp_name, e.e_s_date
from e_info e, emp_info ei
where e.emp_code=ei.emp_code
and e.go_ck_code=204
and ei.emp_name=v_saname
order by 3 desc)
where rownum=1;
v_event:=v_saname||' 사원이 가장 최근에 진행한 행사는 '||v_ename||' 이며, 해당 행사의 진행일자는 '||to_char(v_es_date, 'YYYY-MM-DD')||' 입니다.';
end;
/

2. 행사정보 테이블에서 총예산 정보 변경할 수 없으며, 변경 시도 시 오류 메시지를 반환하는 트리거

create or replace trigger t_ei_etb
after update of e_tot_budget on e_info
for each row
begin
raise_application_error(-20010, '총예산을 변경할 수 없습니다.');
end;
/

3. 이익률별 평균 수용인원과 특정 행사에 대한 수용인원을 비교하여 평균보다 초과일 경우 ‘초과’, 미만일 경우 ‘여유’를 출력하는 함수

create or replace function f_compare_capacity
(p_ecode in number)
return varchar2
is
v_pcode pr_g.pr_g_code%type;
v_avgcapa e_info.e_capa%type;
v_capa e_info.e_capa%type;
begin
select p.pr_g_code into v_pcode
from e_info e, pr_g p
where e.e_tot_budget>=mini_tot_b
and e.e_tot_budget<=maxi_tot_b
and e.e_code=p_ecode;
select floor(avg(e.e_capa)) into v_avgcapa
from e_info e, pr_g p
where e.e_tot_budget>=mini_tot_b
and e.e_tot_budget<=maxi_tot_b
and p.pr_g_code=v_pcode
group by p.pr_g_code;
select e_capa into v_capa
from e_info 
where e_code=p_ecode;
if v_capa<v_avgcapa then
return '여유';
else 
return '초과';
end if;
end;
/

4. 현재 보류 상태인 행사에 대해 총예산이 ‘8천만원’ 이하인 행사는 진행 취소로 변경하는 커서

accept budget prompt '총예산을 입력하세요 : '

declare
v_budget e_info.e_tot_budget%type;
v_gc_code_ing go_ck.go_ck_code%type;
v_gc_code_end go_ck.go_ck_code%type;
cursor gckcur is
select go_ck_code, e_tot_budget from e_info where go_ck_code=v_gc_code_ing;
begin
v_budget:=&budget;
select go_ck_code into v_gc_code_ing from go_ck where go_ckck='보류';
select go_ck_code into v_gc_code_end from go_ck where go_ckck='진행취소';
for s in gckcur loop
update e_info set go_ck_code=v_gc_code_end where e_tot_budget<=v_budget;
end loop;
end;
/

5. 관련기관 신규 입력 시, 기존 행사관련기관 테이블에 중복될 경우 오류 메시지를 반환하는 트리거

create or replace trigger t_ri
before insert on ro_info
for each row
declare
exist_cout number;
begin
select count(*) into exist_cout from ro_info
where ro_name=:new.ro_name or ro_tel=:new.ro_tel;
if exist_cout>0 then
raise_application_error(-20020, '이미 존재하는 기관정보 입니다.');
end if;
end;
/

6. 행사 관련기관별 기관 전화번호를 입력 시, 해당 기관의 위치를 출력하는 함수

create or replace function f_location
(ro_tel in varchar2)
return varchar2
is
v_lonum varchar2(10);
begin
if instr(ro_tel,'-') > 0 then
v_lonum:=substr(ro_tel, 1, instr(ro_tel, '-') -1);
else v_lonum:=substr(ro_tel, 1, 3);
end if;
case v_lonum 
when '02' then return '서울';
when '042' then return '대전';
when '053' then return '대구';
when '051' then return '부산';
when '064' then return '제주';
else return '그외 지역';
end case;
end;
/

7. 주최기관별 행사개최 횟수에 따른 기관 VIP 등급(A등급~D등급)을 출력하는 함수

create or replace function f_vip
(code number)
return varchar2
is
v_event_count number;
begin
select count(*) into v_event_count 
from e_info e, go_ck g
where e.go_ck_code=g.go_ck_code and go_ckck in ('진행중', '종료')
and ro_code=code;
return case when v_event_count>=0 and v_event_count<3 then 'D'
when v_event_count>=3 and v_event_count<5 then 'C'
when v_event_count>=5 and v_event_count<8 then 'B'
else 'A' end;
end;
/

8. 계약 성사 여부가 N으로 변경 시 계약파기일을 오늘로 변경, 행사 진행상태는 '취소'로 변경하는 트리거

create or replace trigger t_event
before update of ct_stt on ct_info
for each row
begin
if :new.ct_stt='N' and :old.ct_stt='Y' then
:new.ct_cc_date:=sysdate;
update e_info set go_ck_code=203
where e_code=:new.e_code;
end if;
end;
/

9. 행사시작 2주전, 계약을 파기한 계약건을 블랙 리스트로 등록해두는 트리거

create or replace trigger t_bl
before update of ct_stt on ct_info
for each row
declare
v_ckcode e_info.go_ck_code%type;
v_date e_info.e_s_date%type;
begin
select go_ck_code, e_s_date into v_ckcode, v_date from e_info where e_code=:new.e_code;
if :new.ct_stt='N' and :old.ct_stt='Y' 
and v_ckcode!=205
and sysdate<=v_date and sysdate>=(v_date-14) then
insert into bl(bl_code, ct_code, bl_up_d)
values(seq_bl.nextval, :new.ct_code, sysdate);	
end if;
end;
/

10. 거래처 정보 신규 입력 시, 기존 거래처 테이블에 중복될 경우 오류 메시지를 반환하는 트리거

create or replace trigger t_cm
before insert on cm
for each row
declare
v_count number;
begin
select count(*) into v_count from cm where cm_name=:new.cm_name;
if v_count=1 then
raise_application_error(-20030,'기존 거래처 테이블에 존재하는 거래처 정보입니다');
end if;
end;
/

11. 행사명 입력 시, 지출금액이 가장 높은 렌탈거래처 및 인력거래처 와 해당 금액 출력하는 프로시저

create or replace procedure p_cm_cost(
v_e_name in varchar2,
v_cm out varchar2)
is
v_max_lcm cm.cm_name%type;
v_max_lcost number;
v_max_rcm cm.cm_name%type;
v_max_rcost number;
begin
select cm_name, cm_tot into v_max_rcm, v_max_rcost
from (select ei.e_name, cm_name, sum(ri_tot_pay) cm_tot
from e_info ei, ct_info ci, rs_d rd, ri_pay rp, ri_info ri, cm cm
where ei.e_code=ci.e_code
and ci.ct_code=rd.ct_code
and rd.rs_d_code=rp.rs_d_code
and ri.ri_code=rd.ri_code
and cm.cm_code=ri.cm_code
and ei.e_name=v_e_name
and ri_pay_ck='Y'
group by ei.e_name, cm.cm_name
order by 3 desc)
where rownum=1;
select cm_name, cm_tot into v_max_lcm, v_max_lcost
from (select ei.e_name, cm_name, sum(l_tot_pay) cm_tot
from e_info ei, ct_info ci, ls_d ld, l_pay lp, ld_info li, cm cm
where ei.e_code=ci.e_code
and ci.ct_code=ld.ct_code
and ld.ls_d_code=lp.ls_d_code
and li.ld_code=ld.ld_code
and cm.cm_code=li.cm_code
and ei.e_name=v_e_name
and lp.l_pay_ck='Y'
group by ei.e_name, cm.cm_name
order by 3 desc)
where rownum=1;
v_cm:=v_e_name||' 에서 가장 총지출금액이 높은 렌탈거래처는 '||v_max_rcm ||', 인력거래처는 '||v_max_lcm||' 이며, 각각 총 지출금액은 렌탈은 '||v_max_rcost||' 원, 인력은 '||v_max_lcost|| ' 원 입니다.';
end;
/

12. ‘스피드렌탈’ 렌탈업체의 전체 물품 단가를 모두 10% 인상하는 커서

accept cname prompt '거래처명을 입력하세요 : '
accept cup prompt '인상률을 입력해주세요 : '

DECLARE
v_cc cm.cm_code%type;
v_up number(10,2);
v_c ri_info.ri_code%type;
v_ric ri_info.ri_cost%type;
v_nric ri_info.ri_cost%type;
cursor cmcur is select ri.ri_code, ri.ri_cost from ri_info ri where ri.cm_code=v_cc;
begin
v_up:=(&cup*0.01)+1;
select cm.cm_code into v_cc from cm cm where cm.cm_name='&cname';
for i in cmcur loop
v_c:=i.ri_code;
v_ric:=i.ri_cost;
v_nric:=v_up*v_ric;
update ri_info set ri_cost=v_nric where ri_code=v_c;
end loop;
dbms_output.put_line('거래처의 물품 단가가 모두 인상되었습니다.');
end;
/

13. 렌탈 견적 요청 수량이 변경될 시, 렌탈 견적 요청일자를 오늘로 변경하는 트리거

create or replace trigger t_update_rdate
before update of ri_qt on rs_d
for each row
begin
:new.rs_s_date:=sysdate;
end;
/

14. 인력 견적 요청 인원이 변경될 시, 인력 결제 테이블의 총 결제 금액을 변경하는 트리거

create or replace trigger t_update_lpay
after update of l_qt on ls_d 
for each row
declare
begin
update l_pay set l_tot_pay=:new.l_qt*(
select ld_avg_cost
from ld_info where ld_code=:old.ld_code)
where ls_d_code=:new.ls_d_code;
end;
/

15. 신규 결제 건 입력 시, 해당 행사의 총지출비용이 총수익을 초과할 경우, 오류 메시지를 반환하는 트리거

create or replace trigger t_pay
before insert on ri_pay
for each row
declare
v_ct_code rs_d.ct_code%type;
v_rsum ri_pay.ri_tot_pay%type;
v_lsum l_pay.l_tot_pay%type;
v_pay number;
v_e_code e_info.e_code%type;
v_budget e_info.e_tot_budget%type;
v_pr e_info.e_tot_budget%type;
begin
select ct_code into v_ct_code from rs_d where rs_d_code=:new.rs_d_code;
select sum(rp.ri_tot_pay) into v_rsum
from ct_info ci, e_info ei, rs_d rd, ri_pay rp
where ei.e_code=ci.e_code
and ci.ct_code=rd.ct_code
and rd.rs_d_code=rp.rs_d_code 
and rp.ri_pay_ck='N' 
and ci.ct_code=v_ct_code;
select sum(lp.l_tot_pay) into v_lsum
from ct_info ci, e_info ei, ls_d ld, l_pay lp
where ei.e_code=ci.e_code
and ci.ct_code=ld.ct_code
and ld.ls_d_code=lp.ls_d_code 
and lp.l_pay_ck='N' 
and ci.ct_code=v_ct_code;
v_pay:=v_rsum+v_lsum;
select e_code into v_e_code from ct_info where ct_code=v_ct_code;
select e_tot_budget into v_budget from e_info where e_code=v_e_code;
select ei.e_tot_budget*p.pr_g_code into v_pr
from e_info ei, pr_g p 
where ei.e_tot_budget>=p.mini_tot_b 
and ei.e_tot_budget<=p.maxi_tot_b
and ei.e_code=v_e_code;
if v_pr<=v_pay then
raise_application_error(-20040, '총 지출비용이 총수익을 초과합니다. 비용 견적을 재산출하세요.');
end if;
end;
/

16. 결제년월 입력 시, 렌탈 물품 및 인력 물품의 총 지출 비용을 출력하는 프로시저

create or replace procedure p_avgpay(
v_paydate in varchar2,
v_avgpay out varchar2)
is
v_ri ri_pay.ri_tot_pay%type;
v_l l_pay.l_tot_pay%type;
begin
select sum(ri_tot_pay) into v_ri
from ri_pay 
where to_char(ri_pay_date, 'yyyy/mm')=v_paydate and ri_pay_ck='Y'
group by to_char(ri_pay_date, 'yyyy/mm');
select sum(l_tot_pay) into v_l
from l_pay 
where to_char(l_pay_date, 'yyyy/mm')=v_paydate and l_pay_ck='Y'
group by to_char(l_pay_date, 'yyyy/mm');
v_avgpay:=v_paydate||' 에서 렌탈물품 총 지출금액은 '||v_ri||' 원 이며, 인력항목 총 지출금액은 '||v_l||' 원 입니다.';
end;
/

17. 행사명 입력 시, 총수익 대비 총 지출비용의 비율을 출력하는 함수

create or replace procedure p_cm_cost(
v_e_name in varchar2,
v_cm out varchar2)
is
v_max_lcm cm.cm_name%type;
v_max_lcost number;
v_max_rcm cm.cm_name%type;
v_max_rcost number;
begin
select cm_name, cm_tot into v_max_rcm, v_max_rcost
from (select ei.e_name, cm_name, sum(ri_tot_pay) cm_tot
from e_info ei, ct_info ci, rs_d rd, ri_pay rp, ri_info ri, cm cm
where ei.e_code=ci.e_code
and ci.ct_code=rd.ct_code
and rd.rs_d_code=rp.rs_d_code
and ri.ri_code=rd.ri_code
and cm.cm_code=ri.cm_code
and ei.e_name=v_e_name
and ri_pay_ck='Y'
group by ei.e_name, cm.cm_name
order by 3 desc)
where rownum=1;
select cm_name, cm_tot into v_max_lcm, v_max_lcost
from (select ei.e_name, cm_name, sum(l_tot_pay) cm_tot
from e_info ei, ct_info ci, ls_d ld, l_pay lp, ld_info li, cm cm
where ei.e_code=ci.e_code
and ci.ct_code=ld.ct_code
and ld.ls_d_code=lp.ls_d_code
and li.ld_code=ld.ld_code
and cm.cm_code=li.cm_code
and ei.e_name=v_e_name
and lp.l_pay_ck='Y'
group by ei.e_name, cm.cm_name
order by 3 desc)
where rownum=1;
v_cm:=v_e_name||' 에서 가장 총지출금액이 높은 렌탈거래처는 '||v_max_rcm ||', 인력거래처는 '||v_max_lcm||' 이며, 각각 총 지출금액은 렌탈은 '||v_max_rcost||' 원, 인력은 '||v_max_lcost|| ' 원 입니다.';
end;
/