[SQL] Advanced SQL - 기본적인 SELECT 명령문 작성

Advanced SQL - 기본적인 SELECT 명령문 작성

수정

(2019-01-04 수정)

※ SQL명령어 만드는 팁

첫 번째 : 어떤 테이블의 정보를 원하는지를 생각 할 것

-> FROM절에 있는 데이터만 화면에 출력할 수 있음

-> 검색 대상의 결과가 올라가는 곳이 FROM절 이라고 할 수 있음

두 번째 : WHERE절에 어떤 조건이 들어갈 지 생각 할 것

-> 속도를 감안 한다면, 테이블에 있는 컬럼의 데이터 타입을 확인 할 것

-> 'DESC 테이블' 로 컬럼의 데이터 타입을 확인할 수 있음

-> 보통 WHERE절에 항상 '1 = 1'이라는 무의미한 식을 넣고 나머지 조건을 AND로 붙힘

--> 서비스를 해 줄때, 처음에는 모든 항목을 보여 준 뒤에 필터링(AND조건)을 해 주기 때문

--> 추가되는 조건을 바로바로 넣기 위해서

-> WHERE절은 참/거짓 하나만 검증 하면 됨

세 번째 : 실행된 결과만을 보고 답이라고 생각하지 말것

-> 실제 업무 환경에서는 요구하는 바를 정확하게 알아야 함

-> 상황에 따라서 달라 짐

01. emp 테이블에서, 이름(ename)이 'S' 또는 'A'로 시작하는 사원을 검색 하시오.

해답 SQL문

-- 문자를 비교 한다면 LIKE를 사용

SELECT 

empno, ename, sal, deptno

FROM

emp 

WHERE

ename LIKE 'S%' 

OR 

ename LIKE 'A%';

-- 정규식을 사용 REGEXP_LIKE

SELECT 

    empno, ename, sal, deptno

FROM 

    emp 

WHERE 

    REGEXP_LIKE(ename, '^(S|A)');

-- '(S|A)'의 의미는 문자열의 패턴에 S 또는 A가 포함된 문자열을 검색

-- '^(S|A)'의 의미는 문자열의 첫 번째 문자가 S 또는 A인 문자열 검색

------ 오류가 나오는 SQL문 ------------------

SELECT *

empno, ename, sal, deptno

FROM 

emp

WHERE 

ename IN ('S%','A%');

-- %는 와일드카드가 아닌 문자로 인식 됨

-- 실행 결과 --

4 rows selected.

---

02. emp 테이블에서, 커미션(comm)을 받지 않는 모든 사원을 검색 하시오.

해답 SQL문

SELECT 

    empno, ename, sal, comm, deptno

FROM 

    emp

WHERE 

    comm IS NULL

OR 

    comm <= 0;

-- 실행 결과 --

11 rows selected.

---

03. dept, emp 테이블에서, 2000 이상의 급여(sal)를 받는 사원들의 소속 부서의 이름(dname)을 함께 검색 하시오. 단, 근무하는 사원이 없는 부서도 검색합니다.

- OUTER JOIN 수행할 때 주어진 조건에 의해서 OUTER JOIN에서 만들어진 값이 사라질 수 있음의 주의

해답 SQL문

-- ANSI-JOIN ------------------------------------------

SELECT 

    d.deptno "DEPT_DEPTNO", d.dname "DNAME", 

    e.deptno "EMP_DEPTNO",e.empno "EMPNO", 

    e.ename "ENAME", e.sal "SAL"

FROM 

    dept d LEFT OUTER JOIN emp e

ON  

    d.deptno = e.deptno

WHERE

    (e.sal >= 2000 OR e.deptno IS NULL);

-- 식이 아래와 같이 만들어지면 OUTER JOIN의 값이 나오지 않음

SELECT 

    d.deptno "DEPT_DEPTNO", d.dname "DNAME", 

    e.deptno "EMP_DEPTNO",e.empno "EMPNO", 

    e.ename "ENAME", e.sal "SAL"

FROM 

    dept d LEFT OUTER JOIN emp e

ON  

    d.deptno = e.deptno

WHERE

    (e.sal >= 2000 OR e.deptno IS NULL);

-- 실제로 많이 사용되는 OUTER JOIN

-- emp테이블의 조건을 동시에 줌

SELECT 

    d.deptno "DEPT_DEPTNO", d.dname "DNAME", 

    e.deptno "EMP_DEPTNO",e.empno "EMPNO", 

    e.ename "ENAME", e.sal "SAL"

FROM 

    dept d LEFT OUTER JOIN emp e

ON  

    d.deptno = e.deptno

AND

    e.sal >= 2000;

-- ORACLE-JOIN --------------------------------------

SELECT 

    d.deptno "DEPT_DEPTNO", d.dname "DNAME", 

    e.deptno "EMP_DEPTNO",e.empno "EMPNO", 

    e.ename "ENAME", e.sal "SAL"

FROM 

    dept d, emp e 

WHERE  

    d.deptno = e.deptno(+)

    -- 40번 부서의 정보는 dept에 있으므로

    -- 정보가 데이터가 부족한 emp에 (+)를 부여

AND 

    (e.sal >= 2000 OR e.deptno IS NULL);

-- 실행 결과 --

7 rows selected.

---

04. emp 테이블에서, 'JONES' (ename)보다 더 많은 급여(sal)를 받는 사원을 검색 하시오. 

     단, JONES의 급여도 함께 검색합니다.

해답 SQL문

-- ANSI-JOIN -----------------------------------------------------------------------

SELECT

    a.empno, a.ename, 

    a.sal, b.sal "Jones's Salary"

FROM

    emp a JOIN (SELECT sal FROM emp WHERE ename = 'JONES') b

ON

    a.sal > b.sal;

-- Self JOIN

SELECT

    E.empno, E.ename, 

    E.sal, J.sal "Jones's Salary"

FROM emp E JOIN emp J

ON J.ename = 'JONES'

AND E.sal > J.sal;

-- WITH 절을 이용한 JOIN문

WITH JONES AS (SELECT * FROM emp WHERE ename = 'JONES')

SELECT 

    E.empno, E.ename, 

    E.sal, J.sal "Jones's Salary"

FROM emp E JOIN JONES J

ON E.sal > J.sal;

-- ORACLE-JOIN ------------------------------------------------------------------

SELECT 

    a.empno, a.ename, 

    a.sal, (SELECT sal FROM emp WHERE ename = 'JONES') "Jones's Salary"

FROM 

    emp a, emp b

WHERE 

    a.empno(+) = b.empno

AND 

    a.sal > (SELECT sal FROM emp WHERE ename = 'JONES');

-- 실행 결과 --

3 rows selected.

---

05. departments, employees 테이블에서, 근무하는 사원이 없는 부서 정보를 검색 하시오.

해답 SQL문

-- EXISTS사용

SELECT * 

FROM departments D

WHERE 

    NOT EXISTS (

        SELECT * 

        FROM employees

        WHERE department_id = D.department_id );

-- 존재 유/무를 따지기 위해서 라면 EXISTS와 NOT EXISTS를 적절히 사용하면 됨

-- Subquery에 나오는 결과가 하나라도 NULL이라면 거기서 조회를 멈추기 때문에 더 효율 적임

-- custs 테이블에서 한 번도 주문 내역이 없는 즉, sales 테이블에 없는 고객을 검색할 때

SELECT *

FROM custs C

WHERE NOT EXISTS (

        SELECT *

        FROM sales

        WHERE cust_id = C.cust_id );

-- 실행 결과 --

1 rows selected.

---

06. emp 테이블에서, 커미션(comm)을 기준으로 내림차순 정렬된 결과를 검색 하시오. 

     단, 커미션이 NULL인 행은 마지막에 검색되도록 한다.

해답 SQL문

SELECT 

    empno, ename, sal, comm

FROM 

    emp

ORDER BY 

    comm DESC NULLS LAST;

-- 실행 결과 --

14 rows selected.

---

07. emp 테이블에서, 메니저(mgr)가 없는 사원은 'NO MANAGER'의 문자로 대채하여 검색 하시오.

해답 SQL문

SELECT 

    empno, ename, job, 

    NVL(TO_CHAR(mgr), 'NO Manager') mgr

FROM emp;

-- 실행 결과 --

14 rows selected.