SQLD 자격검증실전문제 91번 문제

SQLD 자격검증실전문제(일명 주황책) 기반

p96의 91번 문제

우선 booseo 테이블을 생성합니다. 이 테이블은 부서를 관리하기 위한 테이블입니다.

CREATE TABLE booseo(
	code CHAR(3),
	name VARCHAR(18),
	upcode CHAR(3),
	CONSTRAINT boose_pk PRIMARY KEY (code),
	CONSTRAINT booseo_fk FOREIGN KEY (upcode) REFERENCES booseo (code)
);

이제 테이블에 데이터를 입력합니다.

INSERT INTO booseo VALUES ('100', '아시아부', NULL);
INSERT INTO booseo VALUES ('110', '한국지사', '100');
INSERT INTO booseo VALUES ('111', '서울지점', '110');
INSERT INTO booseo VALUES ('112', '부산지점', '110');
INSERT INTO booseo VALUES ('120', '일본지사', '100');
INSERT INTO booseo VALUES ('121', '도쿄지점', '120');
INSERT INTO booseo VALUES  ('122', '오사카지점', '120');
INSERT INTO booseo VALUES ('130', '중국지사', '100');
INSERT INTO booseo VALUES ('131', '베이징지점', '130');
INSERT INTO booseo VALUES ('132', '상하이지점', '130');
INSERT INTO booseo VALUES ('200', '남유럽지부', NULL);
INSERT INTO booseo VALUES ('210', '스페인지사', '200');
INSERT INTO booseo VALUES ('211', '마드리드지점', '210');
INSERT INTO booseo VALUES ('212', '그라나다지점', '210');
INSERT INTO booseo VALUES ('220', '포르투갈지사', '200');
INSERT INTO booseo VALUES ('221', '리스본지점', '220');
INSERT INTO booseo VALUES ('222', '포르투지점', '220');

 

다음으로는 maechool 테이블을 생성합니다. 이 테이블은 부서별 매출을 저장하기 위한 테이블입니다.

CREATE TABLE maechool(
	code CHAR(3),
	amount NUMBER
);

maechool 테이블에 데이터를 입력합니다.

INSERT INTO maechool VALUES ('111', 1000);
INSERT INTO maechool VALUES ('112', 2000);
INSERT INTO maechool VALUES ('121', 1500);
INSERT INTO maechool VALUES ('122', 1000);
INSERT INTO maechool VALUES ('131', 1500);
INSERT INTO maechool VALUES ('132', 2000);
INSERT INTO maechool VALUES ('211', 2000);
INSERT INTO maechool VALUES ('212', 1500);
INSERT INTO maechool VALUES ('222', 2000);

 

1번

SELECT A.code, A.name, A.upcode, B.amount, LVL
FROM (SELECT code, name, upcode, LEVEL AS LVL    --- (1)번 SELECT 문
	  FROM booseo
      START WITH code = '120'
      CONNECT BY PRIOR upcode = code
      UNION
      SELECT code, name, upcode, LEVEL AS LVL    --- (2)번 SELECT 문
      FROM booseo
      START WITH code = '120'
      CONNECT BY upcode = PRIOR code) A LEFT OUTER JOIN maechool B ON (A.code = B.code)
ORDER BY A.code;

 

1번 결과

해설:

먼저, START WITH 절에 '120'을 지정하여 일본지사를 루트로 하는 부서 트리를 생성합니다. 그 다음, CONNECT BY 절에서 PRIOR upcode = code로 지정하여 부모 노드의 code와 자식 노드의 upcode가 서로 연결되도록 합니다. 이렇게 하면 일본지사를 루트로 하는 부서 트리에서 일본지사 밑에 있는 모든 지점이 나열됩니다.

그리고 UNION으로 두 개의 SELECT 문을 연결합니다. 첫 번째 SELECT 문은 CONNECT BY PRIOR upcode = code로 연결된 트리를 생성하며, 두 번째 SELECT 문은 CONNECT BY upcode = PRIOR code로 연결된 트리를 생성합니다. 이렇게 하면 일본지사 밑에 있는 모든 부서가 나열됩니다.

마지막으로, 부서 정보를 담고 있는 A 테이블과 부서별 매출 정보를 담고 있는 B 테이블을 LEFT OUTER JOIN으로 조인하면서, 부서별 매출 정보가 없는 경우에도 부서 정보를 출력할 수 있도록 합니다. 결과적으로 일본지사 밑에 있는 도쿄지점과 오사카지점의 매출 정보만 출력됩니다.

 

 

2번

SELECT A.code, A.name, A.upcode, B.amount, LVL
FROM (SELECT code, name, upcode, LEVEL AS LVL
          FROM booseo
          START WITH code = '100'
          CONNECT BY upcode = PRIOR code) A LEFT OUTER JOIN maechool B
ON (A.code = B.code)
ORDER BY A.code;

 

2번 결과

 

해당 쿼리는 booseo 테이블의 계층 구조를 조회하고, START WITH와 CONNECT BY 구문을 사용하여 부모-자식 관계를 계층적으로 연결하고 있습니다. 그리고 LEFT OUTER JOIN을 사용하여 부서별 판매 금액 정보를 매칭하고 있습니다. ORDER BY 구문을 통해 부서 코드(CODE) 순으로 정렬되어 출력됩니다.

 

3번

SELECT A.code, A.name, A.upcode, B.amount, LVL
FROM (SELECT code, name, upcode, LEVEL AS LVL
          FROM booseo
          START WITH code = '121'
          CONNECT BY PRIOR upcode = code) A LEFT OUTER JOIN maechool B
ON (A.code = B.code)
ORDER BY A.code;

 

3번 결과

 

4번

SELECT A.code, A.name, A.upcode, B.amount, LVL       
FROM (SELECT code, name, upcode, LEVEL AS LVL         
          FROM booseo
          START WITH code = (SELECT code               
                                       FROM booseo
                                      WHERE upcode IS NULL
                                      START WITH code = '120'
                                       CONNECT BY PRIOR upcode = code)
          CONNECT BY upcode = PRIOR code) A LEFT OUTER JOIN maechool B
ON (A.code = B.code)
ORDER BY A.code;

 

4번 결과

 

이 쿼리는 "booseo" 테이블을 기반으로 결과를 생성합니다.

먼저, 서브쿼리 (SELECT code FROM booseo WHERE upcode IS NULL START WITH code = '120' CONNECT BY PRIOR upcode = code)를 사용하여 루트 코드를 찾습니다. 이 경우 루트 코드는 '120'이며 이 코드가 부서의 가장 상위 수준이라고 가정합니다.

그 다음, START WITH절을 사용하여 루트 코드에서 시작하고 CONNECT BY절을 사용하여 계층 구조를 따라 모든 하위 부서를 검색합니다. LEVEL 키워드를 사용하여 계층 구조의 각 레벨에 대한 값을 포함하는 "LVL" 열을 생성합니다.

그런 다음 LEFT OUTER JOIN을 사용하여 "maechool" 테이블과 "booseo" 테이블을 조인합니다. 이는 매출 정보가 있는 부서에 대해서만 매출 정보를 반환하기 위함입니다. 부서가 매출 정보가 없으면 결과에는 NULL값이 포함됩니다.

최종 결과는 "booseo" 테이블의 코드, 이름, 상위 코드, 매출 정보 및 레벨 정보를 포함합니다. 그리고 부서의 계층 구조에 따라 코드 순서대로 정렬됩니다.