Re: 오늘의 마지막 문제: 상호명에 떡볶이를 포함하고 있는 떡볶이 프랜차이즈 상호명과 매장수를 다음과 같이 출력하시오!

[17기_김정명]

SELECT CASE 
    WHEN 상호명 LIKE '%동대문엽기%' THEN '동대문엽기떡볶이' 
    WHEN 상호명 LIKE '%신전떡볶이%' THEN '신전떡볶이'
    WHEN 상호명 LIKE '%감탄떡볶이%' THEN '감탄떡볶이'
    WHEN 상호명 LIKE '%두끼떡볶이%' THEN '두끼떡볶이'
    WHEN 상호명 LIKE '%죠스떡볶이%' THEN '죠스떡볶이'
    WHEN 상호명 LIKE '%떡볶이참잘하는집떡참%' THEN '떡볶이참잘하는집떡참'
    WHEN 상호명 LIKE '%신참떡볶이%' THEN '신참떡볶이'
    WHEN 상호명 LIKE '%우리할매떡볶이%' THEN '우리할매떡볶이'
    WHEN 상호명 LIKE '%응급실국물떡볶이%' THEN '응급실국물떡볶이'
    WHEN 상호명 LIKE '%걸작떡볶이%' THEN '걸작떡볶이' 
    ELSE 상호명 END AS 상호,
    COUNT(*) AS 건수
FROM market_2022
WHERE 상호명 LIKE '%떡볶이%'
GROUP BY CASE 
    WHEN 상호명 LIKE '%동대문엽기%' THEN '동대문엽기떡볶이' 
    WHEN 상호명 LIKE '%신전떡볶이%' THEN '신전떡볶이'
    WHEN 상호명 LIKE '%감탄떡볶이%' THEN '감탄떡볶이'
    WHEN 상호명 LIKE '%두끼떡볶이%' THEN '두끼떡볶이'
    WHEN 상호명 LIKE '%죠스떡볶이%' THEN '죠스떡볶이'
    WHEN 상호명 LIKE '%떡볶이참잘하는집떡참%' THEN '떡볶이참잘하는집떡참'
    WHEN 상호명 LIKE '%신참떡볶이%' THEN '신참떡볶이'
    WHEN 상호명 LIKE '%우리할매떡볶이%' THEN '우리할매떡볶이'
    WHEN 상호명 LIKE '%응급실국물떡볶이%' THEN '응급실국물떡볶이'
    WHEN 상호명 LIKE '%걸작떡볶이%' THEN '걸작떡볶이' 
    ELSE 상호명 END
ORDER BY 2 DESC;

[17기_김정명]

SELECT ename, sal,
RANK() OVER(ORDER BY sal DESC) AS rank순위,
round(RANK() OVER (ORDER BY sal DESC) / 14,2) AS rank비율,
DENSE_RANK() OVER(ORDER BY sal DESC) AS dense순위,
round(DENSE_RANK() OVER (ORDER BY sal DESC) / 14,2) AS dense비율,
round(CUME_DIST() OVER (ORDER BY sal DESC),2) AS cume순위비율
FROM emp;

[유연수]

SELECT ename, sal,
RANK() OVER(ORDER BY sal DESC) AS rank순위,
round(RANK() OVER (ORDER BY sal DESC) / 14,2) AS rank비율,
DENSE_RANK() OVER(ORDER BY sal DESC) AS dense순위,
round(DENSE_RANK() OVER (ORDER BY sal DESC) / 14,2) AS dense비율,
round(CUME_DIST() OVER (ORDER BY sal DESC),2) AS cume순위비율,
round(sum(sal) over (order by sal desc) / sum(sal) over (), 2) ,
sum(sal) over (order by sal desc) ,
sum(sal) over ()

FROM emp;

[유연수]

SELECT ename, sal,
ROUND((SELECT COUNT(*) FROM emp e2 WHERE e2.sal >= e1.sal) / (SELECT COUNT(*) FROM emp), 2) AS cume순위비율,
ROUND(CUME_DIST() OVER (ORDER BY sal DESC), 2) AS cume순위비율
FROM emp e1
ORDER BY sal DESC;