SELECT a.BOOK_ID,
b.AUTHOR_NAME,
DATE_FORMAT(a.PUBLISHED_DATE, '%Y-%m-%d') as PUBLISHED_DATE
FROM BOOK as a
INNER JOIN AUTHOR as b
on a.AUTHOR_ID = b.AUTHOR_ID
WHERE CATEGORY = '경제'
ORDER BY a.PUBLISHED_DATE;
SELECT a.PRODUCT_CODE,
sum(b.SALES_AMOUNT) * a.PRICE as SALES
FROM PRODUCT as a, OFFLINE_SALE as b
WHERE a.PRODUCT_ID = b.PRODUCT_ID
GROUP BY a.PRODUCT_CODE
ORDER BY SALES DESC, a.PRODUCT_CODE;
SELECT NAME, DATETIME
FROM ANIMAL_INS
WHERE ANIMAL_ID NOT IN
(SELECT ANIMAL_ID FROM ANIMAL_OUTS)
ORDER BY DATETIME
LIMIT 3;
SELECT a.NAME, a.DATETIME
FROM ANIMAL_INS as a
LEFT JOIN ANIMAL_OUTS as b
ON a.ANIMAL_ID = b.ANIMAL_ID
WHERE b.DATETIME IS NULL
ORDER BY a.DATETIME
LIMIT 3;
SELECT b.ANIMAL_ID, b.NAME
FROM ANIMAL_INS as a
RIGHT JOIN ANIMAL_OUTS as b
ON a.ANIMAL_ID = b.ANIMAL_ID
WHERE a.ANIMAL_ID IS NULL
ORDER BY b.ANIMAL_ID;
SELECT a.ANIMAL_ID, a.NAME
FROM ANIMAL_INS as a
INNER JOIN ANIMAL_OUTS as b
ON a.ANIMAL_ID = b.ANIMAL_ID
WHERE a.DATETIME > b.DATETIME
ORDER BY a.DATETIME;
SELECT a.PRODUCT_ID, a.PRODUCT_NAME, a.PRICE * sum(b.AMOUNT) as AMOUNT
FROM FOOD_PRODUCT as a
INNER JOIN FOOD_ORDER as b
ON a.PRODUCT_ID = b.PRODUCT_ID
WHERE DATE_FORMAT(b.PRODUCE_DATE, '%Y-%m') = '2022-05'
GROUP BY a.PRODUCT_ID
ORDER BY AMOUNT DESC, a.PRODUCT_ID;
7.특정 기간동안 대여 가능한 자동차들의 대여비용 구하기
SELECT a.CAR_ID,
a.CAR_TYPE,
ROUND( a.DAILY_FEE * 30 * (1 - (c.DISCOUNT_RATE / 100)) ) AS FEE
FROM CAR_RENTAL_COMPANY_CAR as a
INNER JOIN
(SELECT CAR_TYPE, DISCOUNT_RATE FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
WHERE DURATION_TYPE = '30일 이상' AND CAR_TYPE IN ('세단', 'SUV')) as c
ON a.CAR_TYPE = c.CAR_TYPE
AND a.CAR_ID NOT IN
(SELECT CAR_ID FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE END_DATE >= '2022-11-01' AND START_DATE <= '2022-11-30')
HAVING FEE BETWEEN 500000 AND 1999999
ORDER BY FEE DESC, a.CAR_TYPE, a.CAR_ID DESC;
SELECT FLAVOR FROM
(SELECT FLAVOR, TOTAL_ORDER FROM FIRST_HALF
UNION ALL
SELECT FLAVOR, TOTAL_ORDER FROM JULY
) AS BASE
GROUP BY FLAVOR
ORDER BY SUM(TOTAL_ORDER) DESC
LIMIT 3
;
FULL OUTER JOIN (FULL JOIN)
이 없다UNION ALL
처리 또는 LEFT JOIN
과 RIGHT JOIN
을 합쳐야 한다SELECT b.MEMBER_NAME, REVIEW_TEXT, DATE_FORMAT(REVIEW_DATE, '%Y-%m-%d') as REVIEW_DATE
FROM REST_REVIEW as a
INNER JOIN MEMBER_PROFILE as b
ON a.MEMBER_ID = b.MEMBER_ID
WHERE b.MEMBER_ID IN
(
SELECT MEMBER_ID
FROM REST_REVIEW
GROUP BY MEMBER_ID
HAVING COUNT(REVIEW_ID) =
(SELECT COUNT(REVIEW_ID) AS FREQ
FROM REST_REVIEW
GROUP BY MEMBER_ID
ORDER BY FREQ DESC
LIMIT 1)
)
ORDER BY REVIEW_DATE, REVIEW_TEXT;