[PROGRAMMERS] SELECT
[Notification]
DBMS : MySQL
1. SELECT LV.1
1. 강원도에 위치한 생산공장 목록 출력하기
1.1 정답
SELECT FACTORY_ID, FACTORY_NAME, ADDRESS FROM FOOD_FACTORY
WHERE ADDRESS LIKE "%강원도%"
ORDER BY FACTORY_ID ASC
2. SELECT LV.2
1. 3월에 태어난 여성 회원 목록 출력하기
1.1 정답
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 MEMBER_ID ASC
2. 재구매가 일어난 상품과 회원 리스트 구하기
2.1 정답
SELECT USER_ID,PRODUCT_ID FROM ONLINE_SALE
GROUP BY USER_ID, PRODUCT_ID HAVING COUNT(PRODUCT_ID) >1
ORDER BY USER_ID, PRODUCT_ID DESC
3. 특정 물고기를 잡은 총 수 구하기
3.1 정답
SELECT COUNT(*) AS FISH_COUNT
FROM FISH_INFO AS A, FISH_NAME_INFO AS B
WHERE (A.FISH_TYPE=B.FISH_TYPE) AND ((B.FISH_NAME = "BASS") OR (B.FISH_NAME = "SNAPPER"))
4. 업그레이드 된 아이템 구하기
4.1 정답
SELECT ITEM_ID, ITEM_NAME, RARITY
FROM ITEM_INFO
WHERE ITEM_ID IN (SELECT ITEM_ID FROM ITEM_TREE WHERE PARENT_ITEM_ID IN (SELECT ITEM_ID FROM ITEM_INFO WHERE RARITY="RARE"))
ORDER BY ITEM_ID DESC
5. 조건에 맞는 개발자 찾기
5.1 정답
SELECT B.ID, B.EMAIL, B.FIRST_NAME, B.LAST_NAME
FROM
(SELECT SUM(CODE) CODE FROM SKILLCODES
WHERE NAME = "Python" or NAME = "C#") AS A, DEVELOPERS AS B
WHERE B.SKILL_CODE & A.CODE >0
ORDER BY B.ID ASC
6. 부모의 형질을 모두 가지는 대장균 찾기
6.1 정답
SELECT A.ID, A.GENOTYPE, B.GENOTYPE AS PARENT_GENOTYPE
FROM ECOLI_DATA AS A, ECOLI_DATA AS B
WHERE A.PARENT_ID = B.ID AND (B.GENOTYPE & A.GENOTYPE) = B.GENOTYPE
ORDER BY A.ID ASC
3. SELECT LV.3
1. 대장균들의 자식 수 구하기
1.1 정답
SELECT B.ID, IF (A.COUNT IS NULL, 0, A.COUNT) AS CHILD_COUNT
FROM
(SELECT IF (PARENT_ID IS NULL, 0, PARENT_ID) AS PARENT_ID, COUNT(PARENT_ID) AS COUNT FROM ECOLI_DATA
GROUP BY PARENT_ID) AS A
RIGHT OUTER JOIN ECOLI_DATA AS B
ON A.PARENT_ID = B.ID
2. 대장균의 크기에 따라 분류하기 1
2.1 정답
SELECT ID,
CASE WHEN SIZE_OF_COLONY<=100 THEN "LOW"
WHEN SIZE_OF_COLONY<=1000 THEN "MEDIUM"
ELSE "HIGH"
END AS SIZE
FROM ECOLI_DATA
ORDER BY ID ASC
3. 대장균의 크기에 따라 분류하기 2
3.1 정답
SELECT A.ID,
CASE WHEN A.PERCENT<=0.25 THEN "CRITICAL"
WHEN A.PERCENT<=0.5 THEN "HIGH"
WHEN A.PERCENT<=0.75 THEN "MEDIUM"
ELSE "LOW"
END AS COLONY_NAME
FROM
(SELECT ID, SIZE_OF_COLONY,
PERCENT_RANK() OVER(ORDER BY SIZE_OF_COLONY DESC) AS PERCENT
FROM ECOLI_DATA) AS A
ORDER BY ID ASC
4. SELECT LV.4
1. 서울에 위치한 식당 목록 출력하기
1.1 정답
SELECT
B.REST_ID, B.REST_NAME, B.FOOD_TYPE, B.FAVORITES, B.ADDRESS, A.SCORE
FROM
(SELECT REST_ID, ROUND(AVG(REVIEW_SCORE),2) AS SCORE FROM REST_REVIEW
GROUP BY REST_ID) AS A, REST_INFO AS B
WHERE (A.REST_ID = B.REST_ID) AND B.ADDRESS LIKE "서울%"
ORDER BY A.SCORE DESC, B.FAVORITES DESC
2. 오프라인/온라인 판매 데이터 통합하기
2.1 정답
SELECT
DATE_FORMAT(SALES_DATE,"%Y-%m-%d") AS SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT
FROM ONLINE_SALE
WHERE SALES_DATE LIKE "2022-03%"
UNION ALL
SELECT
DATE_FORMAT(SALES_DATE,"%Y-%m-%d") AS SALES_DATE, PRODUCT_ID, NULL, SALES_AMOUNT
FROM OFFLINE_SALE
WHERE SALES_DATE LIKE "2022-03%"
ORDER BY SALES_DATE ASC, PRODUCT_ID, USER_ID
3. 특정 세대의 대장균 찾기
3.1 정답
SELECT ID
FROM ECOLI_DATA
WHERE PARENT_ID IN (SELECT ID FROM ECOLI_DATA WHERE PARENT_ID IN (SELECT ID FROM ECOLI_DATA WHERE PARENT_ID IS NULL))
ORDER BY ID ASC
댓글남기기