4. ORACLE SQL 기초: 데이터 조작어 - 검색

---

 

 

1. SQL 개요

SQL은 Java, C#처럼 완전한 프로그래밍 언어라고 취급하지는 않는다.

그 대신, 데이터 부속어(Data Sublanguage)라고 부른다.

이는 SQL이 데이터베이스의 데이터와 메타 데이터를 생성하고 처리하는 문법만 갖추고 있기 때문이다.

그 덕분에 Java나 C로 작성된 클라이언트 서버 응용 프로그램에 SQL을 삽입하여 사용할 수도 있다.

SQL 문은 실행 순서가 없는 비절차적 언어이다.

즉, 찾고자 하는 데이터만 기술하고, 어떻게 찾는지, 찾는 절차(실행 순서)는 사용자가 따로 기술하지 않는다.

아래의 표 3-1은 SQL과 일반 프로그래밍 언어의 차이점을 보여준다.

 

1.1) SQL 기능에 따른 분류

SQL은 기능에 따라 데이터 정의어, 데이터 조작어, 데이터 제어어로 나눠진다.

데이터 정의어 (DDL: Data Definition Language)

• 테이블이나 관계의 구조를 생성하는 데 사용된다.
• CREATE, ALTER, DROP 문 등이 있다.

데이터 조작어 (DML: Data Manipulation Language)

• 테이블에 데이터를 검색, 삽입, 수정, 삭제하는 데 사용된다.
• SELECT, INSERT, DELETE, UPDATE 문 등이 있다.
• 여기서 SELECT 문은 특별히 질의어(Query)라고 부른다.

데이터 제어어(DCL: Data Control Language)

• 데이터의 사용 권한을 관리하는 데 사용된다.
• GRANT, REVOKE 문 등이 있다.

 

---

 

2. 데이터 조작어: 검색

2.1) SELECT 문

SELECT 문의 문장 구조는 다음과 같다.

SELECT 문의 기본 문법은 다음과 같다.

SELECT [ALL | DISTINCT] 속성1, 속성2
FROM 테이블1, 테이블2
[WHERE 검색 조건1, 검색 조건2]
[GROUP BY 속성 이름]
[HAVING 검색 조건1, 검색 조건2]
[ORDER BY 속성 이름 [ASC | DESC]]

/* [ ] == 선택 사항 */
/* | == 동시에 1개만 사용 가능 */

SELECT 문의 고급 문법은 다음과 같다.

SELECT 
    [ALL | DISTINCT] 
    [테이블 이름.]{* | 속성 이름 [[ AS ] 속성 이름 별칭]}
    
[FROM
    {테이블 이름 [AS 테이블 이름 별칭]}
    [INNER JOIN | LEFT [OUTER] JOIN | RIGHT [OUTER]JOIN
    {테이블 이름 [ON 검색 조건]}
    |FULL [OUTER] JOIN {테이블 이름}]]

[WHERE 검색 조건1, 검색 조건2]
[GROUP BY {속성 이름, [..., n]}]
[HAVING 검색 조건1, 검색 조건2]
[ORDER BY {속성 이름 [ASC | DESC], [..., n]}]

/* [ ] == 선택 사항 */
/* { } == 필수 사항 */
/* | == 동시에 1개만 사용 가능 */

예를 들어, "Customer 테이블에서 김연아 고객의 전화번호를 찾아라"라는 질의를 수행하는 코드를 작성한다고 가정하면 다음과 같다.

SELECT phone_num
FROM customer
WHERE name='김연아';

● Book 테이블에서 모든 도서의 이름과 가격을 검색하라.

Book 테이블

 

SELECT bookname, price
FROM book;

● Book 테이블에서 모든 도서의 가격과 이름을 검색하라.

Book 테이블

SELECT price, bookname
FROM book;

● Book 테이블에서 모든 도서의 번호, 이름, 출판사, 가격을 검색하라.

Book 테이블

SELECT bookid, bookname, publisher, price
FROM book;

SELECT *    /* 전체 선택 */
FROM book;

● Book 테이블에서 중복을 제거한 출판사 전체 목록을 검색하라.

Book 테이블

SELECT DISTINCT publisher
FROM book;

DISTINCT 키워드를 추가해주면 질의 결과에서 중복을 제거한다.

이는 선택 사항이다.

● Book 테이블에서 도서 번호와 도서 이름, 도서 가격을 출력하되, 도서 가격이 가장 앞에 나오도록 출력하라.

Book 테이블

SELECT price, bookname, bookid
FROM book;

SELECT 문을 사용할 때는 키워드 작성 순서에 따라 출력되는 항목의 순서가 결정된다.

● Book 테이블에서 도서 번호와 출판사를 출력하되, 도서 번호 속성의 이름을 'Book ID'로 설정하라.

Book 테이블

SELECT bookid as "Book ID", publisher
FROM book;

SELECT 문을 사용할 때 속성 이름 옆에 as 키워드를 사용하면, 속성의 이름을 변경할 수 있다.

속성의 이름을 작성해줄 때는 " " 기호를 사용해야 한다.

 

2.2) WHERE 조건

WHERE 키워드를 사용하면 검색 조건을 설정해줄 수 있다.

● Book 테이블에서 가격이 10000원이 넘는 도서의 이름과 출판사, 번호를 출력하라.

Book 테이블

SELECT bookname, publisher, bookid
FROM book
WHERE price >= 10000;

● Book 테이블에서 가격이 10000원 이상, 20000원 이하인 도서의 정보를 출력하라.

Book 테이블

SELECT *
FROM book
WHERE price >= 10000 AND price <= 20000;

SELECT *
FROM book
WHERE price BETWEEN 10000 AND 20000;

AND 키워드를 사용하면 AND 조건을 적용할 수 있다.

AND는 조건 2가지가 모두 만족해야 TRUE를 반환한다.

BETWEEN 키워드를 사용하면 ~이상 ~이하를 간편하게 표현할 수 있다.

● Book 테이블에서 출판사가 '나무수' 또는 '대한미디어'인 도서의 이름을 출력하라.

Book 테이블

SELECT bookname
FROM book
WHERE publisher IN ('나무수', '대한미디어');

IN 키워드를 사용하면 ( ) 안에 작성된 조건들 중 만족하는 값을 결과에 적용한다.

괄호 안에 오는 값이 여러 개일 경우 OR 조건을 적용한다.

● Book 테이블에서 출판사가 '나무수' 또는 '대한미디어'가 아닌 도서의 이름을 출력하라.

Book 테이블

SELECT bookname
FROM book
WHERE publisher NOT IN ('나무수', '대한미디어');

NOT IN 키워드는 IN 키워드의 반대 의미를 지닌다.

( ) 안에 작성된 조건들을 제외한 값을 탐색한다.

괄호 안에 오는 값이 여러 개일 경우 OR 조건을 적용한다.

● Book 테이블에서 '축구의 역사'를 출판한 출판사를 검색하라.

Book 테이블

SELECT bookname, publisher
FROM Book
WHERE bookname LIKE '축구의 역사';

LIKE 키워드를 사용하면 = 기호를 대신할 수 있다.

LIKE 키워드를 사용할 때는 ' ' 기호로 값을 묶어야 한다.

" " 기호가 아님에 주의하자.

 

2.3) 와일드카드

WHERE 키워드를 사용하여 검색 조건을 설정할 때 와일드카드를 사용하여 보다 세세한 부분까지 검색 조건을 설정해줄 수 있다.

ORACLE SQL에서 주로 사용되는 와일드카드는 다음 표와 같다.

● Book 테이블에서 도서 이름에 '축구'가 포함된 출판사를 검색하라.

Book 테이블

SELECT bookname, publisher
FROM Book
WHERE bookname LIKE '%축구%';

● Book 테이블에서 도서 이름 왼쪽 2번째 위치에 '구'라는 문자열을 가지는 도서를 검색하라.

Book 테이블

SELECT *
FROM book
WHERE bookname LIKE '_구%';

 

2.4) 복합 조건

WHERE 키워드를 사용해 조건을 설정할 때, 다양한 조건들을 함께 적용해 사용할 수 있다.

이 경우, AND 또는 OR로 여러 조건들을 어떻게 적용할 것인지를 설정해주어야 한다.

● Book 테이블에서 축구에 관한 도서 중 가격이 20,000원 이상인 도서를 검색하라.

Book 테이블

SELECT *
FROM book
WHERE bookname LIKE '%축구%' AND price >= 20000;

● Book 테이블에서 출판사가 '굿스포츠' 혹은 '대한미디어'인 도서를 검색하라.

Book 테이블

SELECT *
FROM book
WHERE publisher = '굿스포츠' OR publisher = '대한미디어';

 

2.5) ORDER BY

ORDER BY 키워드를 사용하면 검색한 결과를 사용자가 설정해준 조건에 맞춰 정렬할 수 있다.

오름차순/내림차순 설정을 제공하며, 각각 ASC/DESC 키워드를 사용한다.

이때 기본 설정은 오름차순(ASC)이다.

● Book 테이블에서 도서를 이름순으로 검색하라.

Book 테이블

SELECT *
FROM book
ORDER BY bookname;

● Book 테이블에서 도서를 가격순으로 검색하되, 가격이 같으면 이름순으로 검색하라.

Book 테이블

SELECT *
FROM book
ORDER BY price, bookname;

● Book 테이블에서 도서를 가격의 내림차순으로 검색하되, 가격이 같다면 출판사 오름차순으로 검색하라.

Book 테이블

SELECT *
FROM book
ORDER BY price DESC, publisher ASC;

 

---

 

3. 집계 함수

SQL 상에서도 SUM, AVG, MAX, MIN, COUNT와 같은 집계 함수를 사용할 수 있다.

집계 함수를 사용할 때, 의미 있는 열 이름을 출력하고 싶으면, 속성 이름의 별칭을 지정하는 as 키워드를 사용하면 된다.

집계 함수의 종류와 문법은 다음과 같다.

● 고객이 주문한 도서의 총판매액을 구하시오.

 SELECT SUM(saleprice)
 FROM   orders;

● 2번 김연아 고객이 주문한 도서의 총판매액을 구하시오.

SELECT SUM(saleprice) AS "총매출"
FROM   Orders
WHERE  custid = 2;

● 고객이 주문한 도서의 총 판매액, 평균값, 최저가, 최고가를 구하시오.

SELECT SUM(saleprice) AS "Total",
       AVG(saleprice) AS "Average",
       MIN(saleprice) AS "Minimum",
       MAX(saleprice) AS "Maximum"
FROM Orders;

● 마당 서점의 도서 판매 건수를 구하시오.

SELECT COUNT(*)
FROM   Orders;

 

3.1) GROUP BY

GROUP BY 키워드를 사용하면 아래와 같이 지정해준 속성으로 결괏값을 묶을 수 있다.

SELECT custid, COUNT(*) AS "도서수량", SUM(saleprice) AS "총액"
FROM Orders
GROUP BY custid;

● 가격이 8,000원 이상인 도서를 구매한 고객에 대하여 고객별 주문 도서의 총수량을 구하시오. 단, 두 권 이상 구매한 고객만 구한다.

SELECT   custid, COUNT(*) AS "도서수량"
FROM     Orders
WHERE    saleprice >= 8000
GROUP BY custid
HAVING   count(*) >= 2;

HAVING 키워드를 사용하면 집계 함수를 가지고 조건을 설정해줄 수 있다.

HAVING 키워드는 항상 GROUP BY 키워드 뒤에 와야 한다.

 

---

 

4. JOIN

2개 이상의 테이블을 합쳐야 할 때 JOIN 키워드를 사용할 수 있다.

JOIN을 적용하면 각 테이블의 튜플을 가지고 모든 경우의 수를 전부 표현한다.

즉, 튜플의 개수가 5개인 Customer 테이블과 튜플의 개수가 10개인 Orders 테이블을 JOIN으로 연결하면 5 * 10, 총 50개의 튜플로 합쳐진다.

SELECT *
FROM   Customer, Orders;

● 고객과 고객의 주문에 대한 데이터를 모두 보여라.

SELECT *
FROM   Customer, Orders
WHERE  Customer.custid = Orders.custid;

● 고객과 고객의 주문에 관한 데이터를 고객 번호순으로 정렬하여 보이시오.

SELECT    *
FROM      Customer, Orders
WHERE     Customer.custid = Orders.custid
ORDER BY  Customer.custid;

● 고객의 이름과 고객이 주문한 도서의 판매 가격을 검색하시오.

SELECT name, saleprice
FROM   Customer, Orders
WHERE  Customer.custid = Orders.custid;

● 고객별로 주문한 모든 도서의 총판매액을 구하고, 고객별로 정렬하시오.

SELECT    name, SUM(saleprice)
FROM      Customer, Orders
WHERE     Customer.custid = Orders.custid
GROUP BY  Customer.name
ORDER BY  Customer.name;

● 고객의 이름과 고객이 주문한 도서의 판매 가격을 검색하시오. (JOIN 사용)

SELECT name, saleprice
FROM   Customer JOIN Orders
ON     Customer.custid = Orders.custid;

● 고객의 이름과 고객이 주문한 도서의 판매 가격을 검색하시오. (USING 사용)

SELECT name, saleprice
FROM   Customer JOIN Orders USING(custid);

● 고객의 이름과 고객이 주문한 도서의 판매 가격을 검색하시오. (Natural Join)

SELECT name, saleprice
FROM   Customer NATURAL JOIN Orders;

 

4.1) OUTER JOIN

외부 조인을 적용한다.

LEFT/RIGHT JOIN으로 작성하여 OUTER 키워드를 생략할 수 있다.

● 도서를 구매하지 않은 고객을 포함하여 고객의 이름과 고객이 주문한 도서의 판매 가격을 구하시오.

SELECT Customer.name, saleprice
FROM   Customer LEFT OUTER JOIN Orders
ON     Customer.custid = Orders.custid;

 

---

 

5. 부속 질의

SQL 질의 안에 또 다른 질의를 포함시킬 수 있다.

이를 '부속 질의'라고 부른다.

● 가장 비싼 도서의 이름을 보여라.

SELECT bookname
FROM   Book
WHERE  price = ( SELECT MAX(price)
                 FROM   Book );

● 도서를 구매한 적이 있는 고객의 이름을 검색하라.

SELECT name
FROM   Customer
WHERE  custid IN ( SELECT custid
                   FROM   Orders );

● '대한미디어' 출판사에서 출판한 도서를 구매한 고객의 이름을 검색하라.

SELECT name
FROM   Customer
WHERE  custid IN ( SELECT custid
                   FROM   Orders
                   WHERE  bookid IN ( SELECT bookid
                                      FROM   Book
                                      WHERE publisher = '대한미디어' ));

 

5.1) 상관 부속 질의

상관 부속 질의(Correlated subquery)는 상위 부속 질의의 튜플을 사용하여 하위 부속 질의를 계산한다.

즉, 상위 부속 질의와 하위 부속 질의가 독립적이지 않고, 서로 관련을 맺고 있다.

● 출판사별로 출판사 평균 도서 가격보다 비싼 도서를 검색하라.

SELECT b1.bookname
FROM   Book b1
WHERE  b1.price > (SELECT AVG(b2.price)
                   FROM   Book b2
                   WHERE  b2.publisher = b1.publisher);

 

---

 

6. 집합 연산

합집합(UNION), 차집합(MINUS), 교집합(INTERSECT)을 계산할 수 있다.

● 도서를 주문하지 않은 고객의 이름을 보이시오.

SELECT name
FROM   Customer
MINUS
SELECT name
FROM   Customer
WHERE  custid IN ( SELECT custid
                   FROM   Orders );

 

---

 

7. EXISTS

튜플이 존재하면 결과에 포함시킨다.

부속 질의문의 어떤 행이 조건에 만족하면 참이다.

NOT EXISTS는 부속 질의문의 모든 행이 조건에 만족하지 않을 때만 참이다.

● 주문이 있는 고객의 이름과 주소를 보이시오.

SELECT name, address
FROM   Customer cs
WHERE  EXISTS ( SELECT *
                FROM   Orders od
                WHERE  cs.custid = od.custid );

 

---

 

---

수고하셨습니다!

---