오라클 oracle / round, sum, to_char, to_date min, max, having, group 이용한 연습 문제 풀이 1~19번

문제1) EMPLOYEES 테이블에서 입사일자 순으로 정렬하여 사원번호, 이름, 업무, 급여, 입사일자,부서번호를 출력하라.

SELECT employee_id, first_name, job_id, salary, hire_date, department_id
FROM employees
ORDER BY hire_date ASC;     -- ASC 생략 가능(오름차순)

 

 

문제2) EMPLOYEES 테이블에서 가장 최근에 입사한 순으로 사원번호, 이름, 업무, 급여, 입사일자,부서번호를 출력하라.

SELECT employee_id, first_name, job_id, salary, hire_date, department_id
FROM employees
ORDER BY hire_date DESC;        --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;

 

 

문제5) EMPLOYEES 테이블에서 King의 정보를 대문자로 검색하고 사원번호, 성명, 담당업무를 대문자로 그리고 부서번호를 출력하라.

SELECT employee_id, last_name, UPPER(job_id), department_id
FROM employees
WHERE UPPER(last_name) = 'KING';       

*WHERE UPPER(last_name) = 'KING';     

어떻게 검색하든 대문자로 바꿔서 검색, 검색이 안되는 것을 막아주는 역할(KING, King, king 뭐로 검색해도 됨)
UPPER():  대문자로 변환

 

 

문제6) DEPARTMENTS 테이블에서 부서번호와 부서이름, 부서이름과 위치번호를 합하여 출력하도록 하라.

SELECT department_id, department_name, CONCAT(department_name, location_id)     
FROM departments;

* CONCAT : 문자열 합치기

 

 

문제7) EMPLOYEES 테이블에서 이름 중 ‘e’자의 위치를 출력하라.

SELECT first_name, INSTR(first_name,'e')        -- DB는 1번째부터 시작
FROM employees;

*INSTR:  해당 글자의 위치 출력

 

SELECT first_name, INSTR(first_name,'e', 1, 2)      -- 1번부터 시작해서, 두번째 e의 위치
FROM employees;

*INSTR ('a',b,c)

'a':찾으려는 문자

b:시작위치

b번부터 시작한 c번째 a의 위치

 

 

문제8) EMPLOYEES 테이블에서 부서번호가 80인 사람의 급여를 30으로 나눈 나머지를 구하여 출력하라.

SELECT department_id, first_name, MOD(salary, 30), salary
FROM employees
WHERE department_id = 80;

*MOD: 나머지

 

 

문제9) EMPLOYEES 테이블에서 현재(09/01/01로 설정)까지 근무일 수가 몇주 몇일 인가를 출력하여라.

단 근무 일수가 많은 사람 순으로 출력하여라.

SELECT first_name, hire_date,    TO_DATE('09/01/01') - hire_date AS "총 근무 일수",     
TRUNC((TO_DATE('09/01/01') - hire_date) / 7) AS "총 근무 주일 수"
FROM employees
ORDER BY "총 근무 일수" DESC;

*TRUNC : 소수점 버림

*ROUND : 반올림

 

 

문제10) EMPLOYEES 테이블에서 부서 50에서 급여 앞에 $를 삽입하고 3자리마다 ,를 출력하라

SELECT first_name, salary, TO_CHAR(salary, '$999,999,999')
FROM employees
WHERE department_id = 50;

*TO_CHAR : 문자열로 바꿔주는 함수 (바꿀부분, 표현방식)

 

 

문제11) EMPLOYEES 테이블에서 모든 SALESMAN(SA_)에 대하여 급여의 평균, 최고액, 최저액, 합계를 구하여 출력하여라.

SELECT AVG(salary), MAX(salary), MIN(salary), SUM(salary)
FROM employees
WHERE job_id LIKE 'SA_%';

 

문제12) EMPLOYEES 테이블에 등록되어 있는 인원수, 
보너스가 NULL이 아닌 인원수, 보너스의 평균, 등록되어 있는 부서의 수를 구하여 출력하라.

SELECT COUNT(*), COUNT(commission_pct), ROUND(AVG(salary*commission_pct)), COUNT(DISTINCT department_id)        
FROM employees;             

*COUNT는 NULL을 세지 않는다

*DISTINCT를 이용해 중복된 부서를 빼고 숫자 세기

 

 

문제13) EMPLOYEES 테이블에서 부서별로 인원수, 평균 급여, 최저급여, 최고 급여, 급여의 합을 구하여 출력하라.

SELECT department_id, COUNT(*), ROUND(AVG(salary)), MIN(salary), MAX(salary), SUM(salary)
FROM employees
GROUP BY department_id;

 

 

문제14) EMPLOYEES 테이블에서 각 부서별로 인원수,급여의 평균, 최저 급여, 최고 급여, 급여의 합을 구하여 급여의 합이 많은 순으로 출력하여라.

SELECT department_id, COUNT(*), TRUNC(AVG(salary)), MIN(salary), MAX(salary), SUM(salary) AS "급여의 합"
FROM employees
GROUP BY department_id
ORDER BY "급여의 합" DESC;

*sum(salary) 의 별명(alias)인 "급여의 합"을 호출해서도 내림차순 정렬 가능

 

 

문제15) EMPLOYEES 테이블에서 부서별, 업무별 그룹하여 결과를 부서번호, 업무, 인원수, 급여의 평균, 급여의 합을 구하여 출력하여라.

SELECT department_id, job_id, COUNT(*), AVG(SALARY), SUM(SALARY)
FROM employees
GROUP BY department_id, job_id;  

 

문제16) EMPLOYEES 테이블에서 부서 인원이 4명보다 많은 부서의 부서번호, 인원수, 급여의 합을 구하여 출력하여라

SELECT department_id, COUNT(*), SUM(salary)    
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 4;        

*HAVING : 부서로 묶은 다음의 조건

 

 

 

문제17) EMPLOYEES 테이블에서 급여가 최대 10000이상인 부서에 대해서 부서번호, 평균 급여, 급여의 합을 구하여 출력하여라.

SELECT department_id, TRUNC(AVG(salary)), SUM(salary), MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) >= 10000;

 

 

문제18) EMPLOYEES 테이블에서 업무별 급여의 평균이 10000 이상인 업무에 대해서 업무명,평균 급여, 급여의 합을 구하여 출력하라.

SELECT job_id, TRUNC(AVG(salary)), SUM(salary)
FROM employees
GROUP BY job_id
HAVING AVG(salary) >= 10000;

 

문제19) EMPLOYEES 테이블에서 전체 월급이 10000을 초과하는 각 업무에 대해서 업무와 월급여 합계를 출력하라. 
단 판매원(SA)은 제외하고  월 급여 합계로 정렬(내림차순)하라.

SELECT job_id, SUM(salary) AS "월급여 합계"
FROM employees
WHERE job_id NOT LIKE 'SA%'         -- 판매원을 제외 시키고(그룹 전)
GROUP BY job_id                          -- 업무로 그룹을 잡고
HAVING SUM(salary) > 10000          --업무별 월급이 10000초과하는 조건
ORDER BY "월급여 합계" DESC;