1. SELECT란?
데이터를 조회한 결과를 Result Set이라고 하는데 SELECT 구문에 의해 반환된 행들의 집합을 의미
[표현법]
SELECT 조회하고자하는 컬럼, 컬럼, 컬럼...
FROM 테이블명;
-- EMPLOYEE테이블의 전체 사원들의 모든 컬럼들을 모두 조회
SELECT * FROM EMPLOYEE;
-- EMPLOYEE 테이블의 전체 사원들의 사번, 이름, 급여 컬럼들만 조회
SELECT EMP_ID, EMP_NAME, SALALY FROM EMPLOTEE;
- ResultSet - SELECT구문을 통해 조회된 데이터의 결과물, 조회된 행들의 집합
**꼭 외우기
SELECT * FROM EMPLOYEE;
SELECT EMP_ID, EMP_NAME, SALARY FROM EMPLOYEE;2. SELECT 기본문법
-- 한 줄 주석
- 오라클 설치 시
경로 : 마음대로
비밀번호 : oracle
비밀번호 확인 : oracle
cmd -> sqlplus -> sys as sysdba // oracle -> exit
sqlDeveloper 실행
- 관리자 계정 : 데이터베이스의 생성과 관리를 담당하는 계정이며 모든 권한과 책임을 가짐.
- 사용자 계정 : 데이터베이스에 대하여 질의, 갱신, 보고서 작성 등의 작업을 수행할 수 있는 계정으로
업무에 필요한 최소한의 권한만 가지는 것을 원칙으로 함.
SELECT * FROM DBA_USERS;
-- 어떤 계정들이 있는지 확인
-- 일반 사용자 계정을 생성하는 구문(관리자계정에서만)
-- [표현법] CREATE(생성) USER 사용자이름 IDENTIFIED BY 비밀번호;
CREATE USER kh IDENTIFIED BY kh; -- 아이디는 대소문자 구분이 없음 // 비밀번호는 대소문자 구분
-- 권한주는 명령어
-- 위에서 생성된 일반 사용자 계정에게 최소한의 권한(접속, 데이터) 부여
GRANT CONNECT, RESOURCE TO kh;
-- [표현법] GRANT 권한1, 권한2, ... TO 계정명;
- 실습문제
--1. JOB테이블의 모든 컬럼 조회
--2. JOB테이블의 직급명 컬럼만 조회
--3. DEPARTMENT테이블의 모든 컬럼 조회
--4. EMPLOYEE테이블의 직원명, 이메일, 전화번호, 입사일 칼럼만 조회
--5. EMPLOYEE테이블의 입사일, 직원명, 급여 컬럼만 조회
--1.
SELECT * FROM JOB;
--2.
SELECT JOB_NAME FROM JOB;
--3.
SELECT * FROM DEPARTMENT;
--4.
SELECT EMP_NAME, EMAIL, PHONE, HIRE_DATE FROM EMPLOYEE;
--5.
SELECT HIRE_DATE, EMP_NAME, SALARY FROM EMPLOYEE;< 컬럼 값을 통한 산술 연산 >
: 조회하고자하는 컬럼들을 나열하는 SELECT절에서 산술연산(+, -, *, /)을 기술해서 결과를 조회할 수 있다.
-- EMPLOYEE 테이블로부터, 직원명, 월급, 연봉(SALARY*12) 조회
SELECT EMP_NAME, SALARY, SALARY*12
FROM EMPLOYEE;
-- EMPLOYEE 테이블로부터, 직원명, 월급, 보너스, 보너스가 포함된 연봉
-- (월급 + 보너스 * 월급) * 12
SELECT EMP_NAME, SALARY, BONUS, (SALARY + BONUS*SALARY)*12
FROM EMPLOYEE;
-- 산술연산하는 과정에서 NULL값이 존재할 경우 산술연산 결과마저 NULL이 된다.
-- DATE타입끼리도 연산 가능(DATE => 년, 월, 일, 시, 분, 초)
-- EMPLOYEE테이블로부터 직원명, 입사일, 근무일 수(오늘날짜-입사일) 조회
-- 오늘 날짜 : SYSDATE
SELECT EMP_NAME, HIRE_DATE, SYSDATE-HIRE_DATE
FROM EMPLOYEE;
-- 결과값은 일 수 단위로 출력
-- 값이 지저분한 이유는 DATE타입 안에 포함되어있는 시/분/초 연산이 수행되기 때문에< 컬럼명의 별칭 지정하기 >
: [표현법] 컬럼명 AS 별칭, 컬럼명 AS "별칭", 컬럼명 "별칭", 컬럼명 별칭
- 별칭에 특수문자, 띄어쓰기를 쓰고 싶다면 반드시 " " 로 묶어서 표현해야 함 AS를 붙이든 안 붙이든 상관 X
SELECT EMP_NAME AS 이름
FROM EMPLOYEE;< 리터럴 >
: 임의로 지정한 문자열 ('') SELECT절에 기술하면 실제 그 테이블에 존재하는 데이터처럼 조회가 가능하다.
SELECT EMP_ID, EMP_NAME, SALARY, '원' 단위
FROM EMPLOYEE;< DISTINCT >
: 조회하고자하는 컬럼의 중복된 값을 딱 한 번씩만 조회할 때 해당 컬럼명 앞에 기술
[표현법] DISTINCT 컬럼명
SELECT DISTINCT DEPT_CODE FROM EMPLOYEE;< WHERE 절 > : 조회하고자 하는 테이블에 특정 조건을 제시,
그 조건에 만족하는 데이터만을 조회하고자 할 때 기술하는 구문
[표현법]
SELECT 조회하고자하는 컬럼, 컬럼
FROM 테이블명
WHERE 조건식;
- 조건식에 다양한 연산자 사용 가능
< 비교 연산자 > >, <, >=, <=, =(자바에선 ==): 동등비교 , <>, ^=, !=: 일치하지 않은가
--EMPLOYEE테이블로부터 급여가 400만원 이상인 사람들만 모든 컬럼 조회
SELECT *
FROM EMPLOYEE
WHERE SALARY >= 4000000;- 실습문제
--조건식에 문자열을 쓸 때에는 ''(홑따옴표)를 반드시 사용해야 함.
--EMPLOYEE 테이블로부터 부서코드가 D9인 사원들의 사원명, 부서코드, 급여조회
SELECT EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE DEPT_CODE = 'D9';
--EMPLOYEE 테이블로부터 부서코드가 D9가 아닌 사원들의 사원명, 부서코드, 급여조회
SELECT EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE DEPT_CODE <> 'D9';
--EMPLOYEEE 테이블로부터 급여가 300만원 이상인 사원들의 이름, 급여, 입사일 조회
SELECT EMP_NAME, SARARY, HIRE_DATE
FROM EMPLOYEE
WHERE SALARY >= 3000000;
--EMPLOYEE 테이블로부터 직급코드가 J2인 사원들의 이름, 급여, 보너스 조회
SELECT EMP_NAME, SALARY, BONUS
FROM EMPLOYEE
WHERE JOB_CODE = 'J2';
--EMPLOYEE 테이블로부터 연봉이 5000만원 이상인 사원들의 이름, 급여, 연봉, 입사일 조회
SELECT EMP_NAME, SALARY, SALARY*12 AS 연봉, HIRE_DATE
FROM EMPLOYEE
WHERE (SALARY*12) >= 50000000;
------------------------------------------------------------------------< 논리 연산자 >
: 여러 개의 조건을 엮을 때 사용
: AND(그리고 ~이면서) OR(또는, ~이거나)
-- 부서 코드가 D9이면서 급여가 500만원 이상인 사람들의 이름, 부서코드 , 급여 조회
SELECT EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE DEPT_CODE = D9 AND SALARY >= 5000000;
--부서코드가 D6이거나 급여가 300만원 이상인 사람들의 이름, 부서코드, 급여 조회
SELECT EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE DEPT_CODE = D6
OR SALARY >= 3000000;
-- 급여가 350만원이상이고 600만원 이하인 사람들의 이름, 사번, 급여, 직급코드 조회
SELECT EMP_NAME, EMP_ID, SALARY, JOB_CODE
FROM EMPLOYEE
WHERE 3500000 <= SALARY AND SALARY <= 6000000;< BETWEEN AND >
: 몇 이상 몇 이하인 범위에 대한 조건을 제시할 때 사용
: [표현법]
비교대상 컬럼명 BETWEEN 하한값 AND 상한값;
SELECT EMP_NAME, EMP_ID, SALARY, JOB_CODE
FROM EMPLOYEE
WHERE SALARY BETWEEN 3500000 AND 6000000;
--급여가 350만원 미만이고 600만원 초과인 사원들의 이름, 사번, 급여, 직급코드 조회
SELECT EMP_NAME, EMP_ID, SALARY, JOB_CODE
FROM EMPLOYEE
WHERE SALARY NOT BETWEEN 3500000 AND 6000000;
--> 오라클에서 NOT은 자바의 논리부정연산자인 !와 동일한 의미
--BETWEEN AND 연산자 -> DATE형식 간에도 사용 가능
-- 입사일이 90/01/01 ~ 03/01/01인 사원들의 모든 컬럼 조회
SELECT *
FROM EMPLOYEE
-- WHERE HIRE_DATE >= '90/01/01' AND HIRE_DATE <='03/01/01';
WHERE HIRE_DATE BETWEEN '90/01/01' AND '03/01/01';
--입사일이 90/01/01 ~ /03/01/01 가 아닌 사원들의 모든 컬럼 조회
SELECT *
FROM EMPLOYEE
WHERE HIRE_DATE NOT BETWEEN '90/01/01' AND '03/01/01';
--WHERE NOT HIRE_DATE BETWEEN '90/01/01' AND '03/01/01';도 가능< LIKE '특정패턴' >
: 비교하려는 컬럼값이 내가 지정한 특정 패턴에 만족될 경우 조회
- 특정 패턴에 와일드카드인 '%', '_'를 가지고 제시할 수 있음
'%' : 0글자 이상
비교대상컬럼명 LIKE '문자%' => 컬럼값 중에서 '문자'로 시작되는 것
비교대상컬럼명 LIKE '%문자' => 컬럼값 중에서 '문자'로 끝나는 것
비교대상컬럼명 LIKE '%문자%' => 컬럼값 중에서 '문자'가 포함되는 것
'_' : 1글자
비교대상컬럼명 LIKE '_문자' => 해당컬럼값 중에서 문자 앞에 한 글자가 있을 경우 조회
비교대상컬럼명 LIKE '__문자' => 해당컬럼값 중에서 문자 앞에 두 글자가 있을 경우 조회
--성이 전씨인 사원들의 이름, 급여, 입사일 조회
SELECT EMP_NAME, SALARY, HIRE_DATE
FROM EMPLOYEE
WHERE EMP_NAME LIKE '전%';
--이름 중에 '하'가 포함된 이름, 주민번호, 부서코드 조회
SELECT EMP_NAME, EMP_NO, DEPT_CODE
FROM EMPLOYEE
WHERE EMP_NAME LIKE '%하%';
--전화번호 4번째 자리가 9로 시작하는 사원들의 사번, 사원명, 전화번호, 이메일 조회
SELECT EMP_ID, EMP_NAME, PHONE, EMAIL
FROM EMPLOYEE
WHERE PHONE LIKE '___9%';
--이름 가운데 글자가 '지인 사원들의 모든 칼럼
SELECT *
FROM EMPLOYEE
WHERE EMP_NAME LIKE '_지_';
-- 그 외의 사원
SELECT *
FROM EMPLOYEE
WHERE EMP_NAME NOT LIKE '_지_';- 실습 문제
--1. 이름이 '연'으로 끝나는 사원들의 이름, 입사일 조회
SELECT EMP_NAME, HIRE_DATE
FROM EMPLOYEE
WHERE EMP_NAME LIKE '%연';
--2. 전화번호 처음 3글자가 010이 아닌 사원들의 이름, 전화번호 조회
SELECT EMP_NAME, PHONE
FROM EMPLOYEE
WHERE PHONE NOT LIKE '010%';
--3. DEPARTMENT 테이블에서 해외영업과 관련된 부서들의 모든 컬럼 조회
SELECT *
FROM DEPARTMENT
WHERE DEPT_TITLE LIKE '해외영업%';< IS NULL >
[표현법]
비교대상 컬럼 IS NULL : 컬럼값이 NULL일 경우
비교대상 컬럼 IS NOT NULL : 컬럼값이 NULL이 아닐 경우
SELECT *
FROM EMPLOYEE;
-- 보너스를 받지 않는 사원들 (BONUS컬럼의 값이 NULL)의 사번, 이름, 급여, 보너스
SELECT EMP_ID, EMP_NAME, SALARY, BONUS
FROM EMPLOYEE
WHERE BONUS IS NULL;
--보너스를 받는 사원들
SELECT EMP_ID, EMP_NAME, SALARY, BONUS
FROM EMPLOYEE
WHERE BONUS IS NOT NULL;
--사수가 없는 사원들의 사원명, 사수사번(MANAGER_ID), 부서코드 조회
SELECT EMP_NAME, MANAGER_ID, DEPT_CODE
FROM EMPLOYEE
WHERE MANAGER_ID IS NULL;
--사수도 없고 부서배치도 받지 않은 사원들의 모든 컬럼 조회
SELECT *
FROM EMPLOYEE
WHERE MANAGER_ID IS NULL AND DEPT_CODE IS NULL;< IN >
: 비교 대상 컬럼 값에 내가 제시한 목록들 중에서 일치하는 값이 있는지
[표현법]
비교대상 컬럼 IN (값, 값, 값)
-- 부서코드가 D6거나 또는 D8이거나 D5또는 D2인 사원들의 이름, 부서코드, 급여 조회
SELECT EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
--WHERE DEPT_CODE = 'D6' OR DEPT_CODE = 'D8' OR DEPT_CODE = 'D5' OR DEPT_CODE = 'D2';
WHERE DEPT_CODE IN('D6', 'D8', 'D5', 'D2');
--그 외의 사원들
SELECT EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE DEPT_CODE NOT IN('D6', 'D8', 'D5', 'D2');< 연결 연산자 || >
: 여러 컬럼값들을 마치 하나의 컬럼인 것처럼 연결시켜주는 연산자, 컬럼과 리터럴을 연결할 수 있다.
SELECT EMP_ID || EMP_NAME || SALARY
FROM EMPLOYEE;< 연산자 우선순위 >
0. ()
1. 산술 연산자
2. 연결 연산자
3. 비교 연산자
4. IS NULL, LIKE, IN
5. BETWEEN AND
6. AND
7. OR
< ORDER BY 절 >
[표현법]
SELECT 조회할 컬럼, 컬럼 AS "별칭", 컬럼...
FROM 조회할테이블이름
WHERE 조건식(생략가능)
ORDER
BY [정렬할 컬럼/별칭/컬럼순번] [ASC: 오름차순/DESC: 내림차순] [NULL FIRST/NULL LAST] (생략가능)
- ASC : 오름차순 청렬(생략 시 기본값)
- DESC : 내림차순 정렬
- NULLS FIRST : NULL값이 포함되어있을 경우 앞으로 배치
(내림차순 시 기본값)
- NULLS LAST : NULL값이 포함되어있을 경우 뒤로 배치
(오름차순 시 기본값)
SELECT *
FROM EMPLOYEE
--ORDER BY BONUS;--ASC또는 DESC생략 시 기본값이 ASC(오름차순)
--ASC은 기본적으로 NULLS LAST임을 알 수 있다.
--ORDER BY BONUS ASC NULLS FIRST;
--ORDER BY BONUS DESC;
--DESC로 정렬 시 기본적으로 NULLS FIRST임을 알 수 있다.
ORDER BY BONUS DESC, SALARY ASC;
--먼저 DESC로 정렬 후 그 중 NULL 값을 가진 똑같은 값들 중 SALARY를 오름차순으로 정렬3. SELECT 함수
: 전달된 값들을 읽어서 계산한 결과를 반환
- 단일행 함수 : N개의 값을 읽어서 N개의 값을 리턴
- 그룹 함수 : N개의 값을 읽어서 1개의 결과를 리턴
* 단일행함수와 그룹함수는 하나의 쿼리문에 함께 사용할 수 없음
: 결과 행의 개수가 다르기 때문에
< 단일행 함수 >
1) 문자열과 관련된 함수
- LENGTH(STR) : 해당 전달된 STR(문자열)의 글자 수 반환
- LENGTHB(STR) : 해당 전달된 STR의 바이트 수 반환
결과값 : NUMBER타입으로 반환
숫자, 영문, 특수문자 : '!', '~', 'A', '1' => 한 글자당 1Byte
한글 : 'ㄱ', 'ㅏ', '강' => 한 글자당 3Byte
SELECT LENGTH('ORACLE'), LENGTHB('ORACLE')
FROM DUAL; --가상테이블(DUMMY TABLE)
SELECT LENGTH('오라클'), LENGTHB('오라클') --한글을 한 글자 당 3BYTE로 인식
FROM DUAL;
SELECT EMAIL, LENGTH(EMAIL), LENGTH(EMAIL), EMP_NAME, LENGTH(EMP_NAME), LENGTHB(EMP_NAME)
FROM EMPLOYEE;- INSTR(STR) : 문자열로부터 특정 문자의 위치값 반환
[표현법] INSTR(STR, '특정문자열', 찾을 위치의 시작값, 순번)
결과값 : NUMBER타입으로 반환
찾을 위치의 시작값, 순번은 생략 가능
SELECT INSTR('AAABCCAABAA', 'B')
FROM DUAL; -- 찾을 위치 생략 시 기본적으로 앞에서부터 첫 번째 글자 검색
SELECT INSTR('AAABCCAABAA', 'B', -1)
FROM DUAL; -- 뒤에서부터 첫 번째 글자 검색
SELECT EMP_NAME "이메일 주소 주인" , INSTR(EMAIL, '@') "@의 위치"
FROM EMPLOYEE;- SUBSTR : 문자열로부터 특정 문자열 추출 => 반환
[표현법] SUBSTR(STR, POSITION, LENGTH)
- STR: '문자열'또는 문자타입컬럼
- POSITION: 시작위치값
- LENGTH: 추출할 문자 개수(생략 시 끝까지)
결과값 : CHARACTER타입으로 반환
SELECT SUBSTR('LEEKIMSONGPARK', 7)
FROM DUAL;
SELECT SUBSTR('LEEKIMSONGPAKR', 7, 5)
FROM DUAL;
SELECT SUBSTR('LEEKIMSONGPARK', -10, 5)
FROM DUAL;
-- 주민번호 성별 부분을 추출해서 (남자(1)/여자(2)를 체크)
SELECT EMP_NAME, SUBSTR(EMP_NO, 8, 1) "성별"
FROM EMPLOYEE;
--남자사원들만 조회(사원명, 급여)
SELECT EMP_NAME, SALARY
FROM EMPLOYEE
WHERE SUBSTR(EMP_NO, 8, 1)='1' OR SUBSTR(EMP_NO, 8, 1)='3';
--여자사원들만 조회(사원명, 급여)
SELECT EMP_NAME, SALARY
FROM EMPLOYEE
WHERE SUBSTR(EMP_NO, 8, 1) IN ('2', '4');
-- 이메일에서 ID부분만 추출해서 조회(이름, 이메일, ID)
SELECT EMP_NAME, EMAIL, SUBSTR(EMAIL, 1, (INSTR(EMAIL, '@')-1)) AS "ID"
FROM EMPLOYEE;- LPAD / RPAD
[표현법] LPAD /RPAD(STR, 최종적으로 반화할 문자의 길이(바이트), 덧붙이고자하는 문자)
결과값 : CHARACTER타입으로 반환
덧붙이고자 하는 문자는 생략 가능
SELECT LPAD(EMAIL, 20, 'A')
FROM EMPLOYEE;
SELECT RPAD(EMAIL, 20, '!')
FROM EMPLOYEE;
--주민번호 111111-9******마스킹 할 때 14BYTE
SELECT RPAD('121212-2', 14, '*') "주민번호"
FROM DUAL;
--모든 직원의 주민등록번호 뒤 6자리를 마스킹처리를 해서 표현해보자
--이름, 주민번호
SELECT EMP_NAME, RPAD(SUBSTR(EMP_NO,1,8), 14, '*' ) "주민번호"
FROM EMPLOYEE;- LOWER / UPPER / INITCAP
- LOWER (STR)
: 전부 다 소문자로 변경
- UPPER(STR)
: 전부 다 대문자로 변경
- INITCAP(STR)
: 각 단어 앞글자만 대문자로 변경
SELECT LOWER('Welcome to oracle')
FROM DUAL;
SELECT UPPER('Welcome to oracle')
FROM DUAL;
SELECT INITCAP('Welcome to oracle')
FROM DUAL; - CONCAT(STR1, STR2) : 전달된 두 개의 문자열을 하나로 합친 결과로 반환
결과값 : CHARACTOR 반환
SELECT CONCAT('가나다', 'ABC')
FROM DUAL; --CONCAT함수는 3개이상 합치기 불가!
SELECT '가나다'||'ABC'||'123'
FROM DUAL; - REPLACE(STR, 찾을 문자, 바꿀 문자) : STR로부터 찾을 문자를 찾아서 바꿀문자로 변환 후 반환
결과값 : CHARACTOR로 반환
SELECT REPLACE('서울시 강남구 역삼동', '역삼동', '논현동')
FROM DUAL;
SELECT EMAIL, REPLACE(EMAIL, 'kh.or.kr', 'iei.com')
FROM EMPLOYEE;- TRIM (BOTH(양쪽) / LEADING(앞쪽) / TRAILING(뒤쪽)) -> 생략가능
: 문자열 앞/뒤/양쪽에 있는 특정 문자를 제거한 나머지 문자열을 반환
결과값 : CHARACTOR으로 반환
SELECT TRIM('ㅋ' FROM 'ㅋㅋㅋKHㅋㅋㅋ')
FROM DUAL; --양쪽(기본값) : BOTH
SELECT TRIM(LEADING 'ㅋ' FROM 'ㅋㅋㅋKHㅋㅋㅋ')
FROM DUAL; --앞쪽 : LEADING
SELECT TRIM(TRAILING 'ㅋ' FROM 'ㅋㅋㅋKHㅋㅋㅋ')
FROM DUAL; --뒤쪽 : TRAILING2) 숫자 관련된 함수
- ABS(NUMBER) : 절대값을 구해주는 함수
SELECT ABS(-10)
FROM DUAL;- MOD(NUMBER1, NUMBER2) : 두 수를 나눈 나머지 값으로 반환
SELECT MOD(10, 3)
FROM DUAL;- ROUND(NUMBER, 위치) : 반올림 처리해주는 함수
위치: 소수점 아래에 N번째 수에서 반올림
위치는 생략이 가능, 기본값은 0
SELECT ROUND(123.456)
FROM DUAL;
SELECT ROUND(123.456, 1)
FROM DUAL;- CEIL(NUMBER) : 소수점 아래에 수를 무조건 올림 처리해주는 함수
SELECT CEIL(123.456)
FROM DUAL;- FLOOR(NUMBER): 소수점 아래의 수를 무조건 버림처리해주는 함수
SELECT FLOOR(123.888)
FROM DUAL;
--각 직원별로 고용일로부터 오늘까지 근무일수를 조회
SELECT EMP_NAME, CONCAT(FLOOR(SYSDATE - HIRE_DATE), '일') "근무일수"
FROM EMPLOYEE;3) 형변환 함수
- TO_CHAR(NUMBER/DATE) : 숫자형 날짜형 데이터를 문자형타입으로 변환(CHARACTOR타입으로 반환)
SELECT SYSDATE
FROM DUAL;
SELECT TO_CHAR(SYSDATE)
FROM DUAL;
SELECT TO_CHAR(1234, '00000')
FROM DUAL; --빈칸에 0을 채워넣겠다 -> 01234
SELECT TO_CHAR(1234, '99999')
FROM DUAL; --빈칸에 공백을 채워넣겠다
SELECT TO_CHAR(1234, 'L00000')
FROM DUAL;
SELECT TO_CHAR(1234, 'L99,999')
FROM DUAL;
SELECT EMP_NAME, TO_CHAR(SALARY, 'L999,999,999') 급여
FROM EMPLOYEE;4) NULL 처리 함수
- NVL(컬럼명, 반환할 결과값) : 해당 컬럼 값이 NULL일 경우 반환할 결과값을 넣어줄 수 있음
SELECT EMP_NAME, BONUS, SALARY + (SALARY * NVL(BONUS,0))*12 "보너스 포함 연봉"
FROM EMPLOYEE;
SELECT EMP_NAME, NVL(DEPT_CODE, '부서없음') "부서"
FROM EMPLOYEE;
--NVL2
SELECT EMP_NAME, NVL2(DEPT_CODE, '부서있음', '부서없음')
FROM EMPLOYEE;5) 선택 함수
- DECODE(비교대상(컬럼명/산술연산/함수), 조건값, 결과값..) : JAVA에서 switch문과 비슷
--사원명, 주민등록번호로부터 성별자리를 추출해서 1/2로 조회
SELECT EMP_NAME, DECODE(SUBSTR(EMP_NO, 8, 1), 1, '남', 2, '여')
FROM EMPLOYEE;
--직원들의 급여를 인상시켜서 조회
--직급코드가 'J7'인 사원은 급여를 10%인상해서 조회
--직급코드가 'J8'인 사원은 급여를 15%인상해서 조회
--직급코드가 'J5'인 사원은 급여를 20%인상해서 조회
--그 외의 직급코드인 사원들은 급여를 5% 인상 후 급여
SELECT EMP_NAME
, JOB_CODE
, SALARY "인상 전 급여"
, DECODE(JOB_CODE
, 'J7', (SALARY + SALARY*0.1)
, 'J8', (SALARY + SALARY*0.15)
, 'J5', (SALARY + SALARY*0.2)
,(SALARY + SALARY*0.05)) "인상 후 급여"
FROM EMPLOYEE;- CASE WHEN THEN : JAVA에서 if문과 비슷
[표현식]
CASE WHEN 조건식1 THEN 결과값1
WHEN 조건식2 THEN 결과값2
..
ELSE 결과값
END
SELECT EMP_NAME, DECODE(SUBSTR(EMP_NO, 8, 1), 1, '남', 2, '여')"성별"
FROM EMPLOYEE;
--CASE WHEN THEN 구문
SELECT EMP_NAME,
CASE
WHEN SUBSTR(EMP_NO, 8, 1) = '1' THEN '남'
ELSE '여'
END
FROM EMPLOYEE;
--사원명, 급여, 급여등급(고급, 중급, 초급)
--SALARY값이 500만원 초과일 경우 '고급'
-- 500만원 이하 350만원 초과일 경우 '중급'
-- 350만 이하일 경우 '초급'
SELECT EMP_NAME, SALARY,
CASE
WHEN SALARY > 5000000 THEN '고급'
WHEN 3500000 < SALARY AND SALARY <= 5000000 THEN '중급'
ELSE '초급'
END
FROM EMPLOYEE;< 그룹 함수 >
: N개의 값을 읽어서 1개의 결과를 반환(그룹별로 하나의 결과를 내놓음)
1) SUM(숫자타입컬럼) : 해당 컬럼 값들의 총 합계를 반환해주는 함수
-- 전체 사원들의 총 급여 합계
SELECT SUM(SALARY) "급여합계"
FROM EMPLOYEE;
--부서코드 'D5'인 사원들의 총 급여 합
SELECT SUM(SALARY)
FROM EMPLOYEE
WHERE DEPT_CODE = 'D5';
--남자사원들의 총 급여
SELECT SUM(SALARY)
FROM EMPLOYEE
WHERE SUBSTR(EMP_NO, 8, 1) = '1';2) AVG(숫자타입컬럼) : 해당 컬럼값들의 평균값을 구해서 반환
--전체사원들의 평균 급여
SELECT ROUND(AVG(SALARY))
FROM EMPLOYEE;3) MIN : 해당컬럼값들의 가장 작은 값을 반환
--전체 사원들 중에서 최저 급여, 가장 입사일이 빠른 사람, 가장 이름이 빠른 사람
SELECT MIN(SALARY), MIN(HIER_DATE), MIN(EMP_NAME)
FROM EMPLOYEE;4) MAX : 해당 컬럼값의 가장 큰 값을 반환
SELECT MAX(SALARY), MAX(HIRE_DATE), MAX(EMP_NAME)
FROM EMPLOYEE;5) COUNT : 조회된 행 개수를 세서 반환
--COUNT(*) : 조회결과에 해당하는 모든 행 개수를 다 세서 반환
--COUNT(컬럼명) : NULL이 아닌 것만 행 개수를 세서 반환
--COUNT(DISTINCT 컬럼명) : 중복값이 있을 경우 하나로만 개수 COUNT
SELECT COUNT(*) FROM EMPLOYEE;
--여자사원 수만 조회
SELECT COUNT(*)
FROM EMPLOYEE
WHERE SUBSTR(EMP_NO, 8, 1) = '2';
--부서 배치가 완료된 사원 수
SELECT COUNT(*)
FROM EMPLOYEE
WHERE DEPT_CODE IS NOT NULL;
--현재 사원들이 속해있는 부서의 개수
SELECT COUNT(DISTINCT DEPT_CODE)
FROM EMPLOYEE;- 실습문제
1. 직원명과 주민번호를 조회함. 단, 주민번호 9번째 자리부터 끝까지 '*'문자로 채움
EX) 이승철 121212-1******
2. 직원명, 직급코드, 연봉(원) 조회
단 연봉은 보너스 적용된 1년치 급여 TO_CAHR
3. 부서코드가 D5, D9인 직원들 중에서 2004년도에 입사한 직원의 수 조회 COUNT
SELECT EMP_NAME, RPAD(SUBSTR(EMP_NO, 1, 8), 14, '*')
FROM EMPLOYEE;
SELECT EMP_NAME, JOB_CODE, TO_CHAR((SALARY + (SALARY * NVL(BONUS,0))*12),'L999,999,999') "연봉"
FROM EMPLOYEE;
SELECT COUNT(DEPT_CODE)
FROM EMPLOYEE
WHERE DEPT_CODE IN ('D5', 'D9')
AND SUBSTR(HIRE_DATE,1,2) = '04';