LIKE
특정 문자를 포함하는 데이터를 출력
컬럼 LIKE '문자%' → 특정 문자로 시작하는 데이터 확인
컬럼 LIKE '%문자' → 특정 문자로 종료하는 데이터 확인
컬럼 LIKE '%문자%' → 문자 시작부터 종료까지 특정 문자 포함 여부 확인
컬럼 LIKE '%%' → 전체 컬럼 데이터 출력
테이블 생성
create table job_grades(
gra varchar2 (1),
lowest_sal number,
highest_sal number
);데이터 삽입
insert into job_grades values('A',1000,2900);
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('R',25000,40000);데이터 지우기 (테이블 제거 아님)
delete job_grades;
카티시안 곱
카티시안 곱은 다음 경우에 생성된다.
조인 조건을 생략한 경우
조인 조건이 부적합한 경우
첫번째 테이블의 모든 행이 두번째 테이블의 모든 행에 조인된 경우
카티시안 곱이 생성되지 않도록 하려면 where 절에 항상 유효한 조인 조건을 포함시켜야 한다.
select last_name, department_name as dept_name
from employees, departments;조인 유형
등가 조인, 비등가 조인, 포괄 조인, 자체 조인
교차 조인, 자연 조인, Using 절, 전체 또는 한쪽 포괄 조인, 포괄 조인에 대한 임의의 조인 조건
where 절에서 조인 조건을 작성한다.
등가 조인을 단순 조인 또는 내부 조인이라고도 한다.
등가 조인으로 레코드 검색
select e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id
from employees e, departments d
where e.department_id=d.department_id;and 연산자를 사용한 추가 검색 조건
사원 Matos의 부서 번호 및 부서 이름을 표시
select last_name, e.department_id, 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;비등가 조인
비등가 조인은 동등 연산자가 아닌 연산자를 포함하는 조인 조건이다.
select e.last_name, e.salary, j.gra
from employees e, job_grades j
where e.salary
between j.lowest_sal and j.highest_sal;포괄 조인 사용
포괄 조인을 사용하여 일치하는 항목이 없는 레코드 반환
조인 조건을 만족하지 않는 행도 볼 수 있다.
포괄 조인 연산자는 더하기 기호(+) 이다.
양쪽에 넣을 수 없다.
내가 보고 싶은 쪽의 반대편에 붙인다.
select e.last_name, e.department_id, d.department_name
from employees e, departments d
where e.department_id(+) = d.department_id;포괄 조인 사용하지 않고 그냥 조인을 사용한다면
select e.last_name, e.department_id, d.department_name
from employees e, departments d
where e.department_id = d.department_id;자체 조인
예를 들어 각 사원의 보스를 출력하고 싶은데 보스의 이름을 모른다고 하자.
이 작업을 하려면 자기 자신을 조인을 해서 그 사원 보스의 번호를 본 다음 보스의 이름을 다시 한 번 봐야 함
별칭 필수
select worker.last_name || ' works for ' || manager.last_name
from employees worker, employees manager
where worker.manager_id = manager.employee_id;교차 조인 작성
cross join 절은 두 테이블 상호간의 조합을 생성한다.
이것은 두 테이블 사이의 카티시안 곱과 동일하다.
select last_name, department_name
from employees
cross join departments;자연 조인 작성
natural join 절은 두 테이블에서 동일한 이름을 가진 모든 열을 기준으로 한다.
두 테이블의 일치하는 모든 열에서 같은 값을 가진 행을 선택한다.
동일한 이름을 가진 열의 데이터 유형이 서로 다를 경우 오류가 반환된다.
등가조인의 한 종류이다.
등가조인인 '='를 쓸 때 반복해서 명시해주는 것.
즉, 중복을 제거하기 위해 사용한다.
select department_id, department_name, location_id, city
from departments
natural join locations;자연조인을 등가 조인으로도 작성 가능
select department_id, department_name, departments.location_id, city
from departments, locations
where departments.location_id = locations.location_id;where 절을 포함하는 자연 조인
select department_id, department_name, location_id, city
from departments
natural join locations
where department_id in (20, 50);using 절로 레코드 검색
여러 열이 같은 이름을 가지지만 데이터 유형이 일치하지 않을 경우 natural join 절을 수정하여 using 절을 포함시키면 등가 조인에 사용될 열을 지정할 수 있다.
using 절을 사용하면 둘 이상의 열이 일치할 때 한 열만 일치시킬 수 있다.
참조되는 열에 테이블 이름이나 별칭을 사용해서는 안 된다.
natural join 및 using 절은 서로 배타적이다.
자연 조인은 이름과 데이터 유형이 일치하는 모든 열을 사용하여 테이블을 조인한다.
using 절을 사용하면 등가 조인에 사용될 열만 을 지정할 수 있다.
select e.employee_id, e.last_name, d.location_id
from employees e join departments d
using (department_id);
-- 유효한 코드
select l.city, d.department_name
from locations l join departments d using (location_id)
where location_id = 1400;
-- 유효하지 않은 코드
select l.city, d.department_name
from locations l join departments d using (location_id)
where d.location_id = 1400;on 절 : 조인 조건
자연 조인의 조인 조건은 기본적으로 같은 이름을 가진 모든 열의 등가 조인입니다.
임의의 조건을 지정하거나 조인할 열을 지정하려면 on 절을 사용합니다.
조인 조건이 다른 검색 조건과 분리됩니다.
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);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;
-- 위 아래 동일한 코드
select employee_id, city, department_name
from employees e, departments d, locations l
where e.department_id = d.department_id
and d.location_id = l.location_id;left, right, full outer join
select e.last_name, e.department_id, d.department_name
from employees e
left join departments d
on (e.department_id = d.department_id);위 left부분을 right, full outer로 바꾸면서 실행하면 된다.
추가 조건
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;그룹 함수란?
그룹 함수는 행 집합에 작용하여 그룹 당 하나의 결과를 생성한다.
AVG, COUNT, MAX, MIN, STDDEV(표준편차), SUM, VARIANCE(분산)
select round(avg(salary),2), max(salary), min(salary), sum(salary)
from employees
where job_id like '%REP%';모든 데이터 유형에 대해 min 및 max를 사용할 수 있다.
select min(hire_date), max(hire_date)
from employees;
select min(last_name), max(last_name)
from employees;count 함수 사용
테이블의 행수를 반환
count 함수에는 세 가지 형식이 있다.
COUNT(*)
COUNT(expr)
XOUNT(DISTINCT expr)
select count(*)
from employees
where department_id=50;
-- 널 값을 제외한 부서 값의 수를 표시
select count(commission_pct)
from employees
where department_id=80;
select count(department_id)
from employees;DISTINCT 키워드 사용
select count(distinct department_id)
from employees;그룹 함수 및 널 값
모든 그룹 함수는 해당 열의 널 값을 무시 한다.
밑 코드에서 평균은 테이블의 commission_pct 열에 유효한 값이 저장된 행만으로 계산된다.
select avg(commission_pct)
from employees;그룹 함수에 nvl 함수 사용
nvl 함수는 그룹 함수가 널 값을 포함하도록 강제로 지정한다.
select avg(nvl(commission_pct, 0))
from employees;데이터 그룹 생성
테이블 정보를 더 작은 그룹으로 나누어야 할 경우 group by 절을 사용하면 된다.
group by 절 구문
group by 절에 열을 포함시켜야 한다.
group by 절에는 열 별칭을 사용할 수 없다.
select 목록의 열 중 그룹 함수에 없는 열은 모두 group by 절에 포함되어야 한다.
select department_id, avg(salary)
from employees
group by department_id
order by department_id;
-- group by 열을 select 목록에 포함시키지 않아도 된다.
select round(avg(salary),2)
from employees
group by department_id;
select department_id, round(avg(salary), 2)
from employees
group by department_id
order by avg(salary);여러 열에 group by 절 사용
select department_id dept_id, job_id, round(sum(salary),2)
from employees
group by department_id, job_id;having 절
최고 급여가 $10,000를 넘는 부서의 부서 번호와 평균 급여를 표시한다.
select department_id, round(avg(salary),0)
from employees
group by department_id
having max(salary)>10000;select job_id, sum(salary) payroll
from employees
where job_id not like '%REP%'
group by job_id
having sum(salary) > 13000
order by sum(salary);서브 쿼리 왜써?
이름이 abel 보다 급여가 높은 사원이 누구냐? 를 물어봤을 때
abel의 급여를 먼저 알아야 함
그래서 그 후에 비교를 해야 한다.
서브 쿼리(내부 질의)는 기본 질의 실행 전에 한 번 실행된다.
서브 쿼리의 결과는 메인쿼리(외부 질의)에 사용된다.
select last_name, salary
from employees
where salary > (select salary from employees where last_name = 'Abel');서브 쿼리 사용 지침
일반 상황에서 order by 절은 필요가 없다.
서브 쿼리를 괄호로 묶는다.
서브 쿼리의 order by 절은 Top-N 분석을 수행하지 않을 경우에는 필요가 없다.
단일 행 서브 쿼리에는 단일 행 연산자를 사용하고 다중 행 서브 쿼리에는 다중 행 연산자를 사용한다.
읽기 쉽도록 비교 조건의 오른쪽에 서브 쿼리를 넣는다.
-- 사원 141과 동일한 업무 id를 가진 사원을 표시 "업무 이름 : ST_CLERK"
select last_name, job_id
from employees
where job_id = (select job_id from employees where employee_id = 141);단일 행 서브 쿼리 실행
select last_name, job_id, salary
from employees
where job_id = (select job_id from employees where employee_id = 141) -- ST_CLERK
and salary > (select salary from employees where employee_id = 143); -- 2600 이상서브 쿼리에서 그룹 함수 사용
select last_name, job_id, salary
from employees
where salary = (select min(salary) from employees);HAVING 절에 서브 쿼리 사용
select department_id, min(salary)
from employees
group by department_id
having min(salary) > (select min(salary) from employees where department_id = 50 ); -- 2100 이상서브 쿼리 오류
서브 쿼리의 일반적인 오류는 단일 행 서브 쿼리에 대해 여러 행이 반환되는 것이다.
바로 밑 코드는 여러 행을 반환하게 돼서 오류가 발생하는 것이다.
where 절은 하나의 값만 처리하는 단일 행 비교 연산자인 등호(=) 연산자를 포함하는데
= 연산자가 서브 쿼리로부터 여러 값을 받아 들일 수 없으므로 오류가 발생한다.
이 문제를 수정하려면 = 연산자 를 in으로 바꿔야 한다.
오류 발생 : "single-row subquery returns more than one row"
select employee_id, last_name
from employees
where salary = (select min(salary) from employees group by department_id);
select min(salary) from employees group by department_id;
-- in으로 변경하면 정상 작동
select employee_id, last_name
from employees
where salary in (select min(salary) from employees group by department_id);서브 쿼리에서 발생할 수 있는 문제
서브 쿼리에서 발생할 수 있는 일반적인 문제는 내부 질의에서 행을 반환하지 않는 경우이다.
이름이 Haas라는 사원이 없으므로 서브 쿼리에서 행을 반환하지 않는다.
그래서 where절이 null 값으로 비교를 하게 된다.
근데❗❗ 널 값 두 개를 비교하면 널이 반환되므로 행이 반환되지 않는다.
따라서 where 조건이 true가 되지 않는다.
-- 서브쿼리가 값을 반환하지 않음
select last_name, job_id
from employees
where job_id = (select job_id from employees where last_name = 'Haas');다중 행 서브 쿼리
여러 행을 반환한다.
여러 행 비교 연산자를 사용한다.
IN : 목록에 있는 임의의 멤버와 동일하다
ANY : 값을 서브 쿼리에 의해 반환된 각 값과 비교한다.
ALL : 값을 서브 쿼리에 의해 반환된 모든 값과 비교한다.
select last_name, salary, department_id
from employees
where salary in (select min(salary) from employees group by department_id);
-- 위와 아래는 같은 코드이다
select last_name, salary, department_id
from employees
where salary in (2100, 2500, 4200, 4400, 6000, 6100, 6500, 6900, 7000, 8300, 10000, 17000);다중 행 서브 쿼리에 ANY 연산자 사용
ANY 연산자는 값을 서브 쿼리가 반환하는 각각의 값과 비교한다.
=ANY 는 IN과 동일하다.
select employee_id, last_name, job_id, salary
from employees
where salary < any
(select salary from employees where job_id = 'IT_PROG')
and job_id <> 'IT_PROG';다중 행 서브 쿼리에 ALL 연산자 사용
all 연산자는 값을 서브 쿼리에서 반환하는 모든 값과 비교한다.
not 연산자는 in, any, all 연산자와 함께 사용할 수 있다.
select employee_id, last_name, job_id, salary
from employees
where salary < all
(select salary from employees where job_id = 'IT_PROG')
and job_id <> 'IT_PROG';서브 쿼리에서의 널값
select e.last_name
from employees e
where e.employee_id not in (select m.manager_id from employees m);
-- no rows selected 가 뜸
select e.last_name
from employees e
where e.employee_id not in (select manager_id from employees where manager_id is not null);
-- 이렇게 하면 매니저가 없는 사원의 이름이 출력된다. 