2017 - 09 - 07 (목)
기본사항 정리
1 . SQL DML
--- INSERT 연습하기
INSERT INTO EMP (id, name, salary, title, in_date, dept_name) values(31, 'heejun', 2000, 'manager' , sysdate, 'HR');
INSERT INTO EMP (id, name, salary, title, in_date, dept_name) values(42, 'heejun1', 2000, 'manager' , sysdate, 'HR');
INSERT INTO EMP (id, name, salary, title, in_date, dept_name) values(52, 'heejun2', 2000, 'manager' , sysdate, 'HR');
INSERT INTO EMP (id, name, salary, title, in_date, dept_name) values(62, 'heejun3', 2000, 'manager' , sysdate, 'HR');
INSERT INTO EMP (id, name, salary, title, in_date, dept_name) values(72, 'heejun4', 2000, 'manager' , sysdate, 'HR');
INSERT INTO EMP (id, name, salary, title, in_date, dept_name) values(10, 'heesun', 2000, 'supervisor' , sysdate, 'HR');
INSERT INTO EMP (id, name, salary, title, in_date, dept_name) values(11, 'heemin', 2000, 'assistance' , sysdate, 'HR');
--- EMP 테이블에 INSERT 쿼리로 데이터 삽입
select * from EMP;
--- EMP 테이블의 모든컬럼 검색 * (와일드카드 : 모든 컬럼)
Select id, name from emp;
update emp set title = 'assistance' where name = 'heejun3';
--- EMP 테이블에서 NAME 이 HEEJUN3 인 타이틀컬럼의 값을 ASSISTANCE로 바꾼다
select distinct title as "직급별" from emp
order by title;
--- EMP 테이블에서 TITLE의 필드명을 직급별로 바꾸고 (DISTINCT : 중복제거) 타이틀 기준으로 정렬함.
select name, salary*12, title from emp
order by title;
--- SALARY의 모든 인스턴스에 12씩 곱해줘서 검색
select * from emp
where salary >2000;
--- SALARY가 2000이상인 EMP테이블의 모든 컬럼 검색
select * from emp
where salary between 1000 and 4000
order by salary asc;
--- SALARY가 1000~ 4000사이에 있는 모든 컬럼 검색 ( SALARY기준으로 오름차순 정렬 )
select * from emp
where title in('part_timer','vice_president');
--- PART_TIMER, VICE_PRESIDENT 인 행만 검색
select * from emp
where name like 'h%';
--- 이름이 h로 시작하는 모든 행 검색
select id, salary, upper(title), in_date from emp
where salary>2000;
---upper : 대문자로 출력
select id, salary, lower(title), in_date from emp
where salary>2000;
---lower : 소문자로 출력
select id, salary, concat('직급 : ', title), in_date from emp
where salary>2000;
--- title 필드에 '직급 : '과 합쳐서 문자열로 출력
select id, salary, length(title) as "문자길이", in_date from emp
where salary>2000;
---문자 길이 출력
select id, salary, translate(title, 'vice','ace'), in_date from emp
where salary>2000;
---translate 내장함수로 vice를 ace 로 바꿈.
select id, salary, replace(title,'vice','ace'), in_date from emp
where salary>2000;
---replace : translate와 매우 유사하다.
select id, salary, title, in_date from emp
where salary>2000;
select id , round(salary,-2), title, in_date from emp
where salary>1000;
--- round : 반올림
select id , mod(salary,10) "급여/10 나머지", title, in_date from emp
where salary>1000;
--- mode : 나머지
select id , power(salary,2) as "봉급의 제곱", title, in_date from emp
where salary>1000;
--- power : 제곱
select id , sqrt(salary) as "급여의 제곱근", title, in_date from emp
where salary>1000;
--- sqrt : 제곱근 (루트)
select id , sign(salary), title, in_date from emp
where salary>1000;
select id , chr(salary) as "급여의 문자값", title, in_date from emp
where salary>1000;
SELECT DEPT_ID, AVG(SALARY) FROM STUDENT.S_EMP
WHERE TITLE = '사원'
GROUP BY DEPT_ID;
---GROUP BY
SELECT DEPT_ID AS "부서번호" , ROUND(AVG(SALARY)) AS "봉급" FROM STUDENT.S_EMP
GROUP BY DEPT_ID
order by dept_id;
--각지역별 몇개의 부서가 있는지
select region_id as "지역번호" , count(name) as "부서 갯수" from student.s_dept
group by region_id;
--각 부서별로 평균 급여를 구하되 평균 급여가 2000 이상인 부서만 나타내시오.
select DEPT_ID , ROUND(AVG(SALARY)) AS"봉급" FROM STUDENT.S_EMP
GROUP BY DEPT_ID
HAVING AVG(SALARY)>2000;
---HAVING : salary 필드에 내장함수로 값을 바꾸었기 때문에 이 값에 조건을 주기위해 사용한다.
SELECT DEPT_ID, COUNT(NAME) AS"부서별 직원수" FROM STUDENT.S_EMP
GROUP BY DEPT_ID
ORDER BY COUNT(NAME) ASC;
--- S_EMP 테이블에서 부서별 직원수를 검색 한다.
2 . SQL DML 응용 예제
--각 직책별로 급여의 총합을 구하되 직책이 부장인 사람은 제외하시오. 단, 급여총합이 8000만원 이상인 직책만 나타내며, 급여 총합에 대한 오름차순으로 정렬하시오.
SELECT TITLE, SUM(SALARY) FROM STUDENT.S_EMP
WHERE TITLE NOT LIKE '%부장%'
GROUP BY TITLE
HAVING SUM(SALARY)>8000;
--각 부서별로 직책이 사원인 직원들에 대해서만 평균 급여를 구하시오.
SELECT TITLE, AVG(SALARY) FROM STUDENT.S_EMP
WHERE TITLE='사원'
GROUP BY TITLE;
SELECT DEPT_ID, TITLE, COUNT(*) FROM STUDENT.S_EMP
GROUP BY DEPT_ID, TITLE;
--각 부서내에서 각 직책별로 몇 명의 인원이 있는지를 나타내시오.
SELECT DEPT_ID, TITLE, COUNT(NAME) FROM STUDENT.S_EMP
GROUP BY DEPT_ID, TITLE;
--각 부서내에서 몇 명의 직원이 근무하는지를 나타내시오.
SELECT DEPT_ID, COUNT(NAME) AS "부서내 직원수" FROM STUDENT.S_EMP
GROUP BY DEPT_ID;
--각 부서별로 급여의 최솟값과 최대값을 나타내시오. 단, 최소값과 최대값이 같은 부서는 출력하지 마시오.
SELECT DEPT_ID, MAX(SALARY) "최대 급여" , MIN(SALARY) AS "최소 급여" FROM STUDENT.S_EMP
GROUP BY DEPT_ID
HAVING MAX(SALARY) <> MIN(SALARY);
select dept_id, title, count(*) from s_emp
where dept_id in (106, 112 , 113)
group by rollup(dept_id,title)
order by dept_id;
select dept_id, name, salary, rank() over(partition by dept_id order by salary desc) as rank
from s_emp;
select name, salary, dept_id
from s_emp a
where salary< (select avg(salary) from s_emp where dept_id = a.dept_id);
--본인이 다른 사람의 관리자로 되어 있는 직원의 사번,이름 직책, 부서번호를 나타내시오.
select id, name, title, dept_id
from s_emp e
where exists(select id from s_emp where manager_id =e.id);
--본인의 급여가 각 부서별 평균급여 중 어느 한 부서의 평균급여보다 적은 급여를 받는 직원에 대해 이름, 급여, 부서번호를 출력하시오
select name, salary, dept_id from s_emp
where salary < any(select avg(salary)
from s_emp
group by dept_id);
--마지막 세자리가 데이터가 저장된 순서임
select id, name, dept_id, rowid from s_emp;
--직원 테이블에서 이름을 사전순으로 정렬하여 5개의 데이터만 나타내시오
select name, rownum from (select * from s_emp order by name)
where rownum <=5
order by name asc;
3 . SQL JOIN
JOIN 은 하나 이상의 테이블을 연결하여 데이터를 검색하는 방법이다. primary key(기본키) 와
<b style = color:red;>foreign key(참조키)</b> 로 연결된다.
JOIN의 종류
EquiJoin : 컬럼 간의 값들이 서로 정확히 일치하는 경우에 사용한다. PK와 FK로 연결
Non-EquiJoin : 한 칼럼의 값이 다른 칼럼의 값과 정확히 일치하지 않는 경우 사용
Outer Join : Join 조건을 만족하지 않는 경우에도 모든 행들을 다보려 하는 경우
Self Join : 같은 테이블에 있는 행들을 JOIN 학도자 하는 경우에 사용.
아래 예제를 살펴보자.
SELECT S_EMP.NAME, S_EMP.DEPT_ID, S_DEPT.NAME FROM STUDENT.S_EMP, STUDENT.S_DEPT
WHERE S_EMP.DEPT_ID = S_DEPT.ID;
--직원 테이블과 부서 테이블을 조인하여 사원의 이름과 부서, 부서명을 나타내시오
SELECT S_EMP.NAME, S_EMP.DEPT_ID, S_DEPT.NAME FROM STUDENT.S_EMP, STUDENT.S_DEPT
WHERE S_EMP.DEPT_ID = S_DEPT.ID;
--서울 지역에 근무하는 사원에 대해 각 사원의 이름과 근무하는 부서명을 나타내시오.
SELECT S_EMP.NAME 사원명 , S_DEPT.NAME 부서명 , S_REGION.NAME FROM STUDENT.S_EMP, STUDENT.S_DEPT, STUDENT.S_REGION
WHERE S_EMP.DEPT_ID = S_DEPT.ID AND S_DEPT.REGION_ID = S_REGION.ID AND S_REGION.NAME LIKE '%서울%';
--위에문제 다른방식
SELECT E.NAME, D.NAME, R.NAME
FROM STUDENT.S_EMP E, STUDENT.S_DEPT D, STUDENT.S_REGION R
WHERE E.DEPT_ID = D.ID AND D.REGION_ID = R.ID AND R.NAME LIKE '%서울%';
SELECT E.NAME, E.SALARY , G.GRADE 급여등급 FROM STUDENT.S_EMP E, STUDENT.SALGRADE G
WHERE E.SALARY BETWEEN G.LOSAL AND G.HISAL;
SELECT G.GRADE 급여등급, COUNT(E.NAME) 숫자 FROM STUDENT.S_EMP E, STUDENT.SALGRADE G
WHERE E.SALARY BETWEEN G.LOSAL AND G.HISAL
GROUP BY G.GRADE
ORDER BY 1;
--직원 테이블과 급여 테이블을 조인하여 사원의 이름과 급여, 그리고 해당 급여등급을 나타내시오
SELECT E.NAME 사원명, E.SALARY 급여, G.GRADE 급여등급 FROM STUDENT.S_EMP E, STUDENT.SALGRADE G
WHERE E.TITLE = '사원';
--OUTER JOIN 의 예
SELECT E.NAME 사원명, E.ID, C.NAME 고객명 FROM STUDENT.S_EMP E, STUDENT.S_CUSTOMER C
WHERE E.ID(+) = C.SALES_REP_ID
ORDER BY 2;
--직원 테이블과 고객 테이블에서 사원의 이름과 사번, 그리고 각 사원의 담당고객 이름을 나타내시오, 단, 고객에 대하여 담당영업사원이 없더라도 모든 고객의 이름을 나타내고, 사번 순으로 오름차순 정렬하시오
SELECT E.NAME, E.ID, C.SALES_REP_ID FROM STUDENT.S_EMP E, STUDENT.S_CUSTOMER C
WHERE E.ID (+) = C.SALES_REP_ID
ORDER BY 2;
--SELF JOIN 예제
SELECT W.ID 사번, W.NAME 사원명, M.ID 부서장사번, M.NAME 부서장명 FROM STUDENT.S_EMP W, STUDENT.S_EMP M
WHERE W.MANAGER_ID = M.ID;
--직원중에 김정미와 같은 직책을 가지는 사원의 이름과 직책, 급여, 부서번호를 나타내시오
SELECT W.NAME 사원이름, W.TITLE 직책, W.SALARY, W.DEPT_ID , M.TITLE 김정미직책 FROM STUDENT.S_EMP W, STUDENT.S_EMP M
WHERE W.TITLE = M.TITLE AND M.NAME = '김정미' ;
--직원중에 김정미와 같은 부서
SELECT NAME, TITLE, DEPT_ID FROM STUDENT.S_EMP
WHERE DEPT_ID = (SELECT DEPT_ID FROM STUDENT.S_EMP WHERE NAME = '김정미');
--지역번호가 3인 부서의 사원 MULTI ROW SUBQUERY
SELECT NAME, DEPT_ID FROM STUDENT.S_EMP
WHERE DEPT_ID IN(SELECT ID FROM STUDENT.S_DEPT WHERE REGION_ID = 3);
--MULTI COLUMN SUBQUERY 부서에서 최저 연봉을 받는 사람만
SELECT NAME, DEPT_ID, SALARY FROM STUDENT.S_EMP
WHERE (SALARY, DEPT_ID) IN (SELECT MIN(SALARY), DEPT_ID FROM STUDENT.S_EMP
GROUP BY DEPT_ID);
SELECT DEPT_ID, AVG(SALARY) FROM STUDENT.S_EMP
GROUP BY DEPT_ID
HAVING AVG(SALARY)>(SELECT AVG(SALARY) FROM STUDENT.S_EMP WHERE DEPT_ID = 113);
--가장 적은 평균급여를 받는 직책에 대해 그 직책과 평균급여를 나타내시오
SELECT TITLE ,AVG(SALARY) 평균급여 FROM STUDENT.S_EMP
GROUP BY TITLE
HAVING AVG(SALARY) = (SELECT MIN(AVG(SALARY)) FROM STUDENT.S_EMP GROUP BY TITLE);
--HAVING AVG(SALARY)=MIN(SELECT TITLE, AVG(SALARY) FROM STUDENT.S_EMP);
INSERT INTO EMP_113(ID , NAME, MAILID, START_DATE) SELECT ID, NAME, MAILID, START_DATE FROM STUDENT.S_EMP WHERE START_DATE< '16/01/01';