오라클 sql dbeaver - 서브쿼리 sub query 인라인뷰 rownum, 상관쿼리 create insert unique

• suq query

--단일행 :결과 값이 1개 
--다중형 :결과 값이 여러개

예)퇴사한 여직원과 같은 직급, 같은 부서에 근무하는 직원들의 정보를 조회 
단일행
SELECT * FROM EMPLOYEE
WHERE DEPT_CODE=(SELECT DEPT_CODE FROM EMPLOYEE WHERE ENT_YN='Y')
AND JOB_CODE=(SELECT JOB_CODE FROM EMPLOYEE WHERE ENT_YN='Y')
AND EMP_NAME <> (SELECT EMP_NAME FROM EMPLOYEE WHERE ENT_YN='Y');

다중
SELECT * FROM EMPLOYEE 
WHERE (DEPT_CODE, JOB_CODE)=(SELECT DEPT_CODE,JOB_CODE FROM EMPLOYEE WHERE ENT_YN='Y')
AND EMP_NAME != (SELECT EMP_NAME FROM EMPLOYEE WHERE ENT_YN='Y');

 

 

•  - inline view

FROM 위치에 사용되는 서브쿼리는 
테이블을 테이블 명으로 직접조회하는대신
서브쿼리의 결과(RESULT SET)을 활용하여 조회 가능 
FROM 구문의 서브 쿼리를 INLINE VIEW(인라인 뷰)라고 부른다.

SELECT *
FROM (SELECT EMP_ID, EMP_NAME, DEPT_TITLE, JOB_NAME
FROM EMPLOYEE
JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
JOIN JOB USING(JOB_CODE));

 

• -row num

데이터를 조회할 때 각 행의 번호를 매겨주는 함수 
SELECT ROWNUM 번호, EMP_NAME,SALARY
FROM EMPLOYEE
WHERE ROWNUM <6;

--급여 기준으로 가장 높은 급여를 받는 사원
--상위 5명 조회하여 
--사번, 사원명, 급여 정보를 출력 
SELECT ROWNUM, EMP_ID, EMP_NAME,SALARY
FROM EMPLOYEE
WHERE ROWNUM<6
ORDER BY SALARY DESC;

--ROWNUM은 FROM 시작하면 바로 번호가 매겨져서 순서가 맞지 않음

SELECT EMP_ID, EMP_NAME,SALARY 
FROM EMPLOYEE
ORDER BY SALARY DESC;

SELECT ROWNUM, A.*
FROM (SELECT EMP_ID, EMP_NAME,SALARY 
FROM EMPLOYEE
ORDER BY SALARY DESC ) A
WHERE ROWNUM < 6;

-- () 안 전체를 A로 세팅하고 (AS 쓰던 것처럼 별칭으로 사용 가능, 테이블취급) , 
--SELECT는 A 의 . 전체를 가지고 온다는 뜻

--급여 평균이 3위 안에 드는 부서의 
--부서코드, 부서명, 급여평균을 조회 
--1. 내부에서 정렬할 인라인 뷰 쿼리문 작성 
SELECT DEPT_CODE, DEPT_TITLE, TRUNC(AVG(SALARY),-3)
FROM EMPLOYEE, DEPARTMENT 
WHERE DEPT_CODE=DEPT_ID
GROUP BY DEPT_CODE, DEPT_TITLE 
ORDER BY 3 DESC;

---2.ROWNUM과 인라인뷰를 반영하여 상위 3ㅐㄱ 조회 
SELECT ROWNUM,DEPT_CODE, DEPT_TITLE,평균 
FROM (SELECT DEPT_CODE, DEPT_TITLE, TRUNC(AVG(SALARY),-3) 평균
FROM EMPLOYEE, DEPARTMENT 
WHERE DEPT_CODE=DEPT_ID
GROUP BY DEPT_CODE, DEPT_TITLE 
ORDER BY 3 DESC )
WHERE ROWNUM < 4;

 

• -상호 연관 쿼리

 

--일반적으로 서브 쿼리는 서브쿼리대로, 메인 쿼리는 서브 쿼리의 결과만을 받아서 실행 
--메인쿼리가 사용하는 컬럼값, 계산식 등을 서브쿼리에 적용하여 서브쿼리 실행시 메인쿼리 값도 함꼐 사용


--사원의 직급에 따른 급여 평균보다 많이 받는 사원 조회 
--1
SELECT JOB_CODE 직급, TRUNC(AVG(SALARY), -3) 평균
FROM EMPLOYEE
GROUP BY JOB_CODE;
--2 
SELECT EMP_ID, EMP_NAME, JOB_CODE,SALARY
FROM EMPLOYEE E
WHERE SALARY > ( 
SELECT AVG(SALARY)
FROM EMPLOYEE E2
WHERE E.JOB_CODE = E2.JOB_CODE

 

-스칼라 서브 쿼리

--단일 행 + 상관쿼리 
--select, where, order by 절에 많이 사용된다
--select 절에 가장많이 사용 
--select list라고도 한다 

--모든 사원의 사번, 사번명, 관리자 사번, 관리자명 
--관리자가 없을 경우, '없음'이라고 출력 
--select문에 subquery를 사용하는 형식 


SELECT EMP_ID, EMP_NAME, MANAGER_ID, 
NVL((SELECT EMP_NAME FROM EMPLOYEE E2 WHERE E.MANAGER_ID = E2.EMP_ID),'없음') 관리자이름
FROM EMPLOYEE E 
ORDER BY 3, 1;

--자신이 속한 직급의 평균보다 많이 받는 사우너의
--이름, 직급명, 급여를 조회 
--WHERE문에 서브 쿼리 

--1 SUB준비 
SELECT EMP_NAME 이름, JOB_NAME 직급명, TRUNC(AVG(SALARY), -3) 평균
FROM EMPLOYEE E
JOIN JOB J ON (E.JOB_CODE=J.JOB_CODE)
GROUP BY JOB_NAME, EMP_NAME;

--2 
SELECT EMP_NAME 이름, JOB_NAME 직급명, SALARY  
FROM EMPLOYEE E 
JOIN JOB J ON (E.JOB_CODE=J.JOB_CODE) 
WHERE SALARY > (
SELECT AVG(SALARY)
FROM EMPLOYEE E2
WHERE E.JOB_CODE =E2.JOB_CODE
GROUP BY JOB_CODE 
);

 

 

 

• -create

데이터 베이스의 객체를 생성하는 DDL
형식 - CREATE 개체형태 개체명 (관련 내용)

테이블 생성
CREATE TABLE TB_TEST ( 
컬럼 자료형 (길이) 제약조건
); 


예) 
CREATE TABLE MEMBER(
MEMBER_NO NUMBER, --회원번호 
MEMBER_ID VARCHAR2(20), --회원아이디
MEMBER_PW VARCHAR2(20), --회원비밀번호 
MEMBER_NAME VARCHAR2(20) --회원이름 
);

SELECT * FROM USER_TABLES;
--접속한 계정이 가지고 있는 모든 테이블 보기


제약조건: 테이블에 데이터를 저장할때 지켜야하는 규칙 
NOT NULL = NULL값을 허용하지 않는다 (필수 입력 사항 )
UNIQUE - 중복 값을 허용하지 않는다 
CHECK - 지정한 입력 사항 외에는 받지 못하게 하는 조건 
PRIMARY KEY - (NOT NULL + UNIQUE) 
테이블 내에서 해당 행을 인식할 수 있는 고유의 값 
FOREIGN KEY - 외래키. 다른 테이블에서 지정된 값을 연결 지어서 참조로 가져온다.
--제약 조건 (CONSTRAINTS) 
--테이블을 생성할 때 각 컬럼에 값을 기록하는것에 대한 제약사항을 설정
--이러한 제약조건을 통해 저장되는 값이 아무런 문제가 되지 않는다라는 무결성을 보장할 수 있다. 

NOT NULL : NULL값을 허용하지 않는다 
--해당 칼럼에 반드시 값을 기록해야 하는 경우
--데이터 삽입/수정/삭제 시에 NULL값을 허용하지 않도록 칼럼 작성시 함께 선언
--사용자 계정의 제약조건 확인

SELECT * FROM USER_CONSTRAINTS;

--테이블 별 컬럼 제약 조건 확인
SELECT * FROM USER_CONS_COLUMNS;


 

 

• -insert

테이블에 값 추가 (DML에서)
INSERT INTO ~
VALUES ( );

 

• -unique

중복을 허용하지 않는 제약 조건. 
CREATE TABLE USER_UNIQUE( 
USER_NO NUMBER,
USER_ID VARCHAR2(20) UNIQUE,
USER_PW VARCHAR2(20),
USER_NAME VARCHAR2(15)
);
-> 똑같은 ID값을 넣으려고 하면 에러
위의 예는 컬럼레벨로 제약 조건을 작성, 
CREATE TABLE USER_UNIQUE( 
USER_NO NUMBER,
USER_ID VARCHAR2(20),
USER_PW VARCHAR2(20),
USER_NAME VARCHAR2(15),
UNIQUE(USER_ID)
);
이와 같이 칼럼을 모두 작성한 후에 
아래에 제약조건을 추가하면 테이블 레벨 제약 조건 


두 개 이상의 칼럼을 제약조건으로 묶을 경우
반드시 테이블 레벨에서 제약 조건 선언 
CREATE TABLE USER_UNIQUE3 ( 
USER_NO NUMBER,
USER_ID VARCHAR2(20),
USER_PW VARCHAR2(20),
USER_NAME VARCHAR2(15),
UNIQUE(USER_NO, USER_ID)
);

조건이 2개인 위와 같은 경우에는, 
USER_NO가 똑같더라도 USER_ID가 다르면 다르므로 입력 가능 (OR의 개념)