728x90
<문1> emp테이블에서 SCOTT사원이 받는 급여보다 많이 받는 사원을 구해서 그 사원의 사원번호, 이름, 업무, 급여순으로 출력하는 SQL?
1) SCOTT사원이 받는 급여 => 서브쿼리
select sal from emp where ename='SCOTT';
SAL
----------
3000
2) select empno, ename, job, sal from emp where sal > 3000; => 메인쿼리
SQL> select empno, ename, job, sal from emp where sal > 3000;
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7839 KING PRESIDENT 5000
3) 1) + 2)
select empno, ename, job, sal from emp where sal > (select sal from emp where ename='SCOTT');
SQL> select empno, ename, job, sal from emp where sal > (select sal from emp where ename='SCOTT');
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7839 KING PRESIDENT 5000
<문2> emp 테이블에서 가장 입사일이 오래된 사원의 정보를 출력?
(이름, 입사날짜 순으로 출력)
1) select min(hiredate) from emp;
SQL> select min(hiredate) from emp;
MIN(HIRE
--------
80/12/17
2) select ename, hiredate from emp where hiredate='80/12/17';
SQL> select ename, hiredate from emp where hiredate='80/12/17';
ENAME HIREDATE
---------- --------
SMITH 80/12/17
3) 1) + 2)
SQL> select ename, hiredate from emp where hiredate=(select min(hiredate) from emp);
ENAME HIREDATE
---------- --------
SMITH 80/12/17
<문3> emp테이블에서 사원번호가 7521인 사원과 업무가 같고(and) 사원번호가 7934인 사원보다 급여를 많이 받는 사원을 구해서 그 사원의 사원번호, 이름, 급여순으로 출력하시오.
1) 사원번호가 7521인 사원의 업무?
select job from emp where empno=7521;
SQL> select job from emp where empno=7521;
JOB
---------
SALESMAN
2) 사원번호가 7934인 사원의 급여?
select sal from emp where empno=7934;
SQL> select sal from emp where empno=7934;
SAL
----------
1300
3) 구해야하는 답
select empno, ename, job, sal from emp where job='SALESMAN' and sal > 1300;
SQL> select empno, ename, job, sal from emp where job='SALESMAN' and sal > 1300;
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7499 ALLEN SALESMAN 1600 7844 TURNER SALESMAN 1500
4) 1) + 2)
select empno, ename, job, sal from emp where job=(select job from emp where empno=7521) and sal > ( select sal from emp where empno=7934);
SQL> select empno, ename, job, sal from emp where job=(select job from emp where empno=7521) and sal > ( select sal from emp where empno=7934);
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7499 ALLEN SALESMAN 1600 7844 TURNER SALESMAN 1500
<문4> emp테이블에서 급여의 평균보다 적은 사원을 찾아서 그 사원의 이름, 업무, 급여순으로 출력하시오.
1) 급여의 평균 => select avg(sal) from emp;
SQL> select avg(sal) from emp;
AVG(SAL)
----------
2073.21429
2) select ename, job, sal from emp where sal < 2073.21429;
SQL> select ename, job, sal from emp where sal < 2073.21429;
ENAME JOB SAL
---------- --------- ----------
SMITH CLERK 800 ALLEN SALESMAN 1600 WARD SALESMAN 1250 MARTIN SALESMAN 1250 TURNER SALESMAN 1500 ADAMS CLERK 1100 JAMES CLERK 950 MILLER CLERK 1300 8 개의 행이 선택되었습니다.
3) select ename, job, sal from emp where sal < (select avg(sal) from emp);
SQL> select ename, job, sal from emp where sal < (select avg(sal) from emp);
NAME JOB SAL
---------- --------- ----------
SMITH CLERK 800 ALLEN SALESMAN 1600 WARD SALESMAN 1250 MARTIN SALESMAN 1250 TURNER SALESMAN 1500 ADAMS CLERK 1100 JAMES CLERK 950 MILLER CLERK 1300 8 개의 행이 선택되었습니다.
<문5> emp테이블에서 사원의 급여가 20번부서의 최소급여보다 많이 받는 부서를 부서별로(=group bydeptno) 출력하시오(부서번호, 최소급여순으로 출력).
1) 20번 부서의 최소급여?
select min(sal) from emp where deptno=20;
SQL> select min(sal) from emp where deptno=20;
MIN(SAL)
----------
800
2) select deptno, min(sal) from emp group by deptno having min(sal) > 800;
SQL> select deptno, min(sal) from emp group by deptno having min(sal) > 800;
DEPTNO MIN(SAL)
---------- ----------
30 950 10 1300
3) select deptno, min(sal) from emp group by deptno having min(sal) > (select min(sal) from emp where deptno=20);
SQL> select deptno, min(sal) from emp group by deptno having min(sal) > (select min(sal) from emp where deptno=20);
DEPTNO MIN(SAL)
---------- ----------
30 950 10 1300
<문6> 부서별로 최소급여를 받는 사원의 이름, 업무, 급여, 부서번호순으로 출력하시오.
서브쿼리의 종류
단일행 서브쿼리 => 실행결과(행이 한개)
연산자 =, <=, >=
다중행 서브쿼리 => 실행결과(행이 한개이상)
연산자 in, any, all
1) 부서별로 최소급여?
select min(sal) from emp group by deptno;
SQL> select min(sal) from emp group by deptno;
MIN(SAL)
----------
950 800 1300
2) 메인
select ename, job, sal, deptno from emp where sal=(select min(sal) from emp group by deptno);
SQL> select ename, job, sal, deptno from emp where sal=(select min(sal) from emp group by deptno); select ename, job, sal, deptno from emp where sal=(select min(sal) from emp group by deptno) * 1행에 오류: ORA-01427: 단일 행 하위 질의에 2개 이상의 행이 리턴되었습니다.
=> 오류 원인: 서브쿼리의 결과로 행이 한개이상 나오는데 처리해주는 연산자는 단일행 연산자를 사용했기 때문에
즉, 연산자오류 때문에 발생.다음과 같이 수정해야 한다.
SQL> select ename, job, sal, deptno from emp where sal in(select min(sal) from emp group by deptno);
ENAME JOB SAL DEPTNO
---------- --------- ---------- ----------
SMITH CLERK 800 20 JAMES CLERK 950 30 MILLER CLERK 1300 10
= 을 in으로 수정하여 작성한다.
물리적으로 여러개의 행이 나오는 서브쿼리는 => in
<문7> any 연산자를 사용하는 경우(다중행의 논리적인 경우)
emp 테이블에서 30번부서의 최소급여를 받는 사원보다 많은 급여를 받는 사원을 구해서 그 사원의 이름, 업무, 급여, 부서번호를 출력. (단 30번 부서는 제외한다. (=10, 20번 부서만 적용))
1) 30번 부서의 최소급여?
select min(sal) from emp where deptno=30;
SQL> select min(sal) from emp where deptno=30;
MIN(SAL)
----------
950
2) select ename, job, deptno from emp where deptno!=30 and sal > 950;
SQL> select ename, job, sal, deptno from emp where deptno!=30 and sal > 950;
ENAME JOB SAL DEPTNO
---------- --------- ---------- ----------
JONES MANAGER 2975 20 CLARK MANAGER 2450 10 SCOTT ANALYST 3000 20 KING PRESIDENT 5000 10 ADAMS CLERK 1100 20 FORD ANALYST 3000 20 MILLER CLERK 1300 10 7 개의 행이 선택되었습니다.
3) 1) + 2)
SQL> select ename, job, sal, deptno from emp where deptno!=30 and sal > (select min(sal) from emp where deptno=30);
ENAME JOB SAL DEPTNO
---------- --------- ---------- ----------
JONES MANAGER 2975 20 CLARK MANAGER 2450 10 SCOTT ANALYST 3000 20 KING PRESIDENT 5000 10 ADAMS CLERK 1100 20 FORD ANALYST 3000 20 MILLER CLERK 1300 10 7 개의 행이 선택되었습니다.
4) 서브쿼리 단일행 -> any 로 변경하여 사용
SQL> select ename, job, sal, deptno from emp where deptno!=30 and sal > any(select sal from emp where deptno=30);
ENAME JOB SAL DEPTNO
---------- --------- ---------- ----------
KING PRESIDENT 5000 10 SCOTT ANALYST 3000 20 FORD ANALYST 3000 20 JONES MANAGER 2975 20 CLARK MANAGER 2450 10 MILLER CLERK 1300 10 ADAMS CLERK 1100 20 7 개의 행이 선택되었습니다.
any(조건식)
조건식에 해당하는 가장 작은 값을 구한뒤 기존 조건에 만족하는 값을 하나씩 비교해서 큰값 순으로 내림차순 정렬해주는 기능
<문8> 30번 부서의 최대급여를 받는 사원보다 적은 급여를 받는 사원의 정보를 구하시오.
1) 30번 부서의 최대급여?
select max(sal) from emp where deptno=30;
SQL> select max(sal) from emp where deptno=30;
MAX(SAL)
----------
2850
2) select ename, job, sal, deptno from emp where deptno!=30 and sal < (select max(sal) from emp where deptno=30);
SQL> select ename, job, sal, deptno from emp where deptno!=30 and sal < (select max(sal) from emp where deptno=30);
ENAME JOB SAL DEPTNO
---------- --------- ---------- ----------
SMITH CLERK 800 20 CLARK MANAGER 2450 10 ADAMS CLERK 1100 20 MILLER CLERK 1300 10
3) any 사용
SQL>select ename, job, sal, deptno from emp where deptno!=30 and sal < any(select sal from emp where deptno=30) ;
ENAME JOB SAL DEPTNO
---------- --------- ---------- ----------
SMITH CLERK 800 20 ADAMS CLERK 1100 20 MILLER CLERK 1300 10 CLARK MANAGER 2450 10
물리적으로 여러개의 행이 나오는 서브쿼리는 => in
any 비교해서 큰값 => 큰값보다 작은순 => 오름차순
작은값 => 작은값을 기준 => 내림차순
`> any(작은값을 구하는 SQL)` => 내림차순
`< any(큰값을 구하는 SQL)` => 오름차순
<문9> 업무가 MANAGER인 사원의 이름, 업무, 부서명, 근무지를 출력하시오.
서브쿼리 -> where 조건식 이외의 패턴
인라인뷰 -> SQL구문에서 from 뒤에 서브쿼리가 나오는 구문
select e.ename, e.job, d.dname, d.loc from emp e,dept d where e.deptno=d.deptno and e.job='MANAGER';
SQL> select e.ename, e.job, d.dname, d.loc from emp e,dept d where e.deptno=d.deptno and e.job='MANAGER';
ENAME JOB DNAME LOC
---------- --------- -------------- -------------
JONES MANAGER RESEARCH DALLAS BLAKE MANAGER SALES CHICAGO CLARK MANAGER ACCOUNTING NEW YORK
필드갯수 줄이고, 조건식에 대한 데이터만 메모리에 올리도록 수정
SQL> select e.ename, e.job, d.dname, d.loc from(select ename, job, deptno from emp where job='MANAGER') e,dept d where e.deptno=d.deptno;
ENAME JOB DNAME LOC
---------- --------- -------------- -------------
JONES MANAGER RESEARCH DALLAS BLAKE MANAGER SALES CHICAGO CLARK MANAGER ACCOUNTING NEW YORK
<문10> 서브쿼리 -> select와 from 사이에도 서브쿼리가 나올 수 있다.
dept테이블에서 부서위치가 NEW YORK(중앙), NEW YORK을 제외한나머지 도시근무(변두리)
SQL> select deptno, dname, (case when deptno in(select deptno from dept where loc='NEW YORK') then '중앙' else '변두리주' end) "부서위치" from dept;
DEPTNO DNAME 부서위치
---------- -------------- --------
10 ACCOUNTING 중앙 20 RESEARCH 변두리주 30 SALES 변두리주 40 OPERATIONS 변두리주
<문11> emp테이블에서 부서별로 부서번호, 인원수, 평균급여, 급여합계, 최소급여, 최대급여를 구해서 emp_deptno 백업테이블로 저장하시오.
서브쿼리에서의 사용시 주의할 점 => 테이블을 생성할 수 있다.
create table 테이블명
백업 table => create table 백업t명 as SQL구문
=> SQL구문에 해당하는 조건을 찾아서 그 데이터를 출력
=> 테이블에 만들어서 출력(테이블에 저장)
아래의 오류 주의
SQL> create table emp_deptno as select deptno, count(*), avg(sal), sum(sal), min(sal), max(sal) from emp group by deptno; create table emp_deptno as select deptno, count(*), avg(sal), sum(sal), min(sal), max(sal) from emp group by deptno * 1행에 오류: ORA-00998: 이 식은 열의 별명과 함께 지정해야 합니다
=> 가상필드는 필드로 복사가 안되기 때문에 발생하는 에러메세지
=> 실제존재X 계산식의 가상필드에 어울리는 이름을 부여 -> 별명, 별칭
SQL> create table emp_deptno(deptno, e_count, e_avg, e_sum, e_min, e_max) as select deptno, count(*), avg(sal), sum(sal), min(sal), max(sal) from emp group by deptno; 테이블이 생성되었습니다.
SQL> select * from emp_deptno DEPTNO E_COUNT E_AVG E_SUM E_MIN E_MAX ---------- ---------- ---------- ---------- ---------- ---------- 30 6 1566.66667 9400 950 2850 20 5 2175 10875 800 3000 10 3 2916.66667 8750 1300 5000
sql>desc emp_deptno; //실제로 필드가 만들어져서 복사된 상태에서 저장
SQL> desc emp_deptno; 이름 널? 유형 ----------------------------------------- -------- ---------------------------- DEPTNO NUMBER(2) E_COUNT NUMBER E_AVG NUMBER E_SUM NUMBER E_MIN NUMBER E_MAX NUMBER
형식) create table 백업t명(가상필드에 대응되는 필드명,필드명2,,,,)
as sql구문
테이블의 구조(=Schema(스키마))만 복사하기=>테이블만 구조만 복사하고 싶다.
데이터는 필요X
폴더(파일 X)
create table 백업t명 as select * from 원본t명 //내용 O, 필드 O
**** 제약조건은요? =>복사X ****
create table 백업t명 as select * from 원본t명 where 조건식=>조건식에 해당
create table 백업t명 as select * from 원본t명 where 1=0 //조건식이 거짓
SQL> create table b_emp2 as select empno,ename,job from emp where 1=0; 테이블이 생성되었습니다. SQL> select count(*) from b_emp2;
COUNT(*)
----------
0
<문12> 부서번호가 40부서에 근무할 직원의 데이터를 입력하라(서브쿼리 이용)
서브쿼리=>DML을 이용해서 서브쿼리 사용이 가능하다.
DML의 종류: insert, update, delete
SQL> create table b_emp3 as select * from emp; 테이블이 생성되었습니다.
SQL> insert into (select empno, ename, sal, hiredate, job, deptno from b_emp3 where deptno=40) values(7777, 'JANG', 4000, to_date('20-07-06','rr-mm-dd'), 'MANAGER',40); 1 개의 행이 만들어졌습니다. SQL> select * from b_emp3 where deptno=40;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7777 JANG MANAGER 20/07/06 4000 40
insert into + select 구문을 통해서 필드를 나열하기 위해서 select구문 사용
<문13> b_emp3 테이블에서 SCOTT의 업무와 급여가 일치하도록 JONES의 업무와 급여를 수정하는 SQL을 작성하시오.
update+subQuery 적용
형식) update 수정t
set (필드명1, 필드명2, 필드명3...) =(서브쿼리를 이용) 수정할 값
where 조건식
1) JONES의 업무와 급여
select job, sal from emp
where ename=upper('&ename');
SQL> select job, sal from emp where ename=upper('&ename'); ename의 값을 입력하십시오: jones 구 1: select job, sal from emp where ename=upper('&ename') 신 1: select job, sal from emp where ename=upper('jones')
JOB SAL
--------- ----------
MANAGER 2975
SCOTT
SQL> / ename의 값을 입력하십시오: scott 구 1: select job, sal from emp where ename=upper('&ename') 신 1: select job, sal from emp where ename=upper('scott')
JOB SAL
--------- ----------
ANALYST 3000
일치하도록 작성
SQL> update b_emp3 set(job, sal)=(select job, sal from b_emp3 where ename='SCOTT') where ename='JONES'; 1 행이 갱신되었습니다.
SQL> select job, sal from b_emp3 where ename='JONES';
JOB SAL
--------- ----------
ANALYST 3000
<문14> b_emp3의 자료중에서 부서명이 'SALES'인 사원의 정보를 삭제하시오.
delete구문 + subQuery 사용이 가능하다.
형식) delete from 테이블명 // 모든 데이터 삭제
delete from 테이블명 where 조건식(=서브쿼리)
1) 부서명이 SALES인 부서번호?
select deptno from dept where dname='SALES';
SQL> select deptno from dept where dname='SALES';
DEPTNO
----------
30
2) delete from b_emp3 where deptno = (select deptno from dept where dname='SALES');
(30번 부서를 알고있다면 서브쿼리 대신 30(부서번호) 사용이 가능하나 모를 때 서브쿼리 사용하여 삭제 )
SQL> delete from b_emp3 where deptno = (select deptno from dept where dname='SALES');
6 행이 삭제되었습니다.
inser, update, delete 복구 기능
rollback // 되돌리기
3) rollback
SQL> rollback 2 ; 롤백이 완료되었습니다. SQL> select count(*) from b_emp3; COUNT(*) ---------- 14
<문15> exit 연산자 => 서브쿼리의 결과가 참인지 아닌지를 체크해주는 연산자
서브쿼리에서 적어도 1개의 행을 리턴 => 참
리턴해주는 행이 없음을 의미 => 거짓
select * from emp where 1=2; 거짓
SQL> select * from emp where 1=2; 선택된 레코드가 없습니다.
select * from emp where 2=2; 참
SQL> select * from emp where 2=2;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 80/12/17 800 20 7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30 7521 WARD SALESMAN 7698 81/02/22 1250 500 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7566
JONES MANAGER 7839 81/04/02 2975 20 7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30 7698 BLAKE MANAGER 7839 81/05/01 2850 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 81/06/09 2450 10 7788 SCOTT ANALYST 7566 87/04/19 3000 20 7839 KING PRESIDENT 81/11/17 5000 10 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30 7876 ADAMS CLERK 7788 87/05/23 1100 20 7900 JAMES CLERK 7698 81/12/03 950 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------- ---------- ----------
DEPTNO
----------
7902 FORD ANALYST 7566 81/12/03 3000 20 7934 MILLER CLERK 7782 82/01/23 1300 10 14 개의 행이 선택되었습니다.
부서테이블의 부서번호가 10인 데이터를 보여주시오(emp).
select empno, ename, sal from emp where exists(select * from dept where deptno=10);
SQL> select empno, ename, sal from emp where exists(select * from dept where deptno=10);
EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 800 7499 ALLEN 1600 7521 WARD 1250 7566 JONES 2975 7654 MARTIN 1250 7698 BLAKE 2850 7782 CLARK 2450 7788 SCOTT 3000 7839 KING 5000 7844 TURNER 1500 7876 ADAMS 1100
MPNO ENAME SAL
---------- ---------- ----------
7900 JAMES 950 7902 FORD 3000 7934 MILLER 1300 14 개의 행이 선택되었습니다.
집합연산자
union, union all
서로 다른 두개의 SQL문장의 실행결과를 하나로 합쳐서 결과를 보여주는 연산자.
-- emp 테이블에서 업무가 MANAGER인 사원의 사번, 이름을 구하시오.
select empno, ename from emp where job='MANAGER';
SQL> select empno, ename from emp where job='MANAGER';
EMPNO ENAME
---------- ----------
7566 JONES 7698 BLAKE 7782 CLARK
-- 이름이 A로 시작하는 사원의 사번, 이름을 구하시오.
select empno, ename from emp where ename like 'A%';
SQL> select empno, ename from emp where ename like 'A%';
EMPNO ENAME
---------- ----------
7499 ALLEN 7876 ADAMS
-- 두 문장을 union으로 합치기
select empno, ename from emp where job='MANAGER' union select empno, ename from emp where ename like 'A%';
SQL> select empno, ename from emp where job='MANAGER' union select empno, ename from emp where ename like 'A%';
EMPNO ENAME
---------- ----------
7499 ALLEN 7566 JONES 7698 BLAKE 7782 CLARK 7876 ADAMS
-- union all
SQL> select empno, ename from emp where job='MANAGER' union all select empno, ename from emp where ename like 'A%';
EMPNO ENAME
---------- ----------
7566 JONES 7698 BLAKE 7782 CLARK 7499 ALLEN 7876 ADAMS'SQL' 카테고리의 다른 글
| PL/SQL 기초 실습 (2) | 2022.12.14 |
|---|---|
| SQL Oracle DB XE 11g환경 구축 (1) | 2022.12.14 |
| PL/SQL 프로시저 IN, OUT, IN OUT 차이 (1) | 2022.12.14 |
| R-SQL(ORACLE) 연동 기초 문제 (0) | 2022.12.14 |
| R-SQL 연동(csv,DataFrame) (0) | 2022.12.14 |
