기록

1. lol_feedbacks 테이블에서 만족도 점수(satisfaction_score)에 따라 피드백을 내림차순으로 정렬하는 쿼리를 작성해주세요!

select *

from lol_feedbacks

order by satisfaction_score desc

2. lol_feedbacks 테이블에서 각 유저별로 최신 피드백을 찾는 쿼리를 작성해주세요!

select user_name, max(feedback_date)

from lol_feedbacks

group by user_name

3. lol_feedbacks 테이블에서 만족도 점수가 5점인 피드백의 수를 계산하는 쿼리를 작성해주세요!

select count(*)

from lol_feedbacks

where satisfaction_score = 5

4. lol_feedbacks 테이블에서 가장 많은 피드백을 남긴 상위 3명의 고객을 찾는 쿼리를 작성해주세요!

select user_name,

count(satisfaction_score)

from lol_feedbacks

group by user_name

order by 2 desc limit 3

5. lol_feedbacks 테이블에서 평균 만족도 점수가 가장 높은 날짜를 찾는 쿼리를 작성해주세요!

select feedback_date

from (

select feedback_date, avg(satisfaction_score)

from lol_feedbacks

group by feedback_date

order by 2 desc limit 1

) a

• 배운 것
  • limit 는 order by 절에서 특정한 갯수만큼 가져오고 싶을 때 사용함
  • 서브쿼리는 반드시 별칭을 써줘야 함. 안써주면 오류남

1. lol_users 테이블에서 각 유저의 레이팅(rating) 순위를 계산하는 쿼리를 작성해주세요! 전체 지역(region) 기준이고 순위는 레이팅이 높을수록 높아야해요. (e.g. rating 1400 유저의 순위 > rating 1350 유저의 순위)

SELECT *

FROM lol_users

order by rating desc

2. lol_users 테이블에서 가장 늦게 게임을 시작한(join_date) 유저의 이름을 선택하는 쿼리를 작성해주세요

SELECT name

FROM lol_users

where join_date =

(select max(join_date)

from lol_users

)

3. lol_users 테이블에서 지역별로 레이팅이 높은 순으로 유저들을 정렬해서 나열하는 쿼리를 작성해주세요!

select *

from lol_users

order by region, rating desc

4. lol_users 테이블에서 지역별로 평균 레이팅을 계산하는 쿼리를 작성해주세요!

select region, avg(rating) avg_rating

from lol_users

group by region

배운점

• ORDER BY region, rating -> ORDER BY는 여러 컬럼을 두게 되면 첫번째 컬럼을 기준으로 정렬 후, 그 결과 내에서 두번째 컬럼을 기준으로 정렬함 즉 region을 기준으로 오름차순 정렬 후, 그 결과 내부에서 rating을 기준으로 오름차순 정렬
  • 첫 번째 기준(region): 데이터를 region 열의 값에 따라 정렬
  • 두 번째 기준(rating): region이 같은 데이터끼리 rating 열의 값에 따라 정렬
• GROUP BY는 SQL에서 데이터를 그룹화하여 그룹별로 집계 함수(SUM, COUNT, MAX, AVG, 등)를 사용할 때 사용함 
  • 집계함수랑 같이 쓰는 함수임

SELECT category, MAX(price) AS max_price
FROM sales
GROUP BY category;

1. team_projects 테이블에서 AWS 예산(aws_cost)이 40000 이상 들어간 프로젝트들의 이름을 선택하는 쿼리를 작성해주세요!

SELECT *

FROM team_projects

where aws_cost >= 40000

2. team_projects 테이블에서 2022년에 시작된 프로젝트를 선택하는 쿼리를 작성해주세요! 단, start_date < ‘2023-01-01’ 조건을 사용하지 말고 쿼리를 작성해주세요!

SELECT *

FROM team_projects

where year(start_date) = 2022

3. team_projects 테이블에서 현재 진행중인 프로젝트를 선택하는 쿼리를 작성해주세요. 단, 지금 시점의 날짜를 하드코딩해서 쿼리하지 말아주세요!

SELECT *

FROM team_projects

where start_date <= current_date()

and end_date >= current_date()

4. team_projects 테이블에서 각 프로젝트의 지속 기간을 일 수로 계산하는 쿼리를 작성해주세요!

SELECT name,

datediff(end_date, start_date) period

FROM team_projects

배운 것

• 날짜에서 연도만 추출하는 sql ->  year(start_date)
• 현재 날짜를 확인하는 함수 -> current_date()
• 날짜 기간을 확인 확인하는 함수, end_date와 start_date의 차이를 일 단위로 계산 -> DATEDIFF(end_date, start_date)

1. 식당별 평균 음식 주문 금액과 주문자의 평균 연령을 기반으로 Segmentation 하기

• 평균 음식 주문 금액 기준 : 5,000 / 10,000 / 20,000 / 30,000 초과
• 평균 연령 : ~ 20대 / 30대 / 40대 / 50대 이상

• 두 테이블 모두에 데이터가 있는 경우만 조회, 식당 이름 순으로 오름차순 정렬

select f.restaurant_name,
		case when avg(f.price) <= 5000 then 'price_group1'
			when avg(f.price) <= 10000 then 'price_group2'
			when avg(f.price) <= 20000 then 'price_group3'
			when avg(f.price) <= 30000 then 'price_group3'
			else 'price_group5' end price_group,
		case when avg(c.age) <= 29 then 'age_group1'
			when avg(c.age) <= 39 then 'age_group2'
			when avg(c.age) <= 49 then 'age_group3'
			else 'age_group4' end age_group
from food_orders f
	inner join customers c
		on f.customer_id = c.customer_id
group by 1
order by 1 asc

음식 종류별 가장 높은 주문 금액과 가장 낮은 주문금액을 조회하고, 가장 낮은 주문금액 순으로 (내림차순) 정렬하기

select cuisine_type,
	max(price),
	min(price)
from food_orders
group by cuisine_type
order by min(price) desc

다음의 조건으로 배달시간이 늦었는지 판단하는 값을 만들어주세요.

• 주중 : 25분 이상
• 주말 : 30분 이상

select order_id,
		restaurant_name,
		day_of_the_week,
		delivery_time,
		case when day_of_the_week = 'Weekday' then if(delivery_time >= 25, "Late", "On-time")
			when day_of_the_week = 'Weekend' then if (delivery_time >= 30, "Late", "On-time")
			end "지연여부"
from food_orders

1. sparta_students 테이블에서 모든 학생의 이름(name)과 트랙(track)을 선택하는 쿼리를 작성해주세요!

select name, track

from sparta_student

2. sparta_students 테이블에서 Unity 트랙 소속이 아닌 학생들을 선택하는 쿼리를 작성해주세요!

select *

from sparta_students

where track <> ‘Unity’

3. sparta_students 테이블에서 입학년도(enrollment_year)가 2021년인 학생과 2023년인 학생을 선택하는 쿼리를 작성해주세요!

select *

from sparta_students

where enrollment_year in ('2021', '2023')

4. sparta_students 테이블에서 Node.js 트랙 소속이고 학점이 ‘A’인 학생의 입학년도를 선택하는 쿼리를 작성해주세요!

select enrollment_year

from sparta_students

where track = 'Node.js'

and grade = 'A'

1. orders 테이블에서 주문 수량(amount)이 2개 이상인 주문을 진행한 소비자의 ID(customer_id)만 선택하는 쿼리를 작성해주세요!

select customer_id

from orders

where amount >= 2

2. orders 테이블에서 2023년 11월 2일 이후에 주문된 주문 수량(amount)이 2개 이상인 주문을 선택하는 쿼리를 작성해주세요!

select *

from orders

where order_date > ‘2023-11-02’

and amount >= 2

3. orders 테이블에서 주문 수량이 3개 미만이면서 배송비(shipping_fee)가 15000원보다 비싼 주문을 선택하는 쿼리를 작성해주세요!

select *

from orders

where amount < 3

and shipping_fee > 15000

4. orders 테이블에서 배송비가 높은 금액 순으로 정렬하는 쿼리를 작성해주세요!

select *

from orders

order by shipping_fee desc

1. products 테이블에서 제품 이름(product_name)과 가격(price)만을 선택하는 쿼리를 작성해주세요.

     select product_name, price

     from products

2. products 테이블에서 제품 이름에 '프로'가 포함된 모든 제품을 선택하는 쿼리를 작성해주세요.

      select *

      from products

      where product_name like '%프로%'

3. products 테이블에서 제품 이름이 '갤'로 시작하는 모든 제품을 선택하는 쿼리를 작성해주세요.

       select *

       from products

       where product_name like '갤%'

4. products 테이블에서 모든 제품을 구매하기 위해 필요한 돈을 계산하는 쿼리를 작성해주세요.

      select sum(price)

      from products