[Oracle SQL]9일차_23.06.30

망구씨·2023년 6월 30일
EQUI JOINNON-EQUI JOINOUTER JOINSynonymfetch first 숫자 rows onlyrownum
0

Oracle SQL

목록 보기
9/21
post-thumbnail

오늘의 TIL

  1. 출력되는 행 제한하기 1(ROWNUM)
  2. 출력되는 행 제한하기 2(Simple TOP-n Queries)
  3. 여러 테이블의 데이터를 조인해서 출력하기 1(EQUI JOIN)
  4. 여러 테이블의 데이터를 조인해서 출력하기 2(NON EQUI JOIN)
  5. 여러 테이블의 데이터를 조인해서 출력하기 3(OUTER JOIN)

출력되는 행 제한하기 1(ROWNUM)

rownum은 감춰진 컬럼인데 출력되는 결과에 번호를 넘버링하는 컬럼입니다.
주로 큰 테이블을 볼 때, 일부만 보고 싶을 경우 사용합니다.

select *
 from market_2022
  where rownum < 6;

row_number()함수와 rownum의 차이!

  • row_number 은 출력하는 함수
    정렬을 사용해서 월급이 높은사원순으로 넘버링 하는건 되는데 rownum은 정렬은 안되고 그냥 출력되는 순서대로 넘버링이 된다.
    번호가 뒤바뀌지 않는다 rownum은 정렬에 관계없지만 하고싶으면 order by로 정렬하면 되고, 이 경우 순서가 뒤바뀐다.
  1. row_number()함수는 정렬된 결과에서 번호를 넘버링 하고싶을 때 사용
select  row_number()  over  ( order by sal desc) 번호,
  ename, sal
  from emp
  where job='SALESMAN';
  1. 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;
  • 위에 where절에서 rownum=1 이면 데이터가 나오는데 2번부터 안나온다.
    rownum은 where절에서 1번만 볼 수 있고 나머지 숫자는 볼 수 없다.
    rownnum <=2; 로 하면 볼 수 있는데, 1번을 봐야 2번을 볼 수 있다.
    3번을 보려면 1번과 2번을 봐야 볼 수 있다.

문제 284. rownum이 2번인 행만 출력
문제 285. 위 결과 어떻게 고칠 수 있을까? -> from절의 서브쿼리를 이용해야한다.

select  번호, empno, ename, sal
   from (
 select rownum as 번호, empno, ename, sal
  from emp
  where job='SALESMAN'
  )
  where 번호 =2;

문제 286. 우리반 데이터에서 통신사가 kt인 학생들 두명만 출력하세요.

select *
 from emp17
 where rownum <= 2 and telecom = 'kt';

출력되는 행 제한하기 2(Simple TOP-n Queries)

정렬된 결과에서 상위 몇개의 데이터를 가져오는 방법 2가지
1. order by 컬럼명 asc/desc fetch first 숫자 rows only;
2. order by 컬럼명 asc/desc fetch frise 숫자 percent rows only;

문제 287. 사원 테이블에서 이름, 월급을 출력하는데 월급이 높은 사원 4명만 출력

select ename, sal
 from emp
 order by sal desc `fetch first 4 rows only;`을 사용하지 않고 다른방법으로 출력

문제 288. 위 결과를 fetch first 4 rows only;을 사용하지 않고 다른방법으로 출력

  • 서브쿼리로 작성 -> 
    select ename, sal 
  from (
     select ename, sal
     from emp
     order by sal desc 
       )
 where rownum <= 4;

문제 289. 이름, 월급을 출력하는데 월급이 상위 10%에 해당하는 사원들만 출력

  • fetch first 10 percent rows only;
    select ename, sal
 from emp
 order by sal desc fetch first 10 percent rows only;

문제 290. 우리반 테이블에서 통신사가 kt인 학생들의 이름, 나이, 통신사를 출력하는데 나이가 상위 30%

select ename, telecom, age
 from emp17
 where telecom = 'kt'
 order by age desc fetch first 30 percent rows only;

여러 테이블의 데이터를 조인해서 출력하기 1(EQUI JOIN)

보다 더 좋은 정보를 테이블에서 뽑아내려면, JOIN을 사용해야 합니다!
하나의 테이블로는 알 수 없는 정보를 다른 테이블과 조인함으로써 알 수 있게 해주는 sql 기술입니다.

2개 이상의 테이블에서 각각 컬럼의 데이터를 모아서 하나의 결과로 출력해주는 SQL 문법이 바로 조인입니다.

예제. KING은 어느 부서에서 근무하는가 ? 부서위치는 어떻게 되는가

예제. 사원 테이블에서 이름출력, 부서테이블에서 부서위치 출력(사원이름, 부서위치 같이 출력)

select ename, loc
 from emp, dept
 where emp.deptno = dept.deptno;
  • 연결을 잘 하려면 서로 공통점이 있어야한다.
    emp table와 dept table에는 deptno가 공통으로 있다.
    여기서 where은 검색조건이 emp와 dept를 잘 연결(조인) 하기 위한 조인 조건을 써준다.
  • 조인조건의 갯수 = 테이블의 갯수 - 1개
select ename, loc, bonus_sal
 from emp, dept, bonus
 where emp.deptno = dept.deptno and emp.b_id = bonus.b_id

만약에 조인을 하는데 where절에 조인조건(join condition)을 주지 않거나(omit), 잘못된`(incorrect) 조인 조건을 기술했다면 잘못된 결과(partition product)가 출력된다.

문제 291. 사원번호, 이름, 월급, 부서위치, 입사일 출력 (emp, dept테이블 조인)

select empno, ename, sal, loc, hiredate
 from emp, dept
 where emp.deptno = dept.deptno;

문제 292. 이름, 월급, 부서위치, 부서번호 출력

  • 두개의 테이블에서 deptno 가 있으니까 어떤 테이블에서 가져오는건지 애매해서 저런 에러가 난다. emp.deptno
select ename, sal, loc, emp.deptno
 from  emp, dept
 where emp.deptno = dept.deptno;
  • 원래 다른 컬럼명에도 다 이렇게 구분을 해서 작성해야하는데 한쪽에만 있으니까 그냥 나온거!
    그냥 ename만 썼을 때 이게 어떤 테이블에 있는건지 내부적으로 찾는 쿼리를 자체적으로 수헹한디. 시간이 길어지나. 이쿼리문을 recurcsivq SQLD햐다. 오라클이 찾는 직업을 수행하지 않도ㅗ emp.ename이라고 해야줘야한다.
  • SQL사용자는 다른 사용자를 위해 컬럼명 앞에 테이블명을 기술해야하고 또한 성능을 위해 써줘야한다.
  • TABLE 명이 오라클은 25자까지 만들 수 없음
SELECT E.ENAME ,E.SAL, DE.PEOTNO
 FROM EMP E , DEPTM D
 WHERE DEPTNO D
 WHERE E.DEPTNO = D. DEPTNO
  • 위에서 SELECT문에 E.ENAME를 EMP.ENAME이라고 쓰면 에러난다. 에러가 날 수도 있다.

정리!
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';
  • WHERE절에 조인조건과 검색조건을 모두 작성!!!
    만약 조인조건을 쓰지 않고 검색조건만 적는다면, 잘못된 결과가 나온다.

문제 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 as 통신사,
       listagg(ename || '(' ||age|| ',' || substr(address,1,3 )|| ')' ||  ',')
       within group (order by age desc) as 이름
 from emp17
 group by telecom;

  • 연습을 위해 telecom_table을 만들었습니다!
create table telecom_table
 ( telecom  varchar2(10),
   t_price  number(10),
   etc_service  number(10));

문제 298. emp17과 telecom_table을 조인해서 이름, 통신사, 통신사가격(t_price)를 출력하는데 통신사는 케이티만 출력

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 dept d , emp e
 where e.deptno = d.deptno
 group by d.loc;

문제 301. 부서위치, 부서위치별 토탈월급을 출력하는데 dallas는 제외하여 출려그 부서위치별 토탈월급이 5000 이상인것만 출력, 토탈이 높은 것 부터

select d.loc, sum(e.sal)
 from dept d , emp e
 where e.deptno = d.deptno and  d.loc != 'DALLAS' 
 having sum(e.sal) >= 5000
 group by d.loc
 order by 2 desc;

  • 내 오답 : e.sum(sal) -> sum(e.sal) 이렇게 써야함!

문제 302. 아래의 테이블 생성

create table bonus 
 ( empno  number(10),
   b_bonus  number(10));
   
 insert into bonus // 서브쿼리 insert! 
  select empno, sal * 1.2
  from  emp;
    
commit;

문제 303. 사원이름, 월급, b_bonus를 출력하는데 직업이 salesman만 출력

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와 BOUNS 테이블을 조인해서 이름, 월급, 부서위치, b_bonus를 출력하는데 dallas에서 근무하는 사원들만 출력

select e.ename, e.sal, d.loc, b.b_bonus
 from emp e, bonus b, dept d
 where e.deptno = d.deptno and e.empno = b.empno and d.loc = 'DALLAS';
  • DEPT ----- EMP ----- BONUS 이렇게 연결된 것

▶ 오라클 xe 버전에 회사의 CRM서비스 구축하기

문제 305. 

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;

문제 307. jobs 테이블의 모든 데이터를 출력해서 보세요

 select * from jobs;

문제 308. employees, departments, jobs 를 join해서 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 j.job_id = e.job_id and e.department_id = d.department_id and e.last_name = 'King';

여러 테이블의 데이터를 조인해서 출력하기 2(NON EQUI JOIN)

※ 오라클 조인 문법 4가지
1. EQUI JOIN : 조인하려는 두 테이블의 조인조건이 이퀄(=)조건인 조인문법
ex) where d.d_id = e.d_id
2. NON EQUI JOIN : 조인하려는 두 테이블의 조인조건이 이퀄(=)조건이 아닌 조인문법
3. OUTER JOIN
4. SELF JOIN

문제 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 // join 조건
  and s.grade=1; // 검색 조건

문제 312. grade가 2등급인 사원들의 이름, 월급, grade, 부서위치 출력
( dept ----- emp ----- salgrade )

select e.ename, e.sal, s.grade, d.loc
 from emp e, salgrade s, dept d
 where e.sal between s.losal and s.hisal and e.deptno = d.deptno // 2개의 조인조건
   and s.grade=2; // 검색조건

여러 테이블의 데이터를 조인해서 출력하기 3(OUTER JOIN)

예제. 이름, 부서위치 출력

select e.ename, d.loc
 from emp e, dept d
 where e.deptno = d.deptno

  • dept 테이블에는 loc 컬럼에 보스톤이 있는데 이렇게 출력하면 보스톤이 안나온다
    emp 테이블에는 부서번호에 40번이 없기때문에 안나온 것임.
     이 데이터는 조인이 안된 아웃사이더 데이터 ! OUTER JOIN을 사용하자

※ 오라클 조인 문법 4가지
1. EQUI JOIN : 조인하려는 두 테이블의 조인조건이 이퀄(=)조건인 조인문법
ex) where d.d_id = e.d_id
2. NON EQUI JOIN : 조인하려는 두 테이블의 조인조건이 이퀄(=)조건이 아닌 조인문법
3. OUTER JOIN : equi 조인으로는 조인 안되는 결과를 보려고 할 때 사용하는 조인문법!
4. SELF JOIN

select e.ename, d.loc
  from emp e , dept d
  where e.deptno (+)= d.deptno;
  • (+)= 아우터조인 사인 !!! 이거 (+)를 어디에 쓸까? 모자란 쪽에 쓰기
    : dept 테이블쪽에서 출력되고 있는 보스톤을 보기 위해서 써준 것. 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. detp 테이블에는 있는 부서번호인데 emp테이블에는 없는 부서번호 출력

select deptno from dept 
 minus
 select deptno from emp;

문제 314. emp 테이블에는 존재하는 부서번호인데, dept테이블에는 존재하지 않는 부서번호 찾기

문제 315. employees테이블에는 있는 department_id인데, departments테이블에는 없는 department_id 출력하기

select department_id from departments
  minus
  select department_id from employees; // 바꾸면 있따

select department_id from employees
minus
select department_id from departments; //이건 null

문제 316. employees , departments테이블을 조인해서 last_name과 departmemt_name을 출
력하는데 departments테이블에는 있는 departmemt_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 로 시작하는 데이터를 가지고 다음의 결과를 출력하시오!

profile
망구씨
Slow and steady wins the race.
이전 포스트

[Oracle SQL]8일차_23.06.29

다음 포스트

[Oracle SQL]10일차_23.07.03

0개의 댓글