기본 함수 예제 문제(hr)

-- 기본 함수 실습 과제
-- 문제1) EMPLOYEES 테이블에서 King의 정보를 소문자로 검색하고 
-- 사원번호, 성명, 담당업무(소문자로),부서번호를 출력하라.
SELECT EMPLOYEE_ID, LAST_NAME, LOWER(JOB_ID), DEPARTMENT_ID    
FROM EMPLOYEES
WHERE LOWER(LAST_NAME) = 'king';
 
-- 문제2) EMPLOYEES 테이블에서 King의 정보를 대문자로 검색하고 
-- 사원번호, 성명, 담당업무(대문자로),부서번호를 출력하라.
SELECT employee_id, LAST_NAME, UPPER(JOB_ID), DEPARTMENT_ID    
FROM EMPLOYEES
WHERE UPPER(LAST_NAME) = 'KING';
 
-- 문제3) DEPARTMENTS 테이블에서 부서번호와 부서이름, 부서이름과 
-- 위치번호를 합하여 출력하도록 하라.
SELECT DEPARTMENT_ID, DEPARTMENT_NAME,
    DEPARTMENT_NAME || '' || LOCATION_ID
FROM DEPARTMENTS;
 
SELECT CONCAT(DEPARTMENT_ID, DEPARTMENT_NAME),
    CONCAT(DEPARTMENT_NAME, LOCATION_ID)
FROM DEPARTMENTS;
 
-- 문제4) EMPLOYEES 테이블에서 이름의 첫 글자가 
-- ‘K’ 보다 크고 ‘Y’보다 적은 사원의 정보를 
-- 사원번호, 이름, 업무, 급여, 부서번호를 출력하라. 
-- 단 이름순으로 정렬하여라.
SELECT EMPLOYEE_ID, LAST_NAME, JOB_ID, SALARY, DEPARTMENT_ID
FROM EMPLOYEES
WHERE SUBSTR(LAST_NAME, 1, 1) > 'K'
    AND SUBSTR(LAST_NAME, 1, 1) < 'Y'
ORDER BY LAST_NAME;
 
-- 문제5) EMPLOYEES 테이블에서 20번 부서 중 
-- 이름의 길이 및 급여의 자릿수를 
-- 사원번호, 이름, 이름의 자릿수, 급여, 급여의 자릿수를 출력하라.
SELECT EMPLOYEE_ID, LAST_NAME, LENGTH(LAST_NAME), SALARY, LENGTH(SALARY)        
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 20;
 
-- 문제6) EMPLOYEES 테이블에서 이름 중 ‘e’자의 위치를 출력하라.
SELECT LAST_NAME, INSTR(LAST_NAME, 'e', 1, 1)
FROM EMPLOYEES;
 
-- 문제7) 다음의 쿼리를 실행하고 결과를 분석하라.
SELECT ROUND(4567.678), ROUND(4567.678,0), ROUND(4567.678, 2),
    ROUND(4567.678,-2)
FROM dual;
 
-- 문제8) EMPLOYEES 테이블에서 부서번호가 80인 사람의 급여를 
-- 30으로 나눈 나머지를 구하여 출력하라.
SELECT LAST_NAME, SALARY, MOD(SALARY, 30), DEPARTMENT_ID    
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 80;
 
 
-- 문제9) EMPLOYEES 테이블에서 30번 부서 중 
-- 이름과 담당 업무를 연결하여 출력하여라. 
-- 단 담당 업무를 한 줄 아래로 출력하라.
SELECT LAST_NAME || CHR(10) || JOB_ID    
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 30;
 
-- 문제10) EMPLOYEES 테이블에서 
-- 현재까지 근무일 수가 몇주 몇일 인가를 출력하여라. 
-- 단 근무 일수가 많은 사람 순으로 출력하여라.
SELECT LAST_NAME, HIRE_DATE, SYSDATE, 
    TRUNC(SYSDATE - HIRE_DATE) AS "총 근무일수",
    TRUNC((SYSDATE - HIRE_DATE)/7) AS "총 근무주일수",
    ROUND(MOD(SYSDATE - HIRE_DATE, 7), 0) AS "남은 날수"
FROM EMPLOYEES
ORDER BY (SYSDATE - HIRE_DATE) DESC;
 
-- 문제11) EMPLOYEES 테이블에서 부서 50에서 
-- 급여 앞에 $를 삽입하고 3자리마다 ,를 출력하라
SELECT DEPARTMENT_ID, LAST_NAME, TO_CHAR(SALARY, '$999,999')
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 50;
 
-- 문제12) 다음의 결과를 분석하여 보아라.
SELECT EMPLOYEE_ID,LAST_NAME, JOB_ID,SALARY,
    DECODE(JOB_ID,
        'IT_PROG', SALARY*1.1,
        'ST_MAN', SALARY*1.15,
        'SA_MAN', SALARY*1.2, SALARY) d_sal
FROM EMPLOYEES
WHERE JOB_ID IN('IT_PROG', 'ST_MAN', 'SA_MAN')
ORDER BY SALARY DESC;
 
-- 그룹 함수 실습 과제
-- 문제1) EMPLOYEES 테이블에서 모든 SALESMAN(SA_)에 대하여 
-- 급여의 평균, 최고액, 최저액, 합계를 구하여 출력하여라.
SELECT AVG(SALARY), MAX(SALARY), MIN(SALARY), SUM(SALARY)    
FROM EMPLOYEES
WHERE JOB_ID LIKE 'SA_%';
 
-- 문제2) EMPLOYEES 테이블에 등록되어 있는 
-- 인원수, 보너스가 NULL이 아닌 
-- 인원수, 보너스의 평균, 등록되어 있는 부서의 수를 구하여 출력하라.
SELECT COUNT(*), COUNT(COMMISSION_PCT),
    TRUNC(AVG(COMMISSION_PCT * SALARY)),
    COUNT(DISTINCT DEPARTMENT_ID)
FROM EMPLOYEES;
 
-- 문제3) EMPLOYEES 테이블에서 부서별로 
-- 인원수, 평균 급여, 최저급여, 최고 급여, 급여의 합을 구하여 출력하라.
SELECT DEPARTMENT_ID, COUNT(DEPARTMENT_ID), TRUNC(AVG(SALARY)),
    MIN(SALARY), MAX(SALARY), SUM(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID;
 
-- 문제4) EMPLOYEES 테이블에서 각 부서별로 
-- 인원수,급여의 평균, 최저 급여, 최고 급여, 급여의 합을 구하여 
-- 급여의 합이 많은 순으로 출력하여라.
SELECT DEPARTMENT_ID, COUNT(DEPARTMENT_ID), TRUNC(AVG(SALARY)),
    MIN(SALARY), MAX(SALARY), SUM(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
ORDER BY SUM(SALARY) 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, ROUND(AVG(SALARY)), SUM(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING MAX(SALARY) >= 10000; 
 
-- 문제8) EMPLOYEES 테이블에서 업무별 
-- 급여의 평균이 10000 이상인 업무에 대해서 
-- 업무명,평균 급여, 급여의 합을 구하여 출력하라.
SELECT JOB_ID, COUNT(*), AVG(SALARY), SUM(SALARY)
FROM EMPLOYEES
GROUP BY JOB_ID
HAVING AVG(SALARY) >= 10000;