어려웠던 / 다시 보면 좋을 문제들만 모아서
1. 함수, 기초 문법 관련
🔎 IFNULL(컬럼명,'변경할 값')
IS NULL / IS NOT NULL
IFNULL(컬럼명,'변경할 값')
lv1. IS NULL : 경기도에 위치한 식품창고 목록 출력하기
FOOD_WAREHOUSE 테이블에서 경기도에 위치한 창고의 ID, 이름, 주소, 냉동시설 여부를 조회하는 SQL문을 작성해주세요. 이때 냉동시설 여부가 NULL인 경우, 'N'으로 출력시켜 주시고 결과는 창고 ID를 기준으로 오름차순 정렬해주세요.
SELECT WAREHOUSE_ID, WAREHOUSE_NAME, ADDRESS,
IFNULL(FREEZER_YN, "N") AS FREEZER_YN
FROM FOOD_WAREHOUSE
WHERE ADDRESS LIKE '경기도%'
ORDER BY WAREHOUSE_ID
/*MYSQL에 ISNULL()은 없다.
IS NULL과 IFNULL()이 있을 뿐...
이것 때문에 10분 넘게 고민했다 흑흑.*/🔎 sql 구문 사용 순서
1.FROM
2.WHERE : 테이블 조건에 맞는 데이터 추출
3.GROUP BY
4.HAVING: GROUP BY에 대한 조건
5.SELECT
6.ORDER BYSELECT와 ORDER BY안에 다른 조건들이 들어가야 함.
lv2. GROUP BY : 동명 동물 수 찾기
동물 보호소에 들어온 동물 이름 중 두 번 이상 쓰인 이름과 해당 이름이 쓰인 횟수를 조회하는 SQL문을 작성해주세요. 이때 결과는 이름이 없는 동물은 집계에서 제외하며, 결과는 이름 순으로 조회해주세요.
SELECT NAME, COUNT(NAME) AS COUNT
FROM ANIMAL_INS
GROUP BY NAME
HAVING COUNT(*)>=2 AND NAME IS NOT NULL
ORDER BY NAME
/*중요. sql 구문 사용 순서
1.FROM 2.WHERE : 테이블 조건에 맞는 데이터 추출 3.GROUP BY 4.HAVING: GROUP BY에 대한 조건 5.SELECT 6.ORDER BY
-> SELECT와 ORDER BY안에 다른 조건들이 들어가야 함.
*/🔎 동일한 칼럼에서 여러 내용을 찾을 때
컬럼명 IN ('찾을 값1','찾을 값2'…)
lv2. String, Date : 루시와 엘라 찾기
동물 보호소에 들어온 동물 중 이름이 Lucy, Ella, Pickle, Rogan, Sabrina, Mitty인 동물의 아이디와 이름, 성별 및 중성화 여부를 조회하는 SQL 문을 작성해주세요.
SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS **텍스트**
WHERE NAME IN ('Lucy','Ella','Pickle','Rogan', 'Sabrina', 'Mitty')
ORDER BY ANIMAL_ID
#동일한 칼럼에서 여러 내용을 찾을 때는 IN 함수를 사용하면 됨!🔎 SELECT하는 내용을 바꿔서 조회 - CASE ~ WHEN
CASE
WHEN 조건식1 THEN 내용1(식1)
WHEN 조건식2 THEN 내용2(식2)
...
ELSE 내용3(식3)
END AS '컬럼명'
lv2. String, Date : 중성화 여부 파악하기
동물 보호소에 동물이 몇 마리 들어왔는지 조회하는 SQL 문을 작성해주세요.
SELECT ANIMAL_ID, NAME,
CASE
WHEN SEX_UPON_INTAKE LIKE 'Spayed%' THEN 'O'
WHEN SEX_UPON_INTAKE LIKE 'Neutered%' THEN 'O'
ELSE 'X'
END AS '중성화'
FROM ANIMAL_INS
ORDER BY ANIMAL_ID
/*SELECT하는 내용을 바꿔서 조회하고 싶을 때는 CASE WHEN 사용.
CASE
WHEN 조건식1 THEN 내용1(식1)
WHEN 조건식2 THEN 내용2(식2)
...
ELSE 식3
END AS '컬럼명'*/🔎 DATE_FORMAT() 함수로 날짜 변환
DATE_FORMAT(컬럼명, '표현 형식')
가장 많이 쓰는 표현 형식은 '%Y-%m-%d'
lv2. String, Date : DATETIME에서 DATE로 형 변환
ANIMAL_INS 테이블에 등록된 모든 레코드에 대해, 각 동물의 아이디와 이름, 들어온 날짜(시각(시-분-초)을 제외한 날짜(년-월-일)만 보여주세요. )를 조회하는 SQL문을 작성해주세요. 이때 결과는 아이디 순으로 조회해야 합니다.
SELECT ANIMAL_ID, NAME,
DATE_FORMAT(DATETIME, '%Y-%m-%d') AS "날짜"
FROM ANIMAL_INS
ORDER BY ANIMAL_ID
/*
DATETIME형태는 DATE_FORMAT()함수를 통해 변환 가능함.
DATE_FORMAT(컬럼명, "표현 형식")
자세한 사항은 https://kig6022.tistory.com/7
*/
🔎 문자열 자르기 함수
SUBSTRING(문자열, 시작위치, 길이)
: 지정한 위치에서 지정한 문자열 길이만큼 자르기
LEFT(문자열, 길이)
: 왼쪽에서부터 지정한 문자열의 길이만큼 자르기
RIGHT(문자열, 길이)
: 오른쪽에서부터 지정한 문자열의 길이만큼 자르기
lv2. String, Date : 카테고리 별 상품 개수 구하기
PRODUCT 테이블에서 상품 카테고리 코드(PRODUCT_CODE 앞 2자리) 별 상품 개수를 출력하는 SQL문을 작성해주세요. 결과는 상품 카테고리 코드를 기준으로 오름차순 정렬해주세요.
SELECT LEFT (PRODUCT_CODE, 2) AS CATEGORY,
COUNT(*) AS PRODUCTS
FROM PRODUCT
GROUP BY CATEGORY
ORDER BY PRODUCT_CODE
/*
문자열 자르기 함수
SUBSTRING(문자열, 시작위치, 길이) : 지정한 위치에서 지정한 문자열 길이만큼 자르기
LEFT(문자열, 길이) : 왼쪽에서부터 지정한 문자열의 길이만큼 자르기
RIGHT(문자열, 길이) : 오른쪽에서부터 지정한 문자열의 길이만큼 자르기
*/🔎 중복값 찾기
lv2. SELECT : 재구매가 일어난 상품과 회원 리스트 구하기
ONLINE_SALE 테이블에서 동일한 회원이 동일한 상품을 재구매한 데이터를 구하여, 재구매한 회원 ID와 재구매한 상품 ID를 출력하는 SQL문을 작성해주세요. 결과는 회원 ID를 기준으로 오름차순 정렬해주시고 회원 ID가 같다면 상품 ID를 기준으로 내림차순 정렬해주세요.
SELECT USER_ID, PRODUCT_ID
FROM ONLINE_SALE
GROUP BY USER_ID, PRODUCT_ID
HAVING COUNT(*)>1
ORDER BY USER_ID, PRODUCT_ID DESC
/* 여러 행 안에서 중복값 찾아내기
https://kimsyoung.tistory.com/entry/SQL%EC%97%90%EC%84%9C-%EC%A4%91%EB%B3%B5%EA%B0%92-%EC%B0%BE%EB%8A%94-%EB%B0%A9%EB%B2%95
*/🔎 최대/최소값 찾기 - 상위(하위)N개 추출
ORDER BY ~ (ASC-상위/DESC-하위)
LIMIT 숫자
lv3. JOIN : 오랜 기간 보호한 동물(1)
아직 입양을 못 간 동물 중, 가장 오래 보호소에 있었던 동물 3마리의 이름과 보호 시작일을 조회하는 SQL문을 작성해주세요. 이때 결과는 보호 시작일 순으로 조회해야 합니다.
SELECT I.NAME, I.DATETIME
FROM ANIMAL_INS AS I
LEFT OUTER JOIN ANIMAL_OUTS AS O
ON I.ANIMAL_ID = O.ANIMAL_ID
WHERE O.ANIMAL_ID IS NULL
ORDER BY I.DATETIME
LIMIT 3
/*상위 N개만 추출 -> 코드 맨 마지막에 LIMIT 숫자 붙여주면 됨
하위 N개만 추출 -> 코드 맨 마지막에 LIMIT 숫자 붙여주면 됨, 단, ORDER BY DESC로.*/🔎 날짜 차이 계산
DATEDIFF(날짜1, 날짜2)
날짜1 - 날짜2
lv3. String, Date : 오랜 기간 보호한 동물(2)
입양을 간 동물 중, 보호 기간이 가장 길었던 동물 두 마리의 아이디와 이름을 조회하는 SQL문을 작성해주세요. 이때 결과는 보호 기간이 긴 순으로 조회해야 합니다.
/*날짜 차이 계산
DATEDIFF(날짜1, 날짜2) -> 날짜1 - 날짜2
*/
SELECT I.ANIMAL_ID, I.NAME
FROM ANIMAL_INS AS I
RIGHT OUTER JOIN ANIMAL_OUTS AS O
ON I.ANIMAL_ID = O.ANIMAL_ID
WHERE DATEDIFF(I.DATETIME, O.DATETIME)
ORDER BY DATEDIFF(I.DATETIME, O.DATETIME)
LIMIT 2🔎 중복값 제거
DISTINCT 컬럼명
어디에 사용할 지를 잘 생각해야 함.
lv4. GROUP BY : 년, 월, 성별 별 상품 구매 회원 수 구하기
USER_INFO 테이블과 ONLINE_SALE 테이블에서 년, 월, 성별 별로 상품을 구매한 회원수를 집계하는 SQL문을 작성해주세요. 결과는 년, 월, 성별을 기준으로 오름차순 정렬해주세요. 이때, 성별 정보가 없는 경우 결과에서 제외해주세요.
SELECT YEAR(S.SALES_DATE) AS YEAR,
MONTH(S.SALES_DATE) AS MONTH,
I.GENDER,
COUNT(DISTINCT I.USER_ID) AS USERS
/*원래 코드에서는 이 부분을 COUNT(*)로 출력했음.
COUNT(DISTINCT I.USER_ID)처럼 DISTINCT 사용해서 중복 값 제거.*/
FROM USER_INFO AS I
JOIN ONLINE_SALE AS S
ON I.USER_ID = S.USER_ID
WHERE I.GENDER IS NOT NULL
GROUP BY MONTH, I.GENDER
ORDER BY YEAR, MONTH, I.GENDER🔎 데이터에 존재하지 않는 값을 NULL로 채울때
NULL AS 컬럼명
🔎 데이터 통합에는 UNION
SELECT ~ FROM ~ WHERE~
UNION
SELECT ~ FROM ~ WHERE~
lv4. SELECT : 오프라인/온라인 판매 데이터 통합하기
ONLINE_SALE 테이블과 OFFLINE_SALE 테이블에서 2022년 3월의 오프라인/온라인 상품 판매 데이터의 판매 날짜, 상품ID, 유저ID, 판매량을 출력하는 SQL문을 작성해주세요. OFFLINE_SALE 테이블의 판매 데이터의 USER_ID 값은 NULL 로 표시해주세요. 결과는 판매일을 기준으로 오름차순 정렬해주시고 판매일이 같다면 상품 ID를 기준으로 오름차순, 상품ID까지 같다면 유저 ID를 기준으로 오름차순 정렬해주세요.
/*없는 값을 NULL로 채우고 싶을 땐 NULL AS
데이터 통합 UNION*/
SELECT DATE_FORMAT(SALES_DATE, "%Y-%m-%d") AS SALES_DATE,
PRODUCT_ID, USER_ID, SALES_AMOUNT
FROM ONLINE_SALE
WHERE LEFT(SALES_DATE,7) = '2022-03'
UNION
SELECT DATE_FORMAT(SALES_DATE, "%Y-%m-%d") AS SALES_DATE,
PRODUCT_ID, NULL AS USER_ID, SALES_AMOUNT
FROM OFFLINE_SALE
WHERE LEFT(SALES_DATE,7) = '2022-03'
ORDER BY SALES_DATE, PRODUCT_ID, USER_ID