SQL SELECT 연산, 내장형 함수

 

SQL SELECT 연산, 내장형 함수

 

※ 연습 데이터

makeData.sql

0.01MB

 

1. 산술연산, 연결연산

① 컬럼외에 리터럴 직접 입력

컬럼을 입력한 대상은 테이블에서 값을 차례로 가져오고, 리터럴은 출력행 수만큼 반복되어 출력

SELECT 직원ID
     , 성별
     , 주민등록번호
     , 123
     , 'Hello'
     , SYSDATE
  FROM 직원 ;

 

② SELECT에 입력된 컬럼이나 리터럴에 AS(ALIAS) 문법을 이용해 별칭 주기

SELECT 직원ID 
     , 성별
     , 주민등록번호   AS 주민번호
     , 123          AS 숫자형리터럴    
     , 'HELLO'      AS 문자형리터럴
     , SYSDATE      AS 날짜형리터럴 
  FROM 직원 ;

 

③ SELECT에 입력된 컬럼이나 리터럴에 산술연산

SELECT 직원ID 
     , 성별
     , 주민등록번호   AS 주민번호
     , 123 + 123    AS 리터럴끼리더하기
     , 123 * 123    AS 리터럴끼리곱하기 
     , 123 - 123    AS 리터럴끼리빼기 
     , 123 / 123    AS 리터럴끼리나누기
     , 나이 + 30    AS 컬럼과리터럴끼리연산하기     
     , 연봉 + 나이   AS 컬럼끼리연산하기
     , 'HELLO'      AS 문자형리터럴
     , SYSDATE      AS 날짜형리터럴 
  FROM 직원 ;

 

▶ 실습

직원ID, 이름, 연봉, 연봉의 10% 보너스 컬럼, 합산값 실수령액 컬럼 만들기

 

SELECT 직원ID  
     , 이름 
     , 연봉
     , 연봉 * 0.1           AS 보너스
     , 연봉 + (연봉 * 0.1)   AS 실수령액 
  FROM 직원 ;

 

④ 각 컬럼을 연결하거나 원하는 값들을 '연결'하여 표시하기

|| 연산자 입력 : 각 컬럼 혹은 리터럴 연결 (NULL 값은 아무것도 출력하지 않음)

SELECT 직원ID 
     , 이름 
     , 나이 
     , 연봉 
     , 직원ID || '의 이름은 ' || 이름 || '이고, 나이는 ' || 나이 || '살이고 연봉은 ' || 연봉 || ' 원 입니다' AS 연결연산자를이용함
  FROM 직원 ;

 

---

 

2. 내장형 함수, 형변환 함수

 

① 문자형 함수

LOWER(입력값)	대문자를 소문자로 바꿔서 출력
UPPER(입력값)	소문자를 대문자로 바꿔서 출력
SUBSTR(입력값, 시작위치, 뽑을개수)	시작위치부터 뽑을 개수만큼 문자열을 출력
TRIM(입력값)	양쪽 공백 제거 출력
CONCAT(입력값1, 입력값2)	입력값1과 입력값2를 붙여서 출력
REPLACE(입력값, 바꾸고싶은문자, 바꿀문자)	바꾸고 싶은 문자열을 바꿀문자로 변경에서 출력

SELECT LOWER('HELLO my name is TAE , 20 YEARS old') AS LOWER함수사용
     , UPPER('hello MY NAME IS tae , 20 years OLD') AS UPPER함수사용
     , SUBSTR('안녕하세요' , 2 , 2)                  AS SUBSTR함수사용
     , TRIM('   양쪽공백제거    ')                   AS TRIM함수사용
     , CONCAT('앞' , '뒤')                          AS CONCAT함수사용 
     , REPLACE('010-1234-1234' , '-' , '')         AS REPLACE함수사용
  FROM DUAL ;

 

② 숫자형 함수

MOD(숫자1, 숫자2)	숫자1을 숫자2로 나눈 나머지 출력
ROUND(숫자, 반올림자릿수)	숫자를 반올림자릿수만큼 반올림 출력
CEIL(숫자)	입력받은 숫자를 올림 출력
FLOOR(숫자)	입력받은 숫자를 내림 출력
POWER(숫자1, 숫자2)	숫자1을 숫자2만큼 제곱해서 출력

SELECT MOD(10 , 3)             AS MOD함수사용          
     , ROUND(1.2345 , 1)       AS ROUND함수사용        
     , CEIL(2.4)               AS CEIL함수사용
     , FLOOR(3.9)              AS FLOOR함수사용
     , POWER(2,4)              AS POWER함수사용
  FROM DUAL;

 

③ 날짜형 함수

ADD_MONTHS(날짜형입력값, 숫자)	날짜형 입력값에 숫자만큼 가감해 출력
LAST_DAY(날짜형입력값)	입력한 날짜형을 기준으로 마지막 날 출력
SYSDATE	현재시각

 

④ 형변환 함수

TO_NUMBER(입력값, 포맷)	입력값을 숫자형으로 변환하고, 포맷을 이용해 지정
TO_CHAR(입력값, 포맷)	입력값을 문자형으로 변환하고, 포맷을 이용해 지정
TO_DATE(입력값, 포맷)	입력값을 날짜형으로 변환하고, 포맷을 이용해 지정

SELECT TO_NUMBER('1')       AS TO_NUMBER함수사용 -- '1' 이라는 문자형 리터럴을 1 이라는 숫자형 리터럴로 변경
     , TO_CHAR(1)           AS TO_CHAR함수사용   -- 1 이라는 숫자형 리터럴을 '1' 이라는 문자형 리터럴로 변경 
     , TO_DATE('20221212')  AS TO_DATE함수사용   -- '20221212' 라는 문자형 리터럴을 2022/12/12 라는 날짜형 리터럴로 변경  
     , TO_CHAR(SYSDATE , 'YYYY-MM-DD HH24:MI:SS') AS TO_CHAR함수포맷사용  --현재날짜를 '년-월-일 24시-분-초' 형식으로 표현 
  FROM DUAL ;

 

※ 수치가 부적합합니다. (ORA-01722) 발생 원인 확인하기

산술연산을 할 때는 자동으로 형변환 함수 적용

형 변환 우선 순위 : 날짜형 > 숫자형 > 문자형

ex) 문자형 + 숫자형 연산 → 숫자형이 우선 순위가 높기 때문에 숫자형을 기준으로 형 변환

(TO_NUMBER(문자형) + 숫자형)

SELECT '1' + 1 
  FROM DUAL ; 
--정상 , TO_NUMBER('1') 일 때 '1' 은 숫자로 변경가능하기 때문에 1 + 1 이 되어 2로 출력된다.

SELECT 'A' + 1 
  FROM DUAL ;          
--오류 , TO_NUMBER('A') 일 때 'A' 는 숫자로 변경될 수 없으므로 오류가 발생하는 것

SELECT SYSDATE + 1 
  FROM DUAL;       
--정상 , 날짜형과 숫자형이 연산될 경우 숫자가 DAY(일) 로 처리되어 SYSDATE + 1일 이 출력된다.

 

⑤ NULL 관련 연산 및 함수

 

※ NULL 값을 연산을 하면 제대로 출력되지 않는다. → NULL 관련 함수 이용 필요

SELECT 직원ID 
     , 나이
     , 나이 + 10 AS 십년후나이 
  FROM 직원 ;

NVL(입력값1, 입력값2)	입력값1이 NULL이면 입력값2를 출력, NULL이 아니면 입력값1 출력
NVL2(입력값1, 입력값2, 입력값3)	입력값1이 NULL이면 입력값3을 출력, NULL이 아니면 입력값2 출력
DECODE(입력값1, 입력값2, 입력값3, 입력값4)	입력값1이 입력값2와 같으면 입력값3을 출력, 아니면 입력값4 출력
COALESCE(입력값1, 입력값2, ... , 입력값N)	앞에서부터 입력값이 NULL이 아닌 값이 나오면 해당 입력값 출력 (모두 NULL이면 NULL 출력)

SELECT 직원ID 
     , 나이
     , 나이 + 10                           AS 십년후나이 
     , NVL(나이 , 0) + 10                  AS NVL을적용한십년후나이 
     , NVL2(나이 , 나이 , 0) + 10          AS NVL2을적용한십년후나이 
     , DECODE(나이 , NULL , 0 , 나이) + 10 AS DECODE를적용한십년후나이 
     , COALESCE(나이 , 0 )   + 10          AS COALESCE을적용한십년후나이
  FROM 직원 ;

 

---

 

▶ 실습

 

1. 직원 테이블에서 직원ID , 성별 , 주민등록번호 컬럼 데이터를 출력

SELECT 직원ID
     , 성별
     , 주민등록번호
  FROM 직원 ;

 

2. 직원 테이블에서 직원ID , 연봉 컬럼 , 그리고 연봉에 1000 을 더해 인상된연봉 이라는 컬럼 이름으로 데이터를 출력

SELECT 직원ID
     , 연봉
     , 연봉+1000 as 인상된연봉
  FROM 직원 ;

 

3. 직원 테이블에서 컬럼을 연결연산을 이용해 다음과 같은 형태로 출력

(예시) 직원 A0001의 이름은 김철수입니다.

SELECT '직원 ' ||직원ID|| '의 이름은 ' ||이름|| '입니다.'
  FROM 직원 ;

 

4. 현재 시간 기준 1시간 전의 시간을 년-월-일 시:분:초 형태로 출력

SELECT TO_CHAR(SYSDATE-1/24, 'YYYY-MM-DD HH24:MI:SS') AS 현재시각한시간전
  FROM DUAL;

 

5. eat sleep code repeat 리터럴 값이 있을 때, 이 값에서 가운데 code 만 출력하는 SQL을 작성

SELECT SUBSTR('eat sleep code repeat', 11, 4)
  FROM DUAL;

 

6.직원연락처 테이블에서 직원ID , 연락처구분코드, 연락처 컬럼을 출력

단, 연락처를 010-123-1234 형태가 아니라 0101231234 형태처럼 '-' 문자열을 없애고 출력

SELECT 직원ID
     , 연락처구분코드
     , REPLACE(연락처, '-', '') as 연락처
  FROM 직원연락처 ;

 

7. 1.452 의 값을 각각 ROUND(소수점1번째자리까지) , CEIL , FLOOR 처리하여 출력해보세요. DUAL 테이블을 사용

SELECT ROUND(1.452,1)
     , CEIL(1.452)
     , FLOOR(1.452)
  FROM DUAL ;

 

8. 직원 테이블에서 나이가 NULL이면 999를 출력하도록 NVL 함수를 이용해 출력 

출력할 컬럼은 직원ID, 나이, 주민등록번호 출력

SELECT 직원ID
     , NVL(나이, 999) AS 나이
     , 주민등록번호
  FROM 직원 ;