Oracle (feat. docker)

※ Oracle Database 11g Express Edition 중심으로

자바에서 Oracle DB를 사용할 때 JDBC 라이브러리를 사용하게 되는데 이때 JDBC를 사용하기 위해 몇가지 필요한 정보가 있다. 즉 JDBC를 통해 Oracle DB에 connection하기 위해 몇 가지 필요한 정보가 있다. 대표적으로 Oracle 사용자 계정에 대한 id, password... 그 중에서 Oracle database에 대한 url 정보가 있다. 대체로 다음과 같은 형태이다.

jdbc:oracle:thin:@localhost:1521:xet

이것이 무엇을 의미하는지에 대해서 살펴본다. 이에 대한 프로토타입은 다음과 같다.

jdbc:oracle:driver_type:[username/password]@[//]host_name[:port][/XE]

위의 프로토타입으로부터 아래 정보를 해석해 보면 다음과 같다.

jdbc:oracle:thin:@localhost:1521:xe

-. jdbc:oracle:thin은 사용하는 JDBC드라이버가 thin 타입을 의미한다. 자바용 오라클 JDBC드라이버는 크게 두가지가 있는데 하나는 Java JDBC THIN 드라이버고, 다른 하나는 OCI기반의 드라이버라고 한다.

-. username/password은 option이다. [ ]안에 있는 정보는 반드시 명기할 필요는 없다는 뜻이다.

-. :port 번호도 option이다. 다만 Oracle의 listener port인 1521을 사용하지 않을 경우는 이 값을 명기해 줘야 된다. 예를 들어서 jdbc:oracle:thin:hr/hr@//localhost:1522

-. localhost는 Oracle DB가 설치되어 있는 서버의 IP인데 위 경우는 로컬에 설치되어 있다는 뜻이다.

-. 1521 은 오라클 listener의 포트번호이다.

-. /XE는 Oracle database client의 고유한 service name이다. 디폴트로 XE를 사용하므로 이 정보도 option이다. 이에 대한 설정 정보는 Oracle이 설치된 폴더 아래의 app\oracle\product\11.2.0\server\network\ADMIN\listener.ora 파일에 다음과 같이 표시되어 있다

SQLPLUS 를 docker 에서 접속

docker exec -it oracle12c sqlplus

계정 변경

conn kh/1234

SQLPLUS 계정 수정하기

alter user hr account unlock; ==> hr유저의 상태를 unlock

USER hr의 비밀번호를 1234로 지정

alter user hr identified by 1234;

화면 clear

cl scr;

계정 만들기 (id = kh , pwd = 1234 )

create user kh identified by 1234 default table space users;

권한 주기

grant connect to kh;

로그아웃

disconnect

현 사용자 확인

show user

username , account_status 확인

select username , account_status from dba_users

sql 파일 실행하기

start 파일경로

접속시 오류 및 해결 방법

SQL> connect username/password

Error accessing PRODUCT_USER_PROFILE

Warning: Product user profile information not loaded!

You may need to run PUPBLD.SQL as SYSTEM

Connected.

SQL>

 

Oracle User 접속시 Profile 을 액세스 하는 오류입니다.

SYSTEM User 계정으로 PUPBLD.SQL 실행이 필요합니다.

 

SQL> connect system/password Connected. SQL> @?/sqlplus/admin/pupbld.sql

일반관리자 , 슈퍼 관리자

1. sys (슈퍼 관리자) - 데이터베이스 생성/삭제하는 권한 있음

   데이터베이스 관련 모든 문제를 처리 가능

   로그인시 SYSDBA룰로 접속해야함

2. system (일반 관리자) - 데이터베이스 생성/삭제 권한 X

   xxxxxxxxxx
    데이터베이스 유지보수
   

no privileges on tablespace 'USERS' , 테이블 스페이스 권한 주기

grant create session, create table, create view,resource to (User_Name);

테이블 스페이스 권한 주기

xxxxxxxxxx
ALTER USER <user> quota 100M on <tablespace name>;

예제

show user;

--kh가 가진 테이블 조회 select * from tab;

--하나씩 조회 select * from department; select * from employee; select * from job; select * from location; select * from nation; select * from sal_grade;

--일반관리자, 슈퍼관리자 -- 1. sys(슈퍼관리자) : -- 데이터베이스를 생성/삭제하는 권한 있음 -- 데이터베이스 관련 모든 문제를 처리 가능 -- 로그인 시 SYSDBA로 접속해야함

-- 2. system(일반관리자) : -- 데이터베이스 생성/삭제 권한이 없음 -- 데이터베이스를 유지보수하는 관리자

/*

Table

• 데이터를 담고 있는 객체
• 데이터에 대해서 생성, 조회, 수정, 삭제(CRUD)작업을 하는 대상
• 행과 열로 이루어져 있다.

Table(Eneity, Relation) : Employee 테이블과 테이블을 붙여서(조인) = Relation Column(Filed, Attribute : Emp_Id 200 201 202... Row(Record, Tuple) : 행, 200 선동일 621225-1985634 sun_di@kh.or.kr 01099546325 D9 J1 S1 8000000 0.3 90/02/06 N Domain : 하나의 컬럼(속성_이 취할 수 있는 같은 타입으로 이루어진 값의 집합 ​ gender : 남, 여 (여기서 남, 여를 도메인이라고 함) */

--하나의 테이블 정보조회 desc employee; --desc : 내림차순 --null여부 (nullable) --1. not null : 필수요소 --2. null : 선택요소

/* Data type

1. 문자형 CHAR : 고정길이 문자형(최대 2000byte) VARCHAR2 : 가변길이의 문자형(최대 4000byte)

NCHAR : char와 동일하나 유니코드 문자개수로 처리 (고정형), 1000개까지 됨. 유니코드(2byte라서) NVARCHAR2 : varchar2와 동일하나 유니코드 문자개수로 처리 (가변형), 2000개까지 됨.

char(10) 고정형으로 문자열 10byte 'korea' => 실제로는 5byte지만, 저장시에는 10byte를 모두 사용한다. 빈공백으로 잔여크기 채움 >>> 고정형 '대한민국' => 글자당 2byte지만, 저장시에는 8byte가 아닌 10byte를 모두 사용 '대한민국 서울시' => 글자2, 공백1 : 15byte지만 최대크기가 10byte로 지정되어있어 에러가 발생된다.

varchar(20) 가변형으로 문자열 20byte까지 저장가능 'korea' : 실제 5byte, 저장도 5byte '대한민국' : 실제 8byte, 저장도 8byte '대한민국 서울시' : 실제 15byte, 저장도 15byte 물론 20byte보다 크면 오류

nchar(10) 고정형, 문자열개수가 10개까지 허용 nvarchar2(10) 가변형, 문자열개수가 10개까지 허용

long : 최대 2GB까지 작성 가능한 가변형 문자타입

*/

create table tb_chartype( ​ col1 char(8), ​ col2 varchar2(8), ​ col3 nchar(6), ​ col4 nvarchar2(6) );

--데이터입력(create) Insert into tb_chartype values('abc', 'abc', 'abc', 'abc');

Insert into tb_chartype values('가다', '하다', '호호나', '푸호나'); --오라클에서 한글처리는 2byte지만, 11g xe버전은 3byte로 처리된다.

select * from tb_chartype;

--메모리에서 작업인것을 취소 rollback;

--메모리에서 작업한 내용을 실제 파일에 적용 commit;

/*

2. 숫자형 number([p,s]) -- precision : 표현할 수 있는 전체 숫자 자리수(1~38) -- scale : 소수점이하 자리수(-84 ~ 127)

데이터입력값이 1234 678 일때,

데이터 타입 저장되는 값

number(7,3) 1234.678 number(7) 1235 number 1234.678 number(7,1) 1234.7 (반올림) number(5, -2) 1200 (마이너스는 소수점을 기준으로 왼쪽, 0으로 처리)

3. 날짜형 : date 일자(년/월일) 및 시간(시/분/초) 정보를 관리 기본적으로는 화면에 년/월/일만 표시됨 (물론 시분초도 저장되어있음) 날짜의 연산 또는 비교가 가능함

연산 결과타입 설명

날짜+-숫자 date 하루를 1로 계산해서 처리함 날짜+-날짜 number 두 날짜의 차이를 리턴(하루가 1기준) */

select sysdate from dual; select sysdate-1 from dual; select sysdate+1 from dual;

select to_char(sysdate, 'yyyy/mm/dd hh:Mi:ss')from dual;

-- 정확한 시각정보(밀리초 이하)를 표현하기 위한 timestamp타입도 있다 select systimestamp from dual; /*

4. LOB형 clob : 최대 4GB 허용가능한 문자타입 */

--실습문제 : today라는 회사의 회원테이블을 작성할 때, -- 다음 필드에 적절한 db데이터 타입을 설정해서 tb_today_member 라는 테이블을 생성하세요. /*

1. id : 8자리에서 15자리
2. pw : 8자리 이상 15자리
3. name
4. phone : -없이 11자리
5. ssn : 주민번호 -없이 13자리
6. mileage : 회원 마일리지
7. reg_date : 가입일 */

create table tb_today_member( ​ id varchar2(15) NOT NULL,

pw char(15) NOT NULL,

name varchar2(100) NOT NULL,

phone char(11) NOT NULL,

ssn char(13) NOT NULL,

mileage number,

reg_date date

);

 

---

--## SQL

/*

Structed Query Language : 구조화된 질의 언어

dbms사용되는 SQL문법이 조금씩 다름

DDL (Data Definition Language) 데이터베이스의 구조, 객체에 대한 언어 ex) create , drop , alter

DML (Data Manipulation Language) 저장된 데이터의 조작 ex) insert, update , delete, select ==> CRUD

DCL (Data Control Language) 사용자권한, 설정 ex) grant , revoke

TCL (Transaction Control Language) ex) commit , rollback, savepoint

*/

 

--##DQL

/*

데이터를 검색(추출)하기 위한 언어

DML로 분류

데이터를 조회한 결과를 ResultSet(결과집합)

-> 0개 이상의 행이 포함

-> 특정 기준에 의해서 정렬될 수 있음

 

1. select : 조회하고자 하는 컬럼명을 기술

2. from : 조회하고자 하는 테이블명 기술

3. where : 특정 행을 선출하는 조건을 기술

4. group by :

5. having :

6. order by : 특정 컬럼을 대상으로 오름, 내림 차순 지정 asc , desc

   처리 되는 순서 From -> where -> group by -> having -> select -> order by (중요 앙앙)

*/

select * from employee;

select emp_id, emp_name,salary from employee;

select emp_id, emp_name,salary,job_code from employee where job_code='J5' order by emp_name asc;

select emp_id,emp_name,salary,job_code -- 3

from employee --1

where job_code='J5' -- 2

order by emp_name; -- 4

 

—1. job 테이블에서 job_name 컬럼만 출력

select job_name

from job;

--2. department테이블의 전체 컬럼을 출력

select *

from department;

--3 employee테이블에서 이름,이메일, 전화번호, 입사일을 출력

select emp_name,email,phone,hire_date

from employee;

—4. employee테이블에서 월급이 2,500,000원 이상인 사람만 출력

select *

from employee

where salary >= 2500000;

—5. employee테이블에서 월급이 350만원 이상이면서 job_code가 'J3 '인 사람의 이름

select emp_name

from employee

where salary >= 3500000 and job_code = 'J3';

--6 산술연산

— 연봉 : 월급 * 12 + (월급보너스) * 12

select emp_name,

salary,

bonus,

salary * 12,

(salary +(salary * nvl(bonus,0))) *12

from employee;

 

— null처리 함수 : nvl(col, val) ==> 값을 리턴

— col의 값이 null인 경우 val을 리턴

— col의 값이 null이 아닌 경우 col의 값을 리턴

select nvl(7,3), nvl(null,3), nvl('이순신','거북선'), nvl(null,'거북선')

from dual;

select nvl(bonus, 0), bonus

from employee;

— 컬럼명 별칭(alias)지정

— as "별칭" ===> as 와 "" 생략 가능

-- as는 항상 생략이 가능하지만 ""을 반드시 써야하는 경우가 있음 : 공백이 포함된 경우, 숫자로 시작되는 경우

 

— 컬럼에 리터럴(literal)지정

— 리터럴은 resultset의 모든 행에 반복 표시

select emp_name, salary, '원' as 단위 , 12345

from employee;

 

— 중복값 제거 distinct

— select절에 한번만 사용 가능, 여러 컬럼을 사용하면, 여러 컬럼을 합친 것을 고유값으로 간주

select distinct job_code

from employee;

 

— 연결 연산자 ||

— 문자열연결, 기타값과 문자열연결에 +연산자가 아닌 || 연산자 사용

— 문자는 왼쪽 정렬, 정수형은 오른쪽 정렬

select emp_name, salary || '원' as "연봉"

from employee;

 

— where절에 사용하는 연산자

/*

-1. 논리연산자

and

or

not : 해당 결과를 반전 (null 제외)

-2. 비교 연산자

=

< , >

<= , >=

<> , ^= , != : 같지 않다

between 값1 and 값2 : 값1 이상 값2 이하

like , not like : 문자 패턴비교

is null , is not null : null여부 비교 -> where bonus is not null

in , not in : 비교값 목록에 포함되었는지 여부

*/

 

— 부서코드 D6이고, 급여를 2000000원보다 많이 받는 사람의 이름, 부서, 월급을 출력

select emp_name,dept_code,salary

from employee

where dept_code = 'D6' and salary > 2000000;

 

— 직급코드가 J1이 아닌 사원들의 월급등급(sal_level)을 중복없이 출력

select distinct sal_level

from employee

where job_code != 'J1';

 

— 급여를 3500000원 이상, 6000000 이하 의 이름과 연봉 출력

select emp_name,salary

from employee

where salary >= 3500000 and salary <= 6000000;

 

— 숫자 , 날짜에 대해서 사용가능

— 입사일이 90/01/01 ~ 01/01/01인 사원을 조회

— 기본 date 형식을 사용하면 됨

select *

from employee

where hire_date between '90/01/01' and '01/01/01';

 

— 문자열 패턴비교 like

— 비교하려는 값이 지정한 문자패턴을 만족시키면 true를 리턴함

— 와일드카드 % _를 사용함

— % : 글자가 없든지, 있으면 true

-- _ : 임의의 문자 1개를 의미

 

select emp_name,salary

from employee

where emp_name like '형';

 

--email에서 _의 앞자리가 3자리인 직원을 조회 --이스케이핑처리 : 임의의 문자를 이스케이프 문자로 사용하고, 이를 명시

 

create table tb_watch(

watchname varchar2(40),

description varchar2(200)

);

insert into tb_watch values ('금시계','순금 99.99% 함유 고급시계');

insert into tb_watch valuse('은시계','고객만족도 99.99점을 획득한 고급시계');

commit;

실습문제

xxxxxxxxxx
#@실습문제
​
1. EMPLOYEE 테이블에서 이름, 연봉, 총수령액(보너스포함), 실수령액(총 수령액-(월급*세금 3%))가 출력되도록 하시오
(컬럼명 변경)
​
2. EMPLOYEE 테이블에서 이름, 근무 일수(입사한지 몇일인가)를 출력해보시오.
​
3. EMPLOYEE 테이블에서 20년 이상 근속자의 이름,월급,보너스율를 출력하시오
​
4. EMPLOYEE 테이블에서 이름 끝이 연으로 끝나는 사원의 이름을 출력하시오 
​
5. EMPLOYEE 테이블에서 전화번호 처음 3자리가 010이 아닌 사원의 이름, 전화번호를 출력하시오
    
6. EMPLOYEE 테이블에서 메일주소 '_'의 앞이 4자이면서, DEPT_CODE가 D9 또는 D6이고 고용일이 90/01/01 ~ 00/12/01이면서, 월급이 270만원인 사원의 전체 정보를 출력하시오

1

select emp_name as "이름" , (salary * 12) || '원' as "연봉" , ​ (salary + (salary * nvl(bonus,0))) * 12 as "총수령액", ​ ((salary + (salary * nvl(bonus,0))) * 12) - (salary * 0.03) as "실수령액" ​ from employee;

2

select emp_name as "이름",

trunc(sysdate - hire_date) as "근무 일수"

from employee;

3

select emp_name as "이름",

salary as "월급",

nvl(bonus,0) as "보너스율"

from employee

where trunc(sysdate - hire_date) >= (365 * 20);

4

select emp_name as "이름"

from employee

where emp_name like '%연';

5

xxxxxxxxxx
select emp_name as "이름",
  phone as "전화번호"
  from employee
  where phone not like '010%';

6

xxxxxxxxxx
select *
    from employee
    where email like '____\_%' escape '\'
    and (dept_code = 'D9' or dept_code = 'D6')
    and hire_date between '90/01/01' and '00/12/01'
    and salary >= 2700000;

비교연산자 in

비교 하려는 값 목록에 일치하는 것이 있는가? 있으면 true, 없으면 false

D6 , D8 부서인 사원들을 조회하라

xxxxxxxxxx
select *
  from employee
  where dept_code = 'D6' or dept_code = 'D8';
--------  
select *
  from employee
  where dept_code in('D6','D8');

D6, D8 부서가 아닌 사원들을 조회하라

xxxxxxxxxx
select *
  from employee
  where dept_code != 'D6' and dept_code != 'D8';
​
select *
  from employee
  where dept_code not in('D6','D8');

연산자 우선순위

1. 산술 연산자
2. 연결연산자 ||
3. 비교연산자
4. IS NULL / IS NOT NULL, LIKE , IN / NOT IN
5. BETWEEN AND / NOT BETWEEN AND
6. NOT
7. AND
8. OR

부서코드가 J7 또는 J2이고, 급여가 2000000원 이상인 사원 조회

select *

from employee

where job_code in ('J7','J2')

and salary >= 2000000;

order by

select한 컬럼에 대해서 정렬기준을 제시

order by 컬럼명 | 별칭 | 컬럼순서 {정렬방식 (asc , desc)][NULLS FIRST | LAST]

xxxxxxxxxx
select emp_name
  from employee;
  order by emp_name desc;
  
select emp_id as "아이디",
  emp_name as "사원명",
  dept_code as "부서코드"
  from employee
  order by 아이디 desc;
  
// 부서코드로 선 정렬 후, 그안 에서 다시 사원명으로 정렬
select emp_id as "아이디",
  emp_name as "사원명",
  dept_code as "부서코드"
  from employee
  order by 3,2;

FUNCTION

일련의 처리과정을 반복적으로 사용하기 위해서 작성해둔 서브프로그램

호출시 값을 전달하면, 수행결과를 리턴하는 방식 (반드시 값을 리턴 , void X)

 

함수의 유형

1. 단일행 처리 함수

   • 문자처리함수
   • 숫자처리함수
   • 날짜처리함수
   • 형변환함수
   • 기타함수

2. 그룹 함수

   • group by
   • having

단일행 처리 함수

1. 문자 처리 함수

   length : 문자 개수를 리턴

xxxxxxxxxx
select emp_name, length(emp_name),
  email, length(email)
  from employee;

lengthb : 문자열의 byte를 리턴

xxxxxxxxxx
select emp_name, lengthb(emp_name),
  email, lengthb(email)
  from employee;

instr : 찾는 문자열이 대상문자열에서 지정한 위치부터 지정한 회수번째에 나타난 위치를 반환

instr(대상문자열, 찾고자하는 문자열[,시작 인덱스 [, 몇번째]])

오라클에서 인덱스는 0기반이 아닌 1기반이다.

xxxxxxxxxx
select instr('kh정보교육원 국가정보원 정보문화사','정보',1,1),
     instr('kh정보교육원 국가정보원 정보문화사','정보',4),
     instr('kh정보교육원 국가정보원 정보문화사','정보',4,2),
     instr('kh정보교육원 국가정보원 정보문화사','정보',-6),  -- 음수는 역방향으로 찾는다, 오른쪽에서 왼쪽으로 검색
  from dual;

연습문제

email컬럼에서 @의 위치인덱스값을 출력

select email,

instr(email,'@')

from employee;

lpad / rpad : lpad(string,byte[,paddingString])

byte공간에 string문자열을 쓰고, 남은 공간 왼쪽/오른쪽에 paddingString으로 채워라

xxxxxxxxxx
select lpad(email,20,'#'), rpad(email,20,'#'),
  lpad(email,20), rpad(email,20)
  from employee;

ltrim / rtrim

ltrim / rtrim(string, str)

xxxxxxxxxx
select ltrim('             kh',' '),
  ltrim('               kh'),
  ltrim('000123456','0'),
  ltrim('12343124556797923kh','123456789') — 삭제할 문자열은 문자단위로 처리 (하나하나)
  from dual;

trim : 양쪽에서 지정한 문자를 제거

xxxxxxxxxx
select trim('        kh       '),
  trim('z' from 'zzzzzzzzkhzzzzz'),
  trim(leading 'z' from 'zzzzzzkhzzzzzzz'), -- 앞쪽 z만 제거
  trim(trailing 'z' from 'zzzzzkhzzzzzzz'),   -- 뒤쪽 z만 제거
  trim(both 'z' from 'zzzzzzkhzzzzzzzz')    -- 양쪽 z 제거
  from dual;
  
  요놈은 제거할 문자열 하나만 지정할 수 있다,

연습문제

1231233546356542hello48763987649387461

xxxxxxxxxx
select rtrim(ltrim('1231233546356542hello48763987649387461' , '123456789') , '123456789')
  from dual;

substr

substr(string, position ,[, length])

xxxxxxxxxx
select substr('showmethemoney', 5,2),
  substr('showmethemoney',10),
  substr('showmethemoney',-8,3)
  from dual;

lower / upper / initcap

xxxxxxxxxx
select lower('ABCDE'),
  upper('abcde'),
  initcap('abcde')    -- 첫글자만 대문자 잉잉
  from dual;

concat : 문자 연결함수이지만 , 2개의 문자만 처리가능 , 3개 이상연결에는 ||사용할 것

xxxxxxxxxx
select concat('abc','def')
  from dual;
  
select 'abc'||'def'||'ghi'
  from dual;

replace : replace(대상문자열,fromString, toString)

xxxxxxxxxx
select replace('show me the money','money','gimothy')
  from dual;

연습문제

1. 사원명에서 성만 중복없이 출력(성은 한글자라고 가정)

   xxxxxxxxxx
   select distinct substr(emp_name , 1 , 1)
     from employee;
     order by 1;
   

2. employee테이블에서 남자만 사원번호,사원명,주민번호 출력(주민 뒷자리 *출력)

   xxxxxxxxxx
   select emp_name as "사원명",
     emp_id as "사원번호",
     rpad(substr(emp_no , 1,7) ,13, '*')
     from employee
       where substr(emp_no , 8 , 1) = '1' or substr(emp_no , 8 , 1) = '3';
   

3. 다음 tb_files테이블에서 파일명만 출력

xxxxxxxxxx
create table tb_files(
  fileno number(3),
  filepath varchar2(500)
);
​
insert into tb_files values(1,'C:\abc\def\salesinfo.xls);
insert into tb_files values(2,'C:\music\안녕.mp3);
insert into tb_files values(3,'C:\movies\서치.mp4);

xxxxxxxxxx
select substr(filepath,instr(filepath , '\' , -1) + 1)
    from tb_files;

숫자 처리 함수

1. abs : 절대값

   xxxxxxxxxx
   select abs(10),
     abs(-10)
     from dual;
   

2. mod : 나머지 구하는 함수

   %가 아닌 mod함수를 사용

   xxxxxxxxxx
   select mod(10,3),
     mod(10,2),
     mod(10,4)
     from dual;
   

3. round : 반올림

   round(nunber, [, position])

   xxxxxxxxxx
   select round(123.456 , 3),
     round(123.456 , 2),
     round(123.456 , 1),
     round(123.456 , 0),   -- 0은 생략 가능
     round(123.456 , -1),
     round(123.456 , -2),
     from dual;
   

4. ceil

   xxxxxxxxxx
   select cell(123.456)
     from dual;
   

   12.3456을 소수점 둘째자리까지 올림처리해서 표현하기

   xxxxxxxxxx
   select cell(123.456*100)
     from dual;
   

5. floor

   xxxxxxxxxx
   select floor(10.5),
     foor(10.51)
     from dual;
   

6. trunk : trunc(number , position)

   xxxxxxxxxx
   select trunc(123.456 , 1),
     trunc(123.456 , 2),
     trunc(123.456),
     trunc(123.456 , -1)
     from dual;
   

날짜처리함수

1. sysdate

   xxxxxxxxxx
   // 시분초 표현
   select to_char(sysdate - 1, 'yyyy-mm-dd hh24:mi:ss'),
     to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'),
     to_char(sysdate + 1, 'yyyy-mm-dd hh24:mi:ss')
     from dual;
     
   // 어제 , 오늘 , 내일 표현
   // 하루를 1로 치환해서 연산
   select sysdate - 1, sysdate , sysdate + 1
     from dual;
     
   // 한시간 전
   select to_char(sysdate - 1 / 24, 'yyyy-mm-dd hh24:mi:ss')
     from dual;
     
   // 30분전
   select to_char(sysdate - (30*1/24/60), 'yyyy-mm-dd hh24:mi:ss')
     from dual;
   

2. add_months : add_months(date, number);

   xxxxxxxxxx
   select add_months(sysdate , 1)
     from dual;
     
   // 입사 후 3개월 후 날짜 조회
   select emp_name, hire_date, add_months(hire_date , 3);
     from dual;
   

3. months_between : months_between(date1, date2)

   date1 과 date2의 날짜 차이를 month단위로 리턴

   1이 리턴

   xxxxxxxxxx
   select months_between(add_months(sysdate , 12) - sysdate)
     from dual;
     
   // 수료 일로부터 남은 개월수
   select months_between(to_date('19/03/12', 'yy/mm/dd') , sysdate)
     from dual;
   

연습문제

근무개월수 구하기

xxxxxxxxxx
select trunc(months_between(sysdate , hire_date)) || '개월' as "근무 개월수"
  from employee;

군복무 1년 6개월, 전역일, 전역일까지 먹어야 할 짬밥의 수

xxxxxxxxxx
select add_months(sysdate , 18) as "전역일",
  (add_months(sysdate , 18) - sysdate) * 3 as "짬밥 수"
  from dual;

4. next_day : 인자로 전달받은 요일데이터를 토대로 가장 빠른 해당요일을 리턴

   next_day(date, string | number)

xxxxxxxxxx
-- 요일을 추출
select to_char(sysdate, 'day') "월요일",
  to_char(sysdate, 'dy')  "월",
  to_char(sysdate, 'd') "1" -- 일요일 1
  from dual;
  
select next_day(sysdate, 'thursday'),
    next_day(sysdate, 'monday'),
  next_day(sysdate, 6)
  from dual;

5. last_day : 해당일자를 기준으로 해당 월의 말일을 리턴

   xxxxxxxxxx
   select last_day(sysdate)
     from dual;
   

6. extract : 특정일에서 년월일 정보를 추출

   extract(year|month|day from date)

   xxxxxxxxxx
   select extract(year from sysdate) "year",
       extract(month from sysdate) "month",
       extract(day from sysdate) "day"
     from dual;
   

7. trunc : trunc(date) : 시분초 정보를 00:00:00으로 처리

   xxxxxxxxxx
   select to_char(trunc(sysdate), 'yyyy-mm-dd hh24:mi:ss'),
     to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss')
     from dual;
   

// docker 의 컨테이너로 진입

xxxxxxxxxx
docker exec -t -i container_name /bin/bash

// /etc/localtime 링크

xxxxxxxxxx
sudo ln -sf /usr/share/zoneinfo/Asia/Seoul /etc/localtime

xxxxxxxxxx
-- 1
select department_name as "학과 명",
    category as "계열"
    from tb_department;
    
-- 2
select department_name || '의 정원은 ' || capacity || ' 입니다.' as "학과별 정원"
    from tb_department;
    
-- 3
select student_name
    from tb_student
    where absence_yn = 'Y'
    and department_no = '001'
    and (substr(student_ssn , 8 , 1) = '2' or substr(student_ssn , 8 , 3) = '3');
    
-- 4
select student_name
    from tb_student
    where student_no in('A513079','A513090','A513091','A513110','A513119')
    order by 1;
    
-- 5
select department_name as "학과 이름",
    category as "계열"
    from tb_department
    where capacity >= 20 and capacity <= 30;
    
-- 6
select professor_name
    from tb_professor
    where department_no is null;
    
-- 7
select *
    from tb_student
    where department_no is null;
    
-- 8
select class_no as "과목 이름"
    from tb_class
    where preattending_class_no is not null;
    
-- 9
select distinct category
    from tb_department
    order by 1;
    
-- 10
select student_no as "학번",
    student_name as "이름",
    student_ssn as "주민번호",
    student_address as "주소"
    from tb_student
    where absence_yn = 'N'
    and student_address like '%전주%'
​

형변환 함수

xxxxxxxxxx
  to_char()              to_number()
    ----------->            ----------->
  DATE          CHARACTER          NUMBER 
    <----------             <----------
    to_date()              to_char()

xxxxxxxxxx
select to_char(sysdate-1, 'yyyy-mm-dd-hh24:mi:ss') "어제",
    to_char(sysdate, 'yyyy-mm-dd-hh24:mi:ss') "오늘",  
    to_char(sysdate+1, 'yyyy-mm-dd-hh24:mi:ss') "내일"
from dual;

날짜에서 앞에 0 지우기

xxxxxxxxxx
select emp_name "사원명",
      to_char(hire_date, 'fmyyyy-mm-dd (dy)') "입사일" --fm은 공백제거 (0을 제거함!)
from employee;

sysdate 연산

xxxxxxxxxx
--2. 현재시각으로부터 1일 2시간 3분 4초뒤를 출력 (yyyy-mm-dd hh24:mi:ss)
​
select (to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss')) "현재",
      (to_char(sysdate+1, 'yyyy-mm-dd hh24:mi:ss')) "+1일뒤",
      (to_char(sysdate+(1 + (1/24)*2), 'yyyy-mm-dd hh24:mi:ss')) "+2시간뒤",
      (to_char(sysdate+((1 + (1/24)*2)+((1/24)/60)*3), 'yyyy-mm-dd hh24:mi:ss')) "+3분뒤",
      (to_char(sysdate+((1 + (1/24)*2)+((1/24)/60)*3) + (1/24/60/60*4), 'yyyy-mm-dd hh24:mi:ss')) "+4초뒤"
from dual;

1이 1일이다

to_char 포맷

xxxxxxxxxx
-- to_char(number([,format])
/*
, ==> 9.999 : 세자리마다 콤마
. ==> 9.99 : 소수점이하 2자리까지 표현
9 ==> 999 해당 자리의 숫자
0 ==> 900 해당자리의 숫자가 없을 시 0으로 표현
$ ==> $999 통화기호
L ==> L999 Local통화로 표시 >> 한국은 \으로 표시됨
fm ==> fm999 포맷9로부터 치환된 공백 혹은 소수점 이하 0을 제거함
*/
​
​
select to_char(1234567, '999,999,999'),
      to_char(1234567, '999,999'), -- << 오류남 범위를 벗어남
      to_char(1234567, 'L999,999,999'),
      to_char(1234567, '999,999,999.999'),
      to_char(1234567, 'fm000,000,000.000')
from dual;

xxxxxxxxxx
select trunc((to_date('201903122130','yyyymmddhh24mi') - sysdate) * 24 *60 * 60) -- 초 구하기
    from dual;
    
-- ?일 ?시간 ?분 ?초 남았습니다.
--151.201608~~~~~~~~~~~~~
--일단위 -> 초단위 -> 결과도출
? == 초
select trunc(?/60/24/24) as "일",
  mod(trunc(?/60/60),24) as "시간",
  mod(tunc(?/60),60) as "분",
  mod(?,60) as "초"
  from dual;

to_number

to_number(character[, format])

1,000,000,000 - 50,000,000

xxxxxxxxxx
select to_number('1,000,000,000','9,999,999,999')  - to_number('50,000,000','999,999,999')
    from dual;

자동 형변환

xxxxxxxxxx
select '1000' + '100',
  to_number()
  from dual;

nvl

null처리 함수 nvl(컬럼, null일 경우 값)

nvl2(col,val1,val2)

col의 값이 null이 아니면 val1, null이면 val2

xxxxxxxxxx
select nvl2(7,3,2),
  nvl2(null,3,2),
  nvl2('거북선','이순신','이성계'),
  nvl2('null','이순신','이성계'),
  from dual;

보너스가 있는 사원은 보너스있음, 없으면 보너스없음으로 표시하세요,

xxxxxxxxxx
select emp_name,
  bonus,
  nvl2(bonus,'보너스 있음','보너스 없음')
  from employee;

기타함수

1. greatest , least

   숫자,문자,날짜 비교

   xxxxxxxxxx
   select greatest('이재선','한동준','김민성'),
       least('이재선','한동준','김민성'),
       greatest(1,2,3),
       least(1,2,3),
       greatest(sysdate+1,sysdate,sysdate-1),
       least(sysdate+1,sysdate,sysdate-1)
     from dual;
   

2. decode

   선택함수

   switch문과 흡사함

   decode(표현식,조건1,결과1,조건2,결과2) - 표현식은 반드시 값이여야 함

   사원명과 직급코드에 따른 직급명으로 출력

   J1 대표 , J2 부사장 , J3 부장 , J4 차장, J5 과장, J6 대리 , J7 사원

   xxxxxxxxxx
   select emp_name,
     job_code,
     decode(job_code,'J1','대표',
     'J2','부사장',
     'J3','부장',
     'J4','차장',
     'J5','과장',
     'J6','대리',
     'J7','사원'),
     decode(job_code,'J1','대표',
     'J2','부사장',
     'J3','부장',
     '평사원')
     from employee;
   

3. case

   case when 조건1 then 결과1

   when 조건2 then 결과2

   ...

   else 결과

   end

    

   case 조건

   when 조건1 then 결과1

   when 조건2 then 결과2

   ...

   else 결과

   end

   xxxxxxxxxx
   select emp_name,
     case when substr(emp_no,8,1) = '1' then '남'
       when substr(emp_no,8,1) = '3' then '남'
       else '여'
       end "성별",
     case when substr(emp_no,8,1) in ('1','3') then '남'
       else '여'
       end "성별"
     from employee;
   

   xxxxxxxxxx
   select emp_name,
     case substr(emp_no,8,1)
       when '1' then '남'
       when '3' then '남'
       else '여'
       end
     from employee;
   

   사원 나이를 조회 , 사원명, 주민번호 , 성별 , 나이 (현재년도 - 출생년도 + 1)

   xxxxxxxxxx
   select emp_name as "사원명",
     emp_no as "주민번호",
     case substr(emp_no,8,1)
       when '1' then '남'
       when '3' then '남'
       else '여'
       end as "성별",
     case when substr(emp_no,8,1) in('1','2') then to_char(sysdate,'yyyy') - to_char(substr(emp_no,1,2) + 1900)+1
            else to_char(sysdate,'yyyy') - to_char(substr(emp_no,1,2)+2000)+1
           end as "나이"
     from employee;
     
   select extract(year from sysdate)
     - (to_number(substr(emp_no,1,2))+
     case when substr(emp_no,8,1) in ('1','2') then 1900
       else 2000 end) + 1 as "나이"
     from employee;
   

날짜 포맷 (rr을 사용할 시)

현재년도 입력년도 처리년도

00~49 00~49 현재세기(2000년도)

50~99 전세기(1900년도)

50~99 00~49 다음세기(2100년도)

50~99 현재세기(2000년도)

ex)

현재년도 입력년도 rr yy

1994 95 1995 1995

04 2004 1994

4자리인 경우엔 차이가 없구요. 2자리인경우엔 차이가 있습니다. yy 는 무조건 '20'이 앞에 붙습니다. rr 은 50년을 기준으로 작으면 '20' 크거나 같으면 '19'가 붙습니다. TO_DATE('00','yy') ==> 2000 TO_DATE('50','yy') ==> 2050 TO_DATE('00','rr') ==> 2000 TO_DATE('50','rr') ==> 1950

그룹함수

하나의 행이 아니라, 여러행을 그룹으로 묶어서 종합, 평균 등의 값을 리턴하는 함수

1. sum

   xxxxxxxxxx
   select sum(salary)
     from employee;
   

2. avg

   xxxxxxxxxx
   select avg(salary)
     from employee
   

3. count

   xxxxxxxxxx
   select count(*) as "총 사원수"
     from employee
   ​
   select count(*) as "보너스 없는 사원"
     from employee
     where bonus is null;
   

4. min , max

   xxxxxxxxxx
   select max(salary),
     max(salary)
     from employee;
     
   select max(hire_date),
     max(hire_date)
     from employee;
   

2번 pdf

xxxxxxxxxx
--1
select student_no as "학번",
    student_name as "아룸",
    entrance_date as "입학년도"
    from tb_student
    order by 3;
    
--2
select professor_name as "교수 이름",
    professor_ssn as "주민번호"
    from tb_professor
    where length(professor_name) != 3;
    
--3
select professor_name as "교수 이름",
    extract(year from sysdate) -
        (19 || substr(professor_ssn,1,2)) as "나이"
    from tb_professor
    order by 2;
    
--4
select substr(professor_name, 2) as "이름"
    from tb_professor;
    
--5
select student_no as "학번",
    student_name as "이름" 
    from tb_student
    where extract(year from entrance_date) - (1900 + substr(student_ssn , 1 , 2)) = 20;
    
--6
select to_char(to_date('20/12/25' , 'yy/mm/dd') , 'dy') as "크리스마스 요일"
    from dual;
​
--8
select student_no as "학번",
    student_name as "이름"
    from tb_student
    where substr(student_no ,1,1) != 'A';
    
select student_no as "학번",
    student_name as "이름"
    from tb_student
    where extract(year from entrance_date) < 2000;
​
--9
select round(avg(point),1)
    from tb_grade
    where student_no = 'A517178';
​
--10
select department_no as "학과번호",
    count(*)
    from tb_student
    group by department_no
    order by 1;
    
--11
select count(*)
    from tb_student
    where coach_professor_no is null;
select * from tb_student;
​
--12
select to_char(to_date(term_no ,'yyyymm'), 'yyyy') as "년도",
    round(avg(point),1) as "년도 별 평점"
    from tb_grade
    where student_no = 'A112113'
    group by to_char(to_date(term_no ,'yyyymm'), 'yyyy')
    order by 1;
​
--13
select department_no,
    sum(decode(absence_yn,'Y',1,0))
    from tb_student
    group by department_no
    order by 1;
​

group by

세부적인 그룹을 지정해서 그룹함수를 사용할 수 있다.

부서별 salary의 합계

xxxxxxxxxx
select dept_code,
  sum(salary)
  from employee
  group by dept_code;
  
select * 
  from employee
  where dept_code is null;  --null도 하나의 그룹으로 친다
​
--직급별 사원수를 조회하고, 직급순으로 정렬하라
select job_code, count(*) 사원수
    from employee
    group by job_code
    order by 1;
​
-- 부서코드별 급여의 합계, 급여의 평균(정수처리),인원수 조회
select dept_code,
    sum(salary),
    trunc(avg(salary))
    from employee
    group by dept_code;
​
--부서코드별로 보너스를 지급받는 사원 수를 조회
select dept_code,
    count(*)
    from employee
    where bonus is not null
    group by dept_code
    order by 1;
    
select dept_code,
    count(bonus) -- 컬럼의 값이 null인 행을 제외하고 연산
    from employee
    group by dept_code
    order by 1;
    
-- 두개이상의 컬럼을 대상으로 할 수 있다
select dept_code , job_code, count(*)
  from employee
  group by job_code, dept_code
  order by 1,2;
​
-- employee테이블에서 직급코드가 J1인 사원을 제외하고, 직급별 사원수, 평균 급여를 조회
select job_code,
  count(*),
  trunc(avg(salary))
  from employee
    where job_code != 'J7'
  group by job_code;
  
-- employee테이블에서 직급코드가 J1인 사원을 제외하고,
-- 인사년도별 인원수를 조회해서, 입사년 기준으로 정렬할 것
select extract(year from hire_date),
    count(*)
    from employee
    where job_code != 'J1'
    group by extract(year from hire_date)
    order by 1 ;
​
-- group by 안에 있는 것은 컬럼안에 존재 해야함
-- employee테이블에서 성별 인원수와 급여의 평균을 출력할 것
select decode(substr(emp_no,8,1),'1' , '남' , '3', '남' ,'여'),
    count(*),
    trunc(avg(salary))
    from employee
    group by decode(substr(emp_no,8,1),'1' , '남' , '3', '남' ,'여');
​
-- 부서내 성별 인원수를 구하세요
select dept_code,
    decode(substr(emp_no,8,1),'1' , '남' , '3', '남' ,'여'),
    count(*)
    from employee
    group by dept_code , decode(substr(emp_no,8,1),'1' , '남' , '3', '남' ,'여')
    order by 1;

Having

그룹함수 값을 구해야하는 그룹에 조건은 Having절에 기술함

그룹함수를 사용한 where은 쓸수 없다

xxxxxxxxxx
-- 부서별 급여평균이 300만원 이상인 부서들만 부서명, 급여평균을 출력
​
select dept_code as "부서명",
  trunc(avg(salary)) || '원' as "급여평균"
  from employee
  having trunc(avg(salary)) >= 3000000
  group by dept_code
    order by 1;
​
-- 부서별인원이 5명 이상인 부서만 출력
select dept_code,
    count(*)
    from employee
    having count(*) >= 5
    group by dept_code;
    
-- 매니저가 관리하는 사원이 2명 이상인 매니저 아이디와 관리하는 사원 수를 출력
select manager_id as "매니저 아이디",
     count(*) as "관리하는 사원의 수"
     from employee
     group by manager_id
     having count(*) >= 2 and manager_id is not null
     order by 1;

소계, 누계를 처리하기 : rollup, cube

rollup : 가장 먼저 지정한 그룹별 합계와 총 합계를 출력

cube : 그룹으로 지정된 모든 그룹에 대한 합계와 총 합계를 출력

그룹지정한 컬럼이 하나인 경우 , rollup 과 cube의 결과는 같다

xxxxxxxxxx
select dept_code,
  count(*)
  from employee
  group by rollup(dept_code)
  order by 1;
  
select dept_code,
  count(*)
  from employee
  group by cube(dept_code)
  order by 1;

grouping함수

group by절에 의해서 산출된 컬럼은 0을 리턴

rollup, cube절에 의해서 산출된 컬럼은 1을 리턴

xxxxxxxxxx
select dept_code,
    grouping(dept_code),
  count(*)
  from employee
  group by cube(dept_code)
  order by 1;
  
select dept_code,
    decode(grouping(dept_code),0,nvl(dept_code,'인턴'),1,'총계') as "부서",
  count(*)
  from employee
  group by cube(dept_code)
  order by 1;
  
--부서별 급여정도 출력(null처리 할것)
select case grouping(dept_code)
  when 0 then nvl(dept_code, '인턴')
  else '총계' end 부서코드,
  sum(salary)
  from employee
  group by rollup(dept_code)
  order by 1;
  
--두개이상의 컬럼으로 그룹핑하고, rollup/cube를 사용하면
--rollup
--부서내 직급별 인원정보
select dept_code,
    job_code,
    decode(grouping(dept_code), 0 , nvl(dept_code,'인턴') , '총계'),
    decode(grouping(job_code),0,job_code,'소계') as "직급",
    count(*)
    from employee
    group by rollup(dept_code,job_code)  -- 롤업은 첫번째 컬럼에서 두번째 컬럼 순으로 진행
    order by 1,2;
    
    
select dept_code,
    job_code,
    decode(grouping(dept_code), 0 , nvl(dept_code,'인턴') , '총계'),
    decode(grouping(job_code),0,job_code,'소계') as "직급",
    count(*)
    from employee
    group by cube(dept_code,job_code)  -- cube은 첫번째 컬럼에서 두번째 컬럼 순으로 진행 , 그리고 반대순인 두번째컬럼에서 첫번쨰 컬럼순으로도 한번 더 진행
    order by 1,2;

Join

Join : 가로합침, 컬럼과 컬럼을 합치는 것

union : 세로합침, 행과 행을 합치는 것

여러테이블의 레코드를 조합하여 하나의 행으로 표현한 가상 테이블을 생성

연관성이 있는 컬럼을 기준으로 조합

xxxxxxxxxx
select *
    from employee
    where emp_name = '송종기';
    
select *
    from department;
​
-- 조인으로 합체 (equal-join)
select *
    from employee join department
    on employee.dept_code = department.dept_id
    where emp_name = '송종기';

join 두가지 종류

1. equal-join : 동등조건으로 조인하는 경우 , = 을 이용
2. non equal-join : 동등조건이 아닌 between and , is null , is not nul , in등을 이용한 조인

 

join 두가지 문법

1. ANSI 표준문법 : DBMS와 상관없이 공통적으로 사용하는 표준 SQL
2. Oracle 표준문법 (벤더사 제공하는 문법) : Oracle에서 사용하는 구문 , 콤마(오라콤마)

equl - Join

1. 조인하는 컬럼명이 다른 경우

xxxxxxxxxx
-- 부서명과 지역명을 출력하라
select distinct *
  from location join department
  on location.local_code = department.location_id;

2. 조인하는 컬럼명이 같을 경우

xxxxxxxxxx
-- employee , job 
select * from employee;
select * from job;
​
select *
  from employee join job
  on employee.job_code = job.job_code;
  
-- 별칭을 줄수도 있음
select *
  from employee E join job J
  on E.job_code = J.job_code; 
  
select emp_name,
  E.job_code,
  job_name
  from employee E join job J
  on E.job_code = J.job_code;
​
-- 컬럼명이 같을경우에 한해서 on 이 아닌 using 키워드를 사용할 수 있음
-- 테이블명이나 별칭을 사용하면 ERROR
select emp_name,
  job_code,
  job_name
  from employee join job
  using(job_code)
  
-- 지역명과 국가명을 출력(location, nation)
select * from location;
select * from nation;
​
select national_name,
    local_name
    from nation join location
    using(national_code);

eqal-join 종류

1. inner join : 보통 교집합 생각하면됨

   기본 조인 : (inner) join

2. outer join : 합집합

   left (outer) join : 왼쪽 외부 조인

   right (outer) join : 오른쪽 외부 조인

   full (outer) join : 완전 외부 조인

3. cross join
4. self join
5. multiple join

inner join

교집합, 기존되는 컬럼에서 null이나 해당되는 값이 없다면 제외

xxxxxxxxxx
-- employee , department
select * from employee; -- 23개 row
select * from department; -- 9개 row
​
select *    -- 21개 row
    from employee inner join department
    on employee.dept_code = department.dept_id;

outer join

left outer join : 좌측 table 모두 반환, 우측 테이블 조건 컬럼에 일치하는 row만 반환

xxxxxxxxxx
select *
  from employee left outer join department
  on employee.dept_code = department.dept_id;

right outer join : 우측 table 모두 반환, 좌측 테이블 조건 컬럼에 일치하는 row만 반환

xxxxxxxxxx
select *
  from employee right outer join department
  on employee.dept_code = department.dept_id;

full outer join : 조건 컬럼에 일치하지않아도 , 우측 table , 좌측 table 모두 row 반환

xxxxxxxxxx
select *
  from employee full outer join department
  on employee.dept_code = department.dept_id;

조인 결과 : employee(23row) , department(9row)

• inner join : 21
• left outer join : 23 = 21 + 2
• right outer join : 24 = 21 + 3
• full outer join : 26 = 21 + 2 + 3

3번 pdf

xxxxxxxxxx
--1
select student_name as "학생 이름",
    student_address as "주소지"
    from tb_student
    order by 1;
    
--2
select student_name as "학생 이름",
    student_ssn as "주민 번호"
    from tb_student
    where absence_yn = 'Y'
    order by 2 desc;
​
--3
select student_name as "학생 이름",
    student_no as "학번",
    student_address as "거주지 주소"
    from tb_student
    where (student_address like '%경기도%' or student_address like '%강원도%')
    and student_no not like 'A%'
    order by 2;
​
--4
select tb_professor.professor_name as "교수 이름",
    tb_professor.professor_ssn as "주민 번호"
    from tb_professor join tb_department
    using(department_no)
    where tb_department.department_name = '법학과'
    order by 2;
​
--5
select student_no as "학생 학번",
    point as "학점"
    from tb_grade
    where class_no = 'C3118100' and term_no = '200402'
    order by 2 desc , 1;
​
--6
select student_no as "학생 번호",
    student_name as "학생 이름",
    tb_department.department_name as "학과 이름"
    from tb_student join tb_department
    using(department_no)
    order by 2;
    
--7
select class_name as "과목 이름",
    department_name as "학과 이름"
    from tb_class join tb_department
    using(department_no);
    
--8
select class_name as "과목 이름",
    tb_professor.professor_name as "교수 이름"
    from tb_class join tb_professor
    using(department_no)
    order by 1;
​
--9
​
​
​
select * from tb_class;
select * from tb_class_professor;
select * from tb_department;
select * from tb_grade;
select * from tb_professor;
select * from tb_student;

cross join

좌측 테이블 * 오른쪽 테이블

모든 경우의 수를 구하고, 행의 수는 (좌측테이블 행 개수 * 우측테이블 행 개수)

cartesian product : 카테시안의 곱

xxxxxxxxxx
select *
  from employee cross join department; -- 207
  
select count(*) from employee; --23
select count(*) from department; --9
​
-- 평균월급과 평균월급과의 차이
select trunc(avg(salary)) -- trunc(avg(salary))를 하나의 테이블로 인식
  from employee;
​
select emp_id,
    emp_name,
    salary,
    평균,
    salary - 평균 as "월급차이"
    from employee cross join(select trunc(avg(salary)) as "평균"
                            from employee);

self join

같은 테이블을 조인해서 결과셋을 얻고자 할 때 사용

xxxxxxxxxx
--사원명과 해당사원의 매니저명을 조회
select E.emp_id,
    E.emp_name,
    E.manager_id,
    M.emp_name
  from employee E join employee M
  on E.manager_id = M.emp_id; --15

multiple join(다중 조인)

xxxxxxxxxx
--사원명, 부서명, 지역명을 출력
select emp_name, dept_code from employee;
select dept_id,dept_title, location_id from department;
select local_name,local_name from location;
​
select emp_name,
    dept_code,
    local_name 
    from employee E
        join department D on E.dept_code = D.dept_id
        join location L on D.location_id = L.local_code;
        
--직급이 대리이면서, asia지역에 근무하는 직원을 조회
--사번,이름,직급명,부서명,근무지역명,급여
select emp_id as "사번",
    emp_name as "이름",
    J.job_name as "직급명",
    d.dept_title as "부서명",
    L.local_name as "근무지역명",
    salary as "급여"
    from employee E
        join JOB J on E.job_code = J.job_code
        join department D on E.dept_code = d.dept_id
        join location L on D.location_id = L.local_code
    where J.job_name = '대리' and L.local_name like 'ASIA%';

Oracle 전용 문법

join키워드 대신 ,(콤마)를 사용

on절 대신 where절을 사용

xxxxxxxxxx
-- 조인하는 컬럼명이 다를 경우
select dept_title, local_name
  from department D, location L
  where D.location_id = L.local_code;
  
-- 조인하는 컬럼명이 같을 경우 (Oracle 전용에는 Using이 없음)
-- employee, job;
select * from employee;
select * from job;
​

outer join

where절에 (+)이 없는 쪽을 우선 다출력

left : (+) 우항에 작성

right : (+) 좌항에 작성

xxxxxxxxxx
select *
  from employee E, department D
  where E.dept_code = D.dept_id(+); --employee E기준으로 join을 하고 싶으면 D.dept_id에 +표시

self join

xxxxxxxxxx
select E.emp_id,
  E.emp_name,
  E.manager_id,
  M.emp_name
  from employee E, employee M
  where E.manager_id = M.emp_id

multiple join (oracle전용구문의 다중조인에서는 테이블 순서가 중요하지 않다.)

xxxxxxxxxx
select emp_name,
  dept_title,
  local_name
  from employee E, department D, location L
  where E.dept_code = D.dept_id and D.location_id = L.local_code;

non - equal join

동등조건이 아닌 기준으로 조인

xxxxxxxxxx
select * from employee;
select * from sal_grade;
​
select emp_name,
  E.salary,
  S.sal_level
  from employee E join sal_grade S
    on E.salary between S.min_sal and S.max_sal;

집합연산자(Set Operator)

여러개의 질의결과를 가로로 연결하여 합치거나 뺀 후, 하나로 결합하는 방식

조건1. slect절의 컬럼수가 동일해야됨

조건2. select절의 동일위치계 존재하는 컬럼의 데이터타입이 상호 호환 가능해야함

1. union 합집합
2. union all 합집합
3. intersect 교집합
4. minus 차집합

A = {1,2,3,4,5};

B = {3,5,7,9};

union --- A U B = {1,2,3,4,5,7,9}; ==> 중복 제거 O , 첫번째 컬럼기준으로 오름차순

union all --- A U B = {1,2,3,4,5,3,5,7,9}; == > 중복 제거 X , 그냥 연결

intersect --- A n B = {3,5};

minus --- A - B = {1,2,4};

xxxxxxxxxx
​
--union
-- 가로합침, 중복데이터제거, 첫번째 컬럼 기준정렬(오름차순)
-- 부서코드가 D5인 사원의 사원번호, 사원명, 부서코드, 급여를 출력
​
select emp_id,
  emp_name,
  dept_code,
  salary
    from employee
    where dept_code = 'D5' --6
    
-- 급여가 300만원보다 많은 사원의 사원번호, 사원명, 부서코드, 급여를 출력
select emp_id,
  emp_name,
  dept_code,
  salary
  from employee
  where salary > 3000000; --9
  
-- 위에 두개를 union
select emp_id,
  emp_name,
  dept_code,
  salary
    from employee
    where dept_code = 'D5' --6
union
select emp_id,
  emp_name,
  dept_code,
  salary
  from employee
  where salary > 3000000; --9
  
-- 컬럼 타입과 위치가 모두 동일 해야함
select 123, 'zzz' from dual
union
select 'abc' 456 from dual  (XXXX)
​
select 123, 'zzz' from dual
union
select 456, 'abc' from dual
union
select 345, 'def' from dual;

grouping sets

group by + union all 을 한번에 처리

xxxxxxxxxx
--부서코드별 인원수, 직급코드별로 인원수를 동시에 출력
select dept_code,
  job_code,
  count(*)
  from employee
  group by grouping sets(dept_code,job_code);
    
select dept_code,
    null job_code,
    count(*)
    from employee
    group by dept_code
union all
select null dept_code,
    job_code,
    count(*)
    from employee
    group by job_code;
    
-- 성별, 부서별, 직급별 급여평균을 구하시오
select decode(substr(8,1,1),'1','남','3','남','여'),
    dept_code,
    job_code,
    trunc(avg(salary))
  from employee
  group by grouping sets(
    decode(substr(8,1,1),'1','남','3','남','여'),
    dept_code,
    job_code
  )

SubQuery

하나의 SQL문안에 포함된 또 다른 SQL문

메인쿼리에 종속된 서브쿼리가 존재함

존재하지 않는 조건에 근거한 값들을 검색 할 때

 

서브쿼리는 반드시 소괄호로 묶을 것

서브쿼리는 연산자의 오른쪽 위치 할 것.

xxxxxxxxxx
--노옹철사원의 관리자이름을 출력하라.
-- 셀프조인
select E.emp_name
  from employee E left join employee M
  on E.manager_id = m.emp_id
  where e.emp_name = '노옹철';
  
-- 서브쿼리
select emp_name
    from employee
    where emp_id = (select manager_id
                        from employee
                        where emp_name ='노옹철');

xxxxxxxxxx
--전직원의 급여 평균보다 많은 급여를 받고 사원의 사번,이름,직급코드,급여를 출력
--1. 급여 평균
--2. 급여평균보다 많은 급열 받는 사원 필터링
select emp_id as "사번",
    emp_name as "이름",
    sal_level as "직급코드",
    salary as "월급"
    from employee
    where salary >= (select avg(salary)
                            from employee);
​

서브쿼리의 유형

1. 단일행 서브쿼리(단일컬럼)
2. 다중행 서브쿼리(단일컬럼)
3. 다중열 서브쿼리(단일행)
4. 다중행 다중열 서브쿼리
5. 상관 서브쿼리
6. 스칼라 서브쿼리
7. 인라인 뷰

1.단일행 서브쿼리

xxxxxxxxxx
-- 1.윤은혜사원과 급여가 같은 사원의 사번,사원명,급여 출력
select emp_id 사번,
    emp_name 사원명,
    salary 급여
    from employee
    where emp_name != '윤은해' 
    and salary = (select salary
                     from employee
                     where emp_name = '윤은해');
                     
-- 2.기본급여가 가장 많은 사람과 제일 적은 사람을 출력
select *
    from employee
    where salary = (select max(salary)
                            from employee)
    or salary = (select min(salary)
                        from employee);
                    
-- 3. D1, D2부서에 근무하는 사원중에 급여가 D5부서의 평균급여보다 많은 사원을 조회
select emp_id 사번,
    emp_name 사원명,
    dept_code 부서코드,
    salary 급여
    from employee
    where dept_code in ('D1','D2')
    and salary > (select avg(salary)
                        from employee
                        where dept_code = 'D5');

2. 다중행 서브쿼리

IN, NOT IN, ANY , ALL , EXISTS

xxxxxxxxxx
--in
--송종기, 하이유가 속한 부서의 사원정보 출력
select dept_code
    from employee
    where emp_name in ('송종기','하이유');
    
select emp_name, dept_code
    from employee
    where dept_code in('D5','D9');
    
-- 서브쿼리로 합쳐 보리기
select emp_name,dept_code
    from employee
    where dept_code in (
        select dept_code
             from employee
             where emp_name in ('송종기','하이유')
    );
    
-- not in
-- 송종기와 하이유가 근무하지 않는 부서를 다니는 닝겐들
select emp_name , dept_code
    from employee
    where dept_code not in (select dept_code
                                from employee
                                where emp_name in('송중기','하이유'));
                                
--any(some)
--서브쿼리의 결과 중에서 하나라도 참이면 참
/*
x > any(..) : any절의 어떤 값보다도 크기만 하면 참, 최소값보다 크기만 하면 참
x < any(..) : any절의 어떤 값보다도 작기만 하면 참, 최대값보다 작기만 하면 참
x = any(..) : any절의 어떤 값과 일치하기만 하면 참, in 과 동일
x != any(..) : any절의 하나 이상의 값과 일치하지 않으면 참
*/
​
select emp_name, salary
    from employee
    where salary > any(2000000,5000000);
    
-- all
-- 서브쿼르이 결과 중에서 모두 참이면 참.
/*
x> all(..) 모든 값보다 크면 참, 최대값 보다 크면 참
x > all(..) 모든 값보다 작으면 참, 최소값 보다 작으면 참
x = all(..) 모든 값과 같으면 참, 보통 false처리
x != all(..) 모든 값과 다르면 참, not in하고 동일함
*/
select emp_name , salary
    from employee
    where salary > all(2000000,5000000,3000000); -- 50000000이랑만 비교하게 됨
    
select emp_name , salary
    from employee
    where salary < all(2000000,5000000,3000000); -- 20000000이랑만 비교하게 됨
    
-- D2부서의 모든 사원보다 적은 급여를 받는 사원 조회
select *
    from employee
    where salary < all(select salary 
                            from employee
                            where dept_code ='D2');
                            
--부서별 평균급여중 가장 높은 부서의 부서코드, 평균급여를 조회
--all
select dept_code,
    trunc(avg(salary))
    from employee
    group by dept_code
    having avg(salary) >= all(select trunc(avg(salary))
                                    from employee
                                    group by dept_code); --최대값과 비교해서 같거나 크면 되는데, 최대값과 똑같은 결과를 원할 떄 씀
                                      
select trunc(avg(salary))
    from employee
    group by dept_code;
    
--exist
--서브쿼리의 결과 중에서 만족하는 행이 하나라도 존재하면 참
select *
  from employee
  where 1=1; --무적권 참
  
select *
  from employee
  where 1=2; -- 절대 거짓
  
--행을 검사 할 때 서브쿼리의 결과셋에 행이 존재하면 참 => 메인쿼리의 해당행을 resultset에 포함시킴
​
--메인쿼리행을 검사할때 서브쿼리의 결과셋에 행이 존재하지 않으면 거짓
-- 메인쿼리의 해당행을 resulltset에 포함시키지 않음
​
select *
  from employee
  where exists(select emp_name from employee where dept_code='D5');
  
--부하직원이 한명이라도 있는 직원을 출력
select emp_id, emp_name
  from employee E
  where exists(select emp_name
          from employee
          where manager_id = E.emp_id
  );
  
--not exists를 이용해서 최대/최소값 구하기
--가장 많은 급여를 받는 사원을 조회
--행이 존재하면 거짓, 행이 존재하지 않으면 참
select emp_id, salary
  from employee E
  where not exists(
            select 1
              from employee
              where salary > E.salary --이 부분에서는 계속 돈다. 메인쿼리 salary받아와서 한바퀴 쭈욱 받고
  );
  
select emp_id, salary
  from employee E
  where not exists(
            select 1
              from employee
              where salary < E.salary
  );