Moments of growth

SELECT DATE_FORMAT(SALES_DATE,'%Y-%m-%d') AS SALES_DATE, PRODUCT_ID, IFNULL(USER_ID, NULL) AS USER_ID, SALES_AMOUNT FROM ( SELECT SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT FROM ONLINE_SALE UNION SELECT SALES_DATE, PRODUCT_ID, NULL, SALES_AMOUNT FROM OFFLINE_SALE ) AS T WHERE SALES_DATE LIKE '2022-03%' ORDER BY SALES_DATE, PRODUCT_ID, USER_ID 주의) FROM에서 UNION 한 테이블의 이름을 꼭 붙여줘야한다! -> 여기서는 AS T..

SELECT YEAR(SALES_DATE) AS YEAR, MONTH(SALES_DATE) AS MONTH, UI.GENDER, COUNT(DISTINCT OS.USER_ID) AS USERS FROM USER_INFO AS UI JOIN ONLINE_SALE AS OS ON UI.USER_ID = OS.USER_ID WHERE UI.GENDER IS NOT NULL GROUP BY YEAR, MONTH, GENDER ORDER BY YEAR, MONTH, GENDER

SELECT B.AUTHOR_ID, A.AUTHOR_NAME, B.CATEGORY, (SUM(BS.SALES * B.PRICE)) AS SALES FROM BOOK AS B JOIN BOOK_SALES AS BS ON B.BOOK_ID = BS.BOOK_ID JOIN AUTHOR AS A ON B.AUTHOR_ID = A.AUTHOR_ID WHERE SALES_DATE LIKE '2022-01-%' GROUP BY AUTHOR_ID, CATEGORY ORDER BY AUTHOR_ID, CATEGORY DESC 주의할 점 (SUM(BS.SALES) * B.PRICE) AS SALES 로 하면 틀림 -> 책마다 가격이 다르니깐 각 책의 가격 * 그 책의 판매량으로 계산한 것의 합을 구해야함

SELECT A.APNT_NO, P.PT_NAME, P.PT_NO, A.MCDP_CD, D.DR_NAME, A.APNT_YMD FROM DOCTOR AS D JOIN APPOINTMENT AS A ON D.DR_ID = A.MDDR_ID JOIN PATIENT AS P ON P.PT_NO = A.PT_NO WHERE A.APNT_YMD LIKE '2022-04-13%' AND A.APNT_CNCL_YN = 'N' ORDER BY A.APNT_YMD 이중 JOIN DOCTOR와 APPOINTMENT 두개를 JOIN 한 뒤 그걸 AS DA로 만들고 DA와 PATIENT를 JOIN하려고 한건데, DA로 만들면 ERROR가 난다. 그냥 A와 P로 JOIN ON 만들면 된다.

SELECT P.PRODUCT_ID,P.PRODUCT_NAME, SUM(O.AMOUNT)*(P.PRICE) AS TOTAL_SALES FROM FOOD_ORDER AS O JOIN FOOD_PRODUCT AS P ON O.PRODUCT_ID = P.PRODUCT_ID WHERE O.PRODUCE_DATE LIKE '2022-05-%' GROUP BY PRODUCT_ID ORDER BY TOTAL_SALES DESC, P.PRODUCT_ID PRODUCT_ID로 그룹화해서 SUM(O.AMOUNT)*(P.PRICE) AS TOTAL_SALES

SELECT CATEGORY, PRICE AS MAX_PRICE, PRODUCT_NAME FROM FOOD_PRODUCT WHERE CATEGORY IN ('과자', '국', '김치', '식용유') AND PRICE IN (SELECT MAX(PRICE) FROM FOOD_PRODUCT GROUP BY CATEGORY) GROUP BY CATEGORY ORDER BY MAX_PRICE DESC SELECT MAX(PRICE) FROM FOOD_PRODUCT GROUP BY CATEGORY -> 카테고리 별 가장 비싼 식품의 가격만 모아둔 테이블 PRICE IN (SELECT MAX(PRICE) FROM FOOD_PRODUCT GROUP BY CATEGORY) 대신 만약 같은 값을 갖는 식품이 있는 경우는..

SELECT USER_ID,PRODUCT_ID FROM ONLINE_SALE GROUP BY USER_ID, PRODUCT_ID HAVING COUNT(ONLINE_SALE_ID) > 1 ORDER BY USER_ID ASC, PRODUCT_ID DESC; GROUP BY 하면서 USER_ID, PRODUCT_ID가 동시에 같은게 묶이고, 묶이면서 HAVING 조건으로 ONLINE_SALE_ID 개수가 1보다 큰 것들만 출력한다 HAVING은 GROUP BY의 WHERE 역할

SELECT MCDP_CD AS 진료과코드, COUNT(APNT_NO) AS 5월예약건수 FROM APPOINTMENT WHERE APNT_YMD LIKE '2022-05%' GROUP BY 진료과코드 ORDER BY 5월예약건수, 진료과코드 SELECT MCDP_CD AS '진료과코드', COUNT(APNT_NO) AS '5월예약건수' FROM APPOINTMENT WHERE APNT_YMD LIKE '2022-05%' GROUP BY 진료과코드 ORDER BY 5월예약건수, 진료과코드 중요한 것 AS 뒤에 별칭 쓸 때는 ' ' 써도 되지만 GROUP BY와 ORDER BY는 ' ' 쓰면 오류남 한글이어도 ' ' 안 써도 된다!