[TIL]코드카타

51. 없어진 기록 찾기

SELECT ao.animal_ID, ao.NAME
FROM ANIMAL_OUTS ao
LEFT JOIN ANIMAL_INS ai ON ai.ANIMAL_ID = ao.ANIMAL_ID
WHERE ai.animal_ID IS NULL
ORDER BY animal_ID;

 

52. 과일로 만든 아이스크림 고르기

SELECT fh.FLAVOR
FROM FIRST_HALF fh
JOIN ICECREAM_INFO ii ON fh.FLAVOR =ii.FLAVOR
WHERE TOTAL_ORDER >= 3000 AND INGREDIENT_TYPE = 'fruit_based'
ORDER BY TOTAL_ORDER DESC;

 

53. 재구매가 일어난 상품과 회원 리스트

SELECT user_id, product_id  	-- 4. 다음을 출력해라
FROM ONLINE_SALE        	-- 1.ONLINE_SALE 테이블에서
GROUP BY user_id, product_id       -- 2.user id, product id가 같은 것들끼리 그룹화
HAVING COUNT(*) > 1   		  -- 3.그룹한 행의 개수가 1개 이상이라면
ORDER BY user_id ASC, product_id DESC;  --5. user_id기준 오름차순, product_id기준 내림차순

 

54. 최댓값 구하기

SELECT MAX(DATETIME) time
FROM ANIMAL_INS

 

55. 조건에 맞는 사용자 정보 조회하기

-- 내가 작성한 쿼리
SELECT USER_ID, NICKNAME, 
    CONCAT(CITY,' ',STREET_ADDRESS1,' ',STREET_ADDRESS2) '전체주소',
    CONCAT(SUBSTR(TLNO,1,3) , '-',SUBSTR(TLNO,4,4) ,'-', SUBSTR(TLNO,8) )  AS '전화번호'
FROM (
SELECT *,
    COUNT(*) OVER(PARTITION BY bo.WRITER_ID) cnt
FROM USED_GOODS_BOARD bo
JOIN USED_GOODS_USER us ON bo.WRITER_ID = us.USER_ID
    )sub
WHERE sub.cnt >= 3
GROUP BY 1
ORDER BY 1 DESC;

-- 또 다른 정답
SELECT u.USER_ID, u.NICKNAME, CONCAT(u.CITY, ' ', u.STREET_ADDRESS1, ' ', u.STREET_ADDRESS2) AS '전체주소', CONCAT(SUBSTR(u.TLNO, 1, 3), '-', SUBSTR(u.TLNO, 4, 4), '-', SUBSTR(u.TLNO, 8, 4)) AS '전화번호'
FROM USED_GOODS_USER u
INNER JOIN USED_GOODS_BOARD b ON u.USER_ID=b.WRITER_ID
GROUP BY USER_ID
HAVING COUNT(*) >= 3
ORDER BY u.USER_ID DESC;