*subquery
1. blake 와 같은 부서에 있는 모든 직원의 사번, 이름, 입사일자 조회
select deptno, empno, ename, hiredate from emp where deptno=(select deptno from emp where ename='BLAKE');
DEPTNO EMPNO ENAME HIREDATE
-------- ---------- ---------- --------
30 7499 ALLEN 81/02/20
30 7521 WARD 81/02/22
30 7654 MARTIN 81/09/28
30 7698 BLAKE 81/05/01
30 7844 TURNER 81/09/08
30 7900 JAMES 81/12/03
2. select empno, ename, deptno, sal, comm from emp
where (sal, nvl(comm,0)) in (select sal, nvl(comm,0) from emp where deptno=30);
쿼리를 수정하여 보너스가 null 인 직원들도 출력될 수 있도록 하시오.
EMPNO ENAME DEPTNO SAL COMM
------- ---------- ---------- ---------- ----------
7499 ALLEN 30 1600 300
7521 WARD 30 1250 500
7654 MARTIN 30 1250 1400
7698 BLAKE 30 2850
7844 TURNER 30 1500 0
7900 JAMES 30 950
3. 평균 급여 이상을 받는 직원들의 사번, 이름을 조회. 단, 급여가 많은 순으로 정렬
select empno, ename, sal from emp where
sal >=(select avg(sal) from emp) order by sal desc;
EMPNO ENAME SAL
------- ---------- ----------
7839 KING 5000
7902 FORD 3000
7788 SCOTT 3000
7566 JONES 2975
7698 BLAKE 2850
7782 CLARK 2450
select empno, ename, avg(sal), sal from emp having
sal >=(select min(sal) from emp) group by avg(sal);
4. 이름에 T 자가 들어가는 직원이 근무하는 부서에서 근무하는 직원의 사번, 이름 급여 조회
select empno, ename, sal, deptno from emp where deptno in( select deptno from emp where ename like '%T%');
EMPNO ENAME SAL DEPTNO
-------- ---------- ---------- ----------
7902 FORD 3000 20
7876 ADAMS 1100 20
7788 SCOTT 3000 20
7566 JONES 2975 20
7369 SMITH 800 20
7900 JAMES 950 30
7844 TURNER 1500 30
7698 BLAKE 2850 30
7654 MARTIN 1250 30
7521 WARD 1250 30
7499 ALLEN 1600 30
5. 부서의 위치가 dallas 인 모든 직원에 대해 사번, 이름, 급여, 업무 조회
select empno, ename, sal, job from emp where deptno in( select deptno from dept where loc like 'DALLAS');
EMPNO ENAME SAL JOB
--------- ---------- ---------- --------
7369 SMITH 800 CLERK
7566 JONES 2975 MANAGER
7788 SCOTT 3000 ANALYST
7876 ADAMS 1100 CLERK
7902 FORD 3000 ANALYST
6. King 에게 보고하는 모든 직원의 이름과 부서, 업무, 급여를 조회
select ename, deptno, job, sal from emp where mgr in(select empno from emp where ename ='KING');
ENAME DEPTNO JOB SAL
---------- ---------- --------- ----------
JONES 20 MANAGER 2975
BLAKE 30 MANAGER 2850
CLARK 10 MANAGER 2450
7. 월급이 30번 부서의 최저급여보다 높은 직원의 사번, 이름, 급여를 조회
select empno, ename, sal from emp
where sal>(select min(sal) from emp where deptno='30');
EMPNO ENAME SAL
---------- ---------- ----------
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
7902 FORD 3000
EMPNO ENAME SAL
---------- ---------- ----------
7934 MILLER 1300
8. 10번부서에서 30번 부서의 직원과 같은 업무를 하는 직원의 이름과 업무 조회
select ename, job from emp where deptno ='10'and job in(select job from emp where deptno='30');
ENAME JOB
---------- ---------
CLARK MANAGER
MILLER CLERK
***JOIN
9. Newyork 에서 근무하는 직원의 사번, 이름, 업무, 부서명 을 조회
select empno, ename, job, dname, loc from emp inner join dept on emp.deptno = dept.deptno and dept.loc = 'NEW YORK';
EMPNO ENAME JOB DNAME LOC
------ ---------- --------- -------------- ----------
7782 CLARK MANAGER ACCOUNTING NEW YORK
7839 KING PRESIDENT ACCOUNTING NEW YORK
7934 MILLER CLERK ACCOUNTING NEW YORK
10. 커미션을 받는 직원에 대해 이름, 부서명, 근무지를 조회
select ename, dname, loc, comm from emp
inner join dept on emp.deptno = dept.deptno
and emp.comm is not null;
ENAME DNAME LOC COMM
---------- -------------- ------------- ----------
TURNER SALES CHICAGO 0
MARTIN SALES CHICAGO 1400
WARD SALES CHICAGO 500
ALLEN SALES CHICAGO 300
11. 이름 중간에 L 자가 있는 직원의 이름, 업무, 부서명, 근무지 조회
select ename, job, dname, loc from emp inner join dept on emp.deptno = dept.deptno where ename like '%L%';
ENAME JOB DNAME LOC
---------- --------- -------------- --------
MILLER CLERK ACCOUNTING NEW YORK
CLARK MANAGER ACCOUNTING NEW YORK
BLAKE MANAGER SALES CHICAGO
ALLEN SALESMAN SALES CHICAGO
12. 각 직원들에 대해 그들의 관리자보다 먼저 입사한 직원의 이름, 입사일, 관리자이름, 관리자입사일 을 조회
select e.ename, e.hiredate, m.ename, m.hiredate from emp e, emp m
where e.mgr = m.empno and e.hiredate<m.hiredate;
ENAME HIREDATE ENAME HIREDATE
---------- -------- ---------- --------
WARD 81/02/22 BLAKE 81/05/01
ALLEN 81/02/20 BLAKE 81/05/01
CLARK 81/06/09 KING 81/11/17
BLAKE 81/05/01 KING 81/11/17
JONES 81/04/02 KING 81/11/17
SMITH 80/12/17 FORD 81/12/03
13. 말단사원의 사번, 이름, 업무, 부서번호, 근무지를 조회
select empno, ename, job, emp.deptno, loc
from emp right outer join dept on emp.deptno= dept.deptno
where empno not in( select mgr from emp where mgr is not null);
select empno, ename, job, emp.deptno, loc
from emp right outer join dept on emp.deptno= dept.deptno
where empno not in(select nvl(mgr,-1) from emp);
Create table tblbook(
author varchar2(20),
title varchar2(20)
);
insert into tblbook values('최주현', '하늘과 땅');
insert into tblbook values('최주현', '바다');
insert into tblbook values('유은정', '바다');
insert into tblbook values('박성우', '문');
insert into tblbook values('최주현', '문');
insert into tblbook values('박성우', '천국');
insert into tblbook values('최주현', '천국');
insert into tblbook values('최지은', '천국');
insert into tblbook values('박성우', '고슴도치');
insert into tblbook values('서금동', '나');
하나의 잡에 두명의 임프넘버
14. 한권의 책에 대해 두명 이상의 작가가 쓴 책을 검색 하시오
책이름 작가명 작가명
바다 문 천국
select title, author from tblbook
UNION all
select title, author from tblbook;
select a.title, a.author, b.author from tblbook a, tblbook b where a.title = b.title and a.author < b.author;
TITLE AUTHOR AUTHOR
-------------------- -------------------- -------
바다 최주현 유은정
문 최주현 박성우
천국 최지은 박성우
천국 최주현 박성우
천국 최지은 최주현
15. 한권의 책에 대해 세명의 작가가 쓴 책을 검색하시오
책이름 작가명 작가명
천국
select a.title, a.author, b.author, c.author from tblbook a, tblbook b, tblbook c where a.title = b.title and b.title = c.title and a.author < b.author and b.author < c.author;
TITLE AUTHOR AUTHOR
-------------------- -------------------- --------------------
AUTHOR
--------------------
천국 박성우 최주현
최지은
댓글 없음:
댓글 쓰기