SQL 정리 - (5)

• 조건 연산자

  • CASE
  • coalesce
  • null if
  • cast
  • with
  • 재귀커리
  • 트랜잭션
    • begin
    • commit
    • rollback

---

• CASE 행으로 뽑는다.

SELECT

SUM (CASE WHEN RENTAL_RATE = 0.99 THEN 1 ELSE 0 END) AS “C”

FROM FILM;

• coalesce : 입력한 인자값 중에서 널값이 아닌 첫번째 값을 리턴한다. (널 처리할 때 유용하게 사용된다.)

SELECT

PRODUCT, price, discount, COALESCE(DISCOUNT, 0)

, (PRICE - COALESCE(DISCOUNT, 0)) AS NET_PRICE

FROM TB_ITEM_COALESCE_TEST;

---

• null if : 입력한 두개의 인자의 값이 동일하면 NULL을 리턴하고 그렇지 않으면 첫번째 인자값을 리턴한다.

SELECT (SUM(CASE WHEN GENDER = 1 THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN GENDER = 2 THEN 1 ELSE 0 END), 0)) * 100

AS “MALE/FEMALE RATIO”

FROM TB_MEMBER_NULLIF_TEST;

---

• cast 데이터 값을 특정 데이터 타입으로 형변환이 가능하도록 한다.

SELECT CAST (‘100’ AS INTEGER);

SELECT CAST ‘100’::INTEGER; 두개의 결과는 같다.

---

• with : select문의 결과를 임시 집합으로 저장해두고 sql문에서 마치 테이블 처럼 해당 집합을 불러올 수 있다.

WITH TMP1 AS (

SELECT FILM_ID, TITLE, (

CASE WHEN LENGTH < 30 THEN ‘SHORT’

WHEN LENGTH >= 30 AND LENGTH < 90 THEN ‘MEDIUM’

WHEN LENGTH > 90 THEN ‘LONG’

END) LENGTH

FROM FILM )

---

• 재귀 커리 : with문을 이용하여 재귀 커리를 작성. 재귀 커리랑 데이터 값 기준 부모 자식간의 관계를 표현하는 sql이다.

WITH RECURSIVE TMP1 AS (

SELECT EMPLOYEE_ID , MANAGER_ID , FULL_NAME , 0 LVL

FROM TB_EMP_RECURSIVE_TEST

WHERE MANAGER_ID IS NULL

UNION

SELECT E.EMPLOYEE_ID , E.MANAGER_ID , E.FULL_NAME , S.LVL + 1

FROM TB_EMP_RECURSIVE_TEST E , TMP1 S

WHERE S.EMPLOYEE_ID = E.MANAGER_ID )

SELECT EMPLOYEE_ID, MANAGER_ID, LPAD(‘ ‘, 4 * (LVL))

FULL_NAME AS FULL_NAME FROM TMP1;#

는 concat의미,LPAD는 왼쪽에 공백둘 때 사용

---

오라클 DDL은 커밋을 할 필요가 없다 POSTGRESQL dms DDL도 커밋을 해야한다.

• DML을 작업 하고 난 후 DBMS에 반영할건지 말건지를 결정해야 한다. 그렇게 하는게 COMMIT,ROLLBACK

commit 하기 전에 데이터를 입력하고 rollback하면 입력이 취소됨. 하지만 commit하고 rollback하면 효과가 없다.

---

• References

  • 패스트 캠퍼스 모두를 위한 SQL