2024_06_26

오늘의 코딩 순서 + 포인트

scott_0626 인출된 표와 값까지 같이 붙여넣기

0) SQL 함수의 유형

1. 단일 행 함수 : 테이블에 저장되어 있는 개별 행을 대상으로 함수를 적용하여 하나의 결과를 반환하는 함수 데이터 값을 조는데 주로 사용

데이터 값을 조작하는데 주로 사용
행별로 함수를 적용하여 하나의 결과를 반환하는 함수

2.복수 행 함수: 조건에 따라 여러 행을 그룹화하여 그룹별로 결과를 하나씩 반환하는 함수

1) 문자함수: 문자 데이터를 입력하여 문자나 숫자를 결과로 반환하는 함수

1. INITCAP: 문자열의 첫 번째 문자만 대문자로 변환 ex) INITCAP('student') -> Student

문제 1-1) 학생 테이블에서 ‘김영균’ 학생의 이름, 사용자 아이디를 출력하여라. 그리고 사용자 아이디의 첫 문자를 대문자로 변환하여 출력하여라.

SELECT  name, userid, INITCAP(userid)
FROM    student
WHERE   name = '김영균'
;

2. LOWER: 문자열 전체를 소문자로 변환 ex) LOWER('STUDENT')->student

3. UPPER: 문자열 전체를 대문자로 변환 ex) UPPER('student') -> STUDENT

문제 1-2) 학생 테이블에서 학번이 '20101'인 학생의 사용자 아이디를 소문자와 대문자로 변환하여 출력

SELECT  userid, LOWER(userid), UPPER(userid)
FROM    student
WHERE   studno = 20101
;

4. LENGTH, LENGTHB: 문자열 길이 반환 함수

LENGTH 함수: 인수로 입력되는 칼럼이나 표현식의 문자열의 길이를 반환하는 함수,
글자 자리수, 알파벳도 한글도 동일한 한 자리
LENGTHB 함수: 문자열의 바이트 수를 반환하는 함수,
알파벳, 아라비아 숫자 한 글자당 1byte, 한글 한 글자당 3byte(우리나라는 utf-8을 쓰기 때문=> 만약 utf-16을 쓰게 되면 2byte가 될 것임)
↳ 한글 문자열 길이 TEST --> Insert 안 된 이유가 현재 표준이 utf-8이기 때문

INSERT INTO DEPT VALUES (59,'경영지원팀','충정로');

문제 1-3) 부서 테이블에서 부서 이름의 길이를 문자 수와 바이트 수로 각각 출력하여라

SELECT  DNAME, LENGTH(DNAME), LENGTHB(DNAME)
FROM    DEPT
;

5.문자 조작 함수의 종류

CONCAT : concatenation, 두 문자열을 결합, ‘||’와 동일

SELECT name || '의 직책은'
FROM   professor
;
SELECT concat(name, '의 직책은')
FROM   professor
;

SELECT (name || '의 직책은 ')|| position
FROM   professor
;
SELECT concat(concat(name, '의 직책은 '),position)
FROM   professor
;

SUBSTR : substring, 특정 문자 또는 문자열 일부를 추출

문제 1-4) 학생 테이블에서 1학년 학생의 주민등록 번호에서 생년월일과 태어난 달을 추출하여 이름, 주민번호, 생년월일, 태어난 달을 출력하여라

SELECT name, idnum, SUBSTR(idnum,1,6) birth_date, 
        SUBSTR(idnum,3,2) birth_mon, SUBSTR(idnum,7,1) gender
FROM   student
WHERE  grade = 1
;

INSTR : 특정 문자가 출현하는 첫 번째 위치를 반환, 문자열 중에서 사용자가 지정한 특정 문자가 포함된 위치를 반환하는 함수

문제 1-5) 학과 테이블의 부서 이름 칼럼에서 '과' 글자의 위치를 출력하여라

SELECT dname, INSTR(dname,'과')
FROM   department
;

LPAD: 오른쪽 정렬후 왼쪽으로 지정문자를 삽입
RPAD: 왼쪽 정렬후 오른쪽으로 지정 문자를 삽입
LPAD와 RPAD 함수는 문자열이 일정한 크기가 되도록 왼쪽 또는 오른쪽에 지정한 문자를 삽입하는 함수

문제 1-6) 교수테이블에서 직급 칼럼의 왼쪽에 '*'문자를 삽입하여 10바이트로 출력하고 교수 아이디 칼럼은 오른쪽에 '+' 문자를 삽입하여 12바이트로 출력

SELECT position, LPAD(position, 10, '*') lpad_position,
       userid,   RPAD(userid, 12 , '+')  rpad_userid
FROM   professor
;

LTRIM: 왼쪽 지정 문자를 삭제
RTRIM: 오른쪽 지정 문자를 삭제
LTRIM과 RTRIM 함수는 문자열에서 특정 문자를 삭제하기 위해 사용, 보통 스페이스를 많이 삭제함,
함수의 인수에서 삭제할 문자를 지정하지 않으면 문자열의 앞 뒤 부분에 있는 공백 문자를 삭제

dual: 의미없는 테이블을 쓸 때 사용

스페이스 삭제방법↓↓↓

SELECT  ' abcdefg ', LTRIM(' abcdefg ', ' ')
FROM    dual
;

SELECT  ' abcdefg ',
        LTRIM(' abcdefg ', ' '),
        RTRIM(' abcdefg ', ' '),
        TRIM(' abcdefg ')
FROM    dual
;

문제 1-7) 학과 테이블에서 부서 이름의 마지막 글자인 '과'를 삭제하여 출력하여라

SELECT dname, RTRIM(dname, '과')
FROM   department
;

2) 숫자함수: 숫자 데이터를 처리하기 위한 함수

⭐ROUND: 지정한 소수점 자리로 값을 반올림, 지정한 자리 이하에서 반올림한 결과 값을 반환하는 함수

문제 2-1) 교수 테이블에서 101학과 교수의 일급을 계산(월 근무일은 22일)하여 소수점 첫째 자리와 셋째 자리에서 반올림 한 값과 소숫점 왼쪽 첫째 자리에서 반올림한 값을 출력하여라

SELECT  name , sal , sal/22 , ROUND(sal/22) , ROUND(sal/22,2) , ROUND(sal/22, -1)
--  나누기 22==>                반올림             소수점 두자리     -1: 정수기준으로 반올림
FROM    professor
WHERE   deptno = 101
;

⭐ TRUNC: 지정한 소수점 자리까지 남기고 값을 버림

문제 2-2) 교수 테이블에서 101학과 교수의 일급을 계산(월 근무일은 22일)하여 소수점 첫째 자리와 셋째 자리에서 절삭 한 값과 소숫점 왼쪽 첫째 자리에서 절삭한 값을 출력

SELECT  name , sal , sal/22 , TRUNC(sal/22) ,TRUNC(sal/22,2) , TRUNC(sal/22,-1)
--          소수점을 안정해서 소수점 밑을 다 버림/ 소수점 두자리까지만 남김/ 1의자리도 버림          
FROM    professor
WHERE   deptno = 101
;

⭐ MOD(Moduler): MOD 함수는 나누기 연산후에 나머지를 출력하는 함수

문제 2-3) 교수 테이블에서 101번 학과 교수의 급여를 보직수당으로 나눈 나머지를 계산하여 출력하여라

SELECT  name , sal , comm , MOD(sal, comm)
FROM    professor
WHERE   deptno = 101
;

CEIL: CEIL 함수는 지정한 숫자보다 크거나 같은 정수 중에서 최소 값을 출력하는 함수

FLOOR: FLOOR함수는 지정한 숫자보다 작거나 같은 정수 중에서 최대 값을 출력하는 함수

문제 2-4) 19.7보다 큰 정수 중에서 가장 작은 정수와 12.345보다 작은 정수 중에서 가장 큰 정수를 출력하여라

SELECT  CEIL(19.7) , FLOOR(12.345)
FROM    dual
;

3) 날짜 함수⭐⭐⭐

1. 날짜 + 숫자 = 날짜 (날짜에 일수를 가산)

문제 3-1) 교수 번호가 9908인 교수의 입사일을 기준으로 입사 30일 후와 60일 후의 날짜를 출력

SELECT  name , hiredate , hiredate+30 , hiredate+60
FROM    professor
WHERE   profno  =   9908
;

2. ⭐⭐⭐SYSDATE 함수: SYSDATE 함수는 시스템에 저장된 현재 날짜를 반환하는 함수로서, 초 단위까지 반환,

공유된 시간과 날짜로 기록할 때 사용( 컴마다 시간, 날짜가 다를 수 있으니까)

SELECT  sysdate
FROM    dual
;

3. 날짜 - 숫자 = 날짜 (날짜에 일수를 감산)

SELECT  name , hiredate , hiredate-30 , hiredate-60
FROM    professor
WHERE   profno = 9908
;

4. 날짜 - 날짜 = 일수 (날짜에 날짜를 감산), date에서 date를 빼면 일수를 돌려준다

SELECT  name , hiredate , sysdate ,sysdate -hiredate
FROM    professor
WHERE   profno = 9908
;

5. MONTHS_BETWEEN과 ADD_MONTHS 함수는 월 단위로 날짜 연산을 하는 함수

MONTHS_BETWEEN : date1과 date2 사이의 개월 수를 계산
ADD_MONTHS : date에 개월 수를 더한 날짜 계산

문제 3-2) 입사한지 120개월 미만인 교수의 교수번호, 입사일, 입사일로 부터 현재일까지의 개월 수, 입사일에서 6개월 후의 날짜를 출력하여라

SELECT  profno , hiredate,
        MONTHS_BETWEEN(SYSDATE , hiredate)  working_day,
        ADD_MONTHS(hiredate , 6)            hire_6after
FROM    professor
WHERE   MONTHS_BETWEEN(SYSDATE , hiredate) < 120
;

6. TO_CHAR 함수: TO_CHAR 함수는 날짜나 숫자를 문자로 변환하기 위해 사용

↳ 많이 쓰이는 것: MM / MONTH / MON / YYYY / YY / DD / DY / DAY

문제 3-3) 학생 테이블에서 전인하 학생의 학번과 생년월일 중에서 년월만 출력하여라

SELECT  studno 
        , TO_CHAR(birthdate , 'YY/MM')      birthdate1
        , TO_CHAR(birthdate , 'yy-mm')       birthdate2
        , TO_CHAR(birthdate , 'yymm')       birthdate3
        , TO_CHAR(birthdate , 'yymmdd')     birthdate4
        , TO_CHAR(birthdate , 'yyyymmdd')   birthdate5
FROM    student
WHERE   name = '전인하'
;

--2006-10-10,'MONTH')
SELECT  TO_CHAR(sysdate,'MONTH') monthDate
FROM    dual
;

7. LAST_DAY, NEXT_DAY

LAST_DAY 함수: 해당 날짜가 속한 달의 마지막 날짜를 반환하는 함수
NEXT_DAY 함수: 해당 일을 기준으로 명시된 요일의 다음 날짜를 변환하는 함수

문제 3-4)오늘이 속한 달의 마지막 날짜와 다가오는 일요일의 날짜를 출력하여라

SELECT  sysdate , Last_DAY(sysdate) , Next_DAY(sysdate,'토')
FROM    dual;

↓↓↓ (여기서부터 위의 함수들 활용 문제)

문제 3-5) 3-4 문제를 ROUND, TRUNC로 변환

SELECT  TO_CHAR(sysdate , 'YY/MM/DD HH24:MI:SS')   NORMAL,
        TO_CHAR(sysdate , 'YY/MM/DD HH24:MI:SS')   TRUNC,
        TO_CHAR(sysdate , 'YY/MM/DD HH24:MI:SS')   ROUND
FROM    dual
;

문제 3-6)

SELECT  name , TO_CHAR(hiredate , 'YY/MM/DD HH24:MI:SS')   hiredate,
        TO_CHAR(ROUND(hiredate,'dd') , 'YY/MM/DD')   round_dd,
        TO_CHAR(ROUND(hiredate,'mm') , 'YY/MM/DD')   round_mm,
        TO_CHAR(ROUND(hiredate,'yy') , 'YY/MM/DD')   round_yy
FROM    professor
;

문제 3-7) 학생 테이블에서 전인하 학생의 학번과 생년월일 중에서 년월만 '문자로' 출력하여라(TO_CHAR 함수 이용)

SELECT  studno, TO_CHAR(birthdate,'YY/MM') birthdate
FROM    student
WHERE name = '전인하'
;

⭐문제 3-8) 숫자를 문자 형식으로 변환, 보직수당을 받는 교수들의 이름, 급여, 보직수당, 그리고 급여와 보직수당을 더한 값에 12를 곱한 결과를 연봉(anual_sal)으로 출력
(현장HW)(여태까지 한것의 종합판!)

SELECT  name , sal , comm , TO_CHAR((sal+comm)*12, '9,999') anual_sal
FROM    professor
WHERE   comm IS NOT NULL
;

4) NLV함수

1. NVL2 함수: NVL2 함수는 첫 번째 인수 값이 NULL이 아니면 두 번째 인수 값을 출력하고, 첫 번째 인수 값이 NULL이면 세 번째 인수 값을 출력하는 함수

문제 4-1) 102번 학과 교수중에서 보직수당을 받는 사람은 급여와 보직수당을 더한 값을 급여 총액으로 출력하여라.
단, 보직수당을 받지 않는 교수는 급여만 급여 총액으로 출력하여라.

SELECT  name , position , sal ,comm,
        NVL2(comm, sal+comm, sal)total
FROM    professor
WHERE   deptno = 102
;

2. NULLIF 함수: 두 개의 표현식을 비교하여 값이 동일하면 NULL을 반환하고, 일치하지 않으면 첫 번째 표현식의 값을 반환

문제 4-2) 교수 테이블에서 이름의 바이트 수와 사용자 아이디의 바이트 수를 비교해서 같으면 NULL을 반환하고 같지 않으면 이름의 바이트 수를 반환

SELECT  name , userid , LENGTHB(name), LENGTHB(userid),
        NULLIF( LENGTHB(name) , LENGTHB(userid))    nullif_result
FROM    professor
;

3. ⭐⭐⭐⭐⭐DECODE 함수

DECODE 함수는 기존 프로그래밍 언어에서 IF문이나 CASE 문으로 표현되는 복잡한 알고리즘을 하나의 SQL 명령문으로 간단하게 표현할 수 있는 유용한 기능
DECODE 함수에서 비교 연산자는 ‘=‘만 가능
실무에서 매우 많이 사용됨!!!
Java의 'if-else if-else'와 비슷함

문제 4-3) 교수 테이블에서 교수의 소속 학과 번호를 학과 이름으로 변환하여 출력하여라.
학과 번호가 101이면 ‘컴퓨터공학과’, 102이면 ‘멀티미디어학과’, 201이면 ‘전자공학과’, 나머지 학과 번호는 ‘기계공학과’(default)로 변환

↓↓↓ 아래처럼 코딩하면 가독성도 좋고, if문이라는게 확실히 와닿음

SELECT  name , deptno , DECODE(deptno , 101,'컴퓨터공학과' 
                                      , 102,'멀티미디어학과' 
                                      , 201,'전자공학과' 
                                      ,     '기계공학과')
FROM    professor
;

4. ⭐⭐⭐⭐⭐CASE 함수

CASE 함수는 DECODE 함수의 기능을 확장한 함수
DECODE 함수는 표현식 또는 칼럼 값이 ‘=‘ 비교를 통해 조건과 일치하는 경우에만 다른 값으로 대치할 수 있지만, CASE 함수에서는 산술 연산, 관계 연산, 논리 연산과 같은 다양한 비교가 가능(부등호 사용이 가능)
또한 WHEN 절에서 표현식을 다양하게 정의
8.1.7에서부터 지원되었으며, 9i에서 SQL, PL/SQL에서 완벽히 지원
DECODE 함수에 비해 직관적인 문법체계와 다양한 비교 표현식 사용
Java의 switch문과 비슷함

문제 4-4) 위의 DECODE 문제 CASE문으로 변환

SELECT  name , deptno , 
        CASE WHEN deptno = 101 Then '컴퓨터공학과' 
             WHEN deptno = 102 Then '멀티미디어학과'  --가독성을 위해 이렇게 띄어쓰기
             WHEN deptno = 201 Then '전자공학과' 
             Else                   '기계공학과'
        END deptname
FROM    professor
;

5. DECODE 함수와 CASE 함수 비교

CASE문을 더 많이 사용하는 이유?

문제 4-5) 교수 테이블에서 소속 학과에 따라 보너스를 다르게 계산하여 출력하여라. (별명 --> bonus)
학과 번호별로 보너스는 다음과 같이 계산한다. ==> 학과 번호가 101이면 보너스는 급여의 10%, 102이면 20%, 201이면 30%, 나머지 학과는 0%

DECODE문

SELECT  name , deptno , sal , DECODE(sal , 101, sal*0.1
                                         , 102, sal*0.2
                                         , 103, sal*0.3
                                         ,         0)     bonus
FROM    professor
;

CASE문

SELECT  name , deptno , sal , 
        CASE WHEN deptno = 101 Then sal*0.1
             WHEN deptno = 102 Then sal*0.2
             WHEN deptno = 103 Then sal*0.3
             ELSE                        0     
        END bonus
FROM    professor
;

현장과제 1

TO_CHAR 함수를 이용한 숫자 출력 형식 변환 ==> 9
ex) (1234, ’99999’) ===> 1234

문제 1) TO_CHAR 함수 이용, 숫자를 문자 형식으로 변환 (현장HW) (여태까지 한것의 종합판!)
보직수당을 받는 교수들의 이름, 급여, 보직수당, 그리고 급여와 보직수당을 더한 값에 12를 곱한 결과를 연봉(anual_sal)으로 출력

SELECT  name , sal , comm , TO_CHAR((sal+comm)*12, '9,999') anual_sal
FROM    professor
WHERE   comm IS NOT NULL
;

문제 2) TO_CHAR, TO_NUMBER 함수 이용

student Table에서 주민등록번호에서 생년월일을 추출하여 문자 ‘YY/MM/DD’ 형태로 별명 BirthDate로 출력하여라

잠깐!) TO_NUMBER: 문자를 숫자로 바꿔줌↓↓↓

SELECT  TO_NUMBER('123')
FROM    dual
;

SELECT  TO_CHAR(TO_DATE(SUBSTR(idnum,1,6), 'YYMMDD'), 'YY/MM/DD')BirthDate
FROM    student
;

문제 3) NVL 함수이용 (NULL을 0 또는 다른 값으로 변환하기 위한 함수)
101번 학과 교수의 이름, 직급, 급여, 보직수당, 급여와 보직수당의 합계를 출력하여라. 단, 보직수당이 NULL인 경우에는 보직수당을 0으로 계산한다

SELECT  name , position , sal , comm , sal + comm,
        sal + NVL(comm,0)   s1,
        NVL(sal+comm , sal) s2
FROM    professor
WHERE   deptno = 101
;

⭐⭐⭐현장과제 2

문제 1) salgrade 데이터 전체 보기

SELECT  *       --테이블 전체 출력/ *:  테이블 전체
FROM    salgrade;

문제 2) scott에서 사용가능한 테이블 보기

SELECT  *       --테이블 전체 출력/ *:  테이블 전체
FROM    tab;

문제 3) emp Table에서 사번 , 이름, 급여, 업무, 입사일

SELECT  deptno , ename , sal , job , hiredate
FROM    emp
;

문제 4) emp Table에서 급여가 2000미만인 사람 에 대한 사번, 이름, 급여 항목 조회

SELECT  empno , ename , sal
FROM    emp
WHERE   sal < 2000
;

문제 5) emp Table에서 80/02이후에 입사한 사람에 대한 사번,이름,업무,입사일

SELECT  empno , ename , sal
FROM    emp
WHERE   sal < 2000
;

문제 6) emp Table에서 급여가 1500이상이고 3000이하 사번, 이름, 급여 조회 (2가지 코딩하기)

SELECT  empno , ename , sal
FROM    emp
WHERE   sal >= 1500
AND     sal <= 3000
;

SELECT  empno , ename , sal
FROM    emp
WHERE   sal BETWEEN 1500 AND 3000   --현장에서 더 인기 많은 방법
;

문제 7) emp Table에서 사번, 이름, 업무, 급여 출력 [ 급여가 2500이상이고 업무가 MANAGER인 사람]

SELECT  empno , ename , job , sal
FROM    emp
WHERE   sal >= 2500
AND     job = 'MANAGER'
;

문제 8) emp Table에서 이름, 급여, 연봉 조회 => [단 조건은 연봉 = (급여+상여) * 12 , null을 0으로 변경]

SELECT  ename , sal , (sal+NVL(comm,0))*12 연봉
FROM    emp
;

Tip)

--SELECT  ename , sal , (sal+NVL(comm,0))*12 연 봉==> 이렇게 쓰면 안나옴
--FROM    emp
--;

SELECT  ename , sal , (sal+NVL(comm,0))*12 "연 봉" --공백을 넣으려면 큰따옴표 필수
FROM    emp
;

문제 9) emp Table에서 81/02 이후에 입사자들중 xxx는 입사일이 xxX => [ 전체 Row 출력 ] --> 2가지 방법 다

SELECT  ename ||'는 입사일이 '||hiredate|| '이다'
FROM    emp
WHERE   hiredate >= '81/02/01'
;

문제 10) emp Table에서 이름속에 T가 있는 사번,이름 출력

SELECT  empno , ename
FROM    emp
WHERE   ename like '%T%'
;

수업교재

3-1) 날짜 함수

3-2) 날짜 출력 형식의 종류

YYYY
YY
MM
MONTH
MON
DD
DY
DAY

교재] 오라클로 배우는 데이터베이스 입문: p 15~163

오늘의 숙제

1. emp Table 의 이름을 대문자, 소문자, 첫글자만 대문자로 출력

SELECT  upper(ename),lower(ename),initcap(ename)
FROM    emp
;

2. emp Table 의 이름, 업무, 업무를 2-5사이 문자 출력

SELECT  ename , job, substr(job,2,4)
FROM    emp
;

3. emp Table 의 이름, 이름을 10자리로 하고 왼쪽에 #으로 채우기

SELECT  ename , lapd(ename,10,'#')
FROM    emp
;

4. emp Table 의 이름, 업무, 업무가 MANAGER면 관리자로 출력

SELECT  ename , job , replace(job,'MANAGER', '관리자')
FROM    emp
;

5. emp Table 의 이름, 급여/7을 각각 정수, 소숫점 1자리. 10단위로 반올림하여 출력

SELECT  ename , sal/7 , round(sal/7) , round(sal/7,1) , round(sal/7,-1)
FROM    emp
;

'DB > Oracle' 카테고리의 다른 글

2024_06_27_목 (0)	2024.06.27
2024_06_26_수 (0)	2024.06.26
2024_06_25_화 (0)	2024.06.25
2024_06_12_수 (0)	2024.06.12
2024_06_11_화 (0)	2024.06.11

코딩일기

2024_06_26_수

오늘의 코딩 순서 + 포인트

scott_0626 인출된 표와 값까지 같이 붙여넣기

0) SQL 함수의 유형

1) 문자함수: 문자 데이터를 입력하여 문자나 숫자를 결과로 반환하는 함수

2) 숫자함수: 숫자 데이터를 처리하기 위한 함수

3) 날짜 함수⭐⭐⭐

4) NLV함수

현장과제 1

⭐⭐⭐현장과제 2

수업교재

3-1) 날짜 함수

3-2) 날짜 출력 형식의 종류

교재] 오라클로 배우는 데이터베이스 입문: p 15~163

오늘의 숙제

'DB > Oracle' 카테고리의 다른 글

티스토리툴바

2024_06_26_수

오늘의 코딩 순서 + 포인트

scott_0626 인출된 표와 값까지 같이 붙여넣기

0) SQL 함수의 유형

1) 문자함수: 문자 데이터를 입력하여 문자나 숫자를 결과로 반환하는 함수

2) 숫자함수: 숫자 데이터를 처리하기 위한 함수

3) 날짜 함수⭐⭐⭐

4) NLV함수

현장과제 1

⭐⭐⭐현장과제 2

수업교재

3-1) 날짜 함수

3-2) 날짜 출력 형식의 종류

교재] 오라클로 배우는 데이터베이스 입문: p 15~163

오늘의 숙제

'DB > Oracle' 카테고리의 다른 글

관련글

티스토리툴바