SQL 실습문제(join)

1. 회원등급이 관리자인 회원의 정보(회원아이디, 이름, 회원등급, 이메일) 조회

SELECT
	cc.comm_nm AS '회원등급',
	m.mbr_id AS '회원아이디',
	m.mbr_name AS '회원이름',
	m.mbr_grd AS '회원등급',
	m.mbr_email AS '회원이메일'
FROM
	members m
	INNER join
	comm_code cc
	ON m.mbr_grd = cc.comm_cd
WHERE
	cc.comm_nm = '관리자';

 

 

2. 회원들 중에 1월14일 이후에 로그인한 회원의 정보(아이디와 이메일)를 조회하시오

SELECT
	DISTINCT
	m.mbr_id,
	m.mbr_email
FROM
	members m
	INNER JOIN
	members_login_log mll
	ON m.mbr_id = mll.login_id
WHERE
	mll.login_date BETWEEN CAST('2024-01-14' AS DATE) AND CAST('2024-01-15' AS DATE);
	-- mll.login_date >= CAST('2024-01-14' AS DATE);

 

 

3. 회원들 중에 한번도 로그인하지 않은 회원의 정보(회원아이디, 이름, 이메일)를 조회하시오.

SELECT
	m.mbr_id,
	m.mbr_name,
	m.mbr_email
FROM
	members m
	LEFT join
	members_login_log mll
	ON m.mbr_id = mll.login_id
WHERE
	mll.login_no IS NULL;

 

 

4. 회원별 최종 로그인 날짜를 조회(회원아이디, 회원이메일, ip, 최종로그인일시)

SELECT
	m.mbr_id AS '회원아이디',
	m.mbr_email AS '회원이메일',
	IFNULL(mll.login_ip, '로그인하지않음') AS '로그인ip',
	IFNULL(max(mll.login_date), '로그인하지않음') AS '최종로그인일시'
FROM
	members m
	LEFT join
	members_login_log mll
	ON m.mbr_id = mll.login_id
GROUP BY m.mbr_id
ORDER BY cast(substring_index(m.mbr_id, 'ksmartid', -1) AS UNSIGNED);

 

5. 판매자별 등록한 상품의 목록을 다음과 같이 조회하시오

SELECT
	m.mbr_id AS sellerId,
	c.comm_nm AS gradeName,
	v.vend_name AS vendorName,
	ifnull(
				GROUP_CONCAT(
								DISTINCT p.prod_name
								ORDER BY p.prod_name
								SEPARATOR ', '
								),
							'등록한 상품이 없습니다.'
	)AS productList
FROM
	members m
	INNER join
	vendors v
	ON m.mbr_id = v.vend_mbr_id
	LEFT JOIN products p
	ON v.vend_cd = p.vend_cd
	INNER JOIN comm_code c
	ON m.mbr_grd = c.comm_cd
GROUP BY m.mbr_id;

 

 

6. 판매자별 등록한 상품의 총매출액을 다음과 같이 조회하시오

SELECT
	m.mbr_id AS sellerId,
	c.comm_nm AS memberGrade,
	v.vend_name AS vendorName,
	ifnull(SUM(p.prod_untprc * o.order_cnt),0) AS totalSales
FROM
	vendors v
	INNER JOIN members m
	ON v.vend_mbr_id = m.mbr_id
	INNER JOIN comm_code c
	ON m.mbr_grd = c.comm_cd
	LEFT JOIN products p
	ON v.vend_cd = p.vend_cd
	LEFT JOIN orderitems o
	ON p.prod_cd = o.order_prod_cd
GROUP BY m.mbr_id;

 

 

7. 회원등급별 회원 인원수를 조회하시오

SELECT
	c.comm_nm AS '회원등급',
	COUNT(m.mbr_grd) as '인원수'
FROM
	members m
	INNER JOIN comm_code c
	on m.mbr_grd = c.comm_cd
GROUP BY m.mbr_grd;

 

 

8. 회원 주소의 도로명별 구매 총액을 조회하시오

SELECT
	SUBSTRING_INDEX(m.mbr_addr, ' ', 4) AS roadAddress,
	COUNT(m.mbr_id) AS peopleCnt,
	ifnull(SUM(oi.order_cnt * p.prod_untprc),0) AS purchaseAmount
FROM
	members m
	left JOIN orders o
	ON m.mbr_id = o.cust_id
	LEFT JOIN orderitems oi
	ON o.order_no = oi.order_no
	LEFT JOIN products p
	ON oi.order_prod_cd = p.prod_cd
GROUP BY SUBSTRING_INDEX(m.mbr_addr, ' ', 4);

 

 

9. 회원 지역구 별 회원의 분포수를 조회하시오

SELECT
	substring_index(m.mbr_addr, ' ', 3) AS '지역구',
	COUNT(m.mbr_id) AS '회원 분포수'
FROM
	members m
GROUP BY SUBSTRING_INDEX(m.mbr_addr, ' ', 3);

 

 

10. 기간별 매출 금액을 조회하시오

SELECT
	DISTINCT
	DATE_FORMAT(o.order_date, '%Y-%m-%d') AS '구매일자',
	SUM(oi.order_cnt * p.prod_untprc) AS '총판매액',
	SUM(SUM(oi.order_cnt * p.prod_untprc)) OVER (ORDER BY date(o.order_date)) AS '누적금액'
FROM
	orders o INNER JOIN orderitems oi
	USING(order_no)
	INNER JOIN products p
	ON oi.order_prod_cd = p.prod_cd
GROUP BY DATE_FORMAT(o.order_date, '%Y-%m-%d');

-- USING : 컬럼명이 같을 때

 

 

11. 구매일자별 매출과 구매일자별 전일대비 증감을 조회하시오

SELECT
	DISTINCT
	DATE_FORMAT(o.order_date, '%Y-%m-%d') AS '구매일자',
	SUM(oi.order_cnt * p.prod_untprc) AS '총판매액',
	LAG(SUM(oi.order_cnt * p.prod_untprc), 1, 0) OVER (ORDER BY date(o.order_date)) AS '이전판매액',
	case
	WHEN SUM(oi.order_cnt * p.prod_untprc) - LAG(SUM(oi.order_cnt * p.prod_untprc), 1, 0) OVER (ORDER BY date(o.order_date)) > 0
	then CONCAT('▲', ABS(
								SUM(oi.order_cnt * p.prod_untprc) - LAG(SUM(oi.order_cnt * p.prod_untprc), 1, 0) OVER (ORDER BY date(o.order_date))
								)
					)
	WHEN SUM(oi.order_cnt * p.prod_untprc) - LAG(SUM(oi.order_cnt * p.prod_untprc), 1, 0) OVER (ORDER BY date(o.order_date)) < 0
	then CONCAT('▼', ABS(
								SUM(oi.order_cnt * p.prod_untprc) - LAG(SUM(oi.order_cnt * p.prod_untprc),1,0) OVER (ORDER BY date(o.order_date))
								)
					)
	ELSE
		'-'
	END AS '전일대비증감'
FROM
	orders o INNER JOIN orderitems oi
	USING(order_no)
	INNER JOIN products p
	ON oi.order_prod_cd = p.prod_cd
GROUP BY DATE_FORMAT(o.order_date, '%Y-%m-%d');