2022_04_22_DB_2일

ORACLE_DB

 

4.문자열 함수.sql

--LTRIM(), RTRIM() ,TRIM()  공백 제거
SELECT LTRIM('javascript_java','java') FROM dual;  -- : script_java :왼쪽문자를 제거

SELECT RTRIM('javascript_java','java') FROM dual; -- : javascrtipt_ :오른쪽문자를 제거

SELECT TRIM('  JAVA   ') FROM dual;  --java  :공백 제거 


--REPLACE(기준문자열,기존문자열,새로운문자열) :기존 문자열을 새로운 문자열로 대체할 때 사용
SELECT REPLACE('My dream is a president','president','doctor') FROM dual; 
-- :My dream is a doctor

SELECT REPLACE('My dream is a president',' ','') FROM dual; 
--기존문자열의 공백을 빈 문자열로 대체  :Mydreamisapresident

--내부 부터 해석 
SELECT REPLACE(REPLACE('My dream is a president','president','doctor'),' ','')
FROM dual; -- : Mydreamisadoctor

SELECT REPLACE(CONCAT('hello ','world!'),'!','?')
FROM dual;

--연습 문제 3번
SELECT 
    RPAD(SUBSTR(first_name,1,3),LENGTH(first_name),'*') AS name ,
    LPAD(salary,10,'*') AS salary
FROM employees
WHERE lower(job_id)='it_prog';

--연습문제 1번
SELECT 
    CONCAT(first_name,last_name) AS 이름,
    REPLACE(hire_date,'/','') AS 입사일자 
FROM employees
ORDER BY 이름 ASC;

--연습문제 2번
--강사 : SELECT CONCAT('(02',SUBSTR(phone_number,4,LENGTH(phone_number))) AS Phone_number
SELECT REPLACE(phone_number,SUBSTR(phone_number,1,3),'(02)')
FROM employees;

 

5.숫자날짜함수_0422.sql
--숫자함수 
--ROUND(반올림)  (값,위치)
--원하는 반올림 위치를 매개값으로 지정. 음수를 주는 것도 가능 . 
--양수는 소수점 우측 , 음수는 소수점 좌측
--                                            5에서 반올림
SELECT ROUND(3.1415,2),ROUND(45.923,0),ROUND(45.923,-1) 
FROM dual;

--TRUNC(절사)
--정해진 소수점 자리수까지 잘라냅니다.
SELECT TRUNC(3.1415,3),TRUNC(45.923,0),TRUNC(45.923,-1) 
FROM dual;

--ABS(절대값)
SELECT ABS(-34) FROM dual;

--CEIL(올림),FLOOR(내림)
SELECT CEIL(3.14) ,FLOOR(3.14) FROM dual;

--mod(나머지)
SELECT 10/2 ,mod(10,2) FROM dual;


--날짜 함수 
SELECT sysdate FROM dual;  --(오늘)현재 날짜
SELECT systimestamp FROM dual; --(오늘)현재 날짜 +시간정보 + GMT   

--날짜도 연산이 가능합니다.
SELECT first_name,sysdate-hire_date
FROM employees;

SELECT first_name,hire_date,
(sysdate-hire_date)/7 AS week
FROM employees; --주

SELECT first_name,hire_date,
(sysdate-hire_date)/365 AS year
FROM employees; --년 

--날짜 반올림,절사 
SELECT ROUND(sysdate) FROM dual; -- : 22/04/22

SELECT ROUND(sysdate,'year') FROM dual; --년 기준으로 반올림  :22/1/1

SELECT ROUND(sysdate,'month') FROM dual; --월 기준으로 반올림 :22/05/01 

SELECT ROUND(sysdate,'day') FROM dual;-- 일 기준으로 반올림 (해당 주의 일요일 날짜(일월화수목금토)) 
--:22/04/24


SELECT TRUNC(sysdate) FROM dual; -- : 22/04/22

SELECT TRUNC(sysdate,'year') FROM dual; --년 기준으로 절사  :22/1/1

SELECT TRUNC(sysdate,'month') FROM dual; --월 기준으로 절사 :22/04/01 

SELECT TRUNC(sysdate,'day') FROM dual;-- 일 기준으로 절사 (해당 주의 일요일 날짜(일월화수목금토) :일요일이 기준)
-- :22/04/17

 

6.형변환함수_0422.sql

--형 변환함수 TO_CHAR , TO_NUMBER ,TO_DATE 

--날짜를 문자로 TO_CHAR(값,형식) 
SELECT TO_CHAR(sysdate) FROM dual;
SELECT TO_CHAR(sysdate,'YYYY-MM-DD HH:MI:SS') FROM dual;

SELECT TO_CHAR(sysdate,'YY-MM-DD HH24:MI:ss') FROM dual;

-- " "로 묶어서 표기하면 서식이 아닌 것 출력 가능하다.
--사용하고 싶은 문자를 "" 로 묶어 전달합니다.
SELECT first_name,TO_CHAR(hire_date,'YYYY"년" MM"월" DD"일"')
FROM employees;

--숫자를 문자로 TO_CHAR(값,형식)
SELECT TO_CHAR(20000,'99999') FROM dual; --자리수를 표현하기 위한 기호 9
SELECT TO_CHAR(20000,'9999') FROM dual; --자리수를 표현하기 위한 기호 9 -->표기 못해서 #####
--주어진 자릿수에 숫자를 모두 표기할 수 없어서 모두 #으로 표기됩니다.

SELECT TO_CHAR(20000.21,'99999.99') FROM dual; --소수점은 9의 개수 부족하면 내림 , 많으면 0붙음

SELECT TO_CHAR(20000,'99,999')FROM dual; -- : 20,000


SELECT TO_CHAR(salary, 'L99,999') AS salary
FROM employees;  --지역 화폐 단위 표시 L (시스템이 설정된 나라의 기준) / $ 직접도 가능 

--문자를 숫자로 TO_NUMBER(값,형식)
SELECT '2000'+2000 FROM dual;  -- 자동 형 변환  ::'2000'을 숫자로 자동 형변환 해줌
--(순수한 숫자문자열로만 구성된것만 자동 형 변환가능) 
SELECT TO_NUMBER('2000')+2000 FROM dual; --명시적 형변환 : 위와 동일 한 결과

SELECT '$3,300'+2000 FROM dual; -- 에러 : 순수한 숫자문자열아니여서 에러

SELECT TO_NUMBER('$3,300','$9,999')+2000 FROM dual; -- :5300

--문자를 날짜로 변환하는 함수 TO_DATE(값,형식)
SELECT TO_DATE('2021-11-25') FROM dual; -- 21/11/25 기본 형태

SELECT sysdate-TO_DATE('2021-03-25') FROM dual;-- 문자를 날짜로 변환해야 연산이 가능 

SELECT TO_DATE('2020/12/25','YY-MM-DD') FROM dual;

--문자를 날짜 형태로 변경할 때에는 주어진 문자열을 모두 변환을 해야 한다 
--날짜만 추출 불가능('YYYY-MM-DD')만쓰면 오류 
--주어진 문자열을 모두 변환해야 합니다.
SELECT TO_DATE('2021-03-31 12:24:50','YYYY-MM-DD  HH:MI:SS') FROM dual;


SELECT TO_CHAR(TO_DATE('20050102','YYYY/MM/DD'),'YYYY"년" MM"월" DD"일"')AS dateInfo
FROM dual;

--NULL 제거 함수 NVL(컬럼,변환할 타겟값) ::컬럼 전체를 바꿈 
SELECT null FROM dual;

SELECT NVL(null,0) FROM dual;

SELECT first_name,NVL(commission_pct,0) AS comm_pct
FROM employees;

--NULL 제거함수 NVL2(컬럼,null이 아닐 경우 값 ,null일 경우 값)
SELECT NVL2(50,'널아님','널임') FROM dual;
SELECT NVL2(null,'널아님','널임') FROM dual;

SELECT first_name,NVL2(commission_pct,'true','false') 
FROM employees;

SELECT
    first_name,
    commission_pct,
    NVL2(commission_pct,salary+(salary*commission_pct),salary) AS real_salary
FROM employees;

--DECODE(컬럼 혹은 표현식 , 항목1,결과1,항목2,결과2........default)
SELECT DECODE('A','A','A입니다','B','B입니다','C','C입니다','모르겠는데요~')
FROM dual;

SELECT
    job_id,
    salary,
    DECODE(job_id,'IT_PROG',salary*1.1,'FI_MGR',salary*1.2,'AD_BP',salary*1.3,salary) 
    AS result
FROM employees;

--CASE WHEN THEN END
SELECT 
    first_name,
    job_id,
    salary,
    (CASE job_id
        WHEN 'IT_PROG' THEN salary*1.1
        WHEN 'FI_MGR' THEN salary*1.2
        WHEN 'FI_ACCOUNT' THEN salary*1.3
        WHEN 'AD_BP' THEN salary*1.4
        ELSE salary
    END) AS result
FROM employees;


--문제1 
SELECT 
    employee_id AS 사원번호, 
    CONCAT(first_name,last_name) AS사원명,
    hire_date AS 입사일자,
    TRUNC((sysdate-hire_date)/365) AS 근속년수
FROM employees
WHERE (sysdate-hire_date)/365>=15  -- SQL 실행 순서 때문에 '근속년수'라는 별칭 사용 불가
ORDER BY 근속년수 DESC;
--문제2 

SELECT 
    first_name,
    manager_id,
    DECODE(manager_id,100,'사원',120,'주임',121,'대리',122,'과장','임원')  AS 직급
FROM employees
WHERE department_id=50;

6-1.집합연산자_0422.sql
--집합 연산자
--UNION(합집합  -중복 X)  , UNION ALL (합집합 -중복O) , INTERSECT(교집합),MINUS(차집합)
--위 아래 column 개수가 정확히 일치해야 집합연산자가 정상적으로 동작함.


SELECT employee_id,first_name
FROM employees
WHERE hire_date  LIKE '04%'
UNION   -- 두개의 SLECT문의 합집합 연산=UNION :중복 불가  을 수행
SELECT 
    employee_id,first_name
FROM employees
WHERE department_id=20;



SELECT employee_id,first_name
FROM employees
WHERE hire_date  LIKE '04%'
UNION ALL   -- 두개의 SLECT문의  UNION ALL연산:중복 허용 수행
SELECT 
    employee_id,first_name
FROM employees
WHERE department_id=20;

SELECT employee_id,first_name
FROM employees
WHERE hire_date  LIKE '04%'
INTERSECT   -- 두개의 SLECT문의  INTERSECT 연산:교집합 수행
SELECT 
    employee_id,first_name
FROM employees
WHERE department_id=20;


SELECT employee_id,first_name
FROM employees
WHERE hire_date  LIKE '04%'
MINUS   -- 두개의 SLECT문의  MINUS 연산:차집합 수행
SELECT 
    employee_id,first_name
FROM employees
WHERE department_id=20;

7.그룹함수(GROUP BY,HAVING)_0422.sql
--그룹 함수 AVG ,MAX , MIN , SUM , COUNT  :기준(즉 대상)이 있어야 그룹 함수 사용

SELECT 
    AVG(salary),
    MAX(salary),
    MIN(salary),
    SUM(salary),
    COUNT(salary)
FROM employees;  -- 그룹화를 하지 않으면 , 테이블 전체를 대상 

--COUNT(컬럼 값): 이면 NULL값을 제외하고 개수 파악 
SELECT COUNT(*) FROM employees; --전체 컬럼의 수 :107 | 총 행의 데이터의 수 
SELECT COUNT(first_name) FROM employees; --first_name의 컬럼의 수 :107 | null이 아닌 행의 수 
SELECT COUNT(commission_pct) FROM employees; --commission_pct의 컬럼의 수 :35 | null이 아닌 행의 수  
SELECT COUNT(manager_id) FROM employees; --manager_id의 컬럼의 수 :106 |null이 아닌 행의 수 


--부서별로 그룹화 , 그룹함수의 사용
SELECT
    department_id,
    AVG(salary)
FROM employees
GROUP BY department_id; 
--그룹 함수만 단독적으로 사용에 문제가 없으나(department_id 없이 AVG만) , 
--그룹함수만 단독으로 사용하지 않을 시(그룹함수와 다른거와 함께 사용시) 그룹을 형성하지 않으면 오류발생
--주의할 점
--그룹 함수는 그룹화를 하지 않고 일반 컬럼과 동시에 그냥 출력할 수 없습니다.
SELECT
    department_id,
    AVG(salary)
FROM employees ;--에러 발생
-- 그룹화 하지 않고 일반 컬럼과 동시에 사용해서 에러 발생 

SELECT 
    job_id,
    department_id,
    AVG(salary)
FROM employees
GROUP BY department_id;--에러발생 
--GROUP BY절을 사용할 때 GROUP절에 묶이지 않으면 다른 컬럼을 조회할 수 없다 

--GROUP BY절 2개 이상 사용
SELECT 
    job_id,
    department_id,
    AVG(salary)
FROM employees
GROUP BY department_id,job_id
ORDER BY department_id; 


SELECT 
    department_id,
    SUM(salary)
FROM employees   
GROUP BY department_id
HAVING SUM(salary)>100000; 
--그룹화 한 이후에는 조건식 사용하려면 WHERE대신 HAVING을 사용해야 한다(SQL 실행순서때문에)

SELECT
    job_id,
    COUNT(*)
FROM employees
GROUP BY job_id
HAVING COUNT(*)>=20;

--부서 아이디가 50이상인 것들을 그룹화 시키고 ,그룹 월급 평균 중 5000이상만 조회
SELECT 
    department_id,
    AVG(salary)
FROM employees
WHERE department_id>=50
GROUP BY department_id
HAVING AVG(salary)>=5000
ORDER BY department_id ASC;