rownum 은 감춰진 컬럼인데 출력되는 결과에 번호를 넘버링하는 컬럼입니다.
주로 rownum 을 언제 사용하냐면 ? 큰 테이블을 볼 때 일부만 보고 싶을때 사용
select *
from market_2022
where rownum < 6;
**※ row_number() 함수와 rownum 의 차이 ?**
1. row_number() 함수는 **정렬된 결과에서 번호를 넘버링**하고 싶을때 사용
select row_number() over ( order by sal desc) 번호,
ename, sal
from emp
where job='SALESMAN';
2. rownum 은 **그냥 쿼리문으로 출력되는 결과에 번호**를 넘버링 하고 싶을때 사용
select rownum, empno, ename, sal
from emp
where job='SALESMAN' ;
※ rownum에 대한 명확한 이해!문제 283. 직업이 SALESMAN인 사원들의 사원번호, 이름과 월급을 출력하는데 맨 앞에 ROWNUM을 같이 출력하고 rownum 이 1 인 행만 출력하시오
select rownum, empno, ename, sal
from emp
where job = 'SALESMAN' and rownum = 1;
문제284. 이번에는 rownum 이 2번인 행만 출력하시오 !
select rownum, empno, ename, sal
from emp
where job='SALESMAN' and rownum <=2 ;
※ rownum 2번을 보려면 1번을 봐야 2번을 볼 수 있습니다.
rownum 3번을 보려면 1번과 2번을 봐야 3번을 볼 수 있습니다.
문제285. 그러면 1번을 보지않고 바로 2번을 출력하게 하려면 어떻게 해야하는가?
답: from 절의 서브쿼리를 써서 결과를 미리 한번 출력을 해버리고 조회해야합니다.
select 번호, empno, ename, sal
from (
select rownum as 번호, empno, ename, sal
from emp
where job='SALESMAN'
)
where 번호 = 2;
문제286. 우리반 데이터에서 통신사가 kt 인 학생들 2명만 출력하시오 !
select *
from emp17
where lower(telecom) ='kt' and rownum <= 2;
▣ 057 출력되는 행 제한하기 2(Simple TOP-n Queries)
정렬된 결과에서 상위 몇개의 데이터를 가져오는 방법 2가지
- order by 컬럼명 asc/desc fetch first 숫자 rows only ;
- order by 컬럼명 asc/desc fetch first 숫자 percent rows only;
문제287. 사원 테이블에서 이름과 월급을 출력하는데 월급이 높은 사원 4명만
출력하시오 !
select ename, sal
from emp
order by sal desc fetch first 4 rows only;
문제288. 위의 결과를 다른 방법으로 출력하시오 !
select ename, sal
from (
select ename, sal
from emp
order by sal desc
)
where rownum ≤ 4;
문제289. 사원 테이블에서 이름, 월급을 출력하는데 월급이 상위 10% 에
해당하는 사원들만 출력하시오 !
select ename, sal
from emp
order by sal desc fetch first 10 percent rows only;
문제290. 우리반 테이블에서 통신사가 kt 인 학생들의 이름과 나이와 통신사를
출력하는데 나이가 상위 30% 에 해당하는 학생들만 출력하시오 !
select ename, age, telecom
from emp17
where telecom ='kt'
order by age desc fetch first 30 percent rows only;
▣ 058 여러 테이블의 데이터를 조인해서 출력하기 1(EQUI JOIN)
보다 더 좋은 정보를 테이블에서 뽑아내려면 '조인' 을 사용해야 합니다.
조인(join) 하나의 테이블로는 알 수 없는 정보를 다른 테이블과 조인함으로써
알 수 있게 해주는 SQL 기술 입니다.
2개이상의 테이블에서 각각 컬럼의 데이터를 모아서 하나의 결과로 출력해주는
SQL 문법이 바로 '조인(join)' 입니다.
예제: KING 의 어느 부서에서 근무하는가 ? 부서위치가 어떻게 되는가 ?
예제. 부서 테이블을 출력하시오 !
select *
from dept;
예제. 사원 테이블에서 이름을 출력하고 부서 테이블에서 부서위치를 출력하시오
( 사원 이름과 부서위치를 같이 출력하시오 )
select ename, loc, bonus_sal
from emp, dept
where emp.deptno = dept.deptno ;
설명 : where 에 emp 와 dept 를 잘 연결(join) 하기 위한 조인의 연결 조건을
줘야 합니다.
조인조건의 갯수 = 테이블의 갯수 - 1 개
만약에 조인을 하는데 where 절에 조인조건(join condition) 을 주지 않거나 (omit)
잘못된(incorrect) 조인 조건을 기술했다면 잘못된 결과(carteisian product) 가 출력 됩니다.
문제291. 사원번호, 이름, 월급, 부서위치, 입사일을 출력하시오 !
(emp 와 dept 를 조인해서 출력하세요)
select empno, ename, sal, loc, hiredate
from emp, dept
where emp.deptno = dept.deptno;
문제292. 사원이름, 월급, 부서위치, 부서번호를 출력하시오 !
select emp.ename, emp.sal, dept.loc, emp.deptno
from emp , dept
where emp.deptno = dept.deptno;
조인할 때 컬럼명 앞에 테이블명을 명시하지 않으면 내부적으로 오라클이
어느 테이블에 있는 컬럼인지 찾는 쿼리를 자체적으로 수행합니다.
이 쿼리문을 recursive SQL 이라고 합니다. 오라클이 찾는 작업을 수행하지
않도록 emp.ename 이라고 해주어야 합니다.
SQL 사용자는 다른 사용자를 위해서 컬럼명 앞에 테이블명을 기술해줘야하고
또한 성능을 위해서 써줘야합니다.
select e.ename, e.sal, d.loc, e.deptno
from emp e, dept d
where e.deptno = d.deptno ;
테이블명이 오라클은 25자까지 만들수 있습니다. UEHBLDMT
select /+ leading(e d) use_nl(d) / e.ename, e.sal, d.loc, e.deptno
from emp e, dept d
where e.deptno = d.deptno ;
※ from 절에 테이블 별칭을 사용했으면 반드시 다 테이블 별칭을 기술해줘야
합니다. !! → 조인튜닝 같은 거도 알면 좋을 꺼 같은데 힌트 확인
문제293. 이름, 월급, 부서위치, 직업을 출력하시오 !
select e.ename, e.sal, d.loc, e.job
from emp e, dept d
where e.deptno = d.deptno ;
문제294. 위의 결과에서 직업이 SALESMAN 만 출력하시오 !
select e.ename, e.sal, d.loc, e.job
from emp e, dept d
where e.deptno = d.deptno and e.job='SALESMAN';
※ 다음과 같이 조인조건을 안주고 검색조건만 주면 잘못된 결과를 보게 됩니다.
select e.ename, e.sal, d.loc, e.job
from emp e, dept d
where e.job='SALESMAN';
문제295. 월급이 2500 이상인 사원들의 이름과 월급과 부서위치를 출력하시오!
select e.ename, e.sal, d.loc
from emp e, dept d
where e.deptno = d.deptno and e.sal >= 2500;
문제296. DALLAS 에서 근무하는 사원들의 이름과 부서위치를 출력하시오 !
select e.ename, d.loc
from emp e, dept d
where e.deptno = d.deptno and d.loc='DALLAS';
문제297. 월급이 1000 에서 3000 사이인 사원들의 이름과 월급과 부서위치를
출력하시오 !
select e.ename, e.sal, d.loc
from emp e, dept d
where e.deptno = d.deptno and e.sal between 1000 and 3000;
점심시간 문제
다음과 같이 결과를 출력하시오 !
select telecom, listagg(ename || '(' || age || ', ' || substr(address, 1, 3) || ')' , ',') within group (order by age desc)
from emp17
group by telecom;
다음의 테이블을 생성해주세요.
drop table telecom_table;
create table telecom_table
( telecom varchar2(10),
t_price number(10),
etc_service number(10) );
insert into telecom_table values( 'sk', 17000, 7 );
insert into telecom_table values( 'lg', 16000, 6 );
insert into telecom_table values( 'kt', 18000, 9 );
insert into telecom_table values( 'toss', 15000, 10 );
commit;
문제298. emp17 과 telecom_table 을 조인해서 이름, 통신사,
통신사가격(t_price) 를 출력하는데 통신사가 kt 만 출력하시오 !
select e.ename, e.telecom, t.t_price
from emp17 e, telecom_table t
where e.telecom = t.telecom and e.telecom='kt';
문제299. (복습문제) 부서번호, 부서번호별 토탈월급을 출력하시오 !
(세로 출력)
select deptno, sum(sal)
from emp
group by deptno;
문제300. 부서위치, 부서위치별 토탈월급을 출력하시오 !
select d.loc, sum(e.sal)
from emp e, dept d
where e.deptno = d.deptno
group by d.loc;
문제301. (select 문의 6가지절) 부서위치, 부서위치별 토탈월급을
출력하는데 DALLAS 는 제외하고 출력하고 부서위치별 토탈월급
이 5000 이상인것만 출력하고 부서위치별 토탈월급이 높은것부터
출력하시오 !
select d.loc, sum(e.sal)
from emp e, dept d
where e.deptno = d.deptno and d.loc !='DALLAS'
group by d.loc
having sum(e.sal) >= 5000
order by 2 desc ;
문제302. 아래의 테이블을 생성하시오 !
create table bonus
( empno number(10),
b_bonus number(10) );
insert into bonus
select empno, sal * 1.2
from emp; → insert select 구문
commit;
문제303. 사원이름, 월급, b_bonus 를 출력하는데 직업이 SALESMAN
만 출력하시오 ! ( emp 와 bonus 를 조인 )
select e.ename, e.sal, b.b_bonus
from emp e, bonus b
where e.empno = b.empno and e.job='SALESMAN';
※ 조인조건의 갯수 = 테이블 갯수 - 1개
문제304. emp 와 dept 와 bonus 테이블을 조인해서 이름, 월급과
부서위치, b_bonus 를 출력하는데 DALLAS 에서 근무하는
사원들만 출력하시오 !
dept ------- emp -------- bonus
select e.ename, e.sal, d.loc, b.b_bonus
from emp e, dept d, bonus b
where e.deptno = d.deptno and e.empno = b.empno
and d.loc='DALLAS';
▣ 오라클 xe 버젼에 회사의 CRM 서비스 구축 하기
문제305. hr.employees 테이블과 hr.departments 테이블을 서로 조인
해서 last_name, salary, department_name 를 출력하는데
department_id 가 110 것만 출력하시오 !
select e.last_name, e.salary, d.department_name
from hr.employees e, hr.departments d
where e.department_id = d.department_id
and e.department_id = 110;
문제306. hr 계정의 테이블들을 조회할때 hr.테이블명이라고 하지 않고
그냥 테이블명을 조회할 수 있도록 하시오 ! ( dba 업무 )
동의어(synonym) 을 생성하면 됩니다.
create public synonym employees for hr.employees;
create public synonym departments for hr.departments;
create public synonym jobs for hr.jobs;
select from employees;
select from departments;
select * from jobs;
문제307. jobs 테이블의 모든 데이터를 출력해서 한번 보시오.
select *
from jobs;
문제308. employees, departments, jobs 를 조인해서 last_name, job_title, depatment_name 을 조회하는데 last_name 이 King 인 사원만 출력하시오 !
select e.last_name, j.job_title, d.department_name
from employees e, jobs j, departments d
where e.job_id = j.job_id and e.department_id = d.department_id
and e.last_name ='King';
※ join condition 은 table 의 갯수 - 1 개 입니다.
▣ 059 여러 테이블의 데이터를 조인해서 출력하기 2(NON EQUI JOIN)
※ 오라클 조인 문법 4가지
1. equi join : 조인하려는 두 테이블의 조인조건이 이퀄(=) 조건인 조인문법 (예: where e.deptno=d.deptno )
2. non equi join : 조인하려는 두 테이블의 조인조건이 이퀄(=) 조건 이 아닌 조인문법
3. outer join
4. self join문제309. 급여등급(salgrade) 테이블을 생성하시오 !
drop table salgrade;
create table salgrade
( grade number(10),
losal number(10),
hisal number(10) );
insert into salgrade values(1,700,1200);
insert into salgrade values(2,1201,1400);
insert into salgrade values(3,1401,2000);
insert into salgrade values(4,2001,3000);
insert into salgrade values(5,3001,9999);
commit;
select * from salgrade;
문제310. emp 테이블과 salgrade 테이블을 조인해서 이름과 월급과
grade(등급) 를 출력하시오 !
select e.ename, e.sal, s.grade
from emp e , salgrade s
where e.sal between s.losal and s.hisal ;
**※ 위와 같은 조인 문법이 non equi join 입니다.**
문제311. 위의 결과를 다시 출력하는데 grade 가 1 등급인 데이터만
출력하시오 !
select e.ename, e.sal, s.grade
from emp e , salgrade s
where e.sal between s.losal and s.hisal
and s.grade = 1;
문제312. grade 가 2등급인 사원들의 이름과 월급과 grade 와 부서위치를
출력하시오 ! ( dept --- emp --- salgrade )
select e.ename, e.sal, s.grade, d.loc
from emp e, dept d, salgrade s
where e.deptno = d.deptno and e.sal between s.losal and s.hisal
and s.grade = 2;
▣ 060 여러 테이블의 데이터를 조인해서 출력하기 3(OUTER JOIN)
예제. 이름과 부서위치를 출력하시오 !
select e.ename, d.loc
from emp e, dept d
where e.deptno = d.deptno ;
※ 왜 boston 이 안나왔을까요 ?
**※ 오라클 조인 문법 4가지**
1. equi join : 조인하려는 두 테이블의 조인조건이 이퀄(=) 조건인 조인문법 (예: where e.deptno=d.deptno )
2. non equi join : 조인하려는 두 테이블의 조인조건이 이퀄(=) 조건이 아닌 조인문법
3. outer join : equi 조인으로는 조인 안되는 결과를 보려할 때 사용하는 조인문법
4. self joinselect e.ename, d.loc
from emp e, dept d
where e.deptno (+) = d.deptno ;
↑
dept 테이블쪽에서 출력되고 있는 boston 을 보기위해서 써준겁니다.
emp 테이블이 40번이 없는, 데이터가 부족한쪽이므로 여기에 붙여줍니다.
insert into emp(empno, ename, sal, deptno )
values( 2939, 'JACK', 4000, 70 );
commit;
select e.ename, d.loc
from emp e, dept d
where e.deptno = d.deptno (+) ;
이런 아웃 사이더에 해당하는 데이터를 ename 쪽에서도 보고 loc 쪽에서
도 한번에 볼 수 있는 방법을 없을까?
select e.ename, d.loc
from emp e, dept d
where e.deptno (+) = d.deptno (+) ;
※ 위의 결과는 오라클 조인문법으로 볼 수 없고 1999 ANSI 조인문법으로만 볼 수 있습니다.
↓
미국 국립 표준 협회select e.ename, d.loc
from emp e full outer join dept d
on ( e.deptno = d.deptno );
문제313. dept 테이블에는 있는 부서번호인데 emp 테이블에는 없는 부서번호를 출력하시오 !
select deptno from dept
minus
select deptno from emp;
문제314. emp 테이블에는 존재하는 부서번호인데 dept 테이블에는
존재하지 않는 부서번호를 출력하시오 !
select deptno from emp
minus
select deptno from dept;
문제315. employees 테이블에는 있는 department_id 인데
departments 테이블에는 없는 department_id 를 출력하시오 !
select department_id from employees
minus
select department_id from departments;
select department_id from departments
minus
select department_id from employees;
문제316. employees 와 departments 테이블을 조인해서 last_name 과
department_name 을 출력하는데 departments 테이블에는
있는 department_name 인데 employees 테이블에는 없는
데이터도 출력하시오 !
select e.last_name, d.department_name
from employees e, departments d
where e.department_id(+) = d.department_id;
문제317. (오늘의 마지막 문제) sh 계정의 sales 와 times와 products 를
조인해서 CALENDAR_YEAR 가 2000, 2001 이고 prod_name 이 Deluxe 로 시작하는 데이터의
다음의 결과를 출력하시오!
create public synonym products for sh.products;
create public synonym sales for sh.sales;
create public synonym times for sh.times;
select p.prod_name, t.calendar_year, sum(amount_sold)
from sales s, times t, products p
where s.prod_id = p.prod_id and
s.time_id = t.time_id and
t.calendar_year in (2000, 2001) and
p.prod_name like 'Deluxe%'
group by p.prod_name, t.calendar_year;
