SQL Select 활용(서브쿼리, group by, join)

*데이터 자료는 한빛미디어 사이트의 혼공 자료실에 있는 자료

서브쿼리

서브쿼리는 쿼리 안에 () 안에 쿼리가 또 있는 것이다.

SELECT * FROM member WHERE height > (SELECT height FROM member WHERE mem_name LIKE '에이핑크');

(limit은 mysql 전용)

SELECT * FROM member WHERE height > (SELECT height FROM member WHERE mem_name LIKE '에이핑크')
	ORDER BY height DESC LIMIT 3;

 

group by

group by는 그룹으로 묶어주는 역할을 하며, 동일한 값을 갖는 행들을 하나의 그룹으로 묶을 수 있다.
group by는 주로 집계함수와 사용된다.

sum(): 합계
avg(): 평균
min(): 최솟값
max(): 최댓값
count(): 행의 개수
count(distinct): 중복되지 않은 행의 개수

SELECT mem_id, SUM(amount) AS '합계'  FROM buy GROUP BY mem_id ORDER BY mem_id;

grouping과 관련된 조건식은 where이 아닌 having을 사용한다.

SELECT mem_id, SUM(amount) AS '합계'  FROM buy GROUP BY mem_id HAVING SUM(amount) > 5 ORDER BY mem_id;

 

join (=inner join)

2개 이상의 테이블을 결합하는 데 사용된다.
만약 Error Code: 1052. Column 'student_number' in field list is ambiguous 가 뜬다면,  value 이름이 겹쳐서 어느 테이블의 value를 가져오는 건지 확실하게 표시해 주면 된다.

SELECT buy.mem_id, SUM(price*amount) AS total
	FROM buy
		INNER JOIN member
		ON buy.mem_id = member.mem_id
	GROUP BY mem_id;

 

예시 문제

Q1. 소녀시대랑 같은 지역에 사는 연예인 아이디와 이름, 인원을 출력하라. 
       (단, 데뷔날짜가 오래된 순으로 정렬하라.)

	-- 서브쿼리를 사용한 select
		-- Q1. 소녀시대랑 같은 지역에 사는 연예인 아이디와 이름, 인원을 출력하라. 
        -- 단, 데뷔날짜가 오래된 순으로 정렬하라
        
        select mem_id, mem_name, mem_number
			from member
            where addr = (select addr from member where mem_name='소녀시대')
            order by debut_date;

 

Q2. 총 구매내역이 제일 높은 5명의 아이디와 구매내역을 출력하라.
        (단, 구매내역은 100 이상이어야 한다.)

	-- grouping을 사용한 select
		-- Q2. 총 구매내역이 제일 높은 5명의 아이디와 구매내역을 출력하라.
        -- 단, 구매내역은 100 이상이어야 한다.
        select mem_id, sum( price * amount ) as total
			from buy
            group by mem_id
            having total > 100
            order by total desc
            limit 5;

 

Q3. 이름에 '이'가 포함된 그룹의 이름과 아이디, 데뷔일자를 키가 큰 순으로 출력하라.

	-- like를 사용한 조건문
		-- Q3. 이름에 '이'가 포함된 그룹의 이름과 아이디, 데뷔일자를 키가 큰 순으로 출력하라.
        select mem_name, mem_id, debut_date
			from member
            where mem_name
				like '%이%'
			order by height;

 

Q4. member 테이블에 아이브에 대한 정보를 추가하라.

-- insert문
	-- Q. member 테이블에 아이브에 대한 정보를 추가하라.
	insert into member values ('IVE', '아이브', 5, '서울', '02', '12345678', 169, '2021.12.01');
	-- date는 2021.12.01 형태보다 2021-12-01을 표준으로 잡고 있어서 warning 표시가 뜬다.
	select * from member;

 

Q5. 아이브에 대한 정보가 잘못되었다. 멤버 수를 5명에서 6명으로 수정하라.

-- update문
	-- Q. 아이브에 대한 정보가 잘못되었다. 멤버 수를 5명에서 6명으로 수정하라.
    update member set mem_number = '6'
		where mem_id = 'IVE';
	select * from member;

 

Q6. 여자친구는 해체하였다. (ㅜㅜ) member 테이블에서 여자친구를 삭제하라.

-- delete문
	-- Q. 여자친구는 해체하였다. (ㅜㅜ) member 테이블에서 여자친구를 삭제하라.
    delete from buy 
		where mem_id ='WMN';
			-- PK를 삭제할때는 묶여있는 FK를 먼저 삭제해야한다.
	delete from member
		where mem_id = 'WMN';
	select * from member;

 

Q7. 혼공 SQL을 구매한 연예인의 이름과 인원을 출력하라.

-- join을 사용한 select문
	-- Q1. 혼공 SQL을 구매한 연예인의 이름과 인원을 출력하라.
    select member.mem_name, member.mem_number
    from member
    join buy
		on buy.mem_id = member.mem_id
	where prod_name = '혼공SQL';

 

Q8. 총 구매내역이 300 이상인 연예인의 이름, 데뷔일자, 총 구매내역을 출력하라.
    (단, 3명이 넘으면 3명만 출력하고 구매내역이 높은 순으로 출력하라.)

    -- Q2. 총 구매내역이 300 이상인 연예인의 이름, 데뷔일자, 총 구매내역을 출력하라.
    -- 단, 3명이 넘으면 3명만 출력하고 구매내역이 높은 순으로 출력하라.
    SELECT buy.mem_id, mem_name, debut_date, SUM(amount * price) AS total
	FROM buy
	JOIN member
		ON member.mem_id = buy.mem_id
	GROUP BY mem_id
	HAVING total > 300
	ORDER BY total DESC
	LIMIT 3;

 

Q9. 1인당 평균 구매량이 제일 높은 3명의 이름과 아이디, 멤버 수, 1인 평균 구매량을 출력하라.

    -- Q3. 1인당 평균 구매량이 제일 높은 3명의 이름과 아이디, 멤버 수, 1인 평균 구매량을 출력하라.
    select member.mem_name, member.mem_id, member.mem_number, sum(price * amount)/member.mem_number as '1인 평균 구매량'
    from member
    join buy
		on buy.mem_id = member.mem_id
	group by mem_id
    order by '1인 평균 구매량' desc
    limit 3;