Oracle DBA SQL 230627

1. **데이터베이스 관리자(dba)**
2. 데이터 엔지니어 (데이터 이행 및 전처리)
3. db 엔지니어

통신사는 무조건 sk, kt, lg <--- 제약(contraint) 을 이용해서 데이터를 입력할 때부터 강제화
↓
dba

▣ 복습

1. 기본 select 문 :

   select job, min(sal)             -> 컬럼명
     from emp                           -> 테이블명
     where job != 'SALESMAN'  -> 검색조건
     group by job                      -> 그룹핑할 컬럼
     order by min(sal) desc;       -> 정렬할 컬럼

2. 함수 :

   1. single row function : 문자,숫자,날짜, 변환, 일반
   2. multiple  row function : max, min, **avg**, sum, count
   
   **avg :** **(왜 강조? -> 평균계산이 니깐 null 값에 각별히 주의 해야함)**

▣ 038 평균값 출력하기(AVG)

예제. 사원 테이블에서 평균월급을 출력하시오 !

select  avg(sal)
  from  emp;

2073.214285714285714285714285714285714286

문제168. 위의 결과를 다시 출력하는데 소수점 이하가 안나오게
반올림하시오 !

select  round( avg(sal), 0 )
  from  emp;

문제169. 우리반 테이블에서 평균나이를 출력하시오!

select  round( avg( age ) )
  from  emp17;

문제170. 직업, 직업별 평균월급을 출력하시오 !

select    job,  round( avg(sal) )
  from   emp
  group   by   job;

문제171. 위의 결과를 다시 출력하는데 직업별 평균월급이 높은것부터
출력하시오 !

select job, round( avg(sal) ) as 평균월급
from emp
group by job
order by 평균월급 desc;

※ order by 절은 코딩도 맨 마지막이고 실행도 맨 마지막에 실행됩니다.
order by 절에서는 컬럼별칭, 컬럼순서인 숫자를 사용할 수 있습니다.

문제172. 문제171번을 다시 수행하는데 직업이 SALESMAN 은 제외하고
출력하시오 !

select job, round( avg(sal) ) as 평균월급
from emp
where job != 'SALESMAN'
group by job
order by 평균월급 desc;

코딩순서: select --> from --> where --> group by --> order by
실행순서: from --> where ---> group by --> select ---> order by

(SQLP시험)

문제173. 이름과 커미션을 출력하시오 !

select ename, comm
from emp;

문제174. 커미션의 평균값을 출력하시오 !

select avg(comm)
from emp;

※ 그룹함수는 null 값을 무시합니다.

문제175. 위의 결과를 다시 출력하는데 전체 사원수로 나눠져서
평균 커미션이 계산되게 하시오 !

select avg ( nvl( comm, 0 ) )
from emp;

select avg( comm)
from emp;

※ (중요) 평균값을 출력할때는 null 처리를 어떻게 할지 생각하면서
SQL을 작성해야합니다.

문제176. 커미션의 결측치가 몇개가 있는지 확인하시오 !
( 커미션의 NULL 값이 몇개가 있는지 조회하시오 ! )

select count(*)
from emp

where comm is null;

설명: count 를 사용할때는 * (모든 컬럼) 을 써야 null 값을 무시하지 않고 데이터를 잘 가져올 수 있습니다. (ocp 시험용)

select count(comm)
from emp ----> 결과로 0 이 출력됩니다.
where comm is null ;

※ 평균값을 구할 때는 null 값을 어떻게 처리해야할지 먼저 생각하고
평균값을 구하세요 ! 왜냐하면 그룹함수는 null 값을 무시하기
때문입니다.

문제177. 부서번호, 부서번호별 평균월급을 출력하시오 !

( 부서번호별 평균월급을 출력할때 소수점 이하는 안나오게 반올림하세요 )

1. 먼저 월급에 null 값이 있는지 확인을 합니다.

   select count(*)
   from emp
   where sal is null;

2. 부서번호, 부서번호별 평균월급을 구합니다.

   select deptno, round( avg(sal) )
   from emp
   group by deptno ;

▣ 039 토탈값 출력하기(SUM)

합계구하는 database 의 그룹함수는 sum 입니다.

예: 사원 테이블의 월급의 토탈값을 출력하시오 !

select sum(sal)
from emp;

문제178. 직업, 직업별 토탈월급을 출력하시오 !

select job, sum(sal)
from emp
group by job;

문제179. 위의 결과를 다시 출력하는데 직업을 ABCD 순서데로
정렬해서 출력하시오 !

select job, sum(sal)
from emp
group by job
order by job asc ;

문제180. 위의 결과를 다시 출력하는데 직업이 SALESMAN 은
제외하고 출력하시오 !

select job, sum(sal)
from emp
where job != 'SALESMAN'
group by job
order by job asc ;

문제181. 문제 180번 결과를 다시 출력하는데 직업별 토탈월급이
5000 이상인것만 출력하시오 !

select job, sum(sal)
from emp
where job != 'SALESMAN' and sum(sal) >= 5000
group by job
order by job asc ;

3행에 오류:
ORA-00934: 그룹 함수는 허가되지 않습니다

※ 오답노트 : 그룹함수로 검색조건을 사용할때는 where 절에
쓸 수 없습니다. having 절에 사용해야 합니다.

다음과 같이 작성해야합니다.

select job, sum(sal) ---- 보고 싶은 컬럼명
from emp ---- 테이블명
where job !='SALESMAN' ---- 검색조건 (그룹함수를 사용하지 않은)
group by job ---- 그룹핑할 컬럼
having sum(sal) >= 5000 ---- 그룹함수로 검색조건을 줄때 사용
order by job asc ; ---- 정렬할 컬럼

아래의 SQL이 실행이 되는지 확인해보세요 !

select job, sum(sal)
from emp
group by job
having sum(sal) >= 5000 and job !='SALESMAN'
order by job asc;

※ 위와같이 그룹함수 검색조건이 아닌 일반 검색조건인
job !='SALESMAN' 은 having절에 사용하면 안되고 where 절에
사용해야 합니다. having 절은 오로지 그룹함수로 검색조건을
줄 때만 사용해야 합니다. (중요) 만약 having 절에 일반 검색조건을
쓰면 에러도 없고 실행도 잘되어서 문제가 없어 보이지만
성능이 느려집니다. 인덱스(index) 를 사용하지 못하게 됩니다.

※ select 문의 6가지절

**explain    plan   for**
select   job, sum(sal)
  from  emp
  where  job != 'SALESMAN'
  group  by  job
  having  sum(sal)  >= 5000
  order  by   job  asc ;

**select  * from  table(dbms_xplan.display);**

문제182. (복습문제) 이름, 입사일, 입사한 년도(4자리) 를 출력하시오!

select ename, hiredate, to_char(hiredate, 'RRRR')
from emp;

문제183. 입사한 년도(4자리), 입사한 년도별 토탈월급을 출력하는데
입사한 년도를 ascending 하게 출력하시오 !

select to_char( hiredate, 'RRRR'), sum(sal)
from emp
group by to_char(hiredate,'RRRR')
order by 1 asc;

문제184. 문제183번을 다시 수행하는데 입사한 년도가 1981, 1982, 1983
년만 출력하시오 !

select to_char( hiredate, 'RRRR'), sum(sal)
from emp
where to_char(hiredate,'RRRR') in ( '1981', '1982', '1983' )
group by to_char(hiredate,'RRRR')
order by 1 asc;

문제185. 문제184번을 다시 수행하는데 입사한 년도별 토탈월급이
4000 이상인것만 출력하시오 !

select to_char( hiredate, 'RRRR'), sum(sal)
from emp
where to_char(hiredate,'RRRR') in ( '1981', '1982', '1983' )
group by to_char(hiredate,'RRRR')
having sum(sal) >= 4000
order by 1 asc;

문제186. 성씨 , 성씨별 평균 나이를 출력하시오 !

select substr( ename, 1, 1 ), round( avg( age ) )
from emp17
group by substr( ename, 1, 1 ) ;

문제187. 위의 결과를 다시 출력하는데 성씨가 김씨, 이씨, 최씨는
제외하고 출력하시오 !

select substr( ename, 1, 1 ), round( avg( age ) )
from emp17
where substr( ename, 1, 1 ) not in ('김', '이', '최')
group by substr( ename, 1, 1 ) ;

※ 기타 비교 연산자   4가지
not 을 사용해서 검색

1.  between  ..  and                   not  between  ..  and
2.  like                               not   like
3.  is null                            is  not  null
4.  in                                 not   in

문제188. (점심시간 문제) 문제187번을 다시 수행하는데 성씨별 평균나이
가 29 이상만 출력되게하고 성씨가 ㄱ ㄴ ㄷ ㄹ 순으로
출력되게하시오 !

select   substr(  ename,  1,  1 ),  round( avg( age ) )
  from   emp17
  where   substr( ename, 1, 1 )  not   in   ('김', '이', '최')
  group  by  substr( ename, 1, 1 )
  having round( avg( age ) ) >= 29
  order by 1;

▣ 040 건수 출력하기(COUNT)

특정행(row) 의 건수를 출력하는 그룹함수

예: 사원 테이블의 전체 건수가 어떻게 되는가 ?

select count(*)
from emp;

※ (ocp 시험 문제) 다음중 널값의 여부와 관계없이 결과가 잘 카운트
되는 SQL은 무엇인가 ? → null 값은 카운트가 되지 않는다!

1. select count(comm) from emp ;
2. select count(mgr) from emp;
3. select count(*) from emp;

답: 3번

문제189. 직업이 SALESMAN 인 사원들의 인원수를 출력하시오 !

select count(*)
from emp
where job='SALESMAN';

문제190. 직업, 직업별 인원수를 출력하시오 !

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

문제191. 위의 결과를 다시 출력하는데 직업별 인원수가 높은 것부터
출력하시오!

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

문제192. 문제191번을 다시 출력하는데 직업별 인원수가 2명이상인것만
출력하시오 !

select job, count()
from emp
group by job
having count(*) >= 2
order by 2 desc;

문제193. 문제192번을 다시 수행하는데 출력되는 결과에서 직업이
SALESMAN 은 제외하시오 !

select job, count()
from emp
where job != 'SALESMAN'
group by job
having count() >= 2
order by 2 desc;

문제194. (OCP 시험문제) emp 테이블에 직업의 종류가 몇개가 있는지
출력하시오 !

select count( distinct job )
from emp;

문제195. 어제 만들었던 서울시 소상공인 데이터인 market_2022 테이블
의 전체 건수를 출력하시오 !

select count(*)
from market_2022; -- 312811

문제196. 서울 강남구에 스타벅스 매장이 몇개가 있는지 출력하시오 !

select count(*)
from market_2022
where 상호명 like '%스타벅스%' and 시군구명 ='강남구';

문제197. 상호명에 스타벅스가 포함되어져있는 데이터에서
시군구명, 시군구명별 건수를 출력하는데 시군구명별 건수가
높은것 부터 출력하시오 !

select 시군구명, count(*)
from market_2022
where 상호명 like '%스타벅스%'
group by 시군구명
order by 2 desc;

문제198. 위에서 출력되는 결과 중에서 맨 첫번째 행 하나만 출력하시오!

select 시군구명, count(*)
from market_2022
where 상호명 like '%스타벅스%'
group by 시군구명
order by 2 desc fetch first 1 rows only;

※ order by 컬럼 desc/asc fetch first 숫자 rows only ;

문제199. 상호명에 스타벅스를 포함하고 있는 상권업종 소분류명이
무엇인지 조회하시오 !

select *
from market_2022
where 상호명 like '%스타벅스%';

목표: 2022년 기준으로 커피 프랜차이즈 가맹점이 가장 많은 브랜드가
어디인지  랭킹 5위까지 출력하시오 ! ( 데이터 엔지니어 )

문제200. market_2022 테이블에서 상권업종중분류명이 '커피점/카페' 인 상호명, 상호명별 건수를 출력하는데 상호명별 건수가
높은것부터 출력하시오 !

select 상호명, count(*)
from market_2022
where 상권업종중분류명 ='커피점/카페'
group by 상호명
order by 2 desc;

문제201. 위의 결과를 다시 출력하는데 상호명이 '카페' 는 제외하고
출력하시오

select 상호명, count(*)
from market_2022
where 상권업종중분류명 ='커피점/카페' and 상호명 !='카페'
group by 상호명
order by 2 desc;

문제202. 위의 SQL을 수정하는데 다음과 같이 수정하시오 !

select case when 상호명 like '%스타벅스%' then '스타벅스'
when 상호명 like '%이디야커피' then '이디야커피'
else 상호명 end as 상호명 , count(*)
from market_2022
where 상권업종중분류명 ='커피점/카페' and 상호명 !='카페'
group by case when 상호명 like '%스타벅스%' then '스타벅스'
when 상호명 like '%이디야커피' then '이디야커피'
else 상호명 end
order by 2 desc;

문제203. 위의 SQL에 CASE 문에 메가엠지씨커피와 투썸플레이스를
추가해서 GROUP BY 하시오 !

select case when 상호명 like '%스타벅스%' then '스타벅스'
when 상호명 like '%이디야커피%' then '이디야커피'
when 상호명 like '%메가엠지씨커피%' then '메가엠지씨커피'
when 상호명 like '%투썸플레이스%' then '투썸플레이스'
else 상호명 end as 상호명 , count(*)
from market_2022
where 상권업종중분류명 ='커피점/카페' and 상호명 !='카페'
group by case when 상호명 like '%스타벅스%' then '스타벅스'
when 상호명 like '%이디야커피%' then '이디야커피'
when 상호명 like '%메가엠지씨커피%' then '메가엠지씨커피'
when 상호명 like '%투썸플레이스%' then '투썸플레이스'
else 상호명 end
order by 2 desc;

설명: 2022 년도에는 스타벅스, 이디야, 메가 커피, 투썸플이스 순으로
매장수 많습니다.

문제204. 위의 SQL을 그대로 market_2017 에서 검색하시오 !

▣ 041 데이터 분석 함수로 순위 출력하기 1(RANK)

• 회사의 데이터베이스 서버의 종류 2가지 ?

  1. OLTP(OnLine Transacation Processing) 서버 :

     현재 활발하게 주문하는 데이터

  2. DW (Data Warehouse) 서버 : 주문 이력 데이터 :

     데이터 분석 <--- 데이터 분석 함수 : 순위, 집계, ......

문제205. 이름, 월급, 월급에 대한 순위를 출력하시오 !

select ename, sal, rank() over ( order by sal desc ) as 순위
from emp;

문제206. 우리반 테이블에서 이름, 나이, 나이에 대한 순위를 출력하시오

select ename, age, rank() over ( order by age desc ) as 순위
from emp17;

문제207. 우리반 테이블에서 나이가 33은 순위가 몇위인가 ?

select rank(33) within group ( order by age desc ) as 순위
from emp17;

문제208. 사원 테이블에서 월급이 1250은 순위가 몇위인가 ?

select rank(1250) within group ( order by sal desc ) as 순위
from emp;

문제209. 직업, 이름, 월급, 순위를 출력하는데 순위가 직업별로 각각
월급이 높은 순서대로 순위를 부여하시오 !

select job, ename, sal, rank() over ( partition by job
order by sal desc ) as 순위
from emp;

문제210. 우리반 테이블에서 성별, 이름, 나이, 순위를 출력하는데
순위가 성별별로 각각 나이가 높은 순서대로 순위를 부여하시오

select gender, ename, age, rank() over ( partition by gender
order by age desc ) 순위
from emp17;

▣ 042 데이터 분석 함수로 순위 출력하기 2(DENSE_RANK)

같은 순위가 여러개 있어도 그 다음 순위로 바로 출력할 수 있게
해주는 데이터 분석 함수 입니다.

문제211. 부서번호, 이름, 입사일, 순위를 출력하는데 순위가
부서번호별로 각각 먼저 입사한 사원순으로 순위를 부여하시오 !
( dense_rank() 함수를 이용해서 수행하세요 )

select deptno, ename, hiredate,
dense_rank() over ( partition by deptno
order by hiredate asc ) 순위
from emp;

문제212. 우리반에서 통신사, 이름, 나이, 순위를 출력하는데
순위가 통신사별로 각각 나이가 높은순으로 순위를
부여하시오 !

SELECT SUBSTR(LOWER(telecom), 1, 2), ename, age,
DENSE_RANK() over (PARTITION BY SUBSTR(LOWER(telecom), 1, 2)
ORDER BY age DESC) AS 순위
FROM emp17;

문제213. 위의 결과에서 각 통신사별로 순위가 1등만 나오게하시오 !

※ from 절의 서브쿼리를 사용해야 합니다.

SELECT SUBSTR(LOWER(telecom), 1, 2), ename, age,
DENSE_RANK() over (PARTITION BY SUBSTR(LOWER(telecom), 1, 2)
ORDER BY age DESC) AS 순위
FROM emp17
where 순위 = 1;

위와같이 검색할 수 없습니다. 왜냐하면 순위라는 컬럼이 emp17 에
없기 때문입니다. 그리고 SQL의 실행순서가

FROM --> WHERE ---> SELECT 이기 때문입니다.

아래와 같이 from 절의 서브쿼리를 사용해서 괄호안에 sql이 먼저 수행
되게하면, 통신사, 순위라는 컬럼이 컬럼명으로 인식됩니다.

select 통신사, ename, age, 순위
from (

SELECT SUBSTR(LOWER(telecom), 1, 2) 통신사, ename, age,
DENSE_RANK() over (PARTITION BY SUBSTR(LOWER(telecom), 1, 2)
ORDER BY age DESC) AS 순위
FROM emp17
)

where 순위 = 1 ;

문제214. 문제203번의 SQL을 수행해서 결과가 잘 나오는지 확인하시오

select case when 상호명 like '%스타벅스%' then '스타벅스'
when 상호명 like '%이디야커피%' then '이디야커피'
when 상호명 like '%메가엠지씨커피%' then '메가엠지씨커피'
when 상호명 like '%투썸플레이스%' then '투썸플레이스'
else 상호명 end as 상호명 , count(*)
from market_2022
where 상권업종중분류명 ='커피점/카페' and 상호명 !='카페'
group by case when 상호명 like '%스타벅스%' then '스타벅스'
when 상호명 like '%이디야커피%' then '이디야커피'
when 상호명 like '%메가엠지씨커피%' then '메가엠지씨커피'
when 상호명 like '%투썸플레이스%' then '투썸플레이스'
else 상호명 end
order by 2 desc;

문제215. 위의 결과를 다시 출력하는데 상호명, 상호명별 건수,
순위를 출력하는데 순위가 상호명별 건수가 높은 순서데로
순위를 부여하시오 !

select 상호명, 건수, rank() over ( order by 건수 desc) 순위
from (
select case when 상호명 like '%스타벅스%' then '스타벅스'
when 상호명 like '%이디야커피%' then '이디야커피'
when 상호명 like '%메가엠지씨커피%' then '메가엠지씨커피'
when 상호명 like '%투썸플레이스%' then '투썸플레이스'
else 상호명 end as 상호명 , count(*)
from market_2022
where 상권업종중분류명 ='커피점/카페' and 상호명 !='카페'
group by case when 상호명 like '%스타벅스%' then '스타벅스'
when 상호명 like '%이디야커피%' then '이디야커피'
when 상호명 like '%메가엠지씨커피%' then '메가엠지씨커피'
when 상호명 like '%투썸플레이스%' then '투썸플레이스'
else 상호명 end
order by 2 desc ) ;

문제216. market_2022 테이블에서 상호명에 떡볶이가 포함된 상호명과
그 건수를 출력하는데 그 건수가 높은것 부터 출력하시오!

select 상호명, count(*)
from market_2022
where 상호명 like '%떡볶이%'
group by 상호명
order by 2 desc;

문제217. (오늘의 마지막 문제) 다음과 같이 제대로 결과가 출력될 수 있도록
SQL을 작성하시오 ! 문제203번을 참고하세요!!

SELECT 상호명, 건수, DENSE_RANK () OVER(ORDER BY 건수 DESC) AS 순위
  FROM (
    select CASE WHEN 상호명 LIKE '%신전떡볶이%' THEN '신전떡볶이'
                WHEN 상호명 LIKE '%죠스떡%' THEN '죠스떡볶이'
                WHEN 상호명 LIKE '%떡참%' THEN '떡볶이참잘하는집떡참'
                WHEN 상호명 LIKE '%두끼%' THEN '두끼떡볶이'
                WHEN 상호명 LIKE '%우리할매떡%' THEN '우리할매떡볶이'
                WHEN 상호명 LIKE '%동대문엽기떡%' THEN '엽기떡볶이'
                WHEN 상호명 LIKE '%응급실국물떡볶이%' THEN '응급실국물떡볶이'
                WHEN 상호명 LIKE '%걸작떡볶이%' THEN '걸작떡볶이'
                WHEN 상호명 LIKE '%떡군이네떡%' THEN '떡군이네떡볶이'
                WHEN 상호명 LIKE '%감탄떡볶이%' THEN '감탄떡볶이'
                ELSE 상호명 END AS 상호명, count(*) AS 건수 
     from market_2022
     where 상호명 like '%떡%' and 상호명 not like '%떡집%'
     group by CASE WHEN 상호명 LIKE '%신전떡볶이%' THEN '신전떡볶이'
                   WHEN 상호명 LIKE '%죠스떡%' THEN '죠스떡볶이'
                   WHEN 상호명 LIKE '%떡참%' THEN '떡볶이참잘하는집떡참'
                   WHEN 상호명 LIKE '%두끼%' THEN '두끼떡볶이'
                   WHEN 상호명 LIKE '%우리할매떡%' THEN '우리할매떡볶이'
                   WHEN 상호명 LIKE '%동대문엽기떡%' THEN '엽기떡볶이'
                   WHEN 상호명 LIKE '%응급실국물떡볶이%' THEN '응급실국물떡볶이'
                   WHEN 상호명 LIKE '%걸작떡볶이%' THEN '걸작떡볶이'
                   WHEN 상호명 LIKE '%떡군이네떡%' THEN '떡군이네떡볶이'
                   WHEN 상호명 LIKE '%감탄떡볶이%' THEN '감탄떡볶이'
                   ELSE 상호명 END
-- order by 2 desc
 );