[DB] sql 예제 - OUTER JOIN 외

DISTINCT가 필요한 순간 잘 캐치해서 써주기

	SELECT fid, COUNT(DISTINCT cname) FROM crops;

• DISTINCT의 의미 : 조인 과정에서 양측 집합의 건수를 줄여 조인의 부하를 줄일 수 있다.
  참고

CHAR_LENGTH() : 글자의 길이를 반환하는 함수

	SELECT cname, AVG(cprice) cprice_avg
    FROM crops
    WHERE CHAR_LENGTH(cname) > 4
    GROUP BY cname
    HAVING cprice_avg > 1000;

SELECT 결과를 다시 JOIN하는 것도 가능하다!

    SELECT fname, c.total FROM farmers, (
        SELECT fid, SUM(cprice) total
        FROM crops
        GROUP BY fid
    ) c
    WHERE farmers.fid = c.fid;

---

-- 모든작물의 이름을 구하라.(crops)
SELECT DISTINCT cname FROM crops;
SELECT cname FROM crops GROUP BY cname;

-- 관리하는 농작물의 총 종류수 구하기
SELECT fid, COUNT(cname) FROM crops;

-- g로 시작하는 농작물 이름 구하기
SELECT DISTINCT cname FROM crops WHERE cname LIKE 'g%';

-- 작물별 제공 농부수가 2명 이상인 작물을 구하라.  
SELECT cname, COUNT(fid) farmers_count FROM crops GROUP BY cname HAVING farmers_count >= 2;

-- 3000원 이상하는 작물의 수를 구하라.  
SELECT COUNT(*)
FROM crops
WHERE cprice >= 3000;

-- 작물별로 3000원 이상하는 작물의 수가 2개 이상인 작물을 구하라.
SELECT cname
FROM crops
WHERE cprice >= 3000
GROUP BY cname HAVING COUNT(*) >= 2;

-- 작물별 가장 저렴한 제품의 가격을 구하라.  
SELECT cname, MIN(cprice)
FROM crops
GROUP BY cname;

-- 작물별 평균가격을 구하라
SELECT cname, AVG(cprice)
FROM crops
GROUP BY cname;

-- 이름이 4글자가 넘는 작물별 평균가격을 구하라.
SELECT cname, AVG(cprice)
FROM crops
WHERE cname LIKE '____%'
GROUP BY cname;

SELECT cname, AVG(cprice) 
FROM crops
WHERE CHAR_LENGTH(cname) > 4
GROUP BY cname;

-- 이름이 4글자가 넘는 작물별 평균가격을 구하라. 단, 평균가격이 1000원 이상 ★★★
SELECT cname, AVG(cprice) cprice_avg
FROM crops
WHERE cname LIKE '____%'
GROUP BY cname HAVING AVG(cprice) >= 1000;

SELECT cname, AVG(cprice) cprice_avg
FROM crops
WHERE CHAR_LENGTH(cname) > 4
GROUP BY cname
HAVING cprice_avg > 1000;

-- 각 농부별 재배작물의 총 가격을 구하라. ★★★★★
-- fid별 재배작물 총 가격을 구한 다음 -> 결과(=테이블)을 farmers와 조인하여 fname을 적는다.
SELECT fname, c.total FROM farmers, (
	SELECT fid, SUM(cprice) total
	FROM crops
	GROUP BY fid
) c
WHERE farmers.fid = c.fid;

-- daniel이 재배하는 작물의 이름과 가격을 구하라.  
SELECT fname, cname, cprice
FROM crops NATURAL JOIN farmers
WHERE fname = 'daniel';

SELECT fname, cname, cprice FROM crops WHERE fid = (
	SELECT fid FROM farmers WHERE fname = 'daniel'
);

-- kiwi를 생산하는 농부의 이름과 전화번호를 구하라.	
SELECT fname, ftel
FROM crops NATURAL JOIN farmers
WHERE cname = 'kiwi';

-- 가장 값이 싼 수박을 납품하는 농부의 이름과 주소를 구하라. 
SELECT fname, faddr
FROM crops NATURAL JOIN farmers
WHERE cname = 'watermelon' AND cprice = (
	SELECT MIN(cprice) FROM crops WHERE cname = 'watermelon'
);

SELECT fname, faddr FROM farmers
WHERE fid = (
	SELECT fid FROM crops WHERE cname = 'watermelon' AND cprice = (
		SELECT MIN(cprice) FROM crops WHERE cname = 'watermelon'
	)
);

-- 부산에서 재배되는 작물의 이름, 가격, 농부이름 구하라.
SELECT cname, cprice, fname
FROM crops NATURAL JOIN farmers
WHERE faddr = 'busan';

-- 옥수수를 재배하지 않는 농부의 이름은? 	
SELECT DISTINCT fname
FROM farmers
WHERE NOT fname IN 
(SELECT fname FROM crops NATURAL JOIN farmers WHERE cname = 'corn'); 

SELECT DISTINCT fname 
FROM farmers NATURAL JOIN crops 
WHERE farmers.fid NOT IN 
(SELECT fid FROM crops WHERE cname = 'corn');

SELECT fname 
FROM farmers
WHERE fid IN (
	SELECT DISTINCT fid FROM crops WHERE fid NOT IN (
		SELECT fid FROM crops WHERE cname = 'corn'
	)
);

-- 납품을 하지않는 농부의 이름을 구하라	
SELECT fname
FROM farmers LEFT OUTER JOIN crops
ON farmers.fid = crops.fid
WHERE cid IS NULL;

SELECT fname FROM farmers WHERE fid NOT IN (
	SELECT DISTINCT fid FROM crops
);