[DB] 쿼리문 연습하기 (LEFT JOIN, INNER JOIN, GROUP BY)

 

SQL Test

 

연습은 위 사이트에서

 

 

3, 4, 5번 문제만 올바르게 구할 수 있는 테이블 작성

CREATE TABLE Product ( 
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
maker VARCHAR(30), 
model VARCHAR(30)
); 

CREATE TABLE PC ( 
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
model VARCHAR(30), 
speed INT(6),
price FLOAT(6)
); 

CREATE TABLE Labtop ( 
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
model VARCHAR(30), 
price FLOAT(6)
); 

CREATE TABLE Printer ( 
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
model VARCHAR(30), 
price FLOAT(6)
); 



INSERT INTO `Product` (`maker`, `model`) VALUES ('A', 'aaa'); 
INSERT INTO `Product` (`maker`, `model`) VALUES ('A', 'bbb');
INSERT INTO `Product` (`maker`, `model`) VALUES ('A', 'ccc');
INSERT INTO `Product` (`maker`, `model`) VALUES ('B', 'ddd');
INSERT INTO `Product` (`maker`, `model`) VALUES ('B', 'eee');
INSERT INTO `Product` (`maker`, `model`) VALUES ('A', 'aa'); 
INSERT INTO `Product` (`maker`, `model`) VALUES ('A', 'bb');
INSERT INTO `Product` (`maker`, `model`) VALUES ('B', 'cc');
INSERT INTO `Product` (`maker`, `model`) VALUES ('B', 'dd');


INSERT INTO `PC` (`model`, `speed`, `price`) VALUES ('ddd', 2, 10); 
INSERT INTO `PC` (`model`, `speed`, `price`) VALUES ('aaa', 4, 4); 
INSERT INTO `PC` (`model`, `speed`, `price`) VALUES ('bbb', 6, 5); 
INSERT INTO `PC` (`model`, `speed`, `price`) VALUES ('ccc', 8, 6); 
INSERT INTO `PC` (`model`, `speed`, `price`) VALUES ('eee', 10, 6); 

INSERT INTO `Labtop` (`model`,`price`) VALUES ('dd',1); 
INSERT INTO `Labtop` (`model`,`price`) VALUES ('aa',4); 
INSERT INTO `Labtop` (`model`,`price`) VALUES ('bb',6); 
INSERT INTO `Labtop` (`model`,`price`) VALUES ('cc',10);

임의 Data 만드는 SQL스크립트

 

---

Find the average price of PC’s made by manufacturer “A”

select AVG(PC.price)
from PC 
inner join Product 
On PC.model = Product.model
where Product.maker = "A";

# 같은 결과
select AVG(PC.price)
from PC 
inner join Product 
On PC.model = Product.model and Product.maker = "A";

1. PC 테이블과 Product 테이블을 model명이 같은 것들 끼리 null값이 없도록 INNER JOIN을 수행한다.
2. INNER JOIN을 수행한 테이블에서 Product의 maker가 "A"인 경우만 골라서 PC의 AVG(price)를 구한다.

 

Product의 maker가 "A"인 행만 고를 때

ON을 사용하면 JOIN 과정에서 Product의 make = "A"이 행 만을 가진 테이블이 구성되고,

WHERE을 사용하면 최종 테이블 구성에서 maker = "A"인 행들만 남게 된다.

구성되는 중에 조건이 적용되냐, 구성이 다 끝나고 리메이크되냐의 차이라고 이해했다.

 

INNER JOIN에서는 큰 차이가 없는것 같지만 OUTER JOIN상황에서는 많은 차이가 있을 것 같다. 

그 차이는 바로 아래 쿼리문에서 알 수 있다.

 

---

Find the average price of PC’s and laptop made by manufacturer “A”

select AVG(PC.price), AVG(Labtop.price)
from Product
left join PC on Product.model = PC.model
left join Labtop on Product.model = Labtop.model and Product.maker="A";

 

문제를 해석해보면 PC의 평균 price를 구하고, Labtop의 maker가 "A"인 행들의 평균 price를 구해야한다.

따라서 WHERE maker = "A" 라는 쿼리를 작성해버리면 PC 테이블에서도 maker = "A"인 행들만 추려지기 때문에 주의해야한다.

 

 

위 쿼리문을 통해 구성되는 테이블 전체를 보면 위와 같다.

 

LEFT JOIN을 통해서 Product의 값들을 모두 살리게 되었다.

1. Product의 model명과 PC의 model명이 같은 행들만 추려서 LEFT JOIN한다. 
2. Product의 model명과 Labtop의 models 명이 같은 행들만 추려서 LEFT JOIN한다.
3. 2번 과정에서 Product의 maker가 "A"인 경우만 추려낸다.

 

LEFT JOIN을 통해서 필요없는 column들은 null로 처리되었기 때문에 조건에 맞는 PC와 Labtop의 price 평균을 알맞게 구할 수 있다.

 

 

---

Find for each different price, the average speed of PC

select PC.price, AVG(speed)
from PC
group by price;

PC테이블에서 서로 다른 price에 대해서 각각의 평균 speed를 구하면 된다.

 

GROUP BY price를 통해서 price를 중복값이 없게 처리하고, 알맞은 speed의 AVG를 구할 수 있다.