--1. varchar2
--2. select name, position, sal from professor
--3. select deptno, name, userid from student
--4. select profno, name, position from professor
--5. select * from student where name like '%진'
--6. select ename||': month salary = '||sal from emp
--7. select ename, sal*12 as "Annual Salary" from emp
--8. select empno, ename, job, sal from emp where sal between '1500' and '5000' and (job not in ('pregident','ANALYST')) order by empno, ename, job, sal;
--INITCAP : 첫번째 대문자
--select initcap(필드명) from 테이블명 where 필드명 ='값'
--select 필드명 from 테이블명 where initcap(필드명) ='값'
--UPPER : 대문자 변환 / LOWER : 소문자 변환
--select upper(필드명), lower(필드명) from 테이블명 where 필드명 = '값';
--LENGTH : 길이 / LENGTHB : 바이트 길이
--select length(필드명), lengthb(필드명) from 테이블
--바이트 확인 : SELECT * FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET';
--※KO16KSC5601 : 한글 완성형 2,350자지원 글자당 2Byte
--※UTF8 : 한글 11,172자 글자당 3바이트 (편집됨)
--CONCAT : select concat(concat(필드명,' 값 '), 필드명) from 테이블명;
--SUBSTR : select substr(필드명, 시작값, 길이) from 테이블명 where 조건 and substr(필드명,시작값,길이) = '값';
--INSTR : 특정값이 위치한 곳 조회
--select instr(필드명,'값') from 테이블
--LPAD : 왼쪽에 특수문자 삽입 / RPAD : 우측에 특수문자 삽입
--select lpad(필드명,길이,'값'), rpad(필드명,길이,'값') from 테이블명
--LTRIM : 왼쪽 삭제 / RTRIM : 우측삭제
--select LTRIM(필드명,'지울값'), RTRIM(필드명,'지울값') from dual
--ROUND : 지정자리 n+1에서 반올림
--select
--name, sal, sal/22, round(sal/22), round(sal/22,2), round(sal/22,-1) from professor where deptno = '101'
--TRUNC : 지정한 소수점 자리수 이하를 절삭한 결과 값을 반환
--select name, sal, sal/22, TRUNC(sal/22), TRUNC(sal/22,2), trunc(sal/22,-1) from professor where deptno = '101'
--MOD : 나머지 구하기 / select 필드명, 필드명, mod(필드명 또는 값, 필드명 또는 값) from 테이블
--ceil : 지정한 숫자보다 크거나 같은 정수 중에서 최소 값 / select ceil(19.7), floor(12.32) from dual
--날짜 연산 : select hiredate, hiredate +30, hiredate+60 from professor where 조건절;
-- MONTH_BETWEEN select profno, hiredate, MONTHS_BETWEEN(sysdate,hiredate) TENURE, ADD_months(hiredate,6) REVIEV from professor where MONTHS_BETWEEN(SYSDATE,HIREDATE)<365;
--현재일기준 마지막일, 또는 요일 일자 조회 / select sysdate, last_day(sysdate), next_day(sysdate,'일') from dual;
--날짜 절삭 / select to_char(sysdate, 'YY/MM/DD HH24:MI:SS') NORMAL,
-- to_char(trunc(SYSDATE), 'YY/MM/DD HH24:MI:SS') TRUNC,
-- to_char(round(sysdate), 'YY/MM/DD HH24:MI:SS') round from dual
--날짜 올림 / select 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 where deptno = 101;
--형변환 (오라클은 묵시적으로 데이터를 형변환함)
--select sysdate, to_char(sysdate,'MM') as MONTH from dual
--select sysdate, concat(to_char(sysdate,'MM'),'월') as MONTH from dual
--select sysdate, to_char(sysdate,'MM') as MM, concat(substr(to_char(sysdate,'MM'),2,1),'월') as MONTH from dual
--select name, to_char(birthdate,'MM')as 생일, concat(to_char(birthdate,'Q'),'/4') as 분기 from student
--select name, grade, to_char(birthdate, 'DAY MONTH DD, YYYY') from student where deptno = '102';
--select name, TO_CHAR(hiredate, 'MONTH DD, YYYY HH24:MI:SS PM') HIREDATE FROM professor where deptno = 101;
--select name, position, TO_CHAR(hiredate, 'Mon "the" DDTH "of" YYYY') hiredate from professor where deptno = 101;
--select name, sal, comm, to_char((sal*comm)*12,'9,999,999') anual_sal from professor where comm is not null;
--SELECT TRUNC(SYSDATE - TO_DATE('1990-06-01', 'YYYY-MM-DD'), 0) AS days_difference, round(MONTHS_BETWEEN(SYSDATE, TO_DATE('1990-06-01', 'YYYY-MM-DD'))) AS months_difference FROM dual;
--select TO_CHAR(TO_DATE(substr(idnum,1,6),'YYMMDD'), 'YY-MM-DD') Birhday from student;
--select name, position, sal, comm, sal*nvl(comm,0) as s1, nvl(sal+comm, sal) as s2 from professor where deptno = 201
--형변환 : select name, nvl(to_char(comm), 'No comm') as comm from professor;
--NUllif : NULLIF 함수는 두 개의 표현식을 비교하여 값이 동일하면 NULL을 반환하고,일치하지 않으면 첫 번째 표현식의 값을 반환 / select name, lengthb(substr(name,1,2)), lengthb(userid), nullif(lengthb(substr(name,1,2)),lengthb(userid)) as "null if" from professor
--colsce : NULL 이아닌 첫번째 값 출력 / select name, comm, sal, coalesce(comm,sal,0) as s1 from professor
--decode / select name, deptno, decode(deptno, '101','컴공과','102','멀티미디어학과','201','전자공학과','기계공학과') from professor
--case when 조건 : SELECT name, deptno, sal, CASE WHEN deptno = 101 THEN sal * 0.1, when deptno = 102 then sal*0.2, when deptno = 201 then sal*0.3, when deptno = 202 then sal*0.4, ELSE sal END AS calculated_salary FROM professor;
--select max(sal) MAX, min(sal) MIN, sum(sal) SUM, trunc(avg(sal),1) AVG from emp
--group by : select deptno, avg(sal) from professor group by deptno;
--Roll up : select deptno, sum(sal) as 합계 from professor group by ROLLUP(DEPTNO);
--elect deptno, position, count(*) from professor group by cube(deptno, position);
--**언어변경 : ALTER SESSION SET NLS_LANGUAGE = KOREAN;
--조건문
--1. select ename, sal from emp where sal >= '2550'
--between, in 예제
--2. select ename, sal from emp where (sal between '1500' and '5000') and job not in ('PREGIDENT', 'ANALYST') order by empno, ename,job, sal
--Case예제
--3. SELECT ename, CASE
-- WHEN comm IS NULL THEN 200
-- WHEN comm = 0 THEN 200
-- ELSE comm
-- END AS adjusted_comm
-- FROM
-- emp;
--Decode 예제
--4. select deptno, job, count(*) as job, sum(sal) as "Total sum" from emp group by rollup(deptno, job)
--Decode 예제
--5. select
--count(*) TOTAL,
--count(decode(substr(hiredate,1,2),80,1)) "1980",
--count(decode(substr(hiredate,1,2),81,1)) "1981",
--count(decode(substr(hiredate,1,2),82,1)) "1982",
--count(decode(substr(hiredate,1,2),83,1)) "1983",
--count(decode(substr(hiredate,1,2),87,1)) "1987"
--from emp
--6.select ename, to_char(hiredate, 'Month DD, YYYY, DAY') from emp order by to_char(hiredate-1, 'D') asc;
