SELECT * FROM empwhere job = 'PRESIDENT';
SELECT * FROM emPwhere job = 'PRESIDENT';
SELECT * FROM EMP WHERE JOB = 'PRESIDENT';

SELECT * FROM empwhere job = ‘PRESIDENT’;
SELECT * FROM empwhere job = ‘president’;  # 다름

SELECT ENAME, SAL+12, SAL*12 as annual_salary FROM EMP;

SELECT ENAME, MGR Manager,SAL*12 as annual_SAL,COMM+300 "Special Bonus" FROM EMP;
-- 컬럼 Alias3가지 표현방식(공백문자, AS,“~”)중 가장 명료한 방식은?
 , “~”은 특수문자,공백문자,대소문자 구분이 필요한 경우 사용

SELECT ENAME, COMM+300 보너스, COMM+300 AS "Special Bonus" FROM EMP;

SELECT ENAME||JOB FROM EMP;
-- | : 수직선 기호(Vertical bar), 파이프 문자 , OR 연산

SELECT DNAME||' 부서는'||LOC||' 지역에 위치합니다.' as LOC FROM DEPT;
-- ACCOUNTING 부서는NEW YORK 지역에 위치합니다.
-- 명목형 자료를 문장으로

SELECT ename||'''s JOB is '||job as job_list FROM emp;
-- SMITH's JOB is CLERK
-- 홑따옴표(＇) = 단일인용부호 = single quotation mark
-- Oracle DBMS 문자 데이터 표현 : 'Happy New Year'
-- SCOTT'sJOB is ANALYST vs SCOTT"sJOB is ANALYST

SELECT sal, sal*100, sal|| '00', to_char(sal)||'00' FROM EMP;
-- sal||'00' : 숫자 타입과 문자타입 결합, 결과 타입은?
-- Data type Conversion
	Implicit Conversion(암시적, 자동으로) ex) sal|| '00'
	Explicit Conversion(명시적, 수동으로) ex) to_char(sal) || '00'
-- 좋은방식의 SQL Coding은?

desc dual

SELECT * FROM DUAL;
-- DUMMY
-- X

SELECT sysdate FROM dual; // system의 현재 date(날짜와 시간)을리턴하는 함수
-- SYSDATE
-- 25/07/18
-- 

SELECT 2025*12345, to_char(2025*12345,'999,999,999'), 
to_char(2025*12345,'999,999,999') as cal FROM dual;

SELECT 2025*12345, to_char(2025*12345,'999,999'), 
to_char(2025*12345,'999,999,999,999,999') as cal FROM dual;

SELECT TO_CHAR(TO_DATE('0207','MM/YY'), 'MM/YY') FROM DUAL;
SELECT TO_CHAR (TO_DATE('02#07','MM/YY'), 'MM/YY') FROM DUAL;
SELECT TO_CHAR(TO_DATE('02/07', 'fxmm/yy'), 'mm/yy') FROM DUAL;

TO_CH
-----
02/07

SELECT TO_CHAR(TO_DATE('0207', 'fxmm/yy'), 'mm/yy') FROM DUAL;
SELECT TO_CHAR(TO_DATE('0207', 'fxmm/yy'), 'mm/yy') FROM DUAL;
                       *
ERROR at line 1:
ORA-01861: literal does not match format string

SELECT TO_CHAR(TO_DATE('27-OCT-98', 'DD-MON-YY'), 'YYYY') "Year" FROM DUAL;

Year
----
2098

SELECT TO_CHAR(TO_DATE('27-OCT-98', 'DD-MON-RR'), 'YYYY') "Year" FROM DUAL;

Year
----
1998

SELECT * FROM EMP WHERE DEPTNO = 10;

SELECT DEPTNO, ENAME, SAL, JOB FROM EMP WHERE SAL > 2000;
SELECT DEPTNO, ENAME, SAL, JOB FROM EMP WHERE DEPTNO = 10 AND SAL > 2000;
SELECT DEPTNO, ENAME, SAL, JOB FROM EMP WHERE DEPTNO = 10 OR SAL > 2000;

SELECT DEPTNO, SAL, JOB FROM EMP WHERE DEPTNO = 10 AND SAL > 2000 OR JOB='MANAGER';
SELECT DEPTNO, SAL, JOB FROM EMP WHERE (DEPTNO = 10 AND SAL > 2000) OR JOB='MANAGER';
-- AND > OR -> 동일
SELECT DEPTNO, SAL, JOB FROM EMP WHERE DEPTNO = 10 AND (SAL > 2000 OR JOB='MANAGER');
-- 연산자 우선순위 ? , 좋은 방식의 SQL 코딩?

SELECT DEPTNO, ENAME, SAL, JOB FROM EMP WHERE JOB = 'manager';
-- none, data case-sesitive

SELECT DEPTNO,ENAME,JOB FROM EMP WHERE 1=1;
SELECT DEPTNO,ENAME,JOB FROM EMP WHERE 1=2;
-- 1=1 : True, 1=2 : False
SELECT DEPTNO, ENAME, SAL, JOB FROM EMP WHERE (DEPTNO,JOB,MGR) = ((10,'MANAGER',7839));

-- 연산불가
SELECT 300/0 FROM dual; // “divisor is equal to zero"
SELECT 300+400, 300+NULL, 300/NULL FROM dual; // NULL 연산 결과는?
SELECT ENAME, SAL, COMM, COMM+SAL*0.3 as bonus FROM EMP; // 실수하기 쉬운…해결책은….?

-- 비교불가
SELECT ENAME,SAL,COMM FROM EMP WHERE COMM > -1; // null이 있는column 비교연산
SELECT ENAME,SAL,COMM FROM EMP WHERE COMM = null;
SELECT ENAME,SAL,COMM FROM EMP WHERE COMM <> null;

SELECT ENAME,SAL,COMM FROM EMP WHERE COMM is null;
SELECT ENAME,SAL,COMM FROM EMP WHERE COMM is not null;

-- COMM : null data 포함 table
SELECT ENAME, length(ENAME), COMM, length(COMM) FROM EMP;
SELECT sal, comm, abs(sal-comm)+300 FROM emp;

SELECT COMM, NVL(COMM,0), DECODE(COMM, NULL, 0, COMM) AS NVL_SIMUL FROM EMP;
SELECT concat('Commission is ',COMM), 'Commission is '||COMM FROM EMP;--NULL 무시
select count(sal) as sal_cnt, count(comm) as comm_cnt, sum(comm) as comm_sumfrom emp;
-- 단일행 함수(Single Row Function) ex) length, abs
-- 그룹행 함수(Group Row Function) ex) count, sum

SELECT ENAME, HIREDATE, SAL, COMM FROM EMP ORDER BY ENAME ; // 정렬방향? Default
SELECT ENAME, HIREDATE, SAL, COMM FROM EMP ORDER BY ENAME asc; // SQL 가독성

SELECT ENAME, HIREDATE, SAL, COMM FROM EMP ORDER BY HIREDATE desc; // 날짜
SELECT ENAME, HIREDATE, SAL, COMM FROM EMP ORDER BY ENAME; // column name
SELECT ENAME, HIREDATE, SAL, COMM FROM EMP ORDER BY 2; // column position
SELECT ENAME, SAL*12 as 연봉 FROM EMP ORDER BY 연봉; // column alias
SELECT ENAME, HIREDATE, SAL, COMM FROM EMP ORDER BY COMM * 12; // expression, NULL 위치
SELECT ENAME, HIREDATE, SAL, COMM FROM EMP ORDER BY COMM * 12 NULLS FIRST;

SELECT DEPTNO,JOB,ENAME FROM EMP ORDER BY DEPTNO;
SELECT DEPTNO,JOB,ENAME FROM EMP ORDER BY DEPTNO,JOB; // 조합의 순서쌍
SELECT DEPTNO,JOB,ENAME FROM EMP ORDER BY DEPTNO,JOB desc; // 차이점은?

SELECT JOB FROM EMP;//Row개수 만큼,
SELECT UNIQUE JOB FROM EMP;//직군 종류,Oracle
SELECT DISTINCT JOB FROM EMP;//ANSI

SELECT DISTINCT DEPTNO,JOB FROM EMP;//중복 데이터(?)
-- 컬럼(들)의 조합의 중복 필터링
SELECT JOB FROM EMP ORDER BY JOB;
-- distinct연산 내부 알고리즘: Oracle9i:Sort,10g:Hash

SELECT DISTINCT JOB,DISTINCT DEPTNO FROM EMP;//error, 범위?
SELECT JOB, DISTINCT DEPTNO FROM EMP;//error, 위치?

SELECT COMM FROM EMP WHERE COMMIS NOT NULL;// 4Rows
SELECT DISTINCT COMM FROM EMP;//5Rows??
-- **NULL과 DISTINCT

SELECT ENAME,SAL,HIREDATE FROM EMP WHERE SAL between 1000 and 2000; // 숫자타입
SELECT ENAME,SAL,HIREDATE FROM EMP WHERE SAL >= 1000 and SAL <= 2000; // 차이점?
SELECT ENAME,SAL,HIREDATE FROM EMP WHERE SAL between 2000 and 1000; // 이유는?

SELECT ENAME,SAL,HIREDATE FROM EMP WHERE ENAME BETWEEN 'C' AND 'K’; -- 문자 타입

SELECT ENAME,SAL,HIREDATE FROM EMP 
WHERE HIREDATE BETWEEN '81/02/20' AND '82/12/09’; // 날짜 타입, 형변환

SELECT ENAME,SAL,HIREDATE FROM EMP
WHERE HIREDATE 
BETWEEN to_date('81/02/20','yy/mm/dd') AND to_date('82/12/09','yy/mm/dd');
// 날짜 타입, 명시적형변환, 검색이 안되는 이유는 ?

SELECT ENAME,HIREDATE,
TO_CHAR(HIREDATE,'YYYY/MM/DD'),
TO_CHAR(HIREDATE,'YYYY/MM/DD HH24:MI:SS'), // Date에서 시간정보확인
TO_CHAR(HIREDATE,'RRRR/MM/DD HH24:MI:SS'),
TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS')// sysdate에서 시간정보확인
FROM EMP;

SELECT ENAME,SAL,HIREDATE FROM EMP
WHERE HIREDATE 
BETWEEN to_date('81/02/20', 'rr/mm/dd') AND to_date('82/12/09', 'yy/mm/dd');

SELECT ENAME,SAL,HIREDATE FROM EMP
WHERE HIREDATE 
BETWEEN to_date('1981/02/20','yyyy/mm/dd') AND to_date('1982/12/09','yyyy/mm/dd');

SELECT ENAME,SAL,HIREDATE FROM EMP
WHERE HIREDATE 
BETWEEN to_date('2081/02/20','yyyy/mm/dd') AND to_date('2082/12/09','yyyy/mm/dd');

SELECT ENAME FROM EMP WHERE ENAME like 'A%'; // pattern matching
SELECT ENAME FROM EMP WHERE ENAME like '_A%';
SELECT ENAME FROM EMP WHERE ENAME like '%L%E%';
SELECT ENAME FROM EMP WHERE ENAME like '%LE%';
SELECT ENAME FROM EMP WHERE ENAME like '%A%';
SELECT ENAME FROM EMP WHERE ENAME NOT like '%A%';

SELECT ENAME,HIREDATE FROM EMP WHERE HIREDATE like '81%'; // 날짜 , 암시적 형변환
SELECT ENAME,SAL FROM EMP WHERE SAL like 2%; // error, 숫자
SELECT ENAME,SAL FROM EMP WHERE SAL like '2%'; // 암시적 형변환
SELECT ENAME,SAL FROM EMP WHERE TO_CHAR(SAL) like '2%'; // 명시적 형변환

SELECT EMPNO, JOB FROM EMP WHERE EMPNO IN (7369,7521,7654);// 숫자

SELECT EMPNO, JOB FROM EMP 
WHERE EMPNO = 7369 or EMPNO = 7521 or EMPNO=7654; // 차이점 ?

SELECT EMPNO,ENAME,JOB FROM EMP WHERE JOB IN ('clerk','manager'); // 문자

SELECT EMPNO,ENAME,HIREDATE FROM EMP 
WHERE HIREDATE IN ('81/05/01','81/02/20'); // x, yyyy 형식

SELECT EMPNO,ENAME,JOB,DEPTNO FROM EMP
WHERE (JOB,DEPTNO) in (('MANAGER',20),('CLERK',20));// 다중컬럼리스트, in (1..1000)

SELECT DEPTNO, ENAME, DECODE(DEPTNO,10,'ACCOUNTING',20,'RESEARCH',30,'SALES','ETC')
FROM EMP
ORDER BY DEPTNO;

if deptno = 10 then 'ACCOUNTING'
else if deptno =20 then 'RESEARCH'
else if deptno =30 then 'SALES'
else 'ETC'

SELECT DEPTNO,ENAME, -- Simple case
	CASE DEPTNO WHEN '10' THEN 'ACCOUNTING' // 암시적(x), 에러 원인? 수정후 실행
							WHEN 20 THEN 'RESEARCH'
							WHEN 30 THEN 'SALES'
							ELSE 'ETC'
	END AS DEPARTMENT
FROM EMP
ORDER BY DEPTNO;

SELECT DEPTNO, ENAME,
	DECODE(DEPTNO,10,'ACCOUNTING',20,'RESEARCH',30,'SALES','ETC')
FROM EMP
ORDER BY DEPTNO ;

SELECT DEPTNO, ENAME, SAL, -- Searched case
	CASE WHEN SAL >= 4800 THEN 'HIGH' // 비교연산자
			WHEN SAL BETWEEN 3000 AND 4799 THEN 'MID' // SQL 연산자
			WHEN SAL >= 1000 AND SAL <=2999 THEN 'LOW' // 비교 & 논리 연산자
	ELSE 'Passion pay'
	END SAL_GRADE
FROM EMP
ORDER BY DEPTNO;

SELECT DEPTNO, ENAME, COMM,
	CASE WHEN COMM >= 1000 THEN 'Great'
			WHEN COMM >= 500 THEN 'Good'
			WHEN COMM >= 0 THEN 'Bad'
	ELSE 'Dreadful’ // ELSE에서 NULL Catch
	END COMM_GRADE
FROM EMP
ORDER BY DEPTNO;

SELECT DEPTNO, JOB, ENAME, SAL, NVL(comm,0) as comm_0, (SAL+ NVL(comm,0)) as totalsal, 
	CASE DEPTNO WHEN 10 THEN 0.003*(SAL+ NVL(comm,0))
			WHEN 20 THEN 0.2*(SAL+ NVL(comm,0))
			WHEN 30 THEN 0.1*(SAL+ NVL(comm,0))
	ELSE 0.01*(SAL+ NVL(comm,0))
	END BONUS
FROM EMP
ORDER BY DEPTNO, bonus desc;