| 내용 |
문제 범위 : 함수
1. 이메일에 lee를 포함하는 사원의 모든 정보를 출력하세요.
정답 :
SELECT * FROM employees
WHERE lower(email) LIKE '%lee%';
2. 매니저 아이디가 103인 사원들의 이름과 급여, 직무아이디를 출력하세요.
정답 :
SELECT first_name, salary, job_id FROM employees
WHERE manager_id=103;
3. 80번 부서에 근무하면서 직무가 SA_MAN인 사원의 정보와 20번 부서에 근무하면서 매니저 아이디가 100인사원의 정보를 출력하세요. 쿼리문 하나로 출력해야 합니다.
정답 :
SELECT * FROM employees
WHERE (department_id=80 AND job_id='SA_MAN')
OR (department_id=20 AND manager_id=100);
4. 모든 사원의 전화번호를 ###.###.#### 이라면 ###-###-#### 형식으로 출력하세요.
정답 :
SELECT replace(phone_number, '.', '-') AS 전화번호
FROM employees;
5. 직무가 IT_PROG인 사원들 중에서 급여가 5000 이상인 사원들의 이름(Full Name), 급여 지급액, 입사일(2005-02-15형식), 근무한 일수를 출력하세요. 이름순으로 정렬하며, 이름은 최대 20자리, 남는 자리는 *로 채우고 급여 지급액은 소수점 2자리를 포함한 최대 8자리, $표시, 남는 자리는 0으로 채워 출력하세요.
정답 :
SELECT
RPAD(first_name || ' ' || last_name, 20, '*') AS full_name,
TO_CHAR(COALESCE(salary+salary*commission_pct, salary), '$099,999.00') AS salary,
TO_CHAR(hire_date, 'yyyy-mm-dd') AS hire_date,
round(SYSDATE - hire_date) AS work_day
FROM employees
WHERE upper(job_id) = 'IT_PROG' AND salary>5000
ORDER BY full_name;
6. 30번부서 사원의 이름(full name)과 급여, 입사일, 현재 까지 근무 개월 수를 출력하세요. 이름은 최대 20자로출력하고 이름 오른쪽에 남는 공백을 *로 출력하세요. 급여는 상여금을 포함하고 소수점 2자리를 포함한 총8자리로 출력하고 남은 자리는 0으로 채우며 세자리 마다 ,(콤마) 구분기호를 포함하고, 금액 앞에 $를 포함하도록 출력하세요. 입사일은 2005년03월15일 형식으로 출력하세요. 근무 개월 수는 소수점 이하는 버리고 출력하세요. 급여가 큰 사원부터 작은 순서로 출력하세요.
정답 :
SELECT rpad(first_name || ' ' || last_name, 20, '*') AS full_name,
to_char(coalesce(salary+salary*commission_pct, salary), '$099,999.00')
AS salary,
to_char(hire_date, 'yyyy"년" mm"월" dd"일') AS hire_date,
trunc(months_between(sysdate, hire_date)) AS month
FROM employees WHERE department_id=30
ORDER BY salary DESC;
7. 80번 부서에 근무하면서 salary가 10000보다 큰 사원들의 이름과 급여 지급액(salary+salary*commission_pct)을 출력하세요. 이름은 Full Name으로 출력하며 17자리로 출력하세요. 남은 자리는 *로 채우세요. 급여는 소수점 2자리를 포함한 총7자리로 출력하며, 남은 자리는 0으로 채우세요. 금액 앞에 $ 표시를 하며 급여 순으로 정렬하세요.
정답 :
SELECT
rpad(first_name || ' ' || last_name, 17, '*') AS 이름,
to_char(coalesce(salary+salary*commission_pct, salary),'$09,999.00') AS 급여
FROM employees
WHERE department_id=80 AND salary>10000
ORDER BY 급여 DESC;
8. 60번부서 사원의 이름과 근무 년수를 입사일을 현재 일자를 기준으로 현재까지 근무한 근무년수를 5년차, 10년차, 15년차로 표시하세요. 5년~ 9년 근무한 사원은 5년차로 표시합니다. 나머지는 기타로 표시합니다. 근무년수는 근무개월수/12로 계산합니다.(
정답 :
SELECT first_name AS 이름,
decode(trunc(trunc(months_between(SYSDATE, hire_date)/12)/5),
1, '5년차',
2, '10년차',
3, '15년차',
'기타')
AS 근무년수
FROM employees
WHERE department_id=60;
9. Lex가 입사한지 1000일째 되는 날은?
정답 :
SELECT hire_date + 1000
FROM employees
WHERE first_name = 'Lex';
10. 5월에 입사한 사원의 이름과 입사일을 출력하세요.
정답 :
SELECT first_name, hire_date
FROM employees
WHERE to_char(hire_date, 'MM') = '05';
문제 범위 : GROUP BY
11. 직무별 급여 평균을 출력하세요.
정답 :
SELECT job_id, round(avg(salary), 2) AS average FROM employees GROUP BY job_id
12. 부서별 사원의 수를 출력하세요.
정답 :
SELECT department_id, count(*) FROM employees GROUP BY department_id
13. 부서별, 직무별 사원의 수를 각각 출력하세요.
정답 :
SELECT department_id, job_id, count(*) FROM employees GROUP BY department_id, job_id
14. 부서별 급여 표준편차를 출력하세요.
정답 :
SELECT department_id, round(stddev(salary), 2) AS "Standard Deviation" FROM employees GROUP BY department_id
15. 사원의 수가 4명이상인 부서의 아이디와 사원의 수를 출력하세요.
정답 :
SELECT department_id, count(*) FROM employees GROUP BY department_id HAVING count(*) >= 4;
16. 50번부서의 직무별 사원의 수를 출력하세요.
정답 :
SELECT job_id, count(*) FROM employees WHERE department_id=50 GROUP BY job_id
17. 50번 부서에서 직무별 사원의 수가 10명이하인 직무아이디와 사원의 수를 출력하세요.
정답 :
SELECT job_id, count(*) FROM employees WHERE department_id=50 GROUP BY job_id HAVING count(*) <= 10;
18. 모든 사원의 부서번호, 이름, 급여, 부서별 급여 순위를 출력하세요. 중복 순위 사원이 있을 경우 차순위는 없습니다. 이 결과에 이전 순위 사원의 급여를 추가하여 출력하세요.
정답 :
SELECT
department_id,
first_name,
salary,
RANK()
OVER (PARTITION BY department_id
ORDER BY salary DESC) AS sal_rank,
LAG(salary, 1, 0)
OVER (PARTITION BY department_id
ORDER BY salary DESC) AS prev_salary,
FIRST_VALUE(salary)
OVER (PARTITION BY department_id
ORDER BY salary DESC
ROWS 1 PRECEDING) AS prev_salary2
FROM employees
ORDER BY department_id
문제 범위 : 조인
1. John 사원의 이름과 부서이름, 부서위치(city)를 출력하세요.(오라클조인, 안시조인 구문 둘 다 작성하세요)
오라클 조인 :
SELECT e.first_name, d.department_name, l.city
FROM employees e, departments d, locations l
WHERE first_name='John'
AND e.department_id=d.department_id
AND d.location_id=l.location_id
안시 조인 :
SELECT e.first_name, d.department_name, l.city
FROM employees e
JOIN departments d ON e.department_id=d.department_id
JOIN locations l ON d.location_id=l.location_id
WHERE first_name='John'
2. 103번 사원의 사원번호, 이름, 급여, 매니저이름, 매니저 부서이름을 출력하세요.(안시조인으로 작성하세요)
안시 조인 :
SELECT e.employee_id, e.first_name, e.salary, m.first_name, d.department_name
FROM employees e
JOIN employees m ON e.manager_id=m.employee_id
JOIN departments d ON m.department_id=d.department_id
WHERE e.employee_id=103;
3. 90번부서 사원들의 사번, 이름, 급여, 매니저이름, 매니저급여, 매니저부서이름을 출력하세요.(오라클 조인과 안시조인 구문 둘 다 작성하세요)
안시 조인 :
SELECT e.employee_id, e.first_name, e.salary, m.first_name, m.salary,
d.department_name
FROM employees e
LEFT JOIN employees m ON e.manager_id=m.employee_id
LEFT JOIN departments d ON e.department_id=d.DEPARTMENT_ID
WHERE e.department_id=90;
오라클 조인 :
SELECT e.employee_id, e.first_name, e.salary, m.first_name, m.salary,
d.department_name
FROM employees e, employees m, departments d
WHERE e.manager_id=m.employee_id(+)
AND e.department_id=d.department_id
AND e.department_id=90;
4. 103번사원이 근무하는 도시는?(안시 조인 구문으로 작성하세요)
안시 조인 :
SELECT e.employee_id, l.city
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN locations l ON d.location_id = l.location_id
WHERE e.employee_id=103;
5. 사원번호가 103인사원의 부서위치(city)와 매니저의 직무이름(job_title)을 출력하세요.(안시 조인 구문으로 작성하세요)
안시 조인 :
SELECT l.city as "Department Location", j.job_title as "Manager's Job"
FROM employees e
JOIN departments d ON e.department_id=d.department_id
JOIN locations l ON d.location_id=l.location_id
JOIN employees m ON e.manager_id=m.employee_id
JOIN jobs j ON m.job_id=j.job_id
WHERE e.employee_id=103;
6. 다음 중 오류가 있는 라인은?
1: SELECT employee_id,
2: employees.first_name,
3: e.department_id,
4: d.department_id
5: FROM employees e, departments d
6: WHERE e.department_id = d.department_id
정답 :
2라인, 테이블 alias를 부여할 경우 테이블 이름을 select 절에 사용할 수 없습니다.
|