ORACLE - 그룹함수 및 실전문제

그룹함수 : 결과가 무조건 1개 FIELD로 나옴

• SUM : 누적합계를 리턴
  SELECT SUM(SALARY) FROM EMPLOYEE;
• AVG : 그룹의 평균을 리턴
  SELECT AVG(SALARY) FROM EMPLOYEE;
  SELECT SUM(SALARY), AVG(SALARY) FROM EMPLOYEE WHERE DEPT_CODE='D9';
• COUNT: 조회된 ROW의 수를 리턴
  SELECT BONUS FROM EMPLOYEE;
  SELECT COUNT(BONUS) FROM EMPLOYEE;
  SELECT COUNT(NVL(BONUS,0)) FROM EMPLOYEE;
• MAX,MIN : 그룹의 최대값, 최소값
  SELECT MAX(SALARY), MIN(SALARY) FROM EMPLOYEE;

/실습/

1. 직원명과 이메일, 이메일 길이를 출력하시오

SELECT EMP_NAME 직원명, EMAIL 이메일, LENGTH(EMAIL) 이메일길이
FROM EMPLOYEE;

2. 직원의 이름과 이메일 주소 중 아이디 부분만 출력하시오

SELECT EMP_NAME, LPAD(EMAIL, INSTR(EMAIL,'@',1,1)-1)이메일아이디
FROM EMPLOYEE;

SELECT EMP_NAME, SUBSTR(EMAIL, 1,INSTR(EMAIL,'@',1,1)-1)이메일아이디
FROM EMPLOYEE;

3. 60년생의 직원명과 년생, 보너스 값을 출력하시오. 그 때 보너스 값이 NULL인 경우 0으로 출력되게 만드시오.

SELECT EMP_NAME 직원명, RPAD(EMP_NO,2), NVL(BONUS,0) 보너스
FROM EMPLOYEE
WHERE SUBSTR(EMP_NO,1,2) BETWEEN 60 AND 69 ;

4. '010' 핸드폰 번호를 쓰지 않는 사람의 수를 출력하시오(뒤에 단위는 명을 붙이시오)

SELECT COUNT(EMP_NAME) || '명' FROM EMPLOYEE WHERE SUBSTR(PHONE,1,3) != '010';

SELECT COUNT(EMP_NAME) || '명' FROM EMPLOYEE WHERE PHONE NOT LIKE '010%';

5. 직원명과 입사년월을 출력하시오.

SELECT EMP_NAME 직원명, TO_CHAR(HIRE_DATE, 'YYYY"년" MM"월"')입사년월
FROM EMPLOYEE;

6. 직원명과 주민번호를 조회하시오. 단, 주민번호 9번째 자리부터 끝까지는 '*' 문자로 채워 출력하시오.

SELECT EMP_NAME, SUBSTR(EMP_NO,1,8)||'**'
FROM EMPLOYEE;

SELECT EMP_NAME 직원명, RPAD(SUBSTR(EMP_NO,1,8),14,'*') 주민등록번호
FROM EMPLOYEE;

7. 직원명, 직급코드, 연봉(원) 조회. 단, 연봉은 57,000,000 으로 표시되게 함.
   연봉은 보너스포인트가 적용된 1년치 급여임

SELECT EMP_NAME, JOB_CODE,
TO_CHAR((SALARY+SALARYNVL(BONUS,0))12,'L999,999,999') 연봉
FROM EMPLOYEE;

8. 부서코드가 D5, D9인 직원들 중에서 2004년도에 입사한 직원의 수 조회

SELECT EMP_ID, EMP_NAME, DEPT_CODE, HIRE_DATE
FROM EMPLOYEE
WHERE DEPT_CODE IN ('D5' ,'D9')
AND SUBSTR(HIRE_DATE,1,2) LIKE '04' ;

9. 직원명, 입사일, 오늘까지 근무일수 조회. 주말 포함, 소수점 아래 버림

SELECT EMP_NAME, HIRE_DATE, FLOOR(SYSDATE-HIRE_DATE) 입사일
FROM EMPLOYEE;

10. 모든 직원의 나이 중 가장 많은 나이와 가장 적은 나이를 출력 하여라.

SELECT MAX(EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM TO_DATE(SUBSTR(EMP_NO,1,2),'RR'))+1 )최대나이,
MIN(EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM TO_DATE(SUBSTR(EMP_NO,1,2),'RR'))+1 )최소나이
FROM EMPLOYEE;

11. 회사에서 야근을 해야 하는 부서를 발표하여야 한다.
    부서코드가 D5, D6, D9 야근, 그외는 야근없음으로 출력되도록 하여라.
    출력값은 값은 이름, 부서코드, 야근유무(부서코드 기준 오름차순 정렬함)

SELECT EMP_NAME, DEPT_CODE, DECODE(DEPT_CODE, 'D5', '야근','D6', '야근',
'D9', '야근', '야근없음')야근유무
FROM EMPLOYEE
ORDER BY DEPT_CODE;

SELECT EMP_NAME, DEPT_CODE,
CASE
WHEN DEPT_CODE IN ('D5', 'D6', 'D9')
THEN '야근'
ELSE '야근없음'
END 야근유무
FROM EMPLOYEE
ORDER BY DEPT_CODE;

12. 부서코드가 D5이면 총무부, D6이면 기획부, D9이면 영업부로 처리하시오.
    단, 부서코드가 D5, D6, D9인 직원의 정보만 조회함. 부서코드 오름차순.

SELECT EMP_NAME, DEPT_CODE, DECODE(DEPT_CODE, 'D5', '총무부','D6', '기획부',
'D9', '영업부')
FROM EMPLOYEE
WHERE DEPT_CODE IN ('D5', 'D6', 'D9')
ORDER BY DEPT_CODE;

SELECT EMP_NAME, DEPT_CODE,
CASE
WHEN DEPT_CODE='D5' THEN '총무부'
WHEN DEPT_CODE='D6' THEN '기획부'
WHEN DEPT_CODE='D9' THEN '영업부'
END 부서명
FROM EMPLOYEE
WHERE DEPT_CODE IN ('D5', 'D6', 'D9')
ORDER BY DEPT_CODE;

13. 직원명, 부서코드, 생년월일, 나이 조회. 단, 생년월일은 주민번호에서 추출해서
    OO년 OO월 OO일로 출력되게 함. 나이는 주민번호에서 추출해서 날짜데이터로 변환한 다음 계산
    주민번호가 이상한 사람들은 제외시키고 진행(200,201,214번 제외):NOT IN 사용

SELECT EMP_NAME, DEPT_CODE,
EXTRACT(YEAR FROM SYSDATE) -
EXTRACT(YEAR FROM TO_DATE(SUBSTR(EMP_NO,1,6),'RRMMDD'))+1 AS 나이
FROM EMPLOYEE
WHERE EMP_ID NOT IN(200, 201, 214);

SELECT EMP_NAME, DEPT_CODE, TO_CHAR(TO_DATE(SUBSTR(EMP_NO,1,6),'RRMMDD'),
'YY"년" MM"월" DD"일"') 생년월일
FROM EMPLOYEE
WHERE EMP_ID NOT IN(200,201,214);

14. 직원들의 입사일로 부터 년도만 가지고, 각 년도별 입사인원수를 구하시오.
    아래의 년도에 입사한 인원수를 조회하시오. 마지막으로 전체 직원수도 구하시오.

SELECT
SUM(DECODE(EXTRACT(YEAR FROM HIRE_DATE),1998,1,0)) "1998년",
SUM(DECODE(EXTRACT(YEAR FROM HIRE_DATE),1999,1,0)) "1999년",
SUM(DECODE(EXTRACT(YEAR FROM HIRE_DATE),2000,1,0)) "2000년",
SUM(DECODE(EXTRACT(YEAR FROM HIRE_DATE),2001,1,0)) "2001년",
SUM(DECODE(EXTRACT(YEAR FROM HIRE_DATE),2002,1,0)) "2002년",
SUM(DECODE(EXTRACT(YEAR FROM HIRE_DATE),2003,1,0)) "2003년",
COUNT(*)"전체직원수"

FROM EMPLOYEE;

SELECT
EXTRACT(YEAR FROM HIRE_DATE),
DECODE(EXTRACT(YEAR FROM HIRE_DATE), 1999, 1, 0) "1999 년"

FROM EMPLOYEE;

• 그룹함수는 일반 컬럼이랑 같이 못쓴다.