SQLD 2과목 필기

CREATE TABLE test1

( id NUMBER NOT NULL,

name VARCHAR2(100) NOT NULL,

gender VARCHAR2(10) NULL,

age NUMBER NULL,

nowdate DATE NULL,

etc VARCHAR2(300) NULL,

PRIMARY KEY (id) );

 

SELECT * FROM TEST1 t ;

 

 

INSERT INTO test1 (id, name, gender, age, nowdate)

VALUES (1, '조성호', '남자', 28, '2018-01-01');

INSERT INTO test1 (id, name, gender, age, nowdate)

VALUES (2, '전예진', '여자', 25, '2018-01-01');

INSERT INTO test1 (id, name, gender, age, nowdate)

VALUES (3, '손흥민', '남자', 33, '2018-01-01');

INSERT INTO test1 (id, name, gender, age, nowdate)

VALUES (4, '전수빈', '여자', 30, '2018-01-01');

 

SELECT ASCII('A') FROM DUAL; --문자열에 맞는 ASCII코드

SELECT CHR(5) FROM DUAL; --ASCII코드에 맞는 문자열

SELECT SUBSTR('HELLO WORLD',4,5) FROM DUAL; -- 4부터5개만 잘라내기

SELECT CONCAT('HE','LLO') FROM DUAL; -- 문자열 결합

SELECT LOWER('Hello') FROM DUAL; -- 모두 소문자

SELECT UPPER('Hello') FROM DUAL; -- 모두 대문자

SELECT LENGTH ('HE LLO')FROM DUAL; -- 공백포함 문자길이

SELECT TRIM('HE LLO') FROM DUAL; -- 공백삭제

SELECT LTRIM('HE LLO') FROM DUAL; -- 공백삭제-왼쪽

SELECT RTRIM('HE LLO') FROM DUAL; -- 공백삭제-오른쪽

SELECT SYSDATE FROM DUAL; -- 현재시간 2024-03-03 14:54:08.000

SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL; -- SYSDATE에서 YEAR만 빼오기 -2024

SELECT ABS(-5) FROM DUAL; --절대값 5

SELECT SIGN(2) FROM DUAL; --양수면 1 음수면-1 0이면 0

SELECT MOD(10,5) FROM DUAL; --나머지

SELECT CEIL(5) FROM DUAL; --크거나 같은

SELECT FLOOR(5) (5) FROM DUAL; -- 작거나 같은/ 현재 기능안됨

SELECT ROUND(3.14,1) FROM DUAL; --반올림

SELECT TRUNC(3.14,1)FROM DUAL; --내림

 

SELECT DECODE(NAME, '전예진','예진아 안녕','누구세요') FROM TEST1 t ; -- 컬럼 ,값 ,T, F

 

SELECT CASE

WHEN NAME = '전예진' THEN '일치'

ELSE '불일치'

END

FROM TEST1 t ;

 

WITH AABBCC AS (

SELECT NAME FROM TEST1 t

)

SELECT * FROM AABBCC;

 

 

CREATE TABLE TEST_2

( id_2 NUMBER NOT NULL,

name_2 VARCHAR2(100) NOT NULL,

gender_2 VARCHAR2(10) NULL,

age_2 NUMBER NULL,

nowdate_2 DATE NULL,

etc_2 VARCHAR2(300) NULL,

PRIMARY KEY (id_2) );

 

 

INSERT INTO test_2 (id_2, name_2, gender_2, age_2, nowdate_2)

VALUES (1, '조성호', '남자', 28, '2018-01-01');

INSERT INTO test_2 (id_2, name_2, gender_2, age_2, nowdate_2)

VALUES (2, '전예진', '여자', 25, '2018-01-01');

INSERT INTO test_2 (id_2, name_2, gender_2, age_2, nowdate_2)

VALUES (3, '손흥민', '남자', 33, '2018-01-01');

INSERT INTO test_2 (id_2, name_2, gender_2, age_2, nowdate_2)

VALUES (4, '전수빈', '여자', 30, '2018-01-01');

 

SELECT * FROM TEST_2 t ; --

 

--서브쿼리

/*

인라인뷰 : FROM 안에 SELECT문 사용

스칼라 서브쿼리 : SELECT SELECT문 사용

* 반드시 한 행과 한 컬럼만 반환하는 서브쿼리이다

여러행을 반환하면 오류가 발생한다

서브쿼리 : WHERE 안에 SELECT문 사용

연관쿼리 서브쿼리에서 메인쿼리의 칼럼을 사용하는것

 

 

서브쿼리

-단일 행 SINGLE ROW : = < > 이런 연산, 반드시 한 행만 조회한다

-다중 행 MULTI ROW : IN ANY ALL EXISTS 이런 연산, 여러개의 행을 조회한다

IN : 서브쿼리 결과가 하나만 동일하면 참

ALL : 서브쿼리 결과가 모두 동일하면 참

ANY : 서브쿼리 결과가 하나라도 동일하면 참

EXISTS : 서브쿼리 결과가 하나라도 존재하면 참

**/

 

 

 

SELECT ID, NAME, GENDER FROM TEST1 t , TEST_2 t2

WHERE TEST1.NAME = TEST_2.NAME_2

AND TEST1.ID

IN (SELECT NAME FROM TEST1 t3 -- IN 연산 - 오류

WHERE AGE >30);

 

SELECT * FROM TEST1

WHERE AGE <= ALL(30,40); -- 30보다 작거나 같고, 40보다 작거나 같으면 조회

--ALL은 모두 참이어야 참.

 

SELECT ID NAME FROM TEST1 , TEST_2 t --EXISTS 은 존재하면T, 존재하지않으면 F

WHERE TEST1.NAME = TEST_2.NAME_2

AND EXISTS (SELECT 1 FROM TEST1 t2

WHERE TEST1.AGE > 30);

 

 

/*

* [교집합]

* 조인

* -등가조인 EQUI : '='을 사용해서 두 테이블을 연결한다

* SELECT * FROM T1, T2

* WHERE T1.NAME = T2.NAME2

*

* INNER JOIN : 테이블1 INNER JOIN 테이블2 ON 조건

* SELECT * FROM T1 INNER JOIN T2

* ON T1.NAME=T2.NAME

*

* 해쉬조인 : EQUI조인만 사용가능하다

* 테이블을 해시 메모리에 적재한후 해시함수로 연결하는 방법

* 테이블의 수가 적은것을 선행테이블로 지정한다

*

* INTERSECT연산: 두 테이블의 교집합을 조회하는것

* SELECT NAME FROM T1

* ITERSECT

* SELECT NAME FROM T2

*

* -비등가조인 NON-EQUI : ' > < <= >= ' 을 사용해서 연결한다

*

* OUTER JOIN : EUQI JOIN + 한쪽테이블만 있는 데이터도 포함

* '(+)'를 사용

* SELECT * FROM T1, T2

* WHERE T1.NAME (+)= T2.NAME

* NULL 조회가능

*

* LEFT OUTER JOIN : 왼쪽 테이블만 있는 행도 포함하면

* SELECT * FROM T1 LEFT OUTER JOIN T2

* ON T1.NAME = T2.NAME

* RIGHT OUTER JOIN : 오른쪽 테이블만 있는 행도 포함하면

* SELECTO * FROM T1 RIGHY OUTER JOIN T2

* ON T1.NAME = T2. NAME;

* FULL OUTER JOIN : 왼쪽 오른쪽 둘다

* CROSS JOIN : 조인 조건구 X-> 카테시안 곱 발생

* SELECT * FROM T1 CROSS JOIN T2;

* 카테시안 곱: SELECT * FROM T1, T2;

*

* [합집합]

* UNION : 중복데이터 제거하면서 두테이블을 하나로 합치는것

* - 칼럼 수 , 칼럼의 데이터 등 모두 일치해야한다

* - 중복된 데이터는 제거

* SELECT * FROM T1

* UNION

* SELECT * FROM T2

* UNION ALL : 중복 제거 X, 정렬 X

* SELECT * FROM T1

* UNION ALL

* SELECT * FROM T2

*

* [차집합]

* MINUS : 1번째 SELECT에는 해당되나 , 2번째 SELECT문에는 해당되지않는 것 조회

* => 1번째 테이블에만 있는 것 조회

* = EXCEPT

* SELECT * FROM T1

* MINUS

* SELECT * FROM T2

* */

 

 

SELECT AGE, SUM(AGE) FROM TEST1 t

GROUP BY ROLLUP(AGE); -- ROLLUP : GROUP BY의 SUBTOTAL(총합계)를 내준다

 

SELECT AGE, GENDER, NAME ,SUM(AGE) FROM TEST1 t

GROUP BY GROUPING SETS ( AGE, GENDER, NAME); -- GROUPING SETS : 그룹별로 합계를 계산한다

 

SELECT AGE, GENDER, NAME ,SUM(AGE) FROM TEST1 t

GROUP BY CUBE ( AGE, GENDER, NAME); -- CUBE : 모든 결합가능한 집계를 계산한다

 

SELECT AGE, GROUPING(AGE) ,SUM(AGE) FROM TEST1 t -- GROUPING : ROLL UP, GROUPING SETS, CUBE 에서 생성되는 합계값들을 구분하는 함수

GROUP BY ROLLUP(AGE);

 

SELECT NAME, ID, AGE ,SUM(AGE)

OVER (ORDER BY AGE

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) "윈도우함수요"

FROM TEST1 t ;

--ROWS: 물리적인 단위

--RANGE: 논리적인 위치

--UNBOUNDED PRECEDING : 시작이 첫번째 행이야

--UNBOUNDED FOLLOWING : 마지막이 마지막 행이야

--CURRENT ROW : 시작이 현재행이야

 

INSERT INTO TEST1(ID, NAME, GENDER, AGE, NOWDATE)VALUES (5,'박지성','남자',33,'2024-01-01');

 

SELECT AGE, RANK() OVER (ORDER BY AGE) AS RANK,

DENSE_RANK() OVER(ORDER BY AGE) AS DENSE_RANK1

FROM TEST1 t;

--RANK : 동일한 순위는 동일한 값

--DENSE_RANK : 동일한 순위는 하나의 건수

SELECT AGE, RANK() OVER (ORDER BY AGE) AS RANK,

RANK() OVER (PARTITION BY GENDER ORDER BY AGE) AS RANK

FROM TEST1 t;

--PARTITION: 소그룹으로 나누기

SELECT AGE, RANK() OVER (ORDER BY AGE) AS RANK,

ROW_NUMBER() OVER(ORDER BY AGE) AS A

FROM TEST1 t;

--ROW_NUMBER: 순위 동일해도 그냥 숫자 매겨

 

SELECT SUM(AGE) FROM TEST1 t ;

SELECT AVG(AGE) FROM TEST1 t ;

SELECT COUNT(AGE) FROM TEST1 t ;

SELECT MAX(AGE) FROM TEST1 t ;

SELECT MIN(AGE) FROM TEST1 t ;

SELECT NAME, ID, FIRST_VALUE(AGE) OVER (ORDER BY AGE )FROM TEST1 t ;

SELECT NAME, ID, LAST_VALUE(AGE) OVER (ORDER BY AGE )FROM TEST1 t ;

SELECT NAME, ID, LAG(AGE) OVER (ORDER BY AGE )FROM TEST1 t ;

--LAG는 이전의 값을 가져온다

/*

전예진 2 NULL

조성호 1 25

전수빈 4 28

박지성 5 30

손흥민 3 33

* */

SELECT NAME, ID, LEAD(AGE,1) OVER (ORDER BY AGE )FROM TEST1 t ;

/*LEAD: 지정된 행의 값을 가져온다 기본 1

전예진 2 28

조성호 1 30

전수빈 4 33

박지성 5 33

손흥민 3 NULL

* */

SELECT ID , NAME, CUME_DIST() OVER(ORDER BY AGE)FROM TEST1 t ; --CUME_DIST: 작거나 같은 건수에 대한 누적백분율

SELECT ID , NAME, PERCENT_RANK() OVER(ORDER BY AGE)FROM TEST1 t ; --PERCENT_RANK: 파티션에서 제일 먼저 나온게 0,제일 늦게 나온게 1로 백분율

SELECT ID , NAME, NTILE(2) OVER(ORDER BY AGE)FROM TEST1 t ; --NTILE(N): N등분해서 조회

SELECT ID, NAME, RATIO_TO_REPORT(AGE) OVER () FROM TEST1 t; --RATIO_TO_REPORT는 OVER()로 둬야됨 / 백분율 소주점까지

 

SELECT * FROM TEST1 t

WHERE ROWNUM <=1 ;

 

SELECT * FROM(SELECT ID, NAME, ROWNUM LIST FROM TEST1 t) --ROWNUM: 별칭 사용 필수

WHERE LIST <=5;

 

SELECT * FROM(SELECT ID, NAME, ROWNUM LIST FROM TEST1 t) --ROWNUM: 별칭 사용 필수

WHERE LIST BETWEEN 3 AND 5;

 

SELECT ROWID, ID, NAME FROM TEST1 t;

--ROWID : AAASs/AAHAAAAFbAAA 오상블데

--1-6 오트젝트 번호

--7-9 상대파일 번호

--10-15 블록 번호

--16-18 데이터 번호

 

 

--P178 게층형조회

CREATE TABLE EMP(

EMPNO NUMBER(10) PRIMARY KEY, --직원 번호

ENAME VARCHAR2(20), --직원 이름

DEPTNO NUMBER(10), --부서 번호

MGR NUMBER(10), --직원 상사

JOB VARCHAR2(20), --직원 직책

SAL NUMBER(10) --직원 급여

);

 

INSERT INTO EMP VALUES(1000, 'TEST1', 20, NULL, 'CLERK', 800);

INSERT INTO EMP VALUES(1001, 'TEST2', 30, 1000, 'SALESMAN', 1600);

INSERT INTO EMP VALUES(1002, 'TEST3', 30, 1000, 'SALESMAN', 1250);

INSERT INTO EMP VALUES(1003, 'TEST4', 20, 1000, 'MANAGER', 2975);

INSERT INTO EMP VALUES(1004, 'TEST5', 30, 1000, 'SALESMAN', 1250);

INSERT INTO EMP VALUES(1005, 'TEST6', 30, 1001, 'MANAGER', 2850);

INSERT INTO EMP VALUES(1006, 'TEST7', 10, 1001, 'MANAGER', 2450);

INSERT INTO EMP VALUES(1007, 'TEST8', 20, 1006, 'ANALYST', 3000);

INSERT INTO EMP VALUES(1008, 'TEST9', 30, 1006, 'PRESIDENT', 5000);

INSERT INTO EMP VALUES(1009, 'TESTIO', 30, 1002, 'SALESMAN', 1500);

INSERT INTO EMP VALUES(1010, 'TESTII', 20, 1002, 'CLERK', 1100);

INSERT INTO EMP VALUES(1011, 'TEST12', 30, 1001, 'CLERK', 950);

INSERT INTO EMP VALUES(1012, 'TEST13', 20, 1000, 'ANALYST', 3000);

INSERT INTO EMP VALUES(1013, 'TEST14', 10, 1000, 'CLERK', 1300);

 

 

--START WITH: 시작조건

SELECT MAX(LEVEL) --MAX(LEVEL): LEVEL: 트리구조의 깊이

FROM EMP

START WITH MGR IS NULL

CONNECT BY PRIOR EMPNO = MGR; --CONNECT BY PRIOR: 조인조건 / 자식=부모: 순방향

--4

 

 

 

SELECT LPAD(' ', 4*(LEVEL-1)) || EMPNO AS LPAD,--LPAD(' ', 4*(LEVEL-1)) : 문자열PADDING

CONNECT_BY_ROOT(EMPNO) AS ROOT, --CONNECT_BY_ROOT(컬럼): 가장 상위 노드값 반환

CONNECT_BY_ISLEAF AS ISLEAF, --CONNECT_BY_ISLEAF : 자식노드가 있으면 0 없으면 1

CONNECT_BY_ISCYCLE AS ISCYCL --CONNECT_BY_ISCYCLE: 순환구조 발생지점 표시

FROM EMP

START WITH MGR IS NULL

CONNECT BY NOCYCLE PRIOR EMPNO = MGR --NOCYCLE : *위치* 순환구조 발생지점까지만 /조회된거 다시조회 : CYCLE

ORDER SIBLINGS BY EMPNO; --ORDER SIBLINGS BY : LEVEL이 같은 형제 노드 사이에서 정렬

 

SELECT * FROM (SELECT DEPTNO , JOB, SAL FROM EMP)

PIVOT(AVG(SAL) FOR JOB IN ( 'SALESMAN'AS SALESMAN , 'MANAGER'AS MANAGER ,

'ANALYST'AS ANALYST ,'PRESIDENT'AS PRESIDENT ,

'CLERK'AS CLERK ));

--PIVOT은 값 하나하나(SALESMAN이런거)를 컬럼으로 바꾸는것이다. 즉, 행->열로 만드는것

 

--PIVOT된것을 테이블로 바꿔보고 다시 UNPIVOT해보자

CREATE TABLE PIVOT_EMP AS SELECT * FROM (SELECT DEPTNO , JOB, SAL FROM EMP)

PIVOT(AVG(SAL) FOR JOB IN ( 'SALESMAN'AS SALESMAN , 'MANAGER'AS MANAGER ,

'ANALYST'AS ANALYST ,'PRESIDENT'AS PRESIDENT ,

'CLERK'AS CLERK ));

SELECT * FROM PIVOT_EMP

 

--이제UNPIVOT해보자

SELECT * FROM PIVOT_EMP

UNPIVOT (SAL FOR JOB IN (SALESMAN, MANAGER, ANALYST, PRESIDENT, CLERK));

 

INSERT INTO EMP VALUES(1013, 'TEST14', 10, 1000, 'CLERK', 1300);

 

UPDATE EMP SET ENAME = 'TEST15'

WHERE EMPNO =1013;

UPDATE EMP SET ENAME = 'TEST14'

WHERE EMPNO =1013;

 

DELETE FROM EMP

WHERE EMPNO=1013;

 

TRUNCATE TABLE 테이블명;

SELECT * FROM EMP

 

COMMIT;

ROLLBACK;

 

SAVEPOINT FIRSTSAVE;

INSERT INTO EMP VALUES(1014, 'TEST15', 10, 1000, 'CLERK', 1300);

SAVEPOINT SECONDSAVE;

ROLLBACK TO FIRSTSAVE;

SELECT * FROM EMP

 

CREATE TABLE JYJEMP(

EMPNO NUMBER(10) , --직원 번호

ENAME VARCHAR2(20), --직원 이름

DEPTNO NUMBER(10), --부서 번호

MGR NUMBER(10), --직원 상사

JOB VARCHAR2(20), --직원 직책

SAL NUMBER(10), --직원 급여

JOBDATE DATE DEFAULT SYSDATE, --입사 날짜

--EMPNO NUMBER(10) PRIMARY KEY 이렇게 바로 제약조건을 달거나

CONSTRAINT EMPNUMBER PRIMARY KEY (EMPNO), --제약조건의 이름을 지어줄거면 CONSTRAINT 이름

CONSTRAINT JYJDEPNO FOREIGN KEY (DEPTNO) REFERENCES EMP(DEPTNO) --외래키가 참조하는 다른테이블의 컬럼은 PK여야한다

--> EMP의 DEPTNO에 PK걸기

);

--EMP의 DEPTNO에 PK걸기 (제약조건 추가)

ALTER TABLE EMP

ADD PRIMARY KEY (DEPTNO);

-->불가능 하다 . 테이블 1개에 PK1개이다.

--EMP의 PK제약조건을 삭제하자

ALTER TABLE EMP

DROP PRIMARY KEY;

--그래도 DEPTNO가 PK가 되진않는다. 왜 ? 중복값이 있어서! //////PK: 중복안됨,NULL안됨

--원래대로 돌리자..(EMPNO가 PK)

ALTER TABLE EMP

ADD PRIMARY KEY (EMPNO);

 

SELECT *FROM EMP e ;

 

DROP TABLE JYJEMP;

 

CREATE TABLE DEPT --부서테이블

( DEPTNO VARCHAR2(4) PRIMARY KEY,

DEPTNAME VARCHAR2(20)

);

INSERT INTO DEPT VALUES('1000','인사팀');

INSERT INTO DEPT VALUES('1001','총무팀');

SELECT * FROM DEPT d ;

 

--새로운 마스터 테이블을 만들었으니 다시 해보자

DROP TABLE EMP;

 

CREATE TABLE EMP(

EMPNO NUMBER(10) , --직원 번호

ENAME VARCHAR2(20), --직원 이름

SAL NUMBER(10,2) DEFAULT 0,

DEPTNO VARCHAR2(4),

CREATEDATE DATE DEFAULT SYSDATE,

CONSTRAINT E_PK PRIMARY KEY(EMPNO),

CONSTRAINT D_FK FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO) ON DELETE CASCADE);

--ON DELETE CASCADE: DEPT의 DEPTNO가 사라지면 EMP의 DEPTNO도 삭제된다

--> 참조 무결성 지켜짐 굳

INSERT INTO EMP VALUES(100,'임베스트',1000,'1000',SYSDATE);

INSERT INTO EMP VALUES(101,'을지문덕',2000,'1001',SYSDATE);

CREATE VIEW T_EMP AS SELECT * FROM EMP e;

--cmd에서. 뷰생성권한 주기

--grant create view to c##idev;

SELECT * FROM T_EMP;

DROP VIEW T_EMP;

GRANT ALL ON EMP TO c##idev;

GRANT SELECT ON EMP TO c##idev WITH GRANT OPTION; --WITH GRANT OPTION: A->B, B->C REVOKE하면 모두 취소

GRANT SELECT ON EMP TO c##idev WITH ADMIN OPTION;--WITH ADMIN OPTION: A->B, B->C REVOKE하면 B만취소

REVOKE ALL ON EMP TO c##idev;