KH 43일차 - SQL문의 기본 연산자

-- ***************************************

--  SELECT 문의 기본 문법 (***)

-- ***************************************

--  SELECT [DISTINCT] { *, column [AS] [alias] , ... }

--  FROM <테이블명>

--  WHERE <predicates(조건)>

-- ***************************************

---

-- -------------------------------------------

-- 1. 논리 연산자 ( AND, OR )

-- -------------------------------------------

SELECT

    last_name AS "이름",

    job_id AS "부서",

    salary AS "월급"

FROM

    employees

WHERE

    job_id = 'IT_PROG'

    AND salary >= 5000;

-- IT개발자이며, 월급이 5000이상인 직원의 이름, 부서, 월급을 출력해라.

-- AND는 조건식이 모두 참이어야 출력한다.

SELECT

    last_name AS "이름",

    job_id AS "부서",

    salary AS "월급"

FROM

    employees

WHERE

    job_id = 'IT_PROG'

    OR salary >= 5000;

-- IT개발자이거나, 월급이 5000이상인 직원의 이름, 부서, 월급을 출력해라.

-- OR는 조건식 중 하나라도 참이면 출력한다.

 

---

-- -------------------------------------------

-- 2. 논리 연산자 ( NOT )

-- -------------------------------------------

-- 1 ) NOT 연산자

SELECT

    last_name,

    job_id,

    salary

FROM

    employees

WHERE

    NOT salary < 20000;

-- 직원 중 월급이 20000미만이 아닌 직원을 모두 출력한다.

SELECT

    last_name,

    job_id,

    salary

FROM

    employees

WHERE

    NOT (salary < 20000 );

-- 소괄호()를 사용해도 동일한 결과를 출력한다.

-- 소괄호()는 조건식의 우선순위를 정하는 것이다.

-- 2 ) NOT IN 연산자

SELECT

    last_name,

    job_id,

    salary

FROM

    employees

WHERE

    salary NOT IN ( 9000, 8000, 6000 );

-- 직원중 월급이 9000, 8000, 6000이 아닌 직원은 모두 출력한다.

SELECT

    last_name,

    job_id,

    salary

FROM

    employees

WHERE

    NOT( salary IN ( 9000, 8000, 6000 ) );

-- 직원중 월급이 9000, 8000, 6000이 아닌 직원은 모두 출력하여 동일한 결과를 출력한다.

-- 소괄호()를 사용하여 경계선을 명확하게 하거나 우선순위를 정할 수 있다.

-- 가독성 측에서는 이번 SQL문이 좋지만, 실무에서는 NOT IN을 많이 사용한다.

-- 3 ) NOT LIKE 연산자

SELECT

    last_name,

    job_id,

    salary

FROM

    employees

WHERE

    last_name NOT LIKE 'J%';

-- 직원 중 이름이 J로 시작하지 않는 직원을 모두 출력한다.

-- NOT의 위치는 last_name의 앞에 위치해도 괜찮다.

-- 4 ) NOT BETWEEN A AND B 연산자

-- NOT ( a<= x <= b ) --> ( x < a, x > b )

SELECT

    last_name,

    job_id,

    salary

FROM

    employees

WHERE

    salary NOT BETWEEN 2400 AND 20000;

-- 직원 중 월급이 2400이상 20000이하가 아닌 직원들을 모두 출력한다.

-- 월급이 2400미만, 20000초과인 직원들을 출력하게 된다.

SELECT

    last_name,

    job_id,

    salary

FROM

    employees

WHERE

    NOT (salary BETWEEN 2400 AND 20000);

-- 위와 동일하게 NOT을 앞에 작성해도 동일한 결과가 출력된다.

 

---

-- -------------------------------------------

-- 3. IS NULL 연산자 (****)

-- -------------------------------------------

SELECT

    last_name,

    job_id,

    salary,

    commission_pct

FROM

    employees

WHERE

    commission_pct = NULL;

-- 수수료가 NULL인 값을 출력하라고 작성하였으나,

-- NULL의 경우 동등연산자로 비교가 불가능하다.

-- 이렇게 출력할 경우 아무런 결과가 출력되지 않는다.

-- -------------------------------------------

SELECT

    last_name,

    job_id,

    salary,

    commission_pct

FROM

    employees

WHERE

    commission_pct IS NULL;

-- 오라클에서 NULL의 경우 IS NULL과 IS NOT NULL 연산자를 통해 비교해야 한다.(**)

-- 현재는 수수료가 없는 직원들을 모두 출력한다.

SELECT

    last_name,

    job_id,

    salary,

    commission_pct

FROM

    employees

WHERE

    commission_pct IS NOT NULL;

-- 수수료가 있는 모든 직원이 출력된다.

-- -------------------------------------------

SELECT

    last_name,

    job_id,

    salary,

    commission_pct

FROM

    employees

WHERE

    nvl(commission_pct, -1) = -1;

-- nvl을 활용하여, NULL을 -1로 하여 수수료가 -1인 직원을 출력할 수 있다.

-- 하지만 이 방법은 속도가 느려질 위험성이 있기에 사용하지 않는 것이 좋다.

-- 또한 이 방법의 경우 원래 테이블에 있던 값을 가공해버리기 때문에 나중에 오류를 발생할 수 있다.

 

---

-- -------------------------------------------

-- 4. IS NOT NULL 연산자 (****)

-- -------------------------------------------

SELECT

    last_name,

    job_id,

    salary,

    manager_id

FROM

    employees

WHERE

    manager_id IS NOT NULL;

-- 직원 중에 관리자가 없는 직원을 제외한 모든 직원을 출력한다.

DESC employees;

---

-- -------------------------------------------

-- 5. 연산자의 우선순위

-- -------------------------------------------

-- (1) 괄호 ()

-- (2) 비교 연산자

-- (3) NOT 연산자

-- (4) AND 연산자

-- (5) OR 연산자

-- -------------------------------------------

-- * 우선순위 : 괄호() -> 비교 -> NOT -> AND -> OR

-- -------------------------------------------

-- 1 ) AND 연산자가 우선실행 : 예상하지 못한 결과

SELECT

    last_name,

    job_id,

    salary,

    commission_pct

FROM

    employees

WHERE

    job_id = 'AC_MGR' OR job_id = 'MK_REP'

    AND commission_pct IS NULL

    AND salary >= 4000

    AND salary <= 9000;

-- OR전에 AND를 모두 실행시켜버리기 때문에 원하는 결과가 출력되지 않는다.

-- 이때 OR을 이항 연산자이기 때문에 'job_id = 'AC_MGR' OR job_id = 'MK_REP'' 전체가 AND 뒤에서 실행된다.

-- 정확하게 결과를 출력하기 위해서는 소괄호()를 사용해야 한다.

SELECT

    last_name,

    job_id,

    salary,

    commission_pct

FROM

    employees

WHERE

    ( job_id = 'AC_MGR' OR job_id = 'MK_REP' )

    AND commission_pct IS NULL

    AND ( salary BETWEEN 4000 AND 9000 );

-- 수정 버전 : 소괄호()를 사용하여 OR이 먼저 실행되게 수정하였다. (***)

-- 이때 BETWEEN A AND B에서의 AND는 AND 연산자와는 다르다.

-- 자신의 의도한 순서대로 작동하기 위해서 / 가독성을 위해서는 소괄호()를 자주 사용하는 것이 좋다.

-- DB에서 SQL를 최적화하여 수행시키는 주체를 '옵티마이저 (최적화기)'라고 부른다.

 

---

-- -------------------------------------------

-- 6. ORDER BY - 숫자 데이터 정렬

-- -------------------------------------------

SELECT

    employee_id,

    last_name,

    job_id,

    salary

FROM

    employees

ORDER BY

    salary;

-- 급여를 기준으로 오름차순으로 정렬해서 출력한다. ( 디폴트 값 : ASC (오름차순) )

SELECT

    employee_id,

    last_name,

    job_id,

    salary

FROM

    employees

ORDER BY

    salary ASC;

-- 급여를 기준으로 오름차순으로 정렬하여 출력한다.

SELECT

    employee_id,

    last_name,

    job_id,

    salary

FROM

    employees

ORDER BY

    salary DESC;

-- 급여를 기준으로 내림차순으로 정렬하여 출력한다.

 

---

-- -------------------------------------------

-- 7. ORDER BY - 별칭 사용 (**)

-- -------------------------------------------

SELECT

    employee_id,

    last_name,

    job_id,

    salary + 100 AS "월급"

FROM

    employees

ORDER BY

    '월급' DESC;

-- ORDER BY절 전에 SELECT가 실행되어 월급이라는 별칭이 만들어 졌기 때문에

-- ORDER BY절에서 별칭을 사용하여 내림차순 정렬이 가능해진다.

---

-- -------------------------------------------

-- 8. ORDER BY - 표현식 사용 ( 표현식 : 값을 생성하는 식 ( = 연산식 ) )

-- -------------------------------------------

SELECT

    employee_id,

    last_name,

    job_id,

    salary + 100 AS "월급"

FROM

    employees

ORDER BY

    salary + 100 DESC;

-- 표현식을 기준으로 내림차순 정렬을 하여 출력한다.

---

-- -------------------------------------------

-- 9. ORDER BY - 컬럼 인덱스 사용 ( 부작용 O )

-- -------------------------------------------

SELECT

    employee_id,        -- 1

    first_name,         -- 2

    last_name,          -- 3

    job_id,             -- 4

    salary AS "월급"    -- 5

FROM

    employees

ORDER BY

    4 DESC;

-- 컬럼 인덱스 번호를 기준으로 내림차순 정렬하여 출력한다.

-- 부작용이 있기에, 다른 대안이 없는 경우에만 사용해야 한다.(**)

-- SELECT절의 구성컬럼목록에 변경이 없다는 조건하에 작성해야 한다.

-- 그렇지 않으면 컬럼 인덱스 번호 4번인 job_id를 기준으로 작성하였으나, 후에 다른 것으로 갑작스럽게 변경될 수 있다.

SELECT

    employee_id,

    last_name AS 이름,

    job_id,

    salary

FROM

    employees

ORDER BY

    이름 ASC;

-- 이름을 기준으로 오름차순 정렬하여 출력한다.

SELECT

    employee_id,

    last_name AS 이름,

    job_id,

    salary

FROM

    employees

ORDER BY

    2 ASC;

-- 컬럼 인덱스 번호 2번인 이름을 기준으로 오름차순하여 출력한다.

-- 하지만 컬럼 인덱스 번호를 활용한 방법은 사용하지 않는 것이 좋다.

SELECT * FROM dual;

-- 더미 테이블

---

-- -------------------------------------------

-- 10. ORDER BY - 날짜 데이터 정렬

-- -------------------------------------------

SELECT

    employee_id,

    last_name,

    salary,

    hire_date AS 입사일

FROM

    employees

ORDER BY

    hire_date DESC;

-- 입사일을 기준으로 내림차순하였기에, 가장 최근에 입사한 직원부터 오래된 직원이 차례대로 출력된다.

-- 최근일수록 날짜 데이터가 크다.

SELECT

    employee_id,

    last_name,

    salary,

    hire_date AS 입사일

FROM

    employees

ORDER BY

    입사일 DESC;

-- 별칭을 기준으로 내림차순 정렬할 수도 있다.

 

---

-- -------------------------------------------

-- 11. 다중 컬럼 정렬 (***)

-- -------------------------------------------

-- ***************************************

--  SELECT [DISTINCT] { *, column [AS] [alias] , ... }

--  FROM <테이블명>

--  [ WHERE <predicates(조건)> ]

--  [ ORDER BY

--  { column1|표현식1 } [ASC(오름차순)|DESC(내림차림)]

--  { column2|표현식2 } [ASC(오름차순)|DESC(내림차림)] ];

-- ***************************************

-- -------------------------------------------

SELECT

    employee_id,

    last_name,

    salary,

    hire_date

FROM

    employees

ORDER BY

    salary DESC,

    hire_date ASC;

-- 다중 컬럼 정렬의 경우, 앞선 정렬을 지키는 상태에서 그 다음 정렬을 한다.(***)

-- 즉, 이 경우에는 월급을 기준으로 내림차순을 한 다음에, '동일한 월급일 경우' 입사일을 기준으로 오름차순을 하라는 의미이다.(**)

 

---

-- -------------------------------------------

-- 12. NULL값 정렬

-- -------------------------------------------

-- * (주의) 오라클에서는 가장 큰 값이 NULL값이다.

-- ( 값이 없기에, 값의 크기를 비교하는 것이 불가능하다. )

-- 따라서 내림차순으로 정렬시, 가장 큰 값이 NULL로 정렬된다.

-- -------------------------------------------

SELECT

    employee_id,

    last_name,

    commission_pct

FROM

    employees

ORDER BY

    commission_pct DESC;

-- NULL 값이 포함되어 있는 commission_pct를 기준으로 내림차순 정렬하였다.

-- 오라클에서는 NULL값이 가장 큰 값으로 설정되어 있기에, commission_pct의 값이 NULL로 되어있는 직원이 먼저 출력된다. (**)

-- 그렇다면 NULL값인 직원들은 어떤 것을 기준으로 출력되는지 확인해보았더니, 원래 테이블의 투플이 들어가있는 순서대로 출력되고 있음을 알 수 있다.

SELECT

    employee_id,

    last_name,

    commission_pct

FROM

    employees

ORDER BY

    commission_pct ASC;

--  NULL 값이 포함되어 있는 commission_pct를 기준으로 오름차순 정렬하였다.

-- -------------------------------------------

-- (주의) 관계형데이터베이스의 테이블은 수학의 집합과 동일하다.

-- 즉, 수학의 집합 성질을 그대로 물려받는다. :

-- (1) 레코드의 순서를 보장하지 않는다. ( 즉, 무작위로 저장한다. )

-- (2) 중복을 허용하지 않는다.

-- (단, 관계형 테이블은 중복행을 포할할 수는 있으나, 기본키가 지정되어있으면 중복은 없다! )

---

-- -------------------------------------------

-- 13. 단일(행) (반환)함수 - 바로 적용이 가능하다.

-- -------------------------------------------

-- 단일(행) (반환)함수 구분 :

-- -------------------------------------------

-- (1) 문자(처리) 함수 : 문자와 관련된 특별한 조작을 위한 함수이다.

--      a. INITCAP  - 첫글자만 대문자로 변경

--      b. UPPER    - 모든 글자를 대문자로 변경

--      c. LOWER    - 모든 글자를 소문자로 변경

--      d. CONCAT   - 두 문자열 연결

--      e. LENGTH   - 문자열의 길이 반환

--      f. INSTR    - 문자열에서, 특정 문자열의 위치(인덱스) 반환

--      g. SUBSTR   - 문자열에서, 부분문자열(substring) 반환

--      h. REPLACE  - 문자열 치환(replace)

--      i. LPAD     - 문자열 오른쪽 정렬 후, 왼쪽의 빈 공간에 지정문자 채우기(padding)

--      j. RPAD     - 문자열 왼쪽 정렬 후, 오른쪽의 빈 공간에 지정문자 채우기(padding)

--      k. LTRIM    - 문자열의 왼쪽에서, 지정문자 삭제(trim)

--      l. RTRIM    - 문자열의 오른쪽에서, 지정문자 삭제(trim)

--      m. TRIM     - 문자열의 왼쪽/오른쪽/양쪽에서, 지정문자 삭제(trim)

--                    (단, 문자열의 중간은 처리못함)

--  (2) 숫자 (처리)함수

--  (3) 날짜 (처리)함수

--  (4) 변환 (처리)함수 (***) : ex. to_date, to_char

--  (5) 일반 (처리)함수

--

--  단일(행) (반환)함수는, 테이블의 행 단위로 처리됨!

--  인덱스 번호는 0부터 시작된다.

--  함수는 소문자로 작성해야 한다. ( 대문자로 작성해도 문제는 없다. )

---

-- -------------------------------------------

-- 14. 단일(행) (반환)함수 - INITCAP ( 첫글자만 대문자로 )

-- -------------------------------------------

SELECT

    'ORACLE SQL',

    initcap('ORACLE SQL')

FROM

    dual;

-- 더미 테이블에 initcap실행 ( 첫글자만 대문자로 출력 )

SELECT

    email,

    initcap(email)

FROM

    employees;

-- 직원들의 이메일 속성을 첫글자만 대문자로 출력한다.

 

---

-- -------------------------------------------

-- 15. 단일(행) (반환)함수 - UPPER

-- -------------------------------------------

SELECT

    'Oracle Sql',

    upper ('Oracle Sql')

FROM

    dual;

-- 더미 테이블에서 Oracle Sql이라는 문자열을 전부 대문자로 변환하여 출력한다.

SELECT

    last_name,

    upper(last_name)

FROM

    employees;

-- 모든 직원의 이름을 전부 대문자로 변환하여 출력한다.

SELECT

    last_name,

    salary

FROM

    employees

WHERE

    upper(last_name) = 'KING';

-- 값은 대소문자를 구분하기에 upper을 통해 모든 직원의 이름을 대문자로 변경하여 비교해야 한다.

-- 그러나 이 경우에는 데이터를 가공하게 되기 때문에 비추천하는 방법이다.

SELECT

    last_name,

    salary

FROM

    employees

WHERE

    last_name = initcap('KING');

-- 차라리, KING이라는 리터럴을 initcap을 통해서 변환하여 비교하는 것이 좋다.

 

---

-- -------------------------------------------

-- 16. 단일(행) (반환)함수 - LOWER

-- -------------------------------------------

SELECT

    'Oracle Sql',

    lower('Oracle sql')

FROM

    dual;

-- 더미 테이블에서 Oracle Sql이라는 문자열을 전부 소문자로 변환하여 출력한다.

SELECT

    last_name,

    lower(last_name)

FROM

    employees;

-- 모든 직원의 이름을 전부 소문자로 변환하여 출력한다.

 

---

-- -------------------------------------------

-- 17. 단일(행) (반환)함수 - CONCAT

-- -------------------------------------------

SELECT

    'Oracle' || 'Sql',

    concat('Oracle','Sql')

FROM

    dual;

-- 둘다 더미 테이블에서 Oracle과 Sql을 합쳐서 출력한다.

-- 출력 결과는 둘 다 동일하게 출력된다.

SELECT

    'Oracle'||'Sql'||'third',

    concat( concat('Oracle', 'Sql'), 'third')

FROM

    dual;

-- concat은 2개만 합칠 수 있기에, 여러 문자열을 합치기 위해서는 concat을 중첩해야한다.(**)

SELECT

    last_name||salary,

    concat(last_name, salary)

FROM

    employees;

-- 문자열타입의 컬럼과 숫자타입의 컬럼울 합쳤을 때, 두 방법 모두 동일한 결과를 출력한다.

SELECT

    last_name||hire_date,

    concat(last_name, hire_date)

FROM

    employees;

-- 문자열타입의 칼럼과 숫자타입의 칼럼을 합쳤을 때, 두 방법 모두 동일한 결과를 출력한다.

-- 이를 보아 concat에서도 숫자 <-> 문자 <-> 날짜 간의 자동변환이 이루어지고 있음을 알 수 있다.

---

-- -------------------------------------------

-- 18. 단일(행) (반환)함수 - LENGTH ( 문자열의 문자 개수를 반환 ) /  LENGTHB ( 문자열의 바이트 크기를 변환한다. )

-- -------------------------------------------

SELECT

    'Oracle',

    length('Oracle')

FROM

    dual;

-- 더미 테이블에서 Oracle의 문자길이가 얼마인지 출력한다.

SELECT

    '한글',

    length('한글') AS "length",

    lengthb('한글') AS "lengthb"

FROM

    dual;

-- 이를 통해서 한글의 경우 1 문자를 구성하는데 3바이트가 필요함을 알 수 있다. (***)

 

---

-- -------------------------------------------

-- 19. 단일(행) (반환)함수 - INSTR (***)

-- -------------------------------------------

SELECT

    instr('MILLER','L',1,2),    -- 1 : offset , 2 : 몇 번째 해당문자가 나오는 위치를 출력해라

    instr('MILLER','X',1,2)     -- 즉, 이 경우 인덱스번호 1부터 X를 찾아라! 하지만 2번째 X의 위치를 출력해라!

FROM

    dual;

-- 더미 테이블에서 해당 문자의 위치를 출력한다.

-- SQL에서는 문자열의 인덱스 번호가 1부터 시작한다!!(****)

 

---

-- -------------------------------------------

-- 20. 단일(행) (반환)함수 - SUBSTR (****)

-- -------------------------------------------

-- * (주의) ORACLE에서 인덱스 번호는 1부터 시작한다. (***)

-- -------------------------------------------

SELECT

    substr('123456-1234567',1,7) || '*******' AS "주민등록번호"

FROM

    dual;

-- 더미 테이블에서 123456-1234567이라는 문자열에서 문자열 인덱스 1부터 7글자를 추출한다.

-- 이때 -도 포함되어 123456-까지 추출이 되고, 그 후에 *******이 합쳐져서

-- 123456-*******라고 출력이 된다.

SELECT

    substr('123456-1234567',8)

FROM

    dual;

-- 해당 문자열에서 지정한 인덱스번호부터 끝까지 출력하게 된다.

-- 이 경우에는 8이라고 지정했기에, 8번째 문자인 1부터 끝까지 출력된다. (**)

SELECT

    hire_date AS 입사일,

    substr(hire_date, 8, 2) AS 입사년도,

    substr(hire_date, 4, 3 ) AS 입사월,

    substr(hire_date, 1, 2) AS 입사일자

FROM

    employees;

-- 입사일에서 VScode의 날짜 데이터 형식에 맞추어서 입사년도, 입사월, 입사일자를 따로 출력한다.

SELECT

    hire_date,

    to_char(hire_date) AS 입사일,                   -- 문자열로 출력

    to_char(hire_date, 'RR/MM/DD') AS 입사일2,      -- 해당 형식으로 포멧팅하고 문자열로 출력

    substr( to_char(hire_date), 8, 2) AS 입사년도   -- 변환된 문자열에서 입사년도만 출력

FROM

    employees;

-- 입사일 이라는 날짜 데이트를 'RR/MM/DD'라는 형식으로 변경하여 출력된다.

SELECT

    hire_date,

    to_char(hire_date) AS 입사일,

     to_char(hire_date, 'RR/MM/DD') AS 입사일2,

    to_char(hire_date, 'YY/MM/DD HH:MI:SS') AS 입사일자3,

    substr( to_char(hire_date), 8, 2) AS 입사년도

FROM

    employees;

-- 자바에서 사용되는 날짜 형식으로 포멧팅이 가능하다.

-- 단, 자바에서는 분을 mm으로 포멧팅하였으나 ORACLE에서는 MI로 포멧팅한다.

 

---

-- -------------------------------------------

-- + SYSDATE() 함수와 CURRENT_DATE() 함수에 대해서 알아보자!

-- -------------------------------------------

SELECT

    to_char(sysdate, 'YY/MM/DD HH24:MI:SS') AS "now",

    to_char(current_date, 'YY/MM/DD HH24:MI:SS') AS "now2"

FROM

    dual;

-- sysdate는 세계표준시각을 기준으로 현재 시간을 표기한다.

-- current_date는 자신이 있는 나라의 시간을 기준으로 현재 시간을 표기한다.

-- CURRENT_DATE() 함수를 사용하는 것이 좋다.(*)

-- YY/MM/DD HH24:MI:SS는 시간을 24시를 기준으로 표기하게 된다.