Day23

1.오라클의 쿼리를 정리하자

2.총계연산을 구현할 수 있다.

3.함수를 살펴보고 활용할 수 있다.

4.서브쿼리를 이해하고 연동할 수 있다.

✏️기본함수

INTICAP = 앞글자를 대문자로 바꿔줌 (공백부터 시작 EX) hi bye = Hi Bye )
LOWER = 소문자로 바꿔줌
UPPER = 대문자로 바꿔줌
CHR = 코드값을 문자로
ASCII = 문자를 코드값으로
CONCAT = 연결작용
ROUND = 반올림
TRUNC = 뒤에 자리를 버림

✏️함수구분

1.문자열 함수
INPUT
CHARACTER
OUTPUT
CHARACTER(LPAD/RPAD/LTRIM/RTRIM/TRIM/SUBSTR), NUMBER(INSTR/LENGTH/LENGTHB....)

2.숫자함수
INPUT
NUMBER
OUTPUT
NUMBER

3.날짜함수
INPUT
DATE
OUTPUT
DATE(SYSDATE,ADD_MONTHS..), NUMBER(MONTH_BETWEEN...)

4.타입변환 함수
INPUT
ANY
OUTPUT
ANY (TO_CHAR, TO_DATE, TO_NUMBER)

✏️찾는 문자열이 지정된 위치부터 지정한 회수만큼 나타난 시작 위치를 반환하는 함수

INSTR(string,substring,[[position, [occurrence]]

position
어디서 부터 찾을지를 결정하는 시작 위치 (default 1)

position > 0 : String의 시작부터 끝 방향을 의미 한다.
position < 0 : String의 끝 부터 시작 방향을 의미 한다.
단 LENGTH < 0 경우 NULL을 리턴한다.

ex)

Create table temp
(City CHAR(10),
Street VARCHAR(10));

Insert into temp
values('Pune','Oxford');

select length(city), length(street) from temp;

출력

length(City)          Length(street)
10                    6

출처

CHAR = 정적 메모리
VARCHAR = 동적 메모리

---

문제

Q1. EMAIL에서 @ABC.COM 문자열에서 "." 바로 앞의 문자 B의 위치를 구해라

 SELECT 'SG_AHN@ABC.COM',    INSTR('SG_AHN@ABC.COM','B',-1,1) 위치
 FROM DUAL;

 SELECT 'SG_AHN@ABC.COM',    INSTR('SG_AHN@ABC.COM','B',INSTR('SG_AHN@ABC.COM','.')-1) 위치
 FROM DUAL;

---

Q2. EMAIL에서 @ACC.COM 문자열에서 "." 바로 앞의 문자 C의 위치를 구해라

SELECT 'SG_AHN@ACC.COM',    INSTR('SG_AHN@ACC.COM','C',-1,2) 위치
FROM DUAL;

---

Q3.사원테이블에서 LPAD를 이용해서 전체 20자리를 확보하고 나머지는 *로 채우자

SELECT ENAME, LPAD(ENAME,20,'*') RES 
FROM EMP; 

---

Q4.사원테이블에서 RPAD를 이용해서 전체 20자리를 확보하고 오른쪽 나머지는 *로 채우자

SELECT ENAME, RPAD(ENAME,20,'*') RES 
FROM EMP; 

---

TRIM

Q5.해당 문자열의 공백을 지우자.
TRIM() 양쪽 공백
RTRIM() 오른쪽 공백 및 원하는 글자
LTRIM() 왼쪽 공백 및 원하는 글자

SELECT '    ABC  ' , TRIM('  ABC   '), RTRIM('  ABC   '), LTRIM('  ABC   ')
FROM DUAL;

---

Q6. SELECT LTRIM(' TECH') FROM DUAL; 왼쪽에 있는걸 지워줌
SELECT LTRIM(' TECH',' ') FROM DUAL;

SELECT LTRIM('000123','0') FROM DUAL;
SELECT LTRIM('123123TECH','123') FROM DUAL;

SELECT LTRIM('123123TECH123','123') FROM DUAL;
SELECT LTRIM('XYXZYYYTECH', 'XYZ') FROM DUAL;

---

Q7. SELECT TRIM('A' FROM 'AATECHAA') FROM DUAL;
SELECT TRIM(LEADING '2'FROM '22222TECH ') FROM DUAL;

SELECT TRIM( TRAILING '1' FROM '    TECH11111') FROM DUAL;

---

SUBSTR

Q8.SUBSTR(STRING,POSITION,[LENGTH]); 주어진 컬럼이나 문자열에서 지정한 위치부터 지정한 개수 만큼의
문자열을 잘라내어 리턴하는 함수

SELECT SUBSTR('This is a test',6,2) FROM DUAL;   6번째의 2글자 =is

SELECT SUBSTR('This is a test',-4,2) FROM DUAL; 뒤에서 4번째에서 2글자 =te

---

Q9. 반올림을 연동해보자. [ROUND]

SELECT ROUND(125.315) FROM DUAL;   =125
SELECT ROUND(125.315,0) FROM DUAL; =125

SELECT ROUND(125.315,1) FROM DUAL;  =125.3
SELECT ROUND(125.315,-1) FROM DUAL; =130    --(125 에서 -1 = 5이므로 5가 반올림하여 130이 된다.)

---

Q10. 지정한 자릿수에서 버림 하는 함수 [TRUNC]
TRUNC (NUMBER,[INTEGER: decimal_places])

SELECT TRUNC(125.315) FROM DUAL;   =125
SELECT TRUNC(125.315,0) FROM DUAL; =125

SELECT TRUNC(125.315,1) FROM DUAL;  =125.3
SELECT TRUNC(125.315,-1) FROM DUAL; =120

SELECT TRUNC(125.315,3) FROM DUAL; = 125.315

SELECT TRUNC(-125.315,-3) FROM DUAL;  = 0
 SELECT TRUNC(125.315,-3) FROM DUAL; = 0

---

Q11. 사원 테이블에서 입사일 기준으로 근무한지 20년이 되는 일자를 출력 해보자.

ADD_MONTHS
SELECT ENAME,HIREDATE,ADD_MONTHS(HIREDATE,240) FROM EMP;

(HIREDATE 에 240을 더함)

---

Q12. MONTH_BETWEEN(DATE1,DATE2) : 지정된 두 날짜 사이의 월 수를 리턴하는 함수

SELECT MONTHS_BETWEEN('21-08-23','21-09-01') FROM DUAL;

SELECT MONTHS_BETWEEN('21-09-01','21-08-23') FROM DUAL;

SELECT MONTHS_BETWEEN('21-09-01','21-10-01') FROM DUAL; = -1 =1달

---

Q13. 1980년 01월 01일 기준으로 입사한지 20년이 넘은 직원들의 근무 년수를 조회하자.

SELECT ENAME, HIREDATE,MONTHS_BETWEEN('1980-01-01',HIREDATE)  /12 AS 근무년수
FROM EMP 
WHERE MONTHS_BETWEEN('1980-01-01',HIREDATE) > 180;

---

Q14. 현재일을 기준으로 입사한지 20년이 넘은 직원들의 근무 년수를 조회하자.

SELECT ENAME, HIREDATE,MONTHS_BETWEEN(SYSDATE,HIREDATE)  /12 AS 근무년수
FROM EMP 
WHERE MONTHS_BETWEEN(SYSDATE,HIREDATE) > 180;

ANY

타입변환 함수 : ANY ANY (TO_NUMBER,TO_CHAR, TO_DATE)

TO_CHAR(INPUT_TYPE,FORMAT)
-NUMBER 타입을 CHARACTER 타입으로 변환이 필요한 경우

EX) 표현방식을 변경할 때 : 1000 --> 1000숫자를 문자로 변경 100 -> '100'

-DATE 타입을 CHARACTER 타입으로 변환이 필요한 경우
EX) 21/08/23 -> '21-08-23' ,
EX2) 시간정보를 표시하고 싶을 때 , HIREDATE = '21/08/23'

9=자리수 ,0=남는 자리수 ,$ ,L,콤마(,),점(.) = 통화기호

---

Q15. TO_CHAR()를 이용해서 숫자를 문자로 변경 해보자

SELECT TO_CHAR(1234,'99999') FROM DUAL;
SELECT TO_CHAR(1234,'09999') FROM DUAL;

SELECT TO_CHAR(1234,'L9999') FROM DUAL;
SELECT TO_CHAR(1234,'99,999') FROM DUAL;

SELECT TO_CHAR(1234,'09,999') FROM DUAL;
SELECT TO_CHAR(1000,'9.9EEEE') FROM DUAL;
SELECT TO_CHAR(1234,'999') FROM DUAL;

---

Q16.
-YYY/YY/YEAR : 년도(4/2/문자)
-MONTH/MON/MM/RM : 달 (이름/약어/숫자/로마기호)
-DDD/DD/D : 일 (1년 기준/ 1달 기준 / 1주 기준)
-Q : 분기(1,2,3,4)
-DAY/DY : 요일 (이름/ 약어)
-HH(12)/HH24 : 12시간/24시간
-AM|PM : 오전/오후
-MI : 분 (0~59)
-SS : 초 (0~59)

SELECT TO_CHAR(SYSDATE, 'PM HH24:MI:SS' ) FROM DUAL;

SELECT TO_CHAR(SYSDATE, 'AM HH:MI:SS' ) FROM DUAL;

---

Q17. 아래와 같이 오늘 날짜가 출력 되도록 해보자.
8월 월, 2021

SELECT TO_CHAR(SYSDATE, 'MON/DY/YYYY' ) FROM DUAL;

---

Q18. 오늘 날짜를 기점을 분기를 나타내 보자

SELECT TO_CHAR(SYSDATE,'yearQ') FROM DUAL;

---

Q19. 사원테이블에서 사원의 이름과 입사일을 00년 00월 00일로 출력하도록 하자.

SELECT ENAME AS 이름 , TO_CHAR(HIREDATE, 'YYYY"년" MM"월" DD"일"') AS 입사일 FROM EMP;

SELECT ENAME AS 이름 , SUBSTR(HIREDATE,1,2)||'년'|| SUBSTR(HIREDATE,4,2)||'월'|| SUBSTR(HIREDATE,7,2)||'일' AS 입사일
FROM EMP;

SELECT ENAME AS 이름, TO_CHAR(HIREDATE,'YYYY"년" MM"월" DD"일" HH24:MI:SS') AS 입사일
FROM EMP;