[4/100] 240415

from절에 table명 두개이상 = join

alias 컬럼에는 as인데 생략하고 띄어쓰기로 해도 되는걸로 알고있음

테이블에는 그냥 띄어쓰기로(as생략)

기준은 결국 더 많은 것

ANSI 라우터 문법

 

 

er다이어그램 공부할것

가능한 데이터를 추려야 한다.

가능한 데이터를 추리는게 중요하기 때문에 가져온다음 가공하는 having보다는 where을 이용한다.

실행 순서를 참고한다.

 

select * from emp;

select empno, ename, job, mgr, hiredate, sal, comm, deptno from emp;

CREATE INDEX idx_name_last_char ON emp (SUBSTR(ename, -1));

SELECT * FROM emp WHERE SUBSTR(ename, -1) = 'N';

--alias 정렬
select sal * 12 from emp order by sal*12 asc;
select sal * 12, sal from emp order by sal asc;
select sal * 12 asal, sal from emp order by asal asc; --alias에서 as는 생략가능

//조인
select e1.*, e2.* from emp e1, emp e2;
//나올 수 있는 모든 경우의 수 출력 = 카티션 프러덕 14 * 4 = 56
select e.empno, d.dname from emp e, dept d;
select e.ename, d.deptno from emp e, dept d where e.deptno = d.deptno;

select e.ename, d.deptno from emp e, dept d where e.deptno (+) /*빈칸이라도 보여달라는 뜻 */ = d.deptno;

--ANSI : (left/right)outer join // 기준에 따라 사용
select e.ename, d.deptno
from emp e right outer join dept d on e.deptno = d.deptno
where e.job = 'SALESMAN';

// selfjoin
// 사원번호 사원명 사수번호 사수이름
SELECT e.empno, e.ename, m.empno mgrnum, m.ename mgrname
FROM emp e, emp m
where m.empno = e.mgr
;
//사수번호 자체가 곧 사원번호이면서 조건이 들어가야하는 번호기 때문에

//ANSI문법 on 사용
select e.empno, e.ename, m.empno mno, m.ename mname
from emp e join emp m on e.mgr = m.empno
;

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

--그룹

select sum(sal) Ssal
from emp;

select distinct deptno from emp;

select deptno from emp
group by deptno;

/*
--그룹함수 group function--
sum(컬럼) - 총합
avg(컬럼) - 평균
max(컬럼) - 최대
min(컬럼) - 최소
count(컬럼), count(*), count(1) - 갯수
*/

--일반컬럼, 그룹함수를 같이 사용할 경우 반드시 group by에 일반컬럼 표기
select deptno, job, sum(sal) ssal
from emp
group by deptno, job
order by deptno desc
;

--10번 직업 사람들의 평균 최대 최소 총합

select avg(sal) avgsal , max(sal) maxsal, min(sal) minsal, sum(sal) sumsal
from emp
where deptno = 10
;
--round(반올림할 컬럼,반올림할자릿수)
select deptno, round(avg(sal),2) avgsal , max(sal) maxsal, min(sal) minsal, sum(sal) sumsal
from emp
--where deptno = 10
group by deptno
order by deptno asc
;

--평균 급여가 2000이상인 부서만 출력
select deptno, round(avg(sal),2) avgsal , max(sal) maxsal, min(sal) minsal, sum(sal) sumsal
from emp
where avg(sal) >= 2000
group by deptno
order by deptno asc
;
--평균 급여가 2000이상인 부서만 출력
--그룹펑션은 where절에 사용불가능
--조건을 넣고싶으면 having사용한다
select deptno, round(avg(sal),2) avgsal , max(sal) maxsal, min(sal) minsal, sum(sal) sumsal
from emp
group by deptno
having avg(sal) >= 2000
order by deptno asc
;


--count
select count(comm), count(1), count(*) from emp;
select rowid, rownum, empno, ename from emp;

select count(comm), count(1) from emp;
select * from emp where comm is null;

--nvl 널밸류(컬럼,null대체값)
select comm, nvl(comm,0)*12 ssal,nvl(comm,999999) from emp;

--직업별 평균급여 단 직업은 clerk, salesman인 경우만 반영
select job, avg(sal)
from emp
group by job
having job in ('CLERK','SALESMAN')
;

select job, avg(sal)
from emp
where job in ('CLERK','SALESMAN')
group by job
;

--각 부서별 평균 급여 중 최대평균급여 출력
select max(avg(sal)) avsal
from emp
group by deptno;

select deptno, avg(sal)
from emp
group by deptno
having avg(sal) in(select max(avg(sal)) from emp group by deptno);

--1.DALLAS에서 근무하는 사원의 이름, 직업, 부서번호, 부서이름을 출력하라.
select e.ename, e.job, d.deptno, d.dname
from emp e, dept d
where e.deptno = d.deptno and loc = 'DALLAS';

--2. 직업이 'SALESMAN'인 사원들의 직업과 그 사원이름, 부서 이름을 출력하라.
select e.job 직업, e.ename 사원명, d.dname 부서이름
from emp e, dept d
where e.deptno = d.deptno and job = 'SALESMAN';

/*
3.부서번호가 10번, 20번인 사원들의 부서번호, 부서이름, 사원이름, 급여를
출력하라. 그리고 그 출력된 결과물을 부서번호가 낮은 순으로, 급여가 높은 순으로
정렬하라.
*/

select d.deptno, d.dname, e.ename, e.sal
from emp e, dept d
where e.deptno = d.deptno and (d.deptno = 10 or d.deptno = 20)
--where 줄이기 where e.deptno = d.deptno and d.deptno in(10,20)
order by d.deptno asc , e.sal desc
;

--4. EMP 테이블의 모든 사원번호, 사원명, 매니저번호, 매니저명을 출력하라.
select e.empno 사원번호, e.ename 사원명, f.empno 매니저번호, f.ename 매니저명
from emp e, emp f
where f.empno = e.mgr
;

--5. EMP 테이블에서 부서 인원이 4명보다 많은 부서의 부서번호, 인원수, 급여의 합
select deptno, count(deptno), sum(sal)
from emp
group by deptno
having count(deptno) >4
;