2022_11_14 SQL 정리
2022_1114
-----------인라인 뷰 (InlineVIew)
FROM아래에 select가 다시 들어감
rownum은 orderby를 하기전에 번호가 매겨지기 때문에 인라인 뷰로 순서를 결정하고 번호를 붙힘
테이블이 들어갈수 있는 위치는 인라인뷰가 들어갈 수 있다.
---------------------------------------------------------------------------------------------------------------------------
--인라인 뷰 (INLINE VIEW)
SELECT * FROM (SELECT * FROM EMPLOYEES);
SELECT rownum, first_name, job_id, salary
FROM employees
order by salary;
SELECT rownum, a.*
FROM (SELECT first_name, job_id, salary
FROM employees
order by salary) a
WHERE rownum <=10;
--------------------
--3중 인라인 뷰
SELECT rownum, aa.*
FROM (SELECT a.*, rownum AS rnum
FROM (SELECT first_name, job_id, salary
FROM employees
ORDER BY salary DESC)a
)aa
WHERE rnum between 11 and 20;
---------------------
--인라인 뷰 응용 (JOIN테이블의 ㅇ위치로 인라인뷰 삽입 가능 or 스칼라 쿼리와 혼합해서 사용 가능
--DEPARTMEENT테이블에서 MANAGER_ID 가 NULL이 아닌 데이터를 EMPLOYEES에 조인
SELECT*
FROM employees e
LEFT OUTER JOIN( SELECT *
FROM departments
WHERE manager_id IS NOT NULL) d
ON e.department_id = d.department_id;
--SALARY가 10000 이상인 직원의 정보 전부, 부서명 부서의 주소, JOB_TITLE을 출력. SALARY기준으로 내림차순
SELECT e.*,
d.department_name,
l.street_address,
j.job_title
FROM employees e
LEFT OUTER JOIN departments d ON e.department_id = d.department_id
LEFT OUTER JOIN locations l ON l.location_id = d.location_id
LEFT OUTER JOIN jobs j ON e.job_id = j.job_id
WHERE salary >= 10000;
--인라인뷰에 들어갈 구문
SELECT a.* ,
(SELECT street_address from locations l WHERE l.location_id = a.location_id) as street_address,
(SELECT job_title FROM jobs j WHERE j.job_id = a.job_id) as job_title
FROM (SELECT *
FROM employees e
LEFT OUTER JOIN departments d
ON e.department_id = d.department_id
WHERE salary >= 10000) a;
--문제 1.
---EMPLOYEES 테이블에서 모든 사원들의 평균급여보다 높은 사원들을 데이터를 출력 하세요 ( AVG(컬럼) 사용)
---EMPLOYEES 테이블에서 모든 사원들의 평균급여보다 높은 사원들을 수를 출력하세요
---EMPLOYEES 테이블에서 job_id가 IT_PFOG인 사원들의 평균급여보다 높은 사원들을 데이터를 출력하세요
SELECT *
FROM EMPLOYEES
WHERE salary >=(SELECT AVG(salary) FROM EMPLOYEES );
SELECT count(*)
FROM EMPLOYEES
WHERE salary >=(SELECT AVG(salary) FROM EMPLOYEES );
SELECT *
FROM EMPLOYEES
WHERE salary >=(SELECT AVG(salary) FROM EMPLOYEES WHERE job_id = 'IT_PROG');
--문제 2.
---DEPARTMENTS테이블에서 manager_id가 100인 사람의 department_id와
--EMPLOYEES테이블에서 department_id가 일치하는 모든 사원의 정보를 검색하세요.
SELECT * FROM employees e
where department_id = (select department_id from departments d WHERE manager_id = 100);
SELECT * FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
WHERE d.manager_id=100;
--문제 3.
---EMPLOYEES테이블에서 “Pat”의 manager_id보다 높은 manager_id를 갖는 모든 사원의 데이터를 출력하세요
SELECT * FROM employees
WHERE manager_id > (SELECT manager_id FROM employees WHERE first_name = 'Pat');
---EMPLOYEES테이블에서 “James”(2명)들의 manager_id와 갖는 모든 사원의 데이터를 출력하세요.
SELECT * FROM employees
WHERE manager_id = ANY (SELECT manager_id FROM employees WHERE first_name = 'James');
--문제 4.
---EMPLOYEES테이블 에서 first_name기준으로 내림차순 정렬하고, 41~50번째 데이터의 행 번호, 이름을 출력하세요
SELECT aa.* FROM(SELECT rownum as rnum, a.*
FROM( SELECT *
FROM employees
ORDER BY first_name DESC) a ) aa
where rnum between 41 and 50;
--문제 5.
---EMPLOYEES테이블에서 hire_date기준으로 오름차순 정렬하고, 31~40번째 데이터의 행 번호, 사원id, 이름, 번호,
--입사일을 출력하세요.
SELECT aa.*
FROM( SELECT rownum as r, employee_id, first_name, phone_number, hire_date
FROM (SELECT *
FROM employees
ORDER BY hire_date ASC)a ) aa
WHERE r between 31 and 40;
--문제 6.
--employees테이블 departments테이블을 left 조인하세요
--조건) 직원아이디, 이름(성, 이름), 부서아이디, 부서명 만 출력합니다.
--조건) 직원아이디 기준 오름차순 정렬
SELECT e.employee_id, CONCAT(first_name, last_name) as 이름, e.department_id, d.department_name
FROM EMPLOYEES E LEFT JOIN DEPARTMENTs D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
ORDER BY e.employee_id ASC;
--문제 7.
--문제 6의 결과를 (스칼라 쿼리)로 동일하게 조회하세요
SELECT e.employee_id, CONCAT(first_name, last_name) as 이름, e.department_id,
(SELECT d.department_name FROM departments d WHERE d.department_id = e.department_id) as department_name
FROM employees e
ORDER BY e.employee_id ASC;
--문제 8.
--departments테이블 locations테이블을 left 조인하세요
--조건) 부서아이디, 부서이름, 매니저아이디, 로케이션아이디, 스트릿_어드레스, 포스트 코드, 시티 만 출력합니다
--조건) 부서아이디 기준 오름차순 정렬
SELECT d.department_id, d.department_name, d.manager_id, d.location_id, l.street_address, l.postal_code, l.city
FROM departments d LEFT JOIN locations l ON d.location_id = l.location_id
ORDER BY d.department_id;
--문제 9.
--문제 8의 결과를 (스칼라 쿼리)로 동일하게 조회하세요
SELECT d.department_id , d.department_name, d.manager_id, d.location_id,
(SELECT l.street_address FROM locations l WHERE d.location_id = l.location_id) as street_address,
(SELECT l.postal_code FROM locations l WHERE d.location_id = l.location_id) as postal_code,
(SELECT l.city FROM locations l WHERE d.location_id = l.location_id) as city
FROM departments d
ORDER BY d.department_id;
--문제 10.
--locations테이블 countries 테이블을 left 조인하세요
--조건) 로케이션아이디, 주소, 시티, country_id, country_name 만 출력합니다
--조건) country_name기준 오름차순 정렬
SELECT l.location_id, l.street_address, l.city, l.country_id, c.country_name
FROM locations l LEFT JOIN countries c ON l.country_id = c.country_id
ORDER BY country_name ASC;
--문제 11.
--문제 10의 결과를 (스칼라 쿼리)로 동일하게 조회하세요
SELECT l.location_id, l.street_address, l.city, l.country_id,
(SELECT c.country_name FROM countries c WHERE l.country_id= c.country_id) as country_name
FROM locations l
ORDER BY country_name ASC;
--문제 12.
--employees테이블, departments테이블을 left조인 hire_date를 오름차순 기준으로 1-10번째 데이터만 출력합니다
--조건) rownum을 적용하여 번호, 직원아이디, 이름, 전화번호, 입사일, 부서아이디, 부서이름 을 출력합니다.
--조건) hire_date를 기준으로 오름차순 정렬 되어야 합니다. rownum이 틀어지면 안됩니다.
SELECT *
FROM( SELECT rownum rn,
a.*
FROM ( SELECT e.employee_id,
e.phone_number,
e.first_name,
e.hire_date,
e.department_id,
d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
ORDER BY hire_date)a
)
WHERE rn BETWEEN 1 AND 10;
--문제 13.
----EMPLOYEES 과 DEPARTMENTS 테이블에서 JOB_ID가 SA_MAN 사원의 정보의 LAST_NAME, JOB_ID,
--DEPARTMENT_ID,DEPARTMENT_NAME을 출력하세요
SELECT last_name,
JOB_id ,
department_id,
(SELECT department_name FROM departments d WHERE e.department_id = d.department_id) as department_name
FROM employees e
WHERE job_id = 'SA_MAN';
--문제 14
----DEPARTMENT테이블에서 각 부서의 ID, NAME, MANAGER_ID와 부서에 속한 인원수를 출력하세요.
----인원수 기준 내림차순 정렬하세요.
----사람이 없는 부서는 출력하지 뽑지 않습니다
SELECT d.department_id,
d.department_name,
d.manager_id,
(SELECT first_name FROM employees e WHERE d.manager_id = e.employee_id) as manager_name,
(SELECT COUNT(*) from employees e WHERE d.department_id = e.department_id) as ppl
from departments d
WHERE manager_id IS NOT NULL
ORDER BY ppl DESC;
SELECT *
FROM(SELECT d.*,
(SELECT COUNT(*) FROM employees e WHERE d.department_id = e.department_id GROUP BY d.department_id ) AS cnt
FROM departments d)
WHERE cnt IS NOT NULL
ORDER BY cnt DESC;
--문제 15
----부서에 대한 정보 전부와, 주소, 우편번호, 부서별 평균 연봉을 구해서 출력하세요
----부서별 평균이 없으면 0으로 출력하세요
SELECT d.* ,l.Street_address, l.postal_code,
NVL((SELECT TRUNC(AVG(SALARY)) FROM employees e WHERE e.department_id = d.department_id),0) AS avg
FROM departments d LEFT JOIN locations l ON d.location_id = l.location_id;
------
SELECT d.*,
l.Street_address,
l.postal_code,
nvl(a.result, 0) as avg
FROM departments d
LEFT JOIN ( SELECT department_id,
TRUNC(AVG(salary),0) as result
FROM employees
GROUP BY department_id) a
ON d.department_id = a.department_id
LEFT JOIN locations l
ON l.location_id = d.location_id
ORDER BY AVG DESC;
--문제 16
---문제 15결과에 대해 DEPARTMENT_ID기준으로 내림차순 정렬해서 ROWNUM을 붙여 1-10데이터 까지만
--출력하세요
SELECT *
FROM(SELECT rownum as rn,
da.*
FROM(SELECT d.* ,
l.Street_address,
l.postal_code,
NVL((SELECT TRUNC(AVG(SALARY)) FROM employees e WHERE e.department_id = d.department_id),0) AS department_avg
FROM departments d LEFT JOIN locations l ON d.location_id = l.location_id
ORDER BY department_id DESC) da)
WHERE rn<=10;
----------
SELECT *
FROM( SELECT rownum rn,
x.*
FROM (SELECT d.*,
l.Street_address,
l.postal_code,
nvl(a.result, 0) as avg
FROM departments d
LEFT JOIN ( SELECT department_id,
TRUNC(AVG(salary),0) as result
FROM employees
GROUP BY department_id) a
ON d.department_id = a.department_id
LEFT JOIN locations l
ON l.location_id = d.location_id
ORDER BY AVG DESC
)X
)
WHERE rn>10 AND rn<=20;
-------------------------------------------
DML문 = 데이터의 수정 삭제 추가
insert / update / delete
트랜잭션 - 작업의 논리단위 형태인 dml 문장의 모음
DML문이 실행될때 COMMIT이 들어가야 데이터가 최종적으로 수정된다
-그전엔 가상공간에 임시저장을 해둠