요약.
1. 테이블에 저장된 모든 데이터를 보고자 합니다. SELECT와 FROM절로 만들어 보라.
-> SELECT 칼럼1, 칼럼2 FROM 테이블
2. 전체 데이터 중 조건을 만족하는 값만 보고 싶습니다. WHERE 절을 이용해 보라.
-> WHERE 칼럼 조건 값
3. 일정한 기분에 의해 정렬되어 표현하면 보다 쉽게 결과를 읽어 들일 수 있습니다. ORDER BY 절을 이용해 보라.
-> ORDER BY 칼럼 [ASC|DESC]
4. 오늘 가장 많은 매출액을 올린 제품이 궁금하세요? GROUP BY가 여러분에게 그 답을 제시해 줄 것임.
-> GROUP BY
5. GROUP BY 절의 결과로부터 특정 조건을 만족하는 값을 얻고자 한다면 HAVING 절을 사용하라.
-> HAVING 칼럼 연산자 값
====================================================
SQL은 여러분이 데이터베이스와 이야기할 수 있는 언어입니다. 여러분이 한국어를 통해 사람들과 의사 표현을 주고 받듯이 데이터베이스에 저장된 데이터를 처리하기 위해서도 언어가 필요하지요. 즉 데이터베이스와의 소통을 위해 여러분이 알아두어야 하는 언어가 바로 SQL(Structured Query Language)입니다.
====================================================
2.1 내 맘대로 데이터 얻기 – SELECT
1) SELECT를 이용한 데이터 조회
-> 테이블의 데이터를 조회하는 기본적인 SQL문은 다음과 같다.
SELECT 칼럼 이름1, 칼럼 이름2
FROM 테이블 이름;
간단한 SQL문은 위와 같이 SELECT와 FROM절로 구성되어 있습니다. SELECT절 다음에는 칼럼 이름이 오며, FROM절 다음에는 테이블의 이름이 온다.
2) 특정 칼럼 선택
-> 특정 칼럼을 선택하고자 한다면 SELECT절에 아래와 같이 칼럼을 지정해 두면 됩니다. 첫 번째 컬름은 employee_id(사원번호)이며, 두 번째 칼럼은 last_name(사원의 이름)입니다.
SQL> SELECT employee_id, last_name
2 FROM employees;
이상에서 SELECT문은 SELECT 명령으로 시작해서 ;(세미콜론)으로 종료하며, SELECT절과 FROM절로 구성됨을 알 수 있습니다.
그러나 질의 가 테이블에 저장된 전체 데이터를 모두 보여준다면 요즘과 같은 대용량의 데이터를 저장하는 테이블에서는 의미 없는 질의가 될 것입니다. 대용량의 데이터가 화면에 출력되는 모습만을 보게 되기 때문입니다.
====================================================
2.2 WHERE절
1) WHERE절의 기본
-> WHERE절은 전체 데이터 중 조건을 만족하는 값만 얻을 수 있도록 합니다. WHERE절은 다음과 같이 이루어 집니다.
WHERE 칼럼이름 조건 값
그렇다면, 앞의 사원 테이블에서 이름이 King인 사원의 사원번호와 입사일 정보를 얻어보도록 하죠.
SQL> SELECT employee_id, hire_date
2 FROM employees
3 WHERE last_name=’King’;
EMPLOYEE_ID HIRE_DATE
———– ———
156 30-JAN-96
100 17-JUN-87
WHERE절을 보면 last_name 칼럼의 값이 ‘King’과 일치하는 행을 찾습니다. 조건을 만족하는 행들 중에서 사원번호(employee_id)와 입사일(hire_date)칼럼에 대한 값을 반환합니다. 여기서 주의할 점은 테이블에 저장된 문자 데이터에 대해서는 대소문자를 구분한다는 것, 그리고 문자, 날짜 데이터는 ‘ (single quotation)으로 묶어서 표현해 주어야 한다는 것입니다.
SQL> SELECT employee_id, hire_date
2 FROM employees
3 WHERE last_name = ‘king’;
no rows selected
2) AND를 이용한 조건절 작성
-> 전체 사원 중 급여가 $10,000 이상 $20,000 이하인 사원들을 얻어보는 예제입니다. 이 경우 문제를 다시 해석하면, 사원의 급여는 $10,000 이상이면서, $20,000 이하인 조건을 모두 만족해야 합니다. 이 경우 논리 연산자 AND를 사용해서 나타낼 수 있습니다.
SQL> SELECT employee_id, last_name, salary
2 FROM employees
3 WHERE salary >= 10000
4 AND salary <= 20000;
3) OR를 이용한 조건절 작성
-> 그렇다면, 사원가 직무가 ‘FI_MGR’과 ‘FI_ACCOUNT’인 사원들의 목록을 얻으려면 어떻게 해야 할까요. 즉 사원의 직무가 ‘FI_MGR’과 ‘FI_ACCOUNT’중 하나라도 만족하는 사원들을 모두 포함하는 결과를 예상할 수 있겠지요.
SQL> SELECT employee_id, last_name, salary
2 FROM employees
3 WHERE job_id = ‘FI_MGR’
4 OR job_id = ‘FI_ACCOUNT’;
EMPLOYEE_ID LAST_NAME SALARY
———– ————————- ———-
109 Faviet 9000
110 Chen 8200
111 Sciarra 7700
112 Urman 7800
113 Popp 6900
108 Greenberg 12000
6 rows selected.
4) BETWEEN AND를 이용한 조건절 작성
-> 범위를 지정할 때는 BETWEEN AND 조건을 권장합니다. 보다 간결하게 표현되기 때문입니다. 이는 복잡한 질의문에서 WHERE 조건절을 보다 분명히 나타낼 수 있는 장점이 있습니다. 그러나 $10,000을 초과하거나 $20,000 미만일 때는 > (크거나), < (작은) 와 같이 비교해야겠지요.
SQL> SELECT employee_id, last_name, salary
2 FROM employees
3 WHERE salary BETWEEN 10000 AND 20000;
5) IN을 이용한 조건절 작성
-> 만약 원하는 직무가 더 추가되는 경우는?
SQL> SELECT employee_id, last_name, job_id
2 FROM employees
3 WHERE job_id = ‘FI_MGR’
4 OR job_id = ‘FI_ACCOUNT’
5 OR job_id = ‘SA_MGR’
6 OR job_id = ‘SA_REP’;
이 경우 IN 조건을 통해 SQL 문장을 간결하게 나타낼 수 있다.
SQL> SELECT employee_id, last_name, job_id
2 FROM employees
3 WHERE job_id IN (‘FI_MGR’, ‘FI_ACCOUNT’, ‘SA_MGR’, ‘SA_REP’);
6) IS NULL/NOT 을 이용한 조건절 작성
-> 질의문이 조건을 만족하는 결과를 작성해 보았는데 그렇지 않은 경우도 생각해 볼 수 있을 것이다.
SQL> SELECT employee_id, last_name, salary, commission_pct
2 FROM employees
3 WHERE commission_pct IS NOT NULL;
NULL이 할당되지 않은 값이므로 이에 대한 부정으로 NULL 앞에 NOT을 붙여 사용하면 된다. 부정은 이와 같이 조건의 앞에 붙여 사용할 수 있는데, NOT BETWEEN AND나 NOT IN과 같이 사용하며, 수식에서는 ‘!=’ (같지않음)의 의미가 있다.
7) LIKE를 이용한 조건절 작성
-> 1997년에 입사한 사원들의 리스트 얻기.
SQL> SELECT employee_id, last_name, hire_date
2 FROM employees
3 WHERE hire_date BETWEEN ’01/Feb/97′ AND ’31/Dec/97′;
이 질의문은 다음과 같이 작성될 수도 있다.
SQL> SELECT employee_id, last_name, hire_date
2 FROM employees
3 WHERE hire_date LIKE ‘%97’;
‘%97’ 의 의미는 97로 끝나는 모든 값은 조건을 만족함을 나타내며, LIKE 조건과 함께 사용된다. 혹 여러분이 저장된 값의 일부만을 기억하고 있을 때에도 LIKE는 유용하게 사용할 수 있다.
LIKE 절에서 사용할 수 있는 와일드 카드 및 ESCAPE 옵션
-> 앞에서 사용한 ‘%’는 0~n 개의 문자열을 대체한다. 이외에도 단 하나의 글자만을 대체하는 ‘_’ 와일드 카드도 있다.
그러면 %나 _가 포함된 문자열을 검색하고자 할 때는 어떻게 해야 할까. 이때는 ESCAPE 옵션을 사용하여 ESCAPE 문자를 지정한 후 ESCAPE 문자를 이용하여 %나 _를 와일드 카드가 아닌 하나의 문자를 인식하도록 할 수 있다. 다음은 last_name에 ‘Au%’로 시작하는 문자열을 검색하는 예제이다.
SQL> SELECT employee_id, last_name
2 FROM employees
3 WHERE last_name LIKE ‘Au%%’ ESCAPE ”;
참고로 연도를 이용하여 조건을 만족하는 값을 찾을 때는 정확히 4자리로 표현하는 것이 바람직하다. 앞서 연도 4자리 중 뒤 2자리만 나타낸다면 1990년과 2090년의 구분이 의미가 없어지기 때문이다.
아래의 질의문은 함수를 이용하여 더욱 명확한 질의문을 작성해 본 예이다.
SQL> SELECT employee_id, last_name, hire_date
2 FROM employees
3 WHERE TO_CHAR(hire_date, ‘YYYY’) = ‘1997’;
8) 연산자 정리
= : 동일함(같음)을 테스트한다.
!=, <>, ^= : 동일하지 않음(같지 않음)을 테스트한다.
>=, >, <=, < : 큰 경우 혹은 크거나 같은 경우의 비교 테스트를 한다. 이와 반대의 경우도 적용된다.
AND : ‘A AND B’의 경우 두 조건 모두를 만족해야 한다.
OR : ‘A OR B’의 경우 두 조건 중 하나를 만족하는 경우이다.
BETWEEN AND : 주어진 범위의 값에 해당하면 그 값을 반환한다.
IN : 하나 이상의 동일 조건을 만족해야 하는 경우이다.
LIKE : 조건의 일부와 일치하는 경우에 적용된다.
IS NULL / IS NOT NULL : 할당되지 않은 값을 만족하는 경우이다. 할당된 값의 경우 NULL 앞에 NOT을 사용한다.
====================================================
2.3 ORDER BY
1) ORDER BY ASC
-> 때로는 데이터를 일정한 기준에 의하여 정렬하여 표현하면 더욱 쉽게 결과를 읽어들일 수 있는데 ORDER BY 절을 이용하면 가능하다.
ORDER BY 칼럼이름 [ASC/DESC]
칼럼 이름 뒤 대괄호([ ])는 선택사항이며, 기본은 오름차순 정렬인 ‘ASC’이다. ‘ASC’는 ‘Ascend(올라가다)’의 약자이므로, 작은 값부터 큰 값의 순서로 정렬된 결과를 보여준다. 참고로 ‘DESC’는 ‘Descend(내려가다)’의 약자이다.
SQL> SELECT employee_id, last_name, salary
2 FROM employees
3 WHERE salary >= 10000
4 ORDER BY salary;
ORDER BY는 WHERE 조건절을 만족하는 결과로부터 특정 칼럼의 값을 기준으로 정렬한 결과를 보내준다. WHERE 조건을 만족하는 결과를 먼저 얻은 후, 그 결과로부터 특정 칼럼(salary)을 기준으로 정렬하여 최종 결과를 보여준다.
2) ORDER BY DESC
SQL> SELECT employee_id, last_name, hire_date
2 FROM employees
3 WHERE hire_date >= ’01/Jan/00′
4 ORDER BY hire_date DESC;
EMPLOYEE_ID LAST_NAME HIRE_DATE
———– ————————- ———
173 Kumar 21-APR-00
167 Banda 21-APR-00
166 Ande 24-MAR-00
128 Markle 08-MAR-00
165 Lee 23-FEB-00
136 Philtanker 06-FEB-00
183 Geoni 03-FEB-00
149 Zlotkey 29-JAN-00
164 Marvins 24-JAN-00
199 Grant 13-JAN-00
179 Johnson 04-JAN-00
11 rows selected.
내림차순은 DESC(Descending) 키워드를 명시적으로 ORDER BY 절에서 나타내 주어야 한다.
====================================================
2.4 GROUP BY
1) GROUP BY를 이용한 그룹화
-> SQL에서는 GROUP BY 절과 집합 함수를 이용함으로써 데이터를 활용할 수 있다. GROUP BY 절은 일반적으로 질의문으로부터 얻은 결과에 대해 GROUP BY 절에 명시한 칼럼의 값이 같을 때 그룹을 만들고, 이 그룹으로부터 SQL 표준 함수인 집합 함수를 통해 다양한 결과를 얻는다.
SQL> SELECT AVG(salary)
2 FROM employees;
AVG(SALARY)
———–
6461.68224
대략 $6,462 정도를 얻을 수 있다. AVG() 함수는 하나 이상의 값들로부터 평균값을 반환한다. 그렇다면 $10,000 이상의 연봉자들의 평균은 얼마일까
SQL> SELECT AVG(salary)
2 FROM employees
3 WHERE salary >= 10000;
AVG(SALARY)
———–
12631.5789
이 경우는 WHERE절을 만족하는 행의 결과를 하나의 그룹으로 보고 평균을 얻은 경우이다.
그렇다면, 부서별 급여의 평균을 얻어보자. 부서별 평균은 각각의 부서마다 평균을 얻어야 하므로 같은 부서 단위로 그룹을 묶어 평균을 얻는다.
SQL> SELECT department_id, AVG(salary)
2 FROM employees
3 GROUP BY department_id
4 ORDER BY department_id;
DEPARTMENT_ID AVG(SALARY)
————- ———–
10 4400
20 9500
30 4150
40 6500
50 3475.55556
60 5760
70 10000
80 8955.88235
90 19333.3333
100 8600
110 10150
DEPARTMENT_ID AVG(SALARY)
————- ———–
7000
12 rows selected.
결과를 보면 부서 아이디가 오름차순 정렬되어 부서마다 해당 부서에 속한 사원의 평균 급여를 얻어내고 있다. 참고로 NULL 값(할당되지 않은 값)은 오름차순 정렬할 때 위와 같이 가장 나중에 나타난다.
다음의 조건을 만족하는 질의문을 작성해 보라.
요구사항 – 직무별로 최대 급여와 최소 급여를 살펴 보고자 한다. 단 직무는 영업 부서로 제한한다.
먼저 사원 테이블에서 고려해야 할 칼럼이 무엇인지 살펴보아야 한다. 그리고 영업 부서는 어떻게 표현되고 있는지 살펴보아야 한다. 영업 부서는 다시 영업 대표(SA_REP)와 이들의 관리자인 영업 관리자(SA_MIN)가 있다.
그럼 우선 대상이 되는 질의문을 작성한다.
SQL> SELECT job_id, salary
2 FROM employees;
이 결로부터 먼저 부서를 제한한다.
SQL> SELECT job_id, salary
2 FROM employees
3 WHERE job_id IN(‘SA_REP’, ‘SA_MAN’);
마지막으로 위 결로부터 급여의 최대값/최소값을 얻는다. 하나 이상의 행으로부터 최대값/최소값을 반환하는 MAX() / MIN() 함수를 사용한다.
SQL> SELECT job_id, MAX(salary), MIN(salary)
2 FROM employees
3 WHERE job_id IN(‘SA_REP’, ‘SA_MAN’)
4 GROUP BY job_id;
JOB_ID MAX(SALARY) MIN(SALARY)
———- ———– ———–
SA_MAN 14000 10500
SA_REP 11500 6100
2) GROUP BY 응용
-> 연도별 입자들의 평균은 얼마인지 나타내 보라.
SQL> SELECT hire_date, salary
2 FROM employees;
입사일(hire_date)을 연도로 변환한 후 GROUP BY를 이용하여 그룹화한다. TO_CHAR() 함수를 사용하면 날짜 형식의 데이터, 예를 들어 ’99/06/21’을 연도 4자리 데이터 ‘1999’로 변환할 수 있다.
SQL> SELECT hire_date, salary
2 FROM employees
3 GROUP BY TO_CHAR(hire_date, ‘YYYY’);
SELECT hire_date, salary
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression
위의 오류를 보면 SELECT 절의 hire_date의 칼럼이 GROUP BY의 표현이 아니라는 오류를 나타내고 있다. 이와 같은 오류는 GROUP BY 절을 사용할 때 SELECT절에 사용될 수 있는 칼럼이 제한적임을 말해준다. 즉, GROUP BY절에서 사용한 칼럼과 그룹 함수(예를 들면, 평균을 구하는 AVG() 함수나 최대 값을 MAX() 함수 등)만이 사용될 수 있다.
따라서, GROUP BY 식에서 사용한 칼럼이 TO_CHAR() 함수에 의해 값이 변형되었다면 변형된 형태 그대로 SELECT절에서 사용되어야 한다.
SQL> SELECT TO_CHAR(hire_date, ‘YYYY’), AVG(salary)
2 FROM employees
3 GROUP BY TO_CHAR(hire_date, ‘YYYY’);
TO_C AVG(SALARY)
—- ———–
2000 5381.81818
1987 14200
1997 6460.71429
1994 9828.57143
1991 6000
1995 4525
1990 9000
1989 17000
1999 4938.88889
1996 8600
1993 17000
TO_C AVG(SALARY)
—- ———–
1998 4873.91304
12 rows selected.
2.5 HAVING
1) HAVING절의 이용
-> 만약 처음 GROUP BY 수행 결과로부터 부서별 평균 급여가 $10,000 이상인 부서만을 얻고자 한다면 어떻게 해야 할까? GROUP BY 절의 결과로부터 특정 조건을 만족하는 값을 얻고자 한다면 HAVING절을 사용할 수 있다.
SQL> SELECT department_id, AVG(salary)
2 FROM employees
3 GROUP BY department_id
4 HAVING AVG(salary) >= 10000;
DEPARTMENT_ID AVG(SALARY)
————- ———–
70 10000
90 19333.3333
110 10150
HAVING절은 GROUP BY 절의 수행 결과를 대상으로 다시 조건을 만족하는 결과를 얻어온다.
2) 비효율적인 HAVING절
-> 아래 SQL문은 HAVING절이 비효율적으로 사용된 경우이다. 요구 조건은 부서별 최대 급여를 얻는 것이다.
SQL> SELECT department_id, MAX(salary)
2 FROM employees
3 GROUP BY department_id
4 HAVING department_id IN (10, 20);
DEPARTMENT_ID MAX(SALARY)
————- ———–
20 13000
10 4400
어떤 구문이 개선되어야 하나? SQL문은 원하는 결과를 얻기 위해 다양하게 작성될 수 있다. 따라서 하나의 SQL문이 답이 될 수 없다. SQL문에서는 무엇이 최선의 질의문이 될 수 있느냐가 데이터베이스를 이용하여 개발하는 여러분에게 가장 중요한 핵심이라 할 수 있다.
위의 실수는 어디에서 비롯되었는지 위 질의문의 수행 과정을 다시 한 번 생각해 보라. 두 가지 접근 방법을 생각해 볼 수 있다.
먼저 출발은 같은 질의문으로부터 시작한다.
SQL> SELECT department_id, salary
2 FROM employees;
그리고 앞서 수행한 방법은 이 조건을 만족하는 107행을 대상으로 GROUP BY 절을 수행하여 아래와 같은 결과를 얻었다.
SQL> SELECT department_id, MAX(salary)
2 FROM employees
3 GROUP BY department_id;
DEPARTMENT_ID MAX(SALARY)
————- ———–
100 12000
30 11000
7000
20 13000
70 10000
90 24000
110 12000
50 8200
40 6500
80 14000
10 4400
이 결과로부터 HAVING절에서 원하는 부서 아이디에 해당하는 결과를 얻고 있다.
또 다른 방법은 먼저 대상이 되는 부서 아이디를 WHERE절에서 제한하는 것이다.
SQL> SELECT department_id, salary
2 FROM employees
3 WHERE department_id IN (10, 20);
DEPARTMENT_ID SALARY
————- ———-
10 4400
20 13000
20 6000
앞의 방법은 107행에 대해 동일 부서 아이디에 대해 급여 칼럼의 값을 하나씩 비교해 가면서 최대 값을 찾는 과정인데 반해 여기서는 3행에 대해서만 수행하고 있다. 어느 것이 더 효율적인 접근 방법이겠는가?
Tags: ORACLE SQL, Oracle, sql
