[TIL]격파르타SQLD: 단일행 함수 + Speak + 코맥 매카시 논문

7. 2과목: SQL 기본, SQL 활용, 관리 구문 

 

7-2. 함수 - 단일행 함수

-- 7-2. 함수: 내장 함수-단일행 함수
-- 1) 문자형 함수
-- ①LOWER(문자열) / UPPER(문자열) / CONCAT(문자열1, 문자열 2)
SELECT LOWER('SQL EXPERT') FROM DUAL;
SELECT UPPER('sql expert') FROM DUAL;
SELECT CONCAT('RDBMS', ' SQL') FROM DUAL;
SELECT CONCAT(UPPER('sqld '), LOWER('COMPLETED!')) from DUAL

-- ②ASCII(문자) ③Oracle: CHR / SQL Server: CHAR(ASCII 번호) → 이런 게 있구나!
-- ④Oracle : SUBSTR
SELECT SUBSTR('SQL Expert', 5, 5) FROM DUAL;
SELECT SUBSTR('SQL is a standard language for accessing and manipulating  
databases.', 19, 8)
From Dual;

-- ⑤Oracle : LENGTH
SELECT LENGTH('SQL Expert') FROM DUAL;

-- ⑥LTRIM(문자열[, 지정문자]) / RTRIM(문자열[, 지정문자]) / TRIM([leading|trailing|both] 지정문자 FROM 문자열)
SELECT LTRIM('xxxYYZZxYZ', 'x') FROM DUAL; 
SELECT RTRIM('XXYYzzXYzz', 'z') FROM DUAL;
SELECT TRIM('x' FROM 'xxYYZZxYZxx') FROM DUAL;
-- leading: 왼쪽 제거 / trailing: 오른쪽 제거

-- ⑦LPAD(문자열1,n[,문자열2]) / RPAD(문자열1,n[,문자열2])
SELECT LPAD('ABC',8,'Z') FROM dual;
SELECT RPAD('ABC',8) FROM dual;

--2) 숫자형 함수
-- ①ABS(숫자) → 절대값
SELECT ABS(-15) FROM DUAL;
-- ②SIGN(숫자) → 부호판단: 숫자가 양수이면 1, 0이면 0,  음수면 -1을 리턴 
SELECT SIGN(-10) FROM DUAL;

-- ③MOD(숫자1, 숫자2) → 숫자1을 숫자2로 나누어 나머지 값을 계산
SELECT MOD(7, 3) FROM DUAL;
--> 1

-- ④CEIL(숫자) / FLOOR(숫자) → 올림 / 내림
SELECT CEIL(-38.567) FROM DUAL; 
--> -38
SELECT FLOOR(38.567) FROM DUAL; 
--> 38

-- ⑤ ROUND(숫자 [, m]) / TRUNC(숫자 [, m]) → 자를 소수점 m자리에서 반올림 / 버림하여 리턴
SELECT ROUND(38.564, 2) FROM DUAL; 
--> 38.57
SELECT TRUNC(38.567, 2) FROM DUAL; 
--> 38.56

-- ⑥ SIN, COS, TAN(숫자)
SELECT SIN(1.5708) FROM DUAL;
--> 0.99999

-- ⑦ EXP, POWER, SQRT, LOG, LN → 이런 게 있다!  

--3) 날짜형 함수
-- ①‘DATE_FORMAT’ 함수(MySQL 기준)
SELECT DATE_FORMAT(’2024-11-02’, ‘%Y-%m-%d’) FROM DUAL;
--> 2024-11-02

-- ② SYSDATE / EXTRACT(시간날짜단위 FROM 시간날짜)
SELECT SYSDATE FROM DUAL;
--> 01-MAR-25
SELECT EXTRACT(YEAR FROM DATE '2023-10-10') FROM DUAL;
--> 2023

--4) 변환형 함수 → 특정 데이터 타입을 다른 형태로 출력
-- ①명시적 데이터 유형 변환
SELECT TO_NUMBER('888') + TO_NUMBER('111') FROM dual;
--> 999

-- ②암시적(묵시적) 데이터 유형 변환 → 성능 저하 가능하므로 지양

--*5) NULL 관련 함수 → NULL은 아직 정의되지 않은 값을 의미(0, 공백 등과는 다름). 어떠한 값과 NULL과의 연산은 항상 NULL 값이 나온다
SELECT NULL + 100 FROM DUAL;
--> _
-- ①Oracle: NVL / SQL Server: ISNULL 함수 → 결괏값을 NULL 아닌 다른 값 얻고자 할 때 
SELECT NVL(NULL, 'NVL-OK') FROM DUAL;
--> NVL-OK
SELECT NVL(100, 0) FROM DUAL;
--> 100

-- 연습문제
CREATE TABLE pokemon (  
    pm_id NUMBER PRIMARY KEY NOT NULL,  
    name VARCHAR2(20) NOT NULL,  
    attr VARCHAR2(20), 
      weight NUMBER 
);

INSERT INTO pokemon VALUES (1, 'Bulbasaur', 'Grass', 30);
INSERT INTO pokemon VALUES (4, 'Charmander', 'Fire', 80);
INSERT INTO pokemon VALUES (25, 'Pikachu', 'Electric', 15);
INSERT INTO pokemon (pm_id, name) VALUES (54, 'Psyduck');
INSERT INTO pokemon (pm_id, name, attr) VALUES (76, 'Golem', 'Rock');

SELECT  
pm_id,  
name,  NVL(attr, '알 수 없음'),  NVL(weight, 999) 
FROM pokemon;

-- ②NULL과 공집합 → 일치되는 데이터가 없는 것이 공집합, 값 자체가 정의되지 않 은 것이 NULL
SELECT NVL(MGR, 9999) FROM EMP WHERE ENAME='JSC';
--> ...does not exist

-- ③NULLIF(EXPR1, EXPR2) → 특정 값을 NULL로 대체. 1,2 비교해 같다면 NULL을 리턴하고, 다르면 첫 번째 EXPR1 리턴
--연습문제
CREATE TABLE pokemon2 (
  pm_id NUMBER PRIMARY KEY NOT NULL,
  name VARCHAR2(20) NOT NULL,
  attr VARCHAR2(20) DEFAULT 'normal',
	weight NUMBER
);

INSERT INTO pokemon2 VALUES (1, 'Bulbasaur', 'Grass', 30);
INSERT INTO pokemon2 VALUES (4, 'Charmander', 'Fire', 80);
INSERT INTO pokemon2 VALUES (25, 'Pikachu', 'Electric', 15);
INSERT INTO pokemon2 (pm_id, name) VALUES (54, 'Psyduck');
INSERT INTO pokemon2 (pm_id, name, attr) VALUES (76, 'Golem', 'Rock');
INSERT INTO pokemon2 (pm_id, name, weight) VALUES (86, 'Seel', 85);

SELECT name, NULLIF(attr, 'normal') FROM pokemon2;
--> normal은 _로 출력

-- ④COALESCE(EXPR1, EXPR2,...) → 여러 값 중에서 NULL이 아닌 첫 번째 값을 찾을 때

--6) CASE 표현 → 주로 SELECT 문에서, 특정 조건에 따라 다른 결과를 반환하거나 처리할 때

-- ①SIMPLE_CASE_EXPRESSION

-- 연습문제
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_status VARCHAR(50)
);

INSERT INTO orders (order_id, order_status) VALUES (1, '주문 완료');
INSERT INTO orders (order_id, order_status) VALUES (2, '주문 취소');
INSERT INTO orders (order_id, order_status) VALUES (3, '주문 완료');
INSERT INTO orders (order_id, order_status) VALUES (4, '배송 완료');
INSERT INTO orders (order_id, order_status) VALUES (5, '주문 완료');
--
SELECT order_id, order_status,
    CASE
        WHEN order_status = '주문 완료' THEN '배송 준비 중'
        ELSE '배송 완료'
    END AS delivery_status
FROM orders;

-- ②CASE SEARCHED_CASE_EXPRESSION → 다양한 조건에 대한 결과를 설정
--ex)
SELECT name, grade,
    CASE
        WHEN grade >= 90 THEN 'A'
        WHEN grade >= 80 THEN 'B'
        WHEN grade >= 70 THEN 'C'
        WHEN grade >= 60 THEN 'D'
        ELSE 'F'
    END AS grade_letter
FROM Bclass_students;

-- ③DECODE 함수 → 조건을 평가하여 값을 반환. 간단하게 쓰기 좋음.
SELECT employee_id, first_name, last_name,
    DECODE(job_id, 'ST_MAN', 'Store Manager',
                     'SA_MAN', 'Sales Manager',
                     'IT_PROG', 'IT Programmer',
                     'Other Job')
    AS job_title
FROM employees;