* 정렬
문제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;
문제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;
* 표준함수
문제1) EMPLOYEES 테이블에서 King의 정보를 대문자로 검색하고 사원번호, 성명, 담당업무를 대문자로 그리고 부서번호를 출력하라.
-- UPPER() : 대문자로 변환
SELECT employee_id, last_name, UPPER(job_id), department_id
FROM employees
WHERE UPPER(last_name) = 'KING';- 어떻게 검색하든 대문자로 바꿔서 검색, 검색이 안되는 것을 막아주는 역할(KING, King, king 뭐로 검색해도 됨)
문제2) DEPARTMENTS 테이블에서 부서번호와 부서이름, 부서이름과 위치번호를 합하여 출력하도록 하라.
-- CONCAT() : 문자열 합치기
SELECT department_id, department_name, CONCAT(department_name, location_id)
FROM departments;- 띄어쓰기 없이 합쳐짐
문제3) EMPLOYEES 테이블에서 이름 중 ‘e’자의 위치를 출력하라.
-- INSTR() : 해당 글자의 위치 출력
SELECT first_name, INSTR(first_name,'e')
FROM employees;- 맨 앞이 1번째부터 시작
- 대소문자 구분
-- INSTR(문자열, 찾을 글자, 시작위치, 몇번째의 것을 찾을지)
SELECT first_name, INSTR(first_name,'e', 1, 2)
FROM employees;- 1번부터 시작해서, 두번째 e의 위치
문제4) EMPLOYEES 테이블에서 부서번호가 80인 사람의 급여를 30으로 나눈 나머지를 구하여 출력하라.
-- MOD(나머지를 구할 데이터, 나눌 수)
SELECT department_id, first_name, MOD(salary, 30), salary
FROM employees
WHERE department_id = 80;
문제5) EMPLOYEES 테이블에서 현재(09/01/01로 설정)까지 근무일 수가 몇주 몇일 인가를 출력하여라. 단 근무 일수가 많은 사람 순으로 출력하여라.
-- TO_DATE() : 숫자형으로 변경
-- TRUNC() : 소수점 버림
-- ROUND() : 소수점 반올림
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;- 일이 연산되기 때문에 TO_DATE 함수 사용해야 연산 가능
문제6) EMPLOYEES 테이블에서 부서 50에서 급여 앞에 $를 삽입하고 3자리마다 ,를 출력하라
-- TO_CHAR(바꿀부분, 표현방식) : 문자열로 바꿔주는 함수
SELECT first_name, salary, TO_CHAR(salary, '$999,999,999')
FROM employees
WHERE department_id = 50;
* 그룹핑
문제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), ROUND(AVG(salary*commission_pct)), COUNT(DISTINCT department_id)
FROM employees;- 부서의 수 : 중복된 부서를 빼고 세야하기 때문에 DISTINCT 사용
- COUNT는 NULL을 세지 않기 때문에 COUNT(commission_pct) 이렇게만 해도 보너스가 NULL이 아닌 인원수를 구할 수 있다.
문제3) EMPLOYEES 테이블에서 부서별로 인원수, 평균 급여, 최저급여, 최고 급여, 급여의 합을 구하여 출력하라.
SELECT department_id, COUNT(*), ROUND(AVG(salary)), MIN(salary), MAX(salary), SUM(salary)
FROM employees
GROUP BY department_id;
문제4) EMPLOYEES 테이블에서 각 부서별로 인원수,급여의 평균, 최저 급여, 최고 급여, 급여의 합을 구하여 급여의 합이 많은 순으로 출력하여라.
SELECT department_id, 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(*), ROUND(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;- 부서로 묶은 인원 : GROUP BY → HAVING
문제7) EMPLOYEES 테이블에서 급여가 최대 10000이상인 부서에 대해서 부서번호, 평균 급여, 급여의 합을 구하여 출력하여라.
SELECT department_id, TRUNC(AVG(salary)), SUM(salary), MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) >= 10000;
문제8) EMPLOYEES 테이블에서 업무별 급여의 평균이 10000 이상인 업무에 대해서 업무명, 평균 급여, 급여의 합을 구하여 출력하라.
SELECT job_id, TRUNC(AVG(salary)), SUM(salary)
FROM employees
GROUP BY job_id
HAVING AVG(salary) >= 10000;
문제9) 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;
댓글