[SQL] SQL 문법 복습 문제

JH4·2023년 3월 21일
0

SQL

목록 보기
15/49

문제 1) 평균 이상 포인트를 가지고 있으면 '잘 하고 있어요' / 낮으면 '열심히 합시다!' 표시하기!

select pu.point_user_id, pu.point,
case 
when pu.point > (select avg(pu2.point) from point_users pu2) then '잘 하고 있어요!'
else '열심히 합시다!'
end as 'msg'
from point_users pu

문제 2) 이메일 도메인별 유저의 수 세어보기

select domain, count(*) as cnt from (
	select SUBSTRING_INDEX(email,'@',-1) as domain from users
) a
group by domain

문제 3) '화이팅'이 포함된 오늘의 다짐만 출력해보기

select * from checkins c
where c.comment like '%화이팅%'

문제 4) 수강등록정보(enrolled_id)별 전체 강의 수와 들은 강의의 수 출력해보기

with lecture_done as (
	select enrolled_id, count(*) as cnt_done from enrolleds_detail ed 
	where done = 1
	group by enrolled_id
), lecture_total as (
	select enrolled_id, count(*) as cnt_total from enrolleds_detail ed 
	group by enrolled_id
)


select a.enrolled_id, a.cnt_done, b.cnt_total from lecture_done a
inner join lecture_total b on a.enrolled_id = b.enrolled_id

문제 5) 수강등록정보(enrolled_id)별 전체 강의 수와 들은 강의의 수, 그리고 진도율 출력해보기

with table1 as (
	select enrolled_id, count(*) as done_cnt from enrolleds_detail
	where done = 1
	group by enrolled_id
), table2 as (
	select enrolled_id, count(*) as total_cnt from enrolleds_detail
	group by enrolled_id
)

select a.enrolled_id,
       a.done_cnt,
       b.total_cnt,
       round(a.done_cnt/b.total_cnt,2) as ratio
  from table1 a
 inner join table2 b on a.enrolled_id = b.enrolled_id
profile
개발일지

0개의 댓글

관련 채용 정보

Powered by GraphCDN, the GraphQL CDN