DEVELOPPO

출처 : http://302.pe.kr/379

Oracle SQL Developer

- Oracle SQL Developer is a free integrated development environment that simplifies the development and management of Oracle Database. SQL Developer offers complete end-to-end development of your PL/SQL applications, a worksheet for running queries and scripts, a DBA console for managing the database, a reports interface, a complete data modeling solution, and a migration platform for moving your 3rd party databases to Oracle.

- http://www.oracle.com/technetwork/developer-tools/sql-developer/index.html

♣ 들여쓰기 기본값 변경 하기 (2-->4)

- SQL Developer 의 들여쓰기 기본값은 2.

- 도구>환경설정> 데이터베이스> SQL 포맷터>Oracle 형식 지정>편집>들여쓰기 메뉴에서 값을 변경 할 수 있다.

♣ 들여쓰기에 공백(Space) 대신 탭(Tab) 사용하기

- Preferences -> Database -> SQL Formatter -> Alignment and Indentation -> Use tabulator(도표 작성기 사용) 체크.

♣ Format Code

- 쿼리를 지정 된 포맷에 따라 정렬하기

- 단축키: Ctrl + F7

♣ SQL에디터에서 줄번호 보이게 설정

 - 도구 > 환경설정 > 코드 편집기 > 행 여백 > 행 번호 표시에 체크.

♣ 완성 인사이트 대문자로 설정 하기

- 도구 > 환경설정 > 코드 편집기 > 완성 인사이트 > 입력할 때 대소문자 변경 체크 후 'Upper Case'로 설정.

♣ 유용한 단축키 목록

--------- ALER TABLE
-- 테이블 삭제 : 저장공간을 삭제, 저작되어있는 데이터도 모두 삭제
drop table test_tbl;

-- 모든 로우를 제거하는 truncate : 바로삭제!
create table emp06
as
select * from emp;
select * from emp06;

truncate table emp06;


---------- 테이블 이름변경
-- rename old_name to new_name;
rename emp06 to new_emp;




----------  ALTER TABLE : 테이블 구조의 변경
-- alter table table_name 
-- add (컬럼추가) | modify(컬럼변경) | drop(컬럼삭제) ();

-- 기존 테이블에 속성을 추가 : 각 행의 컬럼 데이터는 null값
-- emp01에  job 컬럼을 추가. (varchar2(10)
desc emp01;

alter table emp01 
add(JOB varchar2(10));
alter table emp01 
add(deptno number(2));


-- 기존 테이블의 컬럼 변경 modify 
-- 새롭게 정의된 컬럼으로 교체하는 것

alter table emp01
modify (deptno number(10));

-- 기존 테이블의 컬럼을 삭제 : 데이터도 사라진다
alter table emp01
drop (deptno);

---------- 테이블 생성 : 
-- create table table_name
-- (
--     column_name domain [constraint] 제약/선택적,
--     column_name domain [constraint],
--     column_name domain [constraint],
--     ...
--   )

create table test_tbl (
        no              number(4),
        user_name       varchar2(10),
        user_id         varchar2(16),
        user_password   varchar2(12),
        reg_date        Timestamp DEFAULT sysdate
    );

desc test_tbl;

-- 사원 테이블과 유사한 구조의 사원번호, 사원이름, 급여 3개의 칼럼으로 구성된
-- emp01 테이블을 생성해 보자.
create table emp01(
        empno number(4),
        ename varchar2(10),
        sal   number(7,2)
    );

---------- create table ~as ~ : 테이블 복사! but 제약조건은 복사되지 않는다.

-- CREATE TABLE 명령어 다음에 컬럼을 일일이 정의하는 대신 
-- AS 절을 추가하여 EMP 테이블과 동일한 내용과 구조를 갖는
-- EMP02 테이블을 생성해 봅시다.
CREATE TABLE EMP02 AS SELECT * FROM EMP;
desc emp02;
desc emp;


--서브 쿼리문의 SELECT 절에 * 대신
--원하는 컬럼명을 명시하면 기존 테이블에서 일부의 컬럼만 복사할 수 있습니다.
create table emp03
as
select empno, ename, sal from emp
;

desc emp03;

--서브 쿼리문의 SELECT 문을 구성할 때
-- WHERE 절을 추가하여 원하는 조건을 제시하면 기존 테이블에서 일부의 행만 복사합니다.

create table emp04
as
select * from emp where deptno=10
;
select * from emp04;


-- 테이블의 구조만 복사하는 것은 별도의 명령이 있는 것이 아닙니다. 
--이 역시 서브 쿼리를 이용해야 하는데 WHERE 조건 절에 항상 거짓이 되는 조건을 지정하게 되면 
--테이블에서 얻어질 수 있는 로우가 없게 되므로 빈 테이블이 생성되게 됩니다

create table emp05
as
select * from emp where 1=2
;
select * from emp05;

-- 부속질의

-- 스칼라 부속질의 / select 부속질의,
-- 단일행, 단일열, 단일값의 결과가 나와야한다.

select name from customer where custid=1;

select custid, (select name from customer where customer.custid=orders.custid),
        saleprice, orderdate
from orders
;


--마당서점의 고객별 group by
-- 판매액을 보이시오 orders
-- (결과는 고객이름과 고객별 판매액을 출력).

select c.custid, c.name, sum(o.saleprice)
from customer c, orders o
where c.custid=o.custid(+)
group by c.custid, c.name
order by sum(o.saleprice)
;

select o.custid, (
    select name from customer c where o.custid=c.custid),
sum(o.saleprice)
from orders o
group by o.custid
;

-- 인라인 뷰 / from 부속질의

--질의 고객번호가 2 이하인 고객의 판매액을 보이시오 
--(결과는 고객이름과 고객별 판매액 출력)
select custid, name
from customer
where custid<=2
; -- 가상테이블

select cs.name, sum(o.saleprice)
from (select custid, name
from customer
where custid<=2) cs , orders o
where cs.custid=o.custid
group by cs.name
;

-- 중첩질의 – WHERE 부속질의
-- 평균 주문금액 이하의 주문에 대해서 주문번호와 금액을 보이시오.
select orderid, saleprice
from orders
where saleprice < (select avg(saleprice) from orders)
;

-- 각 고객의 평균 주문금액보다 
-- 큰 금액의 주문 내역에 대해서
-- 주문번호, 고객번호, 금액을 보이시오.
select avg(saleprice) 
from orders
group by custid
;


select o.orderid, o.custid, o.saleprice, c.name
from orders o, customer c
where saleprice >
(select avg(saleprice) 
from orders
o2 where o.custid=o2.custid)
AND (o.custid=c.custid)
;


--대한민국에 거주하는 고객에게 판매한 도서의 총판매액을 구하시오.

select sum(saleprice)
from orders
where custid in (select custid from customer where address LIKE '%대한민국%')
;

--3번 고객이 주문한 도서의 최고 금액보다 
--더 비싼 도서를 구입한 주문의 주문번호와 금액을 보이시오.

select orderid, saleprice
from orders
-- where saleprice > (select max(saleprice) from orders where custid=3);
where saleprice > all(select saleprice from orders where custid=3)
;

-- EXISTS 연산자로 대한민국에 거주하는 고객에
-- 판매한 도서의 총 판매액을 구하시오.  

select * from customer where address LIKE '%대한민국%';

select sum(saleprice)
from orders o
where EXISTS (select * from customer c where address LIKE '%대한민국%' AND o.custid=c.custid)
;

-- 중첩질의 : 단순비교 

--평균 급여를 구하는 쿼리문을 서브 쿼리로 사용하여
--평균 급여보다 더 많은 급여를 받는 사원을 검색

select *
from emp        -- ↓ 평균급여
where sal > (select avg(sal)from emp)    
;

-- SCOTT 사원의 같은 부서에 근무하는 사원의 리스트를 출력
select deptno from emp where ename='SCOTT'; --20

select ename, deptno from emp 
where deptno=(select deptno from emp where ename='SCOTT');

-- sal>3000이상 받는 사원이 소속된 부서
select distinct deptno from emp where sal >=3000;

select *
from emp
--where deptno=10 or deptno=20
where deptno in (select distinct deptno from emp where sal >=3000)
;

--30번 소속 사원들 중에서 급여를 가장 많이 받는 사원보다
--더 많은 급여를 받는 사람의 이름, 급여를 출력하는 쿼리문을 작성해 봅시다.
select sal from emp where deptno=30;

select ename, sal from emp 
where sal> all (select sal from emp where deptno=30);
-- 모든게 다 만족해야할 때

--다음은 부서번호가 30번인 사원들의 급여 중
--가장 작은 값(950)보다 많은 급여를 받는 사원의 이름,
--급여를 출력하는 예제를 작성해 봅시다.
select ename, sal from emp 
where sal> any (select sal from emp where deptno=30);

-- ROWNUM #입력순 (로우넘 날짜순X)
select rownum, empno, ename, hiredate
from emp
order by hiredate
;

-- 기준에 맞춰 몇개 뽑아내기
select rownum, empno, ename, hiredate
from emp
where rownum<4
order by hiredate
;

select rownum, empno, ename, hiredate from emp order by hiredate
;

-- ROWNUM 날짜순 O
select rownum, empno, ename, hiredate
from (select rownum, empno, ename, hiredate from emp order by hiredate desc)
where rownum<4 -- 입사일이 제일 최근인 세명 (desc)
;

-- 가장 급여를 많이 받는 사원 3명을 출력하자
select rownum, ename, sal from emp order by sal desc;
select rownum, ename, sal 
from(select rownum, ename, sal from emp order by sal desc)
where rownum<4
;

-- CROSS JOIN 크로스 조인 : 조건 없이 테이블과 테이블을 붙여준다
-- 단순하게 붙여주는 것이라 아무 의미가 없다.
select *
from emp, dept;

-- EQUI JOIN
-- 동일 칼럼을 기준으로 조인합니다.
SELECT *
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO;

-- SCOTT의 사원번호와 이름, 부서이름, 지역을 출력하자
SELECT empno, ename, dname, loc, dept.deptno -- deptno만 쓰면 오류남 or emp.deptno
-- emp.empno, emp.ename, dept.dname, dept.loc, dept(emp).deptno로 쓰는 것이 사실 맞음
FROM emp, dept
where ename='SCOTT' AND emp.deptno=dept.deptno
;

-- 별칭
-- 테이블의 이름이 긴경우.
SELECT e.empno, e.ename, d.dname, d.loc, d.deptno
FROM emp e, dept d
where ename='SCOTT' AND e.deptno=d.deptno
;

-- NON-EQUI JOIN : 동등비교가 아닌 비교연산이 가능
-- 급여 등급을 5개로 나누어 놓은 salgrade에서 정보를 얻어 와서 
-- 각 사원의 급여 등급을 지정해보도록 합시다. 
-- 이를 위해서 사원(emp) 테이블과 급여 등급(salgrade) 테이블을 조인하도록 합시다.
select e.ename, e.sal, s.grade
from emp e, salgrade s
where e.sal between s.losal and s.hisal
;

-- SELF JOIN : 자신의 테이블을 조인
-- SMITH의 매니저 이름이 무엇인지 알아내려면 어떻게 구해야 할까요?

select ename, mgr
from emp
where ename='SMITH'
;

-- +
select ename, mgr
from emp
where empno=7902
;


-- =

select e.ename, e.mgr, m.ename as MGRNAME
from emp e, emp m
where e.mgr=m.empno AND e.ename='SMITH'
;

-- outer join : 정보가 부족해도 출력하도록 하는 join

select e.ename, e.mgr, m.ename as MGRNAME
from emp e, emp m
where e.mgr=m.empno(+)
;

-- ANSI JOIN : CROSS JOIN
select *
from emp cross join dept
;

-- INNER JOIN / ON : 조인의 조건

--SELECT ename, dname
--FROM emp, dept
--where ename='SCOTT' AND emp.deptno=dept.deptno
--;

SELECT ENAME, DNAME 
FROM EMP INNER JOIN DEPT 
ON EMP.DEPTNO=DEPT.DEPTNO 
WHERE ENAME='SCOTT';

-- USING
select *
from emp join dept
-- on emp.deptno=dept.deptno
using (deptno) 
-- 공통 컬럼은 하나만 가지게 해준다. 공통 컬럼을 구별할 필요가 없다
;

-- NATURAL JOIN : 동일 컬럼 조인의 조건을 생략
-- 같은 이름의 컬럼은 하나씩 존재해야 한다.
select *
from emp NATURAL JOIN dept
;

-- ansi outer join
-- table1 [LEFT | RIGHT | FULL ] outer join table2

select *
from emp e left outer join emp m
on (e.mgr=m.empno)
;

:

-- 회원 구매
-- customer, orders

select * from customer;
select * from orders order by custid;

select *
from customer c left outer join orders o
on (c.custid=o.custid)
;

-- 회원별 구매횟수
select c.name, count(o.orderid), sum(o.saleprice), round(avg(o.saleprice))
from customer c, orders o
where c.custid=o.custid(+)
group by c.name
;

# chapter03_02
# 파이썬 문자형

# 문자열 생성
str1= "I am Python"
str2= 'python'
str3= """How are you?"""
str4= '''Thank you'''

print(type(str1), type(str2), type(str3), type(str4))
print(len(str1)) #문자열의 길이

# 빈문자열
str1_t1="" # or str_t1=''
str2_t2 = str()

print(type(str1_t1), len(str1_t1))
print(type(str2_t2), len(str2_t2)) # 둘다 STR, 길이는 0


# 이스케이프 문자 사용 # 탈출 문자
# I'm Boy >> '
print("I'm Boy")  #print('I'm Boy')
print('I\'m Boy')
print('\' , \\ , \t tap, \n enter')
#등등등, 자바와 같은 느낌, 이스케이프 문자 검색해보기
escape_str1="do you have a \"retro games?\""
print(escape_str1)
escape_str2='what\'s on TV?'
print(escape_str2)
# 탭, 줄바꿈
t_s1= "click \t start!"
t_s2="new Lint \n check!"
print(t_s1)
print(t_s2)
print()
# Raw String -  있는 그대로 표시해준다
raw_s1=r'd:\python\test'
print(raw_s1)
print()

# 멀티라인 입력
# 역슬래시를 사용하면 유용하다
multi_str= \
"""
String
multi Line
test
"""
multi_str2="""
string
multi Line
test2
"""

print(multi_str)
print(multi_str2)
print()


# 문자열 연산 (String에서 in 연산자를 쓸 수 있다는 점을 기억하자)
str_o1= "python"
str_o2= "Apple"
str_o3= "How are you doing"
str_o4= "Seoul Daejeon busan"

print(str_o1 * 3 ) #세번반복되서 출력
print(str_o1 + str_o2)
print('y' in str_o1) # str_o1안에 y가 있어? (True/False) > 시퀀스 형에 사용가능
print('P' not in str_o2) #대문자 소문자 구별함
print()

# 문자열 형변환
print(str(66), type(str(66))) # 문자66을 의미.
print(str(10.1), type(str(10.1)))
print(str(True), type(str(True)))
print()


# 문자열 함수 (upper, iaalnum, startswith, count, endswith, isalpha ... )
print("Capitalize : ", str_o1.capitalize()) #첫글자를 대문자로 바꿔준다.
print("endswith? : ", str_o2.endswith("e")) #마지막 문자가 무엇인지 체크 boolean (ex 마침표)
print("replace : ", str_o1.replace("py","PPYY")) #바꿔줌
print("sorted : ", sorted(str_o1)) # 리스트 형태로 반환
print("split : ", str_o4.split(" ")) # 기준을 정해서 그것을 기준으로 리스트로 반환
print()

# 반복(시퀀스)
im_str= "good boy!"

print(dir(im_str)) #__iter__(반복) #im_str에서 사용하는 모든 것 나열

#출력 (슬라이스 가능)
for i in im_str :  print(i)
print()


# 슬라이싱
str_s1 = "Nice Python"
print(len(str_s1))
# 슬라이싱 연습
print(str_s1[0:3]) # 0 1 2
print(str_s1[:3]) # 처음부터 세개. 위에거랑 같음
print(str_s1[5:11])
print(str_s1[5:]) # 5부터 끝까지, 위에거랑 같음
print(str_s1[:len(str_s1)]) #끝부분의 길이를 모를때는 len이용 #[:11]
print(str_s1[:len(str_s1)-1]) #[:10]
print(str_s1[1:9:2]) # 세번째 인수는 단위, 몇개단위로 출력
print(str_s1[-5:]) #뒤에서는 -1부터 시작한다
print(str_s1[1:-2])
print(str_s1[::2]) #처음부터 끝까지 2칸간격으로
print(str_s1[::-1]) #역으로 출력됨
print()

# 아스키코드(또는 아스키코드)
a ='z'

print(ord(a)) # 아스키코드로,
print(chr(122)) # 문자로,

-- 1. 덧셈연산자를 이용하여 모든 사원에 대해서 $300의 급여 인상을 계산한 후 사원의 이름, 급여, 인상된 급여를 출력하시오.

SELECT ename, sal, sal+300
from emp
;

-- 2. 사원의 이름, 급여, 연간 총 수입을 총 수입이 많은 것부터 작은 순으로 출력하시오,
-- 연간 총수입은 월급에 12를 곱한 후 $100의 상여금을 더해서 계산하시오.

SELECT ename, sal, (sal*12+100) as sal2
FROM emp
order by sal2 asc
;

-- 3. 급여가 2000을 넘는 사원의 이름과 급여를 표현, 급여가 많은 것부터 작은 순으로 출력하시오.

SELECT ename, sal
from emp
where sal>=2000
order by sal desc
;

-- 4. 사원번호가 7788인 사원의 이름과 부서번호를 출력하시오.

SELECT ename, deptno
from emp
where empno=7788
;

-- 5. 급여가 2000에서 3000 사이에 포함되지 않는 사원의 이름과 급여를 출력하시오.

select ename, sal
from emp
where sal not BETWEEN 2000 AND 3000
;

-- 6. 1981년 2월 20일 부터 1981년 5월 1일 사이에 입사한 사원의 이름, 담당업무, 입사일을 출력하시오.
SELECT ename, job, hiredate
from emp
where hiredate between '81/02/20' AND '81/05/01'
;

-- 7. 부서번호가 20 및 30에 속한 사원의 이름과 부서번호를 출력, 이름을 기준(내림차순)으로 영문자순으로 출력하시오.
select ename, deptno
from emp
where deptno=10 OR deptno=20
order by ename desc
;

--8 . 사원의 급여가 2000에서 3000사이에 포함되고 부서번호가 20 또는 30인 사원의 이름,
-- 급여와 부서번호를 출력, 이름순(오름차순)으로 출력하시오.

select ename, sal, deptno
from emp
where sal BETWEEN 2000 AND 3000 AND (deptno=20 OR deptno=30)
order by ename;

-- 9. 1981년도에 입사한 사원의 이름과 입사일을 출력하시오. (like 연산자와 와일드카드 사용)
select ename, hiredate
from emp
where hiredate LIKE '81%';

​-- 10. 관리자가 없는 사원의 이름과 담당 업무를 출력하시오.
SELECT ename, job
from emp
where mgr is null
;

-- 11. 커미션을 받을 수 있는 자격이 되는 사원의 이름, 급여, 커미션을 출력하되
-- 급여 및 커미션을 기준으로 내림차순 정렬하여 표시하시오.

SELECT ename, sal, comm
from emp
where comm is not null AND NOT comm=0
order by sal, comm desc
;


-- 12. 이름의 세번째 문자가 R인 사원의 이름을 표시하시오.

select ename
from emp
where ename LIKE '__R%'
;

-- 13. 이름에 A와 E를 모두 포함하고 있는 사원의 이름을 표시하시오.
select ename
from emp
where ename LIKE '%A%E%'
;

-- 14. 담당업무가 CLERK, 또는 SALESMAN이면서 급여가 $1600, $950 
-- 또는 $1300이 아닌 사원의 이름, 담당업무, 급여를 출력하시오.
select ename, job, sal
from emp
where sal NOT in(1600,950,1300) ANd (job='CLERK' OR job='SALESMAN')
;

-- 15. 커미션이 $500 이상인 사원의 이름과 급여 및 커미션을 출력하시오.
select ename, sal, comm
from emp
where comm>=500
;

-- 16. SUBSTR 함수를 사용하여 사원들의 입사한 년도와 입사한 달만 출력하시오.
select substr(hiredate,1,5) from emp;

-- 17. SUBSTR 함수를 사용하여 4월에 입사한 사원을 출력하시오.
select ename from emp where substr(hiredate,4,2)=04;

-- 18. MOD 함수를 사용하여 사원번호가 짝수인 사람만 출력하시오.
select empno, ename from emp where MOD(empno,2)=0;

-- 19. 입사일을 년도는 2자리(YY), 월은 숫자(MM)로 표시하고 요일은 약어 (DY)로 지정하여 출력하시오.
select ename, to_char(hiredate, 'YY-MM, DY') from emp;

-- 20. 올해 몇 칠이 지났는지 출력하시오. 현재날짜에서 올해 1월 1일을 뺀 결과를 출력하고
-- TO_DATE 함수를 사용하여 데이터 형을 일치 시키시오.
select abs(trunc(to_date('20-01-01','YY-MM-DD')-sysdate)) as "올해 지난날짜", 
to_date(sysdate,'YY-MM-DD')-to_date('20-01-01','YY-MM-DD') as "오늘-1월1일" from dual;

-- 21. 사원들의 상관 사번을 출력하되 상관이 없는 사원에 대해서는 NULL 값 대신 0으로 출력하시오.
select ename, nvl(mgr,0) as "상관사번"
from emp
;

-- 22. DECODE 함수로 직급에 따라 급여를 인상하도록 하시오.
--직급이 ‘ANALIST”인 사원은 200, ‘SALESMAN’인 사원은 180,
--‘MANAGER’인 사원은 150, ‘CLERK”인 사원은 100을 인상하시오.​
select ename, sal,
    decode(job,
    'ANALYST', sal+200,
    'SALESMAN', sal+180,
    'MANAGER', sal+150,
    'CLERK', sal+100)
    as "인상된 급여"
from emp
;
        

-- 23. 모든 사원의 급여 최고액, 최저액, 총액 및 평균 급여를 출력하시오.
-- 평균에 대해서는 정수로 반올림하시오.
select max(sal), min(sal), sum(sal), round(avg(sal))
from emp
;

-- 24. 각 담당 업무 유형별로 급여 최고액, 최저액, 총액 및 평균 액을 출력하시오.
-- 평균에 대해서는 정수로 반올림 하시오.
select job, max(sal), min(sal), sum(sal), round(avg(sal))
from emp
group by job
;

-- 25. COUNT(*) 함수를 이용하여 담당업무가 동일한 사원 수를 출력하시오.​
select job, count(*)
from emp
group by job
;

-- 26. 관리자 수를 출력하시오.
select job, count(*)
from emp
group by job
having job='MANAGER'
;

-- 27. 급여 최고액, 급여 최저액의 차액을 출력하시오.​
select max(sal)-min(sal) as "최고급여-최저급여"
from emp
;

-- 28. 직급별 사원의 최저 급여를 출력하시오.
-- 관리자를 알 수 없는 사원과 최저 급여가 2000 미만인 그룹은 제외시키고
-- 결과를 급여에 대한 내림차순으로 정렬하여 출력하시오.
select job, min(sal)
from emp
where mgr is not null
group by job
having min(sal)>2000
order by min(sal) desc
;

-- 29. 각 부서에 대해 부서번호, 사원 수, 부서 내의 모든 사원의 평균 급여를 출력하시오.
-- 평균 급여는 소수점 둘째 자리로 반올림 하시오.
select deptno, count(*), round(avg(sal),2)
from emp
group by deptno
;

-- 30. 각 부서에 대해 부서번호 이름, 지역 명, 사원 수, 부서내의 모든 사원의 평균 급여를 출력하시오.
-- 평균 급여는 정수로 반올림 하시오. DECODE 사용.​
select deptno, count(*)as "사원 수", round(avg(sal)) as "평균급여",
decode(deptno,
        '10','ACCOUNTING',
        '20','RESEARCH',
        '30','SALES',
        '40','OPERATIONS'
        ) as "부서이름", 
decode(deptno,
        '10','NEW YORK',
        '20','DALLAS',
        '30','CHICAGO',
        '40','BOSTON'
        ) as "지역 명" 
from emp
group by deptno
;

-- 31. 업무를 표시한 다음 해당 업무에 대해 부서 번호별 급여 및 부서 10, 20, 30의 급여 총액을 각각 출력하시오.
-- 별칭은 각 job, dno, 부서 10, 부서 20, 부서 30, 총액으로 지정하시오.
select job, deptno as "dno",
decode(deptno,'10',sum(sal)) as "부서10",
decode(deptno,'20',sum(sal)) as "부서20",
decode(deptno,'30',sum(sal)) as "부서30",
sum(sal) as "총액"
from emp
group by job, deptno
;

요청한 작업을 수행하는 중 오류 발생:

ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-553: character set name is not recognized
06552. 00000 -  "PL/SQL: %s"
*Cause:    
*Action:
업체 코드 6552


@ 조치내역

C:\>sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.2.0 Production on 湲?3??14 23:49:54 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

SQL> update sys.props$ set value$ = 'AL32UTF8' where name = 'NLS_CHARACTERSET'
 2  ;

1 row updated.

SQL> update props$ set value$='AL32UTF8' where name='NLS_NCHAR_CHARACTERSET';

1 row updated.

SQL> select * from sys.props$ where name='NLS_NCHAR_CHARACTERSET';

NAME
--------------------------------------------------------------------------------
VALUE$
--------------------------------------------------------------------------------
COMMENT$
--------------------------------------------------------------------------------
NLS_NCHAR_CHARACTERSET
AL32UTF8
NCHAR Character set


SQL> commit;

Commit complete.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup;
ORACLE instance started.

Total System Global Area  644468736 bytes
Fixed Size                  1385488 bytes
Variable Size             327158768 bytes
Database Buffers          310378496 bytes
Redo Buffers                5545984 bytes
Database mounted.
Database opened.
SQL>

Oracle SQL Developer에서 재시도 -> 테이블 생성 및 성공

-- 그룹함수 : 하나의 행의 컬럼을 대상이 아닌 행의 그룹의 컬럼들을 묶어 그룹화하고 연산하는 함수
-- SUM, AVG, COUNT, MAX, MIN

select 
    sum(sal) as "월 급여 총액",
    to_char(sum(sal), 'L99,999') as "월 급여 총액2",
    round(avg(sal)) as "월 급여 평균",
    count(*) as "사원 수",
    count(comm) as "커미션을 받는 사원수", -- null은 세지 않는다. 0은 셈
    max(sal) as "가장 큰 급여"
from emp
;

select
    sum(comm),
    avg(comm),
    count(comm),
    max(comm),
    min(comm)    
from emp
where comm is not null and comm>0
;

-- 전체행을 구한다
select count(*) as from emp; -- 전체 사원의 수
select count(*) as from emp where job='MANAGER'; -- 매니저의 수
-- 매니저의 수, 평균급여, 최고급여
select count(*), avg(sal), max(sal) from emp where job='MANAGER';

-- 직무의 개수
select count(distinct job) -- 중복제거
from emp
;

------------- 특정 컬럼을 기준으로 그룹핑: Group by 컬럼

select deptno
from emp
group by deptno --order by deptno와 다르다.
;


-- 부서별 평균 월급
select deptno, avg(sal)
from emp
group by deptno
;


-- 각 부서별 인원을 부서별 인원이 많은 수대로 정렬
select deptno, count(*)
from emp
group by deptno
order by count(*) desc
;

-- 각 부서별 인원 및 커미션을 받는 사람.
select deptno, count(*)as "부서인원", count(comm) as"커미션받는사람 수"
from emp
-- where comm is not null and comm>0
group by deptno
;

------------------  HAVING : 그룹함수에 조건/ 그룹의 결과를 제한
-- 부서별로 그룹지은 후(GROUP BY), 그룹 지어진 부서별 평균 급여가 2000 이상인(HAVING) 
-- 부서번호와 부서별 평균 급여를 출력하는 경우
select deptno, round(avg(sal))
from emp
GROUP by deptno
HAVING avg(sal)>=2000  
-- having not avg(sal)>=2000
;

-- 부서별 급여의 최대값과 최소값을 구하되
-- 최대 급여가 2900이상인 부서만 출력합니다.
select deptno, max(sal), min(sal)
from emp
group by deptno
having max(sal)>=2900
order by deptno
;

 
-- 직무별 지표 : 사원의 수, 급여의 총합, 평균 급여, 최대최소
select job, count(*) || '명' as "사원 수",
            to_char(sum(sal)*1100, 'L99,999,999') as "급여 총합",
            to_char((avg(sal))*1100, 'L99,999,999') as "급여 평균", 
            to_char(max(sal)*1100, 'L99,999,999')as "최대 급여", 
            to_char(min(sal)*1100, 'L99,999,999') as "최소 급여"
from emp
where job != 'PRESIDENT'  
GROUP by job
order by job
;