[Day4] Oracle 4 - Subquery, CONCAT, MOD, INSERT, REPLACE, NLS, Dual, SYSDATE, RR/YY, Session, Synonym

[Day4] Oracle 4 [9/28]

 

 

-- SCOTT 0928(수) --

1. subquery 에 대해 설명하세요

1) 하나의 SQL문장의 절에 부속된 또 다른 SELECT 문장.

2) 두 번의 질의를 수행해야 얻을 수 있는 결과를 한 번의 질의로 해결할 수 있는 문장.

3) 질의가 미정된 값을 근거로할 때 유용하다.

4) ORDER BY 절에는 서브쿼리를 사용할 수 없다.

( 예외: 인라인 뷰에서는 사용할 수 있다. )

 

2. Inline View 에 대해 설명하세요 ?

서브쿼리의 일종.

FROM 절에 있는 서브쿼리.

3. Nested Subquery 에 대해 설명하세요 ?

WHERE 절에 있는 서브쿼리.

4. WITH 절 에 대해 설명하세요.

DQL 문 ( select 문 )

1 WITH 절

1) 사용될 서브쿼리 블럭을 미리 선언하여 반복 사용.

2) 형식

with

쿼리이름1 AS ( 서브쿼리 ),

쿼리이름2 AS ( 서브쿼리 ),

쿼리이름3 AS ( 서브쿼리 ),

:

:

3) 서브쿼를 사용하면 성능 저하된다. (단점) + 코딩 간결, 쉽다. (장점)

 

SELECT

FROM

WHeRE

GROUP BY

HAVING

ORDER BY

5. 스키마( Schema ) 란 ?

6. LIKE 연산자에 대해서 설명하세요 ?

WHERE 조건절

WHERE 컬럼명 LIKE '패턴 와일드카드 % _' ESCAPE 옵션

 

7. REGEXP_LIKE() 함수에 대해서 설명하세요 ?

정규표현식을 사용하는 LIKE 함수

REGEXP_LIKE( 컬럼명, '정규표현식 패턴', '모드 i, c, m 등등')

복습 + (암기)

8. 어제까지 배운 Oracle 함수를 적고 설명하세요 .

ㄱ. NVL2(), NVL() comm null 연산 => null

ㄴ. UPPER(), LOWER(), INITCAP()-첫문자만 대문자          UPPER('hr')

ㄷ.

SUBSTR() - 앞에 87을 가져오기 위해/ 문자로 가져옴 (0,1) ,

TO_CHAR('날짜', 숫자, '문자', ) - 문자로 가져옴,

EXTRACT 81 입사 81/02/12 - 숫자로 가져옴

* 왼쪽정렬 문자 / 오른쪽정렬 숫자

 

ㄹ. REGEXP_LIKE()

ㅁ. || CONCAT() // 문자열 연결

등등

 

9. insa 테이블에서 사원들이 속한 부서명을 중복되지 않게 아래와 같이

정렬해서 출력하세요.

[출력결과]

BUSEO

---------------

개발부

기획부

영업부

인사부

자재부

총무부

홍보부

 

SELECT DISTINCT buseo

FROM insa

ORDER BY buseo ASC; (주의!)

 

힌트) 81년 입사한 사원 정보 조회.

10. insa 테이블에서 70년대생 남자사원만 아래와 같이 주민등록번호로 정렬해서 출력하세요.

[출력결과]

NAME RRN

-------------------- --------------

문길수 721217-1******

김인수 731211-1******

김종서 751010-1******

허경운 760105-1******

정한국 760909-1******

최석규 770129-1******

지재환 771115-1******

홍길동 771212-1******

산마루 780505-1******

장인철 780506-1******

박문수 780710-1******

이상헌 781010-1******

김정훈 790304-1******

박세열 790509-1******

이기상 790604-1******

 

15개 행이 선택되었습니다.

SELECT name

-- , ssn rrn

-- , SUBSTR( ssn, 1, 1) // (ssn, 0, 1)도 같음, 주민번호 첫자리 가져오기 // 이건 테스트용임

, CONCAT( SUBSTR( ssn , 0, 8 ), '******' ) rrn 

FROM insa 

-- Oracle 나머지 구하는 연산자 X, 함수 O MOD()

--WHERE SUBSTR( ssn, 1, 1) = '7' AND SUBSTR( ssn, 8, 1)  = '1' -- 1만 선택

a) WHERE SUBSTR( ssn, 1, 1) = '7' AND MOD( SUBSTR( ssn, 8, 1) , 2 ) = '1' -- 1,3,5,7,9 남자 (2로 나눴을때 나머지 1)

b) WHERE ssn LIKE '7%' AND MOD( SUBSTR( ssn, 8, 1) , 2 ) = '1' -- LIKE 연산자 사용 70년대생이면서 남자

--WHERE ssn LIKE '7_____-1%'

WHERE REGEXP_LIKE( ssn, '^7\d{5}-[13579]\d{6}$')

ORDER BY ssn ASC;

11. insa 테이블에서 70년대 12월생 모든 사원 아래와 같이 주민등록번호로 정렬해서 출력하세요.

[출력결과]

NAME SSN

-------------------- --------------

문길수 721217-1951357

김인수 731211-1214576

홍길동 771212-1022432

 

1) LIKE 연산자

2) REGEXP_LIKE 함수

 

SELECT name , ssn

FROM insa

WHERE ssn LIKE '7_12%'

--WHERE REGEXP_LIKE( ssn, '^7\d12')

ORDER BY ssn ASC;

 

12. LIKE 연산자의 ESCAPE 에 대해서 설명하세요.

1) wildcard( %, _ )를 일반문자 처럼 쓰고 싶은 경우에 ESCAPE 옵션을 사용한다.

-- emp 테이블에서 사원번호 7369의 사원명을 S_MITH로 수정하는 DML문을 실행하고,
SELECT *
FROM emp
WHERE empno = 7369;
-- DML + 커밋, 롤백
UPDATE emp
SET ename = 'SMITH'
WHERE empno = 7369;
COMMIT;

-- emp 테이블에서 사원명에 's_' 문자열을 포함한 사원 정보를 조회하는 쿼리 작성하세요.
-- ㄱ. LIKE 연산자
-- ㄴ. REGEXP_LIKE 함수

SELECT ename
FROM emp
WHERE REGEXP_LIKE(ename, 's_' , 'i');
--WHERE ename LIKE '%' || UPPER('s\_') || '%' ESCAPE '\';
--WHERE LOWER(ename) LIKE '%s_%';
WHERE ename LIKE '%%'; -- 실행 O

예) dept 테이블 조회

SELECT *

FROM dept;

예) 새로운 부서 하나 테이블에 레코드(행) 추가 DML - insert 문

INSERT 형식)

INSERT INTO 테이블명 [( 컬럼명,...)] VALUES ( 컬럼값, 컬럼값... );

 

INSERT INTO dept ( deptno, dname, loc ) VALUES ( 40, 'QC100%T', 'SEOUL' );

-- ORA-00001: unique constraint (SCOTT.PK_DEPT) violated

-- 유일성 제약조건 위배되었다.

-- PK_DEPT : PK 의 이름

-- deptno 컬럼값은 중복되면 안된다.

-- PK 제약조건 = 1) U + NN

 

CREATE TABLE DEPT
       (DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
	DNAME VARCHAR2(14) ,
	LOC VARCHAR2(13) ) ;

 

-- ******

-- 팀프로젝트 : DB 모델링(설계)

INSERT INTO dept ( deptno, dname, loc ) VALUES ( 100, 'QC100%T', 'SEOUL' );

-- ORA-01438: value larger than specified precision allowed for this column

 

INSERT INTO dept ( deptno, dname, loc ) VALUES ( 50, 'QC100%T', '대한민국_서울_강남구' );

-- ORA-12899: value too large for column "SCOTT"."DEPT"."LOC" (actual: 29, maximum: 13)

-- 한글 한 문자는 3바이트 처리. 27 + 2

 

 

INSERT INTO dept ( deptno, dname, loc ) VALUES ( 50, 'QC100%T', 'SEOUL' );

-- 컬럼 순서대로 컬럼값을 할당할 때는 컬럼명 생략 가능

INSERT INTO dept VALUES ( 50, 'QC100%T', 'SEOUL' );

 

INSERT INTO dept ( dname, loc, deptno ) VALUES ( 'QC100%T', 'SEOUL' , 50 );

-- ORA-01722: invalid number

 

-- 설명) dname, loc 필수 입력 컬럼 X == 널 허용.

INSERT INTO dept ( deptno, dname, loc ) VALUES ( 50 );

-- ORA-00947: not enough values 컬럼값이 충분하지 않다.   -> 

해결방법 

 

a) INSERT INTO dept ( deptno, dname, loc ) VALUES ( 50 , null, null );

b) INSERT INTO dept ( deptno ) VALUES ( 50 );

 

 

INSERT INTO dept ( deptno, dname, loc ) VALUES ( 50, 'QC100%T', 'SEOUL' );

 

COMMIT;

ROLLBACK;

SELECT *

FROM dept;

 

테이블 제약조건 관련 오류가 발생했을때!!!

-- 테이블의 구조 확인

DESC dept;

 

 

 

이름                       널?                      유형

------               --------             ------------

 

DEPTNO          NOT NULL         NUMBER(2)                   // 숫자(정수, 실수) 2자리 정수

DNAME                                      VARCHAR2(14 CHAR)    // 문자열

LOC                                            VARCHAR2(13 BYTE)     // 문자열

널 허용한다. : 입력 안해도 된다.

회원가입 : NOT NULL 필수 입력 항목

널 허용 O : 입력하지 않아도 된다.

2[,4] = 실수

[p]recision 정밀도 -> NUMBER()에 들어가는거

---

문제) dept 테이블에서

부서명에 '100%' 문자열을 포함하는 부서의 정보를 검색해서 조회(출력)하세요.

 

SELECT *
FROM dept
WHERE REGEXP_LIKE( dname, '100%' ); 
--WHERE dname LIKE '%100\%%' ESCAPE '\';

 

문제) dept 테이블에서 부서명에 '100%' 문자열을 포함하는 부서의 지역명(loc) 컬럼값 "대한민국_서울_강남구" 수정

UPDATE dept

SET loc = '대한민국_서울_강남구'

WHERE REGEXP_LIKE( dname, '100%' );

-- ORA-12899: value too large for column "SCOTT"."DEPT"."LOC" (actual: 29, maximum: 13)

문제) 50 = deptno 레코드(행)를 삭제.

DELETE FROM 테이블명

[WHERE 조건절]; 없으면 테이블의 모든 레코드를 삭제한다.

 

--DELETE FROM dept;

-- ORA-02292: integrity constraint (SCOTT.FK_DEPTNO) violated - child record found

 

-- 왜? 10번 부서는 삭제가 되지 않을까요? : 참조관계이기 때문에

 

parent deptno (참조) child

dept 테이블 emp 테이블

부모테이블의 부서번호 자식테이블의 부서번호(외래키,FK) 컬럼에서 참조

관계형 데이터 모델

 

개체 - 관계(연관성)- 개체

릴레이션

테이블

부모테이블 자식테이블

dept emp

deptno(PK) deptno 참조키, 외래키, FK

empno PK 고유키

 

 

DELETE FROM dept

WHERE deptno = 50;

 

COMMIT;

WHERE deptno IN ( 10, 20, 30 );

-- ORA-02292: integrity constraint (SCOTT.FK_DEPTNO) violated - child record found

-- 무결성 제약조건 위배된다.

-- emp테이블의 deptno FK 제약조건 위배 동일의미

-- 2개 행 이(가) 삭제되었습니다.

 

WHErE deptno >= 40;

WHERE deptno = 40; -- 40, 50

 

DELETE FROM emp;

-- 14개 행 이(가) 삭제되었습니다.

SELECT *

FROM dept;

 

ROLLBACK;

 

-------------------------------

10    ACCOUNTING   NEW YORK

20   RESEARCH        DALLAS

30   SALES               CHICAGO

40   OPERATIONS    BOSTON

 

문제) 부서명 -> 'SALES' -> 'S%ALES' 수정

UPDATE dept

SET dname = 'S%ALES'

WHERE deptno = (

SELECT deptno

FROM dept

WHERE dname = 'SALES'

);

COMMIT;

 

SELECT deptno

FROM dept

WHERE dname = 'SALES';

 

문제) dept테이블에서 부서명에 '%' -> 제거

UPDATE dept

-- SET dname = REPLACE( dname , '%', '')

SET dname = REPLACE( dname , '%')         // 제거한다는뜻이라 세번째 인자값 안줘도 됨

--[WHERE 조건절]

 

SELECT dname

FROM dept;

 

-- JAVA dname.replace() .replaceAll(X, Y)

-- ORACLE

 

CRUD 작업 == CREATE  READ   UPDATE  DELETE

                        insert     select   update    delete

 

 

 

13. emp 테이블에서

pay(sal+comm) 1000 이상~ 3000 이하 받는 30부서원을 제외한 모든 사원들만 ename을 기준으로 오름차순 정렬해서 조회하는 쿼리를 작성하세요.

 

ㄱ. with 절 사용 (

 

WITH

temp AS (

SELECT deptno, ename, sal + NVL( comm, 0 ) pay

FROM emp

WHERE deptno != 30

)

SELECT *

FROM temp

WHERE pay BETWEEN 1000 AND 3000

ORDER BY deptno ASC;

 

ㄴ. inline view 사용

 

SELECT *

FROM (

SELECT deptno, ename, sal + NVL( comm, 0 ) pay

FROM emp

WHERE deptno != 30

) 

WHERE pay BETWEEN 1000 AND 3000

ORDER BY deptno ASC;

 

ㄷ. 일반 쿼리 사용.

SELECT deptno, ename, sal + NVL( comm, 0 ) pay

FROM emp

WHERE deptno != 30 AND ( sal + NVL( comm, 0 ) BETWEEN 1000 AND 3000 );

 

[출력결과]

DEPTNO     ENAME        PAY

---------- ---------- ----------

10               MILLER      1300

10               CLARK        2450

20              JONES        2975

20              FORD         3000

 

14. insa테이블에서 ssn 컬럼을 통해서 year, month, date, gender 출력

[출력결과]

SSN                                YEAR        MONTH       DATE     GENDER

----------                      ------         ----           -----        -----

771212-1022432             77                12               12             1

801007-1544236           80                10              07             1

770922-2312547            77                09              22            2

790304-1788896          79                03              04            1

811112-1566789              81                 11               12             1

:

60개 행이 선택되었습니다.

 

SELECT ssn

, SUBSTR( ssn, 0, 2) year

, SUBSTR( ssn, 3, 2 ) month

, SUBSTR( ssn, 5, 2 ) "DATE" -- date 날짜 자료형(키워드) -> 예약어이기 때문에 "" 붙여주기!

, SUBSTR( ssn, -7, 1 ) gender

FROM insa

15. emp 테이블에서 입사년도 컬럼에서 년,월,일 찾아서 출력

ㄱ. 년도 찾을 때는 TO_CHAR() 함수 사용

ㄴ. 월 찾을 때는 SUBSTR() 함수 사용

ㄷ. 일 찾을 때는 EXTRACT() 함수 사용

 

DATE hiredate -> y,m,d

 

SELECT ename, hiredate

, TO_CHAR( hiredate, 'YYYY' ) year -- '1981'

, SUBSTR( hiredate, 4, 2) month -- '12'

, EXTRACT( DAY FROM hiredate ) "DATE" -- 17

FROM emp;

 

DELETE FROM emp

WHERE ename IN ('ADAMS', 'SCOTT');

COMMIT;

 

[출력결과]

ENAME HIREDATE YEAR MONTH DATE

---------- -------- ---- -- --

SMITH 80/12/17 1980 12 17

ALLEN 81/02/20 1981 02 20

WARD 81/02/22 1981 02 22

JONES 81/04/02 1981 04 02

MARTIN 81/09/28 1981 09 28

BLAKE 81/05/01 1981 05 01

CLARK 81/06/09 1981 06 09

KING 81/11/17 1981 11 17

TURNER 81/09/08 1981 09 08

JAMES 81/12/03 1981 12 03

FORD 81/12/03 1981 12 03

MILLER 82/01/23 1982 01 23

 

12개 행이 선택되었습니다.

16. emp 테이블에서 직속상사(mgr)가 없는 사원의 정보를 조회하는 쿼리 작성.

 

[출력결과]

EMPNO ENAME MGR

---------- ---------- ----------

7839 KING 0

 

SELECT empno, ename, NVL(mgr, 0) MGR

FROM emp

WHERE mgr IS NULL; -- IS NULL, IS NOT NULL SQL연산자

-- WHERE mgr = null;

 

17. 모든 롤( ROLE ) 확인

SELECT *

FROM dba_roles;

18. RESOUCE 롤의 모든 권한 확인

SELECT *

FROM role_sys_privs

WHERE role = 'RESOURCE';

19. scott 계정이 부여받은 롤 확인

SELECT *

FROM user_role_privs;

20. [YY와 RR의 차이점]에 대해서 설명하세요 . ( 다 하신 분은 찾아 보세요 )

 

yyyy-MM-dd

4자리 년도

yy

2자리 년도

 

Ora_Help to_char 검색

【형식】

TO_CHAR( date [,'fmt' [,'nls param']])  // 날짜(date)가 입력되면 문자(TO_CHAR) 로 바꿔주는 함수, nls param = 생략가능

 

 -- YY/MM/DD

-- RR/MM/DD NLS

-- 22/09/28 도구/환경설정/데이터베이스/NLS - 날짜형식 RR/MM/DD

SELECT SYSDATE , TO_CHAR( SYSDATE , 'CC') -- 21세기 == 2000년대

FROM dual; -- [ dual ]

 

                                      

                     

	0~50	51~99
ex)	'22/01/20'	'88/01/20'
RR	2022/01/20	1988/01/20
YY	2022/01/20	2088/01/20

 

오늘날짜

SELECT SYSDATE
FROM dual;

 SYSDATE?

[SYSDATE 함수]

1) 현재 시스템의 날짜 + 타임

2) 22/09/28 시간,분,초 -> TO_CHAR()함수

RR/MM/DD

 

= 

java

Date d = new Date();
C c = C.getInstance();
LocalDateTime.now() of()

 

21. emp 테이블에서 사원이름에 'la' 문자열을 포함하는 사원 정보 출력

ㄱ. LIKE 사용

SELECT deptno, ename

FROM emp

WHERE LOWER( ename) LIKE '%la%';

WHERE ename LIKE '%' || UPPER('la') || '%';

 

ㄴ. REGEXP_LIKE() 사용

WHERE REGEXP_LIKE( ename, 'la', 'i');

 

[출력결과]

DEPTNO ENAME

---------- ----------

30 BLAKE

10 CLARK

22. hr 계정으로 접속

employees 테이블에서 first_name, last_name 이름 속에 'ev' 문자열 포함하는 사원 정보 출력

 

[출력결과]

FIRST_NAME LAST_NAME NAME NAME

-------------------- ------------------------- ---------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Kevin Feeney Kevin Feeney K[ev]in Feeney

Steven King Steven King St[ev]en King

Steven Markle Steven Markle St[ev]en Markle

Kevin Mourgos Kevin Mourgos K[ev]in Mourgos

 

HR

SELECT t.* , REPLACE( name, 'ev' , '<span color="red">ev</span>' )
FROM (
SELECT first_name, last_name
, first_name || ' ' || last_name name
FROM employees
) t
WHERE name LIKE '%ev%';

13행, 10열에서 오류 발생
ORA-00923: FROM keyword not found where expected
FROM 키워드가 필요한 위치에 없습니다. -> *이 아니라 t.*이라고 해야댐

 

--------------------------------------------------------------------------------------------

 

 

[ dual ]

1. 테이블이다.

2. SYS 사용자가 소유하고 있는 오라클의 표준 테이블

3. 더미(dummy)테이블 - 가짜, 모조의

4. 오직 한 행, 한 컬럼

5. 사용처 : 일시적으로 산술연산이나 날짜 연산할 때 주로 사용한다.

6. 사용할 때 SYS.dual => dual 으로 사용하지 않아도 된다.

스키마.테이블명

그 이유 ? 모든 사용자가 사용할 수 있도록 PUBLIC SYNONYM(시노님)으로 설정했기 때문이다.

 

DESC dual;

-> 결과값 : DUMMY VARCHAR2(1)

SELECT * FROM dual; -> X값 (한행 한컬럼) 만 가지고있음

--SYS--

SELECT *
FROM  dba_roles;

 
-- 모든 권한을 가지고 있는 DBA 계정 --
SELECT* 
FROM arirang;
FROM scott.emp;  -- 모든 권한 가지고있더라도 접근할 때 스키마.테이블명으로 접근해야함!
FROM emp;   -- ORA-00942: table or view does not exist

--HR--

-- HR --
-- HR scott.emp 테이블에 SELECT 권한이 없기 때문에
SELECT *
FROM arirang;
FROM scott.emp; -- ORA-00942: table or view does not exist
FROM emp; -- ORA-00942: table or view does not exist


-- HR 계정 scott.emp SELECT 권한 부여 ~ (scott 계정에서)

--SCOTT--

-- DCL문
GRANT SELECT ON emp TO hr;
-- Grant을(를) 성공했습니다.

 

 [ SYNONYM ]

- 데이터베이스 객체에 대한 소유권은 해당 객체를 생성한 사용자가 가진다.

- 따라서 다른 사용자가 소유한 객체에 접근하기 위해서는 소유자로부터 접근권한을 부여 받아야한다. 

- 다른 사용자가 소유한 객체 조회할 때 소유자의 ID를 객체 앞에 적어야한다. (ex) scott.emp)

-> 스키마.객체명 = 번거로움

 

 

해결법(Synonym) : 스키마.객체명 == 이름 정의하는 것.

 

【형식】

CREATE [PUBLIC] SYNONYM [schema.]synonym명

FOR [schema.]object명; 

• 디폴트는 private 시노님이다.

• PUBLIC 시노님은 모든 사용자가 접근 가능한 시노님을 생성한다.

• PUBLIC 시노님은 모든 사용자가 접근 가능하기 때문에 생성 및 삭제는 오직 DBA만이 할 수 있다.

 

--SYS--

-- 시소님 삭제
【형식】
	DROP [PUBLIC] SYNONYM synonym명;

DROP PUBLIC SYNONYM arirang;
-- SYNONYM ARIRANG이(가) 삭제되었습니다.

 -- 시노님 생성 쿼리 : DDL
 CREATE PUBLIC SYNONYM  arirang
  	   FOR scott.emp;
-- ORA-00955: name is already used by an existing object

 

 

SELECT *

FROM emp;             -- 접속한 사용자가 scott

FROM scott.emp;    -- 스키마.객체명(테이블명)

 

 

--------------------------------------------------------------------------------------------

[ NLS ]

1. National Language Support

국가 언어 지원

2. NLS 설정의 우선 순위

TO_CHAR( , [, nls_param ])

SESSION > CLIENT > SERVER

?

 

오라클 서버 클라이언트

미국 일본

예) 통화기호 : 달러$ <- 엔 DD/MM/YY

세션 : 통화기호 \원

MM/DD/YY YY/MM/DD

--------------------------------------------------------------------------------------------

[ 오라클에서 session이란 ? ]

1. 세션(session)

오라클 <- 접속

SQL 실행

오라클 -> 접속 종료

세션 : DB 접속하여 일련의 SQL 실행 후 DB 종료시점까지를 말함