주요내용 : 집계함수, 데이터 그룹화, 하위 쿼리, 테이블 조인
/*0726과제*/
/*1. 상품주문에서 주문번호, 제품번호, 제품제조국가위치를 추출하시오
- 제품제조국가위치: 제품번호의 마지막 2번째 1자리의 값이 0이면 ‘국내’,0이아니면 ‘해외’
-총가격(항목수량*항목가격)이 500$이상인 주문*/
SELECT order_num, PROD_ID, DECODE(SUBSTR(prod_id, LENGTH(TRIM(prod_id))-1,1), '0', '국내', '해외')
FROM Orderitems
WHERE (quantity*item_price) >= 500;
/*2. 주문에서 주문번호와 주문가능날자(YYYY-MM-DD)를 추출하시오
- 주문가능날자: 일자의 10자리가 0이면 1일,1이면 10일, 20이면 20일,30이면 30일 */
SELECT order_num, DECODE(SUBSTR(TO_CHAR(order_date,'DD'),1,1), '0', '1일', '1', '10일', '2', '20일', '3', '30일')
FROM orders;
// 강사님 답
SELECT order_num,
TO_CHAR(
CASE WHEN SUBSTR(TO_CHAR(order_date,'DD'),1,1) = '0' THEN
TO_DATE(TO_CHAR(order_date,'YYYYMM')||'01')
WHEN SUBSTR(TO_CHAR(order_date,'DD'),1,1) = '1' THEN
TO_DATE(TO_CHAR(order_date,'YYYYMM')||'10')
WHEN SUBSTR(TO_CHAR(order_date,'DD'),1,1) = '2' THEN
TO_DATE(TO_CHAR(order_date,'YYYYMM')||'20')
WHEN SUBSTR(TO_CHAR(order_date,'DD'),1,1) = '3' THEN
TO_DATE(TO_CHAR(order_date,'YYYYMM')||'30')
END, 'YYYY-MM-DD') orderCanDt
FROM orders
;
//나의 답
SELECT order_num,
DECODE(SUBSTR(TO_CHAR(order_date,'DD'),1,1),
'0', TO_DATE(TO_CHAR(order_date,'YYYYMM')||'01'),
'1', TO_DATE(TO_CHAR(order_date,'YYYYMM')||'10'),
'2', TO_DATE(TO_CHAR(order_date,'YYYYMM')||'20'),
'3', TO_DATE(TO_CHAR(order_date,'YYYYMM')||'30')
) as Order_available
FROM orders;
/*3. 제품이름과 제품설명에 ‘king’이라는 단어가 들어가고 제품가격의 소수점 1자리가 9가 아닌 제품번호를 추출하시오*/
SELECT prod_id, prod_price
FROM Products
WHERE prod_desc LIKE '%king%'
AND SUBSTR(TRUNC(prod_price,1), LENGTH(TRUNC(prod_price,1)), 1) <> '9';
/*집계함수*/
-- AVG() : NULL값이 있는 경우 계산에서 제외된다.
-- Products 테이블에 있는 모든 제품의 가격평균
SELECT AVG(prod_price) AS avg_price
FROM Products;
-- DLL01의 공급업체에 대한 제품 평균
SELECT AVG(prod_price) AS avg_price
FROM Products
WHERE vend_id = 'DLL01';
-- COUNT()
-- COUNT(*) : 테이블의 모든 행의 갯수, NULL값 포함
-- COUNT(컬럼) : 컬럼의 NULL값을 제외한 행의 개수
-- Customers 테이블에 있는 모든 고객의 수
SELECT COUNT(*) as num_cust
FROM Customers;
--cust_email 열에 값이 있는 고객의 수만 계산
SELECT COUNT(cust_email) as num_cust
FROM Customers;
--MAX() 지정한 열에서 가장 큰값을 반환.
--TEXT도 사용가능, NULL값은 무시된다.
--Products 테이블에서 가격이 가장 비싼 제품의 가격
SELECT MAX(prod_price) AS max_price
FROM PRODUCTS;
-- MIN(): 지정한 열에서 가장 낮은 값을 반환
-- TEXT도 사용가능, NULL값은 무시된다
-- Products 테이블에서 가격이 가장 저렴한 제품의 가격
SELECT MIN(prod_price) AS min_price
FROM Products;
-- SUM(): 지정한 열에서 모든 값을 더한 합계
-- NULL값은 무시된다
-- 모든 주문한 물품의 수량의 합계
SELECT SUM(quantity) AS items_ordered
FROM Orderitems;
-- 각 주문에 대한 총 금액을 반환
-- 연산과정에 null값이 존재하면 결과값도 null값이 됨.(굉장히 많이 발생하는 오류임★★★★★★★)
SELECT SUM(item_price * quantity) AS total_price
FROM Orderitems
WHERE order_num = 20005;
-- 고유값의 집계: SUM, MAX, MIN에서 사용
-- 가격이 같은 물품이 있는 경우 한번만 계산에 포함
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM Products
WHERE vend_id = 'DLL01';
-- 집계함수 결합
SELECT COUNT(*) AS num_items,
MIN(prod_price) AS price_min,
MAX(prod_price) AS price_max,
AVG(prod_price) AS price_avg
FROM Products;
-- 1. 고객주문에서 주문날자가 가장 최근인 날자와 가장 최초인 날자를 추출하시오
SELECT TO_CHAR(MIN(order_date), 'YYYY-MM-DD'), TO_CHAR(MAX(order_date), 'YYYY-MM-DD')
FROM Orders;
-- 2. 주문상품에서 제품번호가 BN으로 시작하는 각 주문의 총금액(항목수량*항목가격)이 가장큰 금액을 추출하시오
SELECT MAX(quantity*item_price)
FROM Orderitems
WHERE prod_id LIKE 'BN%';
-- 3. 주문상품에서 제품번호가 BR01와 BR03인 제품의 주문된 항목수량의 평균을 추출하시오
SELECT ROUND(AVG(quantity), 2)
FROM Orderitems
WHERE prod_id IN ('BR01', 'BR03');
/*중요★★★데이터 그룹화★★★*/
-- 그룹만들기
-- 중첩된 그룹이 있을 경우 데이터는 마지막 지정된 그룹을 기준으로 요약된다
-- GROUP BY로 그룹화 되면 해당 컬럼은 SELECT절에 나타나야 한다
-- NULL값되 그룹으로 분류된다
-- 공급업체별 상품 수
SELECT vend_id, COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id;
-- 필터링 그룹: 그룹을 만든다음 그 그룹결과에서 그룹함수를 통해 필터링을 수행
-- WHERE: 그룹화 하기 전에 필터링 수행
-- HAVING: 그룹화 한 후에 필터링 수행
SELECT cust_id, COUNT(*) AS orders
FROM Orders
GROUP BY cust_id
HAVING COUNT(*) >=2;
-- 가격이 4이상인 제품을 두 개 이상 가진 공급업체를 추출
SELECT vend_id, COUNT(*) AS num_prods
FROM Products
WHERE prod_price >=4
GROUP BY vend_id
HAVING COUNT(*) >=2;
-- ORDER BY와 함께 사용
SELECT order_num, COUNT(*) AS items
FROM Orderitems
GROUP BY order_num
HAVING COUNT(*) >=3
ORDER BY items, order_num;
/*SELECT 절의 순서
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY */
-- 주문된 제품의 종류수
SELECT COUNT(DISTINCT prod_id)
FROM Orderitems;
-- 주문된 제품별 총 주문수량
SELECT prod_id, SUM(quantity)
FROM Orderitems
GROUP BY prod_id;
-- 주문된 제품의 총 주문수량이 100개나 넘은 제품의 제품번호
SELECT prod_id, SUM(quantity)
FROM Orderitems
GROUP BY prod_id
HAVING SUM(quantity) > 100;
-- 주문번호가 20005, 20007인 주문된 제품중에 총 주문수량이 100개가 넘은 제품의 제품번호
SELECT prod_id, SUM(quantity)
FROM Orderitems
WHERE order_num IN (20005, 20007)
GROUP BY prod_id
HAVING SUM(quantity) > 100;
-- 1. 주문제품의 주문번호와 주문번호별 주문제품의 수를 추출하시오 / 결과: 주문번호, 주문제품의수
SELECT order_num, COUNT(prod_id)
FROM Orderitems
GROUP BY order_num;
-- 2. 주문에서 주문날자별 주문한 고객의 수를 추출하시오 / 결과: 주문날자(YYYY-MM-DD), 고객의 수
SELECT TO_CHAR(order_date, 'YYYY-MM-DD'), COUNT(DISTINCT cust_id)
FROM Orders
GROUP by order_date;
-- 3. 제품에서 공급업체번호별 제품의 수를 추출하시오 / 결과: 공급업체번호, 제품의수
-- 제품일수이기 때문에 * 쓰면 안됨. ->널값도 카운트됨.
SELECT vend_id, COUNT(prod_id)
FROM Products
GROUP by vend_id;
-- 4. 고객 중에 우편번호가 4로 시작되는 고객의 수를 추출하시오 /결과: 고객의 수
SELECT COUNT(cust_id)
FROM Customers
WHERE cust_zip LIKE '4%';
-- 5. 고객 중에 이메일 주소가 없는 고객의 수를 추출하시오 / 결과: 고객의 수
SELECT COUNT(cust_id)
FROM Customers
WHERE cust_email IS NULL;
-- 6. 제품중에 공급업체별 제품가격의 평균을 추출하시오 / 결과: 공급업체번호, 평균가격
SELECT vend_id, AVG(prod_price)
FROM Products
GROUP BY vend_id;
-- 7. 고객에서 주별 고객의 수를 추출하시오 / 결과: 고객주, 고객의수
SELECT cust_state, COUNT(cust_id)
FROM Customers
GROUP BY cust_state;
-- 8. 제품에서 공급업체가 BRS01, DLL01인 제품중에 가장비싼 제품의 가격이 5$이상인 제품의 제품번호를 추출하시오
-- 결과: 공급업체번호, 제품번호, 제품가격(5$이상인 가장비싼 제품의 가격)
SELECT vend_id
FROM Products
WHERE vend_id IN ('BRS01', 'DLL01')
group by vend_id
having max (prod_price) >=5;
-- 9. 주문에서 1월중 주문한 주문중에 가장늦게 주문한 고객번호를 추출 / 결과: 주문번호, 주문일자(YYYY-MM-DD), 고객번호
SELECT order_num, MIN(TO_CHAR(order_date, 'YYYY-MM-DD')), cust_id
FROM Orders
WHERE TO_CHAR(order_date, 'MM') = '01' group by order_num, cust_id;
-- 10. 주문에서 1월중 주문한 주문중에 고객별 가장늦게 주문한 주문일자를 추출 / 결과: 고객번호, 주문일자(YYYY-MM-DD)
SELECT cust_id, MIN(TO_CHAR(order_date, 'YYYY-MM-DD'))
FROM Orders
WHERE TO_CHAR(order_date, 'MM') = '01' group by cust_id;
/*하위쿼리 사용*/
-- IN절안에 하위쿼리 사용하기
-- 하위쿼리를 먼저 실행하여 결과를 추출하고 그 결과를 바탕으로 메인 쿼리를 실행한다
-- 상품번호가 RGAN01인 상품을 주문한 고객ID추출
SELECT cust_id
FROM Orders
WHERE order_num IN (
SELECT order_num
FROM Orderitems
WHERE prod_id = 'RGAN01');
-- 하위쿼리 안에 하위쿼리 사용
-- 상품번호가 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_state,
(SELECT COUNT(*)
FROM Orders
WHERE Orders.cust_id = Customers.cust_id) AS orders
FROM Customers
ORDER BY cust_name;
-- FROM절에 SQL 사용하기
SELECT order_num, order_item, type
FROM
(SELECT order_num, order_item, SUBSTR(prod_id, 1, 2), type
FROM Orderitems) A
WHERE type = 'BR';
-- EXISTS, NOT EXISTS
-- EXITTS : 주문한게 1건이라도 있는것을 추출하라.
SELECT cust_id, cust_name, cust_address
FROM customers a
WHERE EXISTS(
SELECT *
FROM orders
WHERE cust_id = a.cust_id
);
-- 1. 제품의 고유ID와 제품이름, 제품가격, 제품설명을 추출하시오 조건: 공급업체의 국가가 ‘USA’인 공급업체의 제품
SELECT prod_id, prod_name, prod_price, prod_desc
FROM Products a
WHERE vend_id IN(
SELECT vend_id
FROM Vendors
WHERE vend_country = 'USA'
);
-- 2. 고객의 고객번호, 고객이름, 고객주소, 고객메일주소를 추출하시오 조건: 고객의 메일주소가 있고 주문을 한 건이라도 한 고객
SELECT cust_id, cust_name, cust_address, cust_email
FROM Customers a
WHERE EXISTS (
SELECT *
FROM Orders
WHERE cust_id = a.cust_id
)
AND cust_email IS NOT NULL; -- AND와 WHERE 위치가 바뀌어도 됨.
-- 3. 공급업체의 공급업체번호, 공급업체이름, 공급업체주소, 공급업체별 제품 수를 추출하시오 조건: 공급업체별 제품수가 2개 이상인 공급업체
SELECT vend_id, vend_name, vend_address,
(SELECT COUNT(*)
FROM Products
WHERE vend_id = a.vend_id) prod_su
FROM Vendors a
WHERE EXISTS(
SELECT *
FROM Products
WHERE vend_id = a.vend_id
GROUP BY vend_id
HAVING COUNT(prod_id) >=2);
-- 4. 주문제품 중에 제품별로 항목가격이 가장 낮은 제품번호를 추출하시오 결과: 제품번호, 항목가격(제품별 가장 낮은 항목가격)
SELECT prod_id, MIN(item_price)
FROM Orderitems
GROUP BY prod_id;
/*테이블 조인*/
-- 1:1로 할지 , 1:多로 할지 사전에 결정해야함. 조인과 조인이 만나면 결과집합의 모수가 변하면 안됨.
-- ★★★Main table 판단(결과값 개수만큼 나오는 테이블이 main) -> 가상의 table
-- 모든 key join
-- 조인: 테이블 간에 동일한 컬럼들을 연결시켜서 통합된 정보를 추출하는 방법
-- 조인을 많이 할 수록 성능을 떨어진다
-- 상품에 대한 공급업체 정보를 함께 추출
SELECT vend_name, prod_name, prod_price
FROM Vendors, Products --메인 테이블 : Products
WHERE Vendors.vend_id = Products.vend_id;
-- 조인 시에 조건절에서 테이블간의 관계를 설정하지 안으면 cartesian product(집합의 개수 곱하기 결과.)
SELECT vend_name, prod_name, prod_price
FROM Vendors, Products;
-- ANSI SQL - 내부 조인 (ANSI도 많이 쓰이므로 연습해놔야함.)
SELECT vend_name, prod_name, prod_price
FROM Vendors INNER JOIN Products -- 이너조인 : 두 데이터가 같은 결과만 조인
ON Vendors.vend_id = Products.vend_id;
-- 여러 테이블 조인
-- 상품에 대한 공급업체 정보와 주문수량까지 추출
SELECT vend_name, prod_name, prod_price, quantity
FROM Orderitems, Vendors, Products -- 메인테이블 : Orderitems
WHERE Products.vend_id = Vendors.vend_id
AND Orderitems.prod_id = Products.prod_id
AND order_num = '20007';
-- 상품번호가 RGAN01인 주문자이름과 연락처 추출
SELECT cust_name, cust_contact
FROM Customers, Orders, Orderitems
WHERE Customers.cust_id = Orders.cust_id
AND Orderitems.order_num = Orders.order_num
AND prod_id = 'RGAN01';
-- 테이블 별칭사용
-- 상품번호가 RGAN01인 주문자이름과 연락처 추출
SELECT cust_name, cust_contact
FROM Customers C, Orders O, Orderitems OI
WHERE C.cust_id = O.cust_id
AND OI.order_num = O.order_num
AND prod_id = 'RGAN01';
-- 동일 테이블 조인
-- Jim Jones라는 사람이 일하는 회사의 모든 고객 담당자의 메일주소 추출
SELECT cust_id, cust_name, cust_contact
FROM Customers
WHERE cust_name = (SELECT cust_name
FROM Customers
WHERE cust_contact = 'Jim Jones');
-- 같은 쿼리를 조인으로 추출
SELECT c1.cust_id, c1.cust_name, c1.cust_contact
FROM Customers c1, Customers c2
WHERE c1.cust_name = c2.cust_name
AND c2.cust_contact = 'Jim Jones';
-- 조인 시 전체 컬럼 조회 처리
SELECT C.*, O.order_num, O.order_date, OI.prod_id
FROM Customers C, Orders O, Orderitems OI -- Orderitems를 기준으로 관계를 맺음.
WHERE C.cust_id = O.cust_id
AND OI.order_num = O.order_num
AND prod_id = 'RGAN01';
-- 1. 주문과 고객정보를 조인하여 다음의 결과를 추출하시오 결과: 주문번호, 주문날자, 고객번호, 고객이름, 고객주소, 고객시, 고객메일주소 조건: 고객별로 마지막 주문날자의 주문
SELECT O.order_num, O.order_date, O.cust_id, C.cust_name, C.cust_address, C.cust_city, C.cust_email
FROM Orders O, Customers C -- 메인테이블 : Orders
WHERE O.cust_id = C.cust_id
AND O.order_date = (
SELECT MAX(order_date)
FROM orders
WHERE O.cust_id = C.cust_id
-- GROUP BY (O.cust_id) 그룹바이로 해도 됨.
)
;
-- 정렬기준 추가
--SELECT * FROM table_name ORDER BY 3, 1 DESC;
--3번째 열을 기준으로 오름차순으로 정렬한 상태에서 1번째 열을 기준으로 내림차순으로 정렬하는 쿼리 입니다.
SELECT prod_id, prod_name, prod_price
FROM Products
ORDER BY 3, 1 DESC;
-- 2. 공급업체와 제품을 조인하여 다음의 결과를 추출하시오 결과: 공급업체번호, 공급업체이름, 공급업체주소, 제품번호, 제품이름, 제품가격, 제품설명 조건: 공급업체별로 평균제품가격 이상인 제품
SELECT V.vend_id, V.vend_name, V.vend_address, P.prod_id, P.prod_name, P.prod_price, P.prod_desc
FROM Vendors V, Products P
WHERE V.vend_id = P.vend_id
AND P.prod_price >= (
SELECT AVG(prod_price)
FROM Products
WHERE vend_id = V.vend_id
)
;
-- 3. 제품, 주문, 주문제품을 조인하여 다음의 결과를 추출하시오
-- 결과: 주문번호, 주문일자, 제품번호, 제품이름, 제품가격, 항목가격 조건: 제품가격과 항목가격이 다른 제품
SELECT O.order_num, TO_CHAR(O.order_date, 'YYYY-MM-DD') order_date, P.prod_id, P.prod_name, P.prod_price, OI.item_price
FROM Orders O, Products P, Orderitems OI
WHERE OI.order_num = O.order_num
AND OI.prod_id = P.prod_id
AND P.prod_price <> OI.item_price;
-- 4. 공급업체, 제품, 주문제품을 조인하여 다음의 결과를 추출하시오
-- 결과: 공급업체번호, 공급업체이름, 공급업체주소, 주문번호, 제품번호, 제품이름
SELECT V.vend_id, V.vend_name, V.vend_address, OI.order_num, P.prod_id, P.prod_name
FROM Vendors V, Orderitems OI, Products P, Orders O
WHERE OI.order_num = O.order_num
AND OI.prod_id = P.prod_id
AND P.vend_id = V.vend_id;
