코딩연습이 좋아서/SQL이 좋아서

입양 시각 구하기(2)

SET @HOUR = -1;SELECT(@HOUR := @HOUR + 1) AS HOUR,(SELECT COUNT(*)FROM ANIMAL_OUTSWHERE HOUR(DATETIME)=@HOUR) AS COUNTFROM ANIMAL_OUTSWHERE @HOUR

년, 월, 성별 별 상품 구매 회원 수 구하기

SELECT YEAR(A.SALES_DATE) AS YEAR,MONTH(A.SALES_DATE) AS MONTH,B.GENDER,COUNT(DISTINCT(A.USER_ID)) AS USERSFROM ONLINE_SALE A JOIN USER_INFO BON A.USER_ID = B.USER_IDWHERE B.GENDER IS NOT NULLGROUP BY YEAR, MONTH, B.GENDERORDER BY YEAR, MONTH, B.GENDER;

식품분류별 가장 비싼 식품의 정보 조회하기

SELECT CATEGORY,PRICE AS MAX_PRICE,PRODUCT_NAMEFROM FOOD_PRODUCT WHERE (CATEGORY, PRICE) IN (SELECT CATEGORY, MAX(PRICE) AS PRICE FROM FOOD_PRODUCT GROUP BY CATEGORY HAVING (CATEGORY = '과자' OR CATEGORY = '국' OR CATEGORY = '김치' ..

저자 별 카테고리 별 매출액 집계하기

SELECT A.AUTHOR_ID,B.AUTHOR_NAME,A.CATEGORY,SUM(C.SALES * A.PRICE) AS TOTAL_SALES FROM BOOK A JOIN AUTHOR BON A.AUTHOR_ID = B.AUTHOR_IDJOIN BOOK_SALES CON A.BOOK_ID = C.BOOK_IDWHERE C.SALES_DATE LIKE '2022-01%'GROUP BY A.AUTHOR_ID, A.CATEGORYORDER BY A.AUTHOR_ID, A.CATEGORY DESC;

보호소에서 중성화한 동물

SELECT A.ANIMAL_ID,A.ANIMAL_TYPE,A.NAMEFROM ANIMAL_INS A JOIN ANIMAL_OUTS BON A.ANIMAL_ID = B.ANIMAL_IDWHERE (A.SEX_UPON_INTAKE NOT LIKE '%Spayed%'AND A.SEX_UPON_INTAKE NOT LIKE '%Neutered%')AND (B.SEX_UPON_OUTCOME LIKE '%Spayed%'OR B.SEX_UPON_OUTCOME LIKE '%Neutered%')ORDER BY A.ANIMAL_ID;

그룹별 조건에 맞는 식당 목록 출력하기

SELECT A.MEMBER_NAME,B.REVIEW_TEXT,DATE_FORMAT(B.REVIEW_DATE, '%Y-%m-%d') AS REVIEW_DATEFROM MEMBER_PROFILE A JOIN REST_REVIEW BON A.MEMBER_ID = B.MEMBER_IDWHERE A.MEMBER_ID = (SELECT C.MEMBER_ID FROM REST_REVIEW C GROUP BY C.MEMBER_ID ORDER BY COUNT(*) DESC LIMIT 1)ORDER BY REVIEW_DATE, B.REVIEW_TEXT;- =과 IN은 반환되는 결과의 개수에 따라 다르게 사용해야 한다.

주문량이 많은 아이스크림들 조회하기

SELECT A.FLAVORFROM FIRST_HALF A JOIN JULY BON A.FLAVOR = B.FLAVORGROUP BY A.FLAVORORDER BY (SUM(A.TOTAL_ORDER) + SUM(B.TOTAL_ORDER)) DESC LIMIT 3;

5월 식품들의 총매출 조회하기

SELECT A.PRODUCT_ID,A.PRODUCT_NAME,(A.PRICE * SUM(B.AMOUNT)) AS TOTAL_SALESFROM FOOD_PRODUCT A JOIN FOOD_ORDER BON A.PRODUCT_ID = B.PRODUCT_IDWHERE B.PRODUCE_DATE LIKE '2022-05%'GROUP BY A.PRODUCT_IDORDER BY TOTAL_SALES DESC, A.PRODUCT_ID;

취소되지 않은 진료 예약 조회하기

SELECT A.APNT_NO,B.PT_NAME,A.PT_NO,A.MCDP_CD,C.DR_NAME,A.APNT_YMDFROM APPOINTMENT A JOIN PATIENT BON A.PT_NO = B.PT_NOJOIN DOCTOR CON A.MDDR_ID = C.DR_ID AND A.APNT_CNCL_YN = 'N'WHERE A.APNT_YMD LIKE '2022-04-13%'ORDER BY A.APNT_YMD;

오프라인/온라인 판매 데이터 통합하기

(SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DATE,PRODUCT_ID,USER_ID,SALES_AMOUNTFROM ONLINE_SALEWHERE SALES_DATE LIKE '2022-03%')UNION(SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DATE,PRODUCT_ID,NULL AS USER_ID,SALES_AMOUNTFROM OFFLINE_SALE WHERE SALES_DATE LIKE '2022-03%')ORDER BY SALES_DATE, PRODUCT_ID, USER_ID;