SQL
1)
GROUP BY
--급여가 5000이상인 사원의 사번, 급여, 부서번호를 출력하시오
SELECT employee_id 사번, salary 급여, department_id 부서번호
FROM employees
WHERE salary>=5000;
--급여가 5000이상인 사원의 부서별 부서번호, 총급여를 출력하시오
SELECT department_id 부서번호, SUM(salary) 부서별총급여
FROM employees
WHERE salary>=5000;
GROUP BY department_id;
*having
--급여가 5000이상인 사원의 부서별 부서번호, 총급여를 출력하시오
-- 단, 부서별 총급여가 20000이하인 부서들만 출력하시오
SELECT department_id 부서번호, SUM(salary) 부서별총급여
FROM employees
WHERE salary >= 5000
GROUP BY department_id
HAVING SUM(salary)<=20000;
--부서별 직무별 부서번호 직무번호 사원수를 출력하시오.
--작은 부서번호부터, 사원수 많은직무부터 출력하시오.
SELECT department_id 부서번호, job_id 직무, COUNT(*) 사원 --부서별 직무를 담당하는 사원수
FROM employees
GROUP BY department_id, job_id
ORDER BY department_id, COUNT(*) DESC;
("더공부하세요" 하고 알려주시는 거임/ 따로 공부하면 좋음)
**소계, 총계구하려면 ROLLUP, CUBE
---부서별 최대급여를 출력하시오
SELECT department_id 부서번호, MAX(salary) 최대급여
FROM employees
GROUP BY department_id
ORDER BY 부서번호;
GROUPBY 절에서 사용한 컬럼만 SELECT절에서 여러행함수와 함께 사용할 수 있다.
--부서별 최대급여를 받는 사원의 사번, 이름, 급여를 출력하시오
--SUBQUERY로 해결
SELECT department_id 사번, employee_id 사번, first_name 이름, salary 급여
FROM employees
GROUP BY department_id (X)
--지역별 지역번호(location_id), 부서수를 출력하시오
SELECT location_id 지역번호, COUNT(*) 부서수
FROM departments
GROUP BY location_id;
--급여가 5000이상인 사원의 부서별 부서번호, 총급여를 출력하시오
-- 단, 부서별 총급여가 20000이하인 부서들만 출력하시오
--부서배치를 받지 못한 사원은 제외한다
SELECT department_id 부서번호, SUM(salary) 부서별총급여
FROM employees
WHERE salary >= 5000 AND department_id IS NOT NULL
GROUP BY department_id
HAVING SUM(salary)<=20000
ORDER BY 부서번호;
2) JOIN
JOIN 과 UNION
JOIN: 여러 테이블의 컬럼을 결합
UNION: 여러 테이블의 행을 결합
JOIN
SELECT employee_id, department_id
FROM employees; --107건
SELECT department_id, department_name
FROM departments; --27건
카티션곱(카티션프로덕트): 조건없는 조인 (결과 행*행)
SELECT employee_id, department_name
FROM employees, departments; --2889건
출력해야되는 대상이 여러테이블의 내용을 같이 출력해야 할 떄는 JOIN을 사용,
같은 테이블의 여러내용을 출력할때는 서브쿼리를 사용한다.
--표준(ANSI)조인 ('Eq join'이라고도 불림)
SELECT
FROM 테이블1 (INNER) JOIN 테이블2 ON(테이블1.컬럼 = 테이블2.컬럼)
(INNER) 가 생략됨
SELECT
FROM 테이블1 NATURAL JOIN 테이블2
SELECT
FROM 테이블1 JOIN 테이블2 USING 컬럼
--사원의 사번, 이름, 부서번호, 부서명을 출력하시오 --106건 (전체사원 107 / 부서없는사원 1 / 출력건수 106건)
SELECT employee_id 사번, first_name 이름, employees.department_id 부서번호, department_name 부서명
FROM employees
JOIN departments ON (employees.department_id = departments.department_id);
--사원의 사번, 이름, 부서번호, 부서명, 직무번호, 직무명을 출력하시오
SELECT employee_id 사번, employees.department_id 부서번호,
department_name 부서명 ,
jobs.job_id 직무번호, jobs.job_title 직무명
FROM employees
JOIN departments ON (employees.department_id =departments.department_id)
JOIN jobs ON(employees.job_id =jobs.job_id);
1) JOIN ON
--사원의 사번, 이름, 부서번호, 부서명을 출력하시오 (별칭주기)
SELECT employee_id 사번, first_name 이름, e.department_id 부서번호, department_name 부서명
FROM employees e
JOIN departments d ON (e.department_id =d.department_id);
--사원의 사번, 이름, 부서번호, 부서명, 직무번호, 직무명을 출력하시오(별칭주기)
SELECT employee_id 사번, e.department_id 부서번호,
department_name 부서명 ,
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);
--국가의 국가번호, 국가이름, 도시명을 출력하시오.
SELECT L.country_id 국가번호, C.country_name 국가이름, L.city 도시명
FROM LOCATIONS L
JOIN COUNTRIES C ON (L.country_id = C.country_id) ;
2) NATURAL JOIN
--사원의 사번, 직무번호, 직무명을 출력하시오
SELECT employee_id, jobs.job_id, jobs.job_title
FROM employees JOIN jobs ON (employees.job_id = jobs.job_id);
SELECT employee_id, job_id, job_title
FROM employees NATURAL JOIN jobs;
--사원의 사번, 부서번호, 부서명을 출력하시오
SELECT employee_id, d.department_id, d.department_name
FROM employees e JOIN departments d ON (e.department_id = d.department_id);
SELECT employee_id, department_id, department_name
FROM employees NATURAL JOIN departments ; (x)
SELECT employee_id, employees.department_id, department_name
FROM employees
JOIN departments ON(employees.department_id = departments.department_id
AND employees.manager_id = departments.manager_id);
3) JOIN USING
--사원의 사번, 직무번호, 직무명을 출력하시오
SELECT employee_id, jobs.job_id, jobs.job_title
FROM employees JOIN jobs ON (employees.job_id = jobs.job_id);
SELECT employee_id, job_id, job_title
FROM employees JOIN jobs USING (job_id);
--사원의 사번, 이름, 부서번호, 부서명, 직무번호, 직무명을 출력하시오
SELECT employee_id, e.department_id,
department_name
FROM employees JOIN departments USING(department_id);
-------------------------------------------------------------------------------
OUTER JOIN
--전체사원의 사번, 이름, 부서번호,부서명을 출력하시오 107건
SELECT employee_id 사번, first_name 이름, employees.department_id 부서번호, department_name 부서명
FROM employees
LEFT OUTER JOIN departments
ON (employees.department_id = departments.department_id);
LEFT/RIGHT있으면 (OUTER) 생략가능
아우터 조인은 이것도 출력됨
-- 국가의 국가번호(country_id), 국가이름(country_name), 도시명(city)을 출력하시오
--도시가 등록안된 국가도 출력한다
SELECT * FROM countries; --25건
SELECT * FROM locations; --23건
-- 국가의 국가번호(country_id), 국가이름(country_name), 도시명(city)을 출력하시오
--도시가 등록안된 국가도 출력한다
SELECT country_id, country_name, city
FROM countries LEFT JOIN locations USING (country_id);
SELECT country_id, country_name, city
FROM locations RIGHT JOIN countries USING (country_id);
--모든사원의 사번, 부서번호, 부서명을 출력하시오
--부서없는 사원도 출력한다. 107건
SELECT employee_id, e.department_id, department_name
FROM employees e LEFT JOIN departments d ON(e.department_id = d.department_id);
--모든사원의 사번, 부서번호, 부서명을 출력하시오
-- 사원이 없는 부서도 출력한다. 122건
SELECT employee_id, e.department_id, department_name
FROM employees e RIGHT JOIN departments d ON(e.department_id = d.department_id);
--모든사원의 사번, 부서번호, 부서명을 출력하시오
--부서없는 사원과 사원이 없는 부서도 출력한다 123건
SELECT employee_id, e.department_id, department_name
FROM employees e FULL JOIN departments d ON(e.department_id = d.department_id);
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
SELF JOIN
부모엔터티 = 자식엔터티 = 자기참조관계
--사원의 사번, 이름, 관리자번호, 관리자 이름을 출력하시오
SELECT e.employee_id 사번, e.first_name 이름, m.employee_id 관리자번호, m.first_name 관리자이름
FROM employees e JOIN employees m ON (e.manager_id = m.employee_id )
ORDER BY 관리자번호, 사번;
--사원의 사번, 이름, 사원부서, 관리자번호, 관리자, 관리자부서 이름을 출력하시오
SELECT e.employee_id 사번, e.first_name 이름, e.department_id 사원부서, m.employee_id 관리자번호, m.first_name 관리자이름, m.department_id 관리자부서
FROM employees e JOIN employees m ON (e.manager_id = m.employee_id )
ORDER BY 관리자번호, 사원부서;
--사원의 사번, 이름, 사원부서, 관리자번호, 관리자, 관리자부서 이름을 출력하시오
--사원부서와 관리자부서가 다른 사원들만 출력하시오
SELECT e.employee_id 사번, e.first_name 이름, e.department_id 사원부서, m.employee_id 관리자번호, m.first_name 관리자이름, m.department_id 관리자부서
FROM employees e JOIN employees m ON (e.manager_id = m.employee_id )
WHERE e.department_id <> m.department_id
ORDER BY 관리자번호, 사원부서;
--사원의 사번, 이름, 사원급여, 관리자번호, 관리자 이름, 관리자급여을 출력하시오
--사원급여가 관리자급여보다 많은 사람들만 출력한다.
SELECT e.employee_id 사번, e.first_name 이름, e.salary 사원급여, m.employee_id 관리자번호, m.first_name 관리자이름, m.salary 관리자급여
FROM employees e JOIN employees m ON (e.manager_id = m.employee_id )
WHERE e.salary > m.salary;
----------------------------------------------------------------------------------------------------------------------------------------------------------------
3) 집합
: UNION, UNION ALL(합집합), MINUS(차집합), INTERSECT(교집합) - 여러테이블의 행을 결합
UNION: 합 (중복제거)
UNION ALL: 합
MINUS: 차
INTERSECT: 교
--사원의 사번, 직무번호를 출력하시오
SELECT employee_id 사번, job_id 직무번호
FROM employees;
--사원경력정보(사원, 이전직무번호) 를 출력하시오
SELECT employee_id 사번, job_id 직무번호
FROM job_history;
--경력정보와 현재사원의 사번, 직무번호를 출력하시오 (115건)
SELECT employee_id , job_id
FROM employees
UNION
SELECT employee_id , job_id
FROM job_history
ORDER BY employee_id;
--경력정보와 현재사원의 사번, 직무번호를 출력하시오 (117건)
SELECT employee_id , job_id
FROM employees
UNION ALL
SELECT employee_id , job_id
FROM job_history
ORDER BY employee_id;
--이전직무와 다른직무를 담당하는 사원의 사번, 직무번호를 출력하시오
SELECT employee_id , job_id
FROM employees
MINUS
SELECT employee_id , job_id
FROM job_history
ORDER BY employee_id;
--이전직무와 같은직무를 담당하는 사원의 사번, 직무번호를 출력하시오
SELECT employee_id , job_id
FROM employees
INTERSECT
SELECT employee_id , job_id
FROM job_history
ORDER BY employee_id;
'JAVA 수업 > SQL 수업 기록' 카테고리의 다른 글
| 0619 수업 내용 (0) | 2023.06.19 |
|---|---|
| 0616 수업 내용 (2) | 2023.06.16 |
| 0614 수업 내용 (0) | 2023.06.14 |
| 0613 수업 내용 (2) | 2023.06.13 |
| 0612 수업내용 (0) | 2023.06.12 |
