헤맨 만큼 내 땅이다 🏃

문제 링크https://school.programmers.co.kr/learn/courses/30/lessons/62284SELECT CART_IDFROM CART_PRODUCTSWHERE NAME IN ('Milk', 'Yogurt')GROUP BY CART_IDHAVING COUNT(DISTINCT NAME) = 2ORDER BY CART_ID;CART_ID가 동일하면서  Milk와 Yogurt 두 제품 모두 포함된 장바구니를 조회해야 하는 문제 CART_ID별로 그룹화하여 각 장바구니를 구분하고HAVING 절과 COUNT를 활용한 집계 값을 사용해서중복 제거한 상품 이름을 카운트하여 2와 같다면(Milk와 Yogurt가 각각 한 번씩 나타남을 의미)해당 장바구니에는 우유와 요거트가 있다는 것!!

문제 링크https://school.programmers.co.kr/learn/courses/30/lessons/131532SELECT YEAR(B.SALES_DATE) AS YEAR, MONTH(B.SALES_DATE) AS MONTH, A.GENDER, COUNT(DISTINCT B.USER_ID) AS USERSFROM USER_INFO AJOIN ONLINE_SALE B ON A.USER_ID = B.USER_IDWHERE A.GENDER IS NOT NULLGROUP BY YEAR(B.SALES_DATE), MONTH(B.SALES_DATE), A.GENDERORDER BY YEAR(B.SALES_DATE), MONTH(B.SALES_DATE), A.GENDER;G..

문제 링크https://school.programmers.co.kr/learn/courses/30/lessons/144856SELECT A.AUTHOR_ID, B.AUTHOR_NAME, A.CATEGORY, SUM(C.SALES * A.PRICE) AS TOTAL_SALESFROM BOOK AJOIN AUTHOR B ON A.AUTHOR_ID = B.AUTHOR_IDJOIN BOOK_SALES C ON A.BOOK_ID = C.BOOK_IDWHERE C.SALES_DATE LIKE '2022-01%'GROUP BY A.AUTHOR_ID, B.AUTHOR_NAME, A.CATEGORYORDER BY A.AUTHOR_ID ASC, A.C..

문제 링크https://school.programmers.co.kr/learn/courses/30/lessons/131118SELECT A.REST_ID, A.REST_NAME, A.FOOD_TYPE,A.FAVORITES, A.ADDRESS, ROUND(AVG(B.REVIEW_SCORE),2) AS SCOREFROM REST_INFO AJOIN REST_REVIEW BON A.REST_ID = B.REST_IDWHERE A.ADDRESS LIKE "서울%"GROUP BY B.REST_IDORDER BY SCORE DESC, A.FAVORITES DESC;처음에 group절을 쓰지 않아서 틀렸었는데문제에서 식당별 이라고 딱 뚜렷하게 명시해주지 않아도문제를 읽으면서 그룹화해야 된다는 걸 인지해야 한다! 식당..

문제 링크https://school.programmers.co.kr/learn/courses/30/lessons/132204SELECT C.APNT_NO, A.PT_NAME, A.PT_NO, C.MCDP_CD, B.DR_NAME, C.APNT_YMDFROM PATIENT AJOIN APPOINTMENT CON A.PT_NO = C.PT_NOJOIN DOCTOR BON B.DR_ID = C.MDDR_IDWHERE C.APNT_CNCL_YN = "N" AND C.MCDP_CD = "CS"AND C.APNT_YMD LIKE "2022-04-13%"ORDER BY C.APNT_YMD; 테이블 여러개 주어질 때 조인 조건 알맞게 테이블 연결해야 함!!!찬찬히 조건대로 작성해나가면 된다.

문제 링크https://school.programmers.co.kr/learn/courses/30/lessons/131117SELECT A.PRODUCT_ID, A.PRODUCT_NAME, SUM(B.AMOUNT * A.PRICE) AS TOTAL_SALESFROM FOOD_PRODUCT AJOIN FOOD_ORDER B ON A.PRODUCT_ID = B.PRODUCT_IDWHERE B.PRODUCE_DATE LIKE '2022-05%'GROUP BY A.PRODUCT_ID, A.PRODUCT_NAMEORDER BY TOTAL_SALES DESC, A.PRODUCT_ID ASC;SQL에서 GROUP BY 절을 사용할 때,  SELECT 절에 있는 모든 컬럼은 GROUP BY 절에 포함하거나, 집계 함수..

문제 링크https://school.programmers.co.kr/learn/courses/30/lessons/131116SELECT CATEGORY, PRICE AS MAX_PRICE, PRODUCT_NAMEFROM FOOD_PRODUCTWHERE (CATEGORY, PRICE) IN ( SELECT CATEGORY, MAX(PRICE) FROM FOOD_PRODUCT WHERE CATEGORY IN ('과자', '국', '김치', '식용유') GROUP BY CATEGORY)ORDER BY PRICE DESC;💡 서브쿼리 사용한 이유FOOD_PRODUCT 테이블에서 CATEGORY별로 가장 비싼 제품의 이름과 가격을 구해야 하는데 서브쿼리를 사용하지 않으면, MAX(PRICE..

문제 링크https://school.programmers.co.kr/learn/courses/30/lessons/59045SELECT A.ANIMAL_ID, A.ANIMAL_TYPE, A.NAMEFROM ANIMAL_INS AJOIN ANIMAL_OUTS BON A.ANIMAL_ID = B.ANIMAL_IDWHERE A.SEX_UPON_INTAKE LIKE "Intact%"AND B.SEX_UPON_OUTCOME NOT LIKE 'Intact%'ORDER BY A.ANIMAL_ID; "Intact"로 시작하는 동물(중성화되지 않은 동물)을 필터링 "Intact"로 시작하지 않는 동물(중성화된 동물)을 필터링

문제 링크https://school.programmers.co.kr/learn/courses/30/lessons/301649SELECT ID, CASE WHEN NTILE_GROUP = 1 THEN 'CRITICAL' WHEN NTILE_GROUP = 2 THEN 'HIGH' WHEN NTILE_GROUP = 3 THEN 'MEDIUM' WHEN NTILE_GROUP = 4 THEN 'LOW' END AS COLONY_NAMEFROM ( SELECT ID, NTILE(4) OVER (ORDER BY SIZE_OF_COLONY DESC) AS NTILE_GROUP FROM ECOL..

문제 링크https://school.programmers.co.kr/learn/courses/30/lessons/293261SELECT A.ID, B.FISH_NAME, A.LENGTHFROM FISH_INFO AJOIN FISH_NAME_INFO B ON A.FISH_TYPE = B.FISH_TYPEJOIN ( SELECT FISH_TYPE, MAX(LENGTH) AS MAX_LENGTH FROM FISH_INFO GROUP BY FISH_TYPE) C ON A.FISH_TYPE = C.FISH_TYPE AND A.LENGTH = C.MAX_LENGTHORDER BY A.ID; 서브쿼리 CFISH_INFO 테이블에서 FISH_TYPE별로 가장 큰 길이 (MAX(LENGTH))를 계산하여..