SQL 1주차 라이브세션 과제 (1~3회) (작성중)

1회차

/*문제1 date 컬럼이 2023-01-01 보다 큰 날짜의 game_account_id, game_actor_id, serverno를 추출해주세요.*/

select 	game_account_id,
		game_actor_id,
		serverno,
		date
from users u
where date > '2023-01-01'

 

 

/*문제2 level > 10,

* serverno !=1

* etc_str2='레벨업 패키지' or '시즌패스'

* etc_str1 '상점에서 구매'

* date, ip_addr, exp, zone_id 조회 date 기준 내림차순 */

select	date,
		ip_addr,
		exp,
		zone_id
from users u 
where serverno > 1
and etc_str2 in ('레벨업%' or '시즌패스')
and etc_str1 in ('상점에서 구매')
order by date desc;

 

/*문제3 레벨구간을 구분하고 game_actor_id, level, levelgroup, date 조회 date기준 내림차순*/

select 	game_account_id,
		level,
		case when level between 1 and 10 then '1~10Lv'
 			 when level between 11 and 20 then '11~20Lv'
			 when level between 21 and 30 then '21~30Lv'
			 when level between 31 and 40 then '31~40Lv'
			 when level between 41 and 50 then '41~50Lv'
			 when level between 51 and 60 then '51~60Lv'
			 when level between 61 and 70 then '61~70Lv'
			 when level between 71 and 80 then '71~80Lv'
			 when level between 81 and 90 then '81~90Lv'
			 else '91~100Lv'
			 end 'levelgroup',
	 	date		
from users u
order by date desc

 

/*문제4 date를 yyyy-mm 으로 바꾸고 이름 m

game_account_id, game_actor_id 추출 월기준으로 오름차순 월이 같으면 game_actor_id 기준 내림 차순*/

select 	game_account_id,
		game_actor_id,
		date_format(date,'%Y-%m') as 'm',
		date_format(date,'%m') as 'm2'
from users u 
order by date_format(date,'%m') desc, game_actor_id desc

 

---

2회차

*(문제1)group by 절을 사용하여, 서버별 게임캐릭터id수(중복값 허용x)와 평균 경험치를 추출해주세요.

select distinct(game_actor_id),
		avg(exp)
from users u 
group by game_actor_id

 

(정답)

select 	serverno,
		count(distinct game_actor_id ) actor_cnt,
		avg(exp) avg_exp
from users u 
group by serverno

 

 

 

 

 

 

(문제2)group by 와 having 절을 사용하여, 날짜 별(yyyy-mm-dd) 게임캐릭터id수(중복값 허용x)를 구하고,

그 값이 10개를 초과하는 경우를 추출해주세요.

select 	date,
		count(game_actor_id) as '캐릭터_cnt'
from users u 
group by date
having count(game_actor_id) > 10

 

(정답)

select 	date,
		count(distinct game_actor_id) as cnt
from users u 
group by date
having count(game_actor_id) > 10

 

 

 

 

 

 

(문제3)위와 같은 문제를 having 이 아닌 인라인 뷰 subquery 를 사용하여, 추출해주세요.

select *
from
	(select  date,
			count(game_actor_id) as 'cnt'
	from users u
	group by date) a
where cnt > 10

 

(정답)

select *
from
	(select  date,
			count(distinct game_actor_id) as 'cnt'
	from users u
	group by date) a
where cnt > 10

 

---

3회차