-- RANK() : 동순위 표기 o, 다음 순위 표기x => 1, 2, 2, 4, 5, 6...
-- DENSE_RANK() : 다음 순위 표기 o => 1, 2, 3, 4, 5...
-- AVERAGE_RANK() : 현재 버전x, 평균 순위() ==> 동순위 1, 2, 3.5,3.5,4....
-- * 세계에서 가장 많이 판매된 오라클 버전이 11g ==> 오래된 버전에서는 존재 ==> 현업에서 여전히 사용하는 기업 존재
-- * 키보드 F1 : SQL Developer 도움말 vs 오라클 21c references (영문) --> 블로그, 구글 검색, 유튜브...
SELECT employee_id, first_name,
RANK() OVER (ORDER BY salary DESC) AS Ranking,
salary,
salary * 12 + NVL(commission_pct, 0) * salary AS 순급여,
department_id
FROM employees;
--ORDER BY : 특별한 경우를 제외하고, 항상 SQL 문장 마지막에
-- ROLLUP(), CUBE() : 합계, 소계 => 누적합을 조회할 때 사용 ==> 회계프로그램 개발시에 활용할 가능성
-- * 분석용 함수
-- 1) 부서별 급여 합, 급여 평균을 조회 (부서번호 기준으로, 오름차순 정렬)
SELECT department_id, SUM(salary), ROUND(AVG(salary))
FROM employees
GROUP BY ROLLUP(department_id)
ORDER BY department_id;
-- 1) 부서별 급여 합, 급여 평균, 급여 소계, 총계를 조회 (부서번호 기준으로, 오름차순 정렬)
-- 쿼리실행(Ctrl + Enter)할 경우는 소계가 표시되지 않으므로, F5를 눌러서 확인
SELECT department_id, MIN(salary), MAX(salary), SUM(salary), ROUND(AVG(salary))
FROM employees
GROUP BY CUBE(department_id)
ORDER BY department_id;
- SQL DEVELPOER로 ERD 확인
파일>Modeler>임포트>데이터 딕셔너리 임포트 마법사
>다음
항목 전부 선택> 다음
완료
로딩 완료
ERD 그림으로 저장
파일>Data Modeler > 다이어그램 인쇄(G) > 이미지 파일에
-- 조인 연산
-- JOIN : 데이터베이스에서 여러 정보가 테이블 단위로 조각나 있는데, 이것을 마치 하나의 테이블인것처럼
-- 여러 조각난 데이블의 공통 컬럼을 기준으로 합쳐서 조회/처리 하는 방법
-- *FROM 절에 테이블을 여러개 나열 ==> 조인!
-- * 조인 연산 vs SET(집합) 연산 차이
-- 1) 테이블을 수평으로 연결하는 방법 ==> 공통 컬럼이 필요 (관계, 연관이 있어야 조인 가능)
-- 2) 서로 관련없는 테이블 조회 결과를 수직으로 연결하는 ==> 컬럼의 갯수, 자료형이 같은지만 중요
-- * SET 연산은 수학의 '집합' 개념을 데이터베이스에 적용 ==> 서로 관련 없는 테이블의 데이터를 조회할 수 있도록
-- 1. 오라클 조인
-- 1) 카테시안 곱(Catesian Product) : 올바른 조인x ==> 조인 조건을 작성하지 않았을 때 발생하는 잘못된 데이터 요청결과
-- 오류는 발생하지 않지만, 요청한 것 이상의 많은 데이터를 반환 ==> 대기시간, 응답시간 지연
-- 2) EQUI JOIN (Equal) --> 동등 연산자 = 를 사용하는 조인 연산
-- Q. 사원정보(사번, 이름)와 부서정보(부서번호, 부서이름)을 조회하시오
SELECT e.employee_id, e.first_name, d.department_id, d.department_name
FROM employees e, departments d
WHERE NVL(e.department_id,0) = d.department_id; -- 106 row, 실제 107명인데 1명이 부서가 NULL
-- *NULL은 비교나 연산의 대상이 되지 않기 때문에 반드시 NULL 처리 함수 : NVL(), NVL2(), COALESCE(), NULLIF()..함수로
-- 올바르게 처리한후 사용
-- * 1명이 NULL인 사원?
SELECT *
FROM employees
WHERE department_id IS NULL;
-- 178 Kimberly Grant 부서코드:NULL
-- 에퀴조인 : 조인 조건 + 일반 조건
-- Q. 100번 사원의 정보와 사원이 소속된 부서이름을 함께 조회하려면?
-- 논리 연산자 : AND, OR, NOT
SELECT e.employee_id, e.fist_name, e.job_id, d.department_id
FROM employees e, departments d
WHERE NVL(e.department_id, 0) = d.department_id -- 조인 조건
AND e.employee_id = 100; -- 일반 조건 : 100번 사원의 정보와 사원이 소속된 부서 이름 정보를 함께 조회
--Q. 10번에서 30번까지 근무하는 사원들의 정보와 각 사원이 소속된 부서의 정보를 조회
SELECT e.employee_id, e.first_name, e.job_id, e.department_id, d.department_id
FROM employees e, departments d
WHERE NVL(e.department_id, 0) = d.department_id -- 조인 조건
AND e.department_id BETWEEN 10 AND 50;
-- NON-EQUI JOIN : 동등 연산자 = 이외의 비교 연산자를 사용하는 조인 연산, 거의 사용하지 않는 방식
-- # 비교 연산자(>, >=, <, <=, !=), 범위비교연산자 BETWEEN 등을 사용하는 조인 연산
-- Q. 사원들 중엥서 급여의 최대, 최소 범위에 있는 사원정보를 조회하고자 한다.
-- 사원정보 : employees (사번, 이름 ,급여, 부서, 입사일자..)
-- 업무정보 : jobs (업무코드, 업무 제목, 최저 급여, 최대 급여)
SELECT e.employee_id, e.first_name, e.salary, j.job_title
FROM employees e, jobs j -- 2033 rows : 107 x 19 (카테시안 곱/ 데카르트 곱(곱집합))
WHERE e.salary BETWEEN j.min_salary AND j.max_salary
AND e.department_id = 10;
-- 3) OUTER JOIN : 아우터 조인 ==> 외부 조인 vs 내부 조인 INNR JOIN
-- * 오라클은 INNER JOING이라는 명칭 x, 대신 EQUI_JOIN이 반대되는 개념의 INNER_JOIN 처럼 취급
-- * 내부 조인 : 공통적으로 만족하는 컬럼을 기준으로 조인 연산
-- * EQUI JOIN (INNER JOIN의 개념 o / 명칭 x) 에서는 공통으로 만족하는 행만 그 결과로 반환, 누락된 값은 생략되었지만
-- OUTER JOIN은 누락된 값까지 포함하는 조인 연산
-- 4-1) EQUI JOIN : 사원정보와 부서명 정보를 함께 조회 - 누락 데이터 생략
SELECT e.employee_id, e.first_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id; -- 전체 사원정보에서 부서가 없는 1명이 누락된 결과, 106 row
-- 4-2) OUTER JOIN : 사원정보와 부서명 정보를 함께 조회 - 누락 데이터 생략
-- * 아우터 : 누락된 데이터(행), 컬럼이 NULL이어서 조인에서 누락시키는 데이터
SELECT e.employee_id, e.first_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+); -- 전체 사원정보에서 부서가 없는 1명이 포함된 결과, 107rows
-- 5) SELF JOIN : 셀프 조인
-- * 사원 테이블과 사원테이블 내의 매니저(manager_id)의 관계 : 스스로를 참조하는 연산 --> 셀프 조인
-- * 모델링 : 셀프 x --> 순환 참조 관계 : 식별(실선), 비식별(점선) 관계 (일반적인 연관성을 설명)
-- * 매니저도 사원이다. 즉, 매니저도 사원 번호를 기준으로 관리된다.
-- * 하나의 테이블을 두번 명시하는 방식의 조인연산
-- Q. 사원정보(사원, 이름, 업무)와 매니정보(매니저 이름을 조회한다.)
SELECT emp.employee_id, emp.first_name, emp.job_id, mgr.first_name AS manager_name
FROM employees emp, employees mgr
WHERE emp.manager_id=mgr.employee_id
ORDER BY emp.employees_id;
-- 2.표준 조인 (ANSI 조인) : 오라클이 아닌 다른 DBMS(예: MSSQL, MySQL, MARIADB..관계형 DBMS에서)
-- 비관계형 데이터베이스(NoSQL: Not Only SQL)에서는 ORM(Object Relational Mapping), ODM(Object Document Mapping) 방식을 사용
-- * ANSI(Natioanl Standards Institute) : 미국 국가 표준 협회에서 제정한 여러 DBMS의 공통 조인방식을
-- ANSI 조인이라고 함.
-- 1) INNER JOIN : 내부 조인 ==> 공통 칼럼 기준으로 (오라클의 EQUI-JOIN과 같은 방식)
-- 2) OUTER JOIN : 외부 조인 ==> 오라클의 OUTER JOIN과 같은 방식
-- 3) NATURAL JOIN : 공통 컬럼을 기준으로 (컬럼이름, 타입 같을 때, 오라클의 EQUI JOIN처럼)
-- * 오라클 조인 -- 다른 DBMS에서 조인 연산 할 수 있께 만들어둔 국제 표준 조인 방식
erd cloud
ERDCloud
Draw ERD with your team members. All states are shared in real time. And it's FREE. Database modeling tool.
www.erdcloud.com
순환참조 : 셀프조인
BOM : 자재 명세서
-- ○ 오라클 조인 연습문제
-- 1. 이름에 소문자 v가 포함된 모든 사원의 사번, 이름, 부서명을 조회하는 쿼리문을 작성한다.
-- 문자열 패턴 : LIKE 연산자
SELECT e.employee_id, e.first_name, d.department_name
FROM employees e, departments d
where e.department_id = d.department_id
AND LOWER(e.first_name) LIKE '%v%'; -- 10 rows
-- 2. 커미션을 받는 사원의 사번, 이름, 급여, 커미션 금액, 부서명을 조회하는 쿼리문을 작성한다.
-- 단, 커미션 금액은 월급여에 대한 커미션 금액을 나타낸다.
-- NVL 처리 함수 : NVL, NVL2, COALESCE
SELECT e.employee_id, e.first_name,TO_CHAR( e.salary,'999,999'),e.salary * NVL(e.commission_pct, 0) as commission, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND e.commission_pct IS NOT NULL;
-- 3. 각 부서의 부서코드, 부서명, 위치코드, 도시명, 국가코드, 국가명을 조회하는 쿼리문을 작성한다.
SELECT d.department_id, d.department_name, l.city, l.location_id, c.country_id
FROM departments d,locations l, countries c
WHERE l.location_id = d.location_id
AND l.country_id = c.country_id
ORDER BY 1;
-- 4. 사원의 사번, 이름, 업무코드, 매니저의 사번, 매니저의 이름, 매니저의 업무코드를 조회하여 사원 의 사번 순서로 정렬하는 쿼리문을 작성한다.
-- 사원의 사번 순서로 정렬하는 쿼리문을 작성한다.
-- SELF 조인
SELECT e.employee_id, e.first_name, e.job_id, e.manager_id,
m.first_name AS manager_name, m.job_id as manager_job
FROM employees e, employees m
WHERE NVL(e.manager_id,0) = m.employee_id
AND e.manager_id IS NOT NULL
ORDER BY e.employee_id;
-- 5. 모든 사원의 사번, 이름, 부서명, 도시, 주소 정보를 조회하여 사번 순으로 정렬하는 쿼리문을 작 성한다.
SELECT e.employee_id, e.first_name, d.department_name, l.city, l.street_address
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id(+)
AND d.location_id = l.location_id
ORDER BY e.employee_id;