[Oracle] ORDER BY , GROUP BY 예제

-- hr 정렬
-- 문제1) EMPLOYEES 테이블에서 입사일자 순으로 정렬하여 사원번호, 이름, 업무, 급여, 입사일자,부서번호를 출력하라.
SELECT employee_id, first_name, job_id, salary, hire_date, department_id
FROM employees
ORDER BY hire_date ASC;

-- 문제2) EMPLOYEES 테이블에서 가장 최근에 입사한 순으로 사원번호, 이름, 업무, 급여, 입사일자,부서번호를 출력하라.
SELECT employee_id, first_name, job_id, salary, hire_date, department_id
FROM employees
ORDER BY hire_date DESC;

-- 문제3) EMPLOYEES 테이블에서 부서번호로 정렬한 후 부서번호가 같을 경우 급여가 많은 순으로 정렬하여 
-- 사원번호, 성명, 업무, 부서번호, 급여를 출력하여라.
SELECT employee_id, first_name, job_id, department_id, salary
FROM employees
ORDER BY department_id ASC, salary DESC;

-- 문제4) EMPLOYEES 테이블에서 첫번째 정렬은 부서번호로 두번째 정렬은 업무로 세번째 정렬은 급여가 많은 순으로 정렬하여 
-- 사원번호, 성명, 입사일자, 부서번호, 업무, 급여를 출력하여라.
SELECT employee_id, first_name, hire_date, department_id, job_id, salary
FROM employees
ORDER BY department_id ASC, job_id ASC, salary DESC;


-- hr 그룹핑
-- 문제1) EMPLOYEES 테이블에서 모든 SALESMAN(SA_)에 대하여 급여의 평균, 최고액, 최저액, 합계를 구하여 출력하여라.
SELECT AVG(salary), MAX(salary), MIN(salary), SUM(salary), COUNT(*)
FROM employees
WHERE job_id LIKE 'SA_%';

-- 문제2) EMPLOYEES 테이블에 등록되어 있는 인원수, 보너스가 NULL이 아닌 인원수, 보너스의 평균, 
-- 등록되어 있는 부서의 수를 구하여 출력하라.
SELECT COUNT(*), AVG(commission_pct * salary), COUNT(DISTINCT department_id)
FROM employees;

-- 문제3) EMPLOYEES 테이블에서 부서별로 인원수, 평균 급여, 최저급여, 최고 급여, 급여의 합을 구하여 출력하라.
SELECT department_id, COUNT(*), ROUND(AVG(salary), 1), MIN(salary), MAX(salary), SUM(salary)
FROM employees
GROUP BY department_id
ORDER BY COUNT(*) DESC;

-- 문제4) EMPLOYEES 테이블에서 각 부서별로 인원수,급여의 평균, 최저 급여, 최고 급여, 급여의 합을 구하여 급여의 합이 많은 순으로 출력하여라.
SELECT COUNT(*), TRUNC(AVG(salary)), MIN(salary), MAX(salary), SUM(salary) AS "급여의 합"
FROM employees
GROUP BY department_id
ORDER BY "급여의 합" DESC;

-- 문제5) EMPLOYEES 테이블에서 부서별, 업무별 그룹하여 결과를 부서번호, 업무, 인원수, 급여의 평균, 급여의 합을 구하여 출력하여라.
SELECT department_id, job_id, COUNT(*), AVG(salary), SUM(salary)
FROM employees
GROUP BY department_id, job_id;

-- 문제6) EMPLOYEES 테이블에서 부서 인원이 4명보다 많은 부서의 부서번호, 인원수, 급여의 합을 구하여 출력하여라
SELECT department_id, COUNT(*), SUM(salary)
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 4;

-- 문제7) EMPLOYEES 테이블에서 급여가 최대 10000이상인 부서에 대해서 부서번호, 평균 급여, 급여의 합을 구하여 출력하여라.
SELECT department_id, AVG(salary), SUM(salary), MAX(SALARY)
FROM employees
GROUP BY department_id
HAVING MAX(SALARY) > 10000;

-- 문제8) EMPLOYEES 테이블에서 업무별 급여의 평균이 10000 이상인 업무에 대해서 업무명,평균 급여, 급여의 합을 구하여 출력하라.
SELECT job_id, AVG(salary), SUM(salary)
FROM employees
GROUP BY job_id
HAVING AVG(SALARY) > 10000;

-- 문제9) EMPLOYEES 테이블에서 전체 월급이 10000을 초과하는 각 업무에 대해서 업무와 월급여 합계를 출력하라. 
-- 단 판매원(SA_)은 제외하고 
-- 월 급여 합계로 정렬(내림차순)하라.
SELECT job_id, SUM(salary)
FROM employees
WHERE job_id NOT LIKE 'SA_%'
GROUP BY job_id
HAVING SUM(salary) > 10000
ORDER BY SUM(salary) DESC;


-- scott
-- SCOTT
--19) emp 테이블에서 사원번호, 사원이름, 입사일을 출력하는데 입사일이 빠른 사람순으로 정렬하시오.
SELECT empno, ename, hiredate
FROM emp
ORDER BY hiredate asc;

--20) emp 테이블에서 사원이름, 급여, 연봉을 구하고 연봉이 많은 순으로 정렬하시오.
SELECT ename, sal, sal * 12 as annsal
FROM emp
ORDER BY annsal DESC;

--21)10번 부서와 20번부서에서 근무하고 있는 사원의 이름과 부서번호를 출력하는데 이름을 영문자순으로 표시하시오.
SELECT ename, deptno
FROM emp
WHERE deptno IN(10, 20)
ORDER BY ename ASC;

--22) 커미션을 받는 모든 사원의 이름,급여 및 커미션을 커미션을 기준으로 내림차순으로 정렬하여 표시하십시오.
SELECT ename, sal, comm
FROM emp
WHERE comm IS NOT NULL AND comm <> 0
ORDER BY comm DESC;

--23) emp테이블의 업무(job)을 첫글자는 대문자 나머지는 소문자로 출력하시오.    INITCAP()
SELECT job, INITCAP(job)
FROM emp;

--24) emp테이블에서 사원이름 중 A가 포함된 사원이름을 구하고 그 이름 중 앞에서 3자만 추출하여 출력
SELECT ename, SUBSTR(ename, 1, 3)
FROM emp
WHERE ename LIKE '%A%';

--25) 이름의 세번째 문자가 A인 모든 사원의 이름을 표시하시오.
SELECT ename
FROM emp
WHERE SUBSTR(ename, 3, 1) = 'A';

--26) 이름이 J,A 또는 M으로 시작하는 모든 사원의 이름(첫 글자는 대문자로, 나머지 글자는 소문자로 표시) 
-- 및 이름의 길이를 표시하시오.(열 레이블은 name과 length로 표시)
SELECT ename, INITCAP(ename), LENGTH(ename)
FROM emp
WHERE ename LIKE 'J%' 
    OR ename LIKE 'A%' 
        OR ename LIKE 'M%';

--27) 이름의 글자수가 6자 이상인 사원의 이름을 소문자로 이름만 출력하시오
SELECT ename, LOWER(ename)
FROM emp
WHERE LENGTH(ename) >= 6;

--28) 이름의 글자수가 6자 이상인 사람의 이름을 앞에서 3자만 구하여 소문자로 출력하시오.
SELECT ename, LOWER(SUBSTR(ename, 1, 3))
FROM emp
WHERE LENGTH(ename) >= 6;

--29) 모든 사원의 이름과 급여를 표시하시오. 급여는 15자 길이로 왼쪽에 $기호가 채워진 형식으로 표기하고 
-- 열레이블(column명)을 SALARY로 지정하시오.
SELECT ename, sal, LPAD(sal, 15, '$') as SALARY
FROM emp;

--30) 오늘부터 이번달의 마지막날까지의 남은 날 수를 구하시오.
SELECT LAST_DAY(sysdate) - sysdate
FROM DUAL;

--31) emp테이블에서 각 사원에 대해 사원번호, 이름, 급여 및 15% 인상된 급여를 정수(반올림)로 표시하시오.
--    인상된 급여열의 레이블을 New Salary로 지정하시오.
SELECT empno, ename, sal, ROUND(sal * 1.15) AS "New Salary"
FROM emp;

--32) emp테이블에서 월급의 4의 배수(mod(sal,4)=0)인 사원의 이름과 월급을 출력하시오.(세자리 단위 쉼표 표시)
SELECT ename, TO_CHAR(sal, '999,999,999')
FROM emp
WHERE MOD(sal, 4) = 0;

-- 33) 각 사원의 이름을 표시하고 근무 달 수(입사일로부터 현재까지의 달수)를 계산하여 
-- 열 레이블을 MONTHS_WORKED로 지정하시오. 
-- 결과는 정수로 반올림하여 표시하고 근무달 수를 기준으로 오름차순으로 정렬하시오.
SELECT ename, ROUND(SYSDATE - hiredate), ROUND(MONTHS_BETWEEN(SYSDATE, hiredate)) as MONTHS_WORKED,
    TO_DATE('2021-04-06', 'YYYY-MM-DD') - TO_DATE('2021-04-05', 'YYYY-MM-DD')
FROM emp;

-- 34)emp테이블에서 이름, 업무, 근무연차를 출력하시오.
SELECT ename, TRUNC(MONTHS_BETWEEN(SYSDATE, hiredate) / 12) "근무연차"
FROM emp;

-- 35)emp테이블에서 사원이름, 월급, 월급과 커미션을 더한 값을 컬럼명 실급여라고 해서 출력.
-- 단, NULL값은 나타나지 않게 작성하시오. NVL
SELECT ename, sal, sal + NVL(comm, 0) as 실급여, comm
FROM emp
WHERE comm IS NOT NULL;

-- 36)월급과 커미션을 합친 금액이 2,000이상인 급여를 받는 사원의 이름,업무,월급,커미션,고용날짜를 출력하시오. 
-- 단, 고용날짜는 1980-12-17 형태로 출력하시오.
SELECT ename, job, sal, NVL(comm, 0), TO_CHAR(hiredate, 'YYYY-MM-DD')
FROM emp
WHERE sal + NVL(comm, 0) > 2000;

-- 38)모든 사원의 급여의 최고액, 최저액, 총액 및 평균액을 표시하시오. 
-- 열 레이블을 각각 maximum,minimum,sum 및 average로 지정하고 
-- 결과를 정수로 반올림하고 세자리 단위로 ,를 명시하시오.
SELECT MAX(sal) as maximum, MIN(sal) as minimum, SUM(sal) as sum, TO_CHAR( ROUND(AVG(sal)), '9,999') as average
FROM emp;

-- 39) 업무가 동일한 사원 수를 표시하는 질의를 작성하시오.
SELECT job, COUNT(*)
FROM emp
GROUP BY job;

-- 40) emp테이블에서 30번부서의 사원수를 구하시오.
SELECT COUNT(*)
FROM emp
WHERE deptno = 30;

-- 41) emp테이블에서 업무별 최고 월급을 구하고 업무,최고 월급을 출력하시오.
SELECT job, MAX(sal)
FROM emp
GROUP BY job;

-- 42) emp테이블에서 20번부서의 급여 합계를 구하고 급여 합계 금액을 출력하시오.
SELECT sum(sal)
FROM emp
WHERE deptno = 20;

-- 43) emp테이블에서 부서별로 지급되는 총월급에서 금액이 7,000이상인 부서번호, 총월급을 출력하시오.
SELECT deptno, SUM(sal)
FROM emp
GROUP BY deptno
HAVING SUM(sal) >= 7000;
 
-- 44) emp테이블에서 업무별로 사번이 제일 늦은 사람을 구하고 그 결과 내에서 사번이 79로 시작하는 결과만 보여주시오.
SELECT job, MAX(empno)
FROM emp
GROUP BY job
HAVING MAX(empno) LIKE '79%';

-- 45) emp테이블에서 업무별 총월급을 출력하는데 업무가 'MANAGER'인 사원들은 제외하고 
-- 총월급이 5,000보다 큰 업무와 총월급만 출력하시오.
SELECT job, SUM(sal)
FROM emp
WHERE job != 'MANAGER'
GROUP BY job
HAVING SUM(sal) > 5000;

-- 46)emp테이블에서 업무별로 사원수가 4명 이상인 업무와 인원수를 출력하시오.
SELECT job, COUNT(*)
FROM emp
GROUP BY job
HAVING COUNT(*) >= 4;

-- 47)emp테이블에서 사원수가 5명이 넘는 부서의 부서번호와 사원수를 구하시오.
SELECT deptno, COUNT(*)
FROM emp
GROUP BY deptno
HAVING COUNT(*) > 5;

-- 48)emp테이블에서 부서별 급여평균을 구할 때 소수점 3자리에서 반올림해서 2자리까지 구하고 
-- 부서번호, 급여평균을 출력하시오.
SELECT deptno, ROUND(AVG(sal), 2)
FROM emp
GROUP BY deptno;