[SQL문제풀기] solvesql - level1 문제들

문제

1. 모든 데이터 조회하기 https://solvesql.com/problems/select-all/

2. 일부 데이터 조회하기 https://solvesql.com/problems/select-where/

3. 데이터 정렬하기 https://solvesql.com/problems/order-by/

4. 데이터 그룹으로 묶기 https://solvesql.com/problems/group-by/

5. 특정 컬럼만 조회하기 https://solvesql.com/problems/select-column/

6. 몇 분이서 오셨어요? https://solvesql.com/problems/size-of-table/

7. 최근 올림픽이 개최된 도시 https://solvesql.com/problems/olympic-cities/

8. 우리 플랫폼에 정착한 판매자 1 https://solvesql.com/problems/settled-sellers-1/

9. 최고의 근무일을 찾아라 https://solvesql.com/problems/best-working-day/

10. 첫 주문과 마지막 주문 https://solvesql.com/problems/first-and-last-orders/

11. 많이 주문한 테이블 찾기 https://solvesql.com/problems/find-tables-with-high-bill/

12. 레스토랑의 일일 평균 매출액 계산하기 https://solvesql.com/problems/sales-summary/

13. 레스토랑의 영업일 https://solvesql.com/problems/restaurant-business-day/

14. 펭귄 조사하기 https://solvesql.com/problems/inspect-penguins/

15. 지자체별 따릉이 정류소 개수 세기 https://solvesql.com/problems/count-stations/

16. 크리스마스 게임 찾기 https://solvesql.com/problems/find-christmas-games/

17. 메리 크리스마스 2024 https://solvesql.com/problems/merry-christmas-2024/

SQLite

내가 작성한 정답

1. 모든 데이터 조회하기

select * from points

2. 일부 데이터 조회하기

select *
from points
where quartet = 'I'

3. 데이터 정렬하기

select *
from points
where quartet = 'I'
order by y

4. 데이터 그룹으로 묶기

select quartet, 
       round(avg(x),2) x_mean,
       round(var_samp(x),2) x_var,
       round(avg(y),2) y_mean,
       round(var_samp(y),2) y_var
from points
group by quartet

5. 특정 컬럼만 조회하기

select x,y from points

6. 몇 분이서 오셨어요?

select * 
from tips
where size%2==1

7. 최근 올림픽이 개최된 도시

select year, upper(substr(city,1,3)) city
from games
where year >= 2000
order by 1 desc

8. 우리 플랫폼에 정착한 판매자 1

select seller_id, count(distinct order_id) orders 
from olist_order_items_dataset
group by seller_id
having count(distinct order_id) >= 100

9. 최고의 근무일을 찾아라

// 정답 1
select * 
from (select day, round(sum(tip),2) tip_daily
      from tips
      group by 1
      order by 2 desc)
limit 1

// 정답 2
select day, max(tip_daily) tip_daily
from (select day, round(sum(tip),2) tip_daily
      from tips
      group by 1)

10. 첫 주문과 마지막 주문

select date(min(order_purchase_timestamp)) first_order_date,
			 date(max(order_purchase_timestamp)) last_order_date
from olist_orders_dataset

11. 많이 주문한 테이블 찾기

select *
from tips
where total_bill > (select avg(total_bill) from tips)

12. 레스토랑의 일일 평균 매출액 계산하기

select round(avg(tb),2) avg_sales
from (select sum(total_bill) tb from tips group by day)

13. 레스토랑의 영업일

select distinct day day_of_week from tips 

14. 펭귄 조사하기

// 정답 1
select species, island
from penguins
group by island, species
order by 2,1

// 정답 2
select distinct species, island
from penguins
order by 2,1

15. 지자체별 따릉이 정류소 개수 세기

select local, count(distinct station_id) num_stations
from station
group by local
order by 2 

16. 크리스마스 게임 찾기

select game_id, name, year
from games
where name like '%Christmas%' or name like '%Santa%'

// 대문자 소문자 구분 없애기
select game_id, name, year
from games
where lower(name) like '%christmas%' or Upper(name) like '%SANTA%'

17. 메리 크리스마스 2024

select "Merry Christmas!"