53회 SQLD (2024 05 25) 후기 및 오답정리

53회 SQLD 시험 후기

이번 시험은 기출 문제에 비해서 어려운 문제가 많았다. 한 달 동안 2023 이기적 sql 교재 하나로만 공부했는데 다시 돌아간다면 노랭이 + 다른 기본서를 참고해서 두 달 정도 깊게 공부할 것이다. 시험을 보다가 2023 교재에는 없는 피벗 테이블 문제가 하나 나와서 당황했다ㅠㅠ
(교재가 오타도 많고 별로라 느꼈는데 그래도 합격 점수를 받은 걸 보면 교재가 아주 별로는 아님)
5만원 내고 응시했는데 결과도 한 달 뒤에 나오고 시험지를 가져올 수 없었다. 아쉬운대로 카페에서 답을 맞춰보며 도움이 많이 됐다. - https://cafe.naver.com/sqlpd
참고로 정처기와는 다르게 시험 장소가 근처에 많지 않아서 접수 첫 날에 바로 하는 걸 추천한다.
----------- 출제 유형 ---------
테이블 간에 같은 row가 있는데 JOIN 결과에 하나만 포함되는지, 둘다 포함되는지
묵시적 형변환(ex) 10 + '10')
NULL이 포함된 로우가 조인 결과로 카운트 되는지, UNION, 순위(ROWNUM, RANK, TOP, ...),
JOIN, NULL과 관련 모든 내용

 

---

 

인조 식별자

인조 식별자를 만든 경우, 반드시 인조 식별자를  사용해야 한다. (X)

 

 

파티셔닝 RANGE, ROWS 차이점

• RANGE
  • price 값을 기준으로 정렬한 다음, current row의 값 price 에 대해
  • price - 100 ~ price + 200 범위에 있는 데이터만 가져온다.

SUM(amount) OVER (
     ORDER BY price
     RANGE BETWEEN 100 PRECEDING AND 200 FOLLOWING
 ) AS running_total

• ROWS
  • price 값을 기준으로 정렬한 다음, current row를 기준으로 전전행 부터(2 preceding) 다음 행(1 following)에 해당하는 row 범위에 있는 데이터를 모두 가져온다.

  SUM(amount) OVER (
       ORDER BY price
       ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING
   ) AS running_total
  

 

 

• 정규식 1) REGEXP_SUBSTR 이메일
• 정규식 2) REGEXP_SUBSTR (ABAA) 

 

 

LTRIM(’abcxyxabx’, ‘cba’)

• 두 번째 매개변수의 값을 쪼갠다. c, b, a
• 첫 번째 매개변수의 앞에서부터 c, b, a 가 아닌 문자가 나오면 그 문자의 앞부분을 제거한다.
• → xyxabx

 

 

UNION 으로 연결했을 때, 각 쿼리에 GROUP BY, ORDER BY 절을 적용 가능한가?  (노랭이 기출)

• ORDER BY: X  마지막에 딱 한번만 적용 가능함.
• GROUP BY: O

 

 

카르테시안 조인

• select count(*) from a , b where 조건;
  a 테이블에 [1, 3] 로우 1개 존재
• b 테이블에 [1, 3] 로우 3개 존재
• →   b의 중복된 로우 3개 모두 카운트된다.

 

 

 UNPIVOT

• PIVOT: 행 데이터를 열 데이터로 바꾼다.
• UNPIVOT: 열 데이터를 행 데이터로 바꾼다.

 

 

CARTESIAL JOIN

• SELECT * FROM a, b WHERE a.col1 = b.col1 and a.col2 = b.col2 and a.col3 = b.col3;
• A, B 테이블을 JOIN 하려는데 [1, 2, NULL] 도 카운트 되는가? (X)
  • a.col3 , b.col3 모두 NULL인 경우, a.col3 = b.col3 결과는 FALSE

 

 

NATURAL JOIN

• A와 B 테이블 A테이블에 [1, 3] 한 개, B테이블에는 [1,3] 가 5개인 경우
• COUNT(*)결과: 5

 

 

 ALIAS

• TABLE ALIAS를 선언한 경우 SELECT 절에 테이블명을 직접 쓰지 말고 선언한 ALIAS를 적용해야한다.

 

 

계층형 질의가 있는 쿼리 실행 순서

• 계층형 구조를 먼저 형성하고 나중에 order by를 실행한다.

 

 

NOT IN(1, 2, NULL) 의 결과

• SELECT * WHERE NOT IN(1, 2, NULL) 의 결과는 ?

 

 

테이블 구조 변경

• 어떤 칼럼의 크기를 10으로 설정한 다음 칼럼의 크기를 더 큰 사이즈 / 더 작은 사이즈로 변경 가능한가?
  • 작은 사이즈로 바꾼다면 이미 큰 값으로 데이터가 들어간 로우는 어떻게 되는지?
• 어떤 칼럼에 대해 null 이 가능하도록 설정한 다음, notnull로 설정을 바꾼다면 
  • 바꾼다면 기존에 null 값이 들어간 row에 에러 발생??

 

 

 

DROP 과 DELETE 차이점

• 선지: WHERE 절이 없는 DELETE문은 DROP과 같다.  (X)
• DROP은 테이블을 CREATE 하기 이전으로 돌아가고 DELETE는 테이블의 형태는 남아있으나 모든 로우만 지우는 기능이다. 따라서 다르다.
• 그렇다면 WHERE 절이 없는 DELETE는 테이블을 CREATE 하기 전으로 돌아가는 TRUNCATE와  같은 결과를 가져올까?  (X)
  • WHERE 절이 없는 DELETE
  • TRUNCATE: 

 

 

ROLLBACK  결과

<hide/>
INSERT INTO TABLE50 VALUES(1);
INSERT INTO TABLE50 VALUES(2);
INSERT INTO TABLE50 VALUES(3);
SAVEPOINT SQL1;
INSERT INTO TABLE50 VALUES(4);
INSERT INTO TABLE50 VALUES(5);
COMMIT;
INSERT INTO TABLE50 VALUES(6);
ROLLBACK TO SAVEPOINT SQL1;

SELECT * FROM TABLE50;

• 이번 시험의 마지막 문제로 기억한다. 카페에서는 예상 답이 {1, 2, 3, 4, 5}  {1, 2, 3, 4, 5, 6} 으로 답이 나뉘었다.
• 문제를 풀면서 롤백에서 에러가 날 거라는 예상을 하지 못하고 1, 2, 3이 출력될거라 잘못 생각했다.
• (Oracle 기준) 이론적으로는 롤백에서 예외가 터지면서 commit이 완료된 5까지 출력되고 6은 삭제되어야한다.
  • Oracle에서는 autocommit = off 가 기본값이므로 1, 2, 3, 4, 5 가 출력되어야 정상이다.
• ROLLBACK: 해당 트랜잭션이 시작되기 전 상태로 되돌린다.
  • 특정 저장점으로 롤백 가능.
  • 하지만, 이전에 실행된 트랜잭션의 안에 있는 SAVEPOINT로 롤백하는 건 불가능하다.
• COMMIT: 트랜잭션의 모든 변경사항을 DB에 저장하고나서 트랜잭션을 종료한다.
  • COMMIT 하고 나면 커밋된 트랜잭션 내에 설정한  SAVEPOINT가 모두 무효화된다.

 

 

---

MySQL , MariaDB  롤백 문제

 

바로 위의 롤백 문제를 여러 DB에서 테스트하다가 다음과 같은 문제를 확인했다.

 

문제점

• PostgreSQL, MariaDB에서  autocommit = off 로 설정하고 동일한 쿼리를 실행했으나 다음과 같이 결과가 달랐다.
• DBeaber 툴 문제인가 싶어서 터미널로도 테스트했으나 문제가 있었다.
• MySQL 계열 DB 에서는 autocommit 설정이 무시되고 전체 트랜잭션이 취소되지 않았다. 롤백 문장 하나만 취소됐다.
  • MySQL, MariaDB: autocommit = off로 설정하더라도, 트랜잭션에서 에러가 나는 경우 전체 트랜잭션을 롤백하지는 않는다. ( ↔ 트랜잭션의 원자성 )
  • Oracle, MsSQL, PostgreSQL: autocommit = off 로 설정하는 경우, 트랜잭션에서 에러가 나면 전체 트랜잭션 내용을 롤백한다.

 

 

Note

• MySQL은 단순함, 성능을 최우선 가치로 두고 설계 되었다. 따라서 복잡한 에러 핸들링 로직이 자동화되어있지 않고 사용자가 명시적으로 롤백해야한다.
  • 위의 쿼리에서는 'INSERT 6' 이 부분을 롤백하도록 개발자가 설정해야 한다.
  • 그러면 autocommit 기능이 왜 있을까?
• MySQL에서 예외 발생 시 자동 롤백하는 방법?
  • 저장 프로시저에서 에러 핸들러를 사용해서 에러 발생 시 트랜잭션을 롤백하도록 설정해야한다.
• 이와 다르게 다른 DB(Oracle, PostgreSQL)는 에러가 나면 자동으로 전체 트랜잭션을 롤백한다. 
• 그럼 MySQL은 autocommit = off 로 설정해도 autocommit = on 인 상태로 동작한다는 뜻?
  • 트랜잭션의 특성 ACID 중 원자성(Atomicity)를 만족하지 않는가?

 

 

 

참고 링크

InnoDB Error Handling 

START TRANSACTION, COMMIT, and ROLLBACK Statements

혹시 잘못된 부분이 있으면 댓글 부탁드립니다 ^^