Story of Hyun

-- 가방 판매쇼핑몰의 회원가입과 회원만 글을 쓸 수 있는 자유게시판의 테이블을 설계하여 제약조건을 설정한다.

create table membersInfo(

 memNum number(5) primary key,

 memID varchar2(12) not null,  -- unique

 memPwd varchar2(12) not null,

 memName varchar(10) not null,

 birthday date,

 zipcode number(5),

 addr1 varchar2(50),

 addr2 varchar2(30),

 email varchar2(20) not null,

 tel varchar(13) not null,

 writedate date default sysdate,

 lastvisitdate date default sysdate,

 memLevel char(1) default 'D'

);

-- memId 필드는 중복허용하지 않는다.

alter table memberInfo

add constraint UK_MEMID_MEMBERINFO unique (memId);

create table freeBoard(

 boardNum number(5) primary key,

 memId varchar2(12) not null,  -- foreign key

 subject varchar(100) not null,

 content clob not null,

 hit number(3) default 0,

 writedate date default sysdate

);

alter table freeBoard

add constraint FK_memid_freeboard

foreign key (memid) references MEMBERINFO(memberid);

select * from user_constraints;

create sequence memberInfoSqu

start with 1

increment by 1;

create sequence freeBoard

start with 1

increment by 1;

select * from user_sequences;

-- 외부파일의 쿼리문 실행

@c://fileFolder/member.ddl  -- @경로값

-- SEQUENCE : 유일한 값을 생성해주는 오라클 객체 / 번호를 자동으로 생성하는 객체

create sequence memberSqu

start with 10   -- 시작값

increment by 10;    -- 증가값

create sequence membersqc start with 1 increment by 1;

create sequence hyun start with 100 increment by 100;

select * from membertbl;

insert into membertbl(num, name, addr, phone)

values(membersqu.nextVal, 'ccc', 'cccccc', '010-444-4444');

select membersqu.currval from dual;  -- 시퀀스의 현재값

select membersqu.nextval from dual;  -- 시퀀스의 다음값

-- 시퀀스 목록 확인

select * from user_sequences;

--시퀀스 수정

alter sequence hyun increment by 200;

--시퀀스 삭제

drop sequence hyun;

-- 1부터 3씩 증가하는 시퀀스 생성, 객체명 : testSqu

create sequence testSqu start with 1 increment by 3;

-- testSqu의 증가값을 10단위로 수정

alter sequence testSqu increment by 10;

-- 시퀀스 목록 확인

select * from user_sequences;

-- testSqu 시퀀스 객체 삭제

drop sequence testSqu;

-- GROUP BY절과 HAVING절

-- GROUP BY : 테이블의 행들을 원하는 그룹으로 나누기

-- 부서별 그룹의 합계

select deptno, sum(sal) from emp group by deptno;

-- 담당업무별 급여의 합계와 평균 구하기

select job 담당업무, sum(sal) 급여합, ceil(avg(sal)) 급여평균 from emp group by job order by job;

-- 부서별 급여의 합계와 평균, 보너스의 합계와 평균을 구하되 81년도에 입사한 사원만으로 통계를 구하라

select deptno 부서, sum(sal) 급여합계, ceil(avg(sal)) 급여평균, sum(nvl(comm, 0)) 보너스합계, ceil(avg(nvl(comm, 0))) 보너스평균 from emp where hiredate like '81%' group by deptno;

-- ROLLUP : GROUP BY절에 의해서 그룹 지어진 집합결과에 대해서 좀 더 상세한 정보를 반환

-- 담당업무별 급여의 합계를 구하라. 담당업무를 오름차순으로 정렬하라

select job, sum(sal) from emp group by rollup(job) order by job;

-- 1차 분류 부서코드(deptno), 2차 분류 담당업무(job)로 급여의 합계와 평균을 출력

select deptno, job, sum(sal), avg(sal) from emp group by deptno, job order by deptno;

select deptno, job, sum(sal), avg(sal) from emp group by rollup(deptno, job) order by deptno;

select deptno, job, sum(sal), ceil(avg(sal)) from emp group by cube(deptno, job) order by deptno;

select deptno, job from emp order by deptno asc, job asc;

-- JOIN

-- 참고용 : 추후 진행

-- 사원명, 부서코드, 부서명, 부서위치 선택

select ename, emp.deptno, dname, loc from emp, dept where emp.deptno=dept.deptno;

select emp.ename, emp.deptno, dept.dname, dept.loc from emp, dept where emp.deptno=dept.deptno;

select e.ename, e.deptno, d.dname, d.loc from emp e, dept d where e.deptno=d.deptno;

select ename, e.deptno, dname, loc from emp e, dept d where e.deptno=d.deptno;

-- GROUP BY HAVING : 그룹함수의 통계 결과를 이용하여 조건설정

-- 담당업무별 급여의 합계를 구하여, 합계가 5,000불 이상인 담당업무만 출력하라

select job 담당업무, sum(sal) 급여합계 from emp group by job having sum(sal)>5000;

-- 부서코드별 사원수를 구하여 사원수가 4명 이상인 부서만 출력하라.

select * from emp;

select deptno 부서코드, count(*) 사원수 from emp group by deptno having count(*)>=4;

-- 입사년도별 급여의 합계와 사원수를 구하고, 사원수가 5명 미만인 입사년도를 출력하되 담당업무가 PRESIDENT는 제외하라

select to_char(hiredate, 'yyyy') 입사년도, sum(sal) 급여합계, count(*) 사원수 from emp where job!='PRESIDENT' group by to_char(hiredate, 'yyyy') having count(*)<5;

-- 그룹 함수 : 통계 함수 / 여러 행 또는 테이블 전체의 행에 대해 함수가 적용

-- COUNT : 갯수 구하기

select count(empno) from empcopy;

select count(comm) from empcopy;

select count(ename) from empcopy;

select count(job) from empcopy;

select count(*) from empcopy;

select ename, count(hiredate) from emp; -- error :그룹함수는 그룹함수끼리만 사용 가능

-- 81년도에 입사한 사원수

select count(*) from emp where to_char(hiredate, 'yy')='81';

select count(*) from emp where hiredate like '81%';

-- MANAGER는 몇명인가?

select count(*) from emp where hiredate like '81%' and job='MANAGER';

-- MAX : 최대값

select max(sal) from emp;

select max(comm) from emp;

-- MIN : 최소값

select min(sal) from emp;

select min(comm) from emp;

-- 부서코드 20인 사원은 최대 급여와 최소 급여는 얼마인가?

select max(sal), min(sal) from emp where deptno=20;

-- SUM : 합계

-- 급여 총액

select sum(sal) from emp;

-- 보너스 총액

select sum(comm) from emp;

-- AVG : 평균

select avg(sal) from emp;

select round(avg(sal), 2) from emp;

-- 보너스 평균

select avg(comm) from emp;

-- null을 포함한 보너스 평균

select avg(nvl(comm, 0)) from emp;

select round(avg(nvl(comm, 0)), 2) from emp;

-- 부서코드가 20인 사원의 급여의 합과 평균을 구하라

select sum(sal), round(avg(sal), 2) from emp where deptno=20;

-- 담당업무가 SALESMAN인 사원중 부서코드가 30인 사원의 보너스의 합과 평균을 구하라

select sum(comm), round(avg(nvl(comm, 0)), 2) from emp where job='SALESMAN' and deptno=30;

-- STDDEV : 표준 편차

-- 급여에 대한 표준편차

select round(stddev(sal), 2) from emp;

-- 변환 함수

-- TO_CHAR : 변환함수

select sysdate from dual;

select to_char(sysdate, 'month') from dual; -- 월만 출력

select to_char(sysdate, 'yyyy') from dual;  -- 년도만 출력

select to_char(sysdate, 'dy') from dual;  -- 요일만 출력

-- emp 테이블에서 담당업무가 MANAGER 이거나 부서코드가 20인 사원의 입사월을 출력하라

-- 사원명, 담당업무, 부서코드, 입사일, 입사월

select * from emp;

select ename 사원명, job 담당업무, deptno 부서코드, hiredate 입사일, to_char(hiredate, 'month') 입사월 from emp where job='MANAGER' or deptno=20;

select ename 사원명, job 담당업무, deptno 부서코드, hiredate 입사일, to_char(hiredate, 'mm') 입사월 from emp where job='MANAGER' or deptno=20;

-- TO_NUMBER : 문자를 숫자로 변환 / 안써도 됨

-- select to_number('1234')+100 from dual;

select '1234'+100 from dual;

select '1234'+'100' from dual;

-- 날짜 처리 함수

-- LAST_DAY(d) : (d)달의 마지막 날 표시

select sysdate, last_day(sysdate) from dual;

select ename, hiredate, last_day(hiredate) from emp;  -- 입사한 달의 마지막 날

--ADD_MONTHS(a, b) : a 날짜에 b의 값을 더한 달을 표시

select sysdate, add_months(sysdate, 5) from dual; -- 오늘을 기준으로 5개월 뒤

select add_months('2017-01-31', 1) from dual; --  17년 1월 31일을 기준으로 1개월 뒤

select add_months(sysdate, -3) from dual; -- 오늘을 기준으로 3개월 전

-- emp 테이블의 사원의 입상후 20개월 후는 언제인가?

select * from emp;

select ename, hiredate, add_months(hiredate, 20) from emp;

-- MONTHS_BETWEEN(a1, a2) : 날짜와 날짜 사이의 개월수 구하기

select floor(months_between('2016/12/10', '2017/02/25')) from dual;

-- emp 테이블의 사원은 근무기간은 몇개월인가?

select * from emp;

select ename, hiredate, ceil(months_between(sysdate, hiredate)) 근속개월 from emp;

-- ROUND(d, [f]) : 날짜의 반올림 / year, month, day

-- TO_DATE() : 문자열을 날짜로 변환

-- 년도를 기준으로 반올림(7월)

select round(to_date('2016/08/01', 'yyyy/mm/dd'), 'year') from dual;

select * from empcopy;

update empcopy set hiredate='2016/10/10' where empno=6666;

select round(sysdate, 'year') from dual;

-- 입사일 기준 년도를 반올림하여 출력하라.

-- 사원명, 입사일, 반올림한 년도

select * from emp;

select ename 사원명, hiredate 입사일, round(hiredate, 'year') 반올림년도 from emp;

-- 월을 기준으로 반올림

select round(sysdate, 'month') from dual; -- 오늘 기준

select round(to_date('2016/10/17'), 'month') from dual; -- 특정일 기준

select ename, hiredate, round(hiredate, 'month') from emp;  -- emp 테이블 입사일 기준

-- 일을 기준으로 반올림

-- TO_CHAR() : 날짜나 숫자를 문자로 변환

select round(sysdate, 'day') from dual;

select to_char(sysdate, 'yyyy/mm/dd hh24:mi:ss pm') from dual;

-- EMP 입사일의 날짜를 반올림하라.

-- 출력은 이름, 입사일, 반올림한 입사일

-- 입사일은 mm/dd hh24:mi:ss 표시

select * from emp;

select ename 사원명, to_char(hiredate, 'mm/dd hh24:mi') 입사일, round(hiredate, 'dd') 반올림 from emp;

-- 날짜에 대한 산술연산

-- 오늘을 기준으로 1000일 후

select sysdate+1000 from dual;

-- 문자형으로 출력

select to_char(sysdate+1000, 'yyyy-mm-dd hh') from dual;  

-- 오늘을 기준으로 100일 전

select sysdate-100 from dual;

-- 2016-11-23 기준으로 960일 후는 언제인가?

select to_date('2016/11/23')+960 from dual;

select to_date('2016-11-23', 'yyyy-mm-dd')+960 from dual;

-- 날짜 경과 계산

select sysdate - to_date('2016/11/23', 'yyyy/mm/dd') from dual;

-- emp 테이블의 사원중 부서코드가 30 사원의 근무일수를 구하라.

-- 사원명, 입사일, 급여, 근무일수, 부서코드

select * from emp;

select ename 사원명, hiredate 입사일, sal 급여, ceil(sysdate-hiredate) 근무일수, deptno 부서코드 from emp where deptno=30;

-- General Function

-- NVL : null값 처리 (not value)

select ename, sal, comm, comm+100 from emp;

select ename, sal, comm, nvl(comm, 0)+100 from emp;

-- emp 테이블에서 사원명, 관리자코드, 급여, 보너스를 출려하되 관리자코드 null인 경우 0000 으로 보너스는 null인 경우 0으로 출력하라.

select ename, nvl(mgr, '0000'), sal, nvl(comm, 0) from emp;

-- DECODE : 조건문 / 데이터를 다른 값으로 변환

select ename, deptno, decode(deptno, 10, '어카운팅', 20, '리서치', 30, '세일즈', 40, '오퍼레이션') 부서명 from emp;

-- emp 테이블에서 사원번호, 사원명, 담당업무, 급여를 출력하되 담당업무는 CLERK-사무직, SALESMAN-세일즈맨, MANAGER-매니저, ANALYST-시스템분석가, PRESIDENT-대표이사로 표시하라.

select * from emp;

select empno, ename, decode(job, 'CLERK', '사무직', 'SALESMAN', '세일즈맨', 'MANAGER', '매니저', 'ANALYST', '시스템분석가', 'PRESIDENT', '대표이사') job, sal from emp;

select * from tab;

select * from emp1;

-- 필드 추가

alter table emp1

add (email varchar2(30));

select * from emp1;

-- 이메일 수정

update emp1 set email='ssss@naver.com' where hiredate like '__/02%';

update emp1 set email='aaaaa@nate.com' where ename like '%A%';

update emp1 set email='nnnnn@daum.com' where ename like '%N%';

update emp1 set email='null@hanmail.net' where email is null;

commit;

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

-- 이름    이메일            아이디 도메인

-- smith  null@hanmail.net  null  hanmail.net

select ename 이름, email 이메일, substr(email, 1, instr(email, '@') -1) 아이디, substr(email, instr(email, '@') +1, length(email)) 도메인 from emp1;

select ename, email, substr(email, 1, instr(email, '@') -1) 아이디, substr(email, instr(email, '@') +1, length(email) - instr(email, '@')) 도메인 from emp1;

-- CONCAT(char1, char2) : 문자 연결

-- select concat('blue', 'color') concat from dual;

-- INITCAP(char) : 단어의 첫번째 문자를 대문자로 변환

-- select initcap('hong gil dong') from dual;

-- LOWER(char) : 모든 문자를 소문자로 변환

-- select lower('HONG GIL DONG') from dual;

select lower(ename) from empcopy;

-- UPPER(char) : 모든 문자를 대문자로 변환

-- select upper('hong gil dong') from dual;

-- 함수 안에 함수 사용

select concat(lower(ename), upper(ename)) 이름 from empcopy;

-- LPAD(char1, n, [char2]) : 왼쪽에 문자열 채우기

-- select lpad('gil dong', 10, '*') from dual;

select lpad(ename, 10, '*') from empcopy;

-- RPAD(char1, n, [char2]) : 오른쪽에 문자열 채우기

-- select rpad('gil dong', 10, '*') from dual;

select rpad(ename, 10, '*') from empcopy;

-- SUBSTR(char, m, [n]) : m 번째 자리부터 길이가 n개인 문자열을 반환 / m이 음수일 경우에는 뒤에서 M번째 문자부터 반대 방향으로 n개의 문자를 반환

-- select substr('hong gil dong', 6, 3) from dual;

-- select substr('hong gil dong', -5, 3) from dual;

select job, substr(job, 2, 3) from empcopy;

-- LENGTH(char) : 글자수 구하기

-- select length('gil dong') from dual;

select ename, length(ename) from empcopy;

-- 이름의 문자길이의 50% 만큼 출력하고, 나머지 문자는 '*' 로 표시하라

--select ename 이름1, rpad(substr(ename, 1, length(ename)/2), length(ename), '*') 이름2 from empcopy;

select ename, rpad(substr(ename, 1, ceil(length(ename)/2)), length(ename), '*') from empcopy;

-- REPLACE(cahr1, str1, str2) : 문자 치환

select ename, replace(ename, 'A', '에이') replace from empcopy;

-- INSTR : 문자위치 구하기

select instr(job, 'SA') instr, job from empcopy;

select instr(job, 'A') instr, job from empcopy;

--                     3번째 문자부터 검색

select instr(job, 'A', 3) instr, job from empcopy;

--                     2번째 문자부터 검색을 시작해서 'A' 문자가 두번째 위치의 값

select instr(job, 'A', 2, 2) instr, job from empcopy;

-- TRIM : 앞뒤 공백 및 특정문자 제거 / 중간의 공백 및 특정문자에는 영향이 없음 / 숫자의 경우 문자로 변환됨

select 'result = ' || trim('   sam   ple   ')  || '결과' from dual;

select ename, trim('S' from ename) from emp;

-- LTRIM : 왼쪽 문자 제거

select ltrim('abcabcTestabc', 'abc') from dual;

-- RTRIM : 오른쪽 문자 제거

select rtrim('abcacbcSampleTest', 'abt') from dual;

-- comm(보너스)가 null인 값을 -100으로 수정

update empcopy set comm=-100 where comm is null;

-- ABS(n) : 절대값을 계산하는 함수

-- select abs(음수) from dual;

select ename 이름, sal 급여, abs(comm) 보너스 from empcopy;

-- CEIL(n) : 올림

-- select ceil(소숫점 수) from dual;

select ename 이름, sal*1.15 급여1, ceil(sal*1.15) 급여2 from empcopy;

-- FLOOR(n) : 버림

-- select floor(소숫점 수) from dual;

select ename 이름, sal*1.15 급여1, floor(sal*1.15) 급여2 from empcopy;

-- MOD(m, n) : 나머지 구하기

-- select mod(10, 3) from dual;

select ename, sal, comm, mod(sal, comm) from empcopy;

-- POWER(m, n) : m의 n 승 구하기

-- select power(5, 8) from dual;

-- ROUND(n, [m]) : n 값의 반올림 / m은 양수일 경우 소숫점 반올림 위치, 음수일 경우 정수 반올림 위치

-- select round(12345.258, 1) from dual;

select ename 이름, sal 급여1, round(sal, -2) 급여2 from empcopy;

-- SQRT(n) : n의 루트값 구하기

-- select sqrt(10) from dual;

-- TRUNC(n, m) : n의 값에서 m 이하의 수 버리기 / m은 양수일 경우 소숫점 위치, 음수일 경우 정수 위치

-- select trunc(7.5597, 2) from dual;