입사한 년도(4자리), 입사한 년도별 평균월급을 출력하는데 맨아래에 전체 평균월급을 출력하시오!
select nvl(to_Char(hiredate,'YYYY'),'전체평균') as 입사년도,to_char(round(avg(sal)),'999,999')as 평균월급
from emp
group by rollup(to_Char(hiredate,'YYYY'))
order by 1 asc;
[TIL 10] _240524
※ 테이블 조인은 연결 조건만 있으면 여러개의 테이블을 조인할 수 있습니다. 1. 2개의 테이블 조인 ---> 조인 연결 조건 1개 2. 3개의 테이블 조인 ---> 조인 연결 조건 2개 3. 4개의 테이블 조인 ---> 조인 연결 조건 3개
문제1. 부서테이블의 모든 컬럼과 데이터를 출력하시오.
select * from dept;
문제2. 사원(emp) 테이블과 부서(dept) 테이블을 조인해서 이름과 부서위치를 출력해서 해당 사원이 어느 부서위치에서 근무하는지 알 수 있게 하시오
select ename, loc
from emp,dept
where emp.deptno=dept.deptno;
설명: emp와 dept 테이블에 동시에 있는 col 은 deptno이다 따라서 deptno로 두 테이블을 엮는다 where 절에 조인조건을 기술하지 않고 조인을 하게 되면 모두 다 조인을 하게 되어서 emp와 dept의 경우는 14건이 나오지 않고 총 56건이 나오게 된다. *cartisian product : 불필요한 데이터 추출 *
문제3. emp 와 dept 를 조인해서 이름과 입사일과 월급과 직업과 부서위치를 출력하시오 !
select ename, hiredate, sal, job
from emp, dept
where emp.deptno=dept.deptno;
문제4. 위의 결과를 다시 출력하는데 직업이 SALESMAN인사람들만 출력하시오.
select ename, hiredate, sal, job
from emp, dept
where emp.deptno=dept.deptno
and job = 'SALESMAN';
설명: emp.deptno = dept.deptno 는 조인조건이고 job ='SALESMAN' 은 검색조건 입니다.
문제5. 이름, 월급, 직업, 부서위치와 부서번호를 출력하시오.
select ename, sal, job, loc,deptno
from emp, dept
where emp.deptno = dept.deptno ;
select ename, sal, job, loc,emp.deptno
from emp, dept
where emp.deptno = dept.deptno ;
조인문장 작성시에는 컬럼명 앞에 테이블명을 접두어로 기술해야 더 검색성능이 좋아집니다.
select e.ename, e.sal, e.job, d.loc, e.deptno
from emp e, dept d
where e.deptno = d.deptno and e.job='SALESMAN';
이 문장에서 emp 는 e 로 변경되었고 dept 는 d 로 변경되었기 때문에 e.ename 이라고 작성할 수 있으면서 코딩이 심플해졌습니다.
문제5. 그렇다면 위의 조인문장을 다음과 같이 코딩하면 실행이 되겠는가?
select emp.ename, e.sal, e.job, d.loc, e.deptno
from emp e, dept d
where e.deptno = d.deptno and e.job='SALESMAN';
emp는 e로 변경되었으로 e로 작성해야됨
문제6. 이름이 king인 사원의 사원의 이름과 월급과 직업과 부서 위치를 출력하시오.
select e.sal,e.ename, e.sal, d.loc
from emp e, dept d
where e.deptno = d.deptno and e.ename ='KING';
문제7. 월급이 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;
문제 8. 이름의 첫글자가 s로 시작하는 사원들의 이름과 월급과 부서위치를 출력하시오,
select e.ename, e.sal, d.loc
from emp e, dept d
where e.deptno = d.deptno and e.ename like 'S%';
문제 9.다음의 테이블을 생성하시오 !
create table telecom_table
( telecom varchar2(10),
t_price number(10),
etc_service number(10) );
insert into telecom_table values('sk', 17000, 9 );
insert into telecom_table values('lg', 18000, 7 );
insert into telecom_table values('kt', 16000, 8 );
insert into telecom_table values('lg알뜰', 17000, 10 );
insert into telecom_table values('sk알뜰', 16000, 5 );
insert into telecom_table values('kt알뜰', 15000, 9 );
commit;
문제10. emp19와 telecom_table 을 조인해서 이름, 주소,통신사, 통신비를 출력하시오.
select e.ename, e.address, t.telecom, t.t_price
from emp19 e, telecom_table t
where lower(e.telecom) = t.telecom ;
문제 11. emp19 와 telecom_table 을 조인해서 이름과 주소, 통신사, 기본 통신비(t_price) 를 출력하시오
select e.ename, e.address, t.telecom, t.t_price
from emp19 e, telecom_table t
where lower(e.telecom) = t.telecom and lower(e.telecom)='sk' ;
문제 12. emp와 dept을 조인해서 dallas에 근무하는 사원들의 이름과 부서위치를 출력하시오
select e.ename, d.loc
from emp e, dept d
where e.deptno = d.deptno and d.loc='DALLAS';
문제 13 (복습문제) 부서번호, 부서번호별 토탈월급을 출력하시오 ( 세로출력 )
select deptno, sum(sal)
from emp
group by deptno;
문제 14. 부서위치, 부서위별 토탈월급을 출력하시오
select d.loc, sum(e.sal)
from emp e, dept d
where e.deptno = d.deptno
group by d.loc;
문제 15. 부서위치 부서위치별 인원수를 출력하시오
select d.loc, count(*)
from emp e, dept d
where e.deptno = d.deptno
group by d.loc;
문제16. 부서번호, 부서번호별 속한 사원들의 이름을 가로로 출력하시오.
select e.deptno, listagg(e.ename,',') within group (order by e.ename asc)
from emp e, dept d
where e.deptno = d.deptno
group by e.deptno;
문제 17. 부서위치, 부서위치별로 속한 사원들의 이름을 가로로 출력하시오
select d.loc, listagg(e.ename,',') within group (order by ename asc)
from emp e, dept d
where e.deptno = d.deptno
group by d.loc;
문제 18. emp와 dept를 서로 조인해서 결과를 출력하는데 sales 부서명에서 근무하는 사원들의 이름과 월급과 부서명을 출력하는데 월급이 높은 사원부터 출력하시오.
select e.ename, e.sal, d.dname
from emp e,dept d
where e.deptno=d.deptno and d.dname='SALES'
order by 2 desc;
문제 19. 위의 결과에서 월급을 출력할 때 천단위를 부여하여 출력되게하시오.
select e.ename, to_char(e.sal,'999,999') as sal, d.dname
from emp e,dept d
where e.deptno=d.deptno and d.dname='SALES'
order by 2 desc;
문제 20. sales 부서의 관리자 는 누구인지 그 이름과 월급과 부서명을 출력하시오
select e.ename, d.dname
from emp e, dept d
where e.deptno=d.deptno and e.job='MANAGER' and d.dname='SALES';
select e.ename, e.sal, d.loc, j.job_max_sal
from emp e , dept d, job_info j
where e.deptno = d.deptno and e.job = j.job_name;
※ 테이블 3개 조인 시 2개의 조인조건 2개를 줘야됨
59. NON EQUI JOIN
예제59번. 여러 테이블의 데이터를 조인해서 출력하기 2(NON EQUI JOIN)
* 조인 문법의 종류 2가지 ?
1. 오라클 조인 문법 4가지:
1.1 equi join : 조인조건이 이퀄(=)조건인 경우의 조인 예) where e.deptno =d.deptno 1.2 non equi join 1.3 outer join 1.4 self join
2. 1999 ANSI(American National Standard Institute) 문법 2.1 on 절을 사용한 조인 2.2 using 절을 사용한 조인 2.3 natural 조인 2.4 left/right/full outer 조인 2.5 cross 조인
문제 1. 급여등급 테이블(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;
문제2. emp와 salgrade 테이블을 조인해서 이름과 월급과 등급 출력하시오.
select e.ename, e.sal, s.grade
from emp e, salgrade s
where e.sal between s.losal and s.hisal ;
설명: emp 테이블의 월급은 salgrade 테이블의 losal과 hisal 사이에 있음.
문제 3. 위의 결과에서 월급의 등급이 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
;
문제 4. grade가 2등급이고 직업이 SALESMAN인 사원들의 이름과 월급과 직업과 등급(GRADE)를 출력하시오.
select e.ename, e.sal, s.grade
from emp e, salgrade s
where e.sal between s.losal and s.hisal
and s.grade= 2 And e.job ='SALESMAN'
;
문제5. 부서번호, 부서번호 별로 속한 사원들의 이름을 가로로 출력하시오.
select e.deptno, listagg(e.ename, ',') within group (order by e.ename asc)
from emp e
group by deptno;
문제6. emp 와 salgrade 를 조인해서 grade 를 출력하고 grade 별로 속한 사원들의 이름을 가로로 출력하시오 !
select listagg(e.ename, ',') within group (order by e.ename asc)
from emp e , salgrade s
where e.sal between s.losal and s.hisal
group by s.grade;
60. OUTER JOIN
예제 60 여러테이블의 데이터 조인 해서 출력하기 3 (outer join)
* 조인 문법의 종류 2가지 ?
1. 오라클 조인 문법 4가지: 1.1 equi join : 조인조건이 이퀄(=) 조건인 경우의 조인 예: where e.deptno = d.deptno 1.2 non equi join : 조인조건이 이퀄(=) 조건이 아닌 경우의 조인 1.3 outer join : equi 조인으로는 조인되지 않는 결과를 볼때 사용하는 조인문법 1.4 self join
2. 1999 ANSI(American National Standard Institute) 문법 2.1 on 절을 사용한 조인 2.2 using 절을 사용한 조인 2.3 natural 조인 2.4 left/right/full outer 조인 2.5 cross 조인
Q1. EMP와 DEPT 를 서로 조인해서 이름과 부서위치를 출력하는데 DEPT테이블에 있는 부서 위치인데 출력 안되고 있는 것이 무엇인지 알아내시오.
select e.ename, d.loc
from emp e, dept d
where e.deptno = d.deptno;
설명: LOC 쪽에 BOSTON 이 안나오는 이유는 EMP 테이블에 40번 부서번호가 없기 때문에 조인이 안되고 있어서 출력이 안되는것입니다.
Q2 .위의 결과를 다시 출력하는데 부서위치쪽에 BOSTON 도 같이 출력될 수있도록 하시오 ! equi join 으로는 조인 안되는 결과도 출력되게 하시오 !
select e.ename, d.loc
from emp e, dept d
where e.deptno (+) = d.deptno;
문제1. (EQUI 문법) EMP19와 telecom_table 을 조인해서 이름,통신사, 통신사 가격을 출력하시오.
select e.ename, lower(e.telecom) , t.t_price
from emp19 e, telecom_table t
where lower(e.telecom) = t.telecom;
문제2. (outer join 문법) 위의 결과를 다시 출력하는데 outer join 을 써서 telecom_table 에는 있는 통신사인데 emp19 에는 없는 통신사도 출력되게 하시오 !
select e.ename, lower(e.telecom) , t.t_price
from emp19 e, telecom_table t
where lower(e.telecom(+)) = t.telecom;
설명: select 절에는 t.telecom 으로 해야하고 lower 함수 괄호안에 (+) 을 써야합니다
문제3. 부서위치, 부서위치별 토탈월급을 출력하시오.
select d.loc, sum(e.sal)
from emp e, dept d
where e.deptno = d.deptno
group by d.loc;
문제 4. 위의 결과에서 조인안된 BOSTON 도 나오게 하시오 !
from emp e, dept d
where e.deptno (+) = d.deptno
group by d.loc;
문제 5. 복습) 커미션이 null 인 사원들의 이름과 월급과 커미션과 부서위치를 출력하시오. emp와 dept을 조인해
select e.ename, e.sal, e.comm , d.loc
from emp e , dept d
where e.deptno= d.deptno and e.comm is null;
문제 6. 부서번호 10, 20번인 사원들의 이름과 월급과 부서위치, 부서번호를 출력하시
select e.ename, e.sal, d.loc , e.deptno
from emp e , dept d
where e.deptno= d.deptno and e.deptno in(10,20);
문제 7. 사원번호, 사원이름, 관리자번호를 출력하시
select empno, ename, mgr
from emp
61. SELF JOIN
예제61. 여러 테이블의 데이터를 조인해서 출력하기 4(SELF JOIN) * 조인 문법의 종류 2가지 ? 1. 오라클 조인 문법 4가지: 1.1 equi join : 조인조건이 이퀄(=) 조건인 경우의 조인 1.2 non equi join : 조인조건이 이퀄(=) 조건이 아닌 경우의 조인 1.3 outer join :equi 조인으로는 조인되지 않는 결과를 볼때 사용하는 조인 1.4 self join : 스스로 조인하는 조인문법, 자기 자신의 테이블과 조인
2. 1999 ANSI(American National Standard Institute) 문법 2.1 on 절을 사용한 조인 2.2 using 절을 사용한 조인 2.3 natural 조인 2.4 left/right/full outer 조인 2.5 cross 조인
Q1. emp 와 emp 를 서로 조인해서 즉 자기 자신의 테이블과 조인해서 이름을 출력하고 자기의 직속상사 이름을 출력하시오
select 사원.ename as 사원 , 관리자.ename as 관리자
from emp 사원, emp 관리자
where 사원.mgr = 관리자.empno;
설명: 위의 조인 문법이 self join 은 자기 자신의 테이블과 스스로 join하는 조인문법입니다. emp 테이블은 사원과 관리자가 섞여있으므로 사원 테이블이라고도 명명할 수 있고 관리자 테이블이라고도 부를 수 있다. 7902라는 번호는 ford에게는 사원번호지만 smith 에게는 관리자번호임.
문제1. 사원 이름, 사원 입사일, 관리자이름, 관리자 입사일을 출력하는데 관리자 보다 먼저 입사한 사원들만 출력하시오.
select 사원.ename as 사원 ,사원.hiredate as 사원입사일,
관리자.ename as 관리자,관리자.hiredate as 관리자입사일
from emp 사원, emp 관리자
where 사원.mgr = 관리자.empno
and 사원.hiredate < 관리자.hiredate;
문제2. 사원이름, 사원의 월급, 관리자, 관리자의 월급을 출력하는데 관리자의 월급이 사원의 월급보다 더 작은 것만 출력하시오.
select 사원.ename as 사원 ,사원.sal as 사원월급,
관리자.ename as 관리자,관리자.sal as 관리자월급
from emp 사원, emp 관리자
where 사원.mgr = 관리자.empno
and 사원.sal > 관리자.sal;
문제 3. 아래의 self join 의 결과에서 사원 이름 King 과 jack이 출력되지 않으면서 전체 15개 행이 아니라 13개 행으로 출력 되고 있음 (이유: king 의 mgr이 null 이기 때문에 조인이 안되서 출력 안된거임)
select 사원.ename as 사원 , 관리자.ename as 관리자
from emp 사원, emp 관리자
where 사원.mgr = 관리자.empno;
select 사원.ename as 사원 , 관리자.ename as 관리자
from emp 사원, emp 관리자
where 사원.mgr = 관리자.empno(+);
★ 문제 4. 아래처럼 sql을 출력하시오
select 관리자.ename ||'('|| 관리자.hiredate||')' as 관리자이름,
listagg( 사원.ename ||'('|| 사원.hiredate||')' ,',' ) within group (order by 사원.ename asc)as 사원이름
from emp 사원, emp 관리자
where 사원.mgr = 관리자.empno
group by 관리자.ename ||'('|| 관리자.hiredate||')';
