sql문(GROUP BY,집계함수, HAVING, 서브쿼리 와 조인)

강사님이 준 sql파일 순서대로 시행하기.

sql 순서!

from -> where -> select 순으로 시행된다.

---

쿼리문!!!!

제조업체(vend_id)가 "DLL01"이거나 "BRS01"인 제풍의 이름(prod_name)과 가격(prod_price)을 가져오시오. 단 제품이름은 오름차순으로 정렬.

• SELECT prod_name, prod_price FROM products WHERE vend_id = 'DLL01' OR vend_id = 'BRS01' ORDER BY prod_name ASC;
• SELECT prod_name, prod_price FROM products WHERE vend_id IN('DLL01', 'BRS01') ORDER BY prod_name;

가격(prod_price)이 5~10인 제품의 가격과 이름을 가져오시오.

• SELECT prod_name, prod_price FROM products WHERE prod_price BETWEEN 5 AND 10;
• SELECT prod_name, prod_price FROM products WHERE prod_price >=5 AND prod_price <=10;

가격(prod_price)이 입력되지 않은 제품의 이름을 가져오시오.
SELECT prod_name FROM products WHERE prod_price IS NULL;

제조 업체(vend_id)가 'DLL01'이 아닌 제품의 이름을 가져오시오

• SELECT prod_name FROM products WHERE vend_id <> 'DLL01' ORDER BY prod_name;
• SELECT prod_name FROM products WHERE NOT vend_id='DLL01' ORDER BY prod_name;

제품의 이름(prod_name)이 'Fish'로 시작하는 모든 제품의 아이디(prod_id)와 이름(prod_name)을 검색하시오.

• SELECT prod_id, prod_name FROM products WHERE prod_name LIKE 'Fish%';

제품의 이름(prod_name)이 F로 시작하고 y로 끝나는 제품의 이름(prod_name)을 검색하시오.
SELECT prod_name FROM products WHERE prod_name LIKE 'F%y';

별칭을 주는 방법 : 한칸 띄우고 별칭을 적어주기. or AS(지원이 되는 것도 있고 안되는 것도 있고!) 적기

as

레코드를 하나의 줄씩 연산한다. 레코드별로 연산이 된다! 자기것만 한다. 자가레코드만 연산한다.

• SELECT prod_id, quantity, quantity*item_price expanded_price FROM orderItems WHERE order_num = 20008
• SELECT prod_id, quantity, quantity*item_price AS expanded_price FROM orderItems WHERE order_num = 20008

GROUP BY

group by

제품별 판매총액을 구하라.

• SELECT * FROM orderItems ORDER BY prod_id;
  : 이게 제품별이다. 7가지 종류가 있다. 7가지 종류의 그룹의 총판매액을 구하는 것이다. 이렇게 어떻게 나누는냐? 이걸 할 수 있는 키워드가 GROUP BY이다.
• SELECT * FROM orderItems GROUP BY prod_id;
  : 이걸 하면 전체 레코드 수와 관련있는게 아니라 그룹수와 관련이 있다. 이것까지 그룹이 된 거.

집계함수

• SUM, AVG, COUNT(줄 수가 몇개냐), MAX, MIN : 여러개의 레코드를 가지고 값을 한개로 만드는 놈이다 = 집계함수.

• GROUP BY 사용했을 때 셀렉트절에 사용하는 게 가능하다.

• 집계함수는 WHERE절에 쓸 수 없다. 웨얼절은 하나의 레코드 가지고 연산하는 것이기 때문에.

• 그룹으로 나눠서 탈락되는 그룹이 있을 수 있다. 조건절이 없을 때는 그룹수가 레코드로 나온다.

• 요약하자면, 그룹바이는 그룹수에 의해서 결과가 결정난다. 일치하지 않는 값은 사용할 수 없다. 여기서는 id밖에 못쓴다. 그러나 합은 사용할 수 있다.

• sum은 세로로 더하는 것이다. -> 더하면 1개가 나온다. 다 더하면 결과는 1개 나오는데 그걸 스칼라값(집계함수)이라고 한다.

• SELECT prod_id, SUM(quantity*item_price) FROM orderitems GROUP BY prod_id;
  prod_id만 살아남고 num,item같은 것을 버려지게 된다. 다 다른 값이기 때문에.
  우연찮게 다 같은 값이 있으면 같이 써도 되는데, 그건 우연일 뿐.

HAVING

• HAVING절에는 집계함수를 조건을 걸 수 있다.
  count()는 null은 카운팅이 되지 않는다. 그래서 '프라이머리키'넣던지, 아니면 '*하기'.
• 해빙이 없으면 그룹수랑 레코드수랑 일치한다.

주문을 2개 이상 받은 제품의 총 판매수량

• SELECT prod_id, SUM(quantityitem_price) FROM orderitems GROUP BY prod_id HAVING COUNT() >=3 ;

• having은 레코드별이 아니라, 그룹별로 건다. 이 그룹이 이게 됩니까? 이런 느낌. where은 레코드 하나씩 검사하는 거.
  그래서 price가 4가 넘는지는 having에서 알 수 없음

가격(prod_price)이 4이상인 제품을 두 개 이상 가진 공급업체(vend_id)와 제품수량을 구하라

• SELECT vend_id, COUNT() FROM products WHERE prod_price >= 4 GROUP BY vend_id HAVING COUNT() >= 2;

• 팁
  SELECT vend_id, COUNT(*) FROM products를 하면 vend_id가 하나만 나오는데, COUNT때문에 제일 처음인 id가 나오는 것이다.

예시
SELECT FROM products WHERE prod_price >= 4 GROUP BY vend_id HAVING COUNT() >= 2;

제조사(vend_id)별 제품의 판매가격(pod_price) 총 합계(total_price)를 구하라.
SELECT vend_id, SUM(prod_price) total_price FROM products GROUP BY vend_id;

제품을 3종류 이상 주문한 주문번호와 / 주문가격의 합을 구하시오.
SELECT oder_num, SUM(item_price quantity) FROM orderItems GROUP BY order_num HAVING COUNT() >=3;

서브쿼리 와 조인

'King doll'을 생산한 제조사의 이름과 주소를 구하라. (King doll 얘는 product_name이다.)
: 사용방법 - 서브쿼리 or 조인 이다. : 연산 방식이 다르다.

• 서브쿼리는 우리가 생각하는 방식과 비슷하다.
  테이블을 하나씩 처리한다.
  그렇지만 쿼리가 길어진다는 단점이 있다.
• 조인은 우리가 사용하는 방식은 아니지만, 길이가 상대적으로 짧다. 결합되는 조건에 맞는 열들을 논리적으로 붙여서 하나의 테이블로 본다.
• 두가지 방식으로 다 풀어보는 걸 권장.

서브쿼리

쿼리문 안에 쿼리가 있는거
테이블을 하나씩 하나씩 처리한다.
-- '=' 라고 사용하면, prod_name은 주키가 아니라서 이게 한개이상 있을 수 있으니까 이름이 여러개 잇을 수 있으니까 오류가 날 수도 있다.
-- whrer 다음에 in으로 바꾼 것은 =라고 하면 1:1의 개념인데, in은 여러개중에 하나와 같은 뜻을 포함하고 잇다. 그래서 =을 사용하면 오류가 난다. 그래서 안전하게 하기 위해서 in을 사용한다. 일반적으로 연산의 속도는 조인이 더 낫다.

서브쿼리방식

SELECT vend_id FROM products WHERE prod_name = 'King doll'; -- 이거는 FNG01이 나온다.

SELECT vend_name, vend_address FROM vendors WHERE vend_id = (SELECT vend_id FROM products WHERE prod_name = 'King doll');
(X, 틀렸다 밑에 처럼 쓰기)

SELECT vend_name, vend_address FROM vendors WHERE vend_id IN (SELECT vend_id FROM products WHERE prod_name = 'King doll');
IN을 넣어라.

조인

join : 두개 이상의 테이블을 합치는거. 종류가 많다.
1.크로스조인: 레코들들끼리 조합할 수 있는 모든 경우의 수를 다 합치는거. a레코드 5개, b레코드 5개면, 25개나오는거.

2.이너조인(보통말하는 조인) : 결합조건이 일치하는 열끼리만 결합시키는 것이다.
SELECT * FROM vendors v, products p WHERE v.vend_id = p.vend_id; -- 이너조인 예제 : 논리적으로 결합시켜서, 한 테이블처럼 보이게 한다.

3.아우터조인 : 결합조건맞지않는 애들도 결합시킨다. a와 b의 교집합이 이너조인이면 그 외의 것은 아우터조인 인다.
a만 포함. b만 포함. 차집합만 포함. 교집합도 포함을 하는데, 아우터조인을 할 때는 교집합은 잘 사용하지 않는다.
그렇지만 결국 결합조건이 맞지 않는 부분만 보통 사용한다.
left outerJoin, right outerJoin, full outerJoin..

옛날방식('King doll'을 생산한 제조사의 이름과 주소를 구하라.)
SELECT vend_name, vend_address FROM vendors v, products p WHERE v.vend_id = p.vend_id AND prod_name = 'King doll';

최근 방식1. INNER JOIN
주키와 외래키로만 묶어야 한다. 그래야지 참조무결성이 지켜진다.
SELECT vend_name, vend_address FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id WHERE proc_name = 'King doll';

최근 방식2. NATURAL JOIN
이너조인의 중 하나인데, 주키와 외래키의 열이름이 같을 때 사용한다.
SELECT vend_name, vend_address FROM vendors NATURAL JOIN products WHERE prod_name = 'King doll';

예제
'RGAN01'(prod_id)물품을 주문한 모든 고객의 정보를 구하라(cust_name, cust_contact)

서브쿼리(변화과정 맨 밑에꺼가 정답)

• SELECT order_num FROM orderItems WHERE prod_id = 'RGAN01';
• SELECT cust_id FROM orders WHERE order_num IN(SELECT order_num FROM orderItems WHERE prod_id = 'RGAN01');
• SELECT cust_name, cust_contact FROM customers WHERE cust_id IN (SELECT cust_id FROM orders WHERE order_num IN(SELECT order_num FROM orderItems WHERE prod_id = 'RGAN01'));

조인 옛날방식
SELECT cust_name, cust_contact FROM customers, orders, orderItems WHERE orders.cust_id = customers.cust_id AND orders.order_num = orderItems.order_num AND prod_id = 'RGAN01';

조인 최근방식 INNERJOIN
SELECT cust_name, cust_contact
FROM customers INNER JOIN orders INNER JOIN orderItems
ON orders.cust_id = customers.cust_id AND orders.order_num = orderItems.order_num WHERE prod_id = 'RGAN01';

NATURAL JOIN
SELECT cust_name, cust_contact
FROM customers NATURAL JOIN orders NATURAL JOIN orderItems
WHERE prod_id = 'RGAN01';

DISTINCT : 중복을 없애준다

100개 이상 판매한 제품을 제조한 제조사이름(vend_name)을 구하라

• 서브쿼리
  SELECT vend_name FROM vendors WHERE vend_id IN (SELECT DISTINCT vend_id FROM products WHERE prod_id IN(SELECT prod_id FROM orderItems WHERE quantity>=100));

• INNERJOIN 조인
  SELECT DISTINCT vend_name
  FROM vendors INNER JOIN products INNER JOIN orderItems
  ON vendors.vend_id= products.vend_id AND products.prod_id = orderItems.prod_id WHERE quantity >= 100;

• NATURAL 조인
  SELECT DISTINCT vend_name FROM vendors NATURAL JOIN products NATURAL JOIN orderItems WHERE quantity >= 100;

outer join 예시

left outer

한번도 주문하지 않은 고객의 이름을 구하라.
--고객은 있는데, orders에 없는 애를 찾으면 된다.이럴 때 아우터조인이 필요하다. 내가 꺼내오려는 정보가 왼쪽에 있는거라서 LEFT OUTER를 쓴 것이다.
-- 아우터조인은 둘 사이에 겹쳐지지 않는 것도 포함해서 order_num을 사용할 수 있다.
SELECT FROM customers LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id WHERE orders.cust_id IS NULL;
SELECT FROM customers LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id WHERE order_num IS NULL;
-- FROM orders RIGHT OUTER JOIN customers

제품을 하나도 생산하지 않는 제조사이름(vend_name)을 구하라.
SELECT vend_name FROM vendors LEFT OUTER JOIN products ON vendors.vend_id = products.vend_id WHERE products.prod_id IS NULL;

제조사 이름이 'Bears R Us'인 제조사의 제품을 구매한 모든 고객의 이름을 구하시오. 단 중복은 제거한다.
SELECT DISTINCT cust_name FROM customers NATURAL JOIN orders NATURAL JOIN orderItems NATURAL JOIN products NATURAL JOIN vendors WHERE vend_name = 'Bears R Us';
SELECT DISTINCT cust_name FROM vendors NATURAL JOIN products NATURAL JOIN orderItems NATURAL JOIN orders NATURAL JOIN customers WHERE vend_name = 'Bears R Us';

가장 비싼(item_price) 제품을 구매한 구매자의 이름(cust_name)과 판매가격(item_price), 구매수량(quantity)을 구하라.
SELECT cust_name, item_price, quantity FROM customers NATURAL JOIN orders NATURAL JOIN orderItems WHERE orderItems.item_price = (SELECT MAX(item_price) FROM orderItems); --값을 하나만 들고오는거 : 스칼라