BLOG ARTICLE DB/기초교육(DB) | 9 ARTICLE FOUND

  1. 2006.06.16 6월16일 수업내용
  2. 2006.06.15 6월15일 수업내용
  3. 2006.06.14 6월14일 수업내용
  4. 2006.06.13 6월13일 수업내용
  5. 2006.06.12 6월12일 수업내용
  6. 2006.06.12 SQL문 연습002
  7. 2006.06.08 6월8일 수업내용
  8. 2006.06.08 SQL문 연습001
  9. 2006.06.07 6월7일 수업내용

6월16일 수업내용

DB/기초교육(DB) 2006. 6. 16. 17:33
select * from emp group by job having (select avg(sal) from emp group by job);

select job, avg(sal) as asal from emp e, (select job, avg(sal) as asal from emp group by job) a where e.asal
select e.job, e.asal from (select job, avg(sal) as asal from emp group by job) e, (select min(avg(sal)) a_sal from emp group by job) a where e.asal=a.a_sal;

select job, avg(sal) from emp group by job having avg(sal)=(select min(avg(sal)) from emp group by job);
//직업별 평균급여중 최소값

select ename, job, dname, loc from (select ename, job, deptno, empno from emp where job='MANAGER') e join dept d on e.deptno=d.deptno;
//직업이 MANAGER인 사람의 이름 직업 부서명 지역명

select ename, e.job, sal from (select job, min(sal) msal from emp group by job) m, emp e where e.job=m.job and sal=msal;

select ename, e.job, sal from (select job, min(sal) msal from emp group by job) m join emp e on e.job=m.job and sal=msal;
//업무별 최소급여를 받는 사람

select ename, sal from emp e where sal>(select avg(sal) from emp where e.deptno=deptno)
//효율이 떨어짐.. 10000x100

select ename, hiredate from emp where deptno=(select deptno from emp where ename='BLAKE');

select ename, job, sal from emp where deptno=(select deptno from dept where loc='DALLAS');
//달라스에 근무하는 사람들

select ename, job, sal from emp e join dept d on e.deptno=d.deptno and loc='DALLAS';
//구지 조인할필요가 없음
//효율이 떨어짐

select ename, job, sal, mgr, empno from emp
where mgr in (select ename, job, sal, mgr, empno from emp
where mgr in (select empno from emp where ename='KING'));

select * from emp where (job, sal) IN(select job, sal from emp where ename='FORD') and ename !='FORD';
//포드와 월급과 직업이 같은 사람을 출력
//본인 제외
select ename, job, sal from emp where job=(select job from emp where ename='JONES') or sal>=(select sal from emp where ename='FORD') order by job, sal desc;
//존과 직업이 같거나 포드보다 월급이 많은사람

select ename, job, sal from emp where sal in (select sal from emp where ename in ('SCOTT','WARD')) and ename not in ('SCOTT','WARD');
//스캇과 워드와 같은 월급 받는 사람

select ename, job, deptno from emp where job in (select job from emp natural join dept where loc='CHICAGO');
//하나로 처리
//주의!!

select * from emp where mgr=(select mgr from emp where ename='BLAKE') and ename!='BLAKE';
//BLAKE와 같은 상사를 가진 사람

select * from emp e where 5>(select count(*) from emp where sal>e.sal) order by sal desc;
//급여가 많은 순서로 5명 출력


select rownum, empno, ename, sal from emp;
//rownum 숨겨진 컬럼명
//
select rownum, empno, ename, sal from emp order by sal;

select rownum, empno, ename, sal from emp where rownum<=5 order by sal desc;

//rownum은 PK를 따라다님

select * from(select rownum, empno, ename, sal from emp order by sal desc) where rownum<=5;
//따라서 월급에 따라 정렬한 뷰를 만들어서 rownum을 호출하면 정렬된 순서대로 가져올 수 있다.!!!!!!!



**PLSQL**


ed
RUN(/)

>ed a
select * from emp; //<--작정 a라는 SQL 문

>/ //이전에 실행했던 SQL문 실행
>@a //a라는 SQL문 실행

>ed b
select * from emp; //<--작성 b라는 SQL 문

>/ //이전에 실행했던 SQL문 실행
>@b //b라는 SQL문 실행

>ed //버퍼영역 재실행
select ename from emp; //버퍼영역수정
>/
>@b //b는 수정되지 않은 dept가 실행

>ed b //<--기본확장자 SQL
select ename, sal from emp; //b를 수정
>@b //b를 실행

>get a //a를 버퍼로 불러옴
>/ //버퍼에 있는 a내용 실행
//위의 두개는 @a

>select empno, ename, sal, sal*12 from emp;
>save c:\c //버퍼의 내용을 c:\c.sql로 저장

>ed w
set linesize 120
set pagesize 50
/
>@W /저장해서 쓰면 편함
//



>select empno, ename
2 from emp
3 where deptno=10;
>L(ist)
>3
>L

>C/10/20 //바꾸기 10을 20으로(deptno)
>L
>1
>L
>A ,sal,job //추가 ,sal,job을 추가
>L

>del 3 //3행을 없앰
>L
>3 where sal>2000
4 //3수정후 엔터치면 4로..
>L

>2
>A join dept //추가 join dept
>L

>2
>i //input
>3i on emp.deptno=dept.deptno //세번째줄로 추가됨(2다음)
AND

6월15일 수업내용

DB/기초교육(DB) 2006. 6. 15. 18:24
select RPAD(RPAD(ename||' '||sal,15,' '),(sal/100)+15,'*') from emp;

select ename, job, dname, loc from emp e join dept d on e.deptno=d.deptno and ename like '%L%';

select ename, job, dname, loc from emp e natural join dept d where ename like '%L%';

select e.ename, sal, s.grade from emp e join salgrade s on sal between losal and hisal;



s1 (union all) s2 = a+b+b+c //중복해서 표시
s1 union s2 = a+b+c
s1 minus s2 = a
s1 intersect s2 = b

select ename, sal job from emp where ename like '%S%' //A
select ename sal, null(개수맞추기) from emp where ename like '%S%' //B

A minus B

20번 부서 이름 급여 부서 업무
2000이상 이름 급여 부서 업무


select e.empno, e.ename, e.mgr, m.ename from emp e join emp m on e.mgr=m.empno;

select e.empno, e.ename, e.mgr, m.ename from emp e left outer join emp m on e.mgr=m.empno;

select e.empno, e.ename, e.mgr, m.ename from emp e right outer join emp m on e.mgr=m.empno;

select e.empno, e.ename, e.mgr, m.ename from emp e full outer join emp m on e.mgr=m.empno;

select e.empno, e.ename, d.dname, d.loc, sal, grade from emp e join dept d on e.deptno=d.deptno join salgrade on sal between losal and hisal;
//세개 테이블 조인!!!!

select e.empno, e.ename, e.mgr, m.ename, d.dname from emp e full outer join emp m on e.mgr=m.empno join dept d on e.deptno=d.deptno;

select m.empno, m.ename, e.empno, e.ename from emp m join emp e on m.empno=e.mgr join dept d on d.deptno;
//
select e.empno, e.ename, m.empno, m.ename, d.dname from emp e full outer join emp m on e.mgr=m.empno join dept d on m.deptno=d.deptno;
//조건기술이 중요!!!!!
emp e right outer join emp m on e.mgr=m.empno left outer join dept d on e.deptno=d.deptno;

from emp e join dept d on e. deptno= d.deptno right outer join emp m on e. mgr=m.empno;
//조인 순서..가 다름!!!!!



select empno, ename, sal, job from emp where sal>3000;
select sal from emp where ename='SCOTT'; //3000
//합치면
select empno, ename, sal, job from emp where sal>(select sal from emp where ename='SCOTT');
//서브쿼리

select a.empno, a.ename, a.sal, a.job, b.ename, b.sal from emp a join emp b on a.sal > b.sal and lower(b.ename)=lower('scott');
//급여가 스캇의 급여보다 많이 받는 사번, 이름, 급여, 업무

select a.empno, a.ename, a.sal, a.job from emp a join emp b on a.job = b.job and lower(b.ename)=lower('scott');
select empno, ename, sal, job from emp where job=(select job from emp where ename='SCOTT');
//스캇의 업무와 같은 사람표시

select ename, job, sal from emp where job=(select job from emp where ename='SMITH') or sal>(select sal from emp where empno=7499);

select a.empno, a.ename, a.sal, a.job from emp a join emp b on (a.job = b.job and lower(b.ename)=lower('SMITH')) or (a.sal>b.sal and b.empno=7499);
//스미스와 업무가 같거나 7499의 급여보다 많이 받는 사람





select count(*), count(comm), avg(comm), count(distinct deptno) from emp;


select count(*), avg(sal), min(sal), max(sal), sum(sal) from emp group by deptno order by sum(sal) desc;

select deptno, avg(sal), max(sal), min(sal) from emp where sal>=2900 group by deptno;


select empno, ename, sal, job, deptno from emp where deptno=(select distinct deptno from emp where job='CLERK');
//두줄이상이 리턴되어 에러
select empno, ename, sal, job, deptno from emp where deptno in (select distinct deptno from emp where job='CLERK');
//IN을 사용해서 해결

단일행 연산자 비교시 >,=,<
복수행 연산자 비교시 in, any, all, exist


select empno, ename, sal, dname from emp natural join dept where sal>(select min(sal) from emp where deptno=10);
//10번 부서의 최소급여보다 많이 받는 사번 이름 급여 부서

//any (or)
//all (and) 연산자포함
//효율적이지 못함

select empno, ename, sal, dname from emp natural join dept where sal//10번 부서의 최대급여보다 적게 받는 사번 이름 급여 부서

select empno, ename, sal, dname from emp natural join dept where sal//10번 부서의 최소급여보다 적게 받는 사번 이름 급여 부서

select empno, ename, sal, dname from emp natural join dept where sal>any(select sal from emp where deptno=10);
//보다 효율적!!!!

select ename, job from emp where empno=any(select mgr from emp);
//관리자만
select ename, job from emp where empno=any(select distinct mgr from emp);
select ename, job, mgr from emp where empno not in(select distinct NVL(mgr,empno) from emp);
//말단사원만
//mgr중 NULL이 들어있으므로 not in 연산시 true와 NULL의 AND연산시 NULL이 되므로 값이 반환되지 않는다.
//표 참조
//NVL함수를 사용해서 실수값을 할당해야만 한다.!!!!
//
select ename, job, mgr from emp where empno e exists (select mgr from emp where e. empno=mgr);
//비효율적임

SELECT ename, sal, job, deptno from emp where job=(select job from emp where ename='SMITH') and deptno=(select deptno from emp where ename='SMITH');
//SMITH 와 급여가 같으면서 부서가 동일 사람

SELECT ename, sal, job, deptno from emp where job||deptno=(select job||deptno from emp where ename='SMITH');
//꽁수
select ename, job, deptno from emp where (job, deptno) in (select job, deptno from emp where ename='SMITH');

select empno, ename, deptno, sal, comm from emp where (sal,NVL(comm,-1)) in (select sal, NVL(comm,-1) from emp where deptno=30);
//()로 묶는것이 포인트.. N:N비교


01
select empno, ename, job, hiredate, sal from emp where job=(select job from emp where empno=7521) and sal>(select sal from emp where empno=7934);
02
select empno, ename, job, sal, deptno from emp where sal<(select avg(sal) from emp);
03
select deptno, min(sal) from emp group by deptno having min(sal)>(select min(sal) from emp where deptno=20);
04
select e.job, e.asal from (select job, avg(sal) as asal from emp group by job) e, (select min(avg(sal)) a_sal from emp group by job) a where e.asal=a.a_sal;

select job, avg(sal) from emp group by job having avg(sal)=(select min(avg(sal)) from emp group by job);
05
select empno, ename, job, hiredate, sal, deptno from emp where sal in (select min(sal) from emp group by job);
06
select empno, ename, job, hiredate, sal, deptno from emp where sal > any (select sal from emp where deptno=30) and deptno != 30;
//***
07
select empno, ename, job, hiredate, sal deptno from emp where sal>all(select sal from emp where deptno=30) and deptno!=30;
//***
08
select empno, ename, job, hiredate, sal, deptno from emp where empno in (select NVL(mgr,0) from emp);
select empno, ename, job, hiredate, sal, deptno from emp e where exists (select * from emp where e.empno=mgr);
//****

09
select ename, deptno, sal, comm from emp where (sal,comm) in (select sal, comm from emp where deptno=30);
//***
10
select empno, ename, job, sal, deptno from emp where sal in (select min(sal) from emp group by job) order by job;
11
select ename, deptno, sal, comm from emp where (sal, NVL(comm,0)) in (select sal, NVL(comm,0) from emp where deptno=30);
12
select empno, ename, job, sal, deptno from emp where (job, sal) in (select job, min(sal) from emp group by job ) order by job;
13
select e.ename, e.job, d.dname, d.loc from emp e, dept d where e.deptno=d.deptno and e.job='MANAGER';
14
select ename, job, dname, loc from (select deptno, ename, job from emp where job='MANAGER') e , dept d where e.deptno=d.deptno;

select ename, job, dname, loc from (select ename, job, deptno from emp where job='MANAGER') e join dept d on e.deptno=d.deptno;

01
select ename, hiredate from emp where deptno=(select deptno from emp where ename='BLAKE');
02
select empno, ename from emp where sal>(select avg(sal) from emp) order by sal desc;
03
select empno, ename, sal, deptno from emp where deptno in (select deptno from emp where ename like '%T%');
04
select ename, job, sal from emp where deptno=(select deptno from dept where lower(loc)=lower('dallas'));
05
select ename, sal, mgr from emp where mgr=(select empno from emp where ename='KING');
06
select ename, job from emp where deptno=(select deptno from dept where dname='SALES');
07
select ename, deptno, sal from emp where sal>(select min(sal) from emp where deptno=30);
08
select * from emp where deptno=10 and job in (select job from emp where deptno=30);
09
select * from emp where (job, sal) in (select job, sal from emp where ename='FORD');
10
select * from emp where job=(select job from emp where ename='JONES') or sal>=(select sal from emp where ename='FORD') order by job, sal desc;
11
select ename, job, sal from emp where sal in (select sal from emp where ename in ('SCOTT','WARD'));
12
select ename, job from emp where job in (select job from emp where deptno=(select deptno from dept where loc='CHICAGO'));
13. EMP 테이블에서 부서별로 월급이 평균 월급보다 높은 사원을 부서번, 이름, 급여를 출력하는 SELECT 문을 작성하시오.
select e.deptno, e.ename, e.sal, e.job, a.a_sal "jobsalavg" from emp e, (select deptno, avg(sal) as a_sal from emp group by deptno) a where a.deptno=e.deptno and e.sal>a.a_sal;

14. EMP 테이블에서 업무별로 월급이 평균월급보다 낮은 사원을 부서번호, 이름, 급여를 출력하는 SELECT 문을 작성하시오.
select e.deptno, e.ename, e.sal, e.job, a.a_sal "jobsalavg" from emp e, (select job, avg(sal) as a_sal from emp group by job) a where a.job=e.job and e.sal>a.a_sal;
//핵심은 알리아스!!!!
15
select job, ename, empno, deptno from emp where empno in (select distinct NVL(mgr,0) from emp);

16
select empno, ename, job, deptno from emp where empno not in (select distinct NVL(mgr,0) from emp);
AND

6월14일 수업내용

DB/기초교육(DB) 2006. 6. 14. 18:33
문제 14 해설
select
job,
sum(decode(deptno,10,sal,20,0,30,0)) "DEPT 10",
sum(decode(deptno,10,0,20,sal,30,0)) "DEPT 20",
sum(decode(deptno,10,0,20,0,30,sal)) "DEPT 30",
sum(sal) "TOTAL"
from emp group by job;

SELECT
select count(*), count(decode(to_char(hiredate,'yy'),'80',job)) "1980", count(decode(to_char(hiredate,'yy'),'81',job)) "1981", count(decode(to_char(hiredate,'yy'),'82',job)) "1982", count(decode(to_char(hiredate,'yy'),'87',job)) "1987" from emp;


equi-join 1:1 대응
outer-join 조인되지 않은 데이터도 내보냄
left-outer-join 좌측을 다 내보냄
right-outer-join 우측을 다 내보냄
non-equi-join 조인범위에 맞춰 데이터를 내보냄
//78 = 70-80
self-join


select * from emp a, dept b;
56행
select * from emp a, dept b where a.deptno=b.deptno;
14행
select ename, dname, emp.deptno from emp, dept where emp.deptno=dept.deptno;
//조인
select ename, dname, e.deptno from emp e, dept d where e.deptno=d.deptno;
//알리아스를 이용(떠올리기 쉽게)
select empno, ename, job, sal, dname, loc from emp e, dept d where e.deptno=d.deptno;
//사번 이름 업무 급여 부서명 위치

select empno, ename, job, sal, dname, loc from emp e, dept d where e.deptno=d.deptno;
//사번 이름 업무 급여 부서명 위치

select empno, ename, job, sal, d.deptno, dname, loc from emp e, dept d where e.deptno(+)=d.deptno;
//(+)의 반대쪽 테이블이 다나옴 /left-outer-join
select empno, ename, job, sal, d.deptno, dname, loc from emp e, dept d where e.deptno=d.deptno(+);
//(+)의 반대쪽 테이블이 다나옴/right-outer-join

select ename, sal, grade from emp e, salgrade s where sal between losal and hisal;
//non-equi-join

select e.job||' '||e.ename "시다바리", m.job||' '||m.ename "왕초", e.sal, m.sal, m.sal-e.sal "급여차" from emp e, emp m where e.mgr=m.empno(+);

select b.job||' '||b.ename, b.sal from emp a, emp b where b.sal

01
select empno, ename, job, deptno, dname, loc from emp natural join dept;
02
select e.empno, e.ename, e.job, e.deptno, d.deptno, d.dname, d.loc from emp e join dept d on e.deptno=d.deptno;
03
select empno, ename, sal, dname, loc from emp join dept on emp.deptno=dept.deptno and lower(job)=lower('SALESMAN');
04
05
select empno, ename, job, sal, grade, losal, hisal from emp e join salgrade s on sal between losal and hisal;
06
select empno, ename, job, e.deptno, d.deptno, dname, loc from emp e right outer join dept d on e.deptno= d.deptno;
07
select worker.ename||'의 관리자는 '||manager.ename from emp worker join emp manager on worker.mgr=manager.empno;


연습문제
1.
select ename, deptno, dname from emp natual join dept;
2
select ename, job, sal, dname from emp natural join dept where upper(loc)=upper('new york');
select ename, job, sal, dname from emp e join dept d on e.deptno=d.deptno and upper(loc)=upper('new york');
3
select ename, comm, dname, loc from emp e join dept d on e.deptno=d.deptno and comm is not NULL;
4
select ename, job, dname, loc from emp natural join dept where ename like '%L%';
select ename, job, dname, loc from emp e join dept d on e.deptno=d.deptno and ename like '%L%';
5
select e.ename, e.empno, m.ename, m. empno from emp e left outer join emp m on e.mgr=m.empno;
6
select e.ename, e.hiredate, m.ename, m.hiredate from emp e join emp m on e.mgr=m.empno and e.hiredate < m.hiredate;
7
select RPAD(RPAD(ename,10,' '),10+sal/100,'*') "Employee and their salary" from emp;
AND

6월13일 수업내용

DB/기초교육(DB) 2006. 6. 13. 18:27

desc dept
dname varchar2(14) //<-- char로 전환
alter table dept modify (dname char(14));

select concat(dname, deptno) from dept;

alter table dept modify (dname varchar2(14));

varchar2 //가변형
ex) 민번, 우편번호
char //고정형
ex) 급여, 수당, 설명


select count(*), count(empno), count(comm) from emp;

select count(comm), sum(comm), avg(comm), count(*), avg(NVL(comm,0)) from emp;

select min(ename), max(ename) from emp;

select sum(sal), avg(sal), stddev(sal), variance(sal) from emp;

select deptno, max(sal), min(sal) from emp group by deptno;

select job, avg(sal, max(sal), min(sal) from emp group by job;


select stddev(100*sal/(select max(sal) from emp)) from emp;

select deptno, job, sum(sal), count(*) from emp group by deptno, job;
//그룹함수를 사용한 select문에 나오는 컬럼은 group by의 조건문에 나와야 한다.

select deptno, avg(sal), sum(sal) from emp group by deptno having avg(sal)>=2500;
//급여평균 2500 이상 부서번호, 급여평균, 합계

select job, count(*) from emp group by job having count(*)>=4;
//업무인원이 4명이상인 업무와 쪽수

select job, max(sal), min(sal) from emp where deptno in (10, 20) group by  job having max(sal)>=2000 order by max(sal);
//부서가 10 20 이고 최대 급여가 2000 이상인 업무별 최대 최소 급여
//그룹함수조건이 아닌경우 where절에 기술!!!!!

select 'smith중 i는 '||instr('smith','i')||'번째에 있습니다.' from dual;

select deptno, ename, sal, decode(deptno,10,sal*1.1,20,sal*1.2,30,sal) "Sal Up" from emp;

select deptno, ename, job, case when sal>=2500 then 1200 when sal>=1500 then 1000 else 1500 end Bouns from emp;
//급여 1500이상 1000 , 2500 이상 1200 , 그외 1500
//주의 순서 바꾸지 말것
select ename, case when sal>=2500 then 1200 when sal>=1500 then 1000 else 1500 end Bouns1, case when sal>=1500 then 1000 when sal>=2500 then 1200 else 1500 end Bouns2 from emp;

select ename, case when sal<1000 then '거지' when sal<2000 then '서민' when sal<3000 then '중산층' else '갑부' end from emp;




select
case when sal<1000 then '거지'
when a.sal<2000 then '서민'
when a.sal<3000 then '중산층'
else '갑부' end living
from emp;




select ename, LEVEL, empno, mgr from emp START WITH mgr is NULL CONNECT BY PRIOR empno=mgr;
//LEVEL을 이용해 조직도를 나타냄


select deptno, to_char(hiredate,'yy'), avg(sal) from emp group by deptno, to_char(hiredate,'yy') not having to_char(hiredate,'yy')='82';

select avg(sal), max(sal), min(sal) from emp group by job having lower(job)=lower('salesman');

select count(empno) "C_INWON", count(comm) "C_COMM", avg(comm) "A_COMM", 3 "N_COMM", count(deptno) "C_DEPT", 3 "C_DIS" from emp;
//N????

select deptno, avg(sal), min(sal), max(sal), sum(sal) from emp group by deptno;

select deptno, count(empno), avg(sal), min(sal), max(sal), sum(sal) from emp group by deptno order by sum(sal) desc;

select count(empno), min(sal), max(sal), sum(sal) from emp group by deptno order by sum(sal) desc;

select deptno, job, count(empno), avg(sal), sum(sal) from emp group by deptno, job;

select deptno, job, count(empno), avg(sal), sum(sal) from emp group by job, deptno;

select deptno, count(empno), sum(sal) from emp group by deptno having count(empno)>4;

select deptno, avg(sal), sum(sal) from emp group by deptno having max(sal)>2900;

select job, avg(sal), sum(sal) from emp group by job having avg(sal)>=3000;

select job, sum(sal) "PAYROLL" from emp where not job='SALESMAN' group by job having sum(sal)>5000 order by sum(sal) desc;

select max(avg(sal)), max(sum(sal)), min(min(sal)), max(max(sal)) from emp group by deptno;


1. EMP 테이블에서 모든 SALESMAN에 대하여 급여의  평균, 최고액, 최저액, 합계를 구하여 출력하라.
select avg(sal), max(sal), min(sal), sum(sal) from emp where job='SALESMAN';
2. EMP 테이블의 이름의 오름차순 정렬상 첫번째에 위치하는 이름, 마지막에 위치하는 이름을 구하고, 입사일의 가장 오래된 사람, 가장 최근에 입사한 사람을 구하고 최대 급여와 최소 급여를 구하여라.
select min(ename), max(ename), min(hiredate), max(hiredate), max(sal), min(sal) from emp;
3. EMP 테이블에 등록되어 있는 인원수, 보너스에 NULL이 아닌 인원수, 보너스의 평균, 등록 되어있는 부서의 수를 구하여 출력하라.
select sum(count(*)), sum(count(comm)), sum(avg(comm)), count(count(deptno)) from emp group by deptno;
4. 부서별로 인원수, 평균급여, 최저급여, 최고급여를 구하여라.
select count(*), avg(sal), min(sal), max(sal) from emp group by deptno;
5. 4번 문제를 급여의 합이 많은 순서로 출력하라.
select count(*), avg(sal), min(sal), max(sal) from emp group by deptno order by sum(sal);
6. 부서별로 그룹지어 부서번호, 인원수, 급여의 평균, 급여의 편차, 급여의 분산을 구하여라.
select deptno, count(*), avg(sal), stddev(sal), variance(sal) from emp group by deptno;
7. 업무별, 부서별로 그룹하여 결과를 부서번호, 업무, 인원수, 급여의 평균, 급여의 편차, 급여의 합을 출력하라.
select deptno, job, count(*), avg(sal), stddev(sal), sum(sal) from emp group by deptno,job;
8. EMP테이블에서 부서인원이 4명 이상인 부서의 부서번호, 인원수, 급여의 합을 출력하라.
select deptno, count(*), sum(sal) from emp group by deptno having count(*)>=4 ;
9. EMP 테이블에서 업무가 PRESIDENT가 아닌 업무의 부서별 급여의 합계와 평균을 구하라.
select deptno, sum(sal), avg(sal) from emp where job!='PRESIDENT' group by deptno;
10. EMP 테이블에서 급여가 2900 이상인 부서에 대하여 부서번호, 평균급여, 최대급여, 최소급여를 구하여라.
select deptno, avg(sal), max(sal), min(sal) from emp group by deptno having avg(sal)>=2900;
11. EMP 테이블에서 전체 급여가 5000을 초과하는 각 업무에 대해 업무와 급여 합계를 출력하라. 단, SALESMAN은 제외하고 급여 합계를 내림차순으로 정렬하라.
select job, sum(sal) from emp where lower(job)!=lower('SALESMAN') group by job having sum(sal)>5000;
12. 부서별 평균 중 최대 평균 급여, 부서별 급여의 합 중 최대 급여, 부서별 급여의 최소 급여,  부서별 급여의 최대 급여를 출력하라.
select max(avg(sal)), max(sum(sal)), min(min(sal)), max(max(sal)) from emp group by deptno;
13.다음의 결과를 작성하는 SELECT문을 작성하라.
select to_char(hiredate,'YY') as "H_YEAR", count(*), min(sal), max(sal), avg(sal), sum(sal) from emp group by to_char(hiredate,'YY');
14.아래의 급여의 합계를 출력하는 SELECT 문장을 작성하라.
select job, decode(deptno,10,sum(sal),20,NULL,30,NULL) "DEPTNO 10", decode(deptno,10,NULL,20,sum(sal),30,NULL) "DEPTNO 20", decode(deptno,10,NULL,20,NULL,30,sum(sal)) "DEPTNO 30", sum(sal) "TOTAL" from emp group by job, deptno;
//분리 되어 출력
select job, decode(deptno,10,sum(sal),20,NULL,30,NULL) "DEPTNO 10", decode(deptno,10,NULL,20,sum(sal),30,NULL) "DEPTNO 20", decode(deptno,10,NULL,20,NULL,30,sum(sal)) "DEPTNO 30", sum(sal) "TOTAL" from emp group by job;
//출력불가
select job, sum(sal*(deptno-20)*(deptno-30)/200) "DEPTNO 10", sum(sal*(deptno-10)*(deptno-30)/(-100)) "DEPTNO 20", sum(sal*(deptno-10)*(deptno-20)/200) "DEPTNO 30", sum(sal) "TOTAL" from emp group by job;
//먼가 부족
select job, sum(decode(deptno,10,sal,20,0,30,0)) "DEPT 10", sum(decode(deptno,10,0,20,sal,30,0)) "DEPT 20", sum(decode(deptno,10,0,20,0,30,sal)) "DEPT 30", sum(sal) "TOTAL"  from emp group by  job;
//좀더 쉬운 풀이**

15. 아래의 인원수의 합계를 출력하는 SELECT 문장을 작성하라.
select to_char(hiredate,'YYYY'), count(*) "TOTAL" from emp group by rollup(to_char(hiredate,'YYYY'));
//이건 원하시는 답이 아닐듯
select count(*) "TOTAL",
count(NULLIF(to_char(hiredate,'YYYY'), '1981')*NULLIF(to_char(hiredate,'YYYY'), '1982')*NULLIF(to_char(hiredate,'YYYY'), '1987')*1) "1980",
count(NULLIF(to_char(hiredate,'YYYY'), '1980')*NULLIF(to_char(hiredate,'YYYY'), '1982')*NULLIF(to_char(hiredate,'YYYY'), '1987')*1) "1981",
count(NULLIF(to_char(hiredate,'YYYY'), '1980')*NULLIF(to_char(hiredate,'YYYY'), '1981')*NULLIF(to_char(hiredate,'YYYY'), '1987')*1) "1982",
count(NULLIF(to_char(hiredate,'YYYY'), '1980')*NULLIF(to_char(hiredate,'YYYY'), '1981')*NULLIF(to_char(hiredate,'YYYY'), '1982')*1) "1987" from emp;
//돌려 풀기
select sum(count(*)) "TOTAL", sum(decode(to_char(hiredate,'YYYY'),1980,count(*),1981,NULL,1982,NULL,1987,NULL)) "1980",
sum(decode(to_char(hiredate,'YYYY'),1980,NULL,1981,count(*),1982,NULL,1987,NULL)) "1981",
sum(decode(to_char(hiredate,'YYYY'),1980,NULL,1981,NULL,1982,count(*),1987,NULL)) "1982",
sum(decode(to_char(hiredate,'YYYY'),1980,NULL,1981,NULL,1982,NULL,1987,count(*))) "1987"
from emp group by to_char(hiredate,'YYYY');
//억지완성
select count(*), count(decode(to_char(hiredate,'yy'),'80',job)) "1980", count(decode(to_char(hiredate,'yy'),'81',job)) "1981", count(decode(to_char(hiredate,'yy'),'82',job)) "1982", count(decode(to_char(hiredate,'yy'),'87',job)) "1987" from emp;
//가시나답변**

AND

6월12일 수업내용

DB/기초교육(DB) 2006. 6. 12. 19:43

input -> 함수 -> output

단일행함수
문자함수
숫자함수
날짜함수
변환함수
기타

복수행함수(그룹함수)


select lower(ename), upper(ename), initcap(ename) from emp;

SELECT
lower(ename), //소문자화
upper(ename), //대문자화
initcap(ename) //첫자만 대문자
FROM emp;


SELECT empno, ename, sal FROM emp WHERE lower(ename)='scott';

SELECT empno, ename, sal
FROM emp
WHERE lower(ename)='scott';

SELECT empno, ename, sal
FROM emp
WHERE ename=upper('scott');

SELECT empno, ename, sal
FROM emp
WHERE initcap(ename)=initcap('scott');

//양항을 함수를 써서 일치 시키는것이 좋다.


SELECT ename||mgr from emp;

SELECT concat(ename,mgr) from emp;
//단 두개만 가능
SELECT concat('dear ',ename) from emp;

SELECT to_number(concat('10',sal))+1 from emp;
//10을 문자화 하여 월급앞에 붙인후 숫자로 변환후 1을 더함

SELECT * from emp where length(ename)>5;
//이름의 길이가 5자 이상인 사람만

SELECT ename, instr(ename,'A') from emp;

SELECT ename, instr(ename,'L'4,1) from emp;
// 4<- 찾는 시작위치
// 1<- 찾는문자 반복순서
// instr(ename,'A') 은 instr(ename,'A',1,1) 과 동일

select LPAD(sal,10,'*') from emp;
select RPAD(sal,10,'*') from emp;
// 10 칸중 sal이 차지하는 칸을 제외한 나머지를 *으로 채워라

select LPAD(sal,6,' ') from emp;

select LTRIM('MILLER','MI') from dual;
//MI만 삭제되어 나옴
select LTRIM('MILLER','IL') from dual;
//효과없음
select RTRIM(sal,'0') from emp;

select trim(leading 'M' from ename) from emp;

select trim(trailing 'N' from ename) from emp;

select trim(both 'S' from ename) from emp;

SELECT
trasrate(sal,'1234567890','일이삼사오육칠팔구영'),
replace(job, 'SALE', 'ORDER')
FROM emp;
//tras 는 1:1
//repl 는 All:All

select CHR(65)||CHR(97) from dual;
//Aa
select job||CHR(13)||ename from ename;
//13 <-엔터


select round(456.789,0), round(456.789,2), round(456.789,-2) from dual;
//457 456.79 500
//반올림

select trunc(456.789,0), trunc(456.789,2), trunc(456.789,-2) from dual;
//456 456.78 400
//버림

NVL(expr1,expr2)
//expr1이 NULL이 아니면 expr1, NULL이면 expr2
NVL2(expr1,expr2,expr3)
//expr1이 NULL이 아니면 expr2, NULL이면 expr3을 출력
nullit(expr1,expr2)
//expr1=expr2이면 NULL을 출력, expr1<>expr2 이면 expr1

select NVL2(comm,sal+comm,sal) from emp;

select comm+sal as "plus", NVL(comm,0)+sal as "NVL", NVL2(comm,sal+comm,sal) as "NVL2" from emp;
800 800
1900 1900 1900
NULL과 덧셈은 NULL

select CHR(ASCII(A)) from dual;
//???항목명 길이..


select sysdate as "Current Date" from dual;

select add_months(sysdate, 10) from dual;

select sysdate, hiredate, months_between(sysdate, hiredate) from emp;

select next_day(sysdate,'월요일'), (last_day(sysdate)-sysdate) from dual;
//돌아오는 월요일
//30(날짜)-12(날짜)=18

select (sysdate-(7*3)) "3주전", (sysdate-(7*2)) "2주전", (sysdate+(7*3)) "3주후" from dual;

//7 일주일의 날수 * 주수 연산..


select hiredate, round(hiredate, 'Month'), round(hiredate,'Year'), trunc(hiredate, 'Month'), trunc(hiredate,'Year') from emp;
//7월인경우 year에 round를 쓰면 1년 PLUS
//7월인경우 year에 trunc를 쓰면 연도는 그대로

//1983-12-25 round month 적용하면 1984-01-01로 나옴

select TO_CHAR(sysdate, 'yyyy"년" mm"월" dd"일"') from dual;
// ''안에 "" 사용하는것에 주의
TO_CHAR //숫자or날짜 -> 문자
TO_NUMBER //문자형숫자 -> 숫자
TO_DATE //문자형식 날짜 -> 날짜

select to_char(sal, '$999,999.00') from emp;
//$999,999.00형식에 맞춰서 sal을 넣어서 표현하라
//9만 사용가능

select to_char(sal*12,'9,999') from emp;
//형식을 넘을때(overflow) #으로 표시됨

select to_char(sal*12,'L9,999') from emp;
//L은 지역통화표시

select TO_CHAR(hiredate,'fmdd month yyyy') from emp;
//fm 을 쓰면 앞에 0이 나오지 않도록 함 01-> 1


TO_DATE('03-02-01', 'mm-dd-yy')
//문자를 날짜형식에 맞춰서 내보냄

select (TO_DATE('03-02-01', 'YY-MM-dd'))-(TO_DATE('03-02-01', 'mm-dd-yy')) from dual;




ex01:
select sysdate as "Current Date" from dual;

select ename, length(ename), job from emp where length(ename)>=6;

select ename, job, sal, comm, sal+comm from emp;

문제01) EMP 테이블에서 SCOTT 의 정보를 사원번호, 성명, 담당업무(소문자로), 부서번호를 출력하여라.
select empno, ename, lower(job), deptno from emp where lower(ename)=lower('scott');

문제02) EMP 테이블에서 SCOTT  의 정보를 사원번호, 성명, 담당업무, 부서번호를 출력하여라.
select empno, ename, job, deptno from emp where upper(ename)=upper('scott');

문제03) DEPT 테이블에서 첫 글자만 대문자로 변환하여 모든 정보를 출력하여라.
select initcap(*) from dept;//불가
select initcap(dname), initcap(loc) from dept;

문제04) 두개의 SELECT 문이 있다. 결과의 차이점을 설명하여라.
select empno, ename, job, concat(empno,ename) e_name, concat(ename,empno) e_empno, concat(ename,job) e_job from emp where deptno='10';

select deptno no,danem,loc, concat(ddptno,dname) d_name, concat(dname,deptno) d_deptno, concat(dname,loc) d_loc from dept;

문제05) EMP 테이블에서 이름의 첫글자가 'K' 보다 크고 'Y' 보다 작은 사원의 정보를 사원번호, 이름, 업무, 급여, 부서번호를 출력하여라. 단 이름순으로 정렬하여라.
select * from emp where ASCII(UPPER(substr(ename,1,1)))>ASCII('K') and ASCII(UPPER(substr(ename,1,1)))<ASCII('Y') ORDER BY ename;

문제06) EMP 테이블에서 20번 부서 중 이름의 길이 및 급여의 자릿수를 사원번호, 이름, 이름의 자릿수, 급여, 급여의 자릿수를 출력하여라.
SELECT empno, ename, Length(ename) "L_ENAME", sal, Length(sal) "L_SAL" from emp where deptno='20';

문제07) EMP 테이블에서 이름 중 첫번째 'L'자의 위치와 두번째 'L'자의 위치를 출력하여라.
SELECT ename, instr(ename,'L',1,1) "E_L1", instr(ename,'L',1,2) "E_L2" from emp;

문제08) 문제없음

문제09) 아래의 결과를 출력하여라.
SELECT ename, LPAD(ename,15,'*') "L_ENAME", sal, LPAD(sal,10,'*') "L_SAL" from emp;

문제10) 아래의 결과를 출력하여라.
SELECT ename, RPAD(ename,15,'*') "R_ENAME", sal, RPAD(sal,10,'*') "R_SAL" from emp;

문제11) EMP 테이블에서 10번 부서에 대하여 담당 업무 중 좌측에 'A'를 삭제하고 급여 중 좌측의 1을 삭제하여 출력하여라.
select ename, job, CASE WHEN deptno='10' THEN LTRIM(job,'A') ELSE job END "LT_A", sal, LTRIM(sal,'1') "LT_1" from emp;

문제12) EMP 테이블에서 10번 부서에 대하여 담당 업무중 우측에서 'T'를 삭제하고 급여 중 우측의 0 을 삭제하여 출력하여라.
select ename, job, RTRIM(job,'K') "R_T", sal, RTRIM(sal,'0') "RT_0" from emp where deptno='10';

문제13) EMP 테이블에서 성명을 소문자로 바꾸어 출력하여라.
select empno, ename, lower(ename) "U-LOWER", sal, translate(sal,'1234567890','일이삼사오육칠팔구영') "N_H" from emp;

문제14) EMP 테이블에서 JOB 에 'A'를 '$'로 바꾸어 출력하여라.
select ename, job, replace(job,'A','$')"replace_J", sal from emp;

문제15) 다음의 결과를 분석하여라.
select round(4567.678) "ROUND_1", round(4567.678,0) "ROUND_2", round(4567.678,2) "ROUND_3", round(4567.678,-2) "ROUND_4" from dual;

문제16) 다음의 결과를 분석하여라.
select trunc(4567.678) "TRUNC_1", trunc(4567.678,0) "TRUNC_2", trunc(4567.678,2) "TRUNC_3", trunc(4567.678,-2) "TRUNC_4" from dual;

문제17) EMP 테이블에서 급여를 30으로 나눈 나머지를 구하여 출력하여라.
select sal, mod(sal,30) from emp;

문제18) EMP 테이블에서 20번 부서 중 이름과 담당업무를 연결하여 출력하여라. 단 담당업무를 한 줄 아래로 출력하여라.
select empno, ename, job, ename||CHR(10)||job "ENAME_JOB" from emp where deptno='20';

문제19) EMP 테이블에서 현재까지 근무일 수가 몇주 몇일 인가를 출력하여라. 단 근무일 수가 많은 사람 순으로 출력하여라.
select ename, hiredate, sysdate, (sysdate-hiredate+1) "Total Days", trunc((sysdate-hiredate+1)/7) "WEEKS", TRUNC(MOD((sysdate-hiredate+1),7)) "DAYS" from emp ORDER BY hiredate;

문제20) EMP 테이블에서 10번 부서 중 현재까지의 근무 월수를 계산하여 출력하여라.
select ename, hiredate, sysdate, months_between(sysdate,hiredate) "M_BETWEEN", trunc(months_between(sysdate,hiredate)) "T_BETWEEN" from emp;

문제21) EMP 테이블에서 10번 부서 중 입사 일자로부터 5개월이 지난 후 날짜를 계산하여 출력하여라.
select ename, hiredate, add_months(hiredate,5) "A_MONTH" from emp where deptno='10';

문제22) EMP 테이블에서 10번 부서 중 입사 일자로부터 돌아오는 금요일을 계산하여 출력하여라.
select ename, hiredate, NEXT_DAY(hiredate,'금요일') "N_DAY", NEXT_DAY(hiredate,'금요일') "N_6", NEXT_DAY(hiredate,'토요일') "N_7" from emp where deptno='10';
//??

문제23) EMP 테이블에서 입사한 달의 근무 일수를 계산하여 출력하여라. 단 토요일과 일요일도 근무 일수에 포함한다.
select empno, ename, hiredate, last_day(hiredate) "L_LAST", (last_day(hiredate)-hiredate+1)  "L_DAY" from emp;
//첫날도 근무일수에 산입해야하므로 +1을 해줌

문제24) EMP 테이블에서 10번 부서 중 입사한 달의  ROUND과 TRUNC 함수를 비교합니다.
select ename, hiredate, round(hiredate,'month') "M_ROUND", trunc(hiredate,'month') "M_TRUNC", round(hiredate,'year') "Y_ROUND", trunc(hiredate,'year') "Y_TRUNC" from emp where deptno='10';

문제25) EMP 테이블에서 10번 부서 중 입사 일자를 '1 MAY 1981'와 1998 년 1 월 1 일'의 형태로 출력하여라.
select ename, hiredate, initcap(to_char(hiredate,'dd month YYYY')) "T_HIREDATE", to_char(hiredate,'YYYY" 년 " MM" 월 " DD" 일"') "T_KOR" from emp where deptno='10';

문제26) EMP 테이블에서 부서 20 중 급여 앞에 $를 삽입하고 3 자리마다 ,를 출력하여라.
select empno, ename, job, sal, to_char(sal,'$999,999') from emp where deptno='20';

문제27) February 22, 1981 에 입사한 사원의 정보를 이름, 업무, 입사일자를 출력하여라.
select ename, job, initcap(to_char(hiredate, 'month dd, YYYY')) "T_HIRE" from emp where hiredate=to_date('February 22 1981', 'month dd YYYY');

문제28) EMP 테이블에서 JOB이 ANALYST 이면 급여 증가는 10%이고 JOB 이 CLERK 이면 급여 증가는 15%이고, JOB 이 MANAGER 이면 급여 증가는 20%입니다. 다른 업무에 대해서는 급여의 증가가 없습니다. 사원번호, 이름, 업무. 급여, 증가된 급여를 출력하여라.
select empno, ename, job, sal, CASE when job ='ANALYST' then (sal*1.1) when job ='CLERK' then (sal*1.15) when job ='MANAGER' then (sal*1.2) ELSE sal END "D_SAL" from emp;

문제29) 다음의 결과를 출력하여라.
select a.deptno, b.dname, RPAD(RPAD(b.dname,15,' '),21,'*') "T_RPAD", RPAD(b.dname,21,'*') "R_R", b.loc from emp a, dept b where a.deptno=b.deptno ;

AND

SQL문 연습002

DB/기초교육(DB) 2006. 6. 12. 12:35
SELECT c.loc||' '||c.dname||'  '||a.job||' '||a.ename NAME, a.sal Salary,
TO_CHAR(a.hiredate,'rrrr'), //연월일중 연도만 추출
b.job||' '||b.ename as Manager  
FROM emp a, emp b, dept c
WHERE NVL(a.mgr,a.empno)=b.empno //사장은 상사가 없으므로 자기자신으로 대체
and a.deptno=c.deptno order by a.sal;

SELECT *
FROM emp
WHERE lower(ename) like '%a%';  //이름을 소문자로 변환후 a가 들어간것을 검색
AND

6월8일 수업내용

DB/기초교육(DB) 2006. 6. 8. 23:26

select ename, deptno, sal*12 ansal  from emp where ename=(uppper(scott)) order by sal;

deptno=10 or sal>=2000 or deptno < 30 or


alter session set nls_date_format='DD-MON-YY';
alter session set nls_date_format='DD-MON-RR';
--날짜표시변경

RR형식
2006
50-99 이전세기
00-49 현재세기
1998
50-99 현재세기
00-49 다음세기

YY형식
현재세기

ex1:
select empno, ename, job, sal from emp where sal>=3000;
ex2:
select empno, ename, job, sal, deptno from emp where job='MANAGER';
ex3:
select empno, ename, job, sal, hiredate, deptno from emp where hiredate>'01-jan-82';
ex4:
select ename, job, sal, deptno from emp where sal>1250 and sal<=1500;
ex5:
select empno, ename, job, sal, hiredate from emp where empno=7902 or empno=7788 or empno=7566;
select empno, ename, job, sal, hiredate from emp where empno IN (7902, 7788, 7566);
ex6:
select empno, ename, job, sal, hiredate, deptno from emp where hiredate like '82%';
select empno, ename, job, sal, hiredate, deptno from emp where TO_CHAR(hiredate,'rr')='82';
ex7:
select empno, ename, job, sal, comm, deptno from emp where comm is Null;
ex8:
select empno, ename, job, sal, hiredate, deptno from emp where sal>=1100 and job='MANAGER';
ex9:
select empno, ename, job, sal, hiredate, deptno from emp where sal>=1100 or job='MANAGER';
ex10:
select empno, ename, job, sal, deptno from emp where NOT job IN('MANAGER','CLERK','ANALYST');
ex11:
select empno, ename, job, sal from emp where sal>=1500 or job='MANAGER';
ex12:
select empno, ename, job, sal from emp where job='PRESIDENT' or (sal>=1500 and job='SALESMAN');

select empno, ename, job, sal from emp where ename like 'S%'; 처음에 S가 나옴
ename like '%S'; 끝에 S가 나옴
ename like '%S%'; S가 나옴
ename like '_S%'; 두번째에 S가 나옴

select empno, ename, job, sal, hiredate, deptno from emp where substr(hiredate,1,2)=82;

select empno, ename, job, sal, hiredate, deptno from emp where hiredate between '82-01-01' and '82-12-31';
A 가 B보다 작아야함
날짜 형식 RR-MM-DD

ex13:
select hiredate, empno, ename, job, sal, deptno from emp order by hiredate;
ex14:
select hiredate, empno, ename, job, sal, deptno from emp order by hiredate DESC;
ex15:
select deptno, sal, empno, ename, job from emp order by sal DESC;
ex16:
select deptno, job, sal, empno, hiredate from emp order by deptno, job, sal;

연습문제

1. WHERE 절에 HIREDATE 의 비교를 '23-JAN-82' 가 아닌 '23-jan-82'로 기술하면 결과는?

SQL> SELECT * FROM emp WHERE hiredate = '23-JAN-82';

    EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
     7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

SQL> SELECT * FROM emp WHERE hiredate = '23-jan-82';

    EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
     7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

차이없음


2.EMP 테이블에서 급여가 3000 이상인 사원의 정보를 사원번호, 이름, 담당업무, 급여를 출력하는 SELECT 문장을 작성하시오.
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
---------- ---------- --------- ----------
     7788 SCOTT      ANALYST         3000
     7839 KING       PRESIDENT       5000
     7902 FORD       ANALYST         3000


3.EMP 테이블에서 사원번호가 7788인 사원의 이름과 부서번호를 출력하는 SELECT 문장을 작성하시오.

SQL> select ename, deptno from emp where empno=7788;

ENAME          DEPTNO
---------- ----------
SCOTT              20


4.EMP 테이블에서 입사일이 February 20, 1981 과 May 1, 1981 사이에 입사한 사원의 이름 업무 입사일을 출력하는 SELECT문장을 작성하시오, 단 입사일 순으로 출력하시오.

SQL> select ename, job, hiredate from emp where hiredate between '20-Feb-81' and '01-May-81' order by hiredate;

ENAME      JOB       HIREDATE
---------- --------- ---------
ALLEN      SALESMAN  20-FEB-81
WARD       SALESMAN  22-FEB-81
JONES      MANAGER   02-APR-81
BLAKE      MANAGER   01-MAY-81


5.EMP 테이블에서 부서번호가 10, 20 인 사원의 모든 정보를 출력하는 SELECT 문장을 작성하시오. 단 이름순으로 정렬하여라.

SQL> select * from emp where deptno IN(10,20) order by ename;

    EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
     7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
     7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
     7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
     7566 JONES      MANAGER         7839 02-APR-81       2975                    20
     7839 KING       PRESIDENT            17-NOV-81       5000                    10
     7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
     7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
     7369 SMITH      CLERK           7902 17-DEC-80        800                    20

8 개의 행이 선택되었습니다.


6.EMP 테이블에서 급여가 1500 이상이고 부서번호가 10, 30 인 사원의 이름과 급여를 출력하는 SELECT 문장을 작성하여라. 단 HEADING을 Emplyee과 Monthly Salary 로 출력하여라.

SQL> select ename as Employee, sal as "Monthly Salary" from emp where sal >= 1500 and deptno IN(10,30);

EMPLOYEE   Monthly Salary
---------- --------------
ALLEN                1600
BLAKE                2850
CLARK                2450
KING                 5000
TURNER               1500


7.EMP 테이블에서 1982년에 입사한 사원의 모든 정보를 출력하는 SELECT 문을 작성하여라.

SQL> select * from emp where hiredate like '%82';

    EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
     7934 MILLER     CLERK           7782 23-JAN-82       1300                    10


8.EMP 테이블에서 COMM 에  NULL 이 아닌 사원의 모든 정보를 출력하는 SELECT 문을 작성하여라.
select * from emp where comm IS NOT NULL;

SQL> select * from emp where comm IS NOT NULL;

    EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
     7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
     7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
     7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
     7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30


9.EMP 테이블에서 보너스가 급여보다 10% 많은 모든 종업원에 대하여 이름, 급여, 보너스를 출력하는 SELECT문을 작성하여라.

SQL> select ename, sal, comm from emp where comm<sal*1.1;

ENAME             SAL       COMM
---------- ---------- ----------
MARTIN           1250       1400


10.EMP 테이블에서 업무가 CLERK 이거나 Analyst 이고 급여가 1000, 3000, 5000 이 아닌 모든 사원의 정보를 출력하는 SELECT문을 작성하여라.

SQL> select * from emp where job IN('CLERK','ANALYST') and sal NOT IN (1000,3000,5000);

    EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
     7369 SMITH      CLERK           7902 17-DEC-80        800                    20
     7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
     7900 JAMES      CLERK           7698 03-DEC-81        950                    30
     7934 MILLER     CLERK           7782 23-JAN-82       1300                    10


11.EMP 테이블에서 이름에 L 이 두 자가 있고 부서가 30 이거나 또는 관리자가 7782 인 모든 사원 정보를 출력하는 SELECT 문을 작성하여라.

SQL> select * from emp where (ename like '%L%L%' and deptno=30) or mgr=7782;

    EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
     7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
     7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

AND

SQL문 연습001

DB/기초교육(DB) 2006. 6. 8. 11:00

select distinct
a.ename||' '||a.job||' '||d.grade as 사원,
b.ename||' '||b.job as 관리자,
c.loc
from emp a, emp b, dept c, salgrade d
where b.empno=NVL(a.mgr,a.empno) and a.deptno=c.deptno and (d.losal<a.sal and a.sal<d.hisal);

d.grade를 산출하는게 포인트..

잘못된 예)
select
a.ename||' '||a.job||' '||
(select distinct d.grade from emp a, salgrade d where d.losal<a.sal and a.sal<d.hisal)
as 사원,
b.ename||''||b.job as 관리자,
c.loc
from emp a, emp b, dept c, salgrade d
where b.empno=NVL(a.mgr,a.empno) and a.deptno=c.deptno
order by a.SAL ASC;

부속질의에서 답이 여러개 반환됨


select ename from emp where sal=(select max(sal) from emp);

select ename from emp where sal=max(select sal from emp);

AND

6월7일 수업내용

DB/기초교육(DB) 2006. 6. 7. 16:35

select 컬럼명
from 테이블명;

select *
from tab;  <-- 모든 테이블

set linesize 100 <-가로길이 압축
set pagesize 50  <-페이지를 늘려줌(항목이 다시 나오는것을 막아줌)

select *
from emp;

empno ename job mgr hiredate sal comm deptno


select *
from dept;

deptno dname loc

select empno, ename from emp;

desc emp <- ; 없음 , 항목보기

select empno, ename, sal, job from emp;

select ename, sal, sal+500 from emp;

select (256+34)*6-54/9 from emp; <- 답이 14번 나옴

select (256+34)*6-54/9 from dual; <- 답이 1번 나옴, 가상 테이블 답

select (256+34)*6-54/9 결과 from dual; <- 결과=alias(별칭)

select empno 사원번호, ename 사원명 from emp;

select empno 사원 번호, ename 사원명 from emp;
ora-00923: from 키워드가 있어야할 곳에 없습니다.

select empno "사원 번호", ename 사원명 from emp; <- 공백이나 따옴표를 제외한 특수문자사용시

** 대개의 경우 ' 를 사용, " 는 2가지 경우만..

select empno as 사원번호, ename as 사원명 from emp;

ex4:
select empno, ename, sal, comm, sal+comm/100 from emp;

ex5:
select ename, sal, comm, sal*12+NVL(comm,0) from emp;

** NVL(a1, a2)  <- 대상 컬럼이 Null인경우 a2값을 반환
a1: 대상컬럼명
a2: 변환목표값

ex6:
select ename as NAME, sal as SALARY from emp;

ex7:
select ename as NAME, sal*12 as "Annual Salary" from emp;

ex8:
select ename as "성 명", sal as "급 여" from emp;


select * from emp where sal>1300 and job=SALESMAN;   <- 에러
ora-00904:열명이 부적합합니다

select * from emp where sal>1300 and job='SALESMAN';

select 'Dear ' ename from emp;
Dear

select 'Dear '||ename from emp;
Dear smith

select 'Dear ', ename from emp;  <- 별개의 컬럼
Dear smith


** 로그저장법
Spool c:\aa.txt
spool off

ex9:
select ename, job from emp;

select ename||' '||job as Employees from emp;

ex10:
select ename||' is a'||job as "Employees Details" from emp;

ex11:
select ename||': 1 Year Salary ='||sal*12 as Monthly from emp;

ex12:
select job from emp;

ex13:
select distinct job from emp;
select distinct job, ename from emp;

ex14:
select deptno, job from emp;
select distinct deptno, job from emp;


연습문제
01:
참
02:
참
03:
select empno, ename, sal*12 "연 봉" from emp;
04:
desc emp
05:
select distinct deptno from emp;
06:
select ename||' '||job from emp;
07:
select dname||' '||loc from dept;
08:
select job||' '||sal from emp;
09:


ed <-sql plus 명령어
select *
from emp
/  <-에디트 실행

sql buffer 편집기
Alt + F4


select empno, ename, sal, job from emp order by sal desc;
select empno, ename, sal*12 ansal, job from emp order by ansal desc;

select ename. sal, deptno from emp order by deptno, sal desc;
select ename. sal, deptno from emp order by deptno desc, sal;

AND

티스토리툴바