데일리루틴(알고리즘)/SQL문제
10/28 SQL 문제풀이
김호진02
2024. 10. 28. 07:15
SELECT i.NAME, i.DATETIME
FROM ANIMAL_INS i LEFT JOIN ANIMAL_OUTS o ON i.ANIMAL_ID=o.ANIMAL_ID
WHERE o.DATETIME IS NULL
ORDER BY i.DATETIME
LIMIT 3;
SELECT b.CATEGORY,SUM( a.TOTAL_SALES) TOTAL_SALES
FROM BOOK b INNER JOIN
(
SELECT BOOK_ID,SUM(SALES) TOTAL_SALES
FROM BOOK_SALES
WHERE DATE_FORMAT(SALES_DATE,'%Y-%m-%d') LIKE '2022-01%'
GROUP BY BOOK_ID
) a
ON b.BOOK_ID=a.BOOK_ID
GROUP BY 1
ORDER BY 1;
SELECT p.PRODUCT_CODE,(p.PRICE*SUM_AMOUNT) SALES
FROM PRODUCT p INNER JOIN
(SELECT PRODUCT_ID,SUM(SALES_AMOUNT) SUM_AMOUNT
FROM OFFLINE_SALE
GROUP BY 1
)a
ON p.PRODUCT_ID= a.PRODUCT_ID
GROUP BY 1
ORDER BY 2 DESC,1;
SELECT i.ANIMAL_ID,i.NAME
FROM ANIMAL_INS i INNER JOIN ANIMAL_OUTS o
ON i.ANIMAL_ID=o.ANIMAL_ID
WHERE i.DATETIME>o.DATETIME
ORDER BY i.DATETIME;
SELECT i.ANIMAL_ID, i.NAME
FROM ANIMAL_INS i INNER JOIN ANIMAL_OUTS o
ON i.ANIMAL_ID=o.ANIMAL_ID
ORDER BY DATEDIFF(o.DATETIME,i.DATETIME) DESC
LIMIT 2;
SELECT o.ANIMAL_ID,o.ANIMAL_TYPE,o.NAME
FROM ANIMAL_OUTS o INNER JOIN
(
SELECT ANIMAL_ID,ANIMAL_TYPE,NAME
FROM ANIMAL_INS
WHERE SEX_UPON_INTAKE LIKE ('Intact%')
) i
ON o.ANIMAL_ID=i.ANIMAL_ID
WHERE o.SEX_UPON_OUTCOME LIKE ('Spayed%')
OR o.SEX_UPON_OUTCOME LIKE ('Neutered %')
ORDER BY 1;
SELECT b.BOOK_ID,a.AUTHOR_NAME,DATE_FORMAT(b.PUBLISHED_DATE,'%Y-%m-%d') PUBLISHED_DATE
FROM AUTHOR a INNER JOIN
(
SELECT BOOK_ID,AUTHOR_ID,PUBLISHED_DATE
FROM BOOK
WHERE CATEGORY LIKE ('경제')
) b
ON a.AUTHOR_ID=b.AUTHOR_ID
ORDER BY 3;
SELECT ORDER_ID,PRODUCT_ID,DATE_FORMAT(OUT_DATE,'%Y-%m-%d'),
CASE WHEN OUT_DATE IS NULL THEN '출고미정'
WHEN OUT_DATE<=DATE('2022-05-01') THEN '출고완료'
ELSE '출고대기' END 출고여부
FROM FOOD_ORDER
ORDER BY 1;
SELECT i.INGREDIENT_TYPE INGREDIENT_TYPE, SUM(f.TOTAL_ORDER) TOTAL_ORDER
FROM ICECREAM_INFO i LEFT JOIN FIRST_HALF f
ON i.FLAVOR=f.FLAVOR
GROUP BY 1;
SELECT ANIMAL_ID,NAME,SEX_UPON_INTAKE
FROM ANIMAL_INS
WHERE NAME IN ('Lucy', 'Ella', 'Pickle', 'Rogan', 'Sabrina', 'Mitty')
ORDER BY 1;
SELECT BOOK_ID,DATE_FORMAT(PUBLISHED_DATE,'%Y-%m-%d') PUBLISHED_DATE
FROM BOOK
WHERE PUBLISHED_DATE LIKE ('2021%')
AND CATEGORY='인문'
ORDER BY 1;
SELECT ROUND(AVG(DAILY_FEE)) AVERAGE_FEE
FROM CAR_RENTAL_COMPANY_CAR
WHERE CAR_TYPE='SUV'
SELECT u.USER_ID,u.NICKNAME,b.tot_Price TOTAL_SALES
FROM USED_GOODS_USER u INNER JOIN
(
SELECT WRITER_ID, SUM(PRICE) tot_Price
FROM USED_GOODS_BOARD
WHERE STATUS='DONE'
GROUP BY 1
HAVING SUM(PRICE) >=700000
) b
ON u.USER_ID=b.WRITER_ID
ORDER BY 3;
SELECT FLOOR(PRICE/10000) *10000 PRICE_GROUP,COUNT(*)
FROM PRODUCT
GROUP BY 1
ORDER BY 1;
SELECT MEMBER_ID,MEMBER_NAME,GENDER,DATE_FORMAT(DATE_OF_BIRTH,'%Y-%m-%d') DATE_OF_BIRTH
FROM MEMBER_PROFILE
WHERE TLNO IS NOT NULL
AND DATE_FORMAT(DATE_OF_BIRTH,'%Y-%m-%d') LIKE '%03-%'
AND GENDER='W'
ORDER BY 1;
SELECT DISTINCT h.CAR_ID
FROM CAR_RENTAL_COMPANY_CAR c INNER JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY h
ON c.CAR_ID=h.CAR_ID
WHERE c.CAR_TYPE='세단' AND h.START_DATE LIKE ('%10-%')
ORDER BY 1 DESC;
SELECT *
FROM ANIMAl_InS
ORDER BY 1;
SELECT FOOD_TYPE,REST_ID,REST_NAME,FAVORITES
FROM REST_INFO
WHERE (FOOD_TYPE,FAVORITES) IN
(
SELECT FOOD_TYPE,MAX(FAVORITES)
FROM REST_INFO
GROUP BY 1
)
ORDER BY 1 DESC;
SELECT CATEGORY,PRICE MAX_PRICE,PRODUCT_NAME
FROM FOOD_PRODUCT
WHERE (CATEGORY,PRICE) IN
(
SELECT CATEGORY,MAX(PRICE) MAX_PRICE
FROM FOOD_PRODUCT
WHERE CATEGORY IN ('과자',
'국','김치','식용유')
GROUP BY 1
)
ORDER BY 2 DESC;
SELECT p.PRODUCT_ID,p.PRODUCT_NAME,(o.tot_Amount*p.PRICE) TOTAL_SALES
FROM FOOD_PRODUCT p INNER JOIN
(
SELECT PRODUCT_ID,SUM(AMOUNT) tot_Amount
FROM FOOD_ORDER
WHERE PRODUCE_DATE LIKE ('2022-05%')
GROUP BY 1
) o
ON p.PRODUCT_ID=o.PRODUCT_ID
ORDER BY 3 DESC ,1;