[SQL] Oracle, SELECT 문

1. 데이터를 조회하는 방법

• SELECTION: 행단위로 조회
• PROJECTION: 열단위로 조회
• JOIN: 2개 이상의 테이블을 연결해서 조회

SELECT * 
FROM EMP;

SELECT EMPNO, ENAME, DEPTNO
FROM EMP;

 

2. 중복 데이터를 삭제: DISTINCT

SELECT DISTINCT DEPTNO
FROM EMP;

SELECT DISTINCT JOB, DEPTNO
FROM EMP;

3. 별칭 사용: AS

SELECT ENAME, SAL, SAL*12+COMM AS ANNSAL, COMM
FROM EMP;
//실무에서 가장 선호

SELECT ENAME, SAL, SAL*12+COMM AS "ANNSAL", COMM
FROM EMP;

 

4. ORDER BY

SELECT * 
FROM EMP
ORDER BY SAL;

SELECT * 
FROM EMP
ORDER BY SAL DESC;

SELECT * 
FROM EMP
ORDER BY DEPTNO ASC, SAL DESC;
//ORDER BY 작업은 많은 비용을 소모한다. 따라서 필요한 경우가 아니라면 ORDER BY절은 하지 않는 것이 좋다.

 

5. 종합 예제1

SELECT  EMPNO AS EMPLLOYEE_NO
        , ENAME AS EMPLOYEE_NAME
        , MGR AS MANAGER
        , SAL AS SALARY
        , COMM AS COMMISSION
        , DEPTNO AS DEPARTMENT_NO 
FROM EMP
ORDER BY DEPTNO DESC, ENAME ASC;

---

6. WHERE

SELECT *
FROM EMP
WHERE DEPTNO = 30;

 

7. AND, OR

SELECT *
FROM EMP
WHERE DEPTNO = 30
    AND JOB = 'SALESMAN';
    
SELECT *
FROM EMP
WHERE DEPTNO = 30
    OR JOB = 'CLERK';

 

8. 산술, 비교 연산자

SELECT *
FROM EMP
WHERE SAL* 12 = 36000;
// 산술연산자

SELECT *
FROM EMP
WHERE SAL>=3000;
//비교연산자

SELECT *
FROM EMP
WHERE ENAME>='F';
//문자를 비교연산자로 비교하기1

SELECT *
FROM EMP
WHERE ENAME<='FORZ';
//문자를 비교연산자로 비교하기2

SELECT *
FROM EMP
WHERE SAL!=3000;
//등가 비교연산자

 

9. 논리 부정 연산자: NOT

SELECT *
FROM EMP
WHERE NOT SAL = 3000;
//논리 부정 연산자, 위와 동일한 결과
//NOT은 보통 IN, BETWEEN, IS NULL과 함께 많이 쓰인다.

 

10. IN

SELECT *
FROM EMP
WHERE JOB IN ('MANAGER', 'SALESMAN', 'CLERK');

SELECT *
FROM EMP
WHERE JOB NOT IN ('MANAGER', 'SALESMAN', 'CLERK');

SELECT *
FROM EMP
WHERE DEPTNO IN (10, 20);

 

11. BETWEEN A AND B

SELECT *
FROM EMP
WHERE SAL BETWEEN 2000 AND 3000;

SELECT *
FROM EMP
WHERE SAL NOT BETWEEN 2000 AND 3000;

 

12. LIKE

SELECT *
FROM EMP
WHERE ENAME LIKE 'S%';
//%: 길이와 상관없이 모든 문자 데이터를 의미

SELECT *
FROM EMP
WHERE ENAME LIKE '_L%';
//_: 어떤 값이든 상관없이 한 개의 문자 데이터를 의미

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

SELECT *
FROM EMP
WHERE ENAME NOT LIKE '%AM%';

SELECT *
FROM SOME_TABLE
WHERE SOME_COLUMN LIKE 'A\_A%' ESCAPE '\';
//ESCAPE: A_A 문자를 가지고 있는 데이터를 찾을 시 사용, \ 외 다른 문자 지정 가능

 

13. IS NULL

SELECT *
FROM EMP
WHERE COMM IS NULL;

SELECT *
FROM EMP
WHERE MGR IS NOT NULL;

 

14. 집합 연산자

UNION(중복제거), UNION ALL(중복포함), MINUS, INTERSECT

SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP
WHERE DEPTNO = 10
UNION
SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP
WHERE DEPTNO = 20;

SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP
MINUS
SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP
WHERE DEPTNO = 10;

SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP
INTERSECT
SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP
WHERE DEPTNO = 10;

 

15. 종합 예제2

--1
SELECT *
FROM EMP
WHERE ENAME LIKE '%S';

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

--3
SELECT EMPNO, ENAME, JOB, SAL, DEPTNO
FROM EMP
WHERE DEPTNO IN (20, 30) 
    AND SAL > 2000;

--4
SELECT *
FROM EMP
WHERE SAL<2000 OR SAL>3000;

--5
SELECT ENAME, EMPNO, SAL, DEPTNO
FROM EMP
WHERE ENAME LIKE '%E%' 
    AND DEPTNO = 30 
    AND SAL NOT BETWEEN 1000 AND 2000;

--6
SELECT *
FROM EMP
WHERE COMM IS NULL
    AND MGR IS NOT NULL
    AND JOB IN ('MANAGER', 'CLERK')
    AND ENAME NOT LIKE '_L%';