[수업 22일차] 21. 09.03 SQL Data Base 2

게시판에 글을 작성을 하면 캐쉬를 주게 되면 오라클에서 캐쉬 메모리를 20개를 주게된다. 

캐쉬에다가 주면 더 빨리 부여를 하게 된다. 1~20까지 글을 작성하게 되면 20개의 글을 처리를 하게 된다. 

시퀀스를 게시글을 10개 작성후 컴퓨터를 그면 20이라는 숫자가 사라져버림

그 다음날 캐쉬 메모리를 20개를 또 받아옴 

캐쉬를 다 쓰지 않는 상태에서 컴퓨터가 꺼지면 문제가 일어나게 된다.

==> 글 번호가 제대로 부여가 되지않기 때문에 캐쉬를 사용 안하는게 좋다. 

---

--like 키워드 : 검색을 하는 키워드

 

--where ename like '%S%'
--  ==> ename 컬럼에 S자를 포함하는 사원의 이름을 검색.

-- where ename like 'S%'
-- ==> ename 컬럼의 첫글자가 S자로 시작하는 사원의 이름을 검색

-- where ename like '%S'
-- ==> ename 컬럼의 마지막 글자가 S자로 끝나는 사원의 이름을 검색

-- where ename like '_S%'
-- ==> ename 컬럼의 두번째 글자가 S자를 포함한 사원의 이름을 검색

 

 

---

* order by 절

자료를 정렬하여 나타나고자 할 때 사용하는 구문

order by 절을 사용할 때는 select 구문의 맨마지막에 위치해야함

 

asc : 오름차순 정렬
desc :내림차순 정렬


기본적으로 order by 절을 사용시에는 오름차순이 default임

오름차순으로 정렬할 경우에는 asc는 생략도 가능함.
null 값은 오름차순에서는 제일 나중에 나오고, 내림차순에서는 제일 먼저 나온다.

 

---

 not 키워드 : 부정의 의미를 지닌 키워드.

 

 일단은 쿼리문을 작성 시 부정이 아닌 긍정의 쿼리문을 작성을 한 후에
 
 부정의 의미인 not을 붙여주면 된다. 

---

그룹함수 

여러 행 또는 테이블 전체에 대하여 함수가 적용되어

하나의 결과값을 가져오는 함수.

1) avg() : 평균값을 구해 주는 함수.

2) count() : 행의 갯수를 구해 주는 함수.
             null 값은 무시하고 행의 갯수를 구해줌.
             
3) max() : 최대값을 구해 주는 함수.

4) min() : 최솟값을 구해 주는 함수.

5) sum() : 총합을 구해 주는 함수.

 

---

시퀀스(sequence)

순번을 부여할 때 사용하는 문법.

형식) create sequence 시퀀스이름

start with 1

increment by 1;

---

join ~ on 키워드

테이블과 테이블을 연결하여 특정한 데이터를 얻고자 할 때 사용하는 키워드

두개 이상의 테이블에 정보가 나뉘어져 있을 때 사용함.

중복해서 데이터가 저장되는 것을 막기 위해 테이블을 나누어서 쓰게 됨.

emp 테이블에서 부서의 상세정보까지 저장을 한다면 10번 부서에 소속된

사원이 3명이므로 근무지가 3번 중복이 되어 나타남.

이렇게 중복되어 저장된 데이터는 추후 삽입, 수정, 삭제 시 이상 현상이

발생할 수 있게 됨.

즉, 이러한 현상이 발생하지 않게 하려면, 즉, 데이터가 중복이 되어 저장되지 

않게 하려면 데이터베이스에서 두 개 이상의 테이블에 정보를 나누어 저장을 헤

두어야 함. 하지만 이렇게 두 개의 테이블로 나누어지게 되면 데이터 중복은 

피할 수 있지만 원하는 정보를 얻으려면 여러 번 질의를 해야하는 불편함이 발생함.

그래서 두 개의 테이블을 결합해서 원하는 정보를 얻어낼 수 있도록 하는 조인이라는 

기능을 제공함

 

 

조인의 종류

1) Cross Join

2) Equi Join

3) Self Join

4) Outer Join

-- 1) Cross Join

-- 두 개 이상의 테이블이 조인될 때 조건이 없이 테이블의 결합이 이루어지는 조인.

-- 그렇기 때문에 테이블 전체 행의 컬럼이 조인이 됨. 

-- emp 테이블 사원수(14) * dept 테이블 부서 수(4) ==> 56개의 정보가 나타남.


select *
from emp, dept;

-- 2) Equi Join

-- 가장 많이 사용되는 조인 방법.

-- 조인의 대상이 되는 두 테이블에서 공통적으로 존재하는 컬럼의 값이 

-- 일치되는 행을 연결하여 결과를 생성하는 방법.

-- 두 테이블이 조인하려면 일치되는 공통 컬럼을 사용하면 됨.

-- 컬럼의 이름이 같으면 혼동이 오기 때문에 컬럼 이름 앞에 테이블의

-- 이름을 기술해야 함.

-- emp 테이블에서 사원의 사번, 이름, 담당업무, 부서번호, 부서명, 근무위치를 
-- 화면에 보여주세요  ==> emp 테이블과 dept 테이블을 조인해 주어야 함.

 

-- 3) Self Join

-- 하나의 테이블 내에서 조인을 해야 자료를 얻을 수 있는 조인 방법

-- 말 그대로 자기 자신 테이블과 조인을 맺는 것을 말함.

-- from 절 다음에 테이블 이름을 나란히 두 번 기술을 할 수 없음.

-- 따라서 같은 테이블이 하나 더 존재하는 것처럼 사용할 수 있도록 

-- 테이블에 별칭을 붙여서 사용해야 함.

 

-- 4) Outer Join

-- 2개 이상의 테이블이 조인이 될 때 어느 한 쪽 테이블에는 해당하는 데이터가

-- 다른 쪽 테이블에는 데이터가 존재하지 않는 경우 그 데이터가 출력이 되지

-- 않는 문제점을 해결하기 위해 사용되는 조인 기법.

-- 정보가 부족한 테이블의 컬럼 뒤에 '(+)' 기호를 붙여서 사용을 하면 됨.

 

---

dual 테이블

- 오라클에서 자체적으로 제공해 주는 테이블.

- 간단하게 함수를 이용해서 계산, 결과값을 확인할 때 사용하는 테이블.

- 오직 한 행, 한 컬럼만을 담고 있는 테이블

- 사용 용도 : 특정 테이블을 생성할 필요 없이 함수 또는 계산을 하고자 할 때 사용됨.



-- 오라클에서 제공해 주는 함수들

-- 1. 날짜와 관련된 함수들

-- 1) 현재 시스템의 날짜를 구해 오는 함수 : sysdate

 

-- 2) 몇 개월 이후의 날짜를 구하는 함수 : add_ months()
--    형식) add_months(현재 날짜, 숫자(개월 수))
select add_months(sysdate,4) from dual;

-- 3) 다가올 날짜(요일)을 구해 주는 함수 : next_day()
--    형식) next_day(현재 날짜, '요일')
select next_day(sysdate, '수요일') from dual;

-- 4) 달의 마지막 날을 구해 주는 함수 : last_day()
select last_day(sysdate) from dual;

-- 5) 두 날짜 사이의 개월 수 차이를 구하는 함수 : months_between()
select empno, ename, hiredate, sysdate,
    months_between(sysdate, hiredate) "개월 수 차"
    from emp;

-- 6) 형식에 맞게 문자열로 날짜를 출력하는 함수 : to_char()
--   형식) to_char(날짜,'날짜형식')
select to_char(sysdate, 'yyyy/MM/dd') from dual;
select to_char(sysdate, 'MM/dd/yyyy') from dual;
select to_char(sysdate, 'yyyy-MM-dd') from dual;

 

---

-- 1. 문자와 관련된 함수들.

-- 1) 대문자, 소문자를 바꾸어 주는 함수 : upper(), lower(), initcap()  
-- upper() : 소문자를 대문자로 바꾸어 주는 함수
-- lower() : 대문자를 소문자로 바꾸어 주는 함수.
-- initcap(): 첫 글자는 대문자로, 나머지 문자는 소문자로 바꾸어 주는 함수.
select ename, upper(ename),lower(ename), initcap(ename)
from emp;

-- 2) 문자열의 길이를 구해 주는 함수 : length()
select ename, length(ename)
from emp;

select ename, length(ename)
from emp
where length(ename) >=5;

select length('한글'), lengthb('한글')
from dual;

-- 3) 문자열 일부를 추출하는 함수 : substr()
--    형식) substr("문자열",시작위치,추출할 문자 길이)
-- 시작위치가 음수인 경우에는 오른쪽부터 시작이 된다. 
-- 형식) substr("문자열", 시작위치)


select substr('ABCDEFG',3,2) from dual;     --CD

select substr('ABCDEFG',-3,2) from dual;     --EF

select substr('ABCDEFG', 2) from dual;       -- BCDEFG

-- 4) 문자열을 연결하는 함수 : concat()
--    형식) concat("문자열1","문자열2");
select concat('안녕','하세요') from dual;

-- 문자열을 연결하는 연산자 : ||
select '방가' ||'방가' from dual;

-- 5) 자릿수를 늘려주는 함수
--    왼쪽 자릿수를 늘려주는 함수 : lpad()
--    형식) lpad('문자열', 전체자릿수, '늘어난 자릿수에 들어갈 문자열')
select lpad('ABCDEFG', 15, '*') from dual;


--    오른쪽 자릿수를 늘려주는 함수 : rpad()
--    형식) rpad('문자열', 전체자릿수, '늘어난 자릿수에 들어갈 문자열')
select rpad('ABCDEFG', 15,'*') from dual;

--6) 문자를 지워주는 함수 
--   왼쪽 문자를 지워주는 함수 : ltrim()
select ltrim('ABCDEFG','A') from dual;
--   오른쪽 문자를 지워주는 함수 :rtrim()
select rtrim('ABCDEFG','FG') from dual;

--7) 문자열을 교체해 주는 함수 : replace()
--   형식) replace('원본 문자열', '교체될 문자열', '새로운 문자열');
select replace('Java Program','Java','JSP') from dual;

 

--like 키워드 : 검색을 하는 키워드

--where ename like '%S%'
--  ==> ename 컬럼에 S자를 포함하는 사원의 이름을 검색.

-- where ename like 'S%'
-- ==> ename 컬럼의 첫글자가 S자로 시작하는 사원의 이름을 검색

-- where ename like '%S'
-- ==> ename 컬럼의 마지막 글자가 S자로 끝나는 사원의 이름을 검색

-- where ename like '_S%'
-- ==> ename 컬럼의 두번째 글자가 S자를 포함한 사원의 이름을 검색

select *
from emp
where ename like '%S%';   --(S자를 다 포함하는)

select *
from emp
where ename like '%M%';

--[문제1] emp 테이블에서 이름이 'S'자로 끝나는
--       사원의 이름과 담당업무를 화면에 보여주세요.

select ename, job
from emp
where ename like '%S';



--[문제2] emp 테이블에서 이름의 세번째에 'R'이 들어가는 
-- 사원의 이름과 담당업무, 급여를 화면에 보여주세요.
select ename, job, sal
from emp
where ename like '__R%';



--[문제3] emp 테이블에서 이름의 두번째에 'O'자가 들어가는 
-- 사원의 모든 정보를 화면에 보여주세요.
select *
from emp
where ename like '_O%';


--[문제4] emp 테이블에서 입사년도가 82년도인 사원의
-- 사번, 이름, 입사일자를 화면에 보여주세요.
select empno, ename, hiredate
from emp
where hiredate like '%82%';



--[문제5] emp 테이블에서 사원의 이름에 두번째에 'L'자가 들어가는
-- 사원의 모든 정보를 보여주세요.
select *
from emp
where ename like'_L%';


--[문제6] emp 테이블에서 사원의 이름에 'AM'이 포함되어 있는
-- 사원의 사번, 이름, 담당업무, 부서번호를 화면에 보여주세요.
select empno, ename, job, deptno
from emp
where ename like '%AM%';


--[문제7] member10 테이블에서 성이 '김'씨인 회원의
-- 모든 정보를 화면에 보여주세요.
select *
from member10
where memname like '김%';


--[문제8] member10 테이블에서 주소에 '서울시'가 포함되어 있는
-- 회원의 이름과, 주소, 직업을 화면에 보여주세요.
select memname, addr, job
from member10
where addr like '%서울시%';

/* order by 절

자료를 정렬하여 나타나고자 할 때 사용하는 구문

order by 절을 사용할 때는 select 구문의 맨마지막에 위치해야함

asc : 오름차순 정렬
desc :내림차순 정렬


기본적으로 order by 절을 사용시에는 오름차순이 default임

오름차순으로 정렬할 경우에는 asc는 생략도 가능함.
null 값은 오름차순에서는 제일 나중에 나오고, 내림차순에서는 제일 먼저 나온다.
*/

--emp 테이블에서 사번을 기준으로 오름차순으로 정렬하여 화면에 보여주세요.
select * 
from emp
order by empno asc;

-- emp 테이블에서 입사일을 기준으로 오름차순 정령하여 화면에 보여주세요.
select *
from emp
order by hiredate asc;


-- member10 테이블에서 이름을 기준으로 오름차순 정렬하여 화면에 보여주세요.
-- 단, 이름이 같은 경우에는 나이를 기준으로 내림차순으로 정렬.

select *
from member10
order by memname asc, age desc;

-- [문제1] emp 테이블에서 부서번호를 기준으로 오름차순으로 정렬을 하고, 
-- 부서번호가 같은 경우 급여를 기준으로 내림차순 정렬을 하여
-- 모든 정보를 화면에 보여주세요.
select *
from emp
order by deptno asc, sal desc;

-- [문제2] products 테이블에서 판매가격을 기준으로 내림차순 정렬하여
-- 모든 정보를 화면에 보여주세요
select *
from products
order by output_price desc;


--[문제3] products 테이블에서 배송비를 기준으로 내림차순으로 정렬하세요.
-- 단, 배송비가 같은 경우 마일리지를 기준으로 내림차순 정렬하여 
-- 모든 정보를 화면에 보여주세요.
select *
from products
order by trans_cost desc, mileage desc;



--[문제4] emp 테이블에서 입사일자가 오래된 사원부터 최근에 입사한
-- 사원을 기준으로 정렬하되, 사원명, 입사일자를 화면에 보여주세요.

select ename, hiredate
from emp
order by hiredate ;


-- [문제5] emp 테이블에서 급여를 기준으로 내림차순으로 정렬하여 모든
-- 정보를 화면에 보여주세요.

select *
from emp
order by sal desc;


-- [문제6] emp 테이블에서 급여가 1100이상인 사원의 모든 정보를 보여주되, 
-- 입사일자가 빠른 순으로 정렬하여 모든 화면에 보여주세요.
select *
from emp
where sal >=1100
order by hiredate ;



-- [문제7] emp 테이블에서 부서번호를 기준으로 오름차순 정렬하여 나타내고,
-- 부서번호가 같은 경우 담당업무를 오름차순으로 정렬하세요. 만약
-- 담당업무가 같다고 한다면, 급여가 많은데서 적은 순으로 정렬하여
-- 모든 정보를 화면에 보여주세요.
select *
from emp
order by deptno , job , sal desc;


/*
 not 키워드 : 부정의 의미를 지닌 키워드.
 
 일단은 쿼리문을 작성 시 부정이 아닌 긍정의 쿼리문을 작성을 한 후에
 
 부정의 의미인 not을 붙여주면 된다. 

*/

-- emp 테이블에서 담당업무가 'MENAGER', 'CLERK', 'ANALYST'가
-- 아닌 사원의 사번, 이름, 담당업무, 급여를 화면에 보여주세요.

select empno, ename, job, sal
from emp
where job not in ('MANAGER', 'CLERK', 'ANALYST');


--[문제1] emp 테이블에서 이름에 'S'자가 들어가지 않는 사원의 
-- 모든 정보를 화면에 보여주세요.
select *
from emp
where ename not like ('%S%');


--[문제2] emp 테이블에서 부서번호가 10번 부서가 아닌 사원의
-- 이름, 담당업무, 부서번호를 화면에 보여주세요.
select ename, job, deptno
from emp
where not deptno = 10
order by deptno;


--[문제3] member10 테이블에서 주소가 '서울시'가 아닌 회원의
-- 모든 정보를 화면에 보여주세요.
select *
from member10
where addr not like '서울시%';



--[문제4] products 테이블에서 출고가가 100만원 미만이 아닌 
-- 제품의 상품명과, 입고가, 출고가를 화면에 보여주세요.

select products_name, input_price,output_price
from products
where not output_price <1000000;

/*
그룹함수 

여러 행 또는 테이블 전체에 대하여 함수가 적용되어

하나의 결과값을 가져오는 함수.

1) avg() : 평균값을 구해 주는 함수.

2) count() : 행의 갯수를 구해 주는 함수.
             null 값은 무시하고 행의 갯수를 구해줌.
             
3) max() : 최대값을 구해 주는 함수.

4) min() : 최솟값을 구해 주는 함수.

5) sum() : 총합을 구해 주는 함수.
*/

-- emp 테이블에서 사번을 가지고 있는 모든 사원의
-- 수를 화면에 보여주세요.
select count(empno)
from emp;

-- emp 테이블에서 보너스를 받는 사원의 수를 화면에 보여주세요.
select count(comm)
from emp;

--[문제1] emp 테이블에서 관리자의 수를 화면에 보여주세요.
select count(distinct mgr) "관리자 수"
from emp;


--[문제2] emp 테이블에서 보너스를 가진 사원의 수를 화면에 보여주세요.
select count(comm)
from emp;



--[문제3] emp 테이블에서 모든 SALESMAN의 급여 평균과, 급여 최고액, 
-- 급여최소액, 급여합계액을 화면에 보여주세요.
select avg(sal)"급여평균액"  ,max(sal) "급여최고액" , min(sal) "급여최소액",
sum(sal) "급여합계액"
from emp
where job ='SALESMAN';


--[문제4] emp 테이블에서 등록되어 있는 사원의 총 수, 보너스가 null이 
-- 아닌 인원 수 , 보너스의 평균, 등록되어 있는 부서의 수를 
-- 화면에 보여주세요.
select count( empno)"사원 수", count(comm)"보너스 인원 수", 
avg(comm)"보너스 평균액", count(distinct deptno) "부서 수"
from emp;

/*
시퀀스(sequence)

순번을 부여할 때 사용하는 문법.

형식) create sequence 시퀀스이름

start with 1

increment by 1;

*/

create table memo(
bunho number(5),                        -- 메모 글 번호
title varchar2(100) not null,           -- 메모 글 제목
writer varchar2(50) not null,            -- 메모 글 작성자
content varchar2(500) not null,         -- 메모 글 내용
regdate date,                           -- 메모 작성일
primary key(bunho)
);

-- 메모 작성 시 사용할 시퀀스를 만들어 보자.
create sequence memo_seq
start with 1
increment by 1;

--memo 테이블에 데이터를 저장해 보자.
insert into memo
 values(memo_seq.nextval, '메모1', '홍길동', '홍길동 님 글',sysdate);
insert into memo
 values(memo_seq.nextval,'메모2','이순신','장군님 글',sysdate);
insert into memo
 values(memo_seq.nextval,'메모3','유관순','유관순님 글',sysdate);
insert into memo
values(memo_seq.nextval,'메모4','김유신','유신님 글',sysdate);
insert into memo
values(memo_seq.nextval,'메모5','김연아','연아님 글',sysdate);

/*

join ~ on 키워드

테이블과 테이블을 연결하여 특정한 데이터를 얻고자 할 때 사용하는 키워드

두개 이상의 테이블에 정보가 나뉘어져 있을 때 사용함.

중복해서 데이터가 저장되는 것을 막기 위해 테이블을 나누어서 쓰게 됨.

emp 테이블에서 부서의 상세정보까지 저장을 한다면 10번 부서에 소속된

사원이 3명이므로 근무지가 3번 중복이 되어 나타남.

이렇게 중복되어 저장된 데이터는 추후 삽입, 수정, 삭제 시 이상 현상이

발생할 수 있게 됨.

즉, 이러한 현상이 발생하지 않게 하려면, 즉, 데이터가 중복이 되어 저장되지 

않게 하려면 데이터베이스에서 두 개 이상의 테이블에 정보를 나누어 저장을 헤

두어야 함. 하지만 이렇게 두 개의 테이블로 나누어지게 되면 데이터 중복은 

피할 수 있지만 원하는 정보를 얻으려면 여러 번 질의를 해야하는 불편함이 발생함.

그래서 두 개의 테이블을 결합해서 원하는 정보를 얻어낼 수 있도록 하는 조인이라는 

기능을 제공함

조인의 종류

1) Cross Join

2) Equi Join

3) Self Join

4) Outer Join
*/


-- 1) Cross Join

-- 두 개 이상의 테이블이 조인될 때 조건이 없이 테이블의 결합이 이루어지는 조인.

-- 그렇기 때문에 테이블 전체 행의 컬럼이 조인이 됨. 

-- emp 테이블 사원수(14) * dept 테이블 부서 수(4) ==> 56개의 정보가 나타남.


select *
from emp, dept;

-- 2) Equi Join

-- 가장 많이 사용되는 조인 방법.

-- 조인의 대상이 되는 두 테이블에서 공통적으로 존재하는 컬럼의 값이 

-- 일치되는 행을 연결하여 결과를 생성하는 방법.

-- 두 테이블이 조인하려면 일치되는 공통 컬럼을 사용하면 됨.

-- 컬럼의 이름이 같으면 혼동이 오기 때문에 컬럼 이름 앞에 테이블의

-- 이름을 기술해야 함.

-- emp 테이블에서 사원의 사번, 이름, 담당업무, 부서번호, 부서명, 근무위치를 
-- 화면에 보여주세요  ==> emp 테이블과 dept 테이블을 조인해 주어야 함.
select empno, ename, job, d.deptno, dname, loc
from emp e join dept d
on e.deptno = d.deptno;  
--(// 두테이블에 공통적인 내용만 지정해 주고, 나머지는 지정 안해줘도 됨)

-- emp 테이블에서 사원명이 "SCOTT"인 사원의
-- 부서명을 화면에 보여주세요.
===================여기 다시 확인
select e.ename, d.deptno, d.dname
from emp e join dept d 
on e.deptno = d.depton
where e.ename = 'SCOTT';

--[문제1] 부서명이 'RESEARCH'인 사원의 
-- 사번, 이름, 급여, 부서명, 근무위치를 화면에 보여주세요.
select e.empno,e.ename, e.sal,d.dname, d.loc
from emp e join dept d
on e.deptno = d.deptno
where d.dname = 'RESEARCH';



--[문제2] emp 테이블에서 'NEW YORK'에 근무하는 사원의 
-- 이름과 급여, 부서번호를 화면에 보여주세요.
select e.ename,e.sal,e.deptno
from emp e join dept d
on e.deptno = d.deptno
where d.loc ='NEW YORK';



--[문제3] emp 테이블에서 'ACCOUNTING' 부서 소속 사원의 
-- 이름과 담당업무, 입사일 그리고 부서번호, 부서명울 
-- 화면에 보여주세요.
select e.ename,e.job, e.hiredate,e.deptno,d.dname
from emp e join dept d
on e.deptno = d.deptno
where d.dname ='ACCOUNTING' ;


--[문제4] emp 테이블에서 담당업무가 'MANAGER' 인 사원의 
-- 이름과 담당업무, 부서번호, 부서명을 화면에 보여주세요
select e.ename, e.job, e.deptno, d.dname
from emp e join dept d
on e.deptno = d.deptno
where e.job ='MANAGER';


-- 3) Self Join

-- 하나의 테이블 내에서 조인을 해야 자료를 얻을 수 있는 조인 방법

-- 말 그대로 자기 자신 테이블과 조인을 맺는 것을 말함.

-- from 절 다음에 테이블 이름을 나란히 두 번 기술을 할 수 없음.

-- 따라서 같은 테이블이 하나 더 존재하는 것처럼 사용할 수 있도록 

-- 테이블에 별칭을 붙여서 사용해야 함.



-- emp 테이블에서 각 사원별 관리자의 이름을 화면에 출력해 보자.

-- 예) CLARK의 매니저 이름은 KING 입니다.

select e1.ename || '의 매니저 이름은' || e2.ename || ' 입니다.'
from emp e1 join emp e2
on e1.mgr = e2.empno;

--[문제] emp 테이블에서 매니저가 "KING" 인 사원들의 
-- 이름과 담당업무를 화면에 보여주세요.
select e1.ename, e1.job ,e1.mgr
from emp e1 join emp e2
on e1.mgr = e2.empno
where e2.ename ='KING';

-- 4) Outer Join

-- 2개 이상의 테이블이 조인이 될 때 어느 한 쪽 테이블에는 해당하는 데이터가

-- 다른 쪽 테이블에는 데이터가 존재하지 않는 경우 그 데이터가 출력이 되지

-- 않는 문제점을 해결하기 위해 사용되는 조인 기법.

-- 정보가 부족한 테이블의 컬럼 뒤에 '(+)' 기호를 붙여서 사용을 하면 됨.

select e1. ename || ' 의 매니저 이름은' || e2.ename || ' 입니다.'
from emp e1 join emp e2
on e1.mgr = e2.empno(+);

select e.ename, d.deptno, d.dname
from emp e join dept d
on e.deptno(+) = d.deptno
order by deptno;

/*
dual 테이블

- 오라클에서 자체적으로 제공해 주는 테이블.

- 간단하게 함수를 이용해서 계산, 결과값을 확인할 때 사용하는 테이블.

- 오직 한 행, 한 컬럼만을 담고 있는 테이블

- 사용 용도 : 특정 테이블을 생성할 필요 없이 함수 또는 계산을 하고자 할 때 사용됨.

*/

-- 오라클에서 제공해 주는 함수들

-- 1. 날짜와 관련된 함수들

-- 1) 현재 시스템의 날짜를 구해 오는 함수 : sysdate
select sysdate from dual;

select sysdate "현재 날짜", sysdate-1 "어제 날짜", sysdate+1 "내일날짜"
from dual;

-- 2) 몇 개월 이후의 날짜를 구하는 함수 : add_ months()
--    형식) add_months(현재 날짜, 숫자(개월 수))
select add_months(sysdate,4) from dual;

-- 3) 다가올 날짜(요일)을 구해 주는 함수 : next_day()
--    형식) next_day(현재 날짜, '요일')
select next_day(sysdate, '수요일') from dual;

-- 4) 달의 마지막 날을 구해 주는 함수 : last_day()
select last_day(sysdate) from dual;

-- 5) 두 날짜 사이의 개월 수 차이를 구하는 함수 : months_between()
select empno, ename, hiredate, sysdate,
    months_between(sysdate, hiredate) "개월 수 차"
    from emp;

-- 6) 형식에 맞게 문자열로 날짜를 출력하는 함수 : to_char()
--   형식) to_char(날짜,'날짜형식')
select to_char(sysdate, 'yyyy/MM/dd') from dual;
select to_char(sysdate, 'MM/dd/yyyy') from dual;
select to_char(sysdate, 'yyyy-MM-dd') from dual;


-- 1. 문자와 관련된 함수들.

-- 1) 대문자, 소문자를 바꾸어 주는 함수 :upper(), lower(), initcap()  
-- upper() : 소문자를 대문자로 바꾸어 주는 함수
-- lower() : 대문자를 소문자로 바꾸어 주는 함수.
-- initcap(): 첫 글자는 대문자로, 나머지 문자는 소문자로 바꾸어 주는 함수.
select ename, upper(ename),lower(ename), initcap(ename)
from emp;

-- 2) 문자열의 길이를 구해 주는 함수 : length()
select ename, length(ename)
from emp;

select ename, length(ename)
from emp
where length(ename) >=5;

select length('한글'), lengthb('한글')
from dual;

-- 3) 문자열 일부를 추출하는 함수 : substr()
--    형식) substr("문자열",시작위치,추출할 문자 길이)
-- 시작위치가 음수인 경우에는 오른쪽부터 시작이 된다. 
-- 형식) substr("문자열", 시작위치)


select substr('ABCDEFG',3,2) from dual;     --CD

select substr('ABCDEFG',-3,2) from dual;     --EF

select substr('ABCDEFG', 2) from dual;       -- BCDEFG

-- 4) 문자열을 연결하는 함수 : concat()
--    형식) concat("문자열1","문자열2");
select concat('안녕','하세요') from dual;

-- 문자열을 연결하는 연산자 : ||
select '방가' ||'방가' from dual;

-- 5) 자릿수를 늘려주는 함수
--    왼쪽 자릿수를 늘려주는 함수 : lpad()
--    형식) lpad('문자열', 전체자릿수, '늘어난 자릿수에 들어갈 문자열')
select lpad('ABCDEFG', 15, '*') from dual;


--    오른쪽 자릿수를 늘려주는 함수 : rpad()
--    형식) rpad('문자열', 전체자릿수, '늘어난 자릿수에 들어갈 문자열')
select rpad('ABCDEFG', 15,'*') from dual;

--6) 문자를 지워주는 함수 
--   왼쪽 문자를 지워주는 함수 : ltrim()
select ltrim('ABCDEFG','A') from dual;
--   오른쪽 문자를 지워주는 함수 :rtrim()
select rtrim('ABCDEFG','FG') from dual;

--7) 문자열을 교체해 주는 함수 : replace()
--   형식) replace('원본 문자열', '교체될 문자열', '새로운 문자열');
select replace('Java Program','Java','JSP') from dual;

--[문제1] emp 테이블에서 결과가 아래와 같이 나오도록 화면에 보여주세요.
--        결과) 'SCPTT의 담당업무는 ANALYST 입니다.'
--        단, concat() 함수를 이용하세요.
select concat(ename,'의 담당업무는 '),concat(job,' 입니다.')
from emp;






--[문제2] emp 테이블에서 결과가 아래와 같이 나오도록 화면에 보여주세요
--        결과) 'SCOTT의 연봉은 36000입니다.'
--        단, concat() 함수를 이용하세요.

select concat(ename, '의 연봉은'),concat(sal*12,' 입니다')
from emp;


--[문제3] member10 테이블에서 결과가 아래와 같이 나오도록 화면에 보여주세요.
--       결과) '홍길동 회원의 직업은 학생입니다.'
--        단, concat() 함수를 이용하세요.
select concat (memname, ' 회원의 직업은 ' ) , concat(job, ' 입니다')
from member10;



--[문제4] emp 테이블에서 사번, 이름, 담당업무를 화면에 보여주세요.
--        단, 담당업무는 소문자로 변경하여 화면에 보여주세요.
select empno, ename, lower(job)
from emp;


-- [문제5] 여러분의 주민등록 번호 중에서 생년월일을 추출하여 화면에 보여주세요.
select substr('940401-222222', 1,6)
from dual;

--[문제6] emp 테이블에서 담당업무에 'A'라는 글자를
--        '$'로 바꾸어 화면에 보여주세요.
select replace(job,'A' ,'$')
from emp;


--[문제7] member10 테이블에서 직업이 '학생'인 정보를 
--       '대학생'으로 바꾸어 화면에 보여주세요.
select replace(job,'학생', '대학생')
from member10;

-- [문제8] member10 테이블에서 주소에 '서울시' 로 된 정보를 
--        '서울특별시' 로 바꾸어 화면에 보여주세요.
select replace(addr, '서울시','서울특별시')
from member10;