SQL - 3

SELECT * FROM nls_session_parameters;
SELECT * FROM nls_database_parameters;
SELECT sysdate FROM dual; -- 2023-03-30

--ALTER session SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
SELECT sysdate FROM dual; -- 2023-03-30 변경

 

SELECT TO_CHAR(sysdate, 'yyyy/mm/dd') FROM dual;
SELECT TO_DATE('2017 01 09', 'yyyy mm dd') FROM dual;

 

-- Q) 2005년에 입사한 사원 출력(이름, 입사일)
-- 단, 조건에 to_char 사용

 

SELECT first_name 이름, hire_date 입사일
FROM employees
WHERE TO_CHAR(hire_date, 'RR') = '05'; -- yy도 가능 yy는 1905년 안 따지고 무조건 2005만 따짐

SELECT TO_NUMBER('12345') FROM dual;

 

□ single-row function(단일행함수) vs multi-row function(다중행함수)

 

1) single-row function(단일행함수)

 

SELECT * 
FROM employees
WHERE LENGTH(first_name) <= 3;
-- 이 과정에서 각각의 행에 하나씩 적용되어 답을 구함

 

2) multi-row function(다중행함수) = group function(그룹함수)

 

excel = sum(a1:a10)
sum(), max(), min(), count(), avg() R언어 : mean()

 

-- Q) 사원들의 급여의 합을 구하시오.

 

SELECT SUM(salary) FROM employees;

 

-- Q) 사원들의 급여 합의 평균을 구하시오.

 

SELECT AVG(salary) FROM employees;

 

-- Q) 사원들의 급여의 최대치와 최소치를 구하시오.

 

SELECT MAX(salary) FROM employees;
SELECT MIN(salary) FROM employees;

 

-- Q) 사원수를 조회 출력하시오. 

 

SELECT COUNT(*) FROM employees; -- 107명 -- 가급적 *를 쓸 것 

SELECT COUNT(department_id) FROM employees; -- 106명 : 부서가 정해지지 않은 킴벌리씨가 누락된다.

 

-- Q) 커미션 퍼센트의 평균을 조회 출력하시오. 

 

SELECT AVG(commission_pct) FROM employees;
-- 여기는 해당되는 사람만 계산된다. 한번 비교해보자

-- 여기는 해당되는 사람만 계산된다. 한번 비교해보자

 

SELECT SUM(commission_pct) FROM employees; -- 7.8
SELECT COUNT(commission_pct) FROM employees; -- 35 커미션 있는 사람만 카운트

SELECT 7.8/35 FROM dual; -- 0.2228571428571428571428571428571428571429

 

-- Q) 
-- 사원들의 급여의 합, 최소, 최대 급여 출력, 급여평균, 직원수, 매니저있는 직원수, 직원부서 수 구하기

 

SELECT SUM(salary) 급여의합, MIN(salary) 최소급여, MAX(salary) 최대급여, 
ROUND(AVG(salary)) 급여평균, COUNT(*) 직원수, COUNT(manager_id) 매니저있는직원수, COUNT(DISTINCT department_id) 직원부서수
FROM employees;

-- 직원부서수는 중복방지 DISTINCT 사용

SELECT * FROM departments; -- 실제 department_id는 27개가 있지만 16개의 부서는 아예 사람이 없어서 COUNT되지가 않는다.

-- 실제 department_id는 27개가 있지만 16개의 부서는 아예 사람이 없어서 COUNT되지가 않는다.

 

■ GROUP BY - 끼리끼리

 

-- Q) 부서별 급여의 평균을 구하시오. (출력은 부서아이디, 급여평균)

 

SELECT department_id 부서아이디, ROUND(AVG(salary)) 급여평균 
FROM employees
GROUP BY department_id
ORDER BY 1;

 

* ORDER BY 4가지 (컬럼명, 순번, 계산식, alias) (GROUP BY는 안 됨!)

 

-- Q) 부서별 급여의 평균을 구하시오
-- (출력은 사원명, 부서아이이디, 급여평균)
-- select 문에서 그룹함수를 제외하고 남은 컬럼명이 GROUP BY 다음에 나오면 OK

 

SELECT first_name 사원명, department_id 부서아이디, ROUND(AVG(salary)) 급여평균
FROM employees
GROUP BY first_name, department_id;

-- 이렇게 하면 결과는 나오지만, 우리가 의도하는 결과는 아니다.

-- 애초에 맞지 않는..

 

-- Q) 부서별, 매니저 아이디별 평균급여를 구하시오
-- (부서 아이디, 매니저 아이디, 평균급여 출력)

 

SELECT department_id 부서아이디, manager_id 매니저아이디, ROUND(AVG(salary)) 평균급여
FROM employees
GROUP BY department_id, manager_id;

 

-- Q) 연봉이 5천만 달러 이하인 직원에 대하여
-- 부서별 최소급여를 구하시오

 

SELECT department_id 부서아이디, MIN(salary) 최소급여
FROM employees
WHERE salary*12 <= 50000000
GROUP BY department_id
ORDER BY 1;

 

-- Q) job_id의 첫 두 글자가 'IT'가 아닌 직원에 대하여
-- 부서별 최소급여를 구하시오.

 

SELECT department_id 부서아이디, MIN(salary) 최소급여
FROM employees
WHERE job_id NOT LIKE 'IT%'
GROUP BY department_id
ORDER BY 1;

 

-- 또 다른 방법

SELECT department_id 부서아이디, MIN(salary) 최소급여
FROM employees
WHERE SUBSTR(job_id, 1, 2) <> 'IT'
GROUP BY department_id
ORDER BY 1;

 

-- Q) 부서아이디가 30 이상인 직원에 대하여 부서별 최소급여를 구하시오.

 

SELECT department_id 부서아이디, MIN(salary) 최소급여
FROM employees
WHERE department_id >= 30
GROUP BY department_id
ORDER BY 1;

 

-- Q) 부서아이디가 30 이상인 직원에 대하여
-- 부서별 최소급여, 평균급여를 구하시오(부서아이디, 최소급여, 평균급여 출력)
-- 단, 부서별 평균급여가 10000 이하인 경우만 구한다.

 

SELECT department_id 부서아이디, MIN(salary) 최소급여, ROUND(AVG(salary)) 평균급여
FROM employees
WHERE department_id >= 30 AND ROUND(AVG(salary)) <= 10000
GROUP BY department_id
ORDER BY 1;

-- 오류 발생 왜?
조건에서
일반조건 salary > 1000 - where에 넣으면 된다.
그룹조건 avg(), sum() ==> where로 물어볼 수 없다 대신에 having을 사용한다.

 

-- 옳은 SQL문은

SELECT department_id 부서아이디, MIN(salary) 최소급여, ROUND(AVG(salary)) 평균급여
FROM employees
WHERE department_id >= 30
GROUP BY department_id 
HAVING ROUND(AVG(salary)) <= 10000
ORDER BY 1;

 

-- Q) 각 부서별 직원수 및 평균급여를 조회하되
-- 직원수가 20명 이상인 부서만 출력되도록 하시오.
-- (부서아이디, 직원수, 평균급여 출력)

 

SELECT department_id 부서아이디, COUNT(*) 직원수, ROUND(AVG(salary)) 평균급여
FROM employees
GROUP BY department_id HAVING COUNT(*) >= 20;

 

-- Q) 
-- 부서별 평균연봉, 최대연봉 구하여 출력하시오
-- 단, 부서별 급여합계가 20000 이상인 경우에 한해서 출력하시오.

 

SELECT department_id 부서아이디, ROUND(AVG(salary)*12) 평균연봉, MAX(salary)*12 최대연봉
FROM employees
GROUP BY department_id 
HAVING SUM(salary) >= 20000
ORDER BY 1;

 

-- Q) 입사 년도별 직원수를 출력하되 직원수가 많은 년도가 먼저 출력되도록 하고
-- SORT 하여 출력, 사원수가 8명 이상인 년도만 출력(년도, 직원수 출력)

 

SELECT SUBSTR(hire_date, 1, 2) || '년' 입사년도, COUNT(*) || '명' 직원수
FROM employees
GROUP BY SUBSTR(hire_date, 1, 2)
HAVING COUNT(*) >= 8
ORDER BY COUNT(*) DESC;

 

-- Q) 각 부서별 최대급여와 최소급여, 평균급여, 급여합계, 인원수를 조회, 출력하시오
-- (단, 인원이 1명이거나 전체 부서별 직원이 동일금액 받을시는 출력에서 제외)

 

-- 첫 번째 풀이

SELECT department_id 부서아이디, MAX(salary) 최대급여, MIN(salary) 최소급여, ROUND(AVG(salary)) 평균급여, SUM(salary) 급여합계, COUNT(*) 인원수
FROM employees
GROUP BY department_id 
HAVING COUNT(*) > 1 OR MAX(salary) != MIN(salary)
ORDER BY 1;

 

-- 두 번째 풀이

SELECT department_id 부서아이디, MAX(salary) 최대급여, MIN(salary) 최소급여, ROUND(AVG(salary)) 평균급여, SUM(salary) 급여합계, COUNT(*) 인원수
FROM employees
GROUP BY department_id 
HAVING MAX(salary) != MIN(salary)
ORDER BY 1;

 

-- 세 번째 풀이

SELECT department_id 부서아이디, MAX(salary) 최대급여, MIN(salary) 최소급여, ROUND(AVG(salary)) 평균급여, SUM(salary) 급여합계, COUNT(*) 인원수
FROM employees
GROUP BY department_id 
HAVING COUNT(*) > 1
ORDER BY 1;

 

-- Q) 부서가 30, 80, 100번인 직원들 중에서 급여를 3000이상 22000원 이하를 받는 직원들에 한해
-- 부서별 평균 급여를 조회, 출력 하시오.
-- 단, 평균급여가 3500 이상인 부서만 출력하되 평균급여가 낮은 부서순으로 출력한다.

 

SELECT department_id 부서아이디, AVG(salary) 평균급여
FROM employees
WHERE department_id in (30, 80, 100) AND salary BETWEEN 3000 AND 22000
GROUP BY department_id
HAVING AVG(salary) >= 3500
ORDER BY AVG(salary);