＼(^o^)／

생각보다 많이 어렵다고느껴져서 카페들어가서 공부하다보니깐 그때 막판에 알게된게 많은것같다 합격해땅
영어공부 시간들이기 하자 ㅠ! 꾸준히

토요일할거
학원에서 프린트 된거 +요약본 읽기
그다음 기출사이트 가서 기출남은거 풀기
노랭이 체크문제들 한번더 풀기 (1과목은 다풀까…고민 아마 시간없겟지,…)

반정규화를 하면 데이터 무결성을 해친다.
따라서, 성능을 위한 반정규화를 고려한다>>이말은 맞지않은 문장임. 성능을위함과는 무관.‘

엔터티- 업무에서 관리해야 하는 데이터의 집합/ 개념.사건.장소등의 명사이다.
유일한 식별자, 2개이상의 인스턴스, 반드시 속성을 가져야 한다.
다른 엔터티와 최소 한개 이상의 관계를 가져야 함.

⭐️엔터티 구분
1. 유무형에 따른구분  - 유형/사건/개념 엔터티
2. 발생시점에 따른구분 - 기본/중심/행위 엔터티

속성 : 엔터티가 가지는 항목, 더이상 분리되지 않는 단위임. 인스턴스의 구성요소.  (기본속성/파생속성/설계속성)
인스턴스 : 데이터베이스에 저장된 데이터내용의 전체집합.

선택참여 관계는 O 동그라미 표시임. 선택적관계이면 동그라미

User 삭제시 user에 속한 모든 object 같이 삭제할 명령어. :  CASCADE
>>ALTER DROP USER user_name CASCADE

⭐️메인쿼리는 서브쿼리에 있는 컬럼을 자유롭게 사용할수 있다. !!!!!

오늘날짜+1일 구하는 문제 > DATE 타입을 문자열로 변환 필요함.  DATE (sysdate) 들어가는게 안맞다고했음 해설찾으면 추가메모달아놓기.
ㅇㅇTO_CHAR(수or날짜, [포맷-생략가능])>>> SELECT TO_CHAR(1234) FROM DUAL;/ SELECT TO_CHAR(SYSDATE, ‘yyyymmdd hh24miss’) FROM DUAL;
ㅇㅇTO_DATE(문자열, 포맷) >>포맷형식의 문자형의 데이터를 날짜형으로 바꿔준다.  > SELECT TO_DATE(‘20220602’,’yyyymmdd’) FROM DUAL; 따라서 DATE 타입인 sysdate는 TO_DATE에 들어갈수가 없는것임.  ex) TO_DATE(‘2022013120’, ‘YYYYMMDDHH24’) > 2022/01/31 20:00:00
답은 :  SELECT TO_CHAR(SYSDATE+1, ‘yyyymmdd’) FROM DUAL;

FETCH (읽어오기) 위해 해야할것은 >> CURSOR OPEN 선언해야함.
OPEN > TETCH > CLOSE  순서임.
CURSOR란 ? DB의 연결포인트, 접점.

RANDOM ACCESS 로 인한 부하로 성능이 지연된다 > > NESTED LOOP JOIN 방식.

WHERE EXISTS (서브쿼리문 - SELECT a FROM TABLE WHERE 조건) >> 조건문을 만족(EXISTS+ 서브쿼리문)면  출력해라

ROWNUM 사용할때는 ROWNUM=1 제외하고, 다른 숫자로 등호표시해서 출력할수 없는 조건임
Ex) ROWNUM >=2 (o) ,
ROWNUM=2 (x)

CHAR - 비교할떄 각각의 길이가 다르면 짧은 쪽에 스페이스를 추가하여 같은 값으로 판단한다.

같은 값인데, 공백 등으로 인해 길미나 서로 다른경우 다른값으로 판단하는 것은 VARCHAR(가변길이문자형 : 입력할 크기만큼 할당) 으로 비교하는 경우이다. 필요에의해 공백등 길이를 할당시켜놓은것이기 떄문에 비교할때도 자릿수 유효하게 비교하기.
CHAR은 자릿수맞춰서 비교

따라서
CHAR(고정길이문자형( 은 비교시 서로 길이가 다른 경우 다른 내용으로 판단한다( x)> 두개중 짧은 쪽에 스페이스 추가하여 비교.

메인쿼리의 값을 서브쿼리에서 받아서 비교하는것 >>>>> 상호연관 서브쿼리(CORRELATED 서브쿼리)

DDL  : CREATE ALTER RENAME DROP TRUNCATE
ALTER 예시
ALTER TABLE TEACHER ADD BIRTHDAY VARCHAR(8);
ALTER TABLE TEACHER DROP COULMN ADDRESS;
⭐️ALTER TABLE TEACHER MODIFY (BIRTHDAY VARCHAR2(8) DEFUALT ‘99999999’ NOT NULL); >>>
⭐️MODIFY 다움에 바로 (컬럼명, 변경할데이터유형); ㄱ바로 괄호나오는애는 MODIFY.

ALTER TABLE TEACHER RENAME COLUMN MOBILE_NO TO HP_NO;

제약조건추가
⭐️ALTER TABLE TEACHER ADD CONSTRAINT TEACHER_PK PRIMARY KEY (SUBJECT_ID) REFERENCES SUBJECT(SUBJECT_ID);
>>CREATE TABLE 구문 안에 명시할때는  ,ADD CONSTRAINT TEACHER _PK PRIMARY KEY (SUBJECT_ID)) ; 이런식으로 포함시켜주면됨.  <<이거맞나..???까먹음 노랭이에 있었는데;

DROP TABLE SUBJECT CASCADE CONSTRAINT ; >> DROP 다음에 바로 테이블명 나오면 된다(FROM 들어가면 틀리다. DROP TABLE 하나만 나옴. +삭제하면서 제약조건도 함께 삭제한다는 의미.


DML : INSERT DELETE UPDATE
DCL : GRANT REVOKE

TCL : ROLLBACK SAVEPOINT COMMIT

⭐️컬럼 속성 변경시 >>>ALTER TABLE MENU MODIFY (컬럼명 VARCHAR20 not null);
⭐️제약조건추가 >>> ALTER TABLE MENU ADD CONSTRAINT MOBILE_PK PRIMARY KEY (COL1)

트랜잭션 구조 > 원고지일 원자성고립성지속성일관성

그룹함수 > 데이터를 GRUOP BY 하여 나타낼수있는 데이터를 구하는 함수이다. 역할에 다라 집계함수와 소계(총계함수로 나눌수있음)(
1.집계함수 : COUNT SUM AGE MAX MIN 등
2.소계(총계)함수 : ROLLUP CUBE GROUPIN SETS
CUBE 함수와 GROUPING SETS 함수는 인수의 순서가 바뀌어도 같은 결과를 출력한다.

GROUPING 함수는 위 소계함수와 함꼐쓰이면서 소계를 나타내는 ROW를 구분할수이ㅏㅆ게 해준다.
>주어진 인자가 null일 경우 1을 리턴.
원랜 그룹핑의 기준이 되는 칼럼 제외하고는 모두 NULL값으로 표현되었지만 GROUPING 함수를 이용하면 원하는 위치에 원하는 텍스트 출력가능.
CASE GROUPING(ORDER_DT) WHEN 1 ‘total’ ELSE ORDER_DT
END AS ORDER_DT
오라클은 DECODE문으로 CASE 문을 대체할수있다.

윈도우함수 구조 >>>내가 보고싶은값을 창문처럼 틀안에 가둬놓고 계산한거만 보겠다.
OVER 키워드와 함꼐 사용. 결과에 대한 함수처리이기 때문에 결과건수는 줄지않는다. !!
순위함수, 집계함수, 행순서함수, 비율함수 이렇게 있음.
순위함수 RANK, DENSE_RANK, ROW_NUMBER
집계함수 SUM, MAX, MIN, AVG, COUNT
행순서함수 FIRST_VALUE, LAST_VALUE, LAG, LEAD
비율함수 CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT

비율함수 CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT
RATIO_TO_REPORT : 파티션별 합계에서. 차지하는 비율
PERCENT_RANK : 파티션별 윈도우에서 처음~끝값 0~1 사이, 현재 행이 위지하는 백분위 순위 값을 구하는 함수.
CUME_DIST : 현재행보다 작거나같은건수에 대한 누적백분율 구하는 함수
NTILE : 주어진 수만큼 행들을 n등분한후 현재행에 해당하는 등급을 구하는 함수. (1,2,3,4쿼터)

TOP_N 추출하기위한 몇가지 방식
1. ROWNUM>>> 항상 <조건이나 <=조건으로 사용해야 한다.  (슈도 컬럼).  WHERE절 ROWNUM 조건뒤에 ORDER BY있게되면 맨나중에 실행되면서 이미 채번된 행값들이 뒤섞이니 주의
2. 윈도우 함수의 순위함수 : SELECT절 스칼라서브쿼리를 활용해서 정렬된 데이터(서브쿼리안의 order by)+순위(row_number등() over(~~)) 뽑아내고, 메인쿼리절의 where절에 줄번호가 몇까지를 뽑아낼지 작성하면됨.

⭐️ top-n 쿼리 작성시 주의사항
ORDER BY절이 WHERE절보다 나중에 수행되기 떄문에 ROWNUM으로 순서를 지정할 때에는 ORDER BY절 바깥에서 해야한다.
ROWNUM과 ORDER BY를 같은 단락에서 작성하게 될경우 ROWNUM우로 랜덤하게 순서가지정된후 ORDER BY로 정렬되기 떄문임.

SELECT 윈도우함수(인수) OVER (PARTITION BY 칼럼 ORDER BY 칼럼명 WINDOWIN절(범위지정) >> order by뒤에 범위지정하는 Windowing절은 종류가 rows/range between a and b 가 있다. >> 집계대상이 되는 레코드 범위를 지정할수있다
FROM 테이블명;

트랜잭션특징: 원자성/ 고립성/ 지속성/ 일관성

제약조건(CONSTRAINT) - 무결성 유지를 위함.
>>Primary key, Unique key, not null, check, Foreign key.

보안성을 위한다 설명이 나오면 >> view와 관련된 설명임. 다른거아님
view특징 : 특립성, 편리성, 보안성

⭐️셀프조인은 반드시 ALIAS를 표시해주어야 한다. 대중소분류
FROM CATEGORY A, CATEGORY B, CATEGORY C

⭐️날짜함수중에 EXTRACT
>>SELECT EXTRACT(YEAR FROM SYSDATE) AS YEAR 이런식으로씀.

IN 연산자의 경우 서브쿼리 결과를 전부 구하고 비교를 시작
반대로. , EXISTS는 단 하나라도 결과를 발견하면 즉시 처리(리턴)한다 -오로지 존재의 여부만 봄 — 따라서 EXISTS가 속도가 빠르다.
>>>추가설명
⭐️EXISTS는 하위쿼리에 레코드가 있는지 테스트하는데 사용한다. 하위쿼리가 하나이상의 레코드를 반환하는 경우 TRUE, 아니면 FALSE
⭐️IN 안에 (1, NULL) 있어도 NULL은 비교연산은 수행하지 않는다. 1만 있는지 비교.

SELECT-FROM절에서 차집합 EXCEPT (오라클 MINUS) 표현한거는
>>>Where절 조건문에 NOT EXISTS, NOT IN 으로 대체가능

NO-CYCLE 옵션 : 중복루프돌지않기위함.

SELECT : 스칼라서브쿼리
FROM : 인라인뷰
WHERE : 서브쿼리

SELECT : 스칼라서브쿼리
>한행,한컬럼만을 반환하는 서브쿼리, 여러행반환하면 에러난다 문제에나옴.
FROM : 인라인뷰
>서브쿼리가 from절에나와서 동적으로 생성된 테이블인것처럼 사용됨. sql이 실행할때만 임시적으로 생성되는뷰임(동적인뷰) , 데이터베이스에 정보 저장되지 않음.
WHERE, HAVING절 : 서브쿼리
>서브쿼리 그룹함수와 함께 사용될때 그룹핑된 결과에 대해 부가적인 조건을 줄때 사용.
>ex) HAVING COUNT(CASE WHEN B, 동의여부 = ‘N’ THEN 0
ELSE NULL END).  >= 1
  ORDER BY A.회원번호
> 동의여부 N인 애들의 갯수(N아닌것들은 NULL처리되었으므로 카운팅되지않음) 가 1보다 큰애들만 데리고와라.

관계의 표기법(엔터티간의 관계)// 관계명,관계차수,관계선택사양

CBO 비용기반 옵티마이저
B tree 구조는 관계형 데이터베이스에서 가장 많이 사용되는 인덱스.
인덱스는 오로지 조회만을 위한 것이다.
삽입,삭제,갱신의 경우 오히려 부하를 가중한다.

⭐️NESTED LOOP JOIN은 OLPT목록처리업무에 많이사용된다 (집계업무x)
⭐️HASH/ SORT MERGE JOIN은 DW등의 데이터 집계업무에 많이 사용되는 조인기법이다.

⭐️정규화가 잘되어있으면 입력/수정/삭제성능 향상
⭐️반정규화 > 조회의 성능이 향상 , 데이터의 무결성을 해친다

⭐️정규화 > 용량산정 > 트랜잭션유형파악 > 반정규화 > 이력모델/pk/fk/슈퍼서브조정 > 성능관점에서 데이터모델검증

⭐️로우체이닝 : 로우길이가 너무길어서 데이터블록 하나에 데이터가 모두 저장되지 않고 두개이상 저장된다.
⭐️로우 마이그레이션 : 저장공간이없어서 다른블록으로 ROW 옮기는현상

대량 데이터 저장으로 인한 성능
테이블 파티션
RANGE PARTITON - 값의 범위를 기준으로 파트션을 나눠 저장. (매출액 기준)
LIST PARTITION - 특정 값 기준으로 분할( 100,250일때 각각 다른 데이터파일에 저장, 컬럼기준으로 다른데다가 저장 등)
HAS PARTITION - 해시함수 사용하여 분할하고 관리하는방식
“파티셔닝“

⭐️순수관계연산자 : SELECT PROJECT JOIN DIVIDE //// UPDATE(X)

⭐️USING 조건절을 이용한 EQUI JOIN에서도 (JOIN ~ USING)
NATURAL JOIN과 마찬가지로 ALIAS와 테이블 이름과 같은 접두사를 붙일수 없다 -SYNTAX 에러!!
기출에 있었던거같음. USING조건절인가 NATURAL JOIN에 ALIAS붙어있으면 틀린거다.

⭐️WINDOW 함수를 사용하지 않는다 > SELF JOIN 해라
EXISTS>IN 대체불가

비연관 서브쿼리 > 주로 메인쿼리에 값 제공목적으로 사용
메인쿼리의 결과가 서브쿼리로 제공될수도 있고 서브쿼리의 결과가 메인쿼리로 제공될수도 있음.

WHERE 조건절의 데이터 찾기위해서는 SELECT권한이 필요.

트리거 - 자동실행 - rollback 불가
프로시저 - 명령어로실행 -  rollback 가능

Exists 절은 실행계획상에 주로 SEMI JOIN 으로 나타낸다
인덱스가 있는경우 NESTED LOOP JOIN 사용 (HASH JOIN 아님)
NESTED LOOP SEMI JOIN

인덱스연관, OLTP, 주로 랜덤방식 (좁은범위)> NL조인
랜덤엑세스 부담되는 넓은범위를 스캔할때 > SORT MERGE JOIN (임시영역을 쓰므로 성능이 떨어질수 있다.)

CONCAT (문자열1, 문자열2) 문자열결합
SIGN(숫자) : 양수면 1 ,0 , 음수면 -1

⭐️집계함수는 where절에 올수없다.
having절에서는 집계함수를 사용하여 조건표시 가능.

ROLLBACK DROP을 되돌릴수 없다 : auto commit이 되었다는 뜼.
SQL SERVER 는 auto commit
오라클 기준은 auto commit X >commit 하기전에 되돌릴수 있다는 뜻 (?)

DECODE(값1,값2, 참일떄출력값, 거짓일댸출력값)
CASE WHEN 조건 THEN 조건이 참일때 결과 ELSE 거짓일떄 결과 END

임시테이블 만드는 구문. WITH
WITH 테이블이름 AS(SELECT * FROM C_INF WHERE NAME LIKE ‘%a%’)
With 구문은
서브쿼리를 사용해서 임시테이블이나 뷰처럼 사용가능.
별칭 지정가능
인라인뷰나 임시테이블로 판단된다.

⭐️NVL 함수 사용시 NVL(COL1, 대체값) >> 컬럼에 등렁가는 두개가 데이터 타입이 같아야 한다. 왜냐면 컬럼의 데이터구조(DDL로 정해진 데이터구조)는 변하지않고 대체값만 들어가는거기 때문에.

NVL2(COL1, 결과1(false일떄), 결과2(true))

GROUP BY 할떄, 오라클에서는 NULL 포함해서 그룹핑해준다!!
SQL 에서는 NULL 제외하고 그룹핑 , 카운팅하기떄문에 NULL 포함시켜서 그룹핑,카운팅 하고싶므면
>SELECT NVL(성별,‘N’) AS gender, COUNT(회원코드) cnt
FROM C_INFO
GROUP BY NVL(성별,’N’) << group by 내에서는 ALIAS 불가!

ROWID > 구분할수있는 유일한 값, 해당 데이터가 어떤 데이터 파일상에서 어느블록에 저장되어있는지 알려준다.
ROWID 번호는 데이터 블록에 데이터가 저장된 순서이다.
테이블에 데이터를 입력하면 자동으로 생성된다.

where 에서 조회되는 행 수를 제한할때 > where ROWNUM=1 (오라클에서 조회된 행이 몇번째행인지 부여해주는것)
SQL server에서는 select top(1) from c_info;

Rollup cube grouping sets 모두 일반 그룹함수로 동일한 결과를 추출할수 있다 (o)

ANSI표준쿼리 : JOIN명시형
from A INNER JOIN B~ ON.  (~JOIN ~ ON 이 우선임(
>>> A,B Where a.회원코드=b.회원코드 (컴마랑 where조건문으로 바꿔쓸수있음)


관련된 두 테이블에 적어도 하나의 공통속성이 있을때 적용할수있는 결합방식 > Join
조회대상이되는 컬럼수가 같고 각 컬럼의 데이터타입 등 속성이 동일할때 적용할수있는 결합방식 , 레코드간의 중복이 제거된 결과가 반환된다 > UNION

⭐️Join 뒤에 on 조건문 없으면 cross join이다/ key값이 없이 join하면 2개의 테이블에 대해 카테시안곱 발생. 비용많이듬.

<서브쿼리 문제>
서브쿼리에서는 정렬을 수행하는 order by를 사용할수있다 (x) > 서브쿼리에서는 order by 사용할수없다. 어차피 서브쿼리는 진짜로 보고자하는 값이 아니기때문에 정렬의 의미가 없음.
여러행 반환하는 서브쿼리는 다중행 연산자를 사용해야 한다.
메인쿼리에서 서브쿼리의 컬럼을 사용할수있다(o)
<> 서브쿼리 내부에서는 메인쿼리의 컬럼을 사용할수 있다 . 서로가능
but 메인쿼리에서는 select되지 않은 서브쿼리의 컬럼은 사용할수 없음.
메인쿼리와 서브쿼리의 결과가 모두 동일할때 참이되는 다중행 연산자는 ? : ALL

옵티마이저 : sql 실행계획을 수립하고, sql을 실행하는 데이터베이스 관리시스템의 소프트웨어, 어떻게 실행하느냐에 따라 성능이 달라진다. (소요시간,자원사용량 등)
sql문작성 > parsing(문법검사,구문분석) > 옵티마이저(비용기반,규칙기반) > 실행계획(Plan_table저장) > sql실행

하나의 테이블에 여러개의 인덱스를 생성할수있고, 하나의 인덱스는 여러 컬럼으로 구성할수있다(o)
규칙기반 옵티마이저에서는 일반적으로 rowid를 기반으로 스캔이 가장 높은 우선순위를 가진다. (전체테이블스캔x)
인덱스는 내림차순으로 생성 및 정렬된다 (o) (오름차순 아님)
모든인덱스는 중복데이터 입력이 불가하다 (x) >> unique속성을 가진 인덱스가 아니라면 중복데이터 입력 가능하다.

파티션테이블은 파티션에 대해 인덱스를 생성할수있다 (x)
>>파티션 키에 대해 인덱스를 생성가능하며, 그경우 해당 인덱스를 global인덱스 라고부른다.

인덱스의 수는 데이터의 입력,삭제,수정에 영향을 미친다. 증가할경우 느려질수있음
인덱스 종류는 순차인덱스, 비트맵, 결합인덱스, 클러스터, 해시인덱스

한테이블내에서 연관관계를 가진 두 컬럼간의 조인 : self join
서로 연관된 칼럼이 없을경우 수행하는 조인 : cross join

Nested loop join
선형테이블(외부테이블,driving table)
순차적
random access
Index
OLTP

Sort merge join
정렬
임시디스크
equi join non-equi join 모두가능

hash join
작은 테이블이 hash메모리에 로딩
시스템자원 최대한활용
equi join메서만 가능
index를 사용하지 않음 >hash

sql38회기출강의 정리 유툽

ㅇㅇ계층형 질의
start with로 시작 - 루트노드 (level 1부터시작)
리프노드는 끝에 위치하므로 자식노드를 가지지 않는다.
레벨이 같은 애들은 형제노드 관계 라고 한다.
start with ~~ 루트노드(시작점을 지정)의 조건
connect by <nocycle>~~~ 상하계층이 이루어지는 조건 (노사이클>뱅글뱅글 도는경우가 있어서 조직도가 계속 만들어지는경우가 있는데, 이렇게되면 오류가 생기고 문제가 생길수있음. 계속 사이클이 생기는 것을 방지)
<order by siblings by ~~> 어떤 기준을 가지고 정렬을 할것이다

자식컬럼과 부모컬럼 찾아내기
mgr empno
connect by  prior 자식컬럼 =.       부모컬럼 :부모에서 자식으로 트리 그리기 : 순방향전개 (프 자 = 부)
Connect by           자식컬럼 = prior 부모컬럼: 자식에서 부모로 트리 그리기 : 역방향전개
Conncet by prior 부모컬럼 =          자식컬럼 : 자식에서 부모로 트리그리기 : 역방향전개
connect by.         부모컬럼 = prior 자식컬럼 : 부모에서 자식으로 트리 그리기 : 순방향전개
>>>>>현재 보고있는 개체의 칼럼에 prior적용하는것이다
>>connect by col1 = prior col2 : 다음 레벨로 지금 보고있는 칼럼의 col2와 일치하는 col1값을 넣어준다. .

dcl : data user conrtrol language
Ddl : data set definition language  >>truncate

Tcl. : transaction control language >>트랜잭션을 관리하고 제어ㅣ하는 랭귀지
논리적으로 분리할수없는 dml의 모임 - 트랜잭션
commit rollback savepoint

Truncate > ddl
모든행을 삭제하고 디스크사용량 초기화하며 데이터의 틀만 유지.
일부 dml의 성격을 가지지만 truncate는 ddl로 취급한다!! 헷갈리지말기!!

delete - 안에 값 ,개체 다 지우고 틀만 냄겨놈, 용량도 보존. 디스크 용량 잡아먹고있음 (dml)
truncate(ddl)- 개체 삭제는 하고 용량은 잡아먹지않아. 구조자체는 유지.
drop - 테이블 완전삭제 (ddl)

commit / auto commit
Dml은 auto commit이 안되지만
ddl은 사용하자마자 auto commit이 된다!!  

Count distinct- null 값 제외하고 행의수 출력
Is not null 을 써야함.
Null값은 부등호 쓸수 없음

Count 집계함수 정리- 차이점 체크!!!
Count(*) count(1), count(2)…. > 전체 테이블의 행의 수를 출력한다 null값을 포함해서 행의 수를 계산.
Count(컬럼명) > null값을 제외한 행의수를 계산한다.
Count(distinct 칼럼) > 칼럼이 가지고있는 고유의 종류의 수를 계산한다.null값 제외.

Null값은 반드시 sql연산자 “is null “과 “is not null”로 해결한다. 그 이외에는 불가능. ‘
where 절에서는 객체의 참/거짓을 판단하는거임. 여기에 꼭 컬럼이 들어갈필요 없음. 개체의 평가를 내리는것임.

null 관련함수
Nvl(a,b) > null value 의 약자 >> 인수는 반드시 2개만 넣어줘야함.
a가 null이면 b를넣어준다 아니면 a —오라클
is null(a,b) 도 같음 (sql server)
nullif(a,b) > ab가 같다면 null, 아니면 a
null if (a = b) but then a
Coalesce (a,b,c…) null값 아닌 최초의 값 출력.

union all/ union
Set-operator를 이용한 결합.  집합류 (교집합,합집합,등등)
1 )위치기반 결합 -변수명 기반 결합이 아니기때문에 각 테이블의 첫번째의 값을 비교해서 작동할수가 있음. 컬럼명이 같지 않아도됨. 위치기준!! 컬럼의 숫자만 같으면 되고 컬럼명은 달라도 됨!!!!
2 ) 중복치 제거 / all이 들어가면 중복제거 안하고 다보여줄수 있음.(중복을 허락한다) 오라클에서만!
결과물의 칼럼은 테이블1의 칼럼명을 따른다. !
복수의 set operatio 의 경우 어떻게 되는가? 92페이지 83번 기억!! 위에서 아래로!!!!! 연산이 끝나고 새로운 연산이 들어가는것임.

Union : 합집합
Intersect : 교집함
Minus(orcale)/ except(sql-server) :차집합 >> not in / not exists
Union all 합집합>중복을 인정한다.

오라클하고 sql언어 차이? 몇개 보기

그룹함수 >>자격검정에 나온 문제 풀어보면 충분히됨.
Rollup> 까다로워, 하나씩 증가하는 형태
group by rollup(a) : 전체 합계, 칼럼a소계
group by rollup(a,b) : 전체합계, 칼럼a소계, 칼럼a,b조합 소계
group by rollup(a,b,c) : 전체합계, 칼럼a소계, 칼럼a,b소계, 칼럼 a,b,c조합별 집계

Cube>> 모든 가능한 것의 조합.의 소계
group by cube(a) : 전체합계, 칼럼a소계
group by cube(a,b) : 전체합계, 칼럼a소계, 칼럼b소계(맨밑), 칼럼a,b소계

>>가능한 모든 조합의 소계및 합계를 생성하기 때문에 시스템에 무리가 갈수있다.

Grouping sets >> 딱 그것만 구함.  전체소계가 없다!라고하면 그룹핑셋츠를 쓴거라고 보면된다(?)
group by grouping sets(a) : 칼럼a 소계’
Group by groupinbg sets(a,b) : 칼럼 a 소계, 칼럼 b 소계 (새로생김)

Group by dname, rollup(job)
Dname으로 그룹핑하고 이 안에서 롤업이 작동한다. (각 그룹핑 안을 각각 테이블로 보고 연산된다.) >>이럴때 전체 행에대한 합계가 없는상태가 되는것임.

Grouping sets(a,b) >> a,b
Grouping sets( (a,b) , a, () ) >> (a,b) , a , () =. Rollup(a,b)와 같다
grouping sets( a, rollup(b))>> a,b,()

Group by에 선언된 칼럼은 select 에 바로 들어갈수 있고, 선언되지 않은 칼럼들은 그룹함수에 낑겨서 들어가야 한다!그냥 들어갈수가 없다

range between 10000 preceding and 10000 following
116번문제 참고

서브쿼리 개념
쿼리문장 안에 들어가있는 서브문장을 서브쿼리라 함. 괄호안에 집어넣음.
서브쿼리를 포함하는애를 메인쿼리라 함.
구조적인 문제점때문에 서브쿼리를 사용하는 경우가 많음.
where절에는 집계함수를 쓸수 없음 왜? 집계함수는 group by의 영향,지시를 받기 때문에. 집계함수를 쓸라면 그룹바이를 만나던지 필요로 한다.
그룹바이를 확인하기 전에 집계함수가 나왔다? 그럼 안돌아간다.
이런 문제점을 해결하기 위해 서브쿼리로 값만 간단하게 넣어주고자 함.
서브쿼리는 단일행서브쿼리와 다중행서브쿼리 두가지가 있음.
결과가 1건인서브쿼리 (비교연산자 = 사용가능)
/ 결과가 여러개 나올수있는거 empno 결과값이 많은 결과를 내보내는것 > 반드시 !!!! In all any some exists의 비교연산자를 사용하여야 한다!!!  비교연산자 = 이런거 사용할수없음.

> any*(30,40,50).     > 30
<any (30,40,50).      <50
Any는 부등호르 가장 크게만드는 값으로 선택하면 된다.
=any (30,40,50).      In(30,40,50).       A=30 or a=40 or a=50

>all(30,40,50) 가장 작게 만드는 부등호를 선택 !!! 범위가 가장 작아지는 범위를 선택 >50
<all(30,40,50).    <30

다중행 서브쿼리 비교연산자는 단일행 서브쿼리에서 사용가능 in(30), any(30), all(30)
단일행 서브쿼리 비교연산자는 다중행 서브쿼리에서 사용불가능>>> 쓰고싶으면 any all 이런 연산자를 붙여서 위처럼 쓸수있음 >any/(30,40,50)


Un-correlated 단순히 간단한 값을 뽑아내는 목적(비연관) 서브쿼리
서브쿼리가 메인쿼리칼럼을 가지고있지 않은 형태의 서브쿼리
주의! 서브쿼리는 메인쿼리 칼럼 사용 가능. 메인쿼리는 서브쿼리 칼럼 사용 불가. 정적임.단순히 계산을 해결하기위한 방법으로 사용.

correlated(연관) 연관서브쿼리 - 서브쿼리가 메인쿼리칼럼을 포함하는 서브쿼리인데 . 메인쿼리가 먼저 수행되며 서브쿼리에 조건이 맞는지를 확인하는 용도로 사용된다. 동적.
exists서브쿼리는 항상 연관 서브쿼리로 사용된다 .

where라는거는 한명씩 와서 평가를 받는거임.  서브쿼리가 where에 왔을대 사람에 따라서 단일서브쿼리 값이 바뀌는것임.  -연관서브쿼리

인덱스 생성 구문
CREATE INDEX index_name ON table_name(coulmn_name)

Tcl
트랜잭션 > 현실세계에서 어떤 행위가 일어날때 데이터베이스에 반영하기 위해서 나타나는필수적 발생 데이터 베이스의 조작(manipulation.= dml)들.
트랜잭션은 분리할수 없는 개념. 하나으 ㅣ분리될수없는 논리작업을 구성하는 세부적인 연산작업들의 모임을 트랜잭션이라고 한다.
CREATE TABLE 통장( 입금 NUMBER(8), 출금 NUMBER(8) , 잔액(NUMBER(8) );
CREATE TABLE 지갑( 입금 NUMBER(8), 출금 NUMBER(8) , 잔액(NUMBER(8) );

이 작업을 쪼갤수없다. 트랜잭션을 분리하는 경우 작업의 의미, 논리가 무너짐.
INSERT INTO 통장 VALUES(500, 0 , 3500)
INSERT INTO 지갑 VALUES(0, 500 , 2500)

트랜잭션의 성질 3개 > 완전성, 0 0 외워서 가기
commit  >. 트랜잭션의 완료, 계약완료, 수정불가 commit 전에는 취소시킬수 있는데 이걸 입력하게되면 회사서버에 등록되고, 앞으로 수정할수없음 반영완료.  (ex . 타인이체)
Rollback. 최신 commit까지 복귀 또는 지정 savepoint까지 복귀/ ddl근처로 돌아가게된다.
Savepoint. 복귀 지점 지정
>>>ddl 문장 실행시 auto commit이 수행된다. 자동으로 반영된다는거임.


Exists
Where -개체평가조건 이 행을 테이블로 뽑아내도 되겠습니까/ 참만 되면 테이블로출력해준다.
exists - 존재 한다 뭔가가 존재하면 참 >> 서브쿼리 내부의 where에 만족하는 어떤임의의 개체가 존재하는가? 존재하면 where조건을 만족시킬수 있다.

dual테이블은 dummy테이블로 누구든 사용할수있는 테이블입니다.
dummy라는 문자열 유형의 칼럼에  ‘x’라는 값이 들어있는 행 1건을 포함하고 있다.  having>1

join은 행 기반으로 움직인다는것 기억!
select 구문에대해 적절하지 않는것
Select 문장에서 projection 행위를 할수있다.(열 ,변수 선택하고 파생시키는 행위0 + selection 행위 : 행을 선택하는 행위
from 절에서는 alias 키워드를 사용할수 없다. (0)
Where에서는 집계함수를 사용할수없다. (0)
order by 에서는 컬럼명과 컬럼의 alias만을 사용할수있다(x) > 행번호도 사용할수있음.

오라클 계층형 쿼리에대해 적절하지않은것
start with 계층구조의 시작점
Order siblings by는 같은 노드간의 형제노드간의 정렬.
order by가 전체 결과의 정렬을 지정하는 구문이다.
순방향전개란 부모노드로부터 자식노드방향으로 전개하는것  부모>자식
prior 자식 = 부모
부모 =prior 자식
prior가 현재 행에 붙는다. 현재 행에대한 이야기다.
루트노드의 level값은 1이다.

함수의 계산방법, null값 처리하는 방법?
함수에서는 null값 제외시키고 계산배제한다.
사칙연산에서는 null값 들어가면 다 null이 되어버림. 모르는값에 뭘 연산해 sun(col1+col2)
Sum(col1,col2) >>오류난다, sum은 인수가 하나이다 ㅠ_ㅠ 컴마 쓸수 없음. 오류남.

()
비교연산자 sql연산자(between a and b / in(a,b,c,d)/ like 형태 escape/ is null )
not
and
or

Cross
Cartesian
Like %


references 테이블(열) on delete cascade >> 참조하겠다 ~를  on 어떤 조건으로 .
delete cascade > 문제생기면 죽이겟다
Delete set null > 문제생기면 null값 주겠다.


varchar 가변길이 문자유형을 의미
Char 고정문자 - 남는공간 띄어쓰기
varchar 가변문자 여기서 띄어쓰기는 의미가 있는거임. 하나의 글자로 인식.
오라클에서는 varchar2 / sql server에서는 varchar 로 쓴다.
numeric - 숫자,  정수실수와같은수.
datetime - 날짜 정보를 해결하기위해서 사용하는 데이터 유형


윈도우함수의 이해-
Select window함수.   ( ) over.  (<행을분할><행을정렬><대상행지정>)
from table ; 윈도우함수 ~에게   ((윈도우함수 지정범위)

window_function over (<partition by 칼럼><order by절><windowing절>)

종류 숙지해두기. ‘


windowing 절 : rows ./ range
Rows
Unbounded preceding 맨윗줄
preceding ; 이전행 (윗방향)위에 위치한 행
current row 현재 행의경우
following 다음행/ 한칸아래행
unbounded following 뒷방향으로  

범위 지정 예제
위와같이 row unbounded preceding 이라고 시작점만 적은 경우 current row까지 자동으로 연산이 된다.

unbounded preeding - current row
Current row - unbounded following

Rows에 대한 예시
rows unbounded preceding > 윈도우 함수의 연산을 맨 위에서부터 현재 행(currunt row)까지
rows unbounded following > 윈도우 한수의 연산을 현재행(current row)에서 맨 아래 행까지 포함해서 계산
rows 1 preceding > 윈도우 함수의 연산은 한칸위 행부터 현재 행(current row )까지 포함해서 계산
rows 2 following > 윈도우 함수의 연산을 현재 행(current row)부터 두 칸 아래 행까지 포함해서 계산.

range > 칼럼의 값을 기준으로 연산에 참여할 행을 선택한다.
range 150 preceding > 현재칼럼의 값을 기준으로 적은 값에서 150 이하로 차이가 나는 행들을 선택적으로 계산
range unbounded preceding > 현재 칼럼의 값(포함하여)을 기준으로 작은 값들을 모두선택하여 계산한다.
range between 150 preceding and 150 following.> 현재 칼럼의 값보다 적은 값에서 150이하로 차이가 나고, 현재 컬럼값 기준으로 큰값에서 150 이상 차이가 나는 행들을 선택한다.
range betwenn unbounced preceding and current row
>현재 칼럼의 값보다 적은 값을 가지는 행부터 현재 행까지 모두 선택한다.
= range unbounded preceding 과 동일하다.

rank > 공동 등수를 수여 , 다음등수를 제거
rank() over (order by sal)
괄호 비워져있음.

dense_rank() over (order bu sal desc)
사람많을때 빽빽할떄 쓰는거임.  중복을 허락함. 언더바 무조건 써야한다 중요!!
Row_number() 등수밀리기 없음. 무조건 등수가 존재한다. 연속하는 등수.

롤

와 벌써 27강 매일 해낸나 대단해

--rank() over(partition by 그룹필드명 order by 정렬필드명)
--dense_rank() over (partition by 그룹필드명 order by 정렬필드명)

SELECT last_name||' '||first_name as 직원이름
    , salary 급여
    , rank() over (order by salary desc) as 순위1 --샐러리 기준으로 급여가 가장높은사람이 1순위로 나오도록/ 중복이있는경우 해당하는 순위가 같이 올라가게 됨. 
    , dense_rank() over (order by salary desc) as 순위2  --중복있어도 연속된 숫자로 순위 매기고 싶을때 
FROM employees ;

 /*WHERE rank() over (order by salary desc) = 2 2순위만 출력하려고 해서 이렇게 where에 윈도우함수를 사용하면 에러남. 
 이렇기때문에 이걸 하위쿼리로 사용하고 추가적으로 상위쿼리를 작성해서 결과를 추출해야 한다. */
 --급여를 가장 많이 받는사람 기준으로 순위 매겨보기 rank함수 사용


select *
from (
            SELECT last_name||' '||first_name as 직원이름
            , salary 급여
            , rank() over (order by salary desc) as 순위1 --
            , dense_rank() over (order by salary desc) as 순위2 
               FROM employees 
                ) A
WHERE 순위1 = 2
;
--4개의 컬럼을 하나의 테이블로 생각해주고, 어떤 작업을 하겠다는 시작점을 바깡트로 나와서 파생으로 만든 내 데이터의 원본을 쓴다고 생각하면 편함.
--하위쿼리에 메인로직이 들어있고, 상위쿼리에서 전체를 호출해주는 개념으로 생각
--파생컬럼을 만들어서 조인도 할수있고 다양한 방법으로 from절 서브쿼리를 확장해서 활용할수있다. 


--사원테이블에서 부서번호기준으로 대상자의 급여순위 구하기
select last_name||' '||first_name as 직원이름
    ,salary 급여
    ,department_id as 부서번호
    ,dense_rank() over(partition by department_id order by salary desc) as 순위2
    from employees;
--이 매긴 순위를 바타응로 사원테이블의 부서별 1순위대상자를 우수사원이라는 컬럼으로 표기할거다. (조인을 해야하고, 서브쿼리를 사용해야 한다)
select A.부서번호, A.직원이름, A.순위
FROM (
select last_name||' '||first_name as 직원이름
    ,salary 급여
    ,department_id as 부서번호
    ,dense_rank() over(partition by department_id order by salary desc) as 순위
    from employees
    ) A
WHERE A.순위 = 1;


--이 부서번호 결과를 가지고 사원테이블의 대상과 결과를 조인해줄거임. !
--EMPLOYEES의 부서ID를 기준으로 부서별 최고 우수사원의 이름을 보여줄거임. JOIN필요
SELECT B.직원이름 AS 우수사원명 , A.*
FROM EMPLOYEES A
        LEFT OUTER JOIN (
                        select A.부서번호, A.직원이름, A.순위
                        FROM (
                        select last_name||' '||first_name as 직원이름
                            ,salary 급여
                            ,department_id as 부서번호
                            ,dense_rank() over(partition by department_id order by salary desc) as 순위
                            from employees
                            ) A
                        WHERE A.순위 = 1
                        ) B
        ON A.DEPARTMENT_ID = B.부서번호
        ;

--서브쿼리에 대한 개념. selet절 서브쿼리 활용
SELECT TO_CHAR(A.DT1,'YYYYMMDD') DATE1 --상위쿼리
FROM (   --하위쿼리
select to_date('20210101','YYYY-MM-DD') DT1
    ,TO_DATE('20220101','YYYY-MM-DD') DT2
    FROM DUAL
    ) A
    ;
    --먼저 SEELCT FROM문으로 컬럼 두개가 만들어졌고 이 부분을 묶어서 테이블에 저장한 테이블이라고 생각.
    --만든 SELECT문을 테이블 하나라고 생각하고 FROM절 안에 넣고 A라고 테이블에 별칭 선언. 
    --=== > 지금 FROM절에 들어가있는데 테이블이 아닌 SELECT문으로 작성한 데이터라고 생각하면됨. (안쪽에 작성되어있기때문에 "하위쿼리"라 부름)
    --여기서 A.DT만 출력하면 A라는테이블의 DT1만 출력하면 FROM절에서 데이터 꺼내온거랑 똑같다. 
    
/*SELECT절의 서브쿼리*/
--전체급여 합산한거를 모든열에 넣어서 쓰고싶음. 
SELECT SUM(SALARY) AS 전체급여 FROM EMPLOYEES; --여기서 표시된 전체급여 합산액을

SELECT EMPLOYEES.*
    , (SELECT SUM(SALARY) AS 전체급여 FROM EMPLOYEES) AS 전체급여 -- *,SALARY 하면 에러남. 결과를 반환할수잇는방법능ㄴ *앞에 테이블명칭을 붙여줘야함.
    --이렇게 셀렉트문으로 계산한 데이터를 그대로 가져오게되면 로우데이터를 건들지않고 구간별비중이나 특별한 계산을 해서 파생컬럼을 만들수있는 장점이 존재. 
    , (SELECT MAX(SALARY) AS 전체급여 FROM EMPLOYEES) AS 최대급여
    , (SELECT MIN(SALARY) AS 전체급여 FROM EMPLOYEES) AS 최소급여
    , (SELECT COUNT(SALARY) AS 전체급여 FROM EMPLOYEES) AS 직원수
    , (SELECT ROUND(AVG(SALARY),2) AS 전체급여 FROM EMPLOYEES) AS 평균급여  --ROUND를 AVG바로앞에서 제어할수도있고 최상단에서 제어할수도있음.
FROM EMPLOYEES;

--셀렉트절의 서브쿼리는 서브쿼리가 셀렉트절로 확장되었다고 해서 스칼럿서브쿼리라고 블림
--출력되는 행수만큼 반복되어 실행된다
--같은 SQL이 반복되어 실행되면서 데이터를 반복해서 출력하므로 성능을 위해 첫번째 행인 100번사원의 데이터를 출력할때 나머지 출력한 데이터를 메모리에 올려놓고
--두번째행부터는메모리에 올려놓은 데이터를 출력하게된다 -- 이러한행위를 서브쿼리캐싱 이라고함.

--이와같은결과를 WINDOW함수를 통해서 출력할수있음.
--윈도우 함수 : 그룹합수 뒤에 OVER라는 함수를 통해 집계하게 됨. 
--OVER 정리 : 쿼리 결과 집합 내의 윈도우 또는 사용자 지정 행 집합을 정의한다. 
SELECT EMPLOYEES.*
    , (SELECT SUM(SALARY) AS 전체급여 FROM EMPLOYEES) AS 전체급여 -- *,SALARY 하면 에러남. 결과를 반환할수잇는방법능ㄴ *앞에 테이블명칭을 붙여줘야함.
    --이렇게 셀렉트문으로 계산한 데이터를 그대로 가져오게되면 로우데이터를 건들지않고 구간별비중이나 특별한 계산을 해서 파생컬럼을 만들수있는 장점이 존재. 
    , (SELECT MAX(SALARY) AS 전체급여 FROM EMPLOYEES) AS 최대급여
    , (SELECT MIN(SALARY) AS 전체급여 FROM EMPLOYEES) AS 최소급여
    , (SELECT COUNT(SALARY) AS 전체급여 FROM EMPLOYEES) AS 직원수
    , (SELECT ROUND(AVG(SALARY),2) AS 전체급여 FROM EMPLOYEES) AS 평균급여  --ROUND를 AVG바로앞에서 제어할수도있고 최상단에서 제어할수도있음.
    ,  SUM(SALARY) OVER () AS 전체급여1 --전체직원의 누적함수를 구할수있음.
    ,  MAX(SALARY) OVER () AS 최대급여1 
    ,  MIN(SALARY) OVER () AS 최소급여1
    ,  COUNT(SALARY) OVER () AS 직원수
    ,  ROUND(AVG(SALARY) OVER (),2) AS 평균급여1    
FROM EMPLOYEES;

--부서별 금액합계를 구했을때 JOIN또는 서브쿼리를 통해서 출력할수도 있지만 윈도우함수로도 출력할수있음. 
SELECT DEPARTMENT_ID, SUM(SALARY) AS 부서별금액
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
;

--기준을 주게되면 기준별 집계의 결과를 확인할수도 있음. PARTITIONN BY 사용. 
--PARTITION BY 정리 : 특정 열을 기준으로 데이터를 나눈다. PARTITION_BY를 지정하지 않으면 쿼리 결과 집합의 모든 행이 단일 그룹으로 취급된다-모두 같은 값을 표시. 
--SUM/MAX/MIN/COUNT/AVG의 파티션을 지정해주면 그 파티션 그룹내의 합산/최대/최소/갯수/평균 값들을 표시해줄수 있다는것. 
--이때 단점은 같은 동선에 GROUPBY가 쓰일수 없다는 점. 
--윈도우함수를 사용해서 상위쿼리로 올라가서 누적집계를 했다면 그룹핑을하거나 다른 작업에 필요한 순간에 상위쿼리로 올라가서 작업을 해야한다는거. 
--누적집계를 구할때는 셀렉트절 서브쿼리보다 윈도우함수를 쓰는게 유연하고 쿼리도 깔끔함. 
--누적집계만 구할수있는건 아니고, 다른테이블의 컬럼과 정보를 조회해서 가져올수도 있음!! 일종의 SELECT 절에서 조인하는거라고 생각할수있음. 
SELECT EMPLOYEES.*
    , (SELECT SUM(SALARY) AS 전체급여 FROM EMPLOYEES) AS 전체급여 -- *,SALARY 하면 에러남. 결과를 반환할수잇는방법능ㄴ *앞에 테이블명칭을 붙여줘야함.
    --이렇게 셀렉트문으로 계산한 데이터를 그대로 가져오게되면 로우데이터를 건들지않고 구간별비중이나 특별한 계산을 해서 파생컬럼을 만들수있는 장점이 존재. 
    , (SELECT MAX(SALARY) AS 전체급여 FROM EMPLOYEES) AS 최대급여
    , (SELECT MIN(SALARY) AS 전체급여 FROM EMPLOYEES) AS 최소급여
    , (SELECT COUNT(SALARY) AS 전체급여 FROM EMPLOYEES) AS 직원수
    , (SELECT ROUND(AVG(SALARY),2) AS 전체급여 FROM EMPLOYEES) AS 평균급여  --ROUND를 AVG바로앞에서 제어할수도있고 최상단에서 제어할수도있음.
    ,  SUM(SALARY) OVER (PARTITION BY DEPARTMENT_ID) AS 전체급여1
    ,  MAX(SALARY) OVER () AS 최대급여1 
    ,  MIN(SALARY) OVER () AS 최소급여1
    ,  COUNT(SALARY) OVER () AS 직원수
    ,  ROUND(AVG(SALARY) OVER (),2) AS 평균급여1    
FROM EMPLOYEES;


--누적집계만 구할수있는건 아니고, 다른테이블의 컬럼과 정보를 조회해서 가져올수도 있음!! 일종의 SELECT 절에서 조인하는거라고 생각할수있음. 
--
SELECT (SELECT SUM(SALARY) AS 전체급여 FROM EMPLOYEES) AS 전체급여 -- *,SALARY 하면 에러남. 결과를 반환할수잇는방법능ㄴ *앞에 테이블명칭을 붙여줘야함.
    --이렇게 셀렉트문으로 계산한 데이터를 그대로 가져오게되면 로우데이터를 건들지않고 구간별비중이나 특별한 계산을 해서 파생컬럼을 만들수있는 장점이 존재. 
    , (SELECT MAX(SALARY) AS 전체급여 FROM EMPLOYEES) AS 최대급여
    , (SELECT MIN(SALARY) AS 전체급여 FROM EMPLOYEES) AS 최소급여
    , (SELECT COUNT(SALARY) AS 전체급여 FROM EMPLOYEES) AS 직원수
    , (SELECT ROUND(AVG(SALARY),2) AS 전체급여 FROM EMPLOYEES) AS 평균급여  --ROUND를 AVG바로앞에서 제어할수도있고 최상단에서 제어할수도있음.
    ,  SUM(SALARY) OVER (PARTITION BY DEPARTMENT_ID) AS 전체급여1
    ,  MAX(SALARY) OVER () AS 최대급여1 
    ,  MIN(SALARY) OVER () AS 최소급여1
    ,  COUNT(SALARY) OVER () AS 직원수
    ,  ROUND(AVG(SALARY) OVER (),2) AS 평균급여1   
    , (SELECT DEPARTMENT_NAME FROM DEPARTMENTS B WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID) 부서명
    --다른테이블과 메인테이블의 셀렉트절을 통해서 조인을통한 가벽적인 정보도 출력이 가능하다. 
FROM EMPLOYEES A;

--21강에서 만들었던 sql문을 테이블ㄹ ㅗ만ㄷ르어줄거임. 
create table cctv설치현황 as
--묶은 지역을 기준으로(group by) 전체설치되어있는 지역별 카메라설치수와  + 각 목적구분별로 설치되어있는 카메라의 수를 구할거임. sum과 사용자정의함수 
SELECT trim(replace(replace(replace(관리기관명,'서울특별시',''),'구청','구'),'경찰서','구')) as 지역 --중복제거,정제,trim사용하여 공백제거
    ,sum(카메라대수) as 전체카메라설치수
    ,sum(CASE WHEN 설치목적구분 = '교통단속' THEN 카메라대수 ELSE 0 END) AS 교통단속
    ,sum(CASE WHEN 설치목적구분 = '교통정보수집' THEN 카메라대수 ELSE 0 END) AS 교통정보수집
    ,sum(CASE WHEN 설치목적구분 = '기타' THEN 카메라대수 ELSE 0 END) AS 기타
    ,sum(CASE WHEN 설치목적구분 = '생활방범' THEN 카메라대수 ELSE 0 END) AS 생활방범
    ,sum(CASE WHEN 설치목적구분 = '시설물관리' THEN 카메라대수 ELSE 0 END) AS 시설물관리
    ,sum(CASE WHEN 설치목적구분 = '쓰레기단속' THEN 카메라대수 ELSE 0 END) AS 쓰레기단속
    ,sum(CASE WHEN 설치목적구분 = '어린이보호' THEN 카메라대수 ELSE 0 END) AS 어린이보호
    ,sum(CASE WHEN 설치목적구분 = '재난재해' THEN 카메라대수 ELSE 0 END) AS 재난재해
    ,sum(CASE WHEN 설치목적구분 = '차량방범' THEN 카메라대수 ELSE 0 END) AS 차량방범
    
FROM cctv
group by  trim(replace(replace(replace(관리기관명,'서울특별시',''),'구청','구'),'경찰서','구'))
;



/* 비상벨 설치현황*/
--지역별 비상벨대수
create table 비상벨설치현황 as
SELECT CASE WHEN INSTR(REPLACE(REPLACE(관리기관명,'서울시',''),'서울특별시',''),'구') > 1 /*'구가 시작하는 최초위치 > 1' */
            THEN TRIM(SUBSTR(REPLACE(REPLACE(관리기관명,'서울시',''),'서울특별시','')
                ,1, INSTR(REPLACE(REPLACE(관리기관명,'서울시',''),'서울특별시',''),'구'))) /*처음부터 구의 위치까지 문자열 가져오기*/
            ELSE TRIM(REPLACE(REPLACE(REPLACE(관리기관명,'서울시',''),'서울특별시',''),'청','')) END AS 지역/*구로 시작하는 경우는 정제*/
            ,COUNT(*) AS 설치대수
            ,SUM(CASE WHEN 설치장소유형 = '가로변' THEN 1 ELSE 0 END) AS 설치위치_가로변
            ,SUM(CASE WHEN 설치장소유형 = '기타' THEN 1 ELSE 0 END) AS 설치위치_기타
            ,SUM(CASE WHEN 설치장소유형 = '화장실' THEN 1 ELSE 0 END) AS 설치위치_화장실
            ,SUM(CASE WHEN 설치장소유형 = '주차장' THEN 1 ELSE 0 END) AS 설치위치_주차장
            ,SUM(CASE WHEN 설치장소유형 = '건물' THEN 1 ELSE 0 END) AS 설치위치_건물
            ,SUM(CASE WHEN 설치장소유형 = '공원' THEN 1 ELSE 0 END) AS 설치위치_공원
FROM 비상벨
GROUP BY CASE WHEN INSTR(REPLACE(REPLACE(관리기관명,'서울시',''),'서울특별시',''),'구') > 1 /*'구가 시작하는 최초위치 > 1' */
            THEN TRIM(SUBSTR(REPLACE(REPLACE(관리기관명,'서울시',''),'서울특별시','')
                ,1, INSTR(REPLACE(REPLACE(관리기관명,'서울시',''),'서울특별시',''),'구'))) /*처음부터 구의 위치까지 문자열 자르기*/
            ELSE TRIM(REPLACE(REPLACE(REPLACE(관리기관명,'서울시',''),'서울특별시',''),'청','')) END /*구로 시작하는 경우는 정제*/
            ;


select *
from 비상벨설치현황;

select *
from cctv설치현황;

--두개 조인해서 지역벌로 어디가 가장많이 설치되어 있는지 확인할것임. 각 지역별 카메라설치율,비상벨설치율 구하기.
select A.지역
    , A.전체카메라설치수
    , B.설치대수
    , ROUND(A.전체카메라설치수 / (A.전체카메라설치수+B.설치대수)*100,2)||'%' AS 전체카메라설치율
    , ROUND(B.설치대수 / (A.전체카메라설치수+B.설치대수)*100,2)||'%' AS 비상벨설치율
from cctv설치현황 A full outer join 비상벨설치현황 B
 on A.지역 = B.지역
 ;
 
 --위에꺼 카메라설치비율 가지고 구간나눠서 해당되는 평균설치 카메라수 구할거임. 
select CASE WHEN ROUND(A.전체카메라설치수 / (A.전체카메라설치수+B.설치대수)*100,2) <= 50 THEN '설치미비'
    WHEN ROUND(A.전체카메라설치수 / (A.전체카메라설치수+B.설치대수)*100,2) <= 80 THEN '설치보통'
    WHEN ROUND(A.전체카메라설치수 / (A.전체카메라설치수+B.설치대수)*100,2) <= 95 THEN '설치우수'
    WHEN ROUND(A.전체카메라설치수 / (A.전체카메라설치수+B.설치대수)*100,2) > 95 THEN '완전설치'
    ELSE 'CHK' END AS 설치구분
    ,TO_CHAR(SUM(A.전체카메라설치수+B.설치대수),'999,999') AS 전체설치건수
    ,TO_CHAR(ROUND(AVG(A.전체카메라설치수+B.설치대수)),'999,999') AS 평균건수
    ,COUNT(*) AS 지역수
    ,TO_CHAR(SUM(A.전체카메라설치수),'999,999') AS 카메라수
    ,TO_CHAR(SUM(B.설치대수),'999,999') AS 비상벨수
    ,ROUND(SUM(A.전체카메라설치수) / SUM(A.전체카메라설치수+B.설치대수)*100,2)||'%' AS 카메라설치비율
    ,ROUND(SUM(B.설치대수) / SUM(A.전체카메라설치수+B.설치대수)*100,2)||'%' AS 비상벨설치비율
from cctv설치현황 A INNER join 비상벨설치현황 B
 on A.지역 = B.지역
 GROUP BY CASE WHEN ROUND(A.전체카메라설치수 / (A.전체카메라설치수+B.설치대수)*100,2) <= 50 THEN '설치미비'
    WHEN ROUND(A.전체카메라설치수 / (A.전체카메라설치수+B.설치대수)*100,2) <= 80 THEN '설치보통'
    WHEN ROUND(A.전체카메라설치수 / (A.전체카메라설치수+B.설치대수)*100,2) <= 95 THEN '설치우수'
    WHEN ROUND(A.전체카메라설치수 / (A.전체카메라설치수+B.설치대수)*100,2) > 95 THEN '완전설치'
    ELSE 'CHK' END
ORDER BY 카메라설치비율 DESC
 ;

이해될듯말듯한 상태라서 따로 찾아보면서 추가적으로 이해해두자

/*기타 조인의 종류
1. natural join
2. cross join
3. 셀프조인
*/

/* natural join - join 조건 주지않고 eq조인하는것*/

select *
from employees natural join departments
;

select *
from employees
;
select *
from departments
;

--employees와 departments에서 중복되는값은 daprtment_id와 manager_id 두개임. 이두개 컬럼이 키로 활용
--기존에넌 innerjoin 해서 전체출력하게되면 해당되는 각각의테이블에 중복되는 컬럼또한 _1붙어서 출력이 되었으나,
--natural join에서는 중복되는 컬럼도 제거해서 한가지만 보여줌, 중복되는 컬럼이 복합키로 활용되어 조인이 되었다고 생각하면됨. 


-- inner join으로 출력했을때 > _1 제외하고는 natural join과 같은 값이 나온다. 
select *
from employees inner join departments
on employees.department_id = departments.department_id
and employees.manager_id = departments.manager_id;

--cross join도 작성할때는 natural join처럼 키를 연결해주지는 않는다. 
select *
from employees cross join departments
;

select count(*)
from employees cross join departments
;

select 107*27 from dual;
--이렇게 데이터가 부풀려지는 이유는 조인에대한 키값을 알지못하기 때문에. 
--cross join은 join이라 할순없지만 시스템 테스트하거나 테스트예제 생성할때 사용되는편임.


--오라클방식 join 테이블선언해주고 조인키 걸어주지않으면 모든 경우의 수에대한 행의수가 생성되게되니 오라클방식 join사용할경우 반드시 키값 걸어주어야 한다. 
select count(*)
from employees, departments
;

--self join 자기자신과 조인할때
--employee 테이블을 두번 join할거임 , 하나는 관리자id로, 하나는 사원정보 기준으로 join할거임.
--첫번째임플로이. 두번째임플로이 테이블 각각 이름 지정하고/ 임플로이테이블의 피관리인과 관리인을 결합하여 내가속한 관리자가 누구인지 출력
select emp.employee_id as 사원ID
        , emp.last_name||' '||emp.first_name 사원이름
        , emp.job_id 사원직업
        , man.last_name||' '||man.first_name 관리자이름
        , man.job_id 관리자직업
        , man.employee_id 관리자번호
from employees emp INNER JOIN employees man
on emp.manager_id = man.employee_id
;
--최고관리자는 관리자가 없기때문에 조인할값(관리자번호)가 NULL이므로 출력에서 빠짐.
--mployee.manager_id기준으로 employee_id가 있는 것을 출력하려고했기때문.
--ㅡMANAGER 피관리인/ EMPLOYEE 관리인 결합, 내가속한 관리자가 누구인지 출력. 

--그래서 1건을더 출력하려면 left outer join으로 바꿔 주어야 한다. >그러면 최고관리자도 출력됨
select emp.employee_id as 사원ID
        , emp.last_name||' '||emp.first_name 사원이름
        , emp.job_id 사원직업
        , man.last_name||' '||man.first_name 관리자이름
        , man.job_id 관리자직업
        , man.employee_id 관리자번호
from employees emp left outer JOIN employees man
on emp.manager_id = man.employee_id
;
--내가 누구에게 속해있는지 자기자신 테이블을 참조하여 JOIN하여 결과출력이 가능함.

--3개이상 조인해서 결과출력 / 사원/직업/부서테이블 조인하여 직업명과 부서타이틀을 가져오겠다. 
SELECT a.last_name||' '||a.first_name as 사원이름
    , to_char(a.hire_date, 'YYYY-MM-DD') AS 입사일
    , b.department_name as 부서명
    , a.salary as 급여
FROM  employees a left outer join departments b
 on a.department_id = b.department_id
 ;
--사원테이블 부서테이블 조인해서 부서명 먼저 출력. 한번더 조인하려면 from join절 on뒤에 한번더 left outer join선언
SELECT a.last_name||' '||a.first_name as 사원이름
    , to_char(a.hire_date, 'YYYY-MM-DD') AS 입사일
    , b.department_name as 부서명
    , c.job_title as 직업명
    , a.salary as 급여
FROM  employees a left outer join departments b
 on a.department_id = b.department_id
 left outer join jobs c    ---직업정보가 들어있는 테이블을 한번더 조인한다. 
 on a.job_id = c.job_id
 ;
 --vlookup을 a시트와 b시트 결과를 만들어놓고 한번더 추가된 시크에서 c 시트에있는 또다른 정보를 가져온다고 생각하는것과 같다. 
 
 
 --oracle표준방식의 경우 
 SELECT a.last_name||' '||a.first_name as 사원이름
    , to_char(a.hire_date, 'YYYY-MM-DD') AS 입사일
    , b.department_name as 부서명
    , c.job_title as 직업명
    , a.salary as 급여
FROM  employees a , departments b, jobs c
where a.department_id = b.department_id
    and a.job_id = c.job_id
 ;

--outer join 배우는날 /엑셀에서 vlookup 사용해서 값 가져오는거랑 동일
--내가 찾으려고 하는 값이 존재하지않으면 보여주지않는다. 왼쪽 정보를 기준으로 정보를 가져온다. 
--값이 존재하지 않더라도 행의수는 살아잇음.  기준 테이블이 어디에 있는지에 따라 LEFT/RIGHTJOIN으로 나뉜다. 

--<ANSI JOIN 방식>
/* SELECT 필드명
FROM 테이블명1 LEFT/RIGHT OUTER JOIN 테이블명2
    ON 테이블명1.필드명1 = 테이블명2.필드명2
WHERE 조건식
*/

SELECT *
 FROM EMPLOYEES left outer join departments
 on employees.department_id = departments.department_id
 ;
SELECT *
 FROM EMPLOYEES right outer join departments
 on employees.department_id = departments.department_id
 ;
SELECT *
 FROM departments left outer join employees
 on employees.department_id = departments.department_id
 ;
--outer join은 leftjoin 하나만 있는것으로 알고 기준테이블을 뭘로할지만 명확하게 알고있다면 한가지로만으로도 사용할수 있겠다. 

--<ORACLE JOIN 방식>  >> join사인, 표시를 해줘야함. 
SELECT *
 FROM employees, departments
 WHERE employees.department_id = departments.department_id(+)  --기준테이블 반대쪽에 조인사인을 주면, 반대테이블이 기준이 되고, 이 기준으로 left조인 된다고 생각.
 --이경우 employees.department_id 기준으로 departments.department_id를 가져와서 조인해준거임
 ;
 
 
 SELECT *
 FROM employees, departments
 WHERE employees.department_id(+) = departments.department_id
 --이경우 departments를 기준테이블로 하고싶어서 employeedp (+)조인 사인을 줌.
 ;
 
 --각각 테이블에 존재하지않는 값을 모두 출력하고 싶을때! > 양쪽에 조인사인(+)을 주는게 아니다! 
 --이때는 ANSI표준 방식인 FULL OUTER JOIN을 사용하면 된다.
 SELECT *
 FROM employees FULL OUTER JOIN departments
 ON employees.department_id = departments.department_id
 --이경우 departments를 기준테이블로 하고싶어서 employeedp (+)조인 사인을 줌.
 ;
 
 

inner join 사용할때 방식 혼용하지않도록 주의하기

/*SELECT 필드명
FROM 테이블명1 INNER JOIN 테이블명2
    ON 테이블명1.필드명1 = 테이블명2.필드명2
WHERE 조건식
*/

SELECT COUNT(*)
FROM employees; --107건

SELECT COUNT(*)
FROM departments;  --27건

--부서id 기준으로 inner join
FROM INNER JOIN 테이블명2         --(1)테이블명+ 2()기준테이블의 참조테이블이 될 테이블
    ON 테이블명1.필드명1 = 테이블명2.필드명2   --join방식에 대한 키값 연결
WHERE 조건식
;

select*
 from employees inner join departments
    on employees.department_id = departments.department_id

    ;--이때 전체 행의 갯수가 107>106건으로 줄었음>employees.department_id에 null값이 한건 존재하여 join할수 없었음. 교집합에대한 정보만 표시.

--<ANSI표준 JOIN방식>
select a.employee_id
    , a.salary
    , a.department_id
    , b.department_name
    from employees a inner join departments b 
    on a.department_id = b.department_id
    where a.department_id = 30
    ;
----<ORACLE JOIN방식> join- inner join을 ,로 대체(JOIN할 테이블을 나열)하고 on대신에 where 조건을 걸어주면
select a.employee_id
    , a.salary
    , a.department_id
    , b.department_name
    from employees a, departments b 
 where a.department_id = b.department_id
    and a.department_id = 30

 ;

데이터 적재  cctv, 비상벨 

@저장파일경로/파일명.sql        +f5 전체스크립트 실행

@C:\study_db/cctv.sql;
@C:\study_db/비상벨.sql;
--f5누르면 전체실행

--묶은 지역을 기준으로(group by) 전체설치되어있는 지역별 카메라설치수와  + 각 목적구분별로 설치되어있는 카메라의 수를 구할거임. sum과 사용자정의함수 
SELECT distinct
    trim(replace(replace(replace(관리기관명,'서울특별시',''),'구청','구'),'경찰서','구')) as 지역 --중복제거,정제,trim사용하여 공백제거
    ,sum(카메라대수) as 전체카메라설치수
    ,sum(CASE WHEN 설치목적구분 = '교통단속' THEN 카메라대수 ELSE 0 END) AS 교통단속
    ,sum(CASE WHEN 설치목적구분 = '교통정보수집' THEN 카메라대수 ELSE 0 END) AS 교통정보수집
    ,sum(CASE WHEN 설치목적구분 = '기타' THEN 카메라대수 ELSE 0 END) AS 기타
    ,sum(CASE WHEN 설치목적구분 = '생활방범' THEN 카메라대수 ELSE 0 END) AS 생활방범
    ,sum(CASE WHEN 설치목적구분 = '시설물관리' THEN 카메라대수 ELSE 0 END) AS 시설물관리
    ,sum(CASE WHEN 설치목적구분 = '쓰레기단속' THEN 카메라대수 ELSE 0 END) AS 쓰레기단속
    ,sum(CASE WHEN 설치목적구분 = '어린이보호' THEN 카메라대수 ELSE 0 END) AS 어린이보호
    ,sum(CASE WHEN 설치목적구분 = '재난재해' THEN 카메라대수 ELSE 0 END) AS 재난재해
    ,sum(CASE WHEN 설치목적구분 = '차량방범' THEN 카메라대수 ELSE 0 END) AS 차량방범
    
FROM cctv
group by  trim(replace(replace(replace(관리기관명,'서울특별시',''),'구청','구'),'경찰서','구'))
;

/*
설치목적구분 어떤거있는지 확인
select distinct설치목적구분
from cctv;

교통단속
교통정보수집
기타
다목적
생활방범
시설물관리
쓰레기단속
어린이보호
재난재해
차량방범
*/
/*
SELECT DISTINCT 설치장소유형
FROM 비상벨
GROUP BY 설치장소유형;
*/

--지역별 비상벨대수
SELECT CASE WHEN INSTR(REPLACE(REPLACE(관리기관명,'서울시',''),'서울특별시',''),'구') > 1 /*'구가 시작하는 최초위치 > 1' */
            THEN TRIM(SUBSTR(REPLACE(REPLACE(관리기관명,'서울시',''),'서울특별시','')
                ,1, INSTR(REPLACE(REPLACE(관리기관명,'서울시',''),'서울특별시',''),'구'))) /*처음부터 구의 위치까지 문자열 가져오기*/
            ELSE TRIM(REPLACE(REPLACE(REPLACE(관리기관명,'서울시',''),'서울특별시',''),'청','')) END AS 지역/*구로 시작하는 경우는 정제*/
            ,COUNT(*) AS 설치대수
            ,SUM(CASE WHEN 설치장소유형 = '가로변' THEN 1 ELSE 0 END) AS 설치위치_가로변
            ,SUM(CASE WHEN 설치장소유형 = '기타' THEN 1 ELSE 0 END) AS 설치위치_기타
            ,SUM(CASE WHEN 설치장소유형 = '화장실' THEN 1 ELSE 0 END) AS 설치위치_화장실
            ,SUM(CASE WHEN 설치장소유형 = '주차장' THEN 1 ELSE 0 END) AS 설치위치_주차장
            ,SUM(CASE WHEN 설치장소유형 = '건물' THEN 1 ELSE 0 END) AS 설치위치_건물
            ,SUM(CASE WHEN 설치장소유형 = '공원' THEN 1 ELSE 0 END) AS 설치위치_공원
FROM 비상벨
GROUP BY CASE WHEN INSTR(REPLACE(REPLACE(관리기관명,'서울시',''),'서울특별시',''),'구') > 1 /*'구가 시작하는 최초위치 > 1' */
            THEN TRIM(SUBSTR(REPLACE(REPLACE(관리기관명,'서울시',''),'서울특별시','')
                ,1, INSTR(REPLACE(REPLACE(관리기관명,'서울시',''),'서울특별시',''),'구'))) /*처음부터 구의 위치까지 문자열 자르기*/
            ELSE TRIM(REPLACE(REPLACE(REPLACE(관리기관명,'서울시',''),'서울특별시',''),'청','')) END /*구로 시작하는 경우는 정제*/
            ;

--복습꼭하기