1주차 SQL 스터디 이후, 생각보다 문제 풀이가 할 만하다라는 생각을 다들 갖고 있어서 SQL 문제풀이는 생각을 요하는 Python 코테보다는 많은 양의 문제를 풀어 다양한 함수를 빠르게 익히자 라는 결론을 갖게 되었다.
그래서 2주차부터 약 10문제 가량씩 풀어보고 풀이를 비교하고 함수를 정리하는 방향으로 스터디 계획을 세우게 되었다!💯💯
이번 2주차 계획은 SELECT문 남은 13문제 모두 풀기!
🙌 1. 학습 내용(함수 및 문법구조) 정리
- 기본적인 SQL SELECT문 구조
- SELECT COLUMNS
- FROM TABLE_NAME
- WHERE ~~ AND, OR
- GROUP BY
- HAVING
- ORDER BY
GROUP BY의 경우 WHERE문 이후 작성. WHERE 대체로 HAVING 가능.
ORDER BY는 제일 마지막에 사용하기!
DATE 형식 컬럼에 YEAR, DAY, MONTH 함수 적용 가능
count(*) 로 한 컬럼 내 row 개수 체크 가능
파이썬과 동일하게 같지 않음을 나타내는 연산자는 '!='
- UNION
: 중복을 제거하고 조회된 결과만 출력!- UNION ALL
: 2개 이상의 SQL 쿼리문을 합쳐서 하나의 결과로 보여지도록 하는 UNION 연산자
: 중복을 제거하지 않고 모든 쿼리 결과 합쳐서 출력!
- UNION ALL이 중복을 제거하지 않으므로 UNION보다 속도가 빠른 점.
- 대용량의 데이터일수록 UNION보단 이를 통합하는 것이 훨씬 더 효율적.
:: 파이썬으로 치면 MERGE가 아닌 CONCAT 느낌이 들었다.
- TABLE 및 COLUMN 명 AS로 간소화 및 컬럼명 변경하여 출력 가능!
ex) SELECT COLUMN AS A COLUMN AS B
ex) FROM TABLE AS T
🙌 2. 문제 풀이
💯 3월에 태어난 여성 회원 목록 출력하기
SELECT MEMBER_ID, MEMBER_NAME, GENDER, LEFT(DATE_OF_BIRTH,10)
FROM MEMBER_PROFILE
WHERE MONTH(DATE_OF_BIRTH) = 3
AND TLNO IS NOT NULL
AND GENDER = 'W'
ORDER BY MEMBER_ID< 풀이 과정 >
1. MONTH 함수를 통해 3월 생일자 명단 출력.
2. IS NOT NULL로 TLNO (전화번호)가 NULL인 경우 제외
3. 1주차 학습했던 LEFT함수로 DATE_FORMAT 처리
💯 강원도에 위치한 생산공장 목록 출력하기
SELECT FACTORY_ID, FACTORY_NAME, ADDRESS
FROM FOOD_FACTORY
WHERE ADDRESS LIKE '강원도%'
ORDER BY FACTORY_ID < 풀이 과정 >
1. 주소가 강원도로 시작하는 ROW만 추출
2. FACTORY_ID 기준 오름차순 정렬
💯 재구매가 일어난 상품과 회원 리스트 구하기
SELECT USER_ID, PRODUCT_ID
FROM ONLINE_SALE
GROUP BY USER_ID, PRODUCT_ID
HAVING count(*) >= 2
ORDER BY USER_ID, PRODUCT_ID DESC< 풀이 과정 >
GROUP BY - HAVING 절을 이용한 풀이
1. 회원 ID와 상품ID가 동일한 상황에서 날짜가 다른 경우 재구매한 케이스이므로, GROUP BY로 회원 ID, 상품 ID를 지정
2. 이후 해당 Length가 2개 이상인 경우 재구매한 것을 의미하므로 count(*) 사용
💯 모든 레코드 조회하기
SELECT *
FROM ANIMAL_INS
ORDER BY ANIMAL_ID< 풀이 과정 >
1. 모든 레코드를 ANIMAL_ID 오름차순으로 조회하므로 SELECT * 사용하여 코드 진행
💯 역순 정렬하기
SELECT NAME, DATETIME
FROM ANIMAL_INS
ORDER BY ANIMAL_ID DESC< 풀이 과정 >
1. 이름, 보호 시작일 컬럼 SELECT 후 동물 ID 기준 내림차순 정렬
💯 아픈 동물 찾기
SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE INTAKE_CONDITION = 'Sick'
ORDER BY ANIMAL_ID< 풀이 과정 >
1. 동물 ID, NAME SELECT 후 WHERE절 사용하여 상태가 'Sick'인 아픈 동물만 출력
2. 동물 ID 기준 오름차순 정렬
💯 어린 동물 찾기
SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE INTAKE_CONDITION != 'Aged'
ORDER BY ANIMAL_ID< 풀이 과정 >
1. 동물 ID, 이름 SELECT로 출력, 이때 상태가 'Aged'가 아니면 젊은 동물로 판단.
- MySQL에서도 != 표시가 가능한 것을 알게 됨!
💯 동물의 아이디와 이름
SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
ORDER BY ANIMAL_ID< 풀이 과정 >
단순 구현 (SELECT - FROM - ORDER BY)로 이어지는 문법 구현 가능한가? 를 물어보는 듯한 느낌을 받았다.
💯 여러 기준으로 정렬하기
SELECT ANIMAL_ID, NAME, DATETIME
FROM ANIMAL_INS
ORDER BY NAME, DATETIME DESC < 풀이 과정 >
1. 동물 ID, 이름, 보호날짜 SELECT 후 이름 알파벳 순서(오름차순), 보호를 가장 늦게 한 기준으로 출력하는 문제이므로 보호시작일은 내림차순 정렬
💯 상위 N개 레코드
SELECT NAME
FROM ANIMAL_INS
ORDER BY DATETIME
LIMIT 1< 풀이 과정 >
LIMIT 2,6 : 상위 2~6개 레코드 출력
Question) 하위 n개 레코드 출력은 어떻게?
: 정렬 순서 변경하기!
💯 조건에 맞는 회원수 구하기
SELECT count(*) AS USERS
FROM USER_INFO
WHERE AGE>=20 AND AGE<=29 AND YEAR(JOINED) = 2021< 풀이 과정 >
1. USER 수를 세기 위해 count(*) as USERS 사용
2. 2021년 가입자 추출 위해 YEAR함수 사용, 20<=AGE<=29 사용
💯 서울에 위치한 식당 목록 출력하기
SELECT REST_REVIEW.REST_ID, REST_INFO.REST_NAME, REST_INFO.FOOD_TYPE, REST_INFO.FAVORITES, REST_INFO.ADDRESS, ROUND(AVG(REST_REVIEW.REVIEW_SCORE), 2) AS SCORE
FROM REST_REVIEW
LEFT JOIN REST_INFO ON REST_INFO.REST_ID = REST_REVIEW.REST_ID
GROUP BY REST_REVIEW.REST_ID
HAVING REST_INFO.ADDRESS LIKE '서울%'
ORDER BY SCORE DESC, REST_INFO.FAVORITES DESC< 풀이 과정 >
1. 각 테이블 별 컬럼 SELECT, 이때 REVIEW_SCORE의 경우 소수점 세번째 자리에서 반올림하고 SCORE컬럼명으로 출력을 문제에서 요구했으므로, ROUND(AVG(REVIEW_SCORE), 2) AS SCORE로 변경!
2. 식당 리뷰 테이블을 뽑아오는데, LEFT JOIN으로 식당 정보 테이블 지정해주기
3. GROUP BY로 식당 리뷰 테이블 내 식당 ID 그룹화 진행
4. 이때 식당 정보에서 '서울'로 시작하는 식당만 HAVING절로 뽑아오기
5. SCORE, 즐겨찾기 순으로 내림차순 정렬 진행!
💯 오프라인/온라인 판매 데이터 통합하기
SELECT LEFT(ONL.SALES_DATE,10) AS SALES_DATE, ONL.PRODUCT_ID, ONL.USER_ID, ONL.SALES_AMOUNT
FROM ONLINE_SALE AS ONL
WHERE YEAR(ONL.SALES_DATE) = 2022 AND MONTH(ONL.SALES_DATE) = 3
UNION ALL
SELECT LEFT(OFL.SALES_DATE, 10) AS SALES_DATE, OFL.PRODUCT_ID, NULL AS USER_ID, OFL.SALES_AMOUNT
FROM OFFLINE_SALE AS OFL
WHERE YEAR(OFL.SALES_DATE) = 2022 AND MONTH(OFL.SALES_DATE) = 3
ORDER BY SALES_DATE, PRODUCT_ID, USER_ID< 풀이 과정 >
LEFT 함수 적용 후 컬럼명을 AS로 지정해주어야 한다...😭 이거때매 지웠다 풀었다만 몇번을 했는지... 추가로 JOIN문으로 해결이 안되어 구글링을 통해 UNION 연산자에 대해 알게 되었다!
- 문제를 보고 ONLINE, OFFLINE 연관되는 KEY 값이 없어 JOIN이 아닌 UNION으로 문제 풀이 접근 >>>> 온라인 / 오프라인 으로 2개의 쿼리문 작성
- 각 TABLE을 불러온 이후 판매날짜, 상품ID, 유저ID, 판매량 출력. 이때 오프라인의 경우 유저 ID가 공란이므로 NULL로 불러오기!
- WHERE절 이용하여 2022년 3월만 출력 (YEAR, MONTH 함수 적용)
- 판매날짜, 상품ID, 유저ID 순서대로 오름차순 정렬 진행하기.
🙌 3. 참고 사항
UNION 연산자 정리
https://jmkim.tistory.com/50
위 작성자분 덕분에 UNION 연산자 이해를 쉽게 할 수 있었고, MySQL 내부에서 어떤 방식으로 해당 연산자가 작동하는지 알 수 있었다.
🙌 4. week 2 study review
음,, 아무래도 1주차 study에서 느낀 그대로 SQL은 함수를 얼마나 많이 잘 활용해서 쓸 수 있는가를 중점으로 공부를 진행해야할 것 같다.
특히 데이터 형식 변경(날짜, 숫자), JOIN문법 및 GROUP BY - HAVING 절 등등 기본적인 SQL 문법구조와 함수 형태를 2달 동안 익혀놔야 할 듯 하다!
