[SQL] 프로그래머스 7 Level 2

문제설명

 

대장균들은 일정 주기로 분화하며, 분화를 시작한 개체를 부모 개체, 분화가 되어 나온 개체를 자식 개체라고 합니다.
다음은 실험실에서 배양한 대장균들의 정보를 담은 ECOLI_DATA 테이블입니다. ECOLI_DATA 테이블의 구조는 다음과 같으며, ID, PARENT_ID, SIZE_OF_COLONY, DIFFERENTIATION_DATE, GENOTYPE 은 각각 대장균 개체의 ID, 부모 개체의 ID, 개체의 크기, 분화되어 나온 날짜, 개체의 형질을 나타냅니다.

Column nameTypeNullable

ID	INTEGER	FALSE
PARENT_ID	INTEGER	TRUE
SIZE_OF_COLONY	INTEGER	FALSE
DIFFERENTIATION_DATE	DATE	FALSE
GENOTYPE	INTEGER	FALSE

최초의 대장균 개체의 PARENT_ID 는 NULL 값입니다.

---

문제

부모의 형질을 모두 보유한 대장균의 ID(ID), 대장균의 형질(GENOTYPE), 부모 대장균의 형질(PARENT_GENOTYPE)을 출력하는 SQL 문을 작성해주세요. 이때 결과는 ID에 대해 오름차순 정렬해주세요.

 

SELECT
    e.ID,
    e.GENOTYPE,
    p.GENOTYPE AS PARENT_GENOTYPE
FROM
    ECOLI_DATA e
INNER JOIN
    ECOLI_DATA p ON e.PARENT_ID = p.ID
WHERE
    (e.GENOTYPE & p.GENOTYPE) = p.GENOTYPE
ORDER BY
    e.ID;

1. SELECT e.ID, e.GENOTYPE, p.GENOTYPE AS PARENT_GENOTYPE
   • ECOLI_DATA 테이블에서 ID와 GENOTYPE 컬럼을 선택하고, 부모의 GENOTYPE을 PARENT_GENOTYPE으로 별칭을 지정하여 추출한다.
2. FROM ECOLI_DATA e
   • 데이터를 추출할 테이블로 ECOLI_DATA를 지정하고, 이 테이블을 e라는 별칭으로 참조한다.
3. INNER JOIN ECOLI_DATA p ON e.PARENT_ID = p.ID
   • ECOLI_DATA 테이블을 p라는 별칭으로 다시 참조하고, e의 PARENT_ID와 p의 ID가 같은 레코드끼리 내부 조인한다. 이는 각 대장균 데이터가 부모 데이터와 어떻게 연결되는지 정의한다.
4. WHERE (e.GENOTYPE & p.GENOTYPE) = p.GENOTYPE
   • 비트 AND 연산자(&)를 사용하여 e.GENOTYPE과 p.GENOTYPE의 비트별 AND 연산 결과가 p.GENOTYPE과 동일한 경우만 필터링한다. 이 조건은 e.GENOTYPE이 p.GENOTYPE의 모든 비트를 포함할 때만 참이 된다.
5. ORDER BY e.ID
   • 결과를 e.ID 컬럼 기준으로 오름차순으로 정렬한다.

대장균 데이터셋에서 각 대장균 샘플이 그것의 부모 샘플의 유전형을 완전히 포함하는 경우에 해당하는 정보를 추출하고 정렬하는 데 사용

 

As = 별칭을 정할때 사용 

ON = 조인할때 연결 칼럼을 정할때 사용

where = 필터링할때 사용 

& = 모두~이어야 한다

문제설명

HR_DEPARTMENT 테이블은 회사의 부서 정보를 담은 테이블입니다. HR_DEPARTMENT 테이블의 구조는 다음과 같으며 DEPT_ID, DEPT_NAME_KR, DEPT_NAME_EN, LOCATION은 각각 부서 ID, 국문 부서명, 영문 부서명, 부서 위치를 의미합니다.

Column nameTypeNullable

DEPT_ID	VARCHAR	FALSE
DEPT_NAME_KR	VARCHAR	FALSE
DEPT_NAME_EN	VARCHAR	FALSE
LOCATION	VARCHAR	FLASE

HR_EMPLOYEES 테이블은 회사의 사원 정보를 담은 테이블입니다. HR_EMPLOYEES 테이블의 구조는 다음과 같으며 EMP_NO, EMP_NAME, DEPT_ID, POSITION, EMAIL, COMP_TEL, HIRE_DATE, SAL은 각각 사번, 성명, 부서 ID, 직책, 이메일, 전화번호, 입사일, 연봉을 의미합니다.

Column nameTypeNullable

EMP_NO	VARCHAR	FALSE
EMP_NAME	VARCHAR	FALSE
DEPT_ID	VARCHAR	FALSE
POSITION	VARCHAR	FALSE
EMAIL	VARCHAR	FALSE
COMP_TEL	VARCHAR	FALSE
HIRE_DATE	DATE	FALSE
SAL	NUMBER	FALSE

HR_GRADE 테이블은 2022년 사원의 평가 정보를 담은 테이블입니다. HR_GRADE의 구조는 다음과 같으며 EMP_NO, YEAR, HALF_YEAR, SCORE는 각각 사번, 연도, 반기, 평가 점수를 의미합니다.

Column nameTypeNullable

EMP_NO	VARCHAR	FALSE
YEAR	NUMBER	FALSE
HALF_YEAR	NUMBER	FALSE
SCORE	NUMBER	FALSE

 

---

문제

HR_DEPARTMENT, HR_EMPLOYEES, HR_GRADE 테이블에서 2022년도 한해 평가 점수가 가장 높은 사원 정보를 조회하려 합니다. 2022년도 평가 점수가 가장 높은 사원들의 점수, 사번, 성명, 직책, 이메일을 조회하는 SQL문을 작성해주세요.

2022년도의 평가 점수는 상,하반기 점수의 합을 의미하고, 평가 점수를 나타내는 컬럼의 이름은 SCORE로 해주세요.

-- 2022년도에 대한 각 사원의 평가 점수 합산
WITH TotalScores AS (
    SELECT
        g.EMP_NO,
        SUM(g.SCORE) AS SCORE
    FROM
        HR_GRADE g
    WHERE
        g.YEAR = 2022
    GROUP BY
        g.EMP_NO
),

-- 최고 점수 계산
MaxScore AS (
    SELECT
        MAX(SCORE) AS SCORE
    FROM
        TotalScores
)

-- 최고 점수를 받은 사원들의 정보 조회
SELECT
    ts.SCORE,
    e.EMP_NO,
    e.EMP_NAME,
    e.POSITION,
    e.EMAIL
FROM
    TotalScores ts
JOIN
    HR_EMPLOYEES e ON ts.EMP_NO = e.EMP_NO
JOIN
    MaxScore ms ON ts.SCORE = ms.SCORE
ORDER BY
    e.EMP_NO;

 

1. TotalScores CTE:
   • 이 CTE는 HR_GRADE 테이블에서 각 사원의 2022년도 평가 점수를 합산한다. 여기서 SUM(g.SCORE)를 사용하여 각 사원별로 연간 총점을 계산한다.
2. MaxScore CTE:
   • 이 CTE는 TotalScores에서 계산된 점수 중 최고 점수를 찾는다. 이 최고 점수는 가장 높은 평가를 받은 사원(또는 사원들)을 결정하는 데 사용된다.
3. 최종 SELECT 문:
   • TotalScores와 HR_EMPLOYEES 테이블을 조인하여 각 사원의 세부 정보(사번, 이름, 직책, 이메일)를 가져온다. 이때 조인 조건은 ts.EMP_NO = e.EMP_NO이다.
   • MaxScore와의 조인을 통해 최고 점수와 동일한 점수를 가진 사원들만 선택된다. 결과는 사원 번호로 정렬되어 표시된다.

특정 조건에 맞는 데이터를 추출하고 필터링하는 데 필요한 모든 요소를 포함하고 있으며, 가장 높은 점수를 받은 사원들의 정보를 효과적으로 추출

 

CTE는 "Common Table Expression"의 약자이다. 이는 SQL에서 임시 결과 집합을 정의하는 방법으로, 해당 집합은 SQL 문 내에서 하나의 실행 시간 동안만 존재한다

CTE는 WITH 절을 사용하여 정의