11.
찾는 항목:관리자보다 먼저 입사한 사원의 이름 및 입사일, 관리자이름, 입사일
-> 후배가 먼저 승진한 사원의 이름, 입사일, 선배보다 먼저 승진한 사원 이름, 입사일
선배보다 먼저 승진한 사원의 이름, 입사일,
후배가 먼저 승진한 사원의 이름, 입사일 ---------->employees
-> 자체조인
select worker.last_name, worker.hire_date, manager.last_name, manager.hire_date
from employees worker, employees manager
where worker.hire_date < manager.hire_date and
worker.manager_id = manager.employee_id;
10.
찾는 항목:Davies보다 늦게 입사한 사원의 이름과 입사일
Davies의 입사일, Davies보다 늦게 입사한 사원의 이름, 입사일 : employees
-> 자체조인
select worker.last_name, worker.hire_date
from employees davies, employees worker
where davies.last_name = 'Davies' and worker.hire_date > davies.hire_date
order by worker.hire_date;
8.
찾는 항목:사원의 이름, 부서번호, 이 사원과 동일한 부서에서 근무하는 사원
사원의 이름, 부서 번호 : employees, 동일한 부서의 사원이름:employees
-> 자체조인
select woker.last_name, woker.department_id, colleage.last_name
from employees woker, employees colleage
where woker.department_id = colleage.department_id
and woker.employee_id <> colleage.employee_id;
7.
6번 문제를 수정해서 관리자가 없는 사원도 출력해라.
정렬: 사원번호를 기준으로(오름차순:사번이 작은--> 사번이 큰)
-> 6번 조인(자체조인(일종의 등가조인)) + 해당하지 않은 것 --> 포괄조인
select w.last_name 사원이름, w.employee_id 사번,
m.last_name 관리자이름, m.employee_id "관리자 사번"
from employees w, employees m
where w.manager_id = m.employee_id(+)
order by w.employee_id;
6.
찾는 항목:사원의 이름, 사원번호, 관리자의 이름, 관리자의 사번
사원의 이름,사원번호, 관리자의 이름, 관리자의 사번 : employees
-> 자체조인
select w.last_name 사원이름, w.employee_id 사번,
m.last_name 관리자이름, m.employee_id "관리자 사번"
from employees w, employees m
where w.manager_id = m.employee_id;
5.
찾는 항목:사원의 이름, 업무코드, 부서번호, 부서이름
조건:Toronto에 근무하는
사원의 이름:employees, 업무코드:employees, 부서번호:employees,departments
부서이름:departments, 도시이름:locations
employees와 departments에 공통데이터:department_id(부서번호)
departments와 locations에 공통데이터:location_id(부서위치번호)
->등가조인
select e.last_name, e.job_id, d.department_id, d.department_name
from employees e, departments d, locations l
where e.department_id = d.department_id and d.location_id = l.location_id
and lower(l.city) = 'toronto';
4.
찾는 항목:사원의 이름, 부서이름
조건 : 이름에 a가 포함된
사원의 이름:employees, 부서이름:departments
employees테이블과 departments에 공통 데이터가 있나? 부서번호(department_id)
-> 등가조인
select last_name, department_name
from employees e, departments d
where e.department_id = d.department_id and last_name like '%a%';
3.
찾는 항목:사원의 이름, 부서이름, 부서위치, 도시이름
조건 : 커미션을 받는->커미션이 있는->
없는 걸 찾는 연산자: is null
있는 걸 찾는 연산자: is not null
사원의 이름 : employees, 부서이름,부서위치:departments, 도시이름:locations
employees와 departments에 공통의 데이터가 있는가?
- 있다. department_id(부서번호)
departments와 locations에 공통의 데이터가 있는가?
- 있다. location_id(위치번호)
있으면 : 등가조인, 없으면 : 나머지에서 찾는다.
select e.last_name, d.department_name, d.location_id, l.city
from employees e, departments d, locations l
where e.department_id = d.department_id and d.location_id = l.location_id
and e.commission_pct is not null;
SQL 15개 명령어
DML(삽입,삭제,변경,조회) + DDL(테이블과 관련) + DCL(권한과 관련)
Data Manipulation Language+Data Definition Language+Data Control Language
4장 조인(join) : 개념: 가상으로 분리된 테이블을 합친 후에 검색
1.등가조인 2.비등가조인 3.자체조인 4.포괄조인
3장 연습문제 12번
12.
select last_name || ' ' || lpad(' ',salary/1000+1,'*') "사원과 월급 목록"
from employees;
월급 $6500 --> ****** (6개)
lpad(A, B, C)
-> A를 B에 출력, 남는 부분에 C를 출력
lpad(salary, 15, '$')
-> 월급을 15자리에 출력, 남는 부분에 $를 출력
lpad(' ', salary/1000+1, '*')
A B
사번/ 이름 / 부서번호 부서번호 / 부서이름 / 부서위치
1 홍길동 100 100 개발부 본관1
2 이길동 102 102 홍보부 별관3
3 박길동 103 103 영업부 본관4
104 자재부 본관5
등가(부서번호)조인의 결과(아래)
1 홍길동 100 개발부 본관1
2 이길동 102 홍보부 별과3
3 박길동 103 영업부 본관4
=================여기까지가 등가조인의 결과=========
104 자재부 본관5
=================여기까지가 포괄조인의 결과=========
조인(join)의 개념
- 분리된 테이블을 가상으로 하나의 테이블로 합친다.
자체 조인의 개념
- 하나의 테이블을 가상으로 분리한 후,
다시 하나의 테이블로 합친다.
포괄조인
select e.last_name, e.department_id, d.department_name
from employees e, departments d
where e.department_id(+) = d.department_id;
(+)를 어디다 붙이는가? null을 출력하는 쪽에 붙이다.
오라클의 조인 4가지
1. 등가조인 : 양쪽테이블에 공통되는 데이터를 중심으로 가상으로 합친다.
2. 비등가조인 : 양쪽테이블에 일치하지는 않지만 범위에 포함되는 데이터를 중심으로
가상으로 합친다.
3. 자체조인 : 하나의 테이블을 가상으로 분리한 후 다시 등가조인으로 테이블을 합친다.
4. 포괄조인 : 등가조인으로는 출력할 수 없는 데이터도 출력할 수 있다.
4장 조인 연습문제 풀이
2.
찾는 항목:업부코드, 부서의 위치 번호
업부코드 : 사원정보테이블에 있다.(employees)
부서위치번호 : 부서정보테이블에 있다.(departments)
-> 양쪽 테이블에 공통데이터(department_id:부서번호)가 있으므로 등가조인
추가 조건 : 부서번호가 80
select job_id, location_id
from employees e, departments d
where e.department_id = d.department_id and e.department_id = 80;
1.
찾는 항목: 사원의 이름, 부서번호, 부서이름
사원의 이름, 부서번호 : 사원정보테이블(employees)
부서이름 : 부서정보테이블(departments)
employees와 departments에 공통되는 데이터가 있는가? 있다. 부서번호
-> 등가조인
select last_name, e.department_id, department_name
from employees e, departments d
where e.department_id = d.department_Id;
11.
select last_name, nvl(to_char(commission_pct), '커미션 없슴') "COMM"
from employees;
10.
찾는 항목:사원의 이름, 입사일, 업무 시작 요일(입사한 날의 요일)
열별칭: DAY
정렬: 월요일을 기준으로
select last_name, hire_date, to_char(hire_date, 'DAY') "DAY"
from employees
order by to_char(hire_date-1,'D');
9.
찾는 항목:사원의 이름,입사일,급여 검토일(입사후 6개월 지나고 첫번째 월요일)
열별칭:검토, 출력형태:요일전체이름, 서수, 월전체이름, 연도4자리
출력형태는 날짜를 문자로 바꿔야 한다. to_char(날짜,'DAY, ddspth MONTH, YYYY')
급여검토일 : next_day(add_months(hire_date, 6),'월요일')
select last_name, hire_date,
to_char(next_day(add_months(hire_date, 6),'월요일'),'DAY, "the" ddspth "of" MONTH, YYYY') 검토
from employees;
8.
찾는 항목:사원의 이름, 급여-->15자로 출력, 왼쪽 빈 칸에 $를 채워라. 열 별칭 "월급"
select last_name, lpad(salary, 15, '$') 월급
from employees;
7.
찾는 항목: 사원의 이름, 월급, 월급 * 3
select last_name || '의 월급은 ' ||
to_char(salary,'$99,999.00') || ' 그런데, 원한다. ' ||
to_char(salary * 3,'$99,999.00') "꿈의 월급"
from employees;
6.
각 사원의 이름, 근무 달 수(입사일로부터 현재까지의 달),
열 이름을 "근무한 달", 결과는 정수로 반올림, 근무 달수를 기준으로 정렬
select last_name, round(months_between(sysdate, hire_date)) "근무한 달"
from employees
order by "근무한 달";
