테이블에서 특정 속성의 값이 같은 투플을 모아 그룹을 만들고, 그룹별로 검색을 하기 위해 GROUP BY 키워드를 사용한다. 그룹에 대한 조건을 추가하려면 GROUP BY 키워드를 HAVING 키워드와 함께 사용하면 된다. GROUP BY 키워드가 없는 SELECT 문에서는 테이블 전체를 하나의 그룹으로 하여 검색을 진행하는 것으로 이해할 수 있다. 그룹별로 검색하는 SELECT 문의 기본 형식은 다음과 같다.
SELECT [ ALL | DISTINCT ] 속성_리스트
FROM 테이블_리스트
[ WHERE 조건 ]
[ GROUP BY 속성_리스트 [ HAVING 조건 ] ]
[ ORDER BY 속성_리스트 [ ASC | DESC ] ];
주문 테이블에서 주문제품별 수량의 합계를 검색해보자. 속성값은 총주문수량으로 한다.
SELECT product, SUM(quantity) AS '총주문수량' FROM order_info GROUP BY product;
주문제품별 수량의 합계를 구하기 위해 동일 제품을 주문한 투플을 모아 그룹으로 만들고, 그룹별로 수량의 합계를 계산한다.
그룹별로 검색할 때는 그룹을 나누는 기준이 되는 속성을 SELECT 절에도 작성하는 것이 좋다. SELECT 절에 그룹을 나누는 기준 속성을 작성하지 않아도 실행은 되지만 어떤 그룹에 대한 검색 결과인지를 결과 테이블에서 확인하기 어렵기 때문이다.
제품 테이블에서 제조업체별로 제조한 제품의 개수와 제품 중 가장 비싼 단가를 검색하되, 제품의 개수는 재품수라는 이름으로 출력하고 가장 비싼 단가는 최고가라는 이름으로 출력해보자.
SELECT manufacturer, COUNT(*) AS 재품수, MAX(price) AS 최고가 FROM product GROUP BY manufacturer;
제품 테이블에서 제품을 3개 이상 제조한 제조업체별로 제품의 개수와, 제품 중 가장 비싼 단가를 검색해보자. 제품의 개수는 제품수, 가장 비싼 단가는 최고가로 속성을 표기한다.
SELECT manufacturer, COUNT(*) AS 제품수, MAX(price) AS 최고가 FROM product GROUP BY manufacturer HAVING COUNT(*)>=3;
고객 테이블에서 적립금 평균이 1000원 이상인 등급에 대해 등급별 고객 수와 적립금 평균을 검색해보자.
SELECT grade, COUNT(*) AS 고객수, AVG(saved_money) AS 평균적립금 FROM customer GROUP BY grade HAVING AVG(saved_money) >= 1000;
그룹별로 검색할 때는 집계 함수나 GROUP BY 절에 있는 속성 외의 속성은 SELECT 절에 사용할 수 없다. 예를 들어 각 주문고객이 주문한 총주문수량을 주문제품별로 검색하기 위해 SELECT 문을 다음과 같이 작성하면 오류가 발생한다. GROUP BY 절에 없는 주문고객 속성을 SELECT 절에서 사용했기 때문이다.
주문 테이블에서 각 주문고객이 주문한 제품의 총주문수량을 주문제품별로 검색하라.
SELECT product, customer, SUM(quantity) AS 총주문수량 FROM order_info GROUP BY product, customer;
9. 여러 테이블에 대한 조인 검색
여러 개의 테이블을 연결하여 데이터를 검색하는 것을 조인 검색이라 한다. 조인 검색을 하려면 테이블을 연결해주는 속성이 필요하고 이 속성을 조인 속성이라 한다. 테이블을 연결하려면, 조인 속성의 이름은 달라도 되지만 도메인은 반드시 같아야 한다. 일반적으로 테이블의 관계를 나타내는 외래키를 조인 속성으로 이용한다.
조인 검색을 위한 SQL 문은 FROM 절에 검색에 필요한 모든 테이블을 나열하고, WHERE 절에는 조인 속성의 값이 같아야 함을 의미하는 조인 조건을 제시한다. 여러 테이블을 이용하는 조인 검색은 이름이 같은 속성이 서로 다른 테이블에 존재할 수도 있기 때문에 속성의 이름 앞에 해당 속성이 소속된 테이블의 이름을 표시해주는 것이 좋다. 테이블의 이름과 속성의 이름은 "." 기호로 연결한다.
판매 데이터베이스에서 banana 고객이 주문한 제품의 이름을 검색해보자.
SELECT product.name AS 제품명 FROM order_info, product WHERE order_info.product = product.id AND order_info.customer = 'banana';
FROM 절에 검색에 필요한 제품 테이블과 주문 테이블을 모두 나열한다. 그리고 WHERE 절에는 주문고객이 banana라는 조건과 함께, 조인 속성인 주문제품 속성이 값과 제품번호 속성의 값이 같아야 함을 의미하는 조인 조건을 제시한다.
판매 데이터베이스에서 나이가 30세 이상의 고객이 주문한 제품의 주문제품(product)과 주문일자를 검색해보자.
SELECT order_info.product AS '주문제품', order_info.order_date AS 주문일자 FROM customer, order_info WHERE order_info.customer = customer.id AND customer.age >= 30;
테이블 이름이 길면 속성 이름 앞에 소속 테이블을 표기하는 일이 번거로울 수 있다. 이 경우 테이블의 이름을 대신하는 단순한 별명을 사용할 수 있다. FROM 절에 테이블의 이름과 별명을 함께 제시하면 된다. 다음과 같이 별명을 사용할 수 있으며 별명을 부여하기 위해 사용하는 AS 키워드는 생략할 수 있다.
SELECT o.product AS '주문제품', o.order_date AS 주문일자 FROM customer AS c, order_info AS o WHERE o.customer = c.id AND c.age >= 30;
판매 데이터베이스에서 고명석 고객이 주문한 제품의 제품명을 검색해보자.
SELECT product.name AS 제품명 FROM order_info, customer, product WHERE order_info.customer = customer.id AND order_info.product = product.id AND customer.name = '고명석';
10. 부속 질의문을 이용한 검색
SELECT 문 안에 또 다른 SELECT 문을 포함할 수도 있다. 다른 SELECT 문 안에 내포된(nested) SELECT 문을 부속 질의문 또는 서브 질의문(sub query)이라 한다. 그리고 다른 SELECT 문을 포함하는 SELECT 문을 상위 질의문 또는 주 질의문(main query)이라 한다. 부속 질의문은 괄호로 묶어 작성하고 ORDER BY 절을 사용할 수 없으며, 상위 질의문보다 먼저 수행된다. 부속 질의문을 이용한 검색은 이어 달리기처럼 부속 질의문을 먼저 수행하고, 그 결과를 이용해 상위 질의문을 수행하여 최종 결과 테이블을 반환한다.
부속 질의문은 하나의 행을 결과로 반환하는 단일 행 부속 질의문과, 하나 이상의 행을 결과로 반환하는 다중 행 부속 질의문으로 분류한다. 부속 질의문과 상위 질의문을 연결하는 연산자가 필요한데 부속 질의문의 종류에 따라 사용할 수 있는 연산자가 다르므로 주의해야 한다. 단일 행 부속 질의문은 일반 비교 연산자를 사용할 수 있지만, 다중 행 부속 질의문은 일반 비교 연산자를 사용할 수 없다.
판매 데이터베이스에서 달콤비스켓과 같은 제조업체에서 제조한 제품의 제품명과 단가를 검색해보자.
SELECT product.name, product.price FROM product WHERE manufacturer = (SELECT manufacturer FROM product WHERE name = '달콤비스켓');
판매 데이터베이스에서 적립금이 가장 많은 고객의 고객이름과 적립금을 검색해보자.
SELECT customer.name, customer.saved_money FROM customer WHERE saved_money = (SELECT MAX(customer.saved_money) FROM customer);
최대 적립금이 단일 값이므로 위의 예제의 부속 질의문은 단일 행 부속 질의문이다. 그러므로 = 연산자를 사용할 수 있다. 단일 행 부속 질의문은 = 연산자 외에도 <>,>,<,>=,<= 와 같은 다른 비교 연산자도 사용할 수 있다. 반면, 다중 행 부속 질의문은 이러한 일반 비교 연산자를 사용할 수 없다.
판매 데이터베이스에서 banana 고객이 주문한 제품의 제품명과 제조업체를 검색해보자.
<부속 질의문>
SELECT product.name, product.manufacturer FROM product WHERE product.id IN (SELECT order_info.product FROM order_info WHERE order_info.customer = 'banana');
<조인 질의를 이용>
SELECT product.name, product.manufacturer FROM product, order_info WHERE product.id = order_info.product AND order_info.name = 'banana';
<EXISTS 연산자를 이용한 SELECT 문>
SELECT product.name, product.manufacturer FROM product WHERE EXISTS(SELECT * FROM order_info WHERE order_info.customer = 'banana' AND order_info.product = product.id);
주문 테이블에서 banana 고객이 주문한 제품의 번호를 부속 질의문으로 먼저 검색하고 그 결과를 이용해 제품 테이블에서 제품의 제품명과 제조업체를 상위 질의문으로 검색한다. 위의 부속 질의문은 결과 값을 여러 개 반환하는 다중 행 부속 질의문이다. 그러므로 = 연산자 대신 IN 연산자를 함께 사용해야 한다. IN 연산자는 부속 질의문의 결과 값들 중에서 하나라도 일치하는 것이 있으면 검색 조건이 참이 되는, 수학의 집합에 사용되는 ∈ 연산자와 같은 역할을 한다.
판매 데이터베이스에서 banana 고객이 주문하지 않은 제품의 제품명과 제조업체를 검색해보아라.
SELECT product.name, product.manufacturer FROM product WHERE product.id NOT IN(SELECT order_info.product FROM order_info WHERE order_info.customer = 'banana');
<다중 행 부속 질의문에 사용 가능한 연산자>
- IN : 부속 질의문의 결과 값 중 일치하는 것이 있으면 검색 조건이 참
- NOT IN : 부속 질의문의 결과 값 중 일치하는 것이 없으면 검색 조건이 참
- EXISTS : 부속 질의문의 결과 값이 하나라도 존재하면 검색 조건이 참
- NOT EXISTS : 부속 질의문의 결과 값이 하나도 존재하지 않으면 검색 조건이 참
- ALL : 부속 질의 문의 결과 값 모두와 비교한 결과가 참이면 검색 조건을 만족(비교 연산자와 함께 사용)
- ANY 또는 SOME : 부속 질의문의 결과 값 중 하나라도 비교한 결과가 참이면 검색 조건을 만족(비교 연산자와 함께 사용)
판매 데이터베이스에서 대한식품이 제조한 모든 제품의 단가보다 비싼 제품의 제품명, 단가, 제조업체를 검색해보자.
SELECT product.name, product.price, product.manufacturer FROM product WHERE product.price > ALL(SELECT product.price FROM product WHERE manufacturer = '대한식품');
판매 데이터베이스에서 2013년 3월 15일에 제품을 주문한 고객의 고객이름을 검색해보자.
SELECT customer.name FROM customer WHERE customer.id = ANY(SELECT order_info.customer FROM order_info WHERE order_date = '2013-03-15');
SELECT customer.name FROM customer WHERE EXISTS(SELECT * FROM order_info WHERE customer.id = order_info.customer AND order_info.order_date = '2013-03-15');
판매 데이터베이스에서 2013년 3월 15일에 제품을 주문하지 않은 고객의 고객이름을 검색해보자.
SELECT customer.name FROM customer WHERE NOT EXISTS(SELECT * FROM order_info WHERE customer.id = order_info.customer AND order_info.order_date = '2013-03-05');
[출처] 한빛아카데미 데이터베이스 개론 김현희지음
