✨ [ORACLE]_23.02.22

🧡 지난 수업 리뷰

select last_name lname, department_id
from employees 
where department_id = 90
or job_id = 'ST_CLERK' 
and hire_date > '17-JUN-11' 
order by lname;

🤍 복습 문제 풀이

-- 복습 문제 
select last_name, job_id 
from employees 
where job_id not in ('ST_CLERK');

select employee_id, last_name 
from employees 
where job_id = 'ST_CLERK'
and department_id =50; 

select * 
from employees; 

select last_name, salary*12 as Anu_sal
from employees;

select last_name, salary*12 as Anu_sal
from employees
where  salary*12 >= 200000; 

select last_name
from employees
where last_name like 'H%';

select employee_id, department_id, hire_date
from employees
where department_id not in (90, 60) 
order by hire_date;

select last_name ||q'['s job is]'||job_id "Employee's job"
from employees;

select last_name, job_id, hire_date
from employees
order by job_id, hire_date desc;

🤍 복습 문제 sol

--1
SELECT last_name, job_id
FROM   employees
WHERE  job_id != 'ST_CLERK';

--2
SELECT employee_id, last_name
FROM   employees
WHERE  job_id='ST_CLERK'
AND     department_id=50;

--3
SELECT last_name, salary*12 "Anu_sal"
FROM   employees;

--4
SELECT last_name, salary*12 "Anu_sal"
FROM   employees
WHERE   salary*12 >= 200000;

--5
SELECT last_name
FROM   employees
WHERE  last_name like 'H%';

--6
SELECT employee_id, department_id, hire_date
FROM   employees
WHERE  department_id not in (90,60)
ORDER BY hire_date desc;

--7
SELECT last_name||'''s job is '||job_id "Employee's Job"
FROM employees;

SELECT last_name||q'['s job is ]'||job_id "Employee's Job"
FROM employees;

--8
SELECT  last_name, job_id, hire_date
FROM    employees
ORDER BY job_id, hire_date desc;

🧡 SQL 행 제한 절

• ref. https://jujun.tistory.com/264

• 오라클 12버전 부터 실행 가능

• 실습 16p

• SQL 행 제한 절

• ROW LIMITING 절 사용 (FETCH)

  • FETCH FIRST 5 ROWS ONLY;
    → 의미) 전체 행 중 5개의 행만 추출해라
    employees 테이블에서 무작위로 5개 행 추출하므로 실행마다 결과값이 상이할 수 있음. (= 첫 5건 검색)
  • OFFSET 4 ROWS
FETECH NEXT 5 ROWS ONLY;
    → 의미) OFFSET은 첫 4행은 건너뛰고 나서 무작위로 5행을 추출하겠다 (= 4 건 건너뛰고 5건 검색)
    +) FETECH NEXT = FETCH FIRST 같은 의미
    +) OFFSET 4 ROWS = OFFSET 4 ROW 같은 의미
  • FETCH FIRST 1 PERCENT ROWS ONLY;
    → 의미) 전체 107건 안 1퍼센트에 해당하는 건수를 찾아 추출하겠다 (= 첫 1% 검색)

• SQL 행 제한 절 예제

  • order by employee_id
    fetch first_5 rows only;
  • order by employee_id
    offset 5 rows fetch first_5 rows only;
    6번째부터 10번째까지(6등부터 10등) 행 추출
    +) fetch절의 only 대신 with ties 사용시 의미=동점인 행까지 추출하겠다
  • top n query 예제
    select employee_id, salary
    from employees
    order by salary desc
    fetch firt 2 rows with ties;

• 순번을 매겨 데이터 추출시 select rownum

• 행 제한 추출시 rownum <= 10
  🔽
  

• rownum = 5; 가 실행 안되는 이유

  주로 <, <= 사용하며 >, >= 인 경우 ROWNUM은 동작하지 않는다.
  ROWNUM = 1은 사용 가능 하지만 ROWNUM = 2인 경우는 데이터가
  추출되지 않는다.
  (ROWNUM은 WHERE절을 만족하는 레코드에 붙이는 순번이므로 해석해 보면,
  ROWNUM = 2는, 처음 한 건 추출해서 ROWNUM이 2인지 비교하는 것이다.
  하지만 처음 레코드는 ROWNUM이 1이며, 조건에 맞지 않으므로 버린다.
  그 다음 레코드 선택 후 또 ROWNUM이 2인지 비교하지만 전 레코드를
  버렸기 때문에 새로 추출되는 레코드는 ROWNUM이 1이다. 2가 아니다.
  그러기 때문에 또 버리게 되고 그 다음 레코드를 추출한다. 이 과정을 반복하면
  ROWNUM = 2는 도달할 수 없는 값이 됨을 알 수 있다.)

• rownum 을 활용한 top-n 쿼리 비교

-- 잘못된 예시 
SELECT employee_id, salary
FROM (SELECT employee_id, salary
 FROM employees
 ORDER BY salary DESC) 
WHERE rownum <= 5;

-- 성공 예시 (서브 쿼리문을 from 절 안에 삽입) 
SELECT employee_id, salary
FROM employees
WHERE rownum <= 5
ORDER BY salary DESC;

• 치환 변수 사용
  ref. https://1day1code.tistory.com/19

• 치환 변수 개념

  • 매번 수행할때마다 내가 입력한 값으로 명령어를 수행하는 것을 말한다.

• 단일 치환 변수 예시
  select ename 이름, deptno 부서 from emp where deptno = &dep;

• 이중 치환 변수
  ※ (순서) 이중 치환 변수는 단일 치환 변수보다 앞쪽에 나와야 한다. (데이터 값을 한 번만 입력받고 이후에 활용하는게 효율적이므로~)

  • 단일 치환 변수는 사용할때마다 변수 입력 팝업창 나옴

  • 그니까 이중 치환 변수로 한번만 입력받게 하는게 효율적인거지

  • 물론 select 절에 단일 치환변수 쓰면 한번 팝업창에 데이터 입력하고, 후에 order by 절에 이중치환변수 쓰면 또 팝업창 뜨고 값 입력하면 추후 데이터값입력 팝업창은 나오지 않음

  • 이중 치환 변수(&&) 사용

  • 최초 입력값 저장

  • 매번값을 입력할 필요없이 변수값을 재사용할때 사용

  • 변수 지정하는 define 명령과 같은 의미 지님

• SHOW VERIFY #로 이전/신규 메세지 표시
  SET VERIFY OFF #로 메시지 표시 끔

🧡 4장. 단일행 함수

🔽 sql 함수

🔽 sql 함수의 유형

• 단일 행 함수

  • • 데이터 조작을 위해 사용
  • • 인수를 받아들여 하나의 결과를 반환
  • • 반환되는 각 행에 대해 작업을 수행
  • • 행 당 하나의 결과를 반환
  • • SELECT, WHERE, ORDER BY 절에 사용할 수 있음
  • • 데이터 타입을 변경할 수 있음
  • • 중첩될 수 있음

• 단일 행 함수 유형
  🔽
  

• 단일 행 함수 유형(문자 함수)
  🔽
  

• 단일 행 함수 유형(문자 함수) 예시

🔽

• LOWER(column | expression) 영문자를 소문자로

• UPPER(column | expression) 영문자를 대문자로

• INITCAP(column | expression) 영문자 단어의 첫 문자는 대문자로 나머지는 소문자로

• CONCAT(column1 | expression1, column2 | expression2)

  • 문자열을 결합

• SUBSTR(column | expression, m [,n])

  • 문자열중 지정한 위치에서 지정한 길이만큼 문자열 추출

• LENGTH(column | expression) 문자의 길이를 숫자 값으로 표시

• INSTR(column | expression, ‘string’ [,m][,n])

  • 문자열내에 특정 문자열의 위치를 숫자로 표시

• LPAD | RPAD (column | expression, n, ‘string’)

  • 문자열을 제외한 왼쪽/오른쪽 공간에 지정한 문자로 채움

• TRIM(leading | trailing | both, trim_char FROM trim_source )

  • 앞, 뒤 나오는 특정 문자를 제거
    REPLACE(text,search_string,replacement_string)
    특정 문자열을 다른 문자열로 대체

• 대소문자 변환 함수

  • LOWER, UPPER 및 INITCAP 이 대소문자 변환 함수

• 대소문자 구분없이 king을 찾아서 출력하시오

🔽

select employee_id, last_name, upper(last_name)
from employees
where upper(last_name) = 'KING'
or 
where lower(last_name) = 'king'

🔽 쿼리문 출력

• 대소문자 변환 함수 예시

-- 치환변수로 값 저장 
SELECT employee_id, last_name, department_id 
FROM employees
WHERE INITCAP(last_name) = INITCAP('&last_name');

-- INITCAP함수는 입력 문자열 중에서
각 단어의 첫 글자를 대문자로 
나머지는 소문자로 변환하여 반환

• concat 문자열 합침

select concat (last_name, first_name, name)
last_name||first_name||job_id
from employees;

• substr 일부 문자열 추출

  • 예) SUBSTR(‘HelloWorld’,-5, 5) -> World

• dual 테이블을 활용한 문자열 조작 함수 적용

select * from dual;
-- dual 은 펑션이나 계산식을 테이블 생성없이 
수행해 보기 위한 용도로 사용하는 
일종의 'dummy' 테이블 

select SUBSTR('HelloWorld',1,5) 
from dual;

select SUBSTR('HelloWorld',-5)
from dual;

-- n으로 끝나는 last_name  추출 
select last_name 
from employees
where substr(last_name, -1, 1) = 'n';
or 
where last_name like '%n';

🔽

• LENGTH: 문자열 길이
• INSTR : 특정 문자가 몇 번째에 위치하는지
• LPAD (왼쪽에 추가함을 의미):
  LPAD(‘Hello’,10,’’) 10자가 될때까지 HELLO 문자열 오른쪽에 ``을 추가하라
• RPAD(오른쪽에 추가함을 의미): 10자가 될때까지 HELLO 문자열 왼쪽에 *을 추가하라
• TRIM : 특정 문자를 기준으로 잘라내라
  • TRIM(‘H’ from ‘HelloWorld’)

  SELECT TRIM(TRAILING 'H' FROM 'HELLOWORLDHHHH')
  FROM DUAL; 
  --  TRAILING : 우측 공백 혹은 문자 제거
   SELECT TRIM( 'H' FROM 'HELLOWORLDHHHH')
  FROM DUAL; 
  --  양옆 공백 혹은 문자 제거

• REPLACE: REPLACE는 특정 문자열을 찾아 바꾸는 함수입니다. 사용자가 바꾸고자 하는 문자나 문자열을 지정하면 지정한 형태로 문자나 문자열이 바뀝니다

  • REPLACE (‘Jack and Jue’,’J’,’Bl’)

• 문자열 조작 함수

  • 가장 안쪽 행의 함수부터 바깥 쪽의 순으로 실행됨

• 숫자함수

-- TRUNC: 버림, MOD: 나머지 구하기
-- ROUND (45.923,2) 2는 1/10^ (소숫점 둘째 자리 의미) 
-- ROUND (45.923,0) 0는 1/1 (소숫점 첫째 자리 의미) , 0 생략 가능 , 정수로 반올림 해라 
-- ROUND (45.923,-1) -1는 10 (10의 자리 의미) , 10의 자리까지 표현해라 
SELECT ROUND (45.923,2), TRUNC(45.926,2),
       MOD(1300,200)
FROM   DUAL;

• 단일행 함수 실습
  🔽

-- 4장 단일행 함수 
SELECT LAST_NAME, JOB_ID, UPPER(LAST_NAME), LOWER(LAST_NAME), INITCAP(JOB_ID)
FROM EMPLOYEES
WHERE DEPARTMENT_ID =20;

SELECT ENAME, JOB, ENAME||JOB, CONCAT(ENAME, JOB) FROM EMP
WHERE DEPTNO =10;

SELECT ename, SUBSTR(ename,1,3), SUBSTR(ename,3), SUBSTR(ename, -2,2)
  FROM emp
  WHERE deptno = 10 ;
  
SELECT ename, LENGTH(ename), INSTR(ename, 'L'), INSTR(ename, 'L', 1, 2)
 FROM emp
 WHERE deptno = 10 ;

SELECT sal, LPAD(sal, 7, '0') , RPAD(sal, 7,'*'), DEPTNO
  FROM emp
  WHERE deptno = 10 
  
SELECT ename, REPLACE(ename, 'CL','M')
FROM emp
WHERE deptno = 10 ;

SELECT ename, TRIM('S' FROM ename), TRIM(LEADING 'S' FROM ename), TRIM(TRAILING 'S' FROM ename)
FROM emp
WHERE deptno = 20 ;
-- LEADING removes characters specified from the start of a string

-- 좌우 공백 제거 BOTH
SELECT TRIM(BOTH FROM ' HELLO WORLD EVERYONE ')
FROM DUAL;

-- 중간 공백 제거 Replace(문자열,' ' ,'')    
SELECT Replace('HELLO WORLD EVERYONE', ' ', '')
FROM DUAL;

• 날짜 함수

-- YY는 오늘 기준의 연도를 붙임 99 
-> 2099년 의미 , 1999 출력은 RR 세션 사용 
ALTER SESSION SET NLS_DATE_FORMAT = 'RR/MM/DD' ;
UPDATE copy_emp
SET hiredate = '99/05/06'
WHERE empno = 7876

• 날짜 함수 종류

🔽

-- sysdate: DB서버의 사간을 DATE 타입으로 보여줌 
-- SYSDATE - hire_date 입사 이후 며칠 지났는지 


SELECT last_name, ROUND((SYSDATE - hire_date)/7) AS weeks
FROM employees
WHERE department_id = 90;

• 날짜 함수 사용

REF. https://webstudynote.tistory.com/56
🔽

4장 연습문제 풀이

--4장 연습문제 풀이
-- 4-1) 현재 시스템 날짜 출력(AS DATE) 
SELECT SYSDATE "DATE" 
FROM DUAL;

-- 4-2) 
SELECT EMPLOYEE_ID, LAST_NAME, SALARY, ROUND(SALARY*1.155) "NEW SALARY"
FROM EMPLOYEES;
 
-- 4-3) 
SELECT EMPLOYEE_ID, LAST_NAME, SALARY, 
ROUND(SALARY*1.155) "NEW SALARY",
SALARY*1.155-SALARY INCREASE
FROM EMPLOYEES;

-- 4-4)
SELECT LAST_NAME, ROUND((SYSDATE-HIRE_DATE)/7) TENURE
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 90
ORDER BY TENURE DESC; 


-- 4-5)
SELECT LAST_NAME, ROUND(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)) TENURE
FROM EMPLOYEES
ORDER BY TENURE ;

-- 4-6) 
SELECT INITCAP(FIRST_NAME) NAME, LENGTH(FIRST_NAME) 
FROM EMPLOYEES
WHERE FIRST_NAME LIKE 'J%'
OR FIRST_NAME LIKE 'A%'
OR FIRST_NAME LIKE 'M%'
ORDER BY LENGTH(FIRST_NAME) ; 

-- 4-7) 
SELECT LAST_NAME, FIRST_NAME, LPAD(SALARY, 15, '$') SALARY
FROM EMPLOYEES;

🧡 5장 변환 함수 및 일반 함수

• 변환 함수

• [명시적 형변환 설명]

  • 명시적 형변환은 형변환 함수를 사용해서 데이터 타입을 일치시키는 것입니다
  • to_number : 문자열을 숫자로 변환합니다
  • to_char : 숫자 혹은 문자를 지정된 포맷의 문자로 변환합니다
  • to_date : 문자열을 지정된 포맷의 날짜형으로 변환합니다

• [암시적 형변환 설명]

  • 암시적 형변환은 개발자가 형변환을 하지 않은 경우 데이터베이스 관리 시스템이 자동으로 형변환을 하는 것입니다

• fm 포맷 사용시 숫자 앞의 0, 채워진 공백문자를 제거하고 표현됨

  SELECT last_name,
  to_char(hire_date, 'fmDD Month YYYY') as HIREDATE
  FROM employees;

-- TO_CHAR: 기본 DATE FORMAT 이 아닌 다른 FORMAT 으로 날짜 확인 위함 
SELECT HIRE_DATE, TO_CHAR(HIRE_DATE, 'YYYY/MM/DD HH24:MI:SS')
FROM EMPLOYEES; 
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-RR' ;

-- HIGGINS가 입사한 연도만 추출 
SELECT employee_id, LAST_NAME, 
TO_CHAR(hire_date, 'YYYY') YEAR_hired 
FROM employees
WHERE last_name = 'Higgins';

SELECT last_name,
to_char(hire_date, 'DD Month YYYY') as HIREDATE
FROM employees;


SELECT last_name,
TO_CHAR(hire_date, 'fmDdspth "of" YYYY fmHH:MI:SS AM') 
HIREDATE
FROM employees

-- 숫자 데이터에 TO_CHAR 함수 사용

SELECT SALARY, TO_CHAR(SALARY,'L999,999.00')
FROM EMPLOYEES
WHERE LAST_NAME = 'Higgins'; 

SELECT SALARY, TO_CHAR(SALARY,'$999,999.00')
FROM EMPLOYEES
WHERE LAST_NAME = 'Higgins'; 

-- 자리수를 부족하게 지정하는 경우 
SELECT SALARY, TO_CHAR(SALARY,'$9,999.00')
FROM EMPLOYEES
WHERE LAST_NAME = 'Higgins'; 

-- 기본 형식이 아닌 데이터 입력시 오라클은 이를 CHAR로 해석한다 
SELECT last_name, TO_CHAR(hire_date, 'DD-Mon-YYYY') 
FROM employees
WHERE hire_date < TO_DATE ('2002/01/01','DD-MON-RR');

SELECT last_name, TO_CHAR(hire_date, 'DD-Mon-YYYY') 
FROM employees
WHERE hire_date > TO_DATE ('2002/01/01','YYYY/MM/DD');

-- 문자열을 숫자로 변환 가능
SELECT TO_NUMBER ('1') FROM DUAL;
-- 문자열을 숫자로 변환 불가능
SELECT TO_NUMBER ('A') FROM DUAL;

-- 명시적 데이터 타입 변환 
SELECT ENAME, HIREDATE, TO_CHAR(HIREDATE, 'YYYY/MM/DD'),
TO_CHAR(HIREDATE, 'YYYY/MM/DD HH24:MI:SS')
FROM EMP
WHERE DEPTNO = 10;

SELECT ENAME, HIREDATE,
TO_CHAR(HIREDATE, 'DD Month YYYY', 'NLS_DATE_LANGUAGE=AMERICAN'),
TO_CHAR(HIREDATE, 'fmDD Month YYYY', 'NLS_DATE_LANGUAGE=AMERICAN')
FROM EMP
WHERE DEPTNO = 10;

SELECT ENAME, HIREDATE, TO_CHAR(HIREDATE, 'Q'),
TO_CHAR(HIREDATE, 'D'), TO_CHAR(HIREDATE, 'W'), TO_CHAR(HIREDATE, 'DAY')
FROM EMP 
WHERE DEPTNO = 10;

SELECT ENAME, SAL, TO_CHAR(SAL, '$99,999.00'),
TO_CHAR(SAL, '$00,000.00'), TO_CHAR(SAL, 'L99,999.00')
FROM EMP
WHERE DEPTNO = 10;

SELECT EMPNO, ENAME, DEPTNO
FROM EMP
WHERE DEPTNO = TO_NUMBER('10') ;

SELECT TRUNC('06-MAY-13', 'MONTH')
FROM DUAL; 
SELECT TRUNC('06-MAY-13', 'MONTH')

SELECT empno, ename, hiredate
FROM emp
WHERE hiredate = TO_DATE('01May, 1981','DD Month, YYYY') ;

SELECT EMPNO, ENAME, HIREDATE FROM EMP
WHERE HIREDATE = TO_DATE('01May, 1981', 'fxDDMonth, YYYY'); 

--  COMMISSION_PCT값이 NULL이면 0을 반환 
SELECT COMMISSION_PCT, NVL(COMMISSION_PCT, 0) 
FROM EMPLOYEES;
-- NULL값이 들어간 산술은 NULL 반환 
SELECT SALARY, COMMISSION_PCT, NVL(COMMISSION_PCT, 1),
SALARY*COMMISSION_PCT, SALARY+SALARY*NVL(COMMISSION_PCT,0)
FROM EMPLOYEES;

SELECT SALARY, COMMISSION_PCT, NVL(COMMISSION_PCT, 1),
SALARY*COMMISSION_PCT, SALARY+SALARY*NVL(COMMISSION_PCT,0.01)
FROM EMPLOYEES;

5장 연습문제 풀이

-- 5장 연습 문제 풀이

-- 5-1)
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:MI:SS') "TODAY"
FROM DUAL;

-- 5-2)
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
SELECT EMPLOYEE_ID, LAST_NAME
FROM EMPLOYEES
WHERE HIRE_DATE = TO_DATE('2020-07-04 13:15:00', 'YYYY-MM-DD HH24:MI:SS');

-- 5-3)
SELECT LAST_NAME, NVL(TO_CHAR(COMMISSION_PCT), 'No Commission') COMM
FROM EMPLOYEES;