[실무]solvesql SQLite 연습문제 난이도1

■ 출처

- 코딩테스트 : SQLite / 연습문제 / 난이도1

https://solvesql.com/problems/?page=1

https://solvesql.com/problems/?page=1

 

 

■ 연습문제

1. 모든 데이터 조회하기

SELECT 
  * 
FROM 
  points

 

 

2. 일부 데이터 조회하기

SELECT
  *
FROM
  points
WHERE
  quartet = 'I'

 

 

3. 데이터 정렬하기

SELECT
  *
FROM
  points
WHERE
  quartet = 'I'
ORDER BY
  y ASC

 

 

4. 데이터 그룹으로 묶기

SELECT
  quartet,
  ROUND(AVG(x), 2) AS x_mean,
  ROUND(VARIANCE(x),2) AS x_var,
  ROUND(AVG(y),2) AS y_mean,
  ROUND(VARIANCE(y),2) AS y_var
FROM
  points
GROUP BY
  quartet

 

 

5. 특정 컬럼만 조회하기

SELECT
  x, 
  y
FROM
  points

 

6. 몇 분이서 오셨어요?

DIV : 몫 구하는 함수

- 예시) 5 DIV 3  →  몫 1

% : 나머지 구하는 함수

- 예시) 5 % 3   →  나머지 2

SELECT
  *
FROM
  Tips 
WHERE
  size%2 = 1

 

 

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

문자열 자르기 함수 : substr(oracle) / left,right(MySQL)

- 예시) substr('대한민국 만세', 2, 4)    →  '한민국 '

UPPER 함수 : 대문자로 변환시키는 함수

LOWER 함수 : 소문자로 변환 시키는 함수

SELECT
  year,
  UPPER(substr(city, 1, 3)) AS city 
FROM
  games
WHERE
  year >= 2000
ORDER BY
  year DESC

 

 

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

DISTINCT 함수 : 중복된 행을 제외하여 조회

SELECT
  seller_id, 
  COUNT(DISTINCT(order_id)) AS orders
FROM
  olist_order_items_dataset 
GROUP BY
  seller_id
HAVING  
  orders >= 100

 

 

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

LIMIT N 함수 : N만큼 결과를 제안하여 조회하는 함수

SELECT
  day,
  ROUND(sum(tip),2) AS tip_daily
FROM
  tips
GROUP BY
  day
ORDER BY
  tip_daily DESC
LIMIT 
  1

 

 

10. 첫 주문과 마지막 주문

SELECT
  DATE(MIN(order_purchase_timestamp)) AS first_order_date ,
  DATE(MAX(order_purchase_timestamp)) AS last_order_date
FROM
  olist_orders_dataset

 

 

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

SELECT
  *
FROM
  tips 
WHERE
  total_bill > (SELECT AVG(total_bill) FROM tips)

 

 

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

SELECT
  ROUND(AVG(Day_total_bill.total), 2) AS avg_sales
FROM
  (SELECT 
    day, 
    SUM(total_bill) AS total
  FROM 
    tips 
  GROUP BY
    day) AS Day_total_bill

 

 

13. 레스토랑의 영업일

DISTINCT 함수 : 중복된 행을 제외하여 조회

SELECT
  DISTINCT(day) AS day_of_week
FROM
  tips

 

 

14. 크리스마스 게임 찾기

SELECT
  game_id, 
  name, 
  year
FROM
  games 
WHERE
  (name LIKE '%Christmas%') OR (name LIKE '%Santa%')

 

 

15. 펭귄 조사하기

SELECT
  DISTINCT(species), 
  island
FROM
  Penguins 
ORDER BY  
  island ASC

 

 

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

SELECT
  local,
  COUNT(station_id) AS num_stations 
FROM
  station 
GROUP BY
  local
ORDER BY
  num_stations ASC

 

 

17. 메리 크리스마스 2024

SELECT CONCAT('Merry Christmas!')