[DB] sub query, rownum

* sub query

- 쿼리문(create, insert, update, delete, select)안에 조회쿼리를 넣어서 사용하는 것

- sub query를 정의할 때에는 괄호()로 묶어서 사용한다.

- 조회 결과가 한 행이 조회되는 단수행 sub query(Scalar sub quert)와 여러행이 조회되는 복수행 sub query가 있다.

- sub query는 하나의 값으로 사용된다.

 

+ create sub query

- 테이블 생성시 테이블 복사로서 사용된다. 조회되는 테이블의 컬럼명, 데이터형, 크기, not null조건(제약사항이지만 not null조건만 예외)이 복사된다.

- 제약사항(primary key, foreign key, unique, check, default)은 복사되지 않는다.

- 문법

create table 테이블명 as (sub query ... );

-------------------------------------

-- create sub query

 

-- 문) dept테이블을 복사하는 cp_dept테이블을 생성하라. (제약사항을 저장하는 테이블은 user_constraints이다.)

select * from user_constraints;

create table cp_dept as(

select deptno, dname, loc from dept

);

select * from dept;

select * from cp_dept;

 

-- 문) 부서번호가 10, 30인 사원번호, 사원명, 부서번호, 입사일, 연봉을 조회하여 cp_emp테이블을 생성하라

create table cp_emp as (

select empno, ename, deptno, hiredate

from emp

where deptno in(10,30)

);

select * from cp_emp;

desc cp_emp;

 

-- *컬럼의 형식 수정 (추후에 다시 배울 것)

alter table cp_emp modify ename varchar2(10) not null;

-- 수정후 제약조건 바뀌고 추가된거 확인하기

create table cp_emp2 as(

select empno, ename, sal from emp

);

select * from user_constraints;

 

-- *테이블의 구조만 복사(레코드x)

-- where 1=1은 항상 참이므로 모든 레코드를 조회한다. 그러나 where 1=0는 부정이므로 레코드를 조회하지 않는다.

create table cp_dept2 as(

select deptno, dname, loc

from dept

where 1=0

);

select * from cp_dept2;

 

 

+ insert sub query

- 다른 테이블의 값을 가져와서(동적) 추가한다.

- 단수행 : 컬럼값이 하나

- 문법

insert into 테이블명 (컬럼명,,,) values(값,,, (select ...) ... );

- 복수행 : 조회된 값으로 한번에 많은 레코드를 추가(배치를 돌린다(배치처리)라고도 말한다.)

- 문법

insert into 테이블명 (컬럼명,,,컬럼명은 생략가능하다.) (select ... );

- insert sub query를 먼저 테스트 해보고 insert문을 실행하는것이 좋다. sql developer의 경우 서브쿼리만 실행 시킬 수 있다.

-------------------------------------

-- insert sub query

 

-- *단수행

-- cp_emp2테이블에 아래와 같은 데이터를 추가하라.

-- 사번 : 1214, 사원명 : 이진기, 연봉 : emp테이블에 사원번호가 7782인 사원의 연봉으로 추가한다.

insert into cp_emp2(empno, ename, sal) values(

1214, '이진기',(

select sal

from emp

where empno = 7782

)

);

-- └컬럼명 명시

insert into cp_emp2 values(

1214, '이진기',(

select sal

from emp

where empno = 7782

)

);

-- └컬럼명 생략

commit;

select * from cp_emp2;

 

-- *복수행

-- dept테이블에 모든 레코드를 cp_dept2테이블로 추가하라

insert into cp_dept2 (

(

select *

from dept

)

);

-- └컬럼명 생략

insert into cp_dept2(deptno, dname, loc) (

(

select deptno, dname, loc

from dept

)

);

-- └컬럼명 명시(직관적인 코딩을 위해 컬럼명을 명시하여 주는 것이 좋다.)

select * from cp_dept2;

 

-- *error 예제

-- 단수행 서브쿼리가 사용되는 곳에서 복수행 서브쿼리가 들어가면 에러

-- error msg : 단일 행 하위 질의에 2개 이상의 행이 리턴되었습니다.

insert into cp_emp2(ename, sal) values(

'최민호', (

select sal

from emp

)

);

 

 

+ update sub query

- 단수행

- 문법

update 테이블명

set 값을 바꾸고자하는 컬럼명 = 변경할 값,,, 컬럼명 = ( select ... )

where 컬럼명 = ( select ... )

-------------------------------------

-- update sub query

 

-- cp_emp2테이블에서 사원번호가 1111번인 사원의 연봉을 emp 테이블에 사원번호가 7788인 사원의 연봉으로 변경

update cp_emp2

set sal = (select sal from emp where empno=7788)

where empno = 1214;

select * from cp_emp2;

 

 

+ delete sub query

- 단수행

- 문법

delete from 테이블명

where 컬럼명 = ( select ... )

[참고] update와 delete도 in을 이용하면 복수행 사용이 가능하다.

-------------------------------------

-- delete sub query

 

-- emp테이블의 사원번호가 7788인 사원이 수령하는 연봉과 같은 연봉을 받는 사원을 cp_emp2테이블에서 삭제하라

delete from cp_emp2

where sal = (

select sal

from emp

where empno=7788

);

select * from cp_emp2;

 

 

+ select sub query

- 단수행

- 문법

select 컬럼명,,, ( select ... )

from 테이블명

where 컬럼명 = 값,,, ( select ... )

- 복수행 : 조회한 결과를 재조회할 때 사용한다.

- 문법

select 컬럼명,,, (조회한 결과의 컬럼명)

from ( select ... )

...

-------------------------------------

-- select sub query

 

-- *단수행

-- emp테이블의 사원번호가 7499번인 사원과 같은 부서에 근무하는 사원의 모든 정보를 cp_emp테이블에서 조회하라

select *

from cp_emp

where deptno = (select deptno from emp where empno=7499);

 

-- *복수행

-- 문) emp테이블에서 가장 마지막 입사한 사원부터 모든 컬럼을 출력하라. 단, 10건의 레코드만 출력하라.

select rownum, empno, ename, hiredate

from (select rownum, empno, ename, hiredate from emp order by hiredate desc)

where rownum between 1 and 10;

-- └rownum은 select절에 종속되며 종속된 select절에 따라 독립적이다.

 

-- *중첩 예제

select r, empno, ename, hiredate

from (select rownum r, empno, ename, hiredate

 from (select rownum, empno, ename, hiredate

  from emp

  order by hiredate desc))

where r between 2 and 10;

-- └ailas를 붙여 상위 select절에서 실제 컬럼처럼 사용 가능하게 바꾸었다.

 

-- *위 예제 단위별로 끊어 보기

-- 1) 가장 안쪽 서브쿼리

select rownum, empno, ename, hiredate

  from emp

  order by hiredate desc;

-- └rownum먼저 실행되고 정렬되기 때문에 실행시 rownum컬럼의 값이 섞여있는것을 확인 할 수 있다. 총 14개 조회

-- 2) 두번째 서브쿼리

select rownum r, empno, ename, hiredate

from (select rownum, empno, ename, hiredate

      from emp

      order by hiredate desc);

-- └하위 select의 rownum과 상위 select의 rownum r과 다르다는 것을 확인 할 수 있으며 r컬럼의 값은 다시 1부터 순차적으로 매겨지게 된다.

-- 3) 마지막 select문

select r, empno, ename, hiredate

from (select rownum r, empno, ename, hiredate

 from (select rownum, empno, ename, hiredate

  from emp

  order by hiredate desc))

where r between 2 and 10;

-- └두번째 서브쿼리에서 선언한 r을 그대로 가져온다. 이때 alias를 이용해서 명시해주었기 때문에 실제 컬럼과 같이 사용할 수 있다.

-- 따라서 일반 rownum에서는 사용 불가능했던 범위 검색도 가능해진다.

 

-- 문) 우편번호 테이블에서 강남구에 해당하는 우편번호, 시도, 도군, 동, 번지를 조회하라.

-- 단, 우편번호의 가장 마지막 번호에서부터 정렬하고 100~200번사이의 레코드만 출력하라

SELECT r, ZIPCODE, SIDO, GUGUN, DONG, BUNJI

FROM (

select rownum r, ZIPCODE, SIDO, GUGUN, DONG, BUNJI

from (

select *

from ZIPCODE

where gugun = '강남구'

ORDER BY ZIPCODE desc))

WHERE r between 100 and 200;

 

 

* rownum

- select에서 사용할 수 있는 번호를 가진 컬럼이다.

- 실제 컬럼은 아니다.(가상 컬럼(pseudo컬럼)이다.)

- order by절 보다 먼저 생성되므로 정렬 수행시 번호가 섞이게 된다.

- 조건절에 사용할 수 있다. 그러나 1번부터는 검색이 되지만 그 다음 번호부터는 검색이 되지 않는다.

- select마다 사용할 수 있다.

- 문법

select rownum, 컬럼명,,,,

- 순차적인 번호를 가지고 있지않은 테이블에 번호를 부여할 때 사용한다.

- rownum은 select절에 종속되며 종속된 select절에 따라 독립적이다. 이때 rownum 컬럼을 상위 select절에서 실제 컬럼처럼 사용하고 싶다면 alias를 붙여서 사용하면 된다.

--

-- *rownun

 

-- 번호, 사원번호, 사원명, 입사일을 조회하라

select rownum, empno, ename, hiredate from emp;

 

select rownum, zipcode, sido, gugun, dong, bunji, seq

from zipcode

where dong like '상도동%';

 

-- 번호, 사원번호, 사원명을 조회하라. 단, 사원명의 오름차순으로 정렬하라

select rownum, empno, ename

from emp

order by ename;

 

-- 번호, 사원번호, 사원명, 입사일을 조회하라. 단, 번호가 1번부터 10번까지만 출력하라

select rownum, empno, ename

from emp

where rownum between 1 and 10;

-- └or where rownum >= 1 and rownum <= 10;

select rownum, empno, ename

from emp

where rownum between 2 and 10;

-- └no error but no records