package database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.Scanner;

public class UpdateJava2 {
	public static void main(String[] args) {
		Connection con = null;
		PreparedStatement pstmt =null;
		Scanner sc = new Scanner(System.in);
		
		try {
			Class.forName("oracle.jdbc.OracleDriver");
			String url= "jdbc:oracle:thin:@localhost:1521:xe";
			String user = "scott";
			String password = "TIGER";
			
			con = DriverManager.getConnection(url,user,password);
			// 부서번호가 55번 dname 을 WEB으로 변경
			System.out.println("부서코드 입력해주세요 >>");
			int deptno = sc.nextInt();
			System.out.println("부서명을 입력해주세요 >>");
			//next , nextLine -  둘다 String 으로 처리
			//nextLine - 은 줄단위로 읽기 (enter 까지 읽는다)
			//next enter - 직전까지 읽는다.
			String dname = sc.next();
			String sql = "update dept_temp set dname = ? where deptno = ?";
			
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, dname);
			pstmt.setInt(2 , deptno);
			
			
			int result = pstmt.executeUpdate();
			
			if(result>0) {
				System.out.println("Update successfull");
			}
					
		} catch (Exception e) {
			e.printStackTrace();
		}
		finally {
			try {
				pstmt.close();
				con.close()
				;
			
			} catch (Exception e2) {
				e2.printStackTrace();
			}
		}
	}
}

--실습1 

CREATE TABLE EXAM_EMP AS SELECT * FROM EMP;
CREATE TABLE EXAM_DEPT AS SELECT * FROM DEPT;
CREATE TABLE EXAM_SALGRADE AS SELECT * FROM SALGRADE;

INSERT INTO  EXAM_EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
values(7201,'TEST_USER1','MANAGER' , 7788 , '2016/01/02', 4500,NULL,50);
INSERT INTO  EXAM_EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
values(7202,'TEST_USER2','CLERK' , 7201 , '2016/02/21', 1800,NULL,50);
INSERT INTO  EXAM_EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
values(7203,'TEST_USER3','ANALYST' , 7201 , '2016/04/11', 3400,NULL,60) ;
INSERT INTO  EXAM_EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
values(7204, 'TEST_USER4' , 'SALESMAN' , 7201 , '2016/05/31', 2700,300,60);
INSERT INTO  EXAM_EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
values(7205,'TEST_USER5','CLERK' , 7201 , '2016/07/20', 2600,NULL,70);
INSERT INTO  EXAM_EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
values(7206,'TEST_USER6','CLERK' , 7201 , '2016/09/08', 2600,NULL,70);
INSERT INTO  EXAM_EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
values(7207,'TEST_USER7','LECTURER' , 7201 , '2016/10/28', 2300,NULL,80);
INSERT INTO  EXAM_EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
values(7208,'TEST_USER8','STUDENT' , 7201 , '2018/03/09', 1200,NULL,80);

SELECT * FROM EXAM_EMP;

--[실습3] EXAM_EMP에 속한 사원 중 50번 부서에서 근무하는 사원들의 평균 급여보다 많은 급여를 받고 있는 사원들을 
--70번 부서로 옮기는 SQL 문 작성하기
UPDATE EXAM_EMP 
SET DEPTNO = 70 
WHERE DEPTNO IN 
(
SELECT DEPTNO 
FROM EXAM_EMP 
WHERE SAL >
(
SELECT  AVG(SAL) 
FROM  EMP 
WHERE DEPTNO = 50
) );

--[실습4] EXAM_EMP에 속한 사원 중 60번 부서의 사원 중에서 입사일이 가장 빠른 사원보다
--늦게 입사한 사원의 급여를 10% 인상하고 80번 부서로 옮기는 SQL 문 작성하기
UPDATE EXAM_EMP
SET SAL = SAL * 1.1, 
        DEPTNO = 80 
WHERE HIREDATE > (
  SELECT MIN(HIREDATE) 
  FROM EXAM_EMP 
  WHERE DEPTNO = 60 
  );

--[실습5] EXAM_EMP에 속한 사원 중, 급여 등급이 5인 사원을 삭제하는 SQL문을 작성하기
DELETE FROM EXAM_EMP
WHERE E.EMPNO  IN (SELECT E.EMPNO  
FROM EXAM_EMP E, EXAM_SALGRADE S 
WHERE E.SAL BETWEEN E.SAL AND S.LOSAL AND S.SALGRADE = 5) ;

COMMIT;

INSERT

--DML(Data Manipulation Language)  : 데이터 추가(insert) , 수정(update) , 삭제(delete) 하는 데이터 조작어
--COMMIT : DML 작업을 데이터베이스에 최종 반영
--ROLLBACK : DML 작업 취소
--select + DML == > 자주 사용하는 sql 임

--연습용 테이블 생성
--기존 테이블 복사
CREATE TABLE dept_temp AS SELECT *  FROM  dept;

--테이블 삭제
DROP TABLE  dept_temp;

-- 열이름은 선택사항임
-- insert into 테이블 이름 (열이름1, 열이름2 , .....)
-- values(데이터1 , 데이터2, ......... )

-- dept_temp 에 새로운 부서 추가하기

insert into dept_temp ( deptno, dname , loc)
values(50,' DATABASE', 'SEOUL');

--열 이름 작성 안할 때 (기존 데이터형식과 같아야 오류가 나지 않는다)
--전체열을 삽입하지 않는다면 필드명은 필수 . 
INSERT INTO dept_temp
VALUES(60,'NETWORK','BUSAN');

--emp_temp 생성(emp 테이블 복사 - 데이터는 복사를 하지 않을 때)

CREATE TABLE emp_temp AS SELECT * FROM  emp WHERE 1<>1; -- 테이블과 열만 복사 ( 데이터 x)

INSERT INTO emp_temp(empno, ename , job , mgr, hiredate , sal ,comm , deptno)
values(9999,'홍길동','president' , null , '2001/01/01', 5000,1000,10);

INSERT INTO emp_temp (empno, ename , job , mgr, hiredate , sal ,comm , deptno)
VALUES(1111,'성춘향', 'MANAGER' , null , '2002/01/05', 4000,NULL,20);

INSERT INTO emp_temp (empno, ename , job , mgr, hiredate , sal ,comm , deptno)
VALUES(2222,'이순신', 'MANAGER' , 9999 , '2001/01/07  ', 4000,NULL,20);

INSERT INTO emp_temp (empno, ename , job , mgr, hiredate , sal ,comm , deptno)
VALUES(3333,'심봉사', 'MANAGER' , 9999 , SYSDATE , 4000,NULL,30);



--서브쿼리로 INSERT 구현
--EMP , SALGRADE 테이블을 JOIN 후 급여 등급이 1인 사원만 EMP_TEMP 에 추가
INSERT INTO emp_temp(empno, ename , job , mgr, hiredate , sal ,comm , deptno)
SELECT E.EMPNO , E.ENAME , E.JOB , E.MGR , E.HIREDATE, E.SAL , E.COMM, E.DEPTNO
FROM EMP E , SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL AND S.GRADE =1;


SELECT * FROM EMP_TEMP ;

COMMIT;

UPDATE

--UPDATE : 테이블에 있는 데이터 수정
--
--UPDATE 테이블명
--SET 변경할 열이름 = 데이터 , 변경할 열이름 = 데이터...
--WHERE 변경을 위한 대상 행을 선별하기 위한조건

--DEPT_TEMP LOC열의 모든 값을 SEOUL 로 변경
UPDATE DEPT_TEMP
SET LOC = 'SEOUL';

--DEPT _TEMP 부서번호가 40번인 LOC 열의 값을 SEOUL로 변경
UPDATE DEPT_TEMP
SET LOC = 'SEOUL'
WHERE DEPTNO = 40;

--DEPT _TEMP 부서번호가 80번인  DNAME 은 SALES , LOC 는 CHICAGO 로 변경
UPDATE DEPT_TEMP
SET DNAME = 'SALES' , LOC = 'CHICAGO'
WHERE DEPTNO = 80 ;

--EMP_TEMP 사원들 중에서 급여가 2500이하인 사원만 추가수당을 50으로 수정
UPDATE EMP_TEMP
SET COMM = 50
WHERE SAL < 2500;

-- 서브쿼리를 사용하여 데이터 수정
--DEPT 테이블의 40번 부서 , DNAME , LOC 를 DEPT_TEMP 40번 부서의 DAME,LOC로 업데이트
UPDATE DEPT_TEMP
SET( DNAME  , LOC ) =
(SELECT DNAME , LOC
FROM DEPT WHERE DEPTNO=40)
WHERE DEPTNO = 40 ;

--  서브쿼리(sub query)
-- sql 문을 실행하는 데 필요한 데이터를 추가로 조회하기 위해 sql 문 내부에서 사용하는 select 문
-- select 문 안에 select 문

--select 조회할 열
--from 테이블명
--where 조건식(select 조회할 열 from 테이블 where 조건식)

--존스의 급여보다 높은 급여를 받는 사원 조회

select ename , sal
from emp
where sal <  (select sal from emp where ename = 'JONES');

--사원 이름이 ALLEN 인 사원의 추가수당 보다 많은 추가 수당을 받는 사원 조회
select  *
from emp
where comm > (select comm from emp where ename = 'ALLEN');

-- WARD 사원의 입사일보다 빨리 입사한 사원 조회
select *
from emp
where hiredate > (select hiredate from emp where ename = 'WARD');

--다중행 서브쿼리 : 서브쿼리 결과로 여러개의 행 반환
-- in , any(some)  , all , exists 연산자 허용 (단일행 서브쿼리에 쓰는 연산자 사용 불가 )

-- 각 부서별 최고 급여와 동일한 급여를 받는 사원정보 조회
--IN : 메인 쿼리 결과가 서브쿼리 결과 중 하나라도 일치하면 true ; 
select * 
from emp 
where sal in (select max(sal) from emp group by deptno);

--실습1 급여가 2000초과인 사원들의 부서정보 , 사원 정보를 아래와 같이 출력
select d.deptno , d.dname , e.empno , e.ename , e.sal
from dept d join emp e  on d.deptno = e.deptno and  e.sal > 2000;

select d.deptno , d.dname , e.empno , e.ename , e.sal
from dept d  ,  emp e 
where d.deptno = e.deptno and  e.sal > 2000;

--실습2 각 부서별 평균 급여 , 최대 급여 , 최소 급여 , 사원수를 출력
select d.deptno, d.dname,  round(avg(sal)) as avgsal , max(sal) as maxsal, min(sal)  as minsal ,count(*) as cnt
from dept d  ,  emp e 
where d.deptno = e.deptno 
group by d.deptno,  d.dname;

select d.deptno, d.dname,  round(avg(sal)) as avgsal , max(sal) as maxsal, min(sal)  as minsal ,count(*) as cnt
from dept d  join  emp e on  d.deptno = e.deptno 
group by d.deptno,  d.dname;

--실습3 모든 부서정보와 사원정보를 아래와 같이 부서번호 , 사원이름 순으로 정렬
select d.deptno , d.dname , e.empno, e.ename , job , sal
from dept d full outer join emp e on d.deptno = e.deptno order by d.deptno , e.ename ;

select d.deptno , d.dname , e.empno, e.ename , job , sal
from dept d , emp e 
where d.deptno = e.deptno order by d.deptno , e.ename ;

-- join
--자신의 담당 매니저의 고용일보다 빠른 입사자 찾기 (self join - empoyees )
select e1.hire_date , e1.last_name , e1.manager_id
from employees e1 , employees e2
where e1.manager_id  = e2.employee_id and e1.hire_date < e2.hire_date;


--도시 이름이 T로 시작하는 지역에 사는 사원들의 사번 , last_name , 부서번호 , 도시 조회
--(employees , departments , locations) inner join
select e.employee_id , e.last_name , e.department_id  , l.city
from employees e , locations l , departments d
where e.department_id = d.department_id
and d.location_id = l.location_id and l.city  like 'T%';

--위치 id가 1700인 사원들의 사번 last_name 부서번호 급여 조회
--employees , departments , locations inner join
SELECT e.employee_id, e.last_name, d.department_id, e.salary
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
INNER JOIN locations l ON d.location_id = l.location_id
WHERE l.location_id = 1700;


-- 부서명 , 위치id , 각 부서별 사원 총 수, 각 부서별 평균 연봉 조회
--평균 연봉은 소수점 2자리 까지만
--employees , departments, inner join
SELECT d.department_name, d.location_id, COUNT(e.employee_id) AS total_employees, 
ROUND(AVG(e.salary), 2) AS avg_salary
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name, d.location_id;

--executive 부서에 근무하는 사원들의 부서번호 last_name job_id 조회
--employees , departments inner join
select e.department_id , e.last_name , e.job_id
from employees e , departments d 
where e.department_id = d.department_id and d.department_name = 'Executive' ;

--각 사원별 소속부서에서 자신보다 늦게 고용되었으나 보다 많은 연봉을 받는 사원이 존재하는 모든 사원들의
--부서번호 , 이름 (first_name , last_name 연결) salary , hire_date 조회
-- employees self join

select e1.department_id , e1.first_name ||  e1.last_name as name , e2.salary , e2.hire_date
from employees e1 , employees e2
where e1.department_id =  e2.department_id and e1.hire_date < e2.hire_date and e1.salary < e2.salary;

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;