like Amazon Web Services
- 한글자 '_'
select * from s_emp
where name like '박_'; // '박근수' 검색 불가, '박__'으로 해야 가능
- 여러글자 '*'
select * from s_emp
where name like '박*'; // '박근수' 검색가능
Note DUAL 은 가상 테이블
문자 출력
select chr(65) from dual; // 영어 'A'
select nchr(44032) from dual; // 한글 '가'
날짜 출력
SELECT SYSDATE FROM DUAL; // 오늘 날짜
말일 구하기
SELECT TO_DATE('19/05/01')-1 FROM DUAL; // 익월에서 하루 빼면
월 반올림
SELECT ROUND(SYSDATE,'MONTH') FROM DUAL;
(오늘 4/16일 때)
SELECT ADD_MONTHS(TRUNC(SYSDATE, 'MONTH'),1)-1 FROM DUAL;
TRUNC - 버림 4/1
ADD_MONTHS - 한달 더하기 5/1
-1 하면 - 4월 마지막날 4/30
결과 4/30
날짜-> 문자
SELECT TO_CHAR(SYSDATE, 'yyyy/MM/dd') FROM DUAL;
update시 select 먼저!!!
퀴즈 ) 박구곤이라는 사람의 mailid를 null로 변경하시오
--update s_emp set mailid = null
select * from s_emp;
where name='박구곤';
count(*) vs count(컬럼)시 - null값 고려
select count(*) from s_emp; --e_emp 테이블의 행의 개수 : 25
select count(mailid) from s_emp; --s_emp 테이블의 mailid컬럼의 값의 개수 : 24 // null 값은 카운팅 되지 않음
GROUP BY - 부서별로 그룹화
직책이 사원인 사람들의 평균연봉을 부서별로 구하고 연봉이 높은 순으로 정렬합니다!!!!!!!
select dept_id, AVG(salary) -- 1. 평균연봉을 구한다 from s_emp where title = '사원' -- 2. 사원인 사람들만 group by dept_id -- 3. 부서별로 구한다(dept_id) order by 2 desc; -- 4. 두번째 컬럼AVG(salary)로 내림차순 정렬!sql 할때 꼭 한두개 정도 평균 확인 해 볼 필요가 있다!
계산기 ㅎㅎㅎ 해보기 크으으으으
쿼리 결과랑 비교해서 값이 맞는지 확인!
Q. 각 부서별 평균 급여를 계산해서 보여주시오
select dept_id, trunc(AVG(salary),2) --1. 평균급여를 구한다. from s_emp group by dept_id --2. 부서별로 구한다 order by 2 desc ;Q. 각 부서별로 직책이 사원인 직원들의 평균 급여를 계산해서 보여주시오
select dept_id, avg(salary) from s_emp where title = '사원' group by dept_id order by 1;그룹화 한 경우에는 그룹함수와 group by 에 지정된 컬럼만 사용가능
select dept_id, avg(salary) from s_emp group by dept_id;Q. 각 지역 별(region_id)로 몇개의 부서가 있는지를 나타내시오
select region_id, count(name) from s_dept -- 1. 몇개부서인지count(name) group by region_id order by region_id; -- 2. 지역별 그룹화Q. 각 부서별(dept_id)로 평균 급여(avg(salary))를 구하되 평균 급여가 2000 이상(avg(salary)>=2000)인 부서만 나타내시오
select dept_id,avg(salary) from s_emp group by dept_id having avg(salary) >= 2000 -- 그룹함수 조건은 where 절이 아닌 having에 넣는다 ;직책을 두개 이상 가지고 있는 그룹 평균 연봉을 보여줌
select title, avg(salary), count(*) -- 직책 / 평균연봉 / 직책 수 를 from s_emp group by title -- 직책별로 having count(*) > 2; -- 직책의 개수가 2 초과 하면 출력Q. 각 직책별(title)로 급여의 총합(sum(salary))을 구하되 직책이 부장인 사람은 제외하시오,
-- 단, 급여 총합이 8000만원 이상인 직책만 나타내며, 급여 총합에 대한 오름차순으로 정렬하시오
select title, sum(salary) from s_emp--1. 급여의 총합 - (sum(salary)) where title not like '%부장' -- 부장인 사람은 제외 group by title--2. 직책별로 - (title) having sum(salary) >= 8000--3. 급여 총합이 8000만원 이상 order by 2 asc;--4. 오름차순Q. 각 부서별로 직책이 사원인 직원들에 대해서만 평균 급여를 구하시오
select dept_id, avg(salary) from s_emp--1. 평균급여 출력 where title='사원' --3. 직책이 사원 group by dept_id--2. 부서별 ;Q. 각 부서내에서(dept_id) 각 직책별(title)로 몇명의 인원(count(*))이 있는지 나타내시오
select title, count(*) from s_emp--1. 몇명있는지 출력 group by title--3. 직책별로 그룹화 order by title ;Q. 각 부서내(dept_id)에서 몇명(count(*))의 직원이 근무하는지를 나타내시오
select dept_id, count(*) from s_emp--1. 몇명있는지 출력 group by dept_id--2. 부서별로 ;Q. 각 부서별(dept_id)로 급여의 최소값(min(salary))과 최대값(max(salary))을 나타내시오
-- 단, 최소값과 최대값이 같은 부서((min(salary))=(max(salary))는 출력하지 마시오.
select dept_id, min(salary), max(salary) from s_emp --1. 급여의 최소값과 최대값 group by dept_id--2. 부서별로 --having min(salary) not like max(salary)--3. 부서별로 최소값과 최대값이 같으면 출력 안함 having min(salary) <> max(salary)--3. 부서별로 최소값과 최대값이 같으면 출력 안함 order by dept_id;GROUP BY & HAVING
※ 작성 순서(숫자: 실행순서)
5: SELECT 컬럼명, AS 별칭, 계산식, 함수식 - 원하는 컬럼 값만 가져옴
1: FROM 참조할 테이블명
2: WHERE 컬럼명 | 함수식 비교 연산자 비교값
3: GROUP BY 그룹을 묶을 컬럼명
4: HAVING 그룹 함수식 비교 연산자 비교값
5: ORDER BY 컬럼명 | 별칭 | 컬럼순법 정렬방식 [NULLS FIRSH | LAST];
[GROUP BY 컬럼명 | 함수식]
여러개의 값을 묶어서 하나로 처리할 목적으로 사용함
그룹으로 묶은 값에 대해서 SELECT절에서 그룹 함수를 사용함
SELECT DEPT_CODE, SUM(SALARY)
FROM EMPLOYEE; >> 에러 발생
그룹 함수는 단 한개의 결과 값만 산출하므로 그룹이 여러개일 경우 오류 발생
- 여러개의 결과 값을 산출하기 위해 그룹 함수가 적용된 그룹의 기준을 GROUP BY절에 기술하여 사용
SELECT DEPT_CODE, SUM(SALARY) FROM EMPLOYEE GROUP BY DEPT_CODE;부서코드, 그룹별 급여 합계, 그룹별 급여 평균(정수처리), 인원 수 조회 후 부서코드 순으로 정렬
SELECT DEPT_CODE 부서코드, SUM(SALARY) 합계, FLOOR(AVG(SALARY)) 평균, COUNT(*) 인원 수
GROUP BY DEPT_CODE >> 그룹처리
ORDER BY DEPT_CODE; >> 부서코드 순으로 정렬
- 부서코드와 부서별 보너스 받는 사원 수 조회 후 부서코드 순으로 정렬
SELECT DEPT_CODE 부서코드, COUNT(BONUS) 인원수 FROM EMPLOYEE GROUP BY DEPT_CODE ORDER BY DEPT_CODE;
- 직원 테이블에서 직급 코드별, 보너스를 받는 사원의 수를 조회하여 직급 코드순 오름차순 정렬
단, 보너스 받는 사원이 해당 직급코드에 없다면 조회하지 X
SELECT JOB_CODE 부서코드, COUNT(BONUS) 인원수 FROM EMPLOYEE WHERE BONUS IS NOT NULL >> 사원이 0인 직급 코드 삭제 GROUP BY JOB_CODE ORDER BY JOB_CODE; >> 원하는 조건을 충족하는 ROW를 WHERE절에서 선행적으로 거름- 성별과 성별별 급여 평균(정수 처리), 급여 합계, 인원 수 조회 후 인원 수 내림차순 정렬
SELECT DECODE(SUBSTR(EMP_NO, 8, 1), 1, '남', 2, '여') 성별, FLOOR(AVG(SALARY) 평균, SUM(SALARY) 합계, COUNT(*) 인원수 FROM EMPLOYEE GROUP BY DECODE(SUBSTR(EMP_NO, 8, 1), 1, '남', 2, '여') ORDER BY COUNT(*) DESC; >> 함수식으로 GROUP BY 가능 * DECODE: 비교하고자 하는 값 또는 컬럼이 조건와 같으면 결과 값 반환 * FLOOR: 내림처리
- 부서별로 같은 직급인 사원의 급여 합계를 조회하고 부서 코드 순으로 정렬
SELECT DEPT_CODE, JOB_CODE, SUM(SALARY) FROM EMPLOYEE GROUP BY DEPT_CODE, JOB_CODE ORDER BY 1; >> 여러 컬럼으로 GROUP BY 가능
[HAVING]
HAVING절: 그룹 함수로 구해올 그룹에 대해 조건을 설정할 때 사용
HAVING 컬럼명 | 함수식 비교 연산자 비교값
- 급여 3,000,000 이상인 직원들의 부서 그룹별 급여 평균 조회(부서 코드 순 정렬)
- 부서 그룹별 급여 평균이 3,000,000 이상인 그룹 조회(부서 코드순 정렬)
;
SELECT DEPT_CODE, FLOOR(AVG(SALARY)) 평균 FROM EMPLOYEE HAVING FLOOR(AVG(SALARY)) >= 3000000 GROUP BY DEPT_CODE ORDER BY 1
- 부서별 그룹의 급여 합계가 9백만원을 초과하는 부서코드와 급여 합계 조회(부서 코드순 정렬)
SELECT DEPT_CODE, SUM(SALARY) FROM EMPLOYEE HAVING SUM(SALARY) > 9000000 GROUP BY DEPT_CODE ORDER BY 1;
※참고
- 급여 합계가 가장 많은 부서의 부서 코드와 부서 합계를 구하세요. (서브쿼리 이용)
SELECT DEPT_CODE, SUM(SALARY) FROM EMPLOYEE GROUP BY DEPT_CODE HAVING SUM(SALARY) = (SELECT MAX(SUM(SALARY)) FROM EMPLOYEE GROUP BY DEPT_CODE); >> 메인 쿼리를 보조하는 서브쿼리 구문
[집계함수 (ROLLUP, CUBE]
그룹별 산출한 결과 값의 집계를 계산하는 함수
- 직급 코드별 급여 합계와 마지막 행에 전체 급여 총합 조회 (직급 코드순 정렬)
SELECT JOB_CODE, SUM(SALARY) FROM EMPLOYEE GROUP BY JOB_CODE ORDER BY 1; >> 각 직급별 급여의 합만 출력 SELECT JOB_CODE, SUM(SALARY) FROM EMPLOYEE GROUP BY ROLLUP(JOB_CODE) ORDER BY 1; >> 마지막 행에 직급별 급여 합을 모두 더한 총합이 출력됨 SELECT JOB_CODE, SUM(SALARY) FROM EMPLOYEE GROUP BY CUBE(JOB_CODE) ORDER BY 1;ROLLUP과 CUBE를 사용했을 때 같은 결과가 출력된다.
ROLLUP과 CUBE의 차이점은?
ROLLUP 함수
- 그룹별로 중간 집계 처리를 하는 함수
- GROUP BY절에서만 사용하는 함수
- 그룹별로 묶여진 값에 대한 중간 집계와 총 집계를 구할 때 사용
- 그룹 함수로 계산된 결과 값들에 대한 총 집계가 자동으로 추가
* 인자로 전달받은 그룹 중 '가장 먼저' 지정한 그룹별 합계와 총 합계를 구하는 함수
- 부서 코드마다 직급 코드별 급여의 합, 부서별 급여의 합, 총합 조회(부서 코드순 정렬)
SELECT DEPT_CODE, JOB_CODE, SUM(SALARY) FROM EMPLOYEE GROUP BY ROLLUP(DEPT_CODE, JOB_CODE) << 첫 번째 행에 대한 그룹별 합계 ORDER BY 1;
CUBE 함수
* 그룹으로 지정된 모든 그룹에 대한 집계와 총 합계를 구하는 함수
SELECT DEPT_CODE, JOB_CODE, SUM(SALARY) FROM EMPLOYEE GROUP CUBE (DEPT_CODE, JOB_CODE) << 모든 행에 대한 그룹별 합계가 출력됨 ORDER BY 1;GROUPING 함수
- ROLLUP이나 CUBE에 의한 산출물이 인자로 전달 받은 컬럼 집합의 산출물이면 0을 반환하고 아니면 1을 반환하는 함수
SELECT DEPT_CODE, JOB_CODE, SUM(SALARY), GROUPING(DEPT_CODE) "부서별그룹묶인상태", GROUPING(JOB_CODE) "직급별그룹묶인상태" FROM EMPLOYEE GROUP BY ROLLUP(DEPT_CODE, JOB_CODE) ORDER BY DEPT_CODE; SELECT DEPT_CODE, JOB_CODE, SUM(SALARY), CASE WHEN GROUPING(DEPT_CODE) = 0 AND GROUPING(JOB_CODE) = 1 THEN '부서별합계' WHEN GROUPING(DEPT_CODE) = 1 AND GROUPING(JOB_CODE) = 0 THEN '직급별합계' WHEN GROUPING(DEPT_CODE) = 0 AND GROUPING(JOB_CODE) = 0 THEN '그룹별합계' ELSE '총합계' END 구분 FROM EMPLOYEE GROUP BY CUBE(DEPT_CODE, JOB_CODE) ORDER BY 1;
SET OPERATION
- 여러가지 조건이 있을 때 그에 해당하는 여러개의 결과 값을 결합시키고 싶을 때 사용
UNION은 OR 같은 개념(합집합)
INTERSECT는 AND 같은 개념(교집합)
UNION ALL은 OR 결과 값에서 AND 결과값이 더해진 것 (합집함 + 교집함)
MINUS는 차집함
UNION
- 여러개의 쿼리 결과를 하나로 합치는 연산자. 중복된 영역을 제외하여 하나로 합친다.
SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY FROM EMPLOYEE WHERE DEPT_CODE = 'D5' UNION SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY FROM EMPLOYEE WHERE SALARY > 3000000; >> 위 쿼리문의 결과는 아래처럼 WHERE절에 OR을 쓴 것과 같다. SELECT EMP_ID, EMP_NAME, DPET_CODE, SALARY FROM EMPLOYEE WHERE DEPT_CODE = 'D5' OR SALARY > 3000000;INTERSECT
- 여러개의 SELECT한 결과에서 공통 부분만 결과로 추출(교집합)
SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY FROM EMPLOYEE WHERE DEPT_CODE = 'D5' INTERSECT SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY FROM EMPLOYEE WHERE SALARY > 3000000; >> 위 쿼리문의 결과는 아래처럼 WHERE절에 AND를 쓴 것과 같다. SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY FROM EMPLOYEE WHERE DEPT_CODE = 'D5' AND SALARY > 3000000;
UNION ALL
- 여러개의 쿼리 결과를 하나로 합치는 연산자
- UINON과의 차이점은 중복 영역을 모두 포함시키는 것(합집함 + 교집함)
SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY FROM EMPLOYEE WHERE DEPT_CODE = 'D5' UNION ALL SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY FROM EMPLOYEE WHERE SALARY > 3000000;
MINUS
- 선행 SELECT 결과에서 다음 SELECT 결과와 겹치는 부분을 제외한 나머지 부분만 추출(차집합)
- 부서 코드 D5 중 급여가 300만을 초과하는 직원 제외
SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY FROM EMPLOYEE WHERE DEPT_CODE = 'D5' MINUS SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY FROM EMPLOYEE WHERE SALARY > 3000000; >> 위 쿼리문의 결과는 다음과 같다. >> 부서코드 D5 중 급여가 300만 이하인 직원 조회 SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY FROM EMPLOYEE WHERE DPET_CODE = 'D5' AND SALARY <= 3000000;
GROUPING SETS
- 그룹별로 처리된 여러개 SELECT문을 하나로 합칠 때 사용
- SET OPERATION를 사용한 결과와 동일
SELECT DEPT_CODE, JOB_CODE, MANAGER_ID, COUNT(*), FLOOR(AVG(SALARY)) FROM EMPLOYEE GROUP BY GROUPING SETS((DEPT_CODE, MANAGER_ID), << 1부터 14행까지 (JOB_CODE, MANAGER)ID)) << 15행부터 ORDER BY 1;