데이터베이스 20180814 (학원1)

2018-08-14

#연결 연산자 -> ||

 -> 여러개의 컬럼은 하나의 컬럼처럼 연결하거나 리터럴과 컬럼을 연결

#LIKE - 비교연산자

전씨 성을 가진 직원의 이름과 급여 조회

-- _이 하나면 한글자 __2개면 두글자 %이면 모든것

-----------------------------------------------------------

#비교연산자 - IS NULL / IS NOT NULL

 -> NULL 여부 비교 연산자

관리자도 없고(MANAGER_ID=NULL),

부서배치도 없는 (DEPT_CODE = NULL)

IN -> 일치하는 데이터가 있는지 확인하는 연산자

#연산자 우선순위

산술(+,-..) > 연결(||) > 비교(<,>)

> IS NULL / IS NOT NULL, LIKE, IN > BETWEEN > NOT > AND > OR 순이다.

부서원중에 직급코드가 J7또는 J2이고, 급여가 2000000원 초과인사람

---------------------------------------------------------

#ORDER BY

 -> SELECT 한 컬럼에 대해서 정렬을 할 때 사용

 -> SELECT 구문의 가장 마지막에 작성, 실행도 가장 마지막

ASC : 사전순정렬(작은수 -> 큰수), NULL이 가장아래로, 빠른날->늦은날

DESC : 역사전순정렬(큰수 -> 작은수), NULL이 가장위로, 늦은날->빠른날

------------------------------------------------------

1. 입사일이 5년 이상, 10년 이하인 직원의 이름,주민번호,급여,입사일을 검색하여라

2. 재직중이 아닌 직원의 이름,부서코드를 검색하여라 (퇴사 여부 : ENT_YN)

3. 근속년수가 10년 이상인 직원들을 검색하여 출력 결과는 이름,급여,근속년수(소수점X)를 근

속년수가 오름차순으로 정렬하여 출력하여라.

단, 급여는 50% 인상된 급여로 출력되도록 하여라.

4. 입사일이 99/01/01 ~ 10/01/01 인 사람 중에서 급여가 2000000 원 이하인 사람의 이름,주민

번호,이메일,폰번호,급여를 검색 하시오 

5. 급여가 2000000원 ~ 3000000원 인 여직원 중에서 4월 생일자를 검색하여 이름,주민번호,급여

,부서코드를 주민번호 순으로(내림차순) 출력하여라

단, 부서코드가 null인 사람은 부서코드가 '없음' 으로 출력 하여라.

6. 남자 사원 중 보너스가 없는 사원의 오늘까지 근무일을 측정하여 1000일 마다(소수점 제외-> 

FLOOR사용) 급여의 10% 보너스를 계산하여 이름,특별 보너스 (계산 금액) 결과를 출력하여라.

단, 이름 순으로 오름 차순 정렬하여 출력하여라.

--------------------------------------------------------------------

소수점제외는 -> FLOOR 사용

_----------------------------------------------------------------

#함수(Function)

 -> 반복적으로 사용되는 부분들을 분리하여 작성해놓은 작은 서브프로그램, 호출하고나면 값을 

리턴해줌

 -> 매개변수와 리턴타입이 있는 메소드

단일행 함수 : 각행마다 반복적으로 적용되어 입력받은 행의 갯수만큼 결과를 반환

여러개의 값 전달 및 호출 -> 작업 -> 여러개의 값 리턴

그룹 함수 : 특정한 행들의 집합으로 그룹이 형성되어 적용

 -> 그룹당 1개의 결과값을 반환

여러개의 값 전달 및 호출 -> 작업 -> 1개의 결과값 리턴

#문자처리함수

@LENGTH

 -> 주어진 컬럼 값/문자열의 길이(문자갯수)를 반환

@LENGTHB

 -> 주어진 컬럼 값/문자열의 길이(BYTE)를 반환

@INSTR

 -> 찾는 문자(열)이 지정한 위치부터 지정한 회수만큼 나타난 시작위치를 반환

※DUAL 테이블 오라클 자체에서 제공해주는 테이블

 -> 간단하게 함수를 이용해서 결과값 확인용 테이블

현재시간 select SYSDATE FROM DUAL;하면 바로 보여준다.

//테스트용 테이블이다.

@LPAD/RPAD

 -> 주어진 컬럼 문자열에 임의의 문자열을 왼쪽/오른쪽에 덧붙여 길이 N인 문자열을 반환

@LTRIM/RTRIM/TRIM

 -> 주어진 컬럼이나 문자열의 왼쪽 혹은 오른쪽에서 지정한 STR에 포함된 모든문자를 제거한 

나머지를 반환

@TRIM

 -> 주어진 컬럼이나 문자열 앞/뒤/양쪽에 있는 지정한 문자를 제거한 나머지를 반환

@SUBSTR

 -> 컬럼이나 문자열에서 지정한 위치부터 갯수의 문자열을 복사해서 리턴

============================================================

===============================================================

select * from EMPLOYEE;

select EMP_NAME, EMP_NO, EMAIL from EMPLOYEE;

select * from EMPLOYEE WHERE JOB_CODE='J5';

select EMP_NAME from EMPLOYEE WHERE JOB_CODE='J5';

/*

JOB테이블에서 JOB_NAME의 정보만 출력되게 하시오

*/

select JOB_NAME from JOB;

/*

DEPARTMENT테이블의 내용전체를 출력

*/

select * from DEPARTMENT;

select * from DEPARTMENT WHERE LOCATION_ID='L1';

/*

예제

1. EMPLOYEE 테이블에서 HIRE_DATE, EMP_NAME, SALARY를 출력

2. EMPLOYEE 테이블에서 월급(SALARY)이 2,500,000원 이상인 사람의 이름(EMP_NAME),

SAL_LEVEL을 출력(hint >,<를 이용)

3. EMPLOYEE테이블에서 월급이 350만원 이상이면서 JOB_CODE가 'J3'인 사람의 이름, 전화번호를 출력

(조건이 여러개면 AND로이어주면된다.)

*/

select HIRE_DATE, EMP_NAME, SALARY from EMPLOYEE;

select HIRE_NAME, SAL_LEVEL from EMPLOYEE where SALARY>=2500000;

select * from EMPLOYEE;

select EMP_NAME, PHONE from EMPLOYEE where SALARY>=3500000 AND JOB_CODE='J3';

select * from EMPLOYEE;

select emp_name salary from EMPLOYEE;

/*

월급이 나온다.

*/

select emp_name, salary*12 from EMPLOYEE;

/*

숫자는 곱해서 바로 표시할 수 있다.

*/

select emp_name as 이름, salary as 월급 from EMPLOYEE;

/*

컬럼명이 내가 지정한걸로 바꿔서 나온다.

*/

select emp_name as 이름, salary*12 as 연봉 from EMPLOYEE;

/*

이렇게도 바꿀수 있다.

*/

select emp_name as 이름, salary*12 as "연봉(원)" from EMPLOYEE;

/*

특수기호 하나라도 들어간다면 이렇게 넣어줘야한다.가로써줘야된다는것이다.

*/

select * from EMPLOYEE;

select emp_name, (salary+(salary*bonus))*12 as "연봉" from EMPLOYEE;

/*

월급의 몇퍼센트를 보너스로 주겠다.

*/

select emp_name, salary from EMPLOYEE;

/*

없는 컬럼을 강제로 넣어줄 수 있다.

*/

select emp_name, salary, '원' as 단위 from EMPLOYEE;

/*

없는 칼럼을 강제로 넣을 수 있다.

*/

/*

1. EMPLOYEE 테이블에서 이름, 연봉, 총수령액(보너스포함), 실수령액(총수령액 - (월급*세금(3%)*12)가

출력되게 하시오 -> 컬럼명은 한글로

2. EMPLOYEE 테이블에서 이름, 근무일수를 출력되게 하시오

(오늘날짜에서 입사날짜를 빼면된다.)

SYSDATE(오늘날짜가된다.)이녀석사용해서 연산을 해라, 입사일은 컬럼에 있다.

3. EMPLOYEE 테이블에서 20년이상의 근속자의 이름, 월급, 보너스율을 출력

select SYSDATE as 오늘날짜 from EMPLOYEE

*/

select * from EMPLOYEE;

select EMP_NAME as 이름, (salary+(salary*bonus))*12 as 연봉, 

(salary+(salary*0.3)) as "총수령액(보너스포함)", (salary+(salary*0.3))-(salary*0.03*12) as 실수령액 from EMPLOYEE;

select EMP_NAME as 이름, Floor(SYSDATE-HIRE_DATE) as 근무일수 from EMPLOYEE;

select EMP_NAME as 이름, salary as 월급, BONUS as 보너스율 from EMPLOYEE where Floor(SYSDATE-HIRE_DATE) >=7300;

select job_code from employee;

select distinct job_code from employee;

select EMP_NAME as 이름, dept_code as 부서코드, salary as 급여 from EMPLOYEE where dept_code='D6' and salary>2000000;

/*

부서코드(dept_code)가 d6이고 급여를 2000000보다 많이 받는 지원이 이름, 부서코드 급여 조회

*/

select EMP_NAME as 이름, salary || '원' FROM EMPLOYEE;

select EMP_NAME as 이름, salary || '원' as 급여 FROM EMPLOYEE;

/*

이게 연결연산자이다.

*/

/*

 급여를 3500000원 보다 많이 받고 6000000원 보다 적게 받는 직원 이름과 급여 조회

1. AND -> 

2. BETWEEN A And B

*/

select EMP_NAME as 직원이름 , salary || '원' as 급여 from EMPLOYEE where salary between 3500000 and 6000000;

select EMP_NAME as 직원이름 , salary || '원' as 급여  from EMPLOYEE where salary>=3500000 and salary<=6000000;

/*

입사일이 90/01/01 ~ 01/01/01 인 사원의 전체 내용 출력

*/

select * from EMPLOYEE where HIRE_DATE between '90/01/01' and '01/01/01';

select * from EMPLOYEE;

select * from EMPLOYEE;

select EMP_NAME as 이름, salary as 급여 from EMPLOYEE where EMP_NAME like '전%';

--%기호 쓰면 되에 어떤게 오던지 라는 뜻이 된다.

select EMP_NAME as 이름, salary as 급여 from EMPLOYEE where EMP_NAME like '전__';-- _이 하나면 한글자 __2개면 두글자 %이면 모든것

select * from EMPLOYEE;

/*

성_이름약자@메일주소 형식으로 가고있다.

언더바 앞에 문자가 3글자인경우, EMAIL중 _앞자리가 3글자직원의 이름, 이메일 조회

*/

select EMP_NAME as 이름, EMAIL as 이메일 from EMPLOYEE where EMAIL like '___#_%' ESCAPE '#';

--샵뒤에 오는 녀석은 예외라는 것이다. 일반 언더바로 인지를 한다는 것이다.

select EMP_NAME as 이름, EMAIL as 이메일 from EMPLOYEE where EMAIL like '__#_%' ESCAPE '#';

--2개 오고 나서 나머지는 예외라고 하는 것이다.

/*

'이'씨 성이 아닌 직원의 사번, 이름 이메일 조회

*/

select * from EMPLOYEE;

select EMP_ID as 사번, EMP_NAME as 이름, EMAIL as 이메일 from EMPLOYEE where  EMP_NAME not like '이%';

--not like를 준 것이다.

select EMP_ID as 사번, EMP_NAME as 이름, EMAIL as 이메일 from EMPLOYEE where not EMP_NAME like '이%';

--like를 쓴후 반전을 준 것이다.

/*

1. EMPLOYEE 테이블에서 이름 끝이 연으로 끝나는 사원의 이름을 출력하시오

2. EMPLOYEE 테이블에서 전화번호 처음 3자리가 010이 아닌 사원의 이름, 전화번호를 출력하시오

3. EMPLOYEE 테이블에서 메일주소의 's'가 들어가면서, DEPT_CODE가 D9또는 D6이고 고용일이 90/01/01~00/12/01이면서, 월급이 270만원이상인 사원의 전체 정보를 출력하시오

*/

select * from EMPLOYEE;

select EMP_NAME as 이름 from EMPLOYEE where EMP_NAME like '%연';

select EMP_NAME as 이름, PHONE as 전화번호 from EMPLOYEE where PHONE not like'010%'; 

select * from EMPLOYEE where (EMAIL like '%s%') and (DEPT_CODE = 'D9' or DEPT_CODE= 'D6') and (HIRE_DATE between '90/01/01' and '00/12/01') and salary>=2700000;

/*

관리자도 없고(MANAGER_ID=NULL),

부서배치도 없는 (DEPT_CODE = NULL)

*/

select * from EMPLOYEE where MANAGER_ID IS NULL AND DEPT_CODE IS NULL;

/*

부서배치를 받지 않았지만, 보너스는 있는 직원

is null is not null

*/

select * from EMPLOYEE;

select * from EMPLOYEE where (DEPT_CODE is null) and (BONUS is not null);  

select * from EMPLOYEE where DEPT_CODE IN ('D6','D9');

--DEPT_CODE가 D6이거나 D9인것

select * from EMPLOYEE where DEPT_CODE NOT IN ('D6','D9');

select * from EMPLOYEE where JOB_CODE='J7' OR JOB_CODE='J2' and salary>2000000;

select * from EMPLOYEE where (JOB_CODE='J7' OR JOB_CODE='J2') and salary>2000000;

/*

부서원중에 직급코드(JOB_CODE)가 J7또는 J2이고, 급여가 2000000원 초과인사람

JOB코드가 J2이면서 봉급이 2000000초과인것 그리고 또는 J7인것

가로를 넣으면 먼저 연산할 것이다.

*/

select * from EMPLOYEE ORDER BY EMP_NAME ASC;

select * from EMPLOYEE ORDER BY EMP_NAME DESC;

select * from EMPLOYEE where DEPT_CODE = 'D5' ORDER BY EMP_NAME;

--default는 asc로 된다.

/*

1. 입사일이 5년 이상, 10년 이하인 직원의 이름,주민번호,급여,입사일을 검색하여라

2. 재직중이 아닌 직원의 이름,부서코드를 검색하여라 (퇴사 여부 : ENT_YN)

3. 근속년수가 10년 이상인 직원들을 검색하여 출력 결과는 이름,급여,근속년수(소수점X)를 근속년수가 오름차순으로 정렬하여 출력하여라.

단, 급여는 50% 인상된 급여로 출력되도록 하여라.

4. 입사일이 99/01/01 ~ 10/01/01 인 사람 중에서 급여가 2000000 원 이하인 사람의 이름,주민번호,이메일,폰번호,급여를 검색 하시오 

5. 급여가 2000000원 ~ 3000000원 인 여직원 중에서 4월 생일자를 검색하여 이름,주민번호,급여,부서코드를 주민번호 순으로(내림차순) 출력하여라

단, 부서코드가 null인 사람은 부서코드가 '없음' 으로 출력 하여라.

6. 남자 사원 중 보너스가 없는 사원의 오늘까지 근무일을 측정하여 1000일 마다(소수점 제외-> FLOOR사용) 급여의 10% 보너스를 계산하여 이름,특별 보너스 (계산 금액) 결과를 출력하여라.

단, 이름 순으로 오름 차순 정렬하여 출력하여라. -> 무시해라

*/

select FLOOR(SALARY/999) FROM EMPLOYEE WHERE DEPT_CODE='D5' ORDER BY EMP_NAME;

--1. 입사일이 5년 이상, 10년 이하인 직원의 이름,주민번호,급여,입사일을 검색하여라

select EMP_NAME as 이름, EMP_NO as 주민번호, salary as 급여 , HIRE_DATE as 입사일 from EMPLOYEE where (SYSDATE-HIRE_DATE)>=1825 and SYSDATE-HIRE_DATE<=3650;

--2. 2. 재직중이 아닌 직원의 이름,부서코드를 검색하여라 (퇴사 여부 : ENT_YN)

select EMP_NAME as 이름, DEPT_CODE as 부서코드 from EMPLOYEE where ENT_YN is not NULL;

--3.근속년수가 10년 이상인 직원들을 검색하여 출력 결과는 이름,급여,근속년수(소수점X)를 근속년수가 오름차순으로 정렬하여 출력하여라.

--단, 급여는 50% 인상된 급여로 출력되도록 하여라.

select EMP_NAME as 이름, FLOOR(salary+(salary*0.5)) as 급여 , HIRE_DATE as 근속년수 from EMPLOYEE  ORDER BY HIRE_DATE asc ;

--4.입사일이 99/01/01 ~ 10/01/01 인 사람 중에서 급여가 2000000 원 이하인 사람의 이름,주민번호,이메일,폰번호,급여를 검색 하시오 

select EMP_NAME as 이름, EMP_NO as 주민번호, EMAIL as 이메일, PHONE as 폰번호, SALARY as 급여 from EMPLOYEE where (HIRE_DATE between '99/01/01' and '10/01/01') and salary<=2000000;

--5.  급여가 2000000원 ~ 3000000원 인 여직원 중에서 4월 생일자를 검색하여 이름,주민번호,급여,부서코드를 주민번호 순으로(내림차순) 출력하여라

--단, 부서코드가 null인 사람은 부서코드가 '없음' 으로 출력 하여라.

select EMP_NAME as 이름, EMP_NO  as 주민번호, salary as 급여, DEPT_CODE as 부서코드 from EMPLOYEE 

where (salary between 2000000 and 3000000) and (EMP_NO like '_______2%')  and (EMP_NO is null);

--6.  남자 사원 중 보너스가 없는 사원의 오늘까지 근무일을 측정하여 1000일 마다(소수점 제외-> FLOOR사용) 급여의 10% 보너스를 계산하여 이름,특별 보너스 (계산 금액) 결과를 출력하여라.

--단, 이름 순으로 오름 차순 정렬하여 출력하여라. -> 무시해라

select EMP_NAME as 이름, FLOOR(((SYSDATE - HIRE_DATE)/1000*(salary*0.1))) as 특별보너스 from EMPLOYEE where (EMP_NO like '_______1%') and (BONUS is null);

-- ////////////////////////////////////////////////////////////// --

--1.선생님코드

select EMP_NAME, EMP_NO, SALARY, HIRE_DATE from EMPLOYEE where (SYSDATE - HIRE_DATE)/365 BETWEEN 5 AND 10;

--2.선생님코드

select EMP_NAME, DEPT_CODE FROM EMPLOYEE where ENT_YN='Y';

--3.선생님코드

select EMP_NAME, salary*1.5, FLOOR((SYSDATE-HIRE_DATE)/365) FROM EMPLOYEE where (SYSDATE-HIRE_DATE)/365 >=10 order by 3;--3번째 뜻이라는의미로 3줘도된다.

--4. 선생님코드

select EMP_NAME, EMP_NO, EMAIL, PHONE, SALARY FROM EMPLOYEE where (HIRE_DATE BETWEEN '99/01/01' and '10/01/01') and salary<=2000000;

--5. 선생님코드

select EMP_NAME, EMP_NO, salary, DEPT_CODE from EMPLOYEE where (salary between 2000000 and 3000000) and EMP_NO LIKE '___4__-2%' order by EMP_NO DESC;

--6. 선생님코드

select EMP_NAME , FLOOR((SYSDATE-HIRE_DATE)/1000)*salary*0.1 as 특별보너스 from EMPLOYEE where EMP_NO like ('______-1%') and bonus is null order by 1;

select * from EMPLOYEE;

select EMP_NAME, LENGTH(EMP_NAME), EMAIl, LENGTH(EMAIL) from EMPLOYEE;

select EMP_NAME, LENGTHB(EMP_NAME), EMAIl, LENGTH(EMAIL) from EMPLOYEE;

--한글 3글자라 9바이트이다. 

select SYSDATE from dual;

select INSTR('Hello World Hi High','H',1,1) FROM dual;--1

select INSTR('Hello World Hi High','H',1,2) FROM dual;--13

select INSTR('Hello World Hi High','H',1,3) FROM dual;--16

select INSTR('Hello World Hi High','H',-1,3) FROM dual;--1 뒤에서부터 방향 찾는다.

select INSTR('Hello World Hi High','H',-1,1) FROM dual;--16 뒤에서부터 방향 찾는다.

select INSTR('Hello World Hi High','H',2,2) FROM dual;--16 뒤에서부터 방향 찾는다.

--검색할 문자열 ,찾는것,시작위치 양수면 앞에서 음수면 뒤에서부터

--몇번째값

--대문자H를 찾을건데 첫번째 H의위치를 알고싶다.

/*

EMPLOYEE 테이블에서 EMAIL컬럼에 @위치

*/

select * from EMPLOYEE;

select EMAIL, INSTR(EMAIL,'@',1,1) from EMPLOYEE;

select LPAD(EMAIL,20,'#') FROM EMPLOYEE;

--어떻게 되냐면 EMAIL이라는 문자열 출력하는데 길이를 20으로 무조건 고정하겠다.

--원래 15글자인데 20길이가 고정하면 5개의 공간이남을텐데 나머지 공간을 얘로 채울꺼야

select RPAD(EMAIL,20,'#') FROM EMPLOYEE;

--주어진 컬럼 문자열에 임의의 문자열을 왼쪽/오른쪽에 덧붙여 길이 N인 문자열을 반환

select LPAD(EMAIL,20,' ')FROM EMPLOYEE;

--주어진 컬럼 문자열에 임의의 문자열을 왼쪽/오른쪽에 덧붙여 길이 N인 문자열을 반환

select LTRIM('000123456','0') FROM DUAL;

--주어진 컬럼이나 문자열의 왼쪽 혹은 오른쪽에서 지정한 STR에 포함된 모든문자를 제거한 

--나머지를 반환

select LTRIM('123123KH123','123') FROM DUAL;

--앞에있는 123이라는 숫자가 지워진다.

select LTRIM('359498145641834006897kh','0123456789') from dual;

--앞에 숫자 다 없애버릴려면 다지워진다.

--즉 숫자가 있는건 다 지운다는 것이다.

select RTRIM('KH359498145641834006897','0123456789') from dual;

select TRIM('Z' FROM 'ZZZKHZZZ') FROM DUAL;

--주어진 컬럼이나 문자열 앞/뒤/양쪽에 있는 지정한 문자를 제거한 나머지를 반환

select TRIM(LEADING 'Z' FROM 'ZZZKHZZZ') FROM DUAL;

--앞에만 글자가 지워진다.

select TRIM(TRAILING 'Z' FROM 'ZZZKHZZZ') FROM dual;

--뒤에 글자만 지워버린다.

select TRIM(BOTH 'Z' FROM 'ZZZKHZZZ') FROM DUAL;

/*

1. Hello KH Java 문자열을 Hello KH 가 출력되게 하여라(dual)

2. Hello KH Java 문자열을 KH Java 가 출력되게 하여라(dual)

3. DEPARTMENT 테이블에서 DEPT_TITLE을 출력하여라(TRIM 사용)

 -> 이때 마지막'부'글자를 빼고 출력

 ex)회계관리부->회계관리

 4. 다음문자열에서 앞뒤 모든 숫자를 제거하세요

    982341678934509hello89798739273402

*/

select RTRIM('Hello KH Java','Java') FROM DUAL;

select LTRIM('Hello KH Java','Hello') FROM DUAL;

select dept_TITLE, TRIM( TRAILING '부' FROM DEPT_TITLE) from DEPARTMENT;

select RTRIM(LTRIM('982341678934509hello89798739273402','0123456789'),'0123456789') from dual;

select SUBSTR('SHOWMETHEMONEY',5,2) from dual;

--5번째부터 시작해서 2개 반환해줘

select SUBSTR('SHOWMETHEMONEY',5) from dual;

--5번째부터 끝까지 반환해

select SUBSTR('SHOWMETHEMONEY',-3,2) from dual;

--뒤에서 3번째부터 2개 반환해

select SUBSTR('SHOW ME THE MONEY',5,9) from dual;

select SUBSTR('쇼 미 더 머니',3,3) from dual;

/*

1. EMPLOYEE 테이블에서 사원명에서 성만 중복없이 사전순으로 출력

2. EMPLOYEE 테이블에서 남자사원만 사원번호, 사원명, 주민번호, 연봉출력 이때 주민번호 뒤 6자리는 *로처리

*/

select * from EMPLOYEE;

select DISTINCT SUBSTR(EMP_NAME,1,1) from EMPLOYEE order by 1;

--중복제거 DISTINCT이다.

select EMP_ID, EMP_NAME, RPAD((RPAD(EMP_NO,8,'*')),14,'*'), salary*12 from EMPLOYEE where SUBSTR(EMP_NO,8,1)='1';

select EMP_ID, EMP_NAME, RPAD((SUBSTR(EMP_NO,1,8)),14,'*'), salary*12 from EMPLOYEE where SUBSTR(EMP_NO,8,1)='1';