[SQL 코딩 테스트 대비-MySQL] JOIN

1. 상품별 오프라인 매출 구하기

[문제] 1) PRODUCT 테이블과 OFFLINE_SALE 테이블에서 2) 상품코드 별 매출액3) (판매가 * 판매량) 4) 합계를 출력하는 SQL문을 작성해주세요. 결과는 5)매출액을 기준으로 내림차순 정렬해주시고 매출액이 같다면 상품코드를 기준으로 오름차순 정렬해주세요.

 

1) 두 테이블 간 동일한 컬럼은 PRODUCT_ID가 존재하므로 이 컬럼을 기준으로 이너조인해준다.
2) 상품코드별로 산출해야 하기 때문에 상품 코드를 동일한 코드끼리 그룹으로 묶어준다.
3) 매출액을 구해야 하는 문제이기 때문에 판매가와 판매량을 곱하고
4) 곱한 값을 가진 각 행을 집계함수로 묶인 하나의 상품코드에서 하나의 행으로 나타내주기 위해 SUM 처리해준다.
5) 매출액으로 내림차순 정렬한 후 동순위는 상품코드로 오름차순 정렬한다.

SELECT PRODUCT_CODE, SUM(PRICE * SALES_AMOUNT) AS SALES 
FROM PRODUCT
INNER JOIN OFFLINE_SALE USING (PRODUCT_ID)
GROUP BY PRODUCT_CODE
ORDER BY 2 DESC, 1 ASC

2. 없어진 기록 찾기

[문제] 1) 입양을 간 기록은 있는데, 보호소에 들어온 기록이 없는 동물의 ID와 이름을 ID 순으로 조회하는 SQL문을 작성해주세요.

 

1) 보호소에 들어온 이력을 I 입양을 간 기록을 O라고 했을 때 입양 간 기록을 기준으로 보호소에 들어온 이력 정보를 합쳐주는 RIGHT JOIN을 이용해 ANIMAL_ID를 기준으로 두 테이블을 합친다. 입양 간 기록이 기준이기 때문에 보호소로 들어온 이력이 없는 경우에도 해당 행은 데이터에 남아있게 된다. INNER JOIN과 마찬가지로 조인 조건을 걸기 위해서는 ON을 사용해준다. 조건 대신 동일한 특정한 한개의 컬럼으로 조인할 때는 USING(컬럼명) 으로 작성한다.

SELECT O.ANIMAL_ID, O.NAME 
FROM ANIMAL_INS AS I
RIGHT JOIN ANIMAL_OUTS AS O
ON I.ANIMAL_ID = O.ANIMAL_ID
WHERE O.ANIMAL_ID IS NOT NULL
AND I.ANIMAL_ID IS NULL

3. 있었는데요 없었습니다

[문제] 관리자의 실수로 일부 동물의 입양일이 잘못 입력되었습니다. 1) 보호 시작일보다 입양일이 더 빠른 동물의 아이디와 이름을 조회하는 SQL문을 작성해주세요. 이때 결과는 보호 시작일이 빠른 순으로 조회해야합니다.

 

1) 보호소에 들어온 기록과 입양 간 기록 테이블을 WHERE 절로 ID가 같고 보호소 기록이 더 늦은 경우를 찾거나, 두번째 코드와 같이 두 기록이 모두 있는 동물에 대해 보호소 기록이 더 늦은 경우를 찾아주면 된다.

SELECT A.ANIMAL_ID, A.NAME 
FROM ANIMAL_INS AS A, ANIMAL_OUTS AS B
WHERE A.ANIMAL_ID = B.ANIMAL_ID
AND A.DATETIME > B.DATETIME
ORDER BY A.DATETIME

SELECT A.ANIMAL_ID, A.NAME 
FROM ANIMAL_INS AS A
INNER JOIN ANIMAL_OUTS AS B USING(ANIMAL_ID)
WHERE A.DATETIME > B.DATETIME
ORDER BY A.DATETIME

4. 오랜 기간 보호한 동물(1)

[문제] 아직 입양을 못 간 동물 중, 가장 오래 보호소에 있었던 동물 3마리의 이름과 보호 시작일을 조회하는 SQL문을 작성해주세요. 이때 결과는 보호 시작일 순으로 조회해야 합니다.

 

보호소 기록에는 있지만 입양 기록에는 없는 동물을 먼저 찾아야 한다. 이를 위해 보호소 기록을 기준으로 입양 기록을 합쳐줘야 한다. ANIMAL_ID로 LEFT JOIN을 해주면 입양 기록이 없는 동물을 찾을 수 있다. 그중에서 가장 오래 보호소에 있던 건 가장 먼저 들어온 강아지이므로 보호소에 들어온 일자를 기준으로 오름차순 정렬해주고 3건만 조회하기 위해 LIMIT 3을 작성해준다.

SELECT I.NAME, I.DATETIME
FROM ANIMAL_INS AS I
LEFT JOIN ANIMAL_OUTS AS O USING (ANIMAL_ID)
WHERE I.DATETIME IS NOT NULL
AND O.DATETIME IS NULL
ORDER BY DATETIME ASC
LIMIT 3;

5. 5월 식품들의 총매출 조회하기

[문제] FOOD_PRODUCT와 FOOD_ORDER 테이블에서 생산일자가 2022년 5월인 식품들의 식품 ID, 식품 이름, 총매출을 조회하는 SQL문을 작성해주세요. 이때 결과는 총매출을 기준으로 내림차순 정렬해주시고 총매출이 같다면 식품 ID를 기준으로 오름차순 정렬해주세요.

 

정답

SELECT PRODUCT_ID, PRODUCT_NAME, SUM(AMOUNT*PRICE) AS TOTAL_SALES
FROM FOOD_PRODUCT AS P
INNER JOIN FOOD_ORDER AS O USING (PRODUCT_ID)
WHERE O.PRODUCE_DATE LIKE "2022-05%"
GROUP BY PRODUCT_ID, PRODUCT_NAME
ORDER BY SUM(AMOUNT*PRICE) DESC, PRODUCT_ID ASC

오답 

같은 상품이라도 다른 날짜에 제조되었다면 따로 그룹지어지기 때문에 같은 상품이 여러개 조회되어 나올 수 있다. 따라서 위처럼 먼저 날짜로 필터링을 한 뒤에 그룹핑을 해주는 게 맞다.

그럼 제조일자를 그룹핑으로 하지 않으면 되지 않느냐라고 할 수 있지만 여기서 만약 HAVING 절로 필터링을 걸려고 하면 그룹핑에 해당 컬럼이 포함되지 않았기 때문에 HAVIING 절에서 사용할 수 없다. 그 이유는 HAVING 절은 GROUP BY 절 이후에 조건을 걸 때 사용되며, 그룹화된 결과에 대해 필터링을 수행하기 때문이다.

SELECT PRODUCT_ID, PRODUCT_NAME, SUM(AMOUNT*PRICE) AS TOTAL_SALES
FROM FOOD_PRODUCT AS P
INNER JOIN FOOD_ORDER AS O USING (PRODUCT_ID)
GROUP BY PRODUCT_ID, PRODUCT_NAME, PRODUCE_DATE
HAVING O.PRODUCE_DATE LIKE "2022-05%"
ORDER BY SUM(AMOUNT*PRICE) DESC, PRODUCT_ID ASC

6. 보호소에서 중성화한 동물

[문제] 보호소에서 중성화 수술을 거친 동물 정보를 알아보려 합니다. 보호소에 들어올 당시에는 중성화되지 않았지만, 보호소를 나갈 당시에는 중성화된 동물의 아이디와 생물 종, 이름을 조회하는 아이디 순으로 조회하는 SQL 문을 작성해주세요.

보호소에 들어오고 나간 기록이 모두 있는 동물이어야 하기 때문에 ANIMAL_ID로 이너 조인을 수행하고 WHERE 조건을 조금 복잡하게 걸어준다. 보호소에 들어온 시점에 중성화되어 있지 않으면서 나가는 시점에 저 두개 중 하나의 기록으로 가지고 있으면 된다.

SELECT A.ANIMAL_ID, A.ANIMAL_TYPE, A.NAME
FROM ANIMAL_INS AS A
INNER JOIN ANIMAL_OUTS AS B USING (ANIMAL_ID)
WHERE A.SEX_UPON_INTAKE LIKE 'Intact%'
AND (B.SEX_UPON_OUTCOME LIKE 'Spayed%' OR B.SEX_UPON_OUTCOME LIKE 'Neutered%');