240624_TIL

아무리 사소하더라도 배움이 없는 날은 없다.

오늘부터 SQL 코드카타는
프로그래머스에서 아직 안 풀었던 SQL문제를 푸는 걸로 대체하기로.
안 푼 문제로 필터링했더니 난이도 오름차순으로 배치되길래
오늘은 간단한 문제들로만 10개를 풀었다. 시간은 포스팅 작성 포함해 30분쯤 걸린 듯.

문제 링크

SELECT Sum(price) AS TOTAL_PRICE
FROM   item_info
WHERE  rarity = 'LEGEND';

문제 링크

SELECT Concat(Max(length), 'cm') AS MAX_LENGTH
FROM   fish_info
WHERE  length IS NOT NULL;

문제 링크

SELECT Count(*) AS FISH_COUNT
FROM   fish_info
WHERE  length IS NULL; 

문제 링크

SELECT Count(*) AS FISH_COUNT,
       fni.fish_name
FROM   fish_info fi
       INNER JOIN fish_name_info fni
               ON fi.fish_type = fni.fish_type
GROUP  BY 2
ORDER  BY 1 DESC;

문제 링크

SELECT it.item_id,
       ii.item_name
FROM   item_tree it
       INNER JOIN item_info ii
               ON it.item_id = ii.item_id
WHERE  it.parent_item_id IS NULL;

문제 링크

SELECT he.dept_id,
       hd.dept_name_en,
       Round(Avg(he.sal), 0) AS AVG_SAL
FROM   hr_employees he
       INNER JOIN hr_department hd
               ON he.dept_id = hd.dept_id
GROUP  BY 1,
          2
ORDER  BY 3 DESC; 

문제 링크

SELECT Year(ym)               AS YEAR,
       Round(Avg(pm_val1), 2) AS 'PM10',
       Round(Avg(pm_val2), 2) AS 'PM2.5'
FROM   air_pollution
WHERE  location1 = '경기도'
       AND location2 = '수원'
GROUP  BY 1
ORDER  BY 1; 

문제 링크

SELECT Count(*) AS FISH_COUNT
FROM   fish_info
WHERE  Year(time) = '2021'; 

문제 링크

SELECT id,
       email,
       first_name,
       last_name
FROM   developer_infos
WHERE  skill_1 = 'Python'
        OR skill_2 = 'Python'
        OR skill_3 = 'Python'
ORDER  BY 1;

문제 링크

SELECT Count(*)    AS FISH_COUNT,
       Month(time) AS MONTH
FROM   fish_info
GROUP  BY 2
ORDER  BY 2;

---