[오라클] 조인함수 복습 / 데이터조작어

--1.사원들의 급여 평균

SELECT TRUNC(AVG(SAL)) FROM EMP;

--2. 20번 부서에 속하는 사원들의 급여평균

SELECT TRUNC(AVG(SAL)) FROM EMP WHERE DEPTNO=20;

--★ 2.1 DEPTNO를 FROM 절 앞에 쓰려면 뒤에 GROUP BY를 꼭 써야 한다.

SELECT DEPTNO,AVG(SAL) FROM EMP WHERE DEPTNO=20 GROUP BY DEPTNO;

--3. 20번 부서에 속한 사원들의 사번, 이름, 직무, 급여, 부서번호, 부서위치

SELECT EMPNO,ENAME,JOB,SAL,DEPTNO,LOC FROM EMP E NATURAL JOIN DEPT D WHERE DEPTNO=20;

SELECT EMPNO,ENAME,JOB, SAL,DEPTNO,LOC FROM EMP E JOIN DEPT D USING(DEPTNO)  WHERE DEPTNO=20;

SELECT EMPNO,ENAME,JOB,SAL, E.DEPTNO,LOC FROM EMP E JOIN DEPT D ON(E.DEPTNO=D.DEPTNO) WHERE E.DEPTNO=20;

--4. 20번 부서에 속한 사원들 중 전체 급여 평균보다 더 많이 받는 사원들의 사번, 이름 직무, 급여, 부서번호, 부서위치

SELECT EMPNO, ENAME, JOB, SAL, E.DEPTNO,LOC  FROM EMP E, DEPT D WHERE E.DEPTNO=D.DEPTNO AND E.DEPTNO=20  AND SAL>(SELECT AVG(SAL)FROM EMP);

SELECT EMPNO, ENAME, JOB,SAL, DEPTNO,LOC  FROM EMP E  JOIN DEPT D USING(DEPTNO) WHERE DEPTNO=20 AND SAL>(SELECT AVG(SAL)FROM EMP);

--5.부서번호 20,30번인 사원들의 정보

SELECT * FROM EMP WHERE DEPTNO IN(20,30);

--6.각 부서의 최고 급여

SELECT DEPTNO,MAX(SAL) FROM EMP GROUP BY DEPTNO;

--7.각 부서들의 최고 급여와 같은 급여를 받는 사원정보

SELECT * FROM EMP  WHERE SAL IN(SELECT MAX(SAL)FROM EMP GROUP BY DEPTNO);

--8.각 부서들의 최고 급여보다 더 많이 받는 사원들의 정보 >ANY 또는 SOME

SELECT * FROM EMP  WHERE SAL > ANY(SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO);

--9. =ANY 와 IN이 같은 의미 (7번문제=9번문제)

SELECT * FROM EMP WHERE SAL = ANY (SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO);

--10. IN =ANY =SOME 동일하다

SELECT * FROM EMP  WHERE SAL = SOME (SELECT MAX(SAL)  FROM EMP GROUP BY DEPTNO);

--11. 30번 부서에 속한 사원의 급여보다 작은 사원의 정보 : ANY 보다 작다 = 가장 큰 범위보다 아래면 다 오케이

SELECT * FROM EMP  WHERE SAL < ANY( SELECT SAL FROM EMP WHERE DEPTNO =30) ORDER BY SAL,EMPNO;

SELECT * FROM EMP  WHERE SAL < ( SELECT MAX(SAL) FROM EMP WHERE DEPTNO =30) ORDER BY SAL,EMPNO;

--12. 30번 부서에 어떤 사원의 급여보다 큰 사원들의 정보 ANY = 제일작은 값보다 크면 돼

SELECT * FROM EMP  WHERE SAL > ANY(SELECT SAL FROM EMP WHERE DEPTNO =30) ORDER BY SAL,EMPNO;

SELECT * FROM EMP  WHERE SAL > (SELECT (SAL) FROM EMP WHERE DEPTNO =30) ORDER BY SAL,EMPNO;

--13.30번 부서의 모든 사원들의 급여보다 큰 급여를 받는 사원들의 정보

SELECT * FROM EMP  WHERE SAL > ALL(SELECT SAL FROM EMP WHERE DEPTNO=30) ORDER BY SAL,EMPNO;

--14.10번 부서의 부서명이 있으면 전체 사원정보 출력

SELECT * FROM EMP WHERE EXISTS (SELECT DNAME FROM DEPT WHERE DEPTNO =10);

--15.50번부서에 부서명이 없어서 정보 없음

SELECT * FROM EMP WHERE EXISTS (SELECT DNAME FROM DEPT WHERE DEPTNO =50);

--16. 다중열 속성 비교하기: 각 부서별 최고 급여 받는 사원의 정보 출력 (실무에 많이 쓰임)

SELECT * FROM EMP WHERE(DEPTNO,SAL) IN(SELECT DEPTNO,MAX(SAL) FROM EMP GROUP BY DEPTNO);

**부속질의어(SUB QUERY)위치** --SELECT 문에 부속질의 (SELECT절에 SUB QUREY가 온다라고 이해하면 됨)  : SCALAR 부속질의 --FROM 절에  부속질의   : IN-LINE-VIEW(인라인 뷰) --WHERE 절에 부속질의   : 중첩질의

--17.10번 부서의 사원번호, 사원명, 부서번호, 부서명, 부서위치 구하되 IN-LINE VIEW 사용하기 E10번으로 부르기로 했다.

SELECT E10.EMPNO,E10.ENAME,E10.DEPTNO,D.DNAME,D.LOC  FROM (SELECT * FROM EMP WHERE DEPTNO=10)E10, (SELECT * FROM DEPT) D WHERE E10.DEPTNO=D.DEPTNO;

--17.1 WITH절 /FROM 절 길게 쓰기 싫어서 WHIT 먼저 선언

WITH E10 AS (SELECT * FROM EMP WHERE DEPTNO=10),D AS(SELECT * FROM DEPT) SELECT E10.EMPNO,E10.ENAME,E10.DEPTNO,D.DNAME,D.LOC  FROM E10,D WHERE E10.DEPTNO=D.DEPTNO;

--18.사원명,급여,급여등급,해당 등급의 최저급여,최고급여

SELECT ENAME,SAL,GRADE,LOSAL,HISAL FROM EMP,SALGRADE WHERE SAL BETWEEN LOSAL AND HISAL;

--19.스칼라 부속질의 사원번호, 사원명,직무,급여, 급여등급(SCALAR 부속질의, 별칭:SALGRADE), 부서번호, 부서명(SCALAR 부속질의, 별칭:DNAME)

SELECT EMPNO,ENAME,JOB,SAL,(SELECT GRADE FROM SALGRADE WHERE E.SAL BETWEEN LOSAL AND HISAL) AS SALGRADE, DEPTNO,(SELECT DNAME FROM DEPT D WHERE E.DEPTNO=D.DEPTNO) AS DNAME FROM EMP E;

--20.사원명이 ALLEN인 사원의 직무

SELECT JOB FROM EMP WHERE ENAME='ALLEN';

SELECT JOB FROM EMP WHERE UPPER(ENAME)=UPPER('allen');

--21.ALLEN인 사원의 직무와 같은 사원의 직무,사번,이름,급여, 부서번호,부서명 출력

SELECT JOB,EMPNO,ENAME,SAL,E.DEPTNO,DNAME  FROM EMP E,DEPT D  WHERE  E.DEPTNO=D.DEPTNO AND JOB=(SELECT JOB FROM EMP WHERE ENAME='ALLEN');

--22.급여가 전체 평균급여보다 더 많이 받는 사원들의 사번,이름,부서명, 입사일,부서위치,급여, 등급 출력하되 급여역순,사원번호순으로 출력

SELECT EMPNO,DNAME,HIREDATE,LOC,SAL,GRADE  FROM EMP E, DEPT D, SALGRADE S WHERE E.DEPTNO=D.DEPTNO AND SAL BETWEEN LOSAL AND HISAL AND  SAL > (SELECT AVG(SAL)FROM EMP) ORDER BY E.SAL DESC, EMPNO;

--23.부서번호가 10번이고 30번 부서에는 없는 직무를 하는 사원들의 사번, 이름, 부서명 출력

SELECT EMPNO,ENAME,DNAME FROM EMP E, DEPT D WHERE E.DEPTNO=D.DEPTNO AND E.DEPTNO=10 AND JOB NOT IN(SELECT DISTINCT JOB FROM EMP WHERE DEPTNO=30);

--24.직무가 SALSEMAN인 사원들의 최고 급여보다 더 받는 사원의 사번,이름,급여,등급을 출력하되 사원번호 순으로 출력   (MAX함수 사용)

SELECT EMPNO,ENAME,SAL,GRADE FROM EMP E,SALGRADE S WHERE SAL BETWEEN LOSAL AND HISAL AND SAL> (SELECT MAX(SAL) FROM EMP WHERE JOB='SALSEMAN') ORDER BY EMPNO;

--25. 24번 문제를 >ALL 를 사용하여 출력하라

SELECT EMPNO,ENAME,SAL,GRADE FROM EMP E,SALGRADE S WHERE SAL BETWEEN LOSAL AND HISAL AND SAL> ALL(SELECT SAL FROM EMP WHERE JOB='SALSEMAN') ORDER BY EMPNO;

 

※ EXISTS는 서브쿼리 조건에 만족하는 모든 레코드를 출력해준다.

※ INLINE VIEW 는 서브쿼리가 FROM 절 안에서 사용되는 경우, 서브쿼리란 SELECT문 안에 다시 SELECT 문이 기술된 형태의 쿼리다. VIEW는 저장장치 내 물리적 존재 NO, 사용자에게 있는 것처럼 보여짐

---

데이터를 추가, 수정, 삭제하는 데이터 조작어

 

▶ 테이블에 데이터를 추가하는 INSERT문

   위의 열의 갯수만큼 값의 갯수가 같아야 하며, 같은 데이터 타입이여야 함

▶ 테이블에 날짜 데이터 입력하기

▶ 서브쿼리를 사용하여 한 번에 여러 데이터 추가하기

   INSERT문에 서브쿼리 사용시 유의점

▶ UPDATE문의 기본 사용법

▶ 데이터 일부분만 수정하기

   WHERE절을 활용

▶ DELETE문의 기본형식

 

데이터조작어 실습예제)

--26. DEPT테이블과 동일한 DEPT_TEMP테이블 생성

CREATE TABLE DEPT_TEMP AS SELECT * FROM DEPT;

--27.생성된 테이블 확인

SELECT * FROM DEPT_TEMP;

--28. 50, DATABASE, SEOUL 값을 DEPT_TEMP 테이블에 삽입  // INSERT INTO 테이블명(속성명1,2,3...)VALUES(값 1,2,3...)

INSERT INTO DEPT_TEMP(DEPTNO,DNAME,LOC) VALUES (50,'DATABASE','SEOUL');

--29.DEPT_TEMP테이블 내용 확인

SELECT * FROM DEPT_TEMP;

--30.NETWORK.BUSAN 삽입

INSERT INTO DEPT_TEMP(DEPTNO,DNAME,LOC) VALUES (60,'NETWORK','BUSAN');

--31.70,WEB, NULL삽입 //NULL은 작음 따옴표 안붙임

INSERT INTO DEPT_TEMP(DEPTNO,DNAME,LOC) VALUES(70,'WEB',NULL);

--32.80,MOBILE,' '삽입

INSERT INTO DEPT_TEMP(DEPTNO,DNAME,LOC) VALUES(80,'MOBILE','');

--33. 속성 2개, 값2개를 삽입

INSERT INTO DEPT_TEMP(DEPTNO,LOC)VALUES(90,'INCHEON');

--34. DEPT_TABLE내용 확인

SELECT * FROM DEPT_TEMP;

--35.EMP 테이블과 같은 구조의 EMP_TEMP테이블 생성 (WHERE문에서 거짓인 조건을 넣기 ex. WHERE 1<>1)

CREATE TABLE EMP_TEMP AS SELECT * FROM EMP WHERE 1<>1;

--36.EMP_TEMP확인  //아무것도 안나와야 정상 (아직 값을 넣지 않음)

SELECT * FROM EMP_TEMP;