-- Quiz7
-- 주문번호가 20008인 품목의 제품 아이디, 수량,
SELECT prod_id, quantity, quantity * item_price AS expanded_price FROM orderItems WHERE order_num = 20008
-- @@ AS - 값 옆에 '(공백) 별칭' 혹은 'AS 별칭'이라고 적으면 값의 이름을 별칭으로 지정해준다.
-- Quiz8
SELECT prod_id, SUM(quantity * item_price) FROM orderItems GROUP BY prod_id HAVING COUNT(*)
-- @@ GROUP BY - 기본적으로 그룹당 하나의 값을 반환하는데, 이때 통일되지 않은(통일될 수 없는) 값을 요구하면 의미없는 값이 나온다. (에러는x)
-- 단 계산하여 하나의 값을 얻을 수 있는 경우는 그 계산값을 반환하게 할 수 있다. (SUM, AVG, COUNT, MAX, MIN 등 집계함수)
-- 이런 값을 Scholar값이라 한다.
-- SUM : 전체 합 / AVG : 전체 평균 / MAX : 가장 큰 값 / MIN : 가장 작은 값
-- COUNT : 그룹내 NULL이 아닌 모든 값의 개수 세기 / COUNT(*) : 그룹내 모든 행의 개수
-- @@ 집계함수는 그룹핑 된 경우에만 사용할 수 있다. WHERE절에서 사용 불가
-- (대상이 다르기 때문. WHERE절의 계산 범위는 테이블 전체. 레코드별 계산.)
-- @@ 그룹별 조건을 걸고 싶다면 HAVING절을 이용한다.
-- HAVING절의 계산 범위는 그룹별. 그룹별 계산.
-- Quiz9
-- 가격(prod_price)이 4 이상인 제품을/ 두 개 이상 가진 공급업체(vend_id)와 제품 수량을 구하라.
SELECT vend_id, COUNT(*) FROM products WHERE prod_price >= 4 GROUP BY vend_id HAVING COUNT(prod_id) >= 2;
SELECT vend_id, COUNT(*) FROM products GROUP BY vend_id HAVING COUNT(prod_id) >= 2 AND vend_id LIKE '%01%';
-- HAVING 절은 그룹별 조건 검사 -> 집계함수는 그룹 전체를 대상으로 계산한 결과가 하나로 나올 수 있는 계산이어야 함
-- Quiz10
-- 제조사(vend_id)별 제품의 판매가격(prod_price) 총 합계(total_price)를 구하라.
SELECT vend_id, SUM(prod_price) AS total_price FROM products GROUP BY vend_id;
-- Quiz11
SELECT order_num, SUM(quantity * item_price) FROM orderItems GROUP BY order_num HAVING COUNT(order_item) >= 3;
-- Quiz12
-- 서브쿼리 : 쿼리 안에 쿼리. 괄호로 감싸 사용하며 먼저 실행됨.
-- 1. 제조사 id 구하기 => FNG01
SELECT vend_id FROM products WHERE prod_name = 'King Doll';
-- 2. 제조사 id로 제조사 이름, 주소 구하기
SELECT vend_name, vend_address FROM vendors WHERE vend_id = 'FNG01';
-- 3. 합치기
SELECT vend_name, vend_address FROM vendors WHERE vend_id IN (SELECT vend_id FROM products WHERE prod_name = 'King Doll');
-- 현재 조건의 결과가 유일한 답을 보장하지 않기 때문에 (why? prod_name이 pk도 unique도 아님)
-- 조인 : 물리적으로 분리된 테이블을 논리적으로 합치는 것
-- 1. 크로스 조인 : 모든 경우의 수를 다 만들어 합치는 것 (일반적으로 사용x, 성능 테스트에서 사용)
SELECT vend_name, vend_address FROM vendors, products;
-- 2. 이너 조인 : 조건이 일치하는 열만 가져와 합치는 것 (일반적으로 말하는 조인)
-- <옛날 방식> : FROM A, B WHERE (결합조건)
SELECT vend_name, vend_address FROM vendors v, products p WHERE v.vend_id = p.vend_id AND prod_name = 'King Doll';
-- <요즘 방식 - ANSI 표준> : A INNER JOIN B ON (결합조건)
SELECT vend_name, vend_address FROM vendors v INNER JOIN products p ON v.vend_id = p.vend_id WHERE prod_name = 'King Doll';
-- <또 다른 방식> : A NATURAL JOIN B (결합조건은 열 이름이 같은 것)
SELECT vend_name, vend_address FROM vendors NATURAL JOIN products WHERE prod_name = 'King Doll';
-- 3. (Left/Right/Full) 아우터 조인 : 조건이 맞지 않는 열도 가져와 합치는 것 (보통 결합 조건에 맞지 않는 데이터를 사용하는 것이 목적)
-- MySQL은 Full Outer Join을 지원하지 않음
-- 서브쿼리 : (장) 우리가 생각하는 논리 흐름과 비슷 (단) 쿼리문 길이가 길어짐
-- 조인 : (장) 일반적으로 연산 속도가 빠름, 쿼리문이 짧음 (단) 우리가 생각하는 방식과 다름
-- 조인은 주키-외래키로 이어져야 함 => 참조무결성을 지키기 위해!!
-- Quiz13
-- 서브쿼리
-- SELECT order_num FROM orderItems WHERE prod_id = 'RGAN01';
-- SELECT cust_id FROM orders WHERE order_num IN ?;
-- SELECT cust_name, cust_contact FROM customers WHERE cust_id IN ?;
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 orderItems NATURAL JOIN orders NATURAL JOIN customers
WHERE prod_id = 'RGAN01';
SELECT cust_name, cust_contact
FROM orderItems ois INNER JOIN orders os INNER JOIN customers cs
ON ois.order_num = os.order_num AND os.cust_id = cs.cust_id
WHERE prod_id = 'RGAN01';
SELECT cust_name, cust_contact
FROM orderItems ois, orders os, customers cs
WHERE ois.order_num = os.order_num
AND
os.cust_id = cs.cust_id
AND
prod_id = 'RGAN01';
welcome