2주차 학습 회고 시작!
👩💻코드카타
<이번주에 푼 SQL>
조건에 맞는 회원수 구하기
흉부외과 또는 일반외과의사 목록 출력하기
강원도에 위치한 생산공장 목록 출력하기
12세 이하인 여자 환자 목록 출력하기
모든 레코드 조회하기
재구매가 일어난 상품과 회원 리스트 구하기
가장 비싼 상품 구하기
가격이 제일 비싼 식품의 정보 출력하기
최솟값 구하기
진료과별 총 예약 횟수 출력하기
5월 식품들의 총매출 조회하기
고양이와 개는 몇 마리 있을까
가격대 별 상품 개수 구하기
경기도에 위치한 식품창고 목록 출력하기
이름이 없는 동물의 아이디
나이 정보가 없는 회원 수 구하기
잡은 물고기의 평균 길이 구하기
조건에 부합하는 중고거래 댓글 조회하기
🤔인사이트💡
라이브 3,4,5회차를 복습하면서
과제를 풀어봤는데 쉽지 않았다!
특히 라이브 세션 5회차 마지막 문제는 조건도 많고 조인, subquery 등 배운 거 다 떄려박아서 푸는 느낌이라..오래 걸렸다.
오늘 푼 코드카타와 가장 어려웠던 라이브세션 과제를 기록하며 알게 된 인사이트를 정리해보겠다.
SQL코드카타[JOIN]
오랜 기간 보호한 동물(1)
ANIMAL_INS 테이블은 동물 보호소에 들어온 동물의 정보를 담은 테이블입니다. ANIMAL_INS 테이블 구조는 다음과 같으며, ANIMAL_ID, ANIMAL_TYPE, DATETIME, INTAKE_CONDITION, NAME, SEX_UPON_INTAKE는 각각 동물의 아이디, 생물 종, 보호 시작일, 보호 시작 시 상태, 이름, 성별 및 중성화 여부를 나타냅니다.
ANIMAL_OUTS 테이블은 동물 보호소에서 입양 보낸 동물의 정보를 담은 테이블입니다. ANIMAL_OUTS 테이블 구조는 다음과 같으며, ANIMAL_ID, ANIMAL_TYPE, DATETIME, NAME, SEX_UPON_OUTCOME는 각각 동물의 아이디, 생물 종, 입양일, 이름, 성별 및 중성화 여부를 나타냅니다. ANIMAL_OUTS 테이블의 ANIMAL_ID는 ANIMAL_INS의 ANIMAL_ID의 외래 키입니다.
Q. 아직 입양을 못 간 동물 중, 가장 오래 보호소에 있었던 동물 3마리의 이름과 보호 시작일을 조회하는 SQL문을 작성해주세요. 이때 결과는 보호 시작일 순으로 조회해야 합니다.
👉조회 시 보여야 하는 데이터 : 동물의 이름(NAME), 보호시작일(DATETIME)
✔️테이블명 : ANIMAL_INS 와 ANIMAL_OUTS 조인
✔️조건 : 아직 입양을 못 간 동물(ANIMAL_INS에는 있지만 ANIMAL_OUTS에는 없는 동물) => WHERE ANIMAL_OUTS IS NULL
✔️조건2 : 가장 오래 보호소에 있던 동물(ORDER BY ANIMAL_TIME
✔️조건3 : 창고 ID 기준 오름차순(ORDER BY ASC)
✔️조건4 : 3마리 조회(LIMIT 3)
SELECT AI.NAME ANIMAL_NAME
,AI.DATETIME ANIMAL_TIME
FROM ANIMAL_INS AI
LEFT JOIN ANIMAL_OUTS AO
ON AI.ANIMAL_ID=AO.ANIMAL_ID
WHERE AO.ANIMAL_ID IS NULL
ORDER BY ANIMAL_TIME
LIMIT 3;
조건에 맞는 사용자와 총 거래금액 조회하기
다음은 중고 거래 게시판 정보를 담은 USED_GOODS_BOARD 테이블과 중고 거래 게시판 사용자 정보를 담은 USED_GOODS_USER 테이블입니다. USED_GOODS_BOARD 테이블은 다음과 같으며 BOARD_ID, WRITER_ID, TITLE, CONTENTS, PRICE, CREATED_DATE, STATUS, VIEWS는 게시글 ID, 작성자 ID, 게시글 제목, 게시글 내용, 가격, 작성일, 거래상태, 조회수를 의미합니다.
USED_GOODS_USER 테이블은 다음과 같으며 USER_ID, NICKNAME, CITY, STREET_ADDRESS1, STREET_ADDRESS2, TLNO는 각각 회원 ID, 닉네임, 시, 도로명 주소, 상세 주소, 전화번호를 를 의미합니다.
Q. USED_GOODS_BOARD와 USED_GOODS_USER 테이블에서 완료된 중고 거래의 총금액이 70만 원 이상인 사람의 회원 ID, 닉네임, 총거래금액을 조회하는 SQL문을 작성해주세요. 결과는 총거래금액을 기준으로 오름차순 정렬해주세요.
👉조회 시 보여야 하는 데이터 : 중고거래 이용 회원 ID, 닉네임, 총거래금액
✔️테이블명 : USED_GOODS_BOARD 와 USED_GOODS_USER 테이블 LEFT JOIN
✔️조건 : 완료된 중고거래(WHERE ~STATUS = 'DONE')
✔️조건2 : 총 금액이 70만 원 이상인 사람(HAVING ~ SUM(PRICE) >=700000)
✔️조건3 : 총 거래금액 기준 오름차순(ORDER BY ASC)
SELECT UGU.USER_ID,
UGU.NICKNAME,
SUM(UGB.PRICE) AS PRICE_SUM
FROM USED_GOODS_BOARD UGB
LEFT JOIN USED_GOODS_USER UGU
ON UGB.WRITER_ID=UGU.USER_ID
WHERE UGB.STATUS='DONE'
GROUP BY 1
HAVING PRICE_SUM >= 700000
ORDER BY 3
보호소에서 중성화한 동물
ANIMAL_INS 테이블은 동물 보호소에 들어온 동물의 정보를 담은 테이블입니다. ANIMAL_INS 테이블 구조는 다음과 같으며, ANIMAL_ID, ANIMAL_TYPE, DATETIME, INTAKE_CONDITION, NAME, SEX_UPON_INTAKE는 각각 동물의 아이디, 생물 종, 보호 시작일, 보호 시작 시 상태, 이름, 성별 및 중성화 여부를 나타냅니다.
ANIMAL_OUTS 테이블은 동물 보호소에서 입양 보낸 동물의 정보를 담은 테이블입니다. ANIMAL_OUTS 테이블 구조는 다음과 같으며, ANIMAL_ID, ANIMAL_TYPE, DATETIME, NAME, SEX_UPON_OUTCOME는 각각 동물의 아이디, 생물 종, 입양일, 이름, 성별 및 중성화 여부를 나타냅니다. ANIMAL_OUTS 테이블의 ANIMAL_ID는 ANIMAL_INS의 ANIMAL_ID의 외래 키입니다.
Q. 보호소에서 중성화 수술을 거친 동물 정보를 알아보려 합니다. 보호소에 들어올 당시에는 중성화1되지 않았지만, 보호소를 나갈 당시에는 중성화된 동물의 아이디와 생물 종, 이름을 조회하는 아이디 순으로 조회하는 SQL 문을 작성해주세요.
👉조회 시 보여야 하는 데이터 : 동물의 아이디(ANIMAL_ID), 생물 종(ANIMAL_TYPE), 이름(NAME)
✔️테이블명 : ANIMAL_INS 와 ANIMAL_OUTS LEFT JOIN
✔️조건 : 보호소에 들어올 당시에는 중성화되지 X(WHERE ~ SEX_UPON_INTAKE LIKE 'Intact%')
✔️조건2 : 보호소를 나갈 당시에는 중성화된 (WHERE ~ SEX_UPON_OUTCOME NOT LIKE 'Intact%')
✔️조건3 : 동물의 아이디 기준 오름차순(ORDER BY ASC)
SELECT AI.ANIMAL_ID
,AI.ANIMAL_TYPE
,AI.NAME
FROM ANIMAL_INS AI
LEFT JOIN ANIMAL_OUTS AO
ON AI.ANIMAL_ID=AO.ANIMAL_ID
WHERE (AI.SEX_UPON_INTAKE like 'Intact%') AND AO.SEX_UPON_OUTCOME NOT LIKE 'Intact%'
라이브세션 5회차 과제
JOIN 응용2
조건1) user 테이블에서 game_account_id, first_login_date, serverno 를 추출한 결과와
조건2) payment 테이블에서 game_account_id 별 가장 마지막 결제일자를 찾고 그 컬럼이름을 date2로 지정해주세요. 그 다음 inner join 을 진행해주세요. 다만, 첫 접속일자보다 마지막 결제일자가 큰 경우만 추출해주세요.
조건3) 조인 결과를 바탕으로 마지막 결제일자-첫 접속일자 를 구해주세요. 그리고 컬럼이름을 diffdate로 설정해주세요. 두 날짜의 형식은 같아야 합니다.
조건4) 인라인 뷰 subquery 를 이용하여 서버별 평균 diffdate를 구해주시고, 컬럼이름을avgdiffdate로 설정해주세요. 해당컬럼은 정수 형태로 출력되어야 합니다.
조건5) 조건절에 diffdate 값이 10일 이상인 경우를 필터링해주세요. 그리고 서버번호를 기준으로 내림차순 정렬해주세요.
힌트) 소수점을 반올림해주는 round 함수를 활용해주세요!
👉조건이 많아 차근차근 풀어보자면,
1️⃣ 조건1과 조건2 해결하기
SELECT u.game_account_id,
u.first_login_date,
u.serverno,
p.date2
FROM sparta.users u
INNER JOIN (
SELECT game_account_id,
MAX(approved_at) AS date2 👈 가장 마지막 결제일 찾는 ✅
FROM sparta.payment
GROUP BY game_account_id
) p
ON u.game_account_id = p.game_account_id;
2️⃣ 첫 접속일자보다 마지막 결제일자가 큰 경우?
SELECT u.game_account_id,
u.first_login_date,
u.serverno,
p.date2
FROM sparta.users u
INNER JOIN (
SELECT game_account_id,
MAX(approved_at) AS date2
FROM sparta.payment
GROUP BY game_account_id
) p
ON u.game_account_id = p.game_account_id
WHERE u.first_login_date < p.date2; 👈 가장 마지막 결제일 찾는 ✅
3️⃣ 마지막 결제일 - 첫 접속일자 (diffdate)?
SELECT u.game_account_id,
u.first_login_date,
u.serverno,
p.date2,
DATEDIFF(p.date2, u.first_login_date) AS diffdate 👈 diffdate 계산하는 ✅
FROM sparta.users u
INNER JOIN (
SELECT game_account_id,
MAX(approved_at) AS date2
FROM sparta.payment
GROUP BY game_account_id
) p
ON u.game_account_id = p.game_account_id
WHERE u.first_login_date < p.date2;
4️⃣ 서버별 평균 diffdate 계산 -> avgdiffdate 생성!
SELECT serverno,
ROUND(AVG(diffdate)) AS avgdiffdate 👈 평균 diffdate 정수로 변환하는 ✅
FROM (
SELECT u.game_account_id,
u.first_login_date,
u.serverno,
p.date2,
DATEDIFF(p.date2, u.first_login_date) AS diffdate
FROM sparta.users u
INNER JOIN (
SELECT game_account_id,
MAX(approved_at) AS date2
FROM sparta.payment
GROUP BY game_account_id
) p
ON u.game_account_id = p.game_account_id
WHERE u.first_login_date < p.date2
) AS subquery
GROUP BY serverno;
5️⃣ diffdate가 10일 이상인 경우만 필터링 & 서버번호 기준 정렬
SELECT serverno,
ROUND(AVG(diffdate)) AS avgdiffdate
FROM (
SELECT u.game_account_id,
u.first_login_date,
u.serverno,
p.date2,
DATEDIFF(p.date2, u.first_login_date) AS diffdate
FROM sparta.users u
INNER JOIN (
SELECT game_account_id,
MAX(approved_at) AS date2
FROM sparta.payment
GROUP BY game_account_id
) p
ON u.game_account_id = p.game_account_id
WHERE u.first_login_date < p.date2
)AS subquery
WHERE diffdate >= 10 👈 diffdate 10 이상인 경우만 필터링✅
GROUP BY serverno
ORDER BY serverno DESC; 👈 서버번호 기준 내림차순 정렬✅
💡인사이트
