SQL 아침 복습 자료 문제 풀이_2

 

 

* 검색하고자 하는 내용 :

직급코드가 J7이거나 J6이면서 SALARY 값이 200만원 이상이고

BONUS가 있고 여자이며 이메일주소는 _앞에 3글자만 있는

사원의 사원명, 주민등록번호, 직급코드, 부서코드, 급여, 보너스를 조회하고자 한다.

(정상적으로 조회가 된다면 2개의 결과를 확인할 수 있음)

 

아래 쿼리문에서 실행 시 원하는 결과가 나오지 않는다. 

어떤 문제가 있는 지 원인을 파악하여 작성한 후 해결 

 

-- 문제 쿼리문--

SELECT EMP_NAME, EMP_NO, JOB_CODE, DEPT_CODE, SALARY, BONUS

FROM EMPLOYEE WHERE JOB_CODE = 'J7' OR JOB_CODE = 'J6' AND SALARY > 2000000

AND EMAIL LIKE '____%' AND BONUS IS NULL;

 

-- 문제해결 --

SELECT EMP_NAME, EMP_NO, JOB_CODE, DEPT_CODE, SALARY, BONUS

FROM EMPLOYEE

WHERE (JOB_CODE = 'J7' OR JOB_CODE = 'J6')  -- JOB_CODE IN ('J7', 'J6')

AND SALARY >= 2000000 AND EMAIL LIKE '___#_%' ESCAPE '#' AND BONUS IS NOT NULL AND SUBSTR(EMP_NO, 8, 1) IN (2, 4);

 

원인 : 

1)

직급코드에 대한 조건이 우선순위로 인해 제대로 실행되지 않음

=> 괄호로 묶어주거나 IN 연산자로 대체해야 함

 

2)

작성된 조건은 SALARY 값이 200만원 초과(>)로 작성되어 있음

=> 200만원 이상인 조건은 등호를 추가해줘야함 (>=)

3)

이메일의 4번째자리에 언더바(_)가 와일드카드로 사용됨 =>

값으로써 조건을 주고자 한다면 ESCAPE를 사용하여 나만의 와일드카드로 구분해줘야 함

 

4)

보너스가 있는 사원을 조회해야하는데 없는 사원을 조회하고 있음

=> BONUS IS NULL --> BONUS IS NOT NULL로 변경해줘야 함 5) 여자 사원 조건이 누락됨 => 여자 사원 조건을 추가해줘야 함

 

 

 

 CREATE TABLE STUDENT (
                ST_NO NUMBER,
                ST_NAME VARCHAR2(100) NOT NULL,
                EMAIL VARCHAR2(100) UNIQUE,     -- 컬럼레벨방식
                BIRTH DATE NOT NULL

                        -- , UNIQUE(EMAIL) -- 테이블레벨방식
        );

        COMMENT ON COLUMN STUDENT.ST_NO IS '학생 번호';
        COMMENT ON COLUMN STUDENT.ST_NAME IS '학생 이름';
        COMMENT ON COLUMN STUDENT.EMAIL IS '이메일';
        COMMENT ON COLUMN STUDENT.BIRTH IS '생년월일';

        INSERT INTO STUDENT
        VALUES (1, '기다운', 'KIDAWUN@KH.OR.KR', '95/04/01');

        INSERT INTO STUDENT
        VALUES (2, NULL, 'INCHANG@KH.OR.KR', NULL); --> ST_NAME 컬럼이 NOT NULL 제약에 위배됨!

                INSERT INTO STUDENT
        VALUES (3, '조건웅', 'KIDAWUN@KH.OR.KR', '99/09/28'); --> EMAIL 컬럼이 UNIQUE 제약에 위배됨!
                ------------------------------------------------------------
/*
    - 도서 정보 테이블 : BOOK
    - 제약 조건
      - 제목과 저자명은 NULL값을 허용하지 않는다.
      - ISBN 번호는 중복을 허용하지 않는다.
    - 저장 데이터
      + 도서 번호 ex) 1, 2, 3, ...
      + 제목 ex) '삼국지', '어린왕자', '코스모스', ...
      + 저자 ex) '지연', '생텍쥐페리', '칼 세이건', ...
      + 출판일 ex) '14/02/14', '22/09/19', ...
      + ISBN번호 ex) '9780394502946', '9780152048044', '978-0152048044', ...
*/
                        CREATE TABLE BOOK (
                BK_NO NUMBER,
                BK_TITLE VARCHAR2(200) NOT NULL,
        BK_AUTHOR VARCHAR2(200) NOT NULL,
        PUB_DATE DATE,
        ISBN VARCHAR2(20) CONSTRAINT ISBN_UQ UNIQUE
);

        COMMENT ON COLUMN BOOK.BK_NO IS '도서 번호';
        COMMENT ON COLUMN BOOK.BK_TITLE IS '도서 제목';
        COMMENT ON COLUMN BOOK.BK_AUTHOR IS '저자명';
        COMMENT ON COLUMN BOOK.PUB_DATE IS '출판일';
        COMMENT ON COLUMN BOOK.ISBN IS '일련번호(ISBN)';

        INSERT INTO BOOK
        VALUES (1, '어린왕자', '생텍쥐페리', '24/07/25', '9780152048044');

        INSERT INTO BOOK
        VALUES (2, '어린왕자2', '생텍쥐페리', '34/07/25', '9780152048044');
        --> ISBN컬럼이 UNIQUE 제약에 위배됨. 오류 내용에 설정한 제약조건명으로 표시됨!
                -- COMMIT;      -- 데이터 저장
                -- ROLLBACK;    -- 데이터 저장하지 않음