SELECT ASCII('A')
FROM dual
SELECT CHR(65)
FROM dual
<< 문자조합 >>
--ASCII로 표현가능한 문자
: 알파벳(A-Z/a-z), 숫자(0-9), 특수문자(\n, \t, \b...) -> 1byte
--MultibyteCharacterSet
: 각 국가나 기관에서 만든것 KSC5601, EUC-KR, MS949... -> 2byte
--UNICODE : 2byte로 구성된 표준화된 문자조합
--UTF-8 : 다국적언어는(ASCII로 표현할 수 없는 것들은) 2~3byte로표현) -> 1byte
--변환함수 TO_CHAR( )
SELECT employee_id, salary, TO_CHAR(salary, '999,999')
FROM employees
--NVL2(대상,대상이NULL이아닐경우,대상이NULL일경우)함수
SELECT employee_id, department_id, NVL2(department_id, '배치됨', '배치안됨')
FROM employees
SELECT salary
FROM employees
ORDER BY salary DESC
--급여가 15000이상인 경우에는 A
급여가 10000이상인 경우에는 B
급여가 5000이상인 경우에는 C
급여가 5000미만인 경우에는 D를 출력하시오.
SELECT employee_id, salary, CASE WHEN salary >= 15000 THEN 'A'
WHEN salary >= 10000 THEN 'B'
WHEN salary >= 5000 THEN 'C'
ELSE 'D'
END "등급"
FROM employees
--그룹함수
SELECT COUNT(*), COUNT(department_id)
FROM employees
--COUNT(*) : NULL값도 합계에 포함
--COUNT(컬럼명) : NULL값은 합계에서 제외
SELECT SUM(salary), AVG(salary), COUNT(salary), MIN(salary), MAX(salary)
FROM employees
--GROUP BY절
업무(job_id)별 사원수와 총급여, 평균급여를 출력하시오.
SELECT job_id, COUNT(*), SUM(salary), AVG(salary)
FROM employees
GROUP BY job_id
--GROUP BY절에서 사용하지 않은 일반컬럼은 그룹함수와 함께 SELECT절에 올 수 없다.
SELECT job_id, COUNT(*), SUM(salary), AVG(salary)
FROM employees
--급여가 4000보다 많은 사원들의 부서별 급여평균을 출력하시오. 단 급여평균은 소숫점이하 2자리에서 반올림합니다.
SELECT department_id, SUM(salary), ROUND(AVG(salary), 1)
FROM employees
WHERE salary > 4000
GROUP BY department_id
--급여평균이 7000이상인 부서별 급여평균을 출력하시오.
SELECT department_id, ROUND(AVG(salary),2)
FROM employees
GROUP BY department_id
HAVING AVG(salary) >= 7000
--GROUP BY에 대한 조건절은 HAVING에 써야한다. WHERE가 GROUP BY보다 우선순위기 때문에.
--급여평균이 7000이상인 부서별 급여평균, 최대급여, 최소급여를 출력하시오. 최대급여와 최소급여가 같은 부서는 출력에서 제외하시오.
SELECT department_id, ROUND(AVG(salary), 2), MAX(salary), MIN(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) >= 7000 AND MAX(salary)<>MIN(salary)
--최대급여를 출력하시오.
SELECT MAX(salary)
FROM employees
--최대급여를 받는 사원의 정보를 출력하시오. --> group 함수와 일반 함수를 같이 쓸 수 없다. 따라서 subquery가 필요함.
SELECT MAX(salary), employee_id, first_name
FROM employees
--JOIN : 여러테이블을 한꺼번에 Query
--각 부서가 속한 도시를 출력하시오.
SELECT COUNT(*)
FROM departments -- 27
SELECT COUNT(*)
FROM locations -- 23
--카티션곱
SELECT department_id, department_name, city
FROM departments, locations
SELECT department_id, department_name, city
FROM departments, locations
WHERE departments.location_id = locations.location_id
SELECT department_id, department_name, d.location_id, city
FROM departments d, locations l
WHERE d.location_id = l.location_id
AND l.location_id >= 1500
--ANSI JOIN : 표준화된 JOIN. 다른 DBMS에서도 사용가능. 어디까지가 일반 구문이고 어디까지가 JOIN구문인지 구별. JOIN구문은 ON절에쓰고 일반구문은 WHERE절에쓴다.
SELECT department_id, department_name, d.location_id, city
FROM departments d JOIN locations l
ON d.location_id = l.location_id
WHERE l.location_id >= 1500
--사원의 사번, 이름, 부서번호, 부서명, 업무번호, 업무명(job_title)출력하시오.
--Oracle JOIN
SELECT employee_id, first_name,
d.department_id, d.department_name,
j.job_id, j.job_title
FROM employees e, jobs j, departments d
WHERE e.department_id = d.department_id
AND e.job_id = j.job_id
--ANSI JOIN
SELECT employee_id, first_name,
d.department_id, d.department_name,
j.job_id, j.job_title
FROM employees e
JOIN jobs j ON e.job_id = j.job_id
JOIN departments d ON e.department_id = d.department_id
--부서별 사원수와 평균급여를 출력하시오. 부서번호, 부서명도 출력하시오.
--Oracle JOIN
SELECT COUNT(*), AVG(salary), d.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_id, d.department_name
--ANSI JOIN
SELECT COUNT(*), AVG(salary), d.department_id, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_id, d.department_name
--SELF JOIN
--사원의 사번, 이름, 관리자사번, 관리자이름을 출력하시오.
SELECT e.employee_id "사원의 사번", e.first_name "사원의 이름", e.hire_date "사원입사일자",
m.employee_id "관리자 사번", m.first_name "관리자 이름", m.hire_date "관리자입사일자"
FROM employees e, employees m
WHERE e.manager_id = m.employee_id
--사원의 입사일자보다 관리자 입사일자가 더 빠른 경우만 출력하시오.
--Oracle JOIN
SELECT e.employee_id "사원의 사번", e.first_name "사원의 이름", e.hire_date "사원입사일자",
m.employee_id "관리자 사번", m.first_name "관리자 이름", m.hire_date "관리자입사일자"
FROM employees e, employees m
WHERE e.manager_id = m.employee_id AND m.hire_date < e.hire_date
--ANSI JOIN
SELECT e.employee_id "사원의 사번", e.first_name "사원의 이름", e.hire_date "사원입사일자",
m.employee_id "관리자 사번", m.first_name "관리자 이름", m.hire_date "관리자입사일자"
FROM employees e INNER JOIN employees m ON e.manager_id = m.employee_id
WHERE m.hire_date < e.hire_date
--OUTER JOIN
SELECT e.employee_id "사원의 사번", e.first_name "사원의 이름", e.hire_date "사원입사일자",
m.employee_id "관리자 사번", m.first_name "관리자 이름", m.hire_date "관리자입사일자"
FROM employees e INNER JOIN employees m ON e.manager_id = m.employee_id
--관리자 없는 사원 1명, 부서없는 사원 1명
--사원의 사번, 이름, 부서번호, 부서명 출력하시오. 단, 부서가 없어도 출력합니다.
사원 이름 부서번호 부서명
XXX XXX XXX XXXX
YYY YYY / /
ZZZ ZZZ ZZZ ZZZZ
--Oracle JOIN
SELECT e.employee_id, e.first_name, d.department_id, department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+)
--ANSI JOIN
SELECT e.employee_id, e.first_name, d.department_id, department_name
FROM employees e LEFT OUTER JOIN departments d ON e.department_id = d.department_id
----------------------------------문제풀이-----------------------------------
1번 35개 IN써서
SELECT first_name
FROM employees
WHERE SUBSTR(first_name, 1, 1) IN('J', 'P', 'S')
2번
SELECT employee_id 사번, MONTHS_BETWEEN(SYSDATE, hire_date) 근무개월수, SYSDATE - hire_date 근무일수
FROM employees
3번 7개
SELECT first_name, hire_date
FROM employees
WHERE hire_date >= '94/01/01' AND hire_date <= '94/12/31'
4번 5개
SELECT d.department_name, first_name, hire_date
FROM employees e JOIN departments d ON e.department_id = d.department_id
WHERE d.department_name = 'IT'
5번 2개
SELECT first_name, last_name, hire_date, d.department_name
FROM employees e JOIN departments d ON e.department_id = d.department_id
WHERE e.last_name = 'Smith'
6번 1개
SELECT first_name, j.job_id
FROM employees e JOIN jobs j ON e.job_id = j.job_id
WHERE j.job_title = 'President'
7번 106개
SELECT first_name, j.job_id, job_title
FROM employees e JOIN jobs j ON e.job_id = j.job_id
WHERE j.job_title <> 'President'
8번 18개
SELECT d.department_id, d.department_name
FROM employees e JOIN departments d ON e.department_id = d.department_id
JOIN locations l ON d.location_id = l.location_id
WHERE l.city = 'Seattle'
9번 11개
SELECT d.department_id 부서번호, d.department_name 부서명,
ROUND(AVG(salary), 2) 평균급여, SUM(salary) 급여총액
FROM employees e JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_id, d.department_name
ORDER BY SUM(salary) ASC
10번 23개
SELECT e.first_name, d.department_name, 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 l.city IN( 'Seattle', 'Southlake' )
11번 5개
SELECT DISTINCT j.job_id, j.job_title
FROM employees e JOIN departments d ON e.department_id = d.department_id
JOIN jobs j ON e.job_id = j.job_id
WHERE d.department_id IN('50', '80')
12번 1개
SELECT m.first_name, m.last_name
FROM employees e JOIN employees m ON e.manager_id = m.employee_id
WHERE e.last_name = 'King'
13번 14개
SELECT e.first_name, e.salary, d.department_name, m.last_name
FROM employees e JOIN employees m ON e.manager_id = m.employee_id
JOIN departments d ON e.department_id = d.department_id
WHERE m.last_name = 'King' AND m.first_name = 'Steven'
14번 107개
SELECT e.employee_id 사번, e.first_name||' '||e.last_name 이름, m.employee_id "매니저ID", m.first_name||' '||m.last_name "매니저 이름"
FROM employees e LEFT OUTER JOIN employees m ON e.manager_id = m.employee_id
15번 34개
SELECT c.country_name, l.city, l.state_province
FROM locations l RIGHT OUTER JOIN countries c ON l.country_id = c.country_
16번 7개
SELECT l.city 도시, COUNT(d.department_id) 부서수
FROM departments d JOIN locations l ON d.location_id = l.location_id
GROUP BY l.city
17번 11개
SELECT l.city "CITY", d.department_name "부서명", COUNT(employee_id)
FROM employees e JOIN departments d ON e.department_id = d.department_id
JOIN locations l ON d.location_id = l.location_id
GROUP BY l.city , d.department_name
ORDER BY COUNT(employee_id) DESC
18번 2개
SELECT l.city "CITY", d.department_name "부서명", COUNT(employee_id)
FROM employees e JOIN departments d ON e.department_id = d.department_id
JOIN locations l ON d.location_id = l.location_id
GROUP BY l.city , d.department_name
HAVING COUNT(employee_id) >= 10
19번 23개
SELECT l.city, COUNT(department_id)
FROM departments d JOIN locations l ON d.location_id(+) = l.location_id
GROUP BY l.city
ORDER BY l.city ASC
=================================================노희석형
1. first_name이 'J', 'P', 'S'로 시작하는 사원들의 first_name을 출력하시오.
SELECT first_name
FROM employees
WHERE SUBSTR(first_name,1,1)='J'
OR SUBSTR(first_name,1,1)='P'
OR SUBSTR(first_name,1,1)='S'
2. 각 사원의 근무개월수, 근무일수와 사번을 출력하시오. 소수점값은 버립니다.
사번 근무개월수 근무일수
-------- ---------- --------
198 140 4285
199 134 4079
:
SELECT employee_id "사번",TRUNC(MONTHS_BETWEEN(SYSDATE,hire_date),0) "근무개월수" ,TRUNC(SYSDATE-hire_date,0) "근무일수"
FROM employees
3.입사년도가 94년도인 사원을 출력하시오.
SELECT *
FROM employees
WHERE SUBSTR(hire_date,1,2)='94'
4.IT부서(DEPARTMENT_NAME) 소속사원의 이름과 입사일을 출력하시오.
SELECT e.first_name,e.hire_date,d.department_name
FROM employees e JOIN departments d
ON e.department_id=d.department_id
WHERE department_name='IT'
5.last_name이 Smith인 사원의 입사일과 소속 부서명을 출력하시오.
SELECT first_name, last_name,hire_date,department_name
FROM departments d JOIN employees e
ON d.department_id=e.department_id
WHERE last_name='Smith'
6.업무명(job_title)이 President인 사원의 사원명과 업무(job_id)을 출력하시오.
SELECT e.first_name,e.last_name,e.job_id
FROM employees e JOIN jobs j
ON e.job_id=j.job_id
WHERE j.job_title='President'
7.업무명(job_title)이 President인 사원을 제외한 사원명과 직종(job_id)을 출력하시오.
SELECT e.first_name,e.last_name,e.job_id
FROM employees e JOIN jobs j
ON e.job_id=j.job_id
WHERE j.job_title <> 'President'
8.Seattle city에 있는 부서번호와 부서명을 출력하시오.
SELECT d.department_id,d.department_name,l.city
FROM departments d JOIN locations l
ON d.location_id = l.location_id
WHERE l.city='Seattle'
9.부서번호, 부서명, 부서별 급여총액과 평균급여를 출력하시오.
단, 조회결과는 급여총액이 낮은 순으로 정렬하고, 평균급여는 소수점 이하 1 자리까지 나타나도록 반올림해야 한다.
부서번호 부서명 평균급여 급여총액
---------- ------------ --------- --------
50 Shipping 3475.6 156400
80 Sales 8955.9 304500
SELECT d.department_id "부서번호",d.department_name "부서이름", ROUND(AVG(salary),1) "평균급여",SUM(salary) "급여총액"
FROM departments d JOIN employees e
ON d.department_id= e.department_id
GROUP BY d.department_id,d.department_name
ORDER BY SUM(salary) ASC
10.'Seattle' city나 'Southlake'city 에서 근무하는 사원명과 부서명을 출력하시오
SELECT e.first_name,e.last_name,d.department_name,l.city
FROM departments d JOIN employees e
ON d.department_id= e.department_id
JOIN locations l
ON d.location_id=l.location_id
WHERE l.city='Seattle' OR l.city= 'Southlake'
11.department_id가 50 이거나 80인 부서에 근무하는 사원들의 업무명(job_title)과 job_id을 출력하시오.
결과에서 직종(job_id)의 중복은 제거하시오
JOB_ID JOB_TITLE
-------------- --------------------
SA_REP Sales Representative
SH_CLERK Shipping Clerk
SA_MAN Sales Manager
ST_CLERK Stock Clerk
ST_MAN Stock Manager
SELECT DISTINCT j.job_title, e.job_id,e.department_id
FROM departments d JOIN employees e
ON d.department_id = e.department_id
JOIN jobs j
ON j.job_id = e.job_id
WHERE d.department_id='50' OR d.department_id='80'
12.last_name이 'King'인 사원의 매니저 이름(employees's Manager_id 참고) 출력하시오.
FIRST_NAME LAST_NAME
---------------------------------------- -----------
Karen Partners
SELECT m.first_name,m.last_name,e.manager_id
FROM employees e JOIN employees m
ON e.manager_id= m.employee_id
WHERE e.last_name='King'
13.
매니저가 [이름 last_name 'King'이고 first_name이 'Steven']인 사원의 이름과 급여와 소속 부서명을 출력하시오.
SELECT e.first_name,e.last_name,e.manager_id "매니저 번호",e.salary "급여",d.department_name "부서명"
FROM employees e JOIN employees m
ON e.manager_id= m.employee_id
JOIN departments d
ON e.department_id= d.department_id
WHERE m.last_name='King' AND m.first_name='Steven'
14. 사원의 사번, 사원이름과 사원의 매니저 이름을 출력하시오.
단, 매니저정보가 없어도 출력에 포함합니다.
사번 사원이름 매니저ID 매니저 이름
---- -------- --------- ------------
198 ~~~~ ~~~ ~~~~
100 Steven King
101 ~~~ ~~~~ ~~~~
:
SELECT e.first_name,e.last_name,e.manager_id "매니저 번호",m.first_name ,m.last_name
FROM employees e LEFT JOIN employees m
ON e.manager_id= m.employee_id
JOIN departments d
ON e.department_id= d.department_id
15. 모든나라의 나라이름(country_name), 도시명(city), 지역명(state_province)을 출력하시오.
SELECT DISTINCT c.country_name "나라이름" ,l.city "도시이름" ,l.state_province "지역명"
FROM countries c LEFT JOIN locations l
ON c.country_id = l.country_id
출처: <https://drive.google.com/drive/folders/0B0osDPEiFK08fkRyRmdZcE10bGtzdVVjb0dwRjRDMGgxSTJQemJ2M2dNNkxmd2JSTThCdHM>
16. 각 city에 있는 부서의 수를 출력하시오. city는 사전순으로 츨력합니다.
SELECT l.city "도시" , COUNT(d.department_id) "부서수"
FROM departments d JOIN locations l
ON d.location_id = l.location_id
GROUP BY l.city
ORDER BY city ASC
17. 각 city에 있는 부서들의 사원수를 출력하시오. city는 사전순으로 출력하고 같은 city에서 사원수가 많은 부서부터 출력하시오.
SELECT l.city "도시" ,d.department_name "부서명", COUNT(e.employee_id) "사원수"
FROM departments d JOIN locations l
ON d.location_id = l.location_id
JOIN employees e
ON d.department_id= e.department_id
GROUP BY l.city,d.department_name
ORDER BY city ASC, COUNT(e.employee_id) DESC
18. 각 city에 있는 부서들의 사원수를 출력하되 사원수가 10명 이상인 곳만 출력하시오.
SELECT l.city "도시" ,d.department_name "부서명", COUNT(e.employee_id) "사원수"
FROM departments d JOIN locations l
ON d.location_id = l.location_id
JOIN employees e
ON d.department_id= e.department_id
GROUP BY l.city,d.department_name
HAVING COUNT(e.employee_id) >=10
19. 각 city에 있는 부서의 수를 출력하시오. city는 사전순으로 츨력합니다. 단, 부서를 포함하지 않는 city도 모두 검색한다.
SELECT l.city "도시" ,COUNT(d.department_id) "부서수"
FROM departments d FULL JOIN locations l
ON d.location_id = l.location_id
GROUP BY l.city,d.department_name
ORDER BY l.city ASC
'공부 > KITRI(한국정보기술연구원) 공공데이터 기반 JAVA Programming과정' 카테고리의 다른 글
| KITRI 공공데이터 기반 JAVA Programming과정 6일차 (0) | 2015.05.09 |
|---|---|
| KITRI 공공데이터 기반 JAVA Programming과정 5일차 (0) | 2015.05.09 |
| KITRI 공공데이터 기반 JAVA Programming과정 4일차 (0) | 2015.05.09 |
| KITRI 공공데이터 기반 JAVA Programming과정 3일차 (0) | 2015.05.09 |
| KITRI 공공데이터 기반 JAVA Programming과정 1일차 (1) | 2015.05.08 |