🚫[실무]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(second_tips.total), 2) AS avg_sales
FROM 
  (SELECT day, SUM(total_bill) AS total
   FROM tips
   GROUP BY day) AS second_tips

 

 

13. 레스토랑의 영업일

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

SELECT
  distinct(day) as day_of_week
FROM
  tips