데이터베이스 SQL 오라클 group by 절, having 절, view

-- group by 절

-- 특정 컬럼이나 값을 기준으로 해당 레코드를 묶어서 자료를 관리할 때 사용

-- 보통은 특정 컬럼을 기준으로 집계를 구하는데 많이 사용이 됨

-- 보통 그룹함수와 함께 사용하면 효과적으로 활용이 가능함.

select deptno from emp order by deptno;



--  emp  테이블에서 부서별로 각 부서의 인원을 확인하고 싶은 경우
select deptno, count(*) from emp group by deptno order by deptno asc;

-- emp 테이블에서 각 부서별로 급여의 합계액을 화면에 보여주세요.
select deptno, sum(sal) 급여합계  from emp group by deptno order by sum(sal) desc;

-- [문제] emp 테이블에서 부서별로 그룹을 지어서 부서의 급여 합계와 부서별 인원 수, 부서별 평균 급여, 
--       부서별 최대 급여, 부서별 최소 급여를 구하여 화면에 출력 .
select  deptno, sum(sal), count(*), avg(sal),  max(sal), min(sal) 
    from emp group by deptno order by sum(sal) desc;
    
 -----------------------------------------------------------------------------------   
 -----------------------------------------------------------------------------------
 
-- having 절
 
-- group by 절 다음에 오는 조건절로
-- group by 절의 결과에 조건을 주어서 제한할 때 사용함.

-- group by 절에는 where(조건절)이 올 수 없다.

-- products 테이블에서 카테고리 별로 상품의 갯수를 화면에 보여주세요.
-- 조건) 카테고리 별로 상품의 갯수가 2개 이상인 것만 화면에 보여주세요.
select category_fk, count(*) from products group by category_fk 
                                having count(*) >= 2 order by category_fk;
                                
 -----------------------------------------------------------------------------------   
 -----------------------------------------------------------------------------------     
                                
-- View(중요)    

-- 물리적인 테이블에 근거한 논리적인 가상의 테이블을 말함.
-- View는 실질적으로 데이터를 저장하고 있지 않음.

-- View를 만들려면 데이터베이스에 질의 시 실제 테이블에 접근하여 불러오게 됨.
-- 간단하게 말하면 필요한 내용들만 추출해서 사용하는 것.
-- 주로 데이터를 조회할 때 많이 사용됨.
-- View 는 테이블과 유사하며, 테이블처럼 사용이 가능함.
-- View 는 테이블에 저장하기 위한 물리적인 공간이 필요가 없음.
-- 테이블과 마찬가지로 insert, update, delete 명령이 가능. (실제 테이블에 적용이 됨. 주의)

-- View를 사용하는 이유
-- 1) 보안 관리를 위해 사용함 (아주 중요)
--    ==> 보안 등급에 맞추어 범위를 정해서 조회가 가능하도록 할 수 있음.
-- 2) 사용자의 편의성을 제공함.

--   형식) 
--        create view 이름 as 쿼리문;

--  인사부 view 
-- 컬럼에 sal(급여), comm(보너스) 컬럼은 제외
create view emp_insa
as 
select empno, ename, job, mgr, hiredate, deptno 
from emp;

-- 영업부 view 
-- 컬럼에 sal(급여)제외.
create view emp_sales
as select empno, ename, job, mgr, hiredate, deptno, comm
from emp;

-- 회계부
-- 전부
create view emp_accounting
as select empno, ename, job, mgr, hiredate, deptno, comm, sal
from emp;

create view emp_view
as select * from emp;

insert into emp_view 
    values(9000, 'ANGEL', 'SALESMAN', 7698, sysdate, 1500, 200, 30);
    
-- 읽기 전용 view를 만들면 insert, update, delete 쿼리가 안 됨.

-- 읽기 전용 view를 만드는 방법 : 쿼리문 맨 마지막에 with read only 문구 추가.

create view emp_view1
as select * from emp
with read only;

insert into emp_view1 
    values(9001, 'LOVE', 'SALESMAN', 7698, sysdate, 1200, 200, 30); -- 읽기전용에서 사용 불가
    
    -- create or replace view : 같은 이름의 view가 있는 경우에는 삭제하고 다시 view를 만들라는 의미
    
create or replace view emp_insa     
    as select empno, ename, job, mgr, hiredate, deptno 
                                        from emp with read only;  
  

-- 2) 사용자의 편의성을 제공함.
create or replace view emp_read(empno, ename, annual_salary)
    as select empno, ename, (sal * 12 + nvl2(comm, comm, 0)) 연봉 from emp with read only;

select * from emp_read;

-- 부서별로 부서별 급여 합계, 부서별 급여 평균을 구한 view를 만들어 화면에 보여주세요.
-- 주의사항) view를 만들 때 그룹함수 사용시에는 반드시 별칭을 설정해주어야 한다.

create or replace view emp_deptno
    as select deptno, sum(sal) "급여합계", avg(sal) "급여평균" from emp 
        group by deptno
            order by sum(sal) desc
                with read only; 

-- [문제1] emp 테이블을 이용하여 emp_dept20 이라는 view를 만들어 주세요.
-- 단, 부서번호가 20번 부서에 속한 사원들의 사번, 이름, 담당업무, 관리자, 부서번호만 화면에 보여주세요.
create view emp_dept20 
    as select empno, ename, job, mgr, deptno from emp where deptno = 20 
        order by empno desc with read only;
        
        select * from emp_dept20;
        
-- [문제2] emp 테이블에서 각 부서별 최대 급여와 최소급여를 보여주는 view를 만들되, sal_view 라는 이름으로 만들어주세요.
create or replace view sal_view
    as select deptno, max(sal) "최대급여" , min(sal)"최소급여" from emp
        group by deptno order by deptno with read only;

-- [문제3] 담당업무가 'SALESMAN'인 사원의 사번과 이름, 담당업무, 입사일, 부서번호를 컬럼으로 하는 view를 만들되
-- emp_sale 이라는 view를 만들어 화면에 보여주세요.
create or replace view emp_sale 
    as select empno, ename, job, hiredate, deptno from emp where job = 'SALESMAN' 
       with read only;
    
  -- view를 만들때 컬럼만 만들고 싶은경우 
  -- ==> 조건을 말이 안되늰 조건으로 작성하면 됨
  create or replace view emp_view2 as select * from emp where deptno = 1;