Level2

SELECT COUNT(DISTINCT NAME) AS 'COUNT' FROM ANIMAL_INS;

SELECT * FROM FOOD_PRODUCT WHERE PRICE = (SELECT MAX(PRICE) FROM FOOD_PRODUCT);

SELECT * FROM FOOD_PRODUCT ORDER BY PRICE DESC LIMIT 1;

SELECT MIN(DATETIME) FROM ANIMAL_INS;

-- 코드를 입력하세요 SELECT NAME, COUNT(NAME) AS 'COUNT' FROM ANIMAL_INS WHERE NAME IS NOT NULL GROUP BY NAME HAVING COUNT(NAME) > 1 ORDER BY NAME

SELECT ANIMAL_ID, NAME FROM ANIMAL_INS WHERE NAME LIKE '%el%' AND ANIMAL_TYPE = 'Dog' ORDER BY NAME;

-- 코드를 입력하세요 SELECT ANIMAL_TYPE, IFNULL(NAME, 'No name') AS NAME, SEX_UPON_INTAKE FROM ANIMAL_INS;

-- 코드를 입력하세요 SELECT SUBSTRING(PRODUCT_CODE, 1, 2) AS CATEGORY, COUNT(*) AS PRODUCTS FROM PRODUCT GROUP BY CATEGORY ORDER BY CATEGORY;

-- 코드를 입력하세요 SELECT WAREHOUSE_ID, WAREHOUSE_NAME, ADDRESS, IFNULL(FREEZER_YN, 'N') AS FREEZER_YN FROM FOOD_WAREHOUSE WHERE ADDRESS LIKE '%경기도%' ORDER BY WAREHOUSE_ID;

SELECT M.MEMBER_ID, M.MEMBER_NAME, M.GENDER, DATE_FORMAT(DATE_OF_BIRTH, '%Y-%m-%d') AS DATE_OF_BIRTH FROM MEMBER_PROFILE M WHERE M.TLNO IS NOT NULL AND M.GENDER = 'W' AND MONTH(M.DATE_OF_BIRTH) = '3' ORDER BY M.MEMBER_ID ASC;

SELECT T.ITEM_ID, C.ITEM_NAME, C.RARITY FROM ITEM_TREE AS T JOIN ITEM_INFO AS C ON C.ITEM_ID = T.ITEM_ID JOIN ITEM_INFO AS P ON P.ITEM_ID = T.PARENT_ITEM_ID WHERE P.RARITY = 'RARE' ORDER BY T.ITEM_ID DESC; -- 서브쿼리 활용 SELECT T.ITEM_ID, I.ITEM_NAME, I.RARITY FROM ITEM_TREE T JOIN ITEM_INFO I ON I.ITEM_ID = T.ITEM_ID WHERE T.PARENT_ITEM_ID IN ( SELECT ITEM_ID FROM ITEM_INFO WHERE RARITY = 'RARE' ) ORDER BY T.ITEM_ID DESC;

SELECT DISTINCT D.ID, D.EMAIL, D.FIRST_NAME, D.LAST_NAME FROM DEVELOPERS AS D JOIN SKILLCODES AS S ON (D.SKILL_CODE & S.CODE) = S.CODE WHERE S.NAME IN ('Python', 'C#') ORDER BY D.ID; -- 서브쿼리 활용 SELECT ID, EMAIL, FIRST_NAME, LAST_NAME FROM DEVELOPERS WHERE (SKILL_CODE & (SELECT CODE FROM SKILLCODES WHERE NAME = 'Python')) > 0 OR (SKILL_CODE & (SELECT CODE FROM SKILLCODES WHERE NAME = 'C#')) > 0 ORDER BY ID;

SELECT COUNT(*) AS FISH_COUNT FROM FISH_INFO F JOIN FISH_NAME_INFO N ON F.FISH_TYPE = N.FISH_TYPE WHERE N.FISH_NAME IN ('BASS', 'SNAPPER');

SELECT COUNT(*) AS COUNT FROM ECOLI_DATA WHERE (GENOTYPE & 2) = 0 -- 형질2 없음 AND ( (GENOTYPE & 1) > 0 -- 형질1 있음 OR (GENOTYPE & 4) > 0 -- 형질3 있음 );

SELECT C.ID, C.GENOTYPE, P.GENOTYPE AS PARENT_GENOTYPE FROM ECOLI_DATA AS C JOIN ECOLI_DATA AS P ON C.PARENT_ID = P.ID WHERE (C.GENOTYPE & P.GENOTYPE) = P.GENOTYPE ORDER BY C.ID ASC;

SELECT I.ITEM_ID, I.ITEM_NAME FROM ITEM_INFO I JOIN ITEM_TREE T ON I.ITEM_ID = T.ITEM_ID WHERE T.PARENT_ITEM_ID IS NULL ORDER BY I.ITEM_ID; -- 아이템 트리에 아예 등록되지 않은 경우 LEFT JOIN 활용 SELECT I.ITEM_ID, I.ITEM_NAME FROM ITEM_INFO I LEFT JOIN ITEM_TREE T ON I.ITEM_ID = T.ITEM_ID WHERE T.PARENT_ITEM_ID IS NULL ORDER BY I.ITEM_ID;

SELECT COUNT(*) AS count FROM ANIMAL_INS WHERE ANIMAL_TYPE IS NOT NULL;

SELECT SUM(PRICE) AS TOTAL_PRICE FROM ITEM_INFO WHERE RARITY = 'LEGEND';

SELECT YEAR(DIFFERENTIATION_DATE) AS YEAR, MAX(SIZE_OF_COLONY) OVER (PARTITION BY YEAR(DIFFERENTIATION_DATE)) - SIZE_OF_COLONY AS YEAR_DEV, ID FROM ECOLI_DATA ORDER BY YEAR, YEAR_DEV, ID;

SELECT I.INGREDIENT_TYPE, SUM(F.TOTAL_ORDER) AS TOTAL_ORDER FROM FIRST_HALF AS F JOIN ICECREAM_INFO AS I ON F.FLAVOR = I.FLAVOR WHERE I.INGREDIENT_TYPE IN ('sugar_based', 'fruit_based') GROUP BY I.INGREDIENT_TYPE ORDER BY TOTAL_ORDER ASC;

-- 코드를 입력하세요 SELECT CAR_TYPE, COUNT(*) AS CARS FROM CAR_RENTAL_COMPANY_CAR WHERE OPTIONS LIKE '%통풍시트%' OR OPTIONS LIKE '%열선시트%' OR OPTIONS LIKE '%가죽시트%' GROUP BY CAR_TYPE ORDER BY CAR_TYPE ASC;

SELECT MCDP_CD AS 진료과코드, COUNT(*) AS 5월예약건수 FROM APPOINTMENT WHERE MONTH(APNT_YMD) = 5 GROUP BY MCDP_CD ORDER BY 5월예약건수 ASC, 진료과코드 ASC;

SELECT ANIMAL_TYPE, COUNT(*) AS count FROM ANIMAL_INS WHERE ANIMAL_TYPE IN ('Cat', 'Dog') GROUP BY ANIMAL_TYPE ORDER BY ANIMAL_TYPE ASC;

SELECT TRUNCATE(PRICE, -4) AS PRICE_GROUP, COUNT(*) AS PRODUCTS FROM PRODUCT GROUP BY PRICE_GROUP ORDER BY PRICE_GROUP ASC; SELECT FLOOR(PRICE / 10000) * 10000 AS PRICE_GROUP, -- 만원 단위 최소값 라벨 COUNT(*) AS PRODUCTS FROM PRODUCT GROUP BY PRICE_GROUP ORDER BY PRICE_GROUP ASC;

SELECT HOUR(DATETIME) AS HOUR, COUNT(*) AS COUNT FROM ANIMAL_OUTS WHERE HOUR(DATETIME) BETWEEN 9 AND 19 GROUP BY HOUR ORDER BY HOUR;

WITH totals AS ( SELECT HE.EMP_NO, SUM(HG.SCORE) AS SCORE FROM HR_EMPLOYEES HE JOIN HR_GRADE HG ON HE.EMP_NO = HG.EMP_NO WHERE HG.YEAR = 2022 GROUP BY HE.EMP_NO ), max_score AS ( SELECT MAX(SCORE) AS MAX_SCORE FROM totals ) SELECT T.SCORE, HE.EMP_NO, HE.EMP_NAME, HE.POSITION, HE.EMAIL FROM TOTALS T JOIN HR_EMPLOYEES HE ON HE.EMP_NO = T.EMP_NO JOIN max_score M ON T.SCORE = M.MAX_SCORE ORDER BY HE.EMP_NO; SELECT tt.SCORE, he.EMP_NO, he.EMP_NAME, he.POSITION, he.EMAIL FROM ( SELECT HE.EMP_NO, SUM(HG.SCORE) AS SCORE, RANK() OVER (ORDER BY SUM(HG.SCORE) DESC) AS rnk FROM HR_EMPLOYEES HE JOIN HR_GRADE HG ON HE.EMP_NO = HG.EMP_NO WHERE HG.YEAR = 2022 GROUP BY HE.EMP_NO ) tt JOIN HR_EMPLOYEES he ON he.EMP_NO = tt.EMP_NO WHERE tt.rnk = 1 ORDER BY he.EMP_NO;

SELECT ROUTE, CONCAT(ROUND(SUM(D_BETWEEN_DIST), 1), 'km') AS TOTAL_DISTANCE, CONCAT(ROUND(AVG(D_BETWEEN_DIST), 2), 'km') AS AVERAGE_DISTANCE FROM SUBWAY_DISTANCE GROUP BY ROUTE ORDER BY SUM(D_BETWEEN_DIST) DESC;

SELECT COUNT(*) AS FISH_COUNT, I.FISH_NAME AS FISH_NAME FROM FISH_INFO F JOIN FISH_NAME_INFO I ON F.FISH_TYPE = I.FISH_TYPE GROUP BY I.FISH_NAME ORDER BY FISH_COUNT DESC;

SELECT COUNT(*) AS FISH_COUNT, MONTH(TIME) AS MONTH FROM FISH_INFO GROUP BY MONTH ORDER BY MONTH;

SELECT B.BOOK_ID, A.AUTHOR_NAME, DATE_FORMAT(B.PUBLISHED_DATE, '%Y-%m-%d') AS PUBLISHED_DATE FROM BOOK B JOIN AUTHOR A ON B.AUTHOR_ID = A.AUTHOR_ID WHERE B.CATEGORY = '경제' ORDER BY PUBLISHED_DATE ASC;

SELECT P.PRODUCT_CODE, SUM(P.PRICE * O.SALES_AMOUNT) AS SALES FROM PRODUCT P JOIN OFFLINE_SALE O ON P.PRODUCT_ID = O.PRODUCT_ID GROUP BY P.PRODUCT_CODE ORDER BY SALES DESC, P.PRODUCT_CODE ASC;