뚝딱뚝딱

💡 GROUP BY

• GROUP BY 컬럼
  컬럼의 값 중 NULL도 있다면 NULL도 하나의 그룹이 될수 있음
  => 사원, 대리, 과장, 차장, 부장, 직급이 정해지지 않은(NULL)이 있을 때, 이를 직급 별로 group화 하면, 6개로 그룹화 가능
• GROUP BY 절에서는 SELECT 절과는 달리 ALIAS 명을 사용할 수 없음
• 집계 함수는 WHERE 절에 올 수 없음
  =>집계 함수는 GROUP BY 절에서 사용 가능
• WHERE 절은 전체 데이터를 GROUP으로 나누기 전에 행들을 미리 제거하는 역할 

💡 GROUP BY - HAVING 절 [53]

• GROUP BY가 없다고 해서 HAVING에 오류가 발생하지는 않는다
  예시)

SELECT SUM(주문금액) AS 합계
FROM 주문
HAVING AVG(주문금액) > 100;

=> GROUP BY가 생략된 경우, 적절한 PK로 그룹핑 되고, 그 그룹의 주문 금액 평균이 100보다 크다면 해당 주문금액들의 총합을 출력하는 쿼리이다.

주문ID | 주문금액
-----------------
1      | 80
2      | 120
3      | 90
4      | 110
5      | 95

 이와 같은 테이블이 있다면, 주문ID에 대해 자동 그룹핑될 것이고, 해당 그룹마다 평균 주문금액이 100보다 큰 것은, 주문ID 2와 4의 그룹이므로, 해당 그룹의 주문 금액 합은 120+110=230이다.

합계
-----
230

• COUNT와 같은 집계함수를 AVG함수로 감싸 평균을 구할 수 없다
  COUNT는 말 그대로 카운트 한 값을 한 레코드로 출력하는 것이고, 이의 평균이랄 것으로 나눌 값이 명시되지 않기 때문에 AVG함수와 함께 쓸 수 없다
  예시)

SELECT 메뉴ID, 사용유형코드, AVG(COUNT(*)) AS AVGCNT
FROM 시스템사용이력
GROUP BY 메뉴ID, 사용유형코드;

💡 ORDER BY [56] [57]

• GROUP BY를 사용할 경우 GROUP BY 표현식이 아닌 값은 기술될 수 없음
  예시)

SELECT 지역, SUM(매출금액) AS 매출금액
FROM 지역별매출
GROUP BY 지역
ORDER BY 년 DESC;

=> 지역에 대해서 그룹핑 되어 매출금액의 SUM을 구하기 때문에, 이를 년이라는 컬럼으로 ORDER할 수 없다

• ORACLE에서는 NULL값을 가장 큰 값으로 간주
  => 오름차순 정렬 시 NULL이 가장 마지막에
  => 내림차순 정렬 시 NULL이 가장 먼저
• SQL server에서는 NULL을 가장 작은 값으로 간주
  => 오름차순 정렬 시 NULL이 가장 먼저
  => 내림차순 정렬 시 NULL이 가장 마지막에
• ORDER BY정레 컬럼명 대신 ALIAS 명이나 컬럼 순서를 나타내는 정수를 혼용하여 사용할 수 있음

💡 SELECT 문장 실행 순서 [59]

FROM > WHERE > GROUP BY > HAVING > SELECT > ORDER BY

💡 TOP() 질의문 

• SQL server의 TOP N 질의문에서 N에 해당하는 값이 동일한 경우 함께 출력되도록 하는 WITH TIES 옵션을 ORDER BY 절과 함께 사용해야 함
• 예시)
  SELECT TOP(3) WITH TIES 팀명, 승리건수
  FROM 팀별성적
  ORDER BY 승리건수 DESC;
  
  =>팀별성적에서 팀명과 높은 순으로 승리건수를 조회하는데, 가장 위 3개를 조회한다. 단 3위의 승리건수와 동일한 팀이 있다면 해당 팀까지도 같이 출력한다. 

💡 EQUI JOIN 문장

• SELECT 테이블1.칼럼명, 테이블2.칼럼명,...
  FROM 테이블1, 테이블2
  WHERE 테이블1.칼럼명1=테이블2.칼럼명2;
  => WHERE 절에 JOIN 조건을 넣음
• SELECT 테이블1.칼럼명, 테이블2.칼럼명,...
  FROM 테이블1 INNER JOIN 테이블2
  ON 테이블1.칼럼명1=테이블2.칼럼명2;
  => ON 절에 JOIN 조건을 넣음

💡 JOIN

• 일반적으로 JOIN은 PK와 FK 값의 연관성에 의해 성립됨
• DBMS 옵티마이저는 FROM 절에 나열된 테이블들을 임의로 2개씩 묶어서 JOIN을 처리함
• EQUI JOIN은 JOIN에 관여하는 테이블 간의 컬럼 값들이 정확하게 일치하는 경우에 사용되는 방법임
• EQUI JOIN은 = 연산자에 의해서만 수행되며, 그 이외의 비교 연산자를 사용하는 경우에는 모두 NON EQUI JOIN임
• 대부분 NON EQUI JOIN을 수행할 수 있지만, 때로는 설계상의 이유로 수행이 불가능한 경우도 있음

💡 isolation level이 낮은 경우 발생할 수 있는 문제점

• Dirty read : 다른 트랜잭션에 의해 수정되었지만 아직 커밋되지 않은 데이터를 읽는 것
• Phantom read : 한 트랙잭션 내에서 같은 쿼리를 두 번 수행했는데, 첫번째 쿼리에서 없던 유령 레코드가 두번째 쿼리에서 나타나는 현상
• Non-Repeatable read : 한 트랜잭션 내에서 같은 쿼리를 두 번 수행했는데, 그 사이에 다른 트랜잭션이 값을 수정 또는 삭제하는 바람에 두 쿼리 결과가 다르게 나타나는 현상

💡 ORACLE과 SQL server에서의 commit과 rollback [29]

• ORACLE에서는 DDL(create, drop, alter..) 문장 수행 후 자동으로 commit을 수행
• SQL server에서는 DDL 문장 수행 후 자동으로 commit을 수행하지 않음
• SQL server에서는 CREATE TABLE 문장도 TRANSACTION의 범주에 포함
  => ROLLBACK 문장에 의해서 최종적으로 B 테이블은 생성되지 않음

💡 저장점(SAVEPOINT)

• 저장점을 정의하면 롤백 할 때 트랜잭션에 포함된 전체 작업을 롤백하는 것이 아니라 현 시점에서 savepoint 까지 트랜잭션의 일부만 롤백할 수 있음
• [ORACLE]
  SAVEPOINT SVPT1;
  ...
  ROLLBACK TO SVPT1;
  => ...에 있던 부분은 모두 ROLLBACK
• [SQL server]
  SAVE TRANSACTION SVPT1;
  ...
  ROLLBACK TRANSACTION SVPT1;
  => ...에 있던 부분은 모두 ROLLBACK

💡 논리연산자의 우선순위

• NOT > AND > OR

💡 NULL 

• NULL 값이 포함된 사칙 연산의 결과는 NULL
• NULL 값을 조건절에서 사용하는 경우 IS NULL / IS NOT NULL 이란 키워드를 사용해야 함

💡 [37]

• [SQL]
  ㄱ. SELECT * FROM 서비스 WHERE 서비스번호 = 1;
  ㄴ. INSERT INTO 서비스 VALUES ('999', '', '2015-11-11');
  ㄷ. SELECT * FROM 서비스 WHERE 서비스명 = '';
  ㄹ. SELECT * FROM 서비스 WHERE 서비스명 IS NULL;
• ㄴ과 같이 데이터를 입력하면, 서비스명 컬럼의 데이터에 대해서 ORACLE에서는 NULL로 입력됨
• ㄴ과 같이 데이터가 입력되어있을 때, ORACLE에서 데이터를 조회하려면 서비스명 IS NULL 조건으로 조회해야 함
• ㄴ과 같이 데이터가 입력되어있을 때, SQL server에서 데이터를 조회하려면 서비스명=''로 조회해야 함

💡 내장함수 [40]

• 함수는 벤더에서 제공하는 함수인 내장 함수와 사용자가 정의할 수 있는 함수로 나눌 수 있음
• 내장 함수는 다시 단일행함수와 다중행함수로 나눌 수 있음
• 다중행 함수는 집계함수, 그룹함수, 윈도우 함수로 구분
• 함수의 입력 행수에 따라 단일행 함수와 다중행 함수로 구분
• 단일행 함수 예 : =, >, <...
• 다중행 함수 예 : IN, EXIST...
• 단일행 함수는 select, where, order by, update의 set절에 사용이 가능
• 일대다 조인이라 하더라도 다쪽에서 출력된 행이 하나씩 단일행 함수의 입력값으로 사용되므로 단일행 함수 사용가능
• 다중행 함수와 단일행 함수는 동일하게 단일 값만을 반환

💡 라인 수 구하기 sql [41]

TAB1

SELECT SUM(CC)
FROM(

           SELECT(LENGTH(C1)-LENGTH(REPLACE(C1, CHR(10)))+1) CC FROM TAB1

)

LENGTH : 문자열의 길이를 반환하는 함수

CHR : 주어진 ASCII 코드에 대한 문자를 반환하는 함수 (CHR(10)-> 줄바꿈)

REPLACE : 문자열을 치환하는 함수 (REPLACE(C1, CHR(10))-> 줄바꿈 제거)

함수 결과 값

2+3 =5

답 5

💡 ORACLE 에서의 날짜 연산 [42]

SELECT TO_CHAR(TO_DATE('2015.01.10 10', 'YYYY.MM.DD HH24')+1/24/(60/10), 'YYYY.MM.DD HH24:MI:SS') FROM DUAL;

• 오라클에서 날짜의 연산은 숫자의 연산과 같음
• 특정 날짜에 1을 더하면 하루를 더한 결과와 같으므로 1/24/60 = 1분을 의미
  1=하루
  1/24 = 하루를 24로 나눈다= 한시간
  1/24/60 = 한시간을 60으로 나눈다 = 1분
  1/24/(60/10) = 한시간을 6으로 나눈다 = 10분
• 따라서 10분 더해주면 된다.

답 : 2015.01.10 10:10:00

💡 SEARCHED_CASE_EXPRESSION -> SIMPLE_CASE_EXPRESSION 으로 변환 [43]

[ SEARCHED_CASE_EXPRESSION ]

WHEN LOC = 'NEW YORK' THEN 'EAST'

[ SIMPLE_CASE_EXPRESSION ]

LOC WHEN 'NEW YORK' THEN 'EAST'

=> SIMPLE은 부등호를 없앤다

💡 ISNULL함수 [45]

• ISNULL 함수는 결과값이 NULL일 경우 지정된 값을 반환
  ISNULL(COL2,'X')
  => COL2값이 NULL이면 'X'를 반환
• WHERE 절안에서 컬럼의 NULL 값을 확인할 때는 IS NULL을 사용해야 함

💡 단일행 NULL 관련 함수 [46]

• NVL(표현식1, 표현식2) / ISNULL(표현식1, 표현식2)
  => 표현식1의 결과값이 NULL이면 표현식2의 값을 출력
  => 단 표현식1과 표현식2의 결과 데이터 타입이 같아야 함
  => NULL 관련 가장 많이 사용되는 함수로 상당히 중요
• NULLIF(표현식1, 표현식2) 
  => 표현식1이 표현식2와 같으면 NULL을, 같지 않으면 표현식1을 반환
• COALESCE(표현식1, 표현식2, ...)
  => 임의의 개수 표현식에서 NULL이 아닌 최초의 표현식을 나타냄
  => 모든 표현식이 NULL이라면 NULL 리턴

사원 테이블에서 MGR의 값이 7698과 같으면 NULL을 표시하고, 같지 않으면 MGR을 표시하려고 한다. 

=> NULLIF(MGR, 7698) 사용

💡 NULL AVG 연산

• NULL은 AVG 연산 대상에서 제외, COUNT도 마찬가지.

💡 비절차적 데이터 조작어(DML) VS 절차적 데이터 조작어(ORACLE / SQL server) [4]

• 비절차적 데이터 조작어는 사용자가 무슨(what) 데이터를 원하는 지만을 명세
• 절차적 데이터 조작어는 어떻게(how) 데이터를 접근해야 하는지 명세
  
  

💡 DDL / DML / DCL / TCL 

• DDL : CREATE, DROP, ALTER, RENAME
• DML : SELECT, INSERT, UPDATE, DELETE
• DCL : GRANT, REVOKE
• TCL : COMMIT, ROLLBACK

💡 SQL server [7]

• SQL server에서는 여러개의 컬럼을 동시에 수정하는 구문은 지원하지 않는다.
• SQL server에서는 괄호를 사용하지 않는다.

💡 PK / UNIQUE

• PK : UNIQUE & NOT NULL
• UNIQUE : 테이블 내 중복 값 x, NULL 입력 가능

💡 테이블명-칼럼명 설정 규칙

• 반드시 문자로 시작
• A-Z, a-z, 0-9, $, _, #

💡 외래키

• 외래키 값은 널 값을 가질 수 있음
• 한 테이블에 여러개 존재 가능

💡 컬럼 삭제 sql [16]

• ALTER TABLE 테이블명 DROP COLUMN 컬럼명;

💡 참조무결성 규정 [19]

• DELETE/MODIFY
  CASCADE : master 삭제시 child 같이 삭제
  Set Null : master 삭제 시 child 해당 필드 Null
  Set Default : master 삭제 시 child 해당 필드 Default 값으로 설정
  Restrict : child 테이블에 PK 값이 없는 경우만 master 삭제 허용
  No Action : 참조무결성을 위반하는 삭제/수정 액션을 취하지 않음
• INSERT
  Automatic : master 테이블에 PK가 없는 경우 master PK를 생성 후 child 입력
  Dependent : master 테이블에 PK가 존재할 때만 child 입력 허용

💡 테이블 이름 변경 sql [18]

• RENAME 구이름 TO 새이름;

💡 제약조건에 따른 실행 [22]

• FK에 대한 제약조건으로, DELETE 시 SET NULL = 부모 키가 DELETE되면 해당 FK는 NULL을 갖도록 설정
• 그런데, 문제에서는 해당 FK 컬럼이 NOT NULL로 지정되어 있기 때문에, 위와 같이 설정되어 있더라도 NULL 값을 가질 수 없으므로, 이와 같이 수행되는 SQL문은 오류가 발생함 

💡 DELETE [23]

Q. 개발 프로젝트의 표준은 모든 삭제 데이터에 대한 로그를 남기는 것을 원칙으로 하고, 테이블 삭제의 경우는 허가된 인력만이 정기적으로 수행 가능하도록 정하고 있다. 개발팀에서 사용 용도가 없다고 판단한 STADIUM 테이블의 데이터를 삭제하는 가장 좋은 방법은 무엇인가?

A. DELETE FROM STADIUM;

• DELETE는 로그는 남기고, 데이터만 삭제한다.
• TRUNCATE TABLE과 DROP은 로그를 남기지 않으므로 해당하지 않는다.

💡 TRUNCATE TABLE

• 특정 테이블의 모든 데이터를 삭제
• 디스크 사용량을 초기화

💡 DROP vs TRUNCATE vs DELETE [26]

💡 성능데이터 모델링 수행 절차 

1. 데이터모델링을 할 때 정규화를 정확하게 수행
2. 데이터베이스 용량산정을 수행
3. 데이터베이스에 발생되는 트랜잭션의 유형을 파악
4. 용량과 트랜잭션의 유형에 따라 반정규화를 수행
5. 이력모델의 조정, PK/FK조정, 슈퍼타입/서브타입 조정 등을 수행
6. 성능관점에서 데이터모델을 검증

💡 I-32 반정규화

- 반정규화는 데이터를 중복하여 성능을 향상시키기 위한 기법
- 좀 더 넓은 의미의 반정규화는 성능을 향상시키기 위해 정규화된 데이터 모델에서 중복, 통합, 분리 등을 수행하는 모든 과정
- 데이터 무결성이 깨질 수 있는 위험을 무릅쓰고 데이터를 중복하여 반정규화를 적용하는 이유
-> 데이터를 조회할 때 디스크 I/O량이 많아서 성능이 저하되거나 경로가 너무 멀어 조인으로 인한 성능저하가 예상되거나 칼럼을 계산하여 읽을 때 성능이 저하될 것이 예상되는 경우 반정규화를 수행

💡 I-38 

컬럼 단위에서 중복된 경우도 1차 정규화의 대상이 된다.

이에 대한 분리는 1:M의 관계로 두 개의 엔터티로 분리된다.

💡 I-39

PK에 대해 반복이 되는 그룹이 존재하지 않으므로 1차 정규형

부분 함수종속의 규칙을 가지고 있으므로 2차 정규형이라고 할 수 없음.

2차 정규화의 대상이 되는 엔터티임.

💡 칼럼의 반정규화 기법

• 중복칼럼 추가
• 파생칼럼 추가
• 이력테이블 칼럼추가
• PK에 의한 칼럼 추가
• 응용시스템 오작동을 위한 칼럼 추가

💡 I-43

주문 엔터티에 전체를 통합한 계산된 칼럼을 추가하는 것이 한번에 데이터를 조회하는 방법이 되므로 가장 효과적인 반정규화 기법이다.

💡 I-44

최근에 변경된 값만을 조회할 경우 과도한 조인으로 인해 성능이 저하되어 나타나게 된다.

💡 파티셔닝

논리적으로는 하나의 테이블이지만 물리적으로는 여러 개의 테이블로 분리하여 데이터 엑세스 성능도 향상시키고, 데이터 관리방법도 개선할 수 있도록 테이블에 적용하는 기법

💡 I-47

개별 테이블을 모두 조회하는 트랜잭션이 대부분이라는 가정이 있으므로 UNION/UNION ALL할 경우 개별조회에 따른 시간요소와 이것을 조합하는 성능저하가 발생된다. 따라서 하나의 테이블로 통합하도록 하고 대신 pk체계나 일반속성에 각 사건을 구분할 수 있도록 구분자를 부여한다.

💡 I-51

데이터베이스 상에서 DBMS가 제공하는 FK Constraints를 생성했는지 여부와 상관없이 조인 성능을 향상시키기 위한 인덱스를 생성해주는 것이 좋다. 

그러므로 수강신청테이블의 학사기준번호에 인덱스가 필요하다.

💡 I-52

Global Single Instance(GSI)는 통합된 한 개의 인스턴스 즉, 통합 데이터베이스 구조를 의미하므로, 분산데이터베이스와는 대치되는 개념임.

분산환경에 복제분산을 하는 방법으로 분산데이터베이스를 구성할 수 있고, 백업 사이트 구성에 대해서도 분산 환경으로 구성하여 적용할 수 있음.

I-2 데이터 모델링

Q 데이터 베이스를 구축하기 위한 용도를 위해 데이터 모델링을 수행하고 업무에 대한 설명은 별도의 표기법을 이용한다.(x)

=> 데이터 모델링이라는 것은 단지 데이터베이스만을 구축하기 위한 용도로 쓰이는 것이 아니라 데이터 모델링 자체로서 업무를 설명하고 분석하는 부분에서도 매우 중요한 의미를 가지고 있다.

I-13 엔터티의 이름을 부여하는 방법

Q 가능하면 약어를 사용하여 엔터티의 이름을 간결하고 명확하게 한다 (x)

=> 가능하면 약어를 사용하지 않는다.

=> 엔터티 생성의미대로 이름을 부여한다.

I-14 속성

Q 업무에서 필요로 하는 인스턴스에서 관리하고자 하는 의미상 더 이상 분리되지 않는 최소의 데이터 단위

=> 속성(ATTRIBUTE)

I-12 식별자

Q 다른 엔터티로부터 주식별자를 상속받지 않고, 자신의 고유한 주식별자를 가지며 사원, 부서, 고객, 상품, 자재 등이 예가 될 수 있는 엔터티

=>기본엔터티(키 엔터티)

I-20 관계

Q 데이터 모델링에는 존재적 관계와 행위에 의한 관계를 구분하는 표기법이 없지만, UML에서는 연관관계와 의존관계에 대해 다른 표기법을 가지고 표현하게 되어있다.

=> 실선과 점선의 표기법

I-23 관계

Q  업무기술서, 장표에 관계연결을 가능하게 하는 명사가 있는가? (X)

=> 동사가 있는가?

I-29 관계

Q 부모 엔터티의 주식별자를 자식엔터티에서 받아 손자엔터티까지 계속 흘려보내기 위해 비식별자관계를 고려한다 (X)

=> 식별자 관계를 고려