7. SELECT문(order by절, 범위연산자, 연산자우선순위)
5) order by (기준 컬럼명) (오름차순/내림차순)
- 정렬(오름차순정렬, 내림차순정렬)을 할때 null 은 존재하지 않는 것이므로
오라클에서는 가장 큰것으로 간주를 해주고, MS-SQL 에서는 가장 작은것으로 간주를 해버린다.
ex1)
select employee_id as 사원번호,
first_name || ' ' || last_name 사원명,
NVL(salary+(salary*commission_pct), salary) 월급,
department_id 부서번호
from employees
order by first_name || ' ' || last_name ASC; * ASC: 오름차순 정렬 * ASC나 DESC가 없으면 기본은 오름차순 정렬
(first_name || ' ' || last_name or 사원명 or 2)
- 오라클에서 컬럼은 1부터 세기 시작한다(사원번호=1, 사원명=2, 월급=3, 부서번호=4)
ex2)
select employee_id as 사원번호,
first_name || ' ' || last_name 사원명,
NVL(salary+(salary*commission_pct), salary) 월급,
department_id 부서번호
from employees
order by first_name || ' ' || last_name DESC; * DESC: 내림차순 정렬
(first_name || ' ' || last_name or 사원명 or 2)
ex3)
select employee_id as 사원번호,
first_name || ' ' || last_name 사원명,
NVL(salary+(salary*commission_pct), salary) 월급,
department_id 부서번호
from employees
order by 4;
*부서번호 순으로 오름차순 정렬하면 오라클에서는 NULL 값을 가장 큰 값으로 인지하므로, NULL 값을 가진 사원이 가장 마지막에 오게 된다.
ex4)
select employee_id as 사원번호,
first_name || ' ' || last_name 사원명,
NVL(salary+(salary*commission_pct), salary) 월급,
department_id 부서번호
from employees
order by 4 ASC, 3 DESC;
=> 1차정렬(4 ASC) 2차정렬(3 DESC)
=> 4번째 부서번호의 오름차순 먼저 해준 뒤, 동일한 부서 내에서 월급의 내림차순으로 정렬
또는
select employee_id as 사원번호,
first_name || ' ' || last_name 사원명,
NVL(salary+(salary*commission_pct), salary) 월급,
department_id 부서번호
from employees
order by 4, 3 DESC;
-> 위와 같이 4번째 부서번호의 오름차순 먼저 해준 뒤, 동일한 부서 내에서 월급의 내림차순으로 정렬
-> 4 뒤에 ASC가 생략되었음
ex5)
employees 테이블에서 수당퍼센티지가 null 인 사원들만 사원번호, 사원명, 월급(기본급여+수당금액), 부서번호를 나타내되
부서번호의 오름차순으로 정렬한 후 동일한 부서번호내에서는 월급의 내림차순으로 나타내세요.
select employee_id as 사원번호,
first_name || ' ' || last_name 사원명,
NVL(salary+(salary*commission_pct), salary) 월급,
department_id 부서번호
from employees
where commission_pct is null
order by 4 ASC, 3 DESC;
ex6)
employees 테이블에서 수당퍼센티지가 null 인 아닌 사원들만 사원번호, 사원명, 월급(기본급여+수당금액), 부서번호를 나타내되 부서번호의 오름차순으로 정렬한 후 동일한 부서번호내에서는 월급의 내림차순으로 나타내세요.
select employee_id as 사원번호,
first_name || ' ' || last_name 사원명,
NVL(salary+(salary*commission_pct), salary) 월급,
department_id 부서번호
from employees
where commission_pct is not null
order by 4 ASC, 3 DESC;
또는
select employee_id as 사원번호,
first_name || ' ' || last_name 사원명,
NVL(salary+(salary*commission_pct), salary) 월급,
department_id 부서번호
from employees
where not commission_pct is null
order by 4 ASC, 3 DESC;
ex7)
employees 테이블에서 월급(기본급여+수당금액)이 10000 보다 큰 사원들만 사원번호, 사원명, 월급(기본급여+수당금액), 부서번호를 나타내되
부서번호의 오름차순으로 정렬한 후 동일한 부서번호내에서는 월급의 내림차순으로 나타내세요.
select employee_id 사원번호,
first_name || ' ' || last_name 사원명,
NVL(salary+(salary*commission_pct), salary) 월급,
department_id 부서번호
from employees
where NVL(salary+(salary*commission_pct), salary)>10000
order by 4 ASC, 3 DESC;
ex8)
employees 테이블에서 부서번호가 50번 부서가 아닌 사원들만 사원번호, 사원명, 월급(기본급여+수당금액), 부서번호를 나타내되
부서번호의 오름차순으로 정렬한 후 동일한 부서번호내에서는 월급의 내림차순으로 나타내세요.
select employee_id as 사원번호,
first_name || ' ' || last_name 사원명,
NVL(salary+(salary*commission_pct), salary) 월급,
department_id 부서번호
from employees
where NVL(department_id, -9999) != 50
order by 4 ASC, 3 DESC;
-- 또는
select employee_id as 사원번호,
first_name || ' ' || last_name 사원명,
NVL(salary+(salary*commission_pct), salary) 월급,
department_id 부서번호
from employees
where not NVL(department_id, -9999) = 50
order by 4 ASC, 3 DESC;
6) 범위 연산자
- > < >= <=
- between A and B : A 이상 B 이하
- 범위 연산자에 사용되는 데이터인 A와 B는 숫자 뿐만아니라 문자, 날짜까지 모두 사용가능하다.
7) 연산자 우선순위
- AND, OR, NOT 연산자가 혼용되어지면 우선순위를 따르는데 NOT > AND > OR 의 순위를 따라간다.
우선순위에 있어서 최우선은 ( ) 괄호 이다.
- OR 연산자와 같은 것이 IN( ) 이다. IN( )는 괄호가 있는 OR 이다.
ex1)
employees 테이블에서 부서번호가 30, 50, 60번 부서에 근무하는 사원들중에
연봉(월급*12)이 20000 이상 60000 이하인 사원들만 사원번호, 사원명, 연봉(월급*12), 부서번호를 나타내되
부서번호의 오름차순으로 정렬한 후, 연봉의 내림차순으로 정렬하여 출력하세요.
select employee_id as 사원번호,
first_name || ' ' || last_name 사원명,
NVL(salary+(salary*commission_pct), salary)*12 연봉,
department_id 부서번호
from employees
where department_id = 30 or department_id = 50 or department_id = 60
and nvl(salary+(salary*commission_pct),salary)*12>=20000
and nvl(salary+(salary*commission_pct),salary)*12<=60000
order by 4, 3 desc;
부서번호 30,50에 연봉제약이 적용되지 않음
-- 오답! AND와 OR가 혼용되어지면 우선순위는 AND가 먼저 실행된다. 부서번호 60인 사원들에게만 연봉의 제약이 있고, 30번 부서나 50번 부서에는 연봉 제약이 없다.
=>
select employee_id as 사원번호,
first_name || ' ' || last_name 사원명,
NVL(salary+(salary*commission_pct), salary)*12 연봉,
department_id 부서번호
from employees
where (department_id = 30 or department_id = 50 or department_id = 60)
and nvl(salary+(salary*commission_pct),salary)*12>=20000
and nvl(salary+(salary*commission_pct),salary)*12<=60000
order by 4, 3 desc;
- 모든 부서에 연봉의 제약이 적용된다.
또는
select employee_id as 사원번호,
first_name || ' ' || last_name 사원명,
NVL(salary+(salary*commission_pct), salary)*12 연봉,
department_id 부서번호
from employees
where department_id in(30, 50, 60)
and nvl(salary+(salary*commission_pct),salary)*12 between 20000 and 60000
order by 4, 3 desc;
- 모든 부서에 연봉의 제약이 적용된다.
* 대용량 데이터베이스인 경우 in 보다는 or를, between A and B 보다는 >= <= 를 사용할것을 권장한다.
왜냐하면 in 은 내부적으로 or 로 변경되어서 사용되고, between A and B 도 >= <= 로 변경되어서 사용되어지기 때문이다.
대용량 데이터베이스의 기준은 어떤 테이블의 행의 갯수가 100만건을 넘을 경우, 소규모 데이터베이스의 기준은 어떤 테이블의 행의 갯수가 1만건 미만인 경우를 말한다.
ex2)
employees 테이블에서 부서번호가 50, 80번 부서에 근무하지 않는 사원들중에
월급이 4000 이상인 사원들만 사원번호, 사원명, 월급, 부서번호를 나타내되
부서번호의 오름차순으로 정렬한 후, 월급의 내림차순으로 정렬하여 출력하세요.
select employee_id as 사원번호,
first_name || ' ' || last_name 사원명,
NVL(salary+(salary*commission_pct), salary)*12 연봉,
department_id 부서번호
from employees
where nvl(department_id, -9999) != 50 and
nvl(department_id, -9999) != 80 and
nvl(salary+(salary*commission_pct),salary) >= 4000
order by 4, 3 desc;
-부서번호 10부터 null값 부서까지 연봉제약 잘 적용됨
또는
select employee_id as 사원번호,
first_name || ' ' || last_name 사원명,
NVL(salary+(salary*commission_pct), salary)*12 연봉,
department_id 부서번호
from employees
where nvl(department_id,-9999) not in(50, 80) and
nvl(salary+(salary*commission_pct),salary) >= 4000
order by 4, 3 desc;
또는
select employee_id as 사원번호,
first_name || ' ' || last_name 사원명,
NVL(salary+(salary*commission_pct), salary)*12 연봉,
department_id 부서번호
from employees
where not nvl(department_id,-9999) in(50, 80) and
nvl(salary+(salary*commission_pct),salary) >= 4000
order by 4, 3 desc;
