SQL 실습_final_sql

1.
다음과 같은 테이블을 생성하시오.

 

CREATE TABLE tb_test(
	t_name VARCHAR(5) NULL,
	t_season VARCHAR(5) NULL,
	t_amount INT(5) NULL
);​

INSERT INTO tb_test
VALUES
	 ('김성주','겨울',10)
	,('정동영','여름',15) 
	,('김성주','가을',25) 
	,('김성주','봄',3) 
	,('김성주','봄',37) 
	,('정동영','겨울',40) 
	('김성주','여름',14)
	,('김성주','겨울',22) 
	,('정동영','여름',64) ;

2.
다음과 같은 테이블을 생성하시오.

SELECT
	 t.t_season AS '계절'
	,SUM(if(t.t_name = '김성주', t.t_amount,0)) AS '김성주'
	,SUM(if(t.t_name = '정동영', t.t_amount,0)) AS '정동영'
	,SUM(t.t_amount) AS '합계'
FROM
	tb_test AS t
GROUP BY t.t_season
ORDER BY FIELD(t.t_season,'봄','여름','가을','겨울');

 

3.
구매자별 구매이력 중 상품별로 구매수량이 20개 이상인 상품의 목록을 추출하여 다음과 같이 조회하시오.

SELECT
 	m.m_name AS '구매자'
 	,GROUP_CONCAT(DISTINCT g.g_name ORDER BY g.g_name) AS '상품명'
FROM
	tb_member AS m
	INNER JOIN
	tb_order AS o
	ON
	m.m_id = o.o_id
	INNER JOIN
	tb_goods AS g
	ON
	o.o_g_code = g.g_code
WHERE
	o.o_amount >= 20
GROUP BY m.m_name;