inner join

--JOIN 여러테이블을 하나의 테이블 처럼 사용
--1) 내부 JOIN ( inner join)  : 여러 개의 테이블에서 공통된 부분만 추출 
--    등가조인 : 두개의 열이 일치할 때 값 추출
--    비등가조인 : 범위에 해당할 때 값 추출

--2) 외부 JOIN (outer join) :
--    left outer join
--    right outer join 
--    full outer join 

--cross join  : 나올수 있는 모든 조합을 출력 ( 잘 사용 안함)
select *
from emp , dept
order by empno;

-- inner join 
select e.empno , e.ename , d.deptno , d.dname , d.loc
from emp e, dept d
where  e.deptno = d.deptno;

--sql-99 표준
--join ~ on
select e.empno , e.ename , d.deptno , d.dname , d.loc
from emp e join dept d on e.deptno =  d.deptno;

-- 급여가 2500 이하 이고 , 사원번호가 9999 이하인 사원 정보 조회
select e.empno , e.ename , d.deptno , d.dname , d.loc
from emp e, dept d
where e.deptno = d.deptno and sal < 2500 and empno < 9999;

-- emp , salgrade 조인
-- 일치하는 필드가 없기때문에 losal , hisal 범위에 들어가는 형태로 조인

select *
from emp e, salgrade s
where e.sal between s.losal and s.hisal;

outer join 

-- outer join 
-- left outer join 오른쪽에 (+)
select  e1.ename , e1.empno  , e2.empno as mgrempno , e2.ename as  mgremname
from emp e1 , emp e2
where e1.mgr = e2.empno(+);

--join ~on
select  e1.ename , e1.empno  , e2.empno as mgrempno , e2.ename as  mgremname
from emp e1 left outer join  emp e2 on  e1.mgr = e2.empno;

-- right outrer join 왼쪽에(+)
select  e1.ename , e1.empno  , e2.empno as mgrempno , e2.ename as  mgremname
from emp e1 , emp e2
where e1.mgr(+) = e2.empno;

--join ~ on
select  e1.ename , e1.empno  , e2.empno as mgrempno , e2.ename as  mgremname
from emp e1 right outer join  emp e2 on  e1.mgr = e2.empno;

select  e1.ename , e1.empno  , e2.empno as mgrempno , e2.ename as  mgremname
from emp e1 full outer join  emp e2 on  e1.mgr = e2.empno;

-- 형 변환 함수 : 자료형을 형 변환
-- NUMBER , VARCHAR2 , DATE
select empno , ename , empno+'500'
from emp
where ename  = 'FORD';

-- TO_CHAR() : 숫자 , 날짜 데이터를 문자로 변환
-- TO_NUMBER() 문자 데이터를 숫자로 변환
-- TO_DATE() 문자 데이터를 날짜 데이터로 변환

--TO_CHAR
select 
to_char(sysdate , 'yyyy/mm/dd hh24:mi:ss') as 현재날짜시간  -- 문자열 형태여야 시분초까지 표시 가능
from dual;

--sal 필드에 , 나 통화표시 하고 싶다면?
select  to_char(sal , '$999,999') , to_char(sal,'L999,999')  -- L 은 local 서버가 설치된곳 언어 기준으로 통화 표시
from emp;

select sysdate , to_char(sysdate, 'yyyy/mm/dd') as 현재날짜,
to_char(sysdate,'yyyy') as 현재년도,
to_char(sysdate,'mm') as 현재월,
to_char(sysdate,'dd') as 현재일,
to_char(sysdate,'ddd') as 현재일2 -- 365일 기준 현재 몇일에 해당하는지 표시 (1/1 기준 D+날짜)
from dual;

--TO_NUMBER(문자열데이터 , 인식될 숫자형태)
select to_number('1,300','999,999') - to_number('1,500','999,999') 
from dual;


--TO_DATE(문자열 데이터 , '인식될 날짜 데이터 포맷')
select to_date ('2018-07-14' , 'yyyy-mm-dd') as todate1 , to_date('20230320' , 'yyyy-mm-dd') as todate2
from dual;

-- 오라클 함수
-- 오라클에서 기본으로 제공하는 내장 함수 , 사용자가 필요에 의해 직접 정의한 사용자 정의 함수

-- 문자열 함수
-- 1.UPPER , LOWER , INITCAP

--UPPER 는 모두 대분자 , LOWER는 모두 소문자 , INITCAP 첫글자만 대문자
SELECT 
    ENAME , 
    UPPER(ENAME) , 
    LOWER(ENAME) , 
    INITCAP(ENAME)
FROM 
    EMP;

--2.LENGTH : 문자열 길이
SELECT 
    ENAME, LENGTH(ENAME)
FROM 
    EMP;
    
-- 사원 이름의 길이가 5 이상인 사원 조회
SELECT ENAME
FROM EMP
WHERE LENGTH(ENAME) >= 5;

--한글일 때
SELECT LENGTH('훈민정음')
FROM DUAL; -- SYS가 소유하는 테이블 ( 임시 연산이나 함수의 결과 값 확인 용도 ) 

--LENGTHB  BYTE단위로 세어줌
SELECT LENGTHB('한글')
FROM DUAL;

--3. SUBSTR(문자열 데이터 , 시작위치 , 추출길이) : 추출길이 생략 가능
--     문자열 일부 추출
SELECT JOB, SUBSTR(JOB, -3) 
FROM EMP ;

-- ENAME 출력 세번째 글자부터 출력
SELECT SUBSTR(ENAME,3)
FROM EMP;

--4.INSTR : 문자열 데이터 안에서 특정 문자 위치 찾기
--INSTR(대상문자열 , 위치를 찾으려는 문자열 , 대상문자열에서 찾기를 시작할 위치(선택사항),시작위치에서 찾으려는 문자가 몇번째인지 지정)

SELECT 
    INSTR( 'HELLO, ORACLE' , 'L') AS INSTR_1 , 
    INSTR('HELLO, ORACLE', 'L', 2 , 2) AS INSTR_2
FROM DUAL;

--5. REPLACE : 특정 문자를 다른 문자로 변경
-- REPLACE(문자열데이터 , 찾는문자 , 변경문자 )
SELECT '010-1234-5678' AS 변경전 , REPLACE('010-1234-5678' , '-', ' ')AS 변경후
FROM DUAL;

SELECT ' 이것이 ORACLE 이다 ' AS 변경전 , REPLACE(' 이것이 ORACLE 이다' , '이것이', 'THIS IS') AS 변경후
FROM DUAL;

-- 6.CONCAT : 두 문자열 데이터 합치기
SELECT CONCAT(EMPNO , ENAME)
FROM EMP;

-- || : 문자열 연결 연산자


-- 7. TRIM , LTRIM , RTRIM : 공백 포함 특정 문자 제거

--EMP 테이블에서 사원이름이 S로 끝나는 사원 데이터 조회
SELECT *
FROM EMP
WHERE ENAME LIKE '%S' ;

--EMP 테이블에서 30번 부서에 근무하는 사원 중에서 직책이 SALESMAN인 사원의
--사원번호 ,이름 급여 조회 (SAL 내림차순)

SELECT EMPNO,ENAME,SAL
FROM  EMP
WHERE DEPTNO = 30 AND JOB = 'SALESMAN';

--EMP 테이블을 사용하여 20,30번 부서에 근무하고 있는 사원중 급여가 2000 초과인 사원
--사원번호,이름,급여,부서조회
--집합 연산자를 사용하는 방식과 사용하지 않는 방식 두가지

--1)
SELECT EMPNO , ENAME , SAL, DEPTNO
FROM EMP
WHERE DEPTNO IN (20,30) AND SAL > 2000;

--2)
SELECT EMPNO , ENAME , SAL, DEPTNO
FROM EMP
WHERE DEPTNO = 20  AND SAL >2000
UNION
SELECT EMPNO , ENAME , SAL, DEPTNO
FROM EMP
WHERE DEPTNO = 30 AND SAL > 2000;

--사원 이름에 E가 포함되어 있는 30번 부서 사원 중 급여가 1000~2000 사이가 아닌 사원의 이름
--사원번호,급여, 부서번호를 조회하기

SELECT ENAME , SAL , EMPNO , DEPTNO
FROM EMP
WHERE ENAME LIKE '%E%'  AND
SAL NOT BETWEEN 1000 AND 2000;

--추가 수당이 존재하지 않으며 , 상급자가 있고 직책이 MANAGER , CLERK 인 사원 중에서 사원 이름의
--두번째 글자가 L 이 아닌 사원의 정보조회

SELECT *
FROM  EMP
WHERE 
(COMM IS NULL AND MGR IS NOT NULL) 
AND JOB IN('MANAGER' ,'CLERK')
AND ENAME NOT LIKE '_L%';

-- 연산자 활용 where 문
-- 산술연산자 : + , - , * / mod(나머지 - 표준x  오라클에서만 제공)
-- 비교연산자 : > < <= >=
-- 등가 비교연산자 : = ,같은의미( != , <> , ^=   )
-- 논리부정 연산자: NOT
-- IN연산자
-- BETWEEN A AND B 연산자
-- LIKE 연산자와 와일드 카드 (_ , %)
-- IS NULL 연산자
-- 집합 연산지 : UNION (합집합 - 중복제거 ) , UNION ALL(합집합 - 중복포함), 
--                    MINUS(차집합),INTERSECT(교집합)

-- EMP테이블에서 급여 열에 12를 곱한 값이 36000인 행 조회
SELECT *
FROM EMP
WHERE SAL*12 = 36000;

-- ENAME 이 F 이후 문자로 시작하는 사원 조회
-- 문자표현시 ' ' 만 사용
SELECT *
FROM EMP
WHERE ENAME > 'F' ;

--JOB 이 MANAGER 이거나 SALESMAN  이거나 CLERK 사원 조회
SELECT *
FROM EMP
WHERE JOB = 'MANAGER' OR JOB='SALESMAN' OR JOB='CLERK';

--SAL 이 3000이 아닌 사원 조회
SELECT *
FROM EMP
WHERE SAL != 3000 ;

--JOB이 MANAGER,SALESMAN,CLERK 사원 조회 ==> IN 연산자

SELECT *
FROM EMP
WHERE JOB IN('MANAGER','SALESMAN','CLERK');

--JOB이 MANAGER,SALESMAN,CLERK 가 아닌 사원 조회

SELECT *
FROM EMP
WHERE JOB != 'MANAGER' AND JOB != 'SALESMAN' AND JOB != 'CLERK'

select *
FROM EMP
where job not in('MANAGER','SALESMAN','CLERK');


-- 커미션을 받는 모든 사원들의 LAST_NAME , 연봉 , 커미션 조회(연봉의 내림차순 , 커미션  내림차순

SELECT LAST_NAME , SALARY , COMMISSION_PCT
FROM EMPLOYEES
WHERE COMMISSION_PCT > 0  ORDER BY SALARY DESC ,commission_pct DESC;


-- 연봉이 2500,3500,7000 이 아니며 직업이 SA_ERP나 ST_CLERK인 사원 조회
SELECT *
FROM employees
WHERE SALARY NOT IN(2500,3500,700) AND JOB_ID IN ('SA_ERP' , 'ST_CLERK'); 

--2008/02/20 ~ 2008/05/01 사이에 고용된 사원들의 LAST_NAME , 사번 , 고용일자 조회
--고용일자 내림차순 정렬
-- 날짜 표현시 홑따옴표 안에 표현     -  ,  / 사용가능
SELECT LAST_NAME , EMPLOYEE_ID, HIRE_DATE
FROM EMPLOYEES
WHERE HIRE_DATE >= '08/02/20' AND HIRE_DATE<= '08/05/01'
            ORDER BY HIRE_DATE DESC;
            
--2004년도에 고용된 사원들의 LAST_NAME , HIRE_DATE 조회
-- 고용일자 오름차숨 정렬

SELECT LAST_NAME , HIRE_DATE
FROM EMPLOYEES
WHERE HIRE_DATE >= '2004-01-01' AND HIRE_DATE <= '2004-12-31' ORDER BY HIRE_DATE;

BETWEEN  A  AND  B ( BETWEEN  최소값 AND 최대값) 

-- BETWEEN A AND B
-- 급여가 2000이상 3000이하인 사원 조회

SELECT *
FROM EMP
WHERE SAL BETWEEN 2000 AND 3000;

--2008/02/20 ~ 2008/05/01 사이에 고용된 사원들의 LAST_NAME , 사번 , 고용일자 조회
--고용일자 내림차순 정렬

SELECT LAST_NAME , EMPLOYEE_ID, HIRE_DATE
FROM EMPLOYEES
WHERE HIRE_DATE BETWEEN '08/02/20' AND '08/05/01' ORDER BY HIRE_DATE DESC;