CHAPTER 05. 더 정확하고 다양하게 결과를 출력하는 WHERE절과 연산자-DO IT! 오라클로 배우는 데이터베이스 입문

CHAPTER 05. 더 정확하고 다양하게 결과를 출력하는 WHERE절과 연산자

05-1. 필요한 데이터만 쏙 출력하는 WHERE절

WHERE절은 SELECT문으로 데이터를 조회할때 특정 조건을 기준으로 원하는 행을 출력하는데 사용.여러 연산자를 함께 사용하면 더욱 세밀한 데이터 검색이 가능.

SELECT절에 *기호를 사용함으로써 모든 데이터 조회가 가능함.

05-2. 여러 개 조건식을 사용하는 AND,OR연산자

1. WHERE절에서 비교하는 데이터가 문자열일 경우에는 작은 따옴표(’ ‘)로 묶어줌.—>앞뒤에 공백이 있으면 문자로 인식하기 때문에 주의
2. SQL의 기본 형식은 대소문자를 구별 하지 않고 사용이 가능—>하지만 테이블 안에 들어있는 문자 또는 문자열 데이터는 대소문자를 구별
3. AND연산자를 사용하면 조건 식의 결과 값이 모두 TRUE인 행만 출력
4. OR연산자는 피 연산자가 둘 다 또는 둘 중 하나 TRUE이면 결과 값이 TRUE가 됨

5-3. 연산자 종류와 활용 방법 알아보기

산술 연산자

더하기(+), 빼기(-), 곱하기(*), 나누기(/) 같은 수치 연산에 사용

나머지 연산자는 SQL에서 지원하지 않음

다만 oracle에서는 mod함수를 통해 나머지 연산 같은 기능을 사용할 수 있음

비교 연산자

• 대소 비교 연산자→비교 대상인 데이터가 숫자가 아닌 문자열일 때, 알파벳 순서로 문자열의 대소를 비교

연산자 사용법 설명

>	A>B	A값이 B값을 초과할 경우 TRUE
≥	A≥B	A값이 B값 이상일 경우 TRUE
<	A<B	A값이 B값 미만일 경우 TRUE
≤	A≤B	A값이 B값 이하일 경우 TRUE

• 등가 비교 연산자

등가 비교 연산자는 연산자의 양쪽 항목 값이 같으면 TRUE가 반환 됨

이와는 반대로 연산자 양쪽 값이 다를 경우 TRUE를 반환하는 연산자도 존재

연산자 사용법 의미

=	A=B	A값이 B값과 같을 경우 true, 다를경우 false를 반환
≠,<>,^=	A≠B A<>B A^=B	A값이 B값과 다를 경우 true, 같을경우 false를 반환

논리 부정 연산자

NOT연산자를 적용하면 TRUE, FALSE 로 나오는 최종 결과의 반대 값을 반환

IN연산자 앞에 논리 부정 연산자 NOT 을 사용하면 좀 더 간단하게 반대 경우를 조회할 수 있음.

Between A and B 연산자

특정 열 값의 최소, 최고 범위를 지정하여 해당 범위 내의 데이터만 조회할 경우에 대소 비교 연산자 대신 BETWEEN A AND B연산자를 사용하면 더 간단하게 표현할 수 있음.

열 이름 BETWEEN 최솟값 AND 최댓값

LIKE 연산자와 와일드 카드

와일드 카드(%):특정 문자열을 대체하거나 문자열 데이터의 패턴을 표기하는 특수 문자

• LIKE연산자와 함께 사용할 수 있는 와일드 카드는 _와 %
  • • : 어떤 값이든 상관없이 한 개의 문자 데이터를 의미
  • % : 길이와 상관없이(문자 없는 경우도 포함) 모든 문자 데이터를 의미EX) ENAME LIKE NOT LIKE ‘%AM%’→AM이라는 단어가 포함된 데이터를 제외한 결과를 얻고자 할 경우
  • EX) ENAME LIKE ‘_L%’→두 번째 글자가 L인 사원만 출력

와일드 카드 문자가 데이터의 일부일 경우

_문자나 %문자를 포함한 데이터를 조회하기 위해서는 와일드 카드 문자를 쓰는 것은 애매

ESCAPE절을 사용하면 _,%를 와일드 카드 기호가 아닌 데이터 문자로 다루는 것이 가능

EX) SOME_COLUMN LIKE ‘A\_A%’ ESCAPE ‘\’→A\_A%에서 \문자 바로 뒤에 있는 _는 와일드 카드 기호로서가 아닌 데이터에 포함된 문자로 인식하라는 의미

ESCAPE문자를 지정하는 방식은 실무에서 그리 자주 사용하지는 않음

IS NULL연산자

NULL:데이터 값이 완전히 ‘비어있는’ 상태, 값이 존재하지 않음, 해당 사항 없음, 노출할 수 없는 값, 확정되지 않은 값

IS NOT NULL: NULL이 아닌 데이터를 조회할 때 쓰임

집합 연산자

1. 집합 연산자로 두 개의 SELECT문의 결과 값을 연결할 떼 각 SELECT문이 출력하려는 열 개수와 각 열의 자료형이 순서 별로 일치해야 함.
2. 최종 출력 되는 열 이름은 먼저 작성한 SELECT문의 열 이름으로 표기

집합 연산자 4종류

• UNION:연결된 SELECT문의 결과 값을 합집합으로 묶어줌. 결과 값의 중복은 제거됨
• UNION ALL:연결된 SELECT문의 결과 값을 합집합으로 묶어 줌. 중복된 결과 값도 제거 없이 모두 출력 됨.
• MINUS: 먼저 작성한 SELECT문의 결과 값에서 다음 SELECT문의 결과 값을 차집합 처리함.먼저 작성한 SELECT문의 결과 값 중 다음 SELECT문에 존재하지 않은 데이터만 출력 됨
• INTERSECT: 먼저 작성한 SELECT문과 다음 SELECT문의 결과 값이 같은 데이터만 출력 됨. 교집합과 같은 의미

*GROUP함수(다중행 함수)

• AVG,SUM: 숫자
• MIN,MAX,COUNT:숫자,날짜 문자

EMPNO SAL

7777	100
7666	200
7888	300
7111	NULL
7222	400

SELECT COUNT(*) FROM EMP; --5
COUNT(SAL) -- 4건
SUM(SAL) -- 1000
AVG(SAL) -- 250
MIN(SAL) -- 100
MAX(SAL) -- 400

SELECT AVG(NVL(SAL,0)) FROM EMP; --(100+200+300+NULL(0)+400)/5->200

*GROUP BY

그룹대상 칼럼 명시, ALIAS사용?X

*HAVING

GROUP결과를 제한

<실행 순서>
SELECT   --5
FROM   --1
WHERE   --2
GROUP BY   --3
HAVING   --4
ORDER BY   --1

*집합 연산자

UNION ALL: 합집합,중복제거안함

UNION: 합집합, 중복제거,첫번째 칼럼 기준 자동 ORDER BY

MINUS: 차집합, 중복제거,자동으로 ORDER BY

INTERSECT: 교집합

SELECT ENAME FROM EMP
UNION
SELECT DNAME FROM DEPT;

UNION ALL과 UNION결과가 같다면 성능상 UNION ALL을 사용하는 것이 좋음.

*조인

• EQUI JOIN: =(등호) 사용
• NON EQUI JOIN: 부등호, BETWEEN A AND B 사용
• SELF JOIN: 자기 자신 복제
• OUTER JOIN

*ANSI표준(SQL-99)

SELECT E.ENAME,D.DNAME
FROM EMP E,DEPT D
WHERE E.DEPTNO=D.DEPTNO;

-- ANSI표준(SQL-99)
SELECT E.ENAME,D.DNAME
FROM EMP E JOIN DEPT D
ON E.DEPTNO=D.DEPTNO;

--NATURAL JOIN(공통된 칼럼을 ORACLE스스로 찾아서 조인)
SELECT E.ENAME,D.DNAME
FROM EMP E NATURAL JOIN DEPT D

-- USING
SELECT E.ENAME,D.DNAME
FROM EMP E JOIN DEPT D
USING DEPTNO;

OUTER JOIN

EMPNO ENAME DEPTNO

101	ALICE	10
102	MARY	20
305	SCOTT	10
306	JANE	30
405	TED

DEPTNO DEPT_NAME

10	인사
20	재경
30	기획
50	총무

INNER JOIN 결과

SELECT E.EMPNO,E.ENAME,D.DEPT_NAME
FROM EMP E,DEPT D
WHERE E.DEPTNO=D.DEPTNO;

EMPNO ENAME DEPTNO DEPTNO DEPT_NAME

101	ALICE	10	10	인사
102	MARY	20	20	재경
305	SCOTT	10	10	인사
306	JANE	30	30	기획

LEFT OUTER JOIN결과

SELECT E.EMPNO,E.ENAME,D.DEPT_NAME
FROM EMP E,DEPT D
WHERE E.DEPTNO=D.DEPTNO(+);
부서 할당 안된 직원정보까지 출력

EMPNO ENAME DEPTNO DEPTNO DEPT_NAME

101	ALICE	10	10	인사
102	MARY	20	20	재경
305	SCOTT	10	10	인사
306	JANE	30	30	기획
405	TED	NULL	NULL	NULL

-- left outer join
SELECT E.LAST_NAME,E.DEPARTMENT_ID,D.DEPARTMENT_NAME
FROM EMPLOYEES E,DEPARTMENTS D
where E.DEPARTMENT_ID=D.DEPARTMENT_ID(+);

-- ANSI 표준 left outer join
SELECT E.LAST_NAME,E.DEPARTMENT_ID,D.DEPARTMENT_NAME
FROM EMPLOYEES E LEFT OUTER JOIN DEPARTMENTS D
ON (E.DEPARTMENT_ID=D.DEPARTMENT_ID);

RIGHT OUTER JOIN결과

SELECT E.EMPNO,E.ENAME,D.DEPT_NAME
FROM EMP E,DEPT D
WHERE E.DEPTNO(+)=D.DEPTNO;
직원 할당 안된 직원정보까지 출력

EMPNO ENAME DEPTNO DEPTNO DEPT_NAME

101	ALICE	10	10	인사
102	MARY	20	20	재경
305	SCOTT	10	10	인사
306	JANE	30	30	기획
NULL	NULL	NULL	50	총무

-- RIGHT OUTER JOIN
SELECT E.LAST_NAME,E.DEPARTMENT_ID,D.DEPARTMENT_NAME
FROM EMPLOYEES E,DEPARTMENTS D
where E.DEPARTMENT_ID(+)=D.DEPARTMENT_ID;

-- ANSI표준 RIGHT OUTER JOIN
SELECT E.LAST_NAME,E.DEPARTMENT_ID,D.DEPARTMENT_NAME
FROM EMPLOYEES E RIGHT OUTER JOIN DEPARTMENTS D
ON (E.DEPARTMENT_ID=D.DEPARTMENT_ID);

FULL OUTER JOIN결과

SELECT E.EMPNO,E.ENAME,D.DEPT_NAME
FROM EMP E,DEPT D
WHERE E.DEPTNO(+)=D.DEPTNO
UNION
SELECT E.EMPNO,E.ENAME,D.DEPT_NAME
FROM EMP E,DEPT D
WHERE E.DEPTNO=D.DEPTNO(+);

EMPNO ENAME DEPTNO DEPTNO DEPT_NAME

101	ALICE	10	10	인사
102	MARY	20	20	재경
305	SCOTT	10	10	인사
306	JANE	30	30	기획
NULL	NULL	NULL	50	총무
405	TED	NULL	NUL	NULL

-- ANSI표준 FULL OUTER JOIN
SELECT E.LAST_NAME,D.DEPARTMENT_ID,D.DEPARTMENT_NAME
FROM EMPLOYEES E FULL OUTER JOIN DEPARTMENTS D
ON (E.DEPARTMENT_ID=D.DEPARTMENT_ID);

-- FULL outer join
SELECT E.LAST_NAME,E.DEPARTMENT_ID,D.DEPARTMENT_NAME
FROM EMPLOYEES E,DEPARTMENTS D
where E.DEPARTMENT_ID(+)=d.department_id
UNION
SELECT E.LAST_NAME,E.DEPARTMENT_ID,D.DEPARTMENT_NAME
FROM EMPLOYEES E,DEPARTMENTS D
where E.DEPARTMENT_ID=D.DEPARTMENT_ID(+);

CARTESIAN PRODUCT- 모든 조합 출력

SELECT LAST_NAME,DEPARTMENT_NAME
FROM EMPLOYEES
CROSS JOIN DEPARTMENTS;

 

잊기 전에 한번 더!

 

Q1. EMP테이블을 사용하여 다음과 같은 사원 이름 (ENAME)이 S로 끝나는 사원 데이터를 모두 출력하는 SQL문을 작성해 보세요.

SELECT *
FROM EMP
WHERE ENAME LIKE '%s';

Q2. EMP테이블을 사용하여 30번 부서(DEPTNO)에서 근무하고 있는 사원 중에 직책(JOB)이 SALESMAN인 사원의 사원 번호, 이름, 직책, 급여, 부서 번호를 출력하는 SQL문을 작성해 보세요.

SELECT EMPNO,ENAME,JOB,SAL,DEPTNO
FROM EMP
WHERE DEPTNO=30 AND JOB=SALESMAN;

Q3. EMP테이블을 사용하여 20번, 30번 부서에 근무하고 있는 사원 중 급여(SAL)가 2000초과인 사원을 다음 두 가지 방식의 SELECT문을 사용하여 사원 번호,이름 , 급여, 부서 번호를 출력하는 SQL문을 작성해보세요.

-- 집합 연산자를 사용하지 않은 방식
SELECT EMPNO,ENAME,JOB,SAL,DEPTNO
FROM EMP
WHERE DEPTNO IN (20,30) AND SAL>2000;

-- 집합 연산자를 사용한 방식
SELECT EMPNO,ENAME,JOB,SAL,DEPTNO
FROM EMP
WHERE DEPTNO = 20 AND SAL>2000
UNION
SELECT EMPNO,ENAME,JOB,SAL,DEPTNO
FROM EMP
WHERE DEPTNO = 30 AND SAL>2000;

Q4. 이번에는 NOT BETWEEN A AND B연산자를 쓰지 않고, 급여(SAL)열 값이 2000이상 3000이하 범위 이외의 값을 갖진 데이터만 출력하도록 SQL문을 작성해 보세요.

SELECT *
FROM XMP
WHERE SAL<2000 OR SAL>3000;

Q5. 사원 이름에 E가 포함되어있는 30번 부서의 사원 중 급여가 1000~2000사이가 아닌 사원 이름,급여,부서 번호를 출력하는 SQL문을 작성해보세요.

SELECT ENAME,EMPNO,SAL,DEPTNO
FROM EMP
WHERE (SAL NOT BETWEEN 1000 AND 2000) AND ENAME LIKE '%E%';

Q6. 추가 수당이 존재하지 않고 상급자가 있고 직책이 MANAGER,CLERK인 사원 중에서 사원 이름 의 두 번째 글자가 L이 아닌 사원의 정보를 출력하는 SQL문을 작성해보세요.

SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
FROM EMP
WHERE COMMISSION_PCT IN NULL AND JOB IN (CLERK,MANAGER) AND EMANE NOT LIKE '_L%';

====================

실습

====================

--최고급여가 10000달러가 넘는 부서에 대해 부서번호와 최고 급여를 표시
select department_id, max(salary)
from employees
HAVING AVG(SALARY)>10000
group by department_id;

-- EMP에서 부서번호가 있고, 부서변 근무 인원수가 3명 이상인 행을 검색하여
-- 부서번호 ,부서별 총급여와 평균급여를 검색,부서번호로 정렬
select deptno,sum(sal),round(avg(sal),2)
from emp
where deptno is not null
group by deptno
having  count(*)>=3
order by deptno;

실행 순서 : from-where-group by-having-select-order by

모든 사원의 MAX(SALARY), MIN(SALARY), SUM(SALARY) 및 AVG(SALARY) 급여를 검색하세요. 
열 레이블을 각각 Maximum, Minimum, Sum 및 Average로 지정합니다. 결과를 가장 가까운 정수로 반올림합니다.
●	테이블	: employees 

SELECT
MAX(SALARY) Maximum,
MIN(SALARY) Minimum,
SUM(SALARY) Sum,
ROUND(AVG(SALARY)) Average
FROM EMPLOYEES;

JOB_ID가 같은 행을 묶어서 MAX(SALARY), MIN(SALARY), SUM(SALARY) 및 AVG(SALARY)를 검색합니다. 
●	테이블	: employees 

Select
JOB_ID,
max(salary) Maximum,
min(salary) Minimum,
sum(salary) Sum ,
round(avg(salary)) Average
From employees
Group by job_id;

-- 동일한 직무를 수행하는 사람 수를 표시하기 위한 query를 작성합니다. 
-- 테이블	: employees 

select JOB_ID,count(*)
from employees
group by JOB_ID;

-- MANAGER_ID의 구분값(중복을 제거한 행 개수)을 검색하세요. 열 레이블을 Number of Managers로 지정합니다.
-- 테이블	: employees 
select  count(distinct manager_id)
from employees;

-- MANAGER_ID별 최소 급여를 검색합니다. 
-- MANAGER_ID가 NULL인 행을 제외하고, 최소 급여가 6000 이하인 그룹을 제외합니다. 최소 급여를 내림차순으로 정렬합니다.
-- 테이블	: employees 

select manager_id,min(salary)
from employees
where manager_id is not null
group by manager_id
having min(salary)>6000
order by min(salary) desc;