2026년 1월 29일 - 5주차
이론
집합연산
합집합(UNION): 두 SELECT 결과를 합쳐서 하나로 만드는 것. 중복된 행은 자동으로 제거되고 컬럼 개수와 타입이 같아야 한다.
차집합(MINUS): mariaDB에서는 EXCEPT로 쓰며, 첫 번째 결과에는 있고, 두 번째 결과에는 없는 것만 남긴다. 말 그대로 빼는것.
교집합(INTERSECT): 두 SELECT 결과에 공통으로 존재하는 데이터만 남기는 것.
EXISTS(): 무조건 상관 부속질의어야 하고, 괄호 안의 쿼리의 실행 결과가 한줄 이상이라도 나오면 true, 결과가 한줄도 나오지 않으면 flase 리턴하고 break 함. 결과가 매트릭스인지 아닌지도 중요하지 않다. 그냥 나오기만 하면 ture고 안나오면 false이니까.
부속 질의
결론: 부속질의는 어디에서든 사용 할 수 있다.
인라인 뷰 - FROM 부속 질의
SELECT문을 작성해서 나온 결과값(표)는 테이블처럼 보이지만, 테이블과는 다른것인데, 그것을 뷰라고 부른다. 그렇게 나온 뷰를 테이블처럼 사용하는 것이 인라인 뷰이다. INLINE. LINE(쿼리) 안에 뷰를 넣겠다는 것. SELECT 다음엔 항상 테이블명이 나왔아야 하는데, 테이블명 대신에 서브쿼리를 넣을 수 있다. 테이블로 쓰기 위해서 별칭을 지어준 후, 그 별칭을 테이블명 처럼 사용한다. 별칭 안지어주면 절대로 안됨..
중첩 질의 - WHERE 부속질의
지금까지 했었던 WHERE 조건 부속질의를 말한다. WHERE조건문에도 부속질의를 쓸 수 있다는 뜻이다. 결과값이 스칼라여야 하는지, 벡터여야 하는지 매트릭스여야 하는지를 연산자가 정한다. 어떤 연산자를 쓰냐에 따라 값의 종류가 바뀐다. HAVING도 WHERE랑 동등한것으로 보기 때문에 HAVING에도 비슷하게 부속질의 사용이 가능하다.
스칼라 부속 질의 - SELECT 부속 질의
SELECT FROM Orders o; 에서 부분에서 부속질의를 사용하는 것. 스칼라 값을 가지는 부속질의를 사용 할 수 있으며, 사용하는 거의 대부분의 경우가 상관 부속질의이다. 당연하게도, 결과값이 스칼라가 아니면 사용 할 수 없다.
SQL 실습
오늘 작성한 코드 전체.
-- 도서를 주문하지 않은 고객의 이름을 구하세요: (억지로 NOT IN이 아닌 IN으로 품)
SELECT * FROM Customer c
EXCEPT
SELECT c.name FROM Customer c
WHERE c.custid IN(SELECT o.custid FROM Orders o);
# 아래는 그냥 NOT IN 쓰는 것.
SELECT c.name FROM Customer c
WHERE c.custid NOT IN(SELECT o.custid FROM Orders o);
SELECT o.custid FROM Orders o;
-- 주문이 있는 고객의 이름과 주소를 구하세요:
SELECT * FROM Customer c
WHERE EXISTS(SELECT * FROM Orders WHERE custid = c.custid);
-- 연습문제
-- 1) 마당서점의 고객이 요구하는 다음 질문에 대해 SQL문을 작성하시오.
-- 5. 박지성이 구매한 도서의 출판사 수
SELECT COUNT(b.bookid)
FROM Orders o
INNER JOIN Customer c ON c.custid = o.custid
INNER JOIN Book b ON b.bookid = o.bookid
WHERE c.name = '박지성'
;
-- 6. 박지성이 구매한 도서의 이름, 가격, 정가와 판매가격의 차이
SELECT b.bookname, b.price, b.price - o.saleprice
FROM Orders o
INNER JOIN Customer c ON c.custid = o.custid
INNER JOIN Book b ON b.bookid = o.bookid
WHERE c.name = '박지성'
;
-- 7. 박지성이 구매하지 않은 도서의 이름
SELECT b.bookname
FROM Orders o
INNER JOIN Customer c ON c.custid = o.custid
INNER JOIN Book b ON b.bookid = o.bookid
WHERE b.bookid NOT IN (
SELECT o.custid FROM Orders o WHERE c.name = '박지성'
);
-- 2) 마당 서점의 운영자와 경영자가 요구하는 다음 질문에 대해 SQL문을 작성하시오.
-- 8. 주문하지 않은 고객의 이름
SELECT c.name
FROM Customer c
LEFT OUTER JOIN Orders o ON c.custid = o.custid
WHERE o.custid IS Null
;
-- 9. 주문 금액의 총액과 주문의 평균 금액
SELECT SUM(o.saleprice), AVG(o.saleprice) FROM Orders o;
-- 10. 고객의 이름과 고객별 구매액
SELECT c.name, SUM(o.saleprice)
FROM Customer c
INNER JOIN Orders o ON c.custid = o.custid
GROUP BY c.name
; # 이름이 같은 사람 있으면 문제 발생.
-- 11. 고객의 이름과 고객이 구매한 도서 목록
SELECT c.name, b.bookname
FROM Customer c
INNER JOIN Orders o ON c.custid = o.custid
INNER JOIN Book b ON b.bookid = o.bookid
;
-- 12. 도서의 가격(Book 테이블)과 판매가격(Orders 테이블)의 차이가 가장 많은 주문
SELECT MAX(b.price-o.saleprice)
FROM Orders o
INNER JOIN Book b ON b.bookid = o.bookid
;
-- 13. 도서의 판매액 평균보다 자신의 구매액 평균이 더 높은 고객의 이름
# 포기: 강사님 코드임.
SELECT c.name, AVG(o.saleprice)
FROM Orders o
INNER JOIN Customer c ON c.custid = o.custid
GROUP BY c.name
HAVING AVG(o.saleprice) > (
SELECT AVG(o.saleprice) FROM Orders o
)
;
-- 연습문제에서 조금 틀린부분
-- 5번: 출판사 수를 출력해야 하는데 책 이름의 수를 해버렸다.
-- 당장에 나오는 값은 비슷하겠지만 달라질 수도 있어서 문제가 됨. + 중복값 제거 해야함
SELECT COUNT(DISTINCT b.publisher) AS cnt
FROM Orders o
INNER JOIN Customer c ON c.custid = o.custid
INNER JOIN Book b ON b.bookid = o.bookid
WHERE c.name = '박지성'
;
-- 억지로 서브쿼리 사용 가능
SELECT COUNT(DISTINCT b.publisher)
FROM Book b
WHERE b.bookid IN (
SELECT o.bookid FROM Orders o
WHERE o.custid IN (
SELECT c.custid FROM Customer c WHERE c.name = '박지성'
)
); # IN 사용 예시
SELECT COUNT(DISTINCT b.publisher)
FROM Book b
WHERE EXISTS (
SELECT * FROM Orders o INNER JOIN Customer c ON c.custid = o.custid
WHERE c.name = '박지성' AND o.bookid = b.bookid
); # EXISTS 사용 예시
-- 12번: 내코드는 차이가 가장 큰 주문의 값 하나만 나오기 때문에 완전하지 않음.
-- 값의 차이가 가장 많은 주문의 값이 아닌, 주문정보 전체를 구하는거라 모든 주문정보 필요.
SELECT o.* FROM Orders o
INNER JOIN Book b ON b.bookid = o.bookid
WHERE b.price - o.saleprice = (
SELECT MAX(b.price - o.saleprice)
FROM Orders o
INNER JOIN Book b ON b.bookid = o.bookid
)
;
SELECT MAX(b.price - o.saleprice)
FROM Orders o
INNER JOIN Book b ON b.bookid = o.bookid;
-- 인라인 뷰 (FROM 부속 질의)
SELECT t1.* FROM(
SELECT c.name, c.address
FROM Customer c
WHERE c.custid != 3
) t1
WHERE t1.address = '대한민국 서울'
SELECT * FROM () t2;
-- 이 괄호 안에 아래 코드 넣을 수 있음. 무한대로 넣을 수 있음...
SELECT c.*, t1.qwer, t1.custid FROM ( # t1.custid 쓰면 변수명 중복된 느낌
SELECT o.custid, SUM(o.saleprice) AS qwer
FROM Orders o
GROUP BY o.custid
) t1
INNER JOIN Customer c ON c.custid = t1.custid
WHERE t1.qwer > 20000;
-- 이러면 HAVING 대신에 WHERE를 쓸 수 있다.
-- 이제 이러면 2중 반복X FROM () 안의 코드가 먼저 실행되고 끝난 다음에 괄호밖으로 나온다.
-- 그래서 아래쪽에 o.custid는 이미 소멸됐기 때문에 쓸 수 없다. 그래서 t1.custid로 쓴다.
-- 고객번호가 2 이하인 고객의 판매액을 구하세요:
-- 결과: 고객이름, 고객별 판매금액
SELECT c.name, t2.total_price FROM (
SELECT t1.custid, SUM(t1.saleprice) AS total_price
FROM (
SELECT * FROM Orders o
WHERE o.custid <= 2
) t1
GROUP BY t1.custid
) t2
INNER JOIN Customer c ON c.custid = t2.custid ;
-- 중첩질의 (WHERE 부속질의)
-- 평균 주문금액 이하의 주문에 대하여 주문번호와 금액을 구하세요:
SELECT * FROM Orders o1
WHERE o1.saleprice <= (SELECT AVG(o2.saleprice) FROM Orders o2)
;
-- 반복이 돌때 괄호 내부의 o랑 밖의 o는 다른것. 근데 이름이 같아서 사용 불가.
-- 밖의 o가 내부로 들어와야 할때(상관 부속질의)는 o1, o2 이렇게 이름 붙여줘야 한다.
-- 근데 그냥 분간이 힘들다 싶으면 그냥 다르게 이름을 줘도 됨.
-- 각 고객의 평균 주문금액보다 큰 금액의 주문내역에 대해 주문번호, 고객번호, 금액을 구하세요:
SELECT o1.orderid, o1.custid, o1.saleprice FROM Orders o1
WHERE o1.saleprice > (
SELECT AVG(o2.saleprice) FROM Orders o2 WHERE o2.custid = o1.custid
)
;
SELECT AVG(o2.saleprice) FROM Orders o2 WHERE o2.custid = 1;
-- 대한민국에 거주하는 고객에게 판매한 도서의 총 판매액을 구하세요:
-- IN 사용
SELECT SUM(o1.saleprice) FROM Orders o1
WHERE o1.custid IN (
SELECT c1.custid FROM Customer c1 WHERE c1.address LIKE '%대한민국'
);
-- EXISTS 사용
SELECT * FROM Orders o
WHERE EXISTS(
SELECT * FROM Customer c
WHERE c.address LIKE '%대한%'
AND c.custid = o.custid
);
-- 3번 고객이 주문한 도서의 최고금액보다 비싼 도서를 구입한 주문의 주문번호와 금액을 구하세요:
SELECT o1.orderid, o1.saleprice FROM Orders o1
WHERE o1.saleprice > ALL (
SELECT o2.saleprice FROM Orders o2 WHERE o2.custid = 3
)
;
-- 스칼라 부속 질의 - SELECT 부속 질의
SELECT
o.custid,
o.bookid
o.saleprice,
(
SELECT c.name FROM Customer c WHERE c.custid = o.custid
) AS qqqq
FROM Orders o; # o.custid를 c.name의 값으로 바꾸고 싶을때 사용한다.
-- 고객별 책 주문 수를 구하세요:
SELECT c.*,
(
SELECT * FROM Orders o WHERE o.custid = c.custid
) AS cnt
FROM Customer c;
-- 고객 별 판매액을 구하세요: (단, 이름이 출력되야 한다)
SELECT (
SELECT c.name FROM Customer c WHERE c.custid = o.custid
), SUM(o.saleprice) FROM Orders o
GROUP BY o.custid
; -- 이게 완벽한 정답임. 지금까지는 mariaDB라 됐던것. Oracle에선 허용 안해줌
-- 연습문제
-- 1. 박지성이 구매한 도서의 출판사와 같은 출판사에서 도서를 구매한 고객의 이름
SELECT DISTINCT c.name
FROM Orders o
INNER JOIN Customer c ON c.custid = o.custid
INNER JOIN Book b ON b.bookid = o.bookid
WHERE b.publisher IN(
SELECT b.publisher
FROM Orders o
INNER JOIN Customer c ON c.custid = o.custid
INNER JOIN Book b ON b.bookid = o.bookid
WHERE c.name = '박지성'
)
-- 2. 두개 이상의 서로 다른 출판사에서 도서를 구매한 고객의 이름
SELECT c.name, t1.cnt FROM (
SELECT c.custid, COUNT(DISTINCT b.publisher) AS cnt
FROM Orders o
INNER JOIN Customer c ON c.custid = o.custid
INNER JOIN Book b ON b.bookid = o.bookid
GROUP BY c.custid
HAVING cnt >= 2
) t1 INNER JOIN Customer c ON c.custid = t1.custid
;
-- 3. 전체 고객의 30% 이상이 구매한 도서
SELECT * FROM Book b
WHERE (
SELECT COUNT(*) FROM Orders o WHERE o.bookid = b.bookid
) > (
SELECT COUNT(*) * 0.3 FROM Customer c
)
;
SELECT COUNT(*) * 0.3 FROM Customer c;
취업하고싶다...