SQL - 4 (JOIN)

728x90

-- Q) 직원의 이름, 급여와 부서명을 출력하시오.

SELECT frist_name 이름, salary 급여, department_name 부서명
FROM employees, departments;

-- 답은 나왔지만 오답이다.

-- cartesian product

-- join :

(1) 테이블을 2개 이상

(2) pk = fk

SELECT first_name 이름, salary 급여, department_name 부서명
FROM employees, departments 
WHERE department_id = department_id; -- pk=fk

-- 테이블명.칼럼명

-- join (equi join)

-- oracle join vs ansi1999

--web document.all document.getElementById()
-- IE
-- ANSI , ISO/IEC 1992, 1999

-- oracle join
-- equi join , non-equi join, self-join, outer join

-- Q) 직원의 이름, 매니저아이디, 지역아이디를 조회 출력하시오
-- (단, 이름순으로 정렬)

SELECT e.first_name 이름, e.manager_id 메니저아이디, d.location_id 지역아이디
FROM employees e, departments d
WHERE e.department_id =d.department_id
order by 1;

-- Q) 직원의 이름, 매니저아이디, 지역아이디를 출력하시오.
-- 단, manager_id가 120이상인 경우만
-- (단, 이름순으로 정렬)

SELECT e.first_name 이름, e.manager_id 매니저아이디, d.location_id 지역아이디
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND e.manager_id >=120
--where employees.department_id = departments.department_id;
order by 1;

-- Q) 전체 사원의 이름과 직책(job_title)을 조회 출력하시오.

SELECT e.first_name 사원명, j.job_title 직책
FROM employees e, jobs j
WHERE e.job_id = j.job_id 
ORDER BY 1;

-- Q) 전체 직원의 이름, 부서명, 해당 city를 조회 출력하시오.
-- (단, city 오름차순으로)

SELECT e.first_name 이름, d.department_name 부서명, l.city 도시명
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id 
AND d.location_id = l.location_id
ORDER BY 3;

-- Q) 도시별 사원수를 조회 출력

SELECT l.city 도시명, COUNT(*) 사원수 
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id
GROUP BY l.city;

-- Q) 부서이름과 해당 부서가 속한 도시명(city)을 조회출력하시오.

SELECT d.department_name 부서이름, l.city 도시명
FROM departments d, locations l
WHERE d.location_id = l.location_id;

-- Q) 사원의 이름과 해당 사원이 근무하는 국가명을 조회출력하시오.

SELECT e.first_name 이름, c.country_name 국가명
FROM employees e, departments d, locations l, countries c
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id
AND l.country_id = c.country_id
ORDER BY 1;

-- Q) 직책(job_title)이 'representative' 인 사람의 이름, 직책, 부서명을 조회 출력하시오.
-- Sales representative(판매직원), Public Relations Representative(홍보직원)
-- (cf) 대표 : president, 대표이사 : CEO, 과대표 : class representative

-- (cf) 테이블의 JOIN시 sql tunning을 생각해볼 때
-- driving table

SELECT e.first_name 이름, j.job_title 직책,   d.department_name 부서명
FROM jobs j, employees e, departments d
WHERE e.job_id = j.job_id
AND e.department_id = d.department_id 
AND LOWER(j.job_title) like '%representative%'
ORDER BY 1;

-- Q) 직책(job_title)이 'clerk'인 사람의 이름, 직책, 부서명을 조회출력하시오.

SELECT e.first_name 이름, j.job_title 직급, d.department_name 부서명
FROM jobs j, employees e, departments d
WHERE e.job_id = j.job_id
AND e.department_id = d.department_id
AND LOWER(j.job_title) LIKE '%clerk%';

-- Q) 도시별로 그리고 같은 도시일 경우 다시 부서명별로 묶어 2008년까지 입사한 사원수를
-- 조회하여 출력하시오. 출력은 도시이름, 부서명 해당 사원수를 출력하며
-- 만일 출력시 도시이름 오름차순으로 정렬하되 같은 도시이면 사원이 많은 순부터 출력하시오.

SELECT l.city 도시명, d.department_name 부서명, count(*) "부서 사원수"
FROM departments d, locations l, employees e
WHERE e.department_id = d.department_id AND d.location_id = l.location_id
AND SUBSTR(e.hire_date, 1 ,2) <=08
GROUP BY l.city, d.department_name
ORDER BY 1 ASC, 3 DESC;

-- Q) 직책명(job_title)이 ‘Sales’로 시작하는
-- 직원 중에서 급여(salary)가 10,000 이상, 20,000 이하인 직원들의
-- 이름(first_name), 성(last_name)과 연봉(salary) 및 직책(job_title)을 조회 출력하시오.
-- (단, 급여가 많은 순으로 출력하시오)

SELECT e.first_name 이름, e.last_name 성, e.salary*12 연봉, j.job_title 직책
FROM employees e, jobs j
WHERE e.job_id=j.job_id
AND j.job_title LIKE 'Sales%'
AND e.salary BETWEEN 10000 AND 20000 
ORDER BY e.salary DESC;

-- kimberly를 해결하자. 107명 ==> 106명

-- Q) 전체 사원의 이름, 급여, 부서명 출력

SELECT e.first_name 이름,e.salary 급여,d.department_name 부서명
FROM employees e,departments d
WHERE e.department_id = d.department_id;

-- 106명 / equi join pk == fk 특성 때문

□ OUTER JOIN

equi join (pk == fk) + 안 나온 것도 나오게 하는 것.

-- Q) 사원의 이름, 급여, 부서명 출력
-- 단, 부서가 없는 kimberly도 나오게 하시오.

SELECT e.first_name 이름, e.salary 급여, d.department_name 부서명
FROM employees e, departments d
WHERE e.department_id = d.department_id(+);

-- Q) 사원의 이름, 급여, 부서명 출력
-- 단, 사원없이 부서이름만 있는 부서명도 출력하게 하시오.

SELECT e.first_name 이름, e.salary 급여, d.department_name 부서명
FROM employees e, departments d
WHERE e.department_id(+) = d.department_id;

-- Q) 사원의 이름, 급여, 부서명 출력
-- 단, 부서가 없는 kimberly도 나오게하시오
-- 그리고 사원없이 부서이름만 있는 부서명도 출력하게 하시오.

SELECT e.first_name 이름, e.salary 급여, d.department_name 부서명
FROM employees e, departments d
WHERE e.department_id(+) = d.department_id(+);

-- 틀린 방법

해결방법 1) (oracle)

집합 : 합집합, 교집합, 차집합

UNION, UNION ALL(중복), INTERSECT, MINUS

SELECT e.first_name 이름, e.salary 급여, d.department_name 부서명
FROM employees e, departments d
WHERE e.department_id(+) = d.department_id
UNION ----------------
SELECT e.first_name 이름, e.salary 급여, d.department_name 부서명
FROM employees e, departments d
WHERE e.department_id = d.department_id(+);

해결방법 3) (ANSI)

-- Q) 사원의 이름, 급여, 부서명 출력
-- 단, 부서가 없는 kimberly도 나오게 하시오.

1) ansi 방식

SELECT e.first_name 이름, e.salary 급여, d.department_name 부서명
FROM employees e LEFT OUTER JOIN departments d -- 이름 나오는 쪽 left
ON (e.department_id = d.department_id);

2) oracle

SELECT e.first_name 이름, e.salary 급여, d.department_name 부서명
FROM employees e, departments d
WHERE e.department_id = d.department_id(+); -- null 나오는 쪽 (+)

-- Q) 사원의 이름 급여 부서명 출력
-- 단, 사원없이 부서이름만 있는 부서명도 출력하게 하시오.

1) ansi 1999

SELECT e.first_name 이름, e.salary 급여, d.department_name 부서명
FROM employees e RIGHT OUTER JOIN departments d -- 부서명 나오는 쪽 left
ON (e.department_id = d.department_id);

2) oracle

SELECT e.first_name 이름, e.salary 급여, d.department_name 부서명
FROM employees e, departments d
WHERE e.department_id(+) = d.department_id;

-- Q) 사원의 이름, 급여, 부서명 출력
-- 단, 부서가 없는 kimberly도 나오게 하시오.
-- 그리고 사원없이 부서이름만 있는 부서명도 출력하게 하시오.

1) ansi

SELECT e.first_name 이름, e.salary 급여, d.department_name 부서명
FROM employees e FULL OUTER JOIN departments d 
ON (e.department_id = d.department_id);

2) oracle

SELECT e.first_name 이름, e.salary 급여, d.department_name 부서명
FROM employees e, departments d
WHERE e.department_id(+) = d.department_id
UNION
SELECT e.first_name 이름, e.salary 급여, d.department_name 부서명
FROM employees e, departments d
WHERE e.department_id = d.department_id(+) ;

-- Q) 2007년 입사한 직원들의 사번, 이름, 성(last_name), 입사일, 부서명을 출력
-- 만일 부서에 배치되지 않은 직원의 경우,
-- ‘그런부서없어’로 보여주고 정렬은 성(last_name) 을 기준 출력
-- 단, 오라클 방식 및 ansi 1999 로 푸시오.

1) oracle

SELECT e.employee_id 사번, e.first_name 이름, e.last_name 성, e.hire_date 입사일, NVL(d.department_name, '그런부서없어') 부서명
FROM employees e, departments d
WHERE e.department_id = d.department_id(+)
AND e.hire_date LIKE '07%'
ORDER BY last_name;

employees의 부서아이디를 추가로 출력하되 null이면 '없어' 출력

SELECT e.employee_id 사번, e.first_name 이름, e.last_name 성, e.hire_date 입사일, NVL(d.department_name, '그런부서없어') 부서명,
        NVL(TO_CHAR(e.department_id), '없어') 부서아이디
FROM employees e, departments d
WHERE e.department_id = d.department_id(+)
AND e.hire_date LIKE '07%'
ORDER BY last_name;

2) ansi

SELECT e.employee_id 사번, e.first_name 이름, 
    e.last_name 성, e.hire_date 입사일, NVL(d.department_name, '그런부서없어') 부서명
FROM employees e LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id)
WHERE e.hire_date LIKE '07%'
ORDER BY last_name;

그렇다면 WHERE에 있는 조건을 ON 에 추가로 넣으면 어떻게 될까?

SELECT e.employee_id 사번, e.first_name 이름, 
    e.last_name 성, e.hire_date 입사일, NVL(d.department_name, '그런부서없어') 부서명
FROM employees e LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id AND e.hire_date LIKE '07%')
ORDER BY last_name;

-- 문제발생 (PK == FK에서)

-- 즉 department_id == (department_id and 2007년)

-- Q) 사원의 사번, 이름, 매니저 아이디를 조회 출력하시오.

SELECT employee_id 사번, first_name 이름, manager_id 매니저아이디
FROM employees;

-- Q) 사원의 사번, 이름, 매니저이름을 조회 출력하시오
-- 매니저이름 컬럼은 어디에? ==> employees 에 숨어 있음.
-- (별도 컬럼 x)

□ 셀프조인

eqi join + 같은 테이블 2개

SELECT e.employee_id 사번, e.first_name 사원명, m.first_name 매니저이름 
FROM employees e, employees m
WHERE e.manager_id = m.employee_id
ORDER BY 1; -- kimbery o, steven x

kimbery는 나오지만, steven은 나오지 않는다. steven의 매니저 아이디는 null이기 때문이다.

따라서

SELECT e.employee_id 사번, e.first_name 사원명, m.first_name 매니저이름 
FROM employees e, employees m
WHERE e.manager_id = m.employee_id(+)
ORDER BY 1; -- kimbery o, steven o

다음과 같이 하면 (+) steven의 행도 출력된다.

NVL을 이용해서 null 값을 처리해주면

SELECT e.employee_id 사번, e.first_name 사원명, NVL(m.first_name, '매니저없음') 매니저이름 
FROM employees e, employees m
WHERE e.manager_id = m.employee_id(+)
ORDER BY 1;

-- Q) 매니저이름과 해당 매니저가 관리하는
-- 사원의 수를 출력하시오.
-- (단, 관리되는 사원수가 5명 이상인 관리자만 조회 출력)

실습용 테이블 생성

CREATE TABLE tel77(
      sabun  NUMBER(5),
      irum   VARCHAR2(20),
      tel    VARCHAR2(20),
      salary NUMBER(7)
      );
      
INSERT INTO tel77
VALUES (1,'hong','010-777-8888',5000000);

INSERT INTO tel77
VALUES (2,'choi','010-5555-8888',3000000);

COMMIT;

-- Q) 전사원의 급여를 10% 인상

UPDATE tel77
SET salary = salary * 1.1;

'hong' 이름 변경

UPDATE tel77
SET irum = 'park'
WHERE irum = 'hong';

commit;

UPDATE tel77
SET salary = salary * 1.2; -- 아직 db로 x

rollback;

-- 롤백, 세이브포인트 연습

SELECT * FROM tel77;

UPDATE tel77
SET salary = salary * 1.3;

SAVEPOINT yoki;

INSERT INTO tel77(sabun)
VALUES (10);

SELECT * FROM tel77;

SAVEPOINT jeoki; 

ROLLBACK yoki;

COMMIT;

-- Q) 사원의 봉급 인상
-- 직책에 'Manager'가 있으면 10%인상
-- 그외는 20% 인상
-- (출력은 이름, 직책, 봉급, 인상된봉급)

자바의 경우에는 if-else 사용

SQL에서는 decode()를 사용

□ DECODE

DECODE(대상칼럼, 조건, yes처리1, no처리2)

-- 예) DECODE(salary, 10000, salary * 1.1, salary * 1.2)

-- 과정1)

SELECT e.first_name 이름, j.job_title 직책, e.salary 봉급
FROM employees e, jobs j
WHERE e.job_id = j.job_id;

-- 과정2)

SELECT e.first_name 이름, j.job_title 직책, e.salary 봉급,
              DECODE(SUBSTR(job_title, -7), 'Manager', salary * 1.1, salary * 1.2) 인상된봉급
FROM employees e, jobs j
WHERE e.job_id = j.job_id;

-- Q) 사원의 봉급 인상
-- 직책에 'Manager'가 있으면 10% 인상 Accountant 20% , 그외는 30%
-- 그외는 20% 인상
-- (출력은 이름, 직책, 봉급, 인상된봉급)
-- (step3) case - when

case when - then -
     when - then -
     when - then -
     else
           
     end

SELECT e.first_name 이름, j.job_title 직책, e.salary 봉급,   
CASE WHEN SUBSTR(job_title, -7) = 'Manager' THEN e.salary * 1.1
     WHEN SUBSTR(job_title, -7) = 'ountant' THEN e.salary * 1.2                    
     ELSE e.salary * 1.3
     END AS 인상된봉급
FROM employees e, jobs j
WHERE e.job_id = j.job_id;

-- Q) 직원의 사번, 사원명, 매니저이름을 출력
-- (단, 매니저가 없는 직원도 출력. 출력형태는 '매니저없어!!!', union사용)

-- 1) steven 없이 (self join 사용)

SELECT e.employee_id 사번, e.first_name 사원명, m.first_name 매니저이름
FROM employees e, employees m
WHERE e.manager_id = m.employee_id;

-- 2) steven 을 해결

SELECT employee_id 사번, first_name 사원명, '매니저없어!!!'
FROM employees
WHERE manager_id IS NULL;

-- 3) 두개를 합쳐서 실행, union (union all, union distinct)

SELECT e.employee_id 사번, e.first_name 사원명, m.first_name 매니저이름
FROM employees e, employees m
WHERE e.manager_id = m.employee_id     
UNION    
SELECT employee_id 사번, first_name 사원명, '매니저없어!!!'
FROM employees
WHERE manager_id IS NULL;

-- Q) 직원의 사번, 사원명, job아이디 조회 출력
-- (단, job_id가 'Clerk'이 들어간 사원은 제외하시오.)

차집합 (minus) 사용

SELECT employee_id 사번, first_name 사원명, job_id job아이디
FROM employees -- 107명
MINUS  
SELECT employee_id 사번, first_name 사원명, job_id job아이디
FROM employees
WHERE LOWER(job_id) LIKE '%clerk%'; -- 45명

□ 실습

DROP TABLE SALGRADE;

CREATE TABLE SALGRADE( 
  GRADE NUMBER(5) NOT NULL,
  LSAL NUMBER(8),
  HSAL NUMBER(8),
  CONSTRAINT SALGRADE_GRADE_PK PRIMARY KEY(GRADE)
);            

INSERT INTO SALGRADE(GRADE, LSAL, HSAL) VALUES(1, 500, 2999);
INSERT INTO SALGRADE(GRADE, LSAL, HSAL) VALUES(2, 3000, 3799);
INSERT INTO SALGRADE(GRADE, LSAL, HSAL) VALUES(3, 3800, 5899);
INSERT INTO SALGRADE(GRADE, LSAL, HSAL) VALUES(4, 5900, 9999);
INSERT INTO SALGRADE(GRADE, LSAL, HSAL) VALUES(5, 10000, 70000);
 
COMMIT;

-- Q) 급여가 3800인 사원의 급여등급을 조회출력하시오.

SELECT grade 급여등급
FROM salgrade 
WHERE lsal = 3800;

-- 문제발생

24000 달러의 등급은 어떻게 표시할 것인가?

SELECT grade 급여등급
FROM salgrade 
WHERE lsal >= 10000 AND hsal <= 70000;

-- non-equi join

-- Q) 직원의 급여가 몇등급인지를 조회 출력하시오(출력은 이름, 급여, 등급)
-- (단, 급여가 많은순부터, 같은급여면 이름 오름차순부터)

SELECT e.first_name 이름, e.salary 급여, s.grade 등급 
FROM employees e, salgrade s
WHERE e.salary BETWEEN s.lsal AND s.hsal 
ORDER BY e.salary DESC, e.first_name;

-- Q1) 사원들의 이름과 해당 부서명을 조회 출력하시오.

SELECT e.first_name 사원명, d.department_name 부서명 
FROM employees e, departments d
WHERE e.department_id = d.department_id
ORDER BY 1;

-- Q2) 사원들의 이름과 해당 부서명을 조회 출력하시오.
-- 단, 담당부서명이 없는 사람도 출력하시오.

SELECT e.first_name 사원명, d.department_name 부서명 
FROM employees e, departments d
WHERE e.department_id = d.department_id(+)
ORDER BY 1;

-- Q3) 사원들의 이름과 담당 부서명을 조회 출력하시오.
-- 단, 사원이 없는 부서명도 출력하시오.

SELECT e.first_name 사원명, d.department_name 부서명 
FROM employees e, departments d
WHERE e.department_id(+) = d.department_id
ORDER BY 1;

-- Q4) 사원들의 이름과 부서명을 조회 출력하시오.
-- 단, 사원이 없는 부서명도 출력하시오.
-- 단, 사원이 없는 곳에 NULL이 아니라 '사원이 없다는~' 글 출력.

SELECT NVL(e.first_name, '사원이 없다는~') 사원명, d.department_name 부서명 
FROM employees e, departments d
WHERE e.department_id(+) = d.department_id
ORDER BY 1;

-- Q5) 사원들의 이름과 부서명을 조회 출력하시오.
-- 단, 사원이 없는 부서명도 출력하시오.
-- 단, 부서명이 없는 사원도 출력하시오.

SELECT e.first_name 이름, e.last_name 성, d.department_name 부서명
FROM employees e, departments d
WHERE e.department_id(+) = d.department_id
UNION
SELECT e.first_name 이름, e.last_name 성, d.department_name 부서명
FROM employees e, departments d
WHERE e.department_id = d.department_id(+)
ORDER BY 1;

SELECT e.first_name 이름, e.last_name 성, d.department_name 부서명
FROM employees e FULL OUTER JOIN departments d
ON (e.department_id = d.department_id)
ORDER BY 1;

728x90

저작자표시 변경금지 (새창열림)

'IT&코딩 > 국비지원' 카테고리의 다른 글

HTML - 2 (0)	2023.05.03
HTML - 1 (웹이론) (0)	2023.05.03
네트워크 - 3 (0)	2023.04.14
SQL - 3 (0)	2023.04.13
SQL - 2 (0)	2023.04.13

움베르토 에코의 반서재

SQL - 4 (JOIN)

'IT&코딩 > 국비지원' 카테고리의 다른 글

티스토리툴바

SQL - 4 (JOIN)

'IT&코딩 > 국비지원' 카테고리의 다른 글

'IT&코딩/국비지원' Related Articles

티스토리툴바