SELECT A.BOOK_ID , B.AUTHOR_NAME , TO_CHAR(A.PUBLISHED_DATE, 'YYYY-MM-DD') AS PUBLISHED_DATE FROM BOOK A INNER JOIN AUTHOR B ON A.AUTHOR_ID = B.AUTHOR_ID WHERE A.CATEGORY = '경제' ORDER BY A.PUBLISHED_DATE

SELECT A.PRODUCT_ID AS PRODUCT_ID , MAX(A.PRODUCT_NAME) AS PRODUCT_NAME , SUM(B.AMOUNT) * MAX(A.PRICE) AS TOTAL_SALES FROM FOOD_PRODUCT A INNER JOIN FOOD_ORDER B ON A.PRODUCT_ID = B.PRODUCT_ID WHERE TO_CHAR(B.PRODUCE_DATE, 'YYYYMM') = '202205' GROUP BY A.PRODUCT_ID ORDER BY SUM(B.AMOUNT) * MAX(A.PRICE) DESC, A.PRODUCT_ID

▶ MySQL 활용# BASS, SNAPPER 수SELECT COUNT(ID) AS FISH_COUNT FROM FISH_INFO WHERE FISH_TYPE IN (SELECT FISH_TYPE FROM FISH_NAME_INFO WHERE FISH_NAME IN ('BASS','SNAPPER'))

▶ MySQL 활용# 길이 순 10마리SELECT ID , LENGTH FROM FISH_INFOWHERE LENGTH IS NOT NULLORDER BY LENGTH DESC, IDLIMIT 10

▶ MySQL 활용# 길이가 10CM 이하면 NULL / NULL인 경우 X# 길이가 10CM 이하인 물고기 수SELECT COUNT(ID) AS FISH_COUNT FROM FISH_INFO WHERE LENGTH IS NULL

▶ MySQL 활용-- Python, c# dev in id, email, name, last nameSELECT DISTINCT A.ID , A.EMAIL , A.FIRST_NAME , A.LAST_NAME FROM DEVELOPERS A JOIN SKILLCODES B ON (A.SKILL_CODE & B.CODE) > 0 AND B.NAME IN ('Python', 'C#') ORDER BY A.ID비트연산자는 2진수의 값이 모두 1일 때 1를 반환한다.

▶ MySQL -- python 스킬을 가진 개발자의 id,email,name,lastNameSELECT ID , EMAIL , FIRST_NAME , LAST_NAME FROM DEVELOPER_INFOS WHERE SKILL_1 = 'Python' OR SKILL_2 = 'Python' OR SKILL_3 = 'Python' ORDER BY ID;

▶ MySQL-- 희귀도가 'RARE'인 아이템-- ITEM_ID, ITEM_NAME, RARITY 출력-- ITEM_ID DESCSELECT C.ITEM_ID , C.ITEM_NAME , C.RARITY FROM ITEM_INFO A LEFT JOIN ITEM_TREE B ON A.ITEM_ID = B.PARENT_ITEM_ID JOIN ITEM_INFO C ON B.ITEM_ID = C.ITEM_ID AND A.RARITY = 'RARE' ORDER BY C.ITEM_ID DESC

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

SELECT EXTRACT(MONTH FROM START_DATE) AS MONTH , CAR_ID , COUNT(HISTORY_ID) AS RECORDS FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY WHERE CAR_ID IN ( SELECT CAR_ID FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY WHERE TO_CHAR(START_DATE, 'YYYY-MM') BETWEEN '2022-08' AND '2022-10' GROUP BY CAR_ID HAVING COUNT(CAR_ID) >= 5 ) AND TO_CHAR(START_DATE, 'YYYY-MM') BETWEEN '2022-08' AND '2022-10' GROUP BY EXTRACT(MONT..

▶ MySQL SELECT ANIMAL_TYPE , IFNULL(NAME,'No name') AS NAME , SEX_UPON_INTAKE FROM ANIMAL_INS ORDER BY ANIMAL_ID ▶ Oracle SELECT ANIMAL_TYPE , NVL(NAME,'No name') AS NAME , SEX_UPON_INTAKE FROM ANIMAL_INS ORDER BY ANIMAL_ID

▶ MySQL / Oracle SELECT ANIMAL_ID FROM ANIMAL_INS WHERE NAME IS NOT NULL ORDER BY ANIMAL_ID