✍🏻 programmers Lv2. MySQL 20문제(24.10.17)

programmers Lv2. MySQL 20문제(24.10.17)

30) 최솟값 구하기

• 동물 보호소에 가장 먼저 들어온 동물은 언제 들어왔는지 조회하는 SQL 문을 작성

SELECT DATETIME AS '시간'
FROM ANIMAL_INS
ORDER BY DATETIME LIMIT 1;

---

31) 동물 수 구하기

• 동물 보호소에 동물이 몇 마리 들어왔는지 조회하는 SQL 문을 작성

SELECT COUNT(*) AS 'count'
FROM ANIMAL_INS

---

32) 동명 동물 수 찾기

• 동물 보호소에 들어온 동물 이름 중 두 번 이상 쓰인 이름과 해당 이름이 쓰인 횟수를 조회하는 SQL문을 작성. 이때 결과는 이름이 없는 동물은 집계에서 제외하며, 결과는 이름 순으로 조회

SELECT NAME, COUNT(NAME) AS 'COUNT' 
FROM ANIMAL_INS
GROUP BY NAME
HAVING COUNT(NAME) >=2
ORDER BY NAME;

• GROUP BY 를 통해 이름들을 하나의 그룹으로 묶어야 함.

• HAVING은 GROUP BY와 함께 사용되어 그룹화된 데이터에 대한 조건을 지정하는 데 사용됨. WHERE절은 그룹화 전에 조건을 적용하는 반면, HAVING은 그룹화 후 결과에 조건을 걸 수 있음.

• WHERE과 GROUP BY의 차이점

  • WHERE은 그룹화 이전에 데이터를 필터링 한다. 즉, 개별 행을 기준으로 조건을 걸 수 있다.

  • GROUP BY는 데이터를 그룹으로 묶고 그룹별로 집계된 결과를 반환한다.

  • HAVING은 그룹화된 데이터에 조건을 걸 때 사용된다. 즉, 그룹화 이후 필터링을 하려면 HAVING을 사용해야 한다.

  • WHERE절은 개별 행에 대한 조건만 적용할 수 있다. COUNT()와 같은 집계 함수는 그룹화된 데이터에 적용되는 것이기 때문에, WHERE로는 집계된 값에 대해 조건을 걸 수 없다.

---

33) 이름에 el이 들어가는 동물 찾기

• 동물 보호소에 들어온 동물 이름 중, 이름에 “EL”이 들어가는 개의 아이디와 이름을 조회하는 SQL문을 작성. 이때 결과는 이름 순으로 조회

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE ANIMAL_TYPE = 'Dog' AND NAME LIKE '%EL%' 
ORDER BY NAME;

---

34) 중복 제거하기

• 동물 보호소에 들어온 동물의 이름은 몇 개인지 조회하는 SQL 문을 작성. 이때 이름이 NULL인 경우는 집계하지 않으며 중복되는 이름은 하나로 친다.

SELECT COUNT(DISTINCT NAME) AS 'count'
FROM ANIMAL_INS
WHERE NAME IS NOT NULL

• 중복 제거 : DISTINCT

---

35) 가격이 제일 비싼 식품의 정보 출력하기

SELECT *
FROM FOOD_PRODUCT
ORDER BY PRICE DESC LIMIT 1;

---

36) NULL 처리하기

SELECT ANIMAL_TYPE, IFNULL(NAME, 'No name') AS NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
ORDER BY ANIMAL_ID;

---

37) DATETIME에서 DATE로 형 변환

SELECT ANIMAL_ID, NAME, DATE_FORMAT(DATETIME,'%Y-%m-%d') AS '날짜'
FROM ANIMAL_INS

---

38) 카테고리 별 상품 개수 구하기

• PRODUCT 테이블에서 상품 카테고리 코드(PRODUCT_CODE 앞 2자리) 별 상품 개수를 출력하는 SQL문을 작성. 결과는 상품 카테고리 코드를 기준으로 오름차순 정렬.

SELECT SUBSTRING(PRODUCT_CODE,1,2) AS 'CATEGORY', COUNT(PRODUCT_CODE) AS 'PRODUCTS'
FROM PRODUCT
GROUP BY CATEGORY

• 원래 GROUP BY로 안 하고 ORDER BY로 해서 틀렸었다.

• GROUP BY는 특정 기준에 따라 그룹화한다. 즉, 같은 값을 가진 행들을 하나의 그룹으로 묶는 것이다.

• ORDER BY는 결과를 정렬하는 데 사용된다. 그룹화와는 관계없이 결과가 출력되는 순서를 결정한다.

• ORDER BY CATEGORY는 단순히 카테고리별로 정렬만 할 수 있고, GROUP BY CATEGORY**는 같은 카테고리를 묶어서 **각 카테고리별로 제품의 개수를 세는 것을 의미한다.

---

39) 조건에 맞는 아이템들의 가격의 총합 구하기

• ITEM_INFO 테이블에서 희귀도가 ‘LEGEND’인 아이템들의 가격의 총합을 구하는 SQL문을 작성. 이때 컬럼명은 ‘TOTAL_PRICE’로 지정

SELECT SUM(PRICE) AS 'TOTAL_PRICE'
FROM ITEM_INFO
WHERE RARITY = 'LEGEND';

---

40) 진료과별 총 예약 횟수 출력하기

• APPOINTMENT 테이블에서 2022년 5월에 예약한 환자 수를 진료과코드 별로 조회하는 SQL문을 작성. 이때, 컬럼명은 ‘진료과 코드’, ‘5월예약건수’로 지정해주시고 결과는 진료과별 예약한 환자 수를 기준으로 오름차순 정렬하고, 예약한 환자 수가 같다면 진료과 코드를 기준으로 오름차순 정렬

SELECT MCDP_CD AS '진료과코드', COUNT(PT_NO) AS '5월예약건수'
FROM APPOINTMENT 
WHERE APNT_YMD LIKE '2022-05%'
GROUP BY MCDP_CD	
ORDER BY COUNT(PT_NO), MCDP_CD;

• HAVING은 집계 함수에 조건을 걸 때 사용한다.

• ORDER문에 ' ' 금지

---

41) 고양이와 개는 몇 마리 있을까

• 동물 보호소에 들어온 동물 중 고양이와 개가 각각 몇 마리인지 조회하는 SQL문을 작성. 이때 고양이를 개보다 먼저 조회.

SELECT ANIMAL_TYPE, COUNT(ANIMAL_TYPE) AS 'count'
FROM ANIMAL_INS 
GROUP BY ANIMAL_TYPE
ORDER BY ANIMAL_TYPE;

---

42) 중성화 여부 파악하기

• 동물의 아이디와 이름, 중성화 여부를 아이디 순으로 조회하는 SQL문을 작성. 동물의 아이디와 이름, 중성화 여부를 아이디 순으로 조회하는 SQL문을 작성

SELECT ANIMAL_ID, NAME, IF(SEX_UPON_INTAKE LIKE '%Neutered%' OR SEX_UPON_INTAKE LIKE '%Spayed%', 'O', 'X')  AS '중성화'
FROM ANIMAL_INS

---

43) 입양 시각 구하기(1)

• 09:00부터 19:59까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성. 결과는 시간대 순으로 정렬.

SELECT HOUR(DATETIME) AS 'HOUR', COUNT(*) AS 'COUNT'
FROM ANIMAL_OUTS 
WHERE HOUR(DATETIME) >=9 AND HOUR(DATETIME) <=19
GROUP BY HOUR(DATETIME)
ORDER BY HOUR(DATETIME);

---

44) 자동차 종류 별 특정 옵션이 포함된 자동차 수 구하기

• CAR_RENTAL_COMPANY_CAR 테이블에서 ‘통풍시트’, ‘열선시트’, ‘가죽시트’ 중 하나 이상의 옵션이 포함된 자동차가 자동차 종류 별로 몇 대인지 출력하는 SQL문을 작성. 이때 자동차 수에 대한 컬럼명은 CARS로 지정하고, 결과는 자동차 종류를 기준으로 오름차순 정렬

SELECT CAR_TYPE, COUNT(*) AS 'CARS'
FROM CAR_RENTAL_COMPANY_CAR
WHERE OPTIONS LIKE '%통풍시트%' OR OPTIONS LIKE '%열선시트%' OR OPTIONS LIKE '%가죽시트%'
GROUP BY CAR_TYPE
ORDER BY CAR_TYPE ASC;

---

45) 상품 별 오프라인 매출 구하기

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

SELECT A.PRODUCT_CODE, SUM(A.PRICE*B.SALES_AMOUNT) AS 'SALES' 
FROM PRODUCT A 
JOIN OFFLINE_SALE B ON A.PRODUCT_ID = B.PRODUCT_ID
GROUP BY A.PRODUCT_CODE
ORDER BY SUM(A.PRICE*B.SALES_AMOUNT) DESC, A.PRODUCT_CODE ASC;

---

46) 성분으로 구분한 아이스크림 총 주문량

• 상반기 동안 각 아이스크림 성분 타입과 성분 타입에 대한 아이스크림의 총주문량을 총주문량이 작은 순서대로 조회하는 SQL 문을 작성. 이때 총주문량을 나타내는 컬럼명은 TOTAL_ORDER로 지정.

SELECT B.INGREDIENT_TYPE, SUM(A.TOTAL_ORDER) AS 'TOTAL_ORDER'
FROM FIRST_HALF A
JOIN ICECREAM_INFO B ON A.FLAVOR = B.FLAVOR
GROUP BY B.INGREDIENT_TYPE
ORDER BY A.TOTAL_ORDER

---

47) 조건에 맞는 도서와 저자 리스트 출력하기

• '경제' 카테고리에 속하는 도서들의 도서 ID(BOOK_ID), 저자명(AUTHOR_NAME), 출판일(PUBLISHED_DATE) 리스트를 출력하는 SQL문을 작성. 결과는 출판일을 기준으로 오름차순 정렬

SELECT BOOK_ID, AUTHOR_NAME, DATE_FORMAT(PUBLISHED_DATE, "%Y-%m-%d") AS 'PUBLISHED_DATE'
FROM BOOK A
JOIN AUTHOR B ON A.AUTHOR_ID = B.AUTHOR_ID
WHERE CATEGORY LIKE '경제'
ORDER BY PUBLISHED_DATE ASC;

---

48) 루시와 엘라 찾기

• 동물 보호소에 들어온 동물 중 이름이 Lucy, Ella, Pickle, Rogan, Sabrina, Mitty인 동물의 아이디와 이름, 성별 및 중성화 여부를 조회하는 SQL 문을 작성

SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
WHERE NAME LIKE "Lucy" OR NAME LIKE "Ella" OR NAME LIKE "Pickle" OR NAME LIKE "Rogan" OR NAME LIKE "Sabrina" OR NAME LIKE "Mitty"     

---

49) 3월에 태어난 여성 회원 목록 출력하기

• MEMBER_PROFILE 테이블에서 생일이 3월인 여성 회원의 ID, 이름, 성별, 생년월일을 조회하는 SQL문을 작성. 이때 전화번호가 NULL인 경우는 출력대상에서 제외시켜 주시고, 결과는 회원ID를 기준으로 오름차순 정렬

SELECT MEMBER_ID, MEMBER_NAME, GENDER, DATE_FORMAT(DATE_OF_BIRTH,"%Y-%m-%d") AS DATE_OF_BIRTH
FROM MEMBER_PROFILE
WHERE MONTH(DATE_OF_BIRTH) = 3 AND GENDER LIKE 'W'AND TLNO IS NOT NULL
ORDER BY MEMBER_ID ASC;