국비지원 JAVA(자바) 프로그래밍 (데이터베이스, 오라클,SQL,SQLD,)

단일행 서브쿼리

 

다중행 서브쿼리 

-> 서브쿼리 결과값이 여러 행이 나오는 것 

 

 EMP 테이블에서 직책별 급여가 가장 큰 사람들의 레코드를 조회하기

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

IN (일치여부 만 확인가능) 

WHERE~IN (OR 같은거) 하나라도 만족하면 TRUE (일치여부 만 확인가능) 

 

ANY (부등호, 이상, 이하, 미만, 초과 등을 적용가능)

WHERE ~ >(부등호) ANY(서브쿼리 형식)

 

ANY예제->직책이 SALEMAN인 급여보다 큰사람들을 조회

SELECT *
FROM EMP
WHERE SAL> ANY (SELECT SAL FROM EMP WHERE JOB='SALESMAN');

 

부서번호가 30번인 사람들의 급여보다 작은급여를 받는 사람들을 조회

정렬은 급여와 사원번호를 기준으로 오름차순 정렬

SELECT *
FROM EMP
WHERE SAL < ANY(SELECT SAL FROM EMP WHERE DEPTNO=30);

 

 

ALL 모두 만족하는 경우만 TRUE

SELECT *
FROM EMP
WHERE SAL < ALL(SELECT SAL FROM EMP WHERE DEPTNO=30);

 

 

EXISTS(서브쿼리 결과가 참인지 거짓인지 판별 후 출력)

서브쿼리의 결과값이 하나라도 있으면 TRUE

서브쿼리의 결과값이 하나도 없으면 FALSE

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

---

위에것들은 열이 1개만 나왔었음 

다중열 서브쿼리 

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

DEPTNO,SAL 과 서브쿼리 DEPTNO, SAL 의 데이터타입이 일치해야함.

(컬럼명은 달라도 상관없음) 

 

 

 FROM 뒤에 (서브쿼리 쓰는경우= 서브쿼리 자체가 하나의 표) 

부서번호가 10인 데이터들 중에서 조회 시작

SELECT * FROM (SELECT *FROM EMP WHERE DEPTNO=10)
WHERE SAL>=2000;

SELECT * 
FROM (SELECT * FROM EMP WHERE DEPTNO=10),
        (SELECT* FROM DEPT)
        WHERE SAL>=2000;

(SELECT * FROM EMP WHERE DEPTNO=10),
        (SELECT* FROM DEPT)  두 테이블의 연봉을 따로따로 계산하는게 아니라 두 테이블을 합친후 조건을 걸기 떄문에

하나의 표 SAL이 없더라도 비교가 가능함 (그렇게 적합한 것은 아님) 

 

 

SELECT * 
FROM (SELECT * FROM EMP WHERE DEPTNO=10) E,
        (SELECT* FROM DEPT)D
        WHERE SAL>=2000 AND E.DEPTNO = D.DEPTNO;

간단하게 설정하기 

 

ROWNUM (행번호 출력 예약어) 

SELECT ROWNUM ENMAE, SAL
FROM (SELECT * FROM EMP ORDER BY SAL DESC);

SELECT ROWNUM ENMAE, SAL
FROM (SELECT * FROM EMP ORDER BY SAL DESC)
WHERE ROWNUM<=3;

EMP,DEPT 테이블 

사원중에 ALLEN과 같은직책인 사람들의 직책, 사원번호, 이름 ,급여 ,부서번호를 출력

SELECT JOB,EMPNO,ENAME,SAL,DEPTNO
FROM EMP
WHERE JOB =( SELECT JOB FROM EMP WHERE ENAME='ALLEN');

EMP,DEPT 테이블

사원중에 ALLEN과 같은직책인 사람들의 직책, 사원번호, 이름 ,급여 ,부서번호,부서이름을 출력

 

방법1 (JOIN 안쓰고)

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

방법 2 

SELECT JOB,EMPNO,ENAME,SAL,EMP.DEPTNO,DNAME
FROM EMP
INNER JOIN DEPT
ON EMP.DEPTNO= DEPT.DEPTNO 
WHERE JOB =( SELECT JOB FROM EMP WHERE ENAME='ALLEN');

 

EMP, SALGRADE 테이블을 이용해서

사원번호, 이름, 급여, 급여등급 출력하게끔 

해당사원의 급여등급이 나타나게 출력

 

방법1 조인안쓰고

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

방법2 조인쓰고 

SELECT EMPNO, ENAME, SAL GRADE
FROM EMP INNER JOIN salgrade
ON SAL BETWEEN LOSAL AND HISAL
ORDER BY ENAME,GRADE;

 

부서번호가 10번인 사람들 중에서

부서번호가 30번인 부서에 없는 직책을 가진 사람들의

사원번호, 이름 ,직책, 부서번호, 부서명, 부서위치를 출력

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

--직책이 SALESMAN인 사람들의 최고급여보다 큰 급여를 받는 사람들의
--이름, 직책, 급여, 급여등급을 출력

-- MAX를 이용한
SELECT EMP.ENAME, EMP.JOB, EMP.SAL, SALGRADE.GRADE
    FROM EMP INNER JOIN SALGRADE
    ON SAL BETWEEN LOSAL AND HISAL
WHERE SAL > (SELECT MAX(SAL) FROM EMP WHERE JOB = 'SALESMAN')
ORDER BY SAL DESC;

-- ALL을 이용한 (SAL에 값이 여러개라 모두 비교해야 되니)
SELECT EMP.ENAME, EMP.JOB, EMP.SAL, SALGRADE.GRADE
    FROM EMP INNER JOIN SALGRADE
    ON SAL BETWEEN LOSAL AND HISAL
WHERE SAL > ALL (SELECT SAL FROM EMP WHERE JOB = 'SALESMAN')
ORDER BY SAL DESC;

---

---

INSERT, UPDATE, DELETE

 

INSERT(삽입문) 

INSERT INTO 테이블 VALUES(추가할 내용들)

INSERT INTO 테이블(컬럼,컬럼) VALUES(추가할 내용들)

 

컬럼 순서와 추가할 내용들의 순서가 일치해야함

 

INSERT문 예제

-- 테이블만들기
CREATE TABLE DEPT_TEMP
AS SELECT*FROM DEPT;

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

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

--널값 넣기
INSERT INTO DEPT_TEMP VALUES(70,'JAVA',NULL);
INSERT INTO DEPT_TEMP VALUES(80,'JAVA','');
--''도 널로표시(스페이스바 없이)
INSERT INTO DEPT_TEMP(DEPTNO,DNAME) VALUES(90,'CSS');

--편법으로 테이블 껍데만 복사하기
CREATE TABLE 연습
AS SELECT * FROM EMP WHERE 1!=1;

--오늘 날짜 넣기 
INSERT INTO 연습 VALUES(9003,'정준하','개그맨',5555,SYSDATE,5000,10,30);

---

다른테이블의 있는 데이터 몇개를 뽑아서 내가 원하는 테이블 넣기

(서브쿼리 이용)

INSERT INTO 연습
SELECT*FROM EMP
WHERE DEPTNO =30;

SELECT*FROM EMP WHERE DEPTNO=30;
SELECT*FROM 연습;

INSERT 서브쿼리로 추가 VALUES  제외하고 작업

 

추가대상이 되는 테이블(연습) 과 추가할 내용이 있는 테이블(EMP)

컬럼 개수가 항상 일치해야함 (컬럼명 까지 같을 필요는 없음) 

컬럼의 데이터 형식도 같아야함

 

DEPT_TEMP 테이블에서 DEPTNO가 10,30인 레코드를 추가

INSERT INTO DEPT_TEMP
SELECT*FROM DEPT WHERE DEPTNO IN(10,30);

 

연습테이블에추가

급여가 EMP 테이블에서 SALGRADE테이블을 참조하여 급여등급이 1등급인 레코드만 연습테이블에 추가

INSERT INTO 연습

SELECT EMP.*

FROM EMP INNER JOIN SALGRADE

ON SAL BETWEEN AND HISAL

WHERE GRADE = 1;

COMMIT;

SLECT EMP.* ->EMP에 있는 컬럼들만 전부

하고 COMMIT; 해야함