JOIN 실습

  데이터 조작 언어 DML

1. 회원의 레벨의 이름이 관리자인 회원의 정보(이름, 레벨이름,이메일)를 조회하시오.

SELECT
	m.m_name AS '이름',
    	l.level_name AS '등급',
    	m.m_email AS '이메일'
FROM
	tb_member_level AS l
    INNER JOIN
    tb_member AS m
    on
    l.level_num	 = m.m_level
WHERE
	l.level_name LIKE '%관리자%';

 

 

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

SELECT
	m.m_id AS 아이디,
   	m.m_email AS 이메일
FROM
	tb_member AS m
INNER JOIN
	tb_login AS l
ON
	m.m_id = l.login_id
WHERE
	MONTH(l.login_date) = 2;
--	YEAR(l.login_date) = 2020;
--	l.login_date BETWEEN '2020-02-01' AND LAST_DAY('2020-02-01');

 

 

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

SELECT
	m.m_id AS '아이디',
    	m.m_email AS '이메일'
FROM
	tb_member AS m
LEFT JOIN
	tb_login AS l
ON
	m.m_id = l.login_id
WHERE
	l.login_date IS NULL

 

 

4. 판매자별상품명 목록을 조회하시오.

SELECT
	g.g_seller_id AS '아이디',
    GROUP_CONCAT(DISTINCT g.g_name) AS '상품명 목록'
FROM
	tb_goods AS g
GROUP BY g.g_seller_id;

 

 

5.회원들 중에 로그인하지 않은 회원의 정보(아이디와 이메일)를 다음과 같이 조회 하시오.

SELECT
	GROUP_CONCAT(if(m.m_id='id010', m.m_email, null) AS 'id010'),
    GROUP_CONCAT(if(m.m_id='id011', m.m_email, null) AS 'id011'),
    GROUP_CONCAT(if(m.m_id='id012', m.m_email, null) AS 'id012'),
FROM
	tb_member AS m
LEFT JOIN
	tb_login AS l
ON
	m.m_id = l.login_id
WHERE
	l.login_date IS NULL;

 

 

6. 회원들 중에 2020-03-01 이후부터 로그인하지 않은 회원의 정보(아이디와 이메일)를 다음과 같이 조회 하시오.

SELECT
	m.m_id AS '아이디',
    m.m_email AS '이메일
FROM
	tb_member AS m
LEFT JOIN
	tb_login AS l
ON
	m.m_id = l.login_id
AND
	l.login_date>'2020-03-01'
WHERE
	l.login_id IS NULL;

 

7. 회원들의 이메일를활용하여 회원 이름을 추출하고 회원아이디와 회원이름을 조회하시오.

SELECT
	m.m_id AS '아이디'
    substring(m.m_email,1,3) AS '회원이름'
--	SUBSTRING_INDEX(m.m_email,'@',1) AS '회원이름'
FROM
	tb_member AS m;

 

 

8. 판매자 별 총 판매액을 조회하시오.

SELECT
	g.g_seller_id AS '아이디',
    sum(g.g_price*o.o_amount) AS '총판매액'
FROM
	tb_goods AS g
INNER JOIN
	tb_order AS o
ON
	g.g_code = o.o_g_code
GROUP BY g.g_seller_id;

 

 

9. 회원의 레벨 별 인원을 조회하시오.

SELECT
	l.level_name AS '회원레벨',
    count(m.m_id) AS '인원수'
FROM
	tb_member_level AS l
INNER JOIN
	tb_member AS m
ON
	l.level_num = m.m_level
GROUP BY l.level_name;

 

 

10. 회원 별 구매이력 중 구매금액이 가장 높은 금액의 상품명을 추출하여 회원아이디와이메일과 함께 조회 하시오.

SELECT
	m.m_id AS '회원아이디',
	m.m_email AS '이메일',
	g.g_name AS '상품명',
	max(g.g_price * o.o_amount) 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
GROUP BY m.m_id;

 

 

 

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

SELECT
m.m_name AS '구매자',
GROUP_CONCAT(distinct g.g_name) AS '상품목록'
FROM
tb_member AS m
INNER join
tb_order AS o
on
m.m_id = o.o_id
AND
o.o_amount >=20
INNER JOIN
tb_goods AS g
on
o.o_g_code = g.g_code
GROUP BY o.o_id;