DB練習03-where、並べ替え

-- 혹시 미리 만들어 놓았을 수 있는, 동일한 이름의 테이블 제거하기
DROP TABLE EMP;
DROP TABLE DEPT;
DROP TABLE SALGRADE;
DROP TABLE EMPLOYEE;
DROP TABLE DEPARTMENT;
DROP TABLE SALGRADE;

-- 부서 테이블 생성하기
CREATE TABLE DEPT(
	 DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
	 DNAME VARCHAR2(14),
	 LOC   VARCHAR2(13) ) ;

-- 사원 테이블 생성하기
CREATE TABLE EMP( 
  	 EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
	 ENAME VARCHAR2(10),
 	 JOB   VARCHAR2(9),
	 MGR  NUMBER(4),
	 HIREDATE DATE,
	 SAL NUMBER(7,2),
	 COMM NUMBER(7,2),
	 DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);

 -- 급여 테이블 생성하기
CREATE TABLE SALGRADE(
	 GRADE NUMBER,
	 LOSAL NUMBER,
	 HISAL NUMBER );


-- 사원 테이블에 데이터 추가하기
INSERT INTO DEPT VALUES(10, '경리부', '서울');
INSERT INTO DEPT VALUES(20, '인사부', '인천');
INSERT INTO DEPT VALUES(30, '영업부', '용인'); 
INSERT INTO DEPT VALUES(40, '전산부', '수원');

-- 부서 테이블에 데이터 추가하기
INSERT INTO EMP VALUES(1001, '김사랑', '사원', 1013, to_date('2007-03-01','yyyy-mm-dd'), 300, NULL, 20);
INSERT INTO EMP VALUES(1002, '한예슬', '대리', 1005, to_date('2007-04-02','yyyy-mm-dd'), 250,   80, 30);
INSERT INTO EMP VALUES(1003, '오지호', '과장', 1005, to_date('2005-02-10','yyyy-mm-dd'), 500,  100, 30);
INSERT INTO EMP VALUES(1004, '이병헌', '부장', 1008, to_date('2003-09-02','yyyy-mm-dd'), 600, NULL, 20);
INSERT INTO EMP VALUES(1005, '신동협', '과장', 1005, to_date('2005-04-07','yyyy-mm-dd'), 450,  200, 30);
INSERT INTO EMP VALUES(1006, '장동건', '부장', 1008, to_date('2003-10-09','yyyy-mm-dd'), 480, NULL, 30);
INSERT INTO EMP VALUES(1007, '이문세', '부장', 1008, to_date('2004-01-08','yyyy-mm-dd'), 520, NULL, 10);
INSERT INTO EMP VALUES(1008, '감우성', '차장', 1003, to_date('2004-03-08','yyyy-mm-dd'), 500,    0, 30);
INSERT INTO EMP VALUES(1009, '안성기', '사장', NULL, to_date('1996-10-04','yyyy-mm-dd'),1000, NULL, 20);
INSERT INTO EMP VALUES(1010, '이병헌', '과장', 1003, to_date('2005-04-07','yyyy-mm-dd'), 500, NULL, 10);
INSERT INTO EMP VALUES(1011, '조향기', '사원', 1007, to_date('2007-03-01','yyyy-mm-dd'), 280, NULL, 30);
INSERT INTO EMP VALUES(1012, '강혜정', '사원', 1006, to_date('2007-08-09','yyyy-mm-dd'), 300, NULL, 20);
INSERT INTO EMP VALUES(1013, '박중훈', '부장', 1003, to_date('2002-10-09','yyyy-mm-dd'), 560, NULL, 20);
INSERT INTO EMP VALUES(1014, '조인성', '사원', 1006, to_date('2007-11-09','yyyy-mm-dd'), 250, NULL, 10);

-- 급여 테이블에 데이터 추가하기
INSERT INTO SALGRADE VALUES (1, 700,1200);
INSERT INTO SALGRADE VALUES (2, 1201,1400);
INSERT INTO SALGRADE VALUES (3, 1401,2000);
INSERT INTO SALGRADE VALUES (4, 2001,3000);
INSERT INTO SALGRADE VALUES (5, 3001,9999);

-- 테이블 작성을 다 했다면 변경 내용을 커밋!
COMMIT;

--dept 테이블 모두 검색--
select * from dept;

-- 행 추가하기 : insert into --
/* (여기 있는 컬럼명 순서대로) values (자료를 넣어야 한다 대응되도록)
따라서 dname, loc, deptno 로 컬럼명을 적어준다면 자료 넣을때도 그 순서대로 값을 입력 */
-- 만약 컬럼명을 생략하면 자료 순서는 이미 정해진 순서대로 넣어야 한다 --
insert into dept(deptno, dname, loc) values (50, '총부무', '서울');

/*총무부인 사람들의 loc를 모두 부산으로 바꿔보자*/
update dept set loc = '부산' where dname='총부무';

/* deptno=50인 자료를 삭제해라*/
delete from dept where deptno=50;

/* 만약 컬럼명 설정을 바꾸고 싶다면 */
alter table dept modify (dname varchar(15) );

/* 바뀐 구조 확인하기 */
desc dept;

/* DB 이름 변경하기 */
rename dept to dept01;
select * from dept;     --에러
select * from dept01;   --확인 가능

-- emp 확인하기
select * from emp;
select ename, sal, sal from emp;
select ename 사원명, sal 급여, sal*12 연봉 from emp;   --간단한 계산은 이런식으로도 작성 가능
select ename 사원명, sal 급여, sal*12 연봉, sal*0.03 세금 from emp;  -- 이런식으로.

-- null 넣어보기
INSERT into emp (EMPNO) values(1015);     --1015인 사원을 추가하겠다. 단 뒤에 아무 내용도 없음
select * from emp;  --확인 가능

--출력하는데 만약 sal 컬렴의 값이 null이라면 그냥 0으로 출력해라 
select ename 사원명, nvl(sal, 0) 급여, sal*12 연봉 from emp; 

--출력을 연결해서 하기
select nvl(ename, '홍길동') || '의 직급은 ' || job || ' 입니다' as 직급 from emp;

-- 급여가 500 이상이면서 과장이거나 부장인 사람만 보겠다
select * from emp where sal >= 500 and job= '과장' or job='부장' ;

-- 급여가 500이상 800 이하인 사람만 보겠다
select * from emp where sal >=500 and sal <= 800 ;
select * from emp where sal BETWEEN 500 and 800;

-- deptno가 20부터 50 사이
select * from emp where deptno between 20 and 50;

-- 사원 혹은 대리인 사람들만 보겠다
select * from emp where job='사원' or job='대리' ;
select * from emp where job in('사원', '대리');

-- MGR 이 1005, 1006, 1007 인 사람들만
select * from emp where mgr in(1005, 1006, 1007);

-- 이름에 특정 글자가 들어가는 사람만 / %=있거나 없거나 / _ = 한글자 있음
select * from emp where ename like '김%';
select * from emp where ename like '_이%';

-- 보너스가 없는 사람의 이름과 직위 comm 출력하기
select ename, job, comm from emp where comm is null;
-- 보너스 없는 사람들에게 보너스로 만원씩 주기
select ename, job, nvl(comm, 10000) 보너스 from emp where comm is null;
-- 보너스 있는 사람들 출력하기
select ename, job, comm 보너스, comm*0.1 반납금액 from emp where comm >0;
select ename, job, comm 보너스, comm*0.1 반납금액 from emp where comm is not null;

-- sal을 기준으로 오름차순 정렬
select * from emp order by sal asc;
-- sal를 기준으로 내림차순 정렬하는데, 급여가 똑같으면 MGR 오름차순으로
select * from emp order by sal desc, mgr asc;
-- 이름 순서대로 오름차순정렬하고, 같으면 deptno 순서대로 내림차순
select * from emp order by ename asc, deptno desc;

-- 문제 1. 사원의 이름과 급여와 입사일자만 출력
select ename 이름, sal 급여, hiredate 입사일자 from emp;

-- 문제 2. 컬럼 이름에 별칭 지정하기 (부서번호, 부서명)
select deptno 부서번호, dname 부서명 from dept01;

-- 문제 3. 직급을 중복하지 않고, 한번씩 나열하기 (직급만 출력)
SELECT DISTINCT job FROM emp;

-- 문제 4. 급여가 300 이하인 사원의 사원번호, 사원이름, 급여 출력
select empno, ename, sal from emp where sal >=300;

-- 문제 5. 이름이 '오지호' 인사원의 사원번호, 사원명, 급여 출력
select empno, ename, sal from emp where ename='오지호';

-- 문제6. 급여가 250이거나 300이거나 500인 사원들의 사원번호, 사원명, 급여를 출력
select empno, ename, sal from emp where sal in(250, 300);

-- 문제 7. 급여가 250도, 300도 500도 아닌 사원들을 출력
select ename, sal from emp where sal not in(250, 300, 500);

-- 문제 8. 사원들 중에서 이름이 '이'로 시작하는 사람과 이름 중에 '기'를 포함하는 사원의 사원번호와 사원이름 출력
select empno, ename from emp where ename like '이%' or ename like '%기%';

-- 문제 9. 상급자가 없는 사원을 출력
select * from emp where mgr is null;

-- 문제 10. 사원테이블의 최급 입사한 직원 순으로 사원번호, 사원명, 직급, 입사일 컬럼만 출력
select empno, ename, job, hiredate from emp order by hiredate desc; 

-- 문제11. 부서 번호가 빠른 사원부터 출력하되 같은 부서내의 사원을 출력할 경우 입사한 지 가장 오래된 사원부터 출력
select * from emp order by deptno asc, hiredate asc;