SELECT COUNT(1) AS USERS
FROM USER_INFO
WHERE AGE IS NULL;
SELECT WAREHOUSE_ID, WAREHOUSE_NAME, ADDRESS, TLNO,
ifnull(FREEZER_YN,'N') as FREEZER_YN
FROM FOOD_WAREHOUSE
WHERE ADDRESS LIKE '%경기도%';
SELECT *
FROM FOOD_PRODUCT
WHERE PRICE = (SELECT MAX(PRICE)
FROM FOOD_PRODUCT)
SELECT LEFT(product_code,2) AS CATEGORY , COUNT(1) AS PRODUCTS
FROM PRODUCT
GROUP BY 1
ORDER BY 1 ASC
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
TLNO IS NOT NULL AND
GENDER = 'W'
ORDER BY 1
SELECT P.product_code, sum(P.price * O.sales_amount) as SALES
FROM PRODUCT P LEFT JOIN OFFLINE_SALE O ON P.product_id = O.product_id
group by 1
order by 2 DESC,1 ASC
SELECT MCDP_CD AS '진료과코드', COUNT(1) AS '5월예약건수'
FROM APPOINTMENT
WHERE APNT_YMD LIKE '2022-05%'
GROUP BY 1
ORDER BY 2 ASC, 1 ASC
SELECT (
CASE
WHEN PRICE <10000 THEN 0
ELSE TRUNCATE(PRICE,-4)
END) AS PRICE_GROUP, COUNT(PRODUCT_ID) AS PRODUCTS
FROM PRODUCT
GROUP BY PRICE_GROUP
ORDER BY PRICE_GROUP
2~4번째 줄: CASE를 사용했다. PRICE가 1000보다 작을 경우 ➡️ 0
TRUNCATE : PRICE가 0보다 클 경우 ➡️ PRICE를 뒤에서부터 4개 삭제한다.
SELECT INGREDIENT_TYPE, SUM(TOTAL_ORDER) AS TOTAL_ORDER
FROM FIRST_HALF A LEFT JOIN ICECREAM_INFO B ON A.FLAVOR = B.FLAVOR
GROUP BY 1
ORDER BY 2 ASC