[Oracle] 4 ~ 6장_Day15

날짜, 문자열, 상수값 : ' '

컬럼 별칭에서 대소문자 구분하거나 스페이스 포함하는 경우 " "

 

select -  검색문

 

select 컬럼명, distinct 컬럼명

from 테이블명;

 

select *

from 테이블명;

 

select*

from 테이블명;

where 조건 -- 원하는 줄을 골라냄

order by 컬럼명;

 

1. JOIN

- 오라클 전용

select 컬럼명, 컬럼명

from t1,t2

where t1.fk(외래키) = t2.pk(기본키);

and 추가조건;

 

- 표준

select 컬럼명, 컬럼명

from t1 natural join t2;

using 조인 컬럼

where 행 조건;

 

1) 등가 조인

외래 키와 기본 키가 같을 조건으로 연결

 

select last_name, e.department_id,location_id, d.department_name
from employees e, departments d
where e.department_id = d.department_id 

and last_name = 'Matos';

select e.last_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;

 

2)비등가 조인

동일한 컬럼이 없는 경우

 

--테이블 생성
create table job_grades(
gra varchar2(1),  -- 컬럼 1
lowest_sal number, -- 컬럼 2
highest_sal number -- 컬럼 3
);

 

--행 삽입
insert into job_grades values('A', 1000, 2999);
insert into job_grades values('B', 3000, 5999);
insert into job_grades values('C', 6000, 9999);
insert into job_grades values('D', 10000, 14999);
insert into job_grades values('E', 15000, 24999);
insert into job_grades values('F', 25000, 40000);

 

-- 행 삭제

delete job_grades;

(where)절 안쓰면 전체 행 삭제

 

select last_name, salary, gra
from employees, job_grades 
where salary between lowest_sal and highest_sal;

 

3) 포괄 조인

 

select e.last_name, e.department_id, d.department_name
from employees e, departments d
where e.department_id = d.department_id(+);

 

4) 자체 조인

별칭 작성 필수

 

5) 자연 조인

 

select department_id, department_name, location_id, city
from departments
natural join locations;  -- 스스로 동일한 컬럼을 찾음. 하나의 테이블을 굳이 지정하지 않음

 

6) using 절을 포함하는 조인

select e.employee_id, e.last_name, d.location_id
from employees e join departments d
using (department_id);

 

7) on절로 레코드 검색

select e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id
from employees e join departments d
on (e.department_id = d.department_id);

 

8)on 절로 3-way 조인 작성

select employee_id, city, department_name
from employees e
join departments d
on d.department_id = e.department_id
join locations l
on d.location_id = l.location_id;

 

9)추가 조건

select e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id
from employees e join departments d
on (e.department_id = d.department_id)
and e.manager_id = 149;

 

 

--4.1
select last_name, d.department_id, department_name
from employees e, departments d
where e.department_id = d.department_id;

--4.2
select distinct job_id, location_id
from employees e, departments d
where e.department_id = d.department_id
and e.department_id = 80;

--4.3
select last_name, department_name, d.location_id, 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;

--4.4
select last_name, department_name
from employees e, departments d
where e.department_id = d.department_id
and last_name like '%a%';

--4.5
select last_name, job_id, d.department_id, department_name
from employees e, departments d, locations l
where e.department_id = d.department_id
and d.location_id = l.location_id
and city = 'Toronto';

--4.6 자체 조인 (같은 테이블 스스로 조인하기  때문에 별칭 필수)
select e.last_name "Employee", e.employee_id "EMP#", m.last_name "Manager", m.employee_id "Mgr#"
from employees e, employees m
where e.manager_id = m.employee_id
order by e.employee_id;

--4.7 포괄 조인                                                                  ___null값을 공백으로 처리                ______ 숫자를 문자로 형 통일.
select e.last_name "Employee", e.employee_id "EMP#", nvl(m.last_name, '  ') "Manager", nvl(to_char(m.employee_id), ' ')  "Mgr#"
from employees e, employees m
where e.manager_id = m.employee_id(+)
order by e.employee_id;

--4.8 ***

<내가 한거>
select e.department_id "DEPARTMENT", c.last_name "Employee", e.last_name "COLLEAGUE"
from employees e, employees c
where e.department_id = c.department_id
and e.last_name <> c.last_name;

 

<강사님이 한거>

select e1.department_id, e1.last_name, e2.last_name

from employees e1, employees e2

where e1.department_id = e2.department_id

and e1.employee_id <> e2.employee_id                > 이름이 같은 사람이 있을 수 있으니 사원번호로 실행.

order by e1.department_id, e1.last_name;

--4.9 비등가 조인
describe job_grades;
select*
from job_grades;
select last_name, job_id, department_name, salary, gra
from employees e, departments d, job_grades j
where e.department_id = d.department_id
and salary between lowest_sal and highest_sal;

--4.10
select last_name, hire_date
from employees
where hire_date > (select hire_date
                   from employees
                   where last_name = 'Davies');
                   
--4.11
select e.last_name, e.hire_date, m.last_name, m.hire_date
from employees e, employees m
where m.employee_id = e.manager_id
and e.hire_date < m.hire_date;

--5.4
select max(salary) "Maximum", min(salary) "Minimum", sum(salary) "Sum", round(avg(salary),0) "Average"
from employees;

--5.5
select job_id, max(salary) "Maximum", min(salary) "Minimum", sum(salary) "Sum", round(avg(salary),0) "Average"
from employees
group by job_id;

--5.6
select job_id, count(*)
from employees
group by job_id;

--5.7
select count(distinct manager_id) "Number of MAnagers"
from employees;

--5.8
select max(salary)-min(salary) "DIFFERENCE"
from employees;

--5.9
select manager_id, min(salary)
from employees
where manager_id is not null
group by manager_id
having min(salary) >=6000 
order by min(salary) desc;

--5.10

<내가한거>
select department_name "Name", d.location_id " Location", count(*) "Number of People", round(avg(salary),2) "Salary"
from employees e, departments d, locations l
where e.department_id = d.department_id
and d.location_id = l.location_id
group by department_name,d.location_id;

 

<강사님>

select department_name "Name", location_id " Location", count(*) "Number of People", round(avg(salary),2) "Salary"
from employees e, departments d
where e.department_id = d.department_id
group by department_name, location_id;

--5.11 ***

<내가 한거>

select  

count(employee_id) "TOTAL",
count(decode (to_char(hire_date,'yy'),'05','a')) "2005",    
count(decode (to_char(hire_date,'yy'),'06','a')) "2006",
count(decode (to_char(hire_date,'yy'),'07','a')) "2007",
count(decode (to_char(hire_date,'yy'),'08','a')) "2008"
from employees;

 

<강사님>

select 
count(employee_id) "TOTAL",
sum(case to_char(hire_date, 'yyyy') when '2005' then 1 else 0 end) "2005",
        sum(case to_char(hire_date, 'yyyy') when '2006' then 1 else 0 end) "2006",
        sum(case to_char(hire_date, 'yyyy') when '2007' then 1 else 0 end) "2007",
        sum(case to_char(hire_date, 'yyyy') when '2008' then 1 else 0 end) "2008"
from employees;
        
--5.12 *** 다시해보기

<강사님>

select job_id,
sum(case department_id when 20 then salary else 0 end) "Dept 20",
sum(case department_id when 50 then salary else 0 end) "Dept 50",
sum(case department_id when 80 then salary else 0 end) "Dept 80",
sum(case department_id when 90 then salary else 0 end) "Dept 90",   
sum(salary) "Total"
from employees
group by job_id;

--6.1
select last_name, hire_date
from employees
where department_id = (select department_id
                       from employees
                       where last_name = 'Zlotkey')
and last_name <> 'Zlotkey';

--6.2
select employee_id, last_name, salary
from employees
where salary > (select avg(salary)
                 from employees)
order by salary;

--6.3
select employee_id, last_name
from employees
where department_id in (select department_id
                        from employees
                        where last_name like '%u%');

--6.4
select e.last_name, e.department_id, e.job_id
from employees e, departments d, locations l
where e.department_id = d.department_id
and d.location_id = l.location_id
and l.location_id = 1700;

--6.5
select last_name, salary, manager_id
from  employees 
where manager_id in (select employee_id
                    from employees
                    where last_name = 'King');

--6.6
select e.department_id, last_name, job_id
from employees e, departments d
where d.department_name = 'Executive';

--6.7
select employee_id, last_name, salary
from employees
where department_id in (select department_id
                        from employees
                        where last_name like '%u%')
and salary > (select avg(salary) 
                from employees);