오라클 days08

insa테이블에서 여자인원수가 5명 이상인 부서만 출력

select 여자사원수 
from insa
where 여자사원만
group by 부서별로
having >=5; 

select *
from insa;
ㄱ.
select buseo, count(*)여자사원수 
from insa
where mod(substr(ssn,-7,1),2)=0
group by buseo
having count(*)>=5; 

ㄴ. 오류 
select *
from(select buseo, count(*)여자사원수 
from insa
where mod(substr(ssn,-7,1),2)=0
group by buseo )t
where count(*)>=5;

insa 테이블에서 급여(pay=basicpay+sudang)이 상위 15퍼에 해당되는 사원들 정보출력 
사원수*.015 =몇명인지 나옴 
select count(*) from insa; --총 60명 

select 60*0.15
from dual;     -- 0.15프로는 9명 


select *
from(
select buseo, name,basicpay+sudang pay
    ,rank() over(order by basicpay+sudang desc) pay_rank 
from insa
)
where pay_rank<=(select count(*) from insa)*0.15;

emp테이블에서 sal 전체사원의 등수, 부서내의 등수를 출력

--rank() 함수 
select deptno, ename,sal
,rank() over (order by sal desc)전체등수
,rank() over(partition by deptno order by sal desc) 부서내등수 
from emp
order by deptno ASC;


--상관쿼리함수 
select deptno, ename ,sal, (select count(*) from emp where sal>e.sal )+1 전교등수
,(select count(*) from emp where sal>e.sal and deptno=e.deptno)+1  반등수
from emp e
order by deptno asc, 반등수 asc;

-- 앞뒤에 있는 별만 제거-- 
select '*****admin****'
--    ,replace('****admin****' ,'*','')
    ,trim('*' from '*****admin****')
    ,trim(' ' from '      admin     ')
from dual;



to_char(숫자날짜,'fmt' ,[,nls param])
to_date(숫자,)
nls param==nls parameter(매개변수, 인자) 


--SESSION > CLIENT > SERVER
--각 사용자는 특정 session에서 server측 설정과 client의 환경변수를 무시하고 싶은 경우, ALTER SESSION 명령을 사용하여 현재 SESSION의 환경을 변경할 수 있다.

select ename, sal,to_char(hiredate, 'yy/mon/day','NLS_DATE_LANGUAGE=JAPANESE')
from emp;


select count(*)
,count(decode(deptno,10,'o'))
,count(decode(deptno,20,'o'))
,count(decode(deptno,30,'o'))
,count(decode(deptno,40,'o'))
from emp;


--문제점 : 40번이 없다. ->outer 조인 사용 

select deptno, count(*)
from emp
group by deptno
order by deptno asc;

--전체사원수랑 union all  
--ORA-00904: "DEPTNO": invalid identifier
--union 할때는 select 칼럼 갯수랑  자료형 맞아한다. 
--칼럼의 갯수를 맞추기 위해 0 을 넣었으며 , 이것은 전체 사원수 
select 0 deptno ,count(*)
from emp 
union all
select deptno, count(*)
from emp
group by deptno
order by deptno asc;

emp테이블에서 
20,40 번 부서는 제외시키고 
그외 부서의 사원수를 계산하고 
그 부서의 사원수가 4명 이상인 부서정보를 조회 

select d.deptno, d.dname, t.cnt
from (
    select deptno, count(*) cnt 
    from emp 
    where deptno not in(20,40) 
    group by deptno
)t join dept d on d.deptno=t.deptno
where cnt>=4;

--그룹화 집계 +조건 (having)
---ORA-00918: column ambiguously defined  모호한 컬럼이 선언되어있다. 

select d.deptno, dname, count(*)
from emp e join dept d on e.deptno=d.deptno
where d.deptno not in (20,40)
group by d.deptno, dname 
having count(*)>=4;

insa 테이블에서 각 부서별로 과장 대리 사원 등등 직급ㅂ뎔 사원수를 출력 쿼리 

select buseo, name, jikwi
from insa
where buseo='개발부'
order by jikwi;


--
select buseo, count(*) 부서사원수 
from insa
group by buseo;


select buseo,jikwi, count(*) 직급사원수 
from insa
group by buseo,jikwi
order by buseo asc, jikwi asc;

insa테이블에서 남자 사원들만 부서별로사원수를 출력 6명이상인 부서만 출력 

select buseo, count(*) 남자사원수 
from insa
where mod(substr(ssn,-7,1),2)=1
group by buseo
having count(*)>=6;

rollup과 cube
rollup은 그룹바이 절의 그룹 조건에 따라 전체행을 그룹화 하고 각 그룹에 대에 부분합을 구하는 연산자 
문제) insa테이블에서 남자사원수, 여자 사원수를 출력(조회 ) 

select count(*)
from insa
group by mod(substr(ssn,-7,1),2);

남자 31
여자 29 이렇게 출력되도록 하자 

select decode(mod(substr(ssn,-7,1),2),1,'남자','여자')gender, count(*)
from insa
group by mod(substr(ssn,-7,1),2);


남자 31
여자 29
총사원수 60

select decode(mod(substr(ssn,-7,1),2),1,'남자','여자')gender, count(*)
from insa
group by mod(substr(ssn,-7,1),2)
union all
select '합',count(*) from insa;

--roll up 사용하기 
select decode(mod(substr(ssn,-7,1),2),1,'남자',0,'여자','합')gender, count(*)
from insa
group by rollup(mod(substr(ssn,-7,1),2));


select decode(mod(substr(ssn,-7,1),2),1,'남자',0,'여자','합')gender, count(*)
from insa
group by cube(mod(substr(ssn,-7,1),2));


---각 부서별로 1차 그룹화, 각 직급별로 2차 그룹화 
--오류 
select buseo, jikwi, count(*)
from insa
group by buseo, jikwi
--order by buseo, jikwi;
union all
select buseo, '', count(*)
from insa
group by buseo
order by buseo asc;

--union 
--출력되는 칼럼명은 첫번재 select 절 
-- ORDER BY 절은 첫 번째와 두 번째 SELECT 문이 끝난 제일 후미에 넣어야 한다.

select buseo, jikwi, count(*)
from insa
group by buseo, jikwi
union all
select buseo, '합', count(*)
from insa
group by buseo
union all 
select '','',count(*) --전체합
from insa
--order by buseo asc;


select buseo, jikwi, count(*)
from insa
group by rollup(buseo,jikwi)
order by buseo, jikwi;

---- cube 
--  과장	8
--	대리	13
--	부장	7
--	사원	32 도 추가됨 
select buseo, jikwi, count(*)
from insa
group by cube(buseo,jikwi)
order by buseo, jikwi;

--ROLLUP은 ()안의 첫번째 값을 기준을 합계를 보여주는 것이고, 
--CUBE는 ()안의 모든 값을 기준으로 합계를 보여주는 것이다.

emp테이블에서 job별로 사원수 몇명 조회하는 쿼리 작성 

select job,count(*)
from emp
group by job;

--decode 함수 이용
select 
    count(decode(job,'clerk','o')) CLERK
    , count(decode(job,'salesman','o')) SALESMAN
    , count(decode(job,'president','o'))PRESIDENT
    , count(decode(job,'manager','o')) MANAGER
    , count(decode(job,'analyst','o')) ANALYST
from emp;

--pivot 
SELECT * FROM TABLE

PIVOT( 집계함수(넣을 데이터 컬럼)  FOR  컬럼1 IN(컬럼1 데이터:열로바꿀거))

  SELECT * 
  FROM (피벗 대상 쿼리문)
  PIVOT (그룹함수(집계컬럼) FOR 피벗컬럼 IN(피벗컬럼 값 AS 별칭...))

 select *
 from (select job from emp)
 pivot (count(job) for job in( 'clerk','salesman','president','manager','analyst'));


insa 테이블에서 성별로 사원수를 조회
group by 
decode 
pivot() 함수 

select count(*)
from (select mod(substr(ssn,-7,1),2)gender from insa)
group by gender;

select 
    count(decode(mod(substr(ssn,-7,1),2),1,'남자')) 남자
    ,count(decode(mod(substr(ssn,-7,1),2),0,'여자')) 여자
from insa; 

select *
from (select mod(substr(ssn,-7,1),2)gender from insa)
pivot(count(*) for gender in (1 as "남자", 0 as "여자"));