뚝딱뚝딱

💡 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도 마찬가지.