봄이여.ora

1. Vi 편집기 

세가지 모드(상태)가 있다

1. 명령모드(esc 상태)

2. 입력모드(편집상태)- 편집기에서 i키 누르면 insert 표시됨

3  명령모드(Last Line Mode)

- 실행하면 처음상태가 명령모드이다.

- 소문자 i 입력모드 insert모드 편집가능한상태, esc insert모드해제 커맨드가능한상태

 실습(임의의 파일을 만들어서 연습해보장)

PGA(Program Global Area)

- 데이터베이스에 접속하는 모든 유저에게 할당되는 각각의 서버프로세스가 독자적으로 사용하는 오라클 메모리 영역

파싱

1. SQL을 실행하면 제일먼저 SQL 파서(parser)가 SQL 문장에 문법적 오류가 없는지 검사(Syntax 검사)
2. 문법적 오류가 없다면 의미상 오류가 없는지 검사(Semantic 검사, 오브젝트 존재유무등)
3. 검사를 다 마치면, 사용자가 발생한 SQL과 그 실행계획이 라이브러리캐시(프로시저캐시)에 캐싱되어 있는지 확인
4. 캐싱되어 있다면 소프트파싱, 캐싱되어있지 않다면 하드파싱

SGA(System Global Area)

- 오라클이 데이터를 읽거나 변경하기 위해 사용하는 공용 메모리 영역

LRU 알고리즘 : 최근에 가장 적게 사용한 메모리 공간을 필요시 가장 먼저 사용하는 오라클 메모리 관리기법

1.EMP테이블과 같은 구조, 같은 데이터를 갖는 테이블 REEMP 테이블을 생성하고 ,테이블의 구조와 데이터를 확인하여라.

CREATE TABLE REEMP 

AS 

SELECT * FROM EMP;

DESC REEMP

2.DEPT 테이블과 같은 구조, 같은 데이터를 갖는 테이블 REDEPT테이블을 생성하고, 테이블의 구조와 데이터를 확인하여라.

CREATE TABLE REDEPT 

AS SELECT * FROM DEPT;

DESC REDEPT

3.REEMP 테이블을 토대로 EMPNO,ENAME,DEPTNO를 포함하는 뷰 REEMP_VIEW를 작성하여라.

CREATE OR REPLACE VIEW REEMP_VIEW AS SELECT EMPNO,ENAME,DEPTNO FROM REEMP;

1) 뷰 REEMP_VIEW의 구조를 확인하여라.

DESC REEMP_VIEW

2) 뷰 REEMP_VIEW의 데이터를 확인하여라.

SELECT * FROM REEMP_VIEW;

3) 뷰 REEMP_VIEW의 EMPNO의 데이터를 EMPNO+100으로 변경하여라.

UPDATE REEMP_VIEW SET EMPNO=EMPNO+100;

4) 뷰 EMPNO의값이 변경되었는지를 REEMP_VIEW에서 확인하여라.

100씩 추가되었다.

5) 뷰 EMPNO의 값이 변경되었는지를 REEMP테이블에서 확인하여라.

100씩 추가되었다.

6) 데이터사전을 이용해서 생성된뷰 REEMP_VIEW의 이름과 생성구문을 확인하여라.

SELECT * FROM USER_VIEWS; 

4.REEMP 테이블로부터 DEPTNO=30인 사원들로만 된 REEMP_VIEW30을 만들어라. 단 DEPTNO의 값은 변경될 수 없도록 하여라.

CREATE OR REPLACE VIEW REEMP_VIEW30 

AS 

SELECT * FROM REEMP 

WHERE DEPTNO = 30 

WITH CHECK OPTION;

1)뷰 REEMP_VIEW30의 구조를 확인하여라.

DESC REEMP_VIEW30;

2) 뷰REEMP_VIEW30의 데이터를 확인하여라.

SELECT * FROM REEMP_VIEW30;

3)뷰 REEMP_VIEW30 에서 DEPTNO가 30인것을 10으로 바꾸어라, 바꿀수없다면 그 이유에 대해 설명하여라.

UPDATE REEMP_VIEW30 

SET DEPTNO=10 WHERE DEPTNO =30;

뷰의 WITH CHECK OPTION의 조건에 위배되기 때문에 바꿀수없다.

4) 데이터 사전을 이용해서 생성된 뷰 REEMP_VIEW30의 이름과 생성 구문을 확인하여라.

SELECT * FROM USER_VIEWS; 

5.REEMP 테이블과 REDEPT테이블로부터 EMPNO,ENAME,DEPTNO,DNAME을 포함하는 뷰 E_D_VIEW를 작성하여라.

CREATE OR REPLACE VIEW E_D_VIEW 

AS 

SELECT E.EMPNO,E.ENAME,E.DEPTNO,D.DNAME 

FROM REEMP E, REDEPT D 

WHERE E.DEPTNO =D.DEPTNO;

1)뷰 E_D_VIEW의 구조를 확인하여라.

DESC E_D_VIEW 

2)뷰 E_D_VIEW의 모든 데이터를 확인하여라.

SELECT * FROM E_D_VIEW;

3)뷰 E_D_VIEW에 하나의 데이터(9000,'KIM',30,'MARKETING')를 추가하여라.만약 추가할 수 없다면 그 이유에 대해 설명하여라.

INSERT INTO E_D_VIEW(EMPNO,ENAME,DEPTNO,DNAME) VALUES(9000,'KIM',30,'MARKETING');

추가할수없다.

유니크한 키가없기때문에?

4)데이터 사전을 이용해서 생성된 뷰 E_D_VIEW의 이름과 생성구문을 확인하여라. 

SELECT * FROM USER_VIEWS; 

1)함수의 정의 

함수란 하나 이상의 매개변수(인수:Argument)를 전달받아 처리해서 그 결과를 반환해주는 프로그램을 말한다.

2)함수의 기능

-데이터에 관한 계산을 수행한다.

-데이터를 다른 형태로 변환한다.

-데이터 그룹 결과를 출력한다.

3)단일행 함수의 종류

1. LOWER 함수

-인수로 받아들인 문자열을 모두 소문자로 변환시켜 결과를 나타낸다.

(1). 오라클 설치시 비밀번호를 변경한 경우

[시작] - [모든프로그램] - [Oracle-OraDb11g_home1] - [응용프로그램 개발] - [SQL Plus] 를 시작한다.

사용자명:scott 비밀번호:tiger 를 입력한다.

화면처럼나온다면 접속성공

(2). 오라클 설치시 비밀번호를 변경하지 않은 경우

사용자명 :system 비밀번호:manager 를 입력한다.

접속성공

alter user scott account unlock;

잠겨있는 scott 계정을 풀어준다.

alter user scott identified by tiger;

비밀번호를 변경해준다.

종료시에는 프롬프트에서 "exit"를 입력하면 오라클이 종료된다.

SQL Plus 속성 - 대상(T)에서 경로마지막에 사용자명과 비민번호를 입력해주면 

매번 SQL Plus에서 "conn scott/tiger"를 입력하지않아도 자동으로 로그인해준다.

http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html 에 접속한후

자신의 컴퓨터의 사양에맞게 File 1,File 2를 다운받는다.

//다운받을때 폴더이름을 한글로 하면 설치가 되지않는다.

 내 노트북사양은 윈도우7 64bit이다.

다운받은 2개의 파일 압축을 풀어준다.

"win64_11gR2_database_2of2\database\stage\Components" 경로에 있는 폴더를 모두 복사한다.

"win64_11gR2_database_1of2\database\stage\Components" 경로에 복사한 파일을  붙여넣는다.

"win64_11gR2_database_1of2\database" 폴더에 있는 setup 파일을 실행한다.

설치가 시작되는 화면이다,

보안갱신구성화면이 나타나면 파일 다운받을때 사용했던 계정을 입력하면되고 사용하기 싫으면 입력 안해도된다.

굳이 필요성을 느끼지못해 나는 입력하진 않았다.

"예" 를 눌러준다.

데이터베이스 생성 및 구성을 체크해주고 [다음]버튼을 누른다.

데스크톱 클래스를 체크해주고 [다음]버튼을 누른다.

원하는 경로를 설정해주고 관리비밀번호를 입력한다.

비밀번호가 권장표준을 따르지 않아도 설정이 가는하지만 ,

이 암호는 SYS, SYSTEM,SYSMAN,DBSNMP 계정을 사용하기 위해 필요하므로 기억해야한다.

"예" 버튼을 눌르고 [다음]버튼을 눌러준다.

요약화면이 나타나면 확인후 [완료]버튼을 누른다.

제품설치가 진행되는 화면이다.

설치진행과정에서 Database Configration Assistant 화면이 나타난다.

Configration Assistant 유틸리티는 오라클에 포함된 데이터베이스 관리 유틸리티 중의 하나로, 

데이터베이스를 생성하는데 사용된다.

Configration Assistant 유틸리티 설치가 완성된후 [비밀번호 관리]를 눌러준다.

완료 화면이 나오면 [닫기]버튼을 누른다.

설치 끝~

1.DEPT학번c 테이블을 다음과 같이 변경하여라.

1)DEPT학번c 테이블의 구조를 확인하여라.

DESC DEPT학번c

2)테이블 이름을 D학번 으로 변경하여라.

RENAME DEPT학번c TO D학번; 

3)D학번 테이블의 구조를 확인하여라.

DESC D학번

4)NAME 칼럼에 NOT NULL 제약조건을 추가하고 테이블의 구조를 확인하여라.

ALTER TABLE D학번 MODIFY NAME VARCHAR2(10) CONSTRAINT D학번_NAME_nn NOT NULL; 

5)regionid 칼럼을 NUM(5)로 추가하고 테이블의 구조를 확인하여라.

ALTER TABLE D학번 ADD regionid NUMBER(5);  

DESC D학번

6)D학번 테이블의 제약조건을 확인하여라.

SELECT TABLE_NAME, CONSTRAINT_NAME,STATUS FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'D학번';

2.EMP학번c 테이블을 다음과 같이 변경하여라.

1) EMP학번c 테이블의 구조를 확인하여라.

DESC EMP학번c 

2) 테이블의 이름을 E학번 으로 변경하여라.

RENAME EMP학번c TO E학번; 

3) E학번 테이블의 구조를 확인하여라.

DESC E학번

4) managerid 칼럼을 NUM(7) NOT NULL 제약조건으로 추가하고 테이블의 구조를 확인하여라.

ALTER TABLE E학번 ADD managerid NUMBER(7) CONSTRAINT E학번_managerid_nn NOT NULL;  

DESC E학번 

5) ID 칼럼의 길이를 NUM(10)으로 하고 테이블의 구조를 확인하여라.

ALTER TABLE E학번 MODIFY ID NUMBER(10);

DESC E학번 

6) LNAME 칼럼의 NOT NULL 제약조건을 삭제하고 테이블의 구조를 확인하여라.

ALTER TABLE E학번 DROP CONSTRAINT EMP학번c_LNAME_nn; 

7)E학번 테이블의 managerid 칼럼에 D학번 테이블의 NO 칼럼을 참조하는 FOREIGN KEY 제약조건을 추가하여라.

ALTER TABLE E학번 

ADD CONSTRAINT  E학번_managerid_fk FOREIGN KEY(managerid) REFERENCES D학번(NO);

8) E학번 테이블의 제약조건을 확인하여라.

SELECT TABLE_NAME, CONSTRAINT_NAME,STATUS FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'E학번';

1.다음테이블을 생성하여라.

테이블이름 :EMP학번c(칼럼레벨),EMP학번t(테이블레벨)

테이블이름 :DEPT학번c(칼럼레벨),DEPT학번t(테이블레벨)

1. 10번 부서에 대해 급여의 평균 값,최대 값,최소 값,인원 수를 구하여 출력하여라.

SELECT AVG(sal),MAX(sal),MIN(sal),COUNT(empno) FROM emp WHERE deptno = 10;

2. 각 부서별 같은 직무를 갖는 사원의 인원수를 구하여 부서 번호, 직무,인원수를 출력하여라.

SELECT deptno,job,COUNT(empno) FROM emp GROUP BY deptno,job;

3. 사원들의 직무별 평균 급여와 최고급여,최저급여를 평균급여에 대해 오름차순으로 정렬하여라.

SELECT AVG(sal),MAX(sal),MIN(sal) FROM emp GROUP BY job ORDER BY AVG(sal) ASC;

4. EMP와 DEPT테이블을 조인하여 모든 사원에 대해 부서 번호, 부서이름, 사원이름, 급여를 출력하여라.

SELECT e.ename,e.sal,e.deptno,d.dname FROM emp e, dept d WHERE e.deptno = d.deptno ;

5. 이름이 'ALLEN'인 사원의 부서 이름을 출력하여라.

SELECT d.dname FROM  emp e,dept d WHERE e.deptno =d.deptno AND e.ename = (SELECT ename FROM emp WHERE ename = 'ALLEN');

6. 'ALLEN'과 직무가 같은 사원의 이름,부서 이름, 급여, 부서위치를 출력하여라.

SELECT e.ename, d.dname, e.sal,d.loc FROM emp e,dept d WHERE e.deptno =d.deptno AND e.job = (select job from emp where ename ='ALLEN');

7. 전체사원의 평균 급여보다 급여가 많은 사람의 사원 번호,이름, 부서이름,입사일,부서위치,급여를 출력하여라.

select e.empno,e.ename,d.dname,e.hiredate,d.loc,e.sal from emp e, dept d where e.deptno =d.deptno and e.sal > (select avg(sal) from emp);

8. 10번 부서 사원들 중에서 20번 부서의 사원과 같은 직무를 갖는 사원의 사원 번호 ,이름,부서 이름, 입사일, 부서 위치를 출력하여라.

select e.empno,d.dname,e.hiredate,d.loc from emp e,dept d where e.deptno =d.deptno and e.deptno=10 and e.job in (select job from emp where deptno =20);

9. 'ALLEN'의 급여보다 많고 'SCOTT'의 급여보다 적은 사원의 사원번호,이름,부서이름,부서위치,급여를 급여가 많은 순으로 출력하여라.

select e.empno,e.ename,d.dname,d.loc,e.sal from emp e, dept d where e.deptno = d.deptno and sal >(select sal from emp where  ename ='ALLEN') and sal <(select sal from emp where  ename ='SCOTT');

10. 30번 부서의 최고급여보다 급여가 많은 사원의 사원 번호 ,이름,급여를 출력하여라.

select empno,ename,sal from emp where sal > (select max(sal) from emp where deptno ='30');

11. 사원 이름의 두 번째 글자가 'A'인 사원들의 수를 출력하여라.

select count(*) from emp where ename like '_A%';

12. 사원들의 평균 급여,총 급여, 최고급여액,최저 급여액을 구하여라.

select avg(sal),sum(sal),max(sal),min(sal) from emp;

13. 평균급여가  1500이 넘는  직무와 평균 급여를 구하여라.

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

14.사원들의 이름,부서 이름, 급여를 출력하여라.

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

15. 각 부서별 부서 번호(모든 부서에 대해서),부서 이름,부서 위치,사원의 수를 출력하여라.

select d.deptno,d.dname,d.loc,count(*) from emp e, dept d where e.deptno =d.deptno group by d.deptno,d.dname,d.loc

16.모든 사원들의 평균 급여보다 많이 받는 사원들의 사원 번호와 이름을 출력하여라.

select empno,ename from emp where sal > (select avg(sal) from emp);

17.'FORD'와 부서가 같은 사원들의 이름,부서 이름, 직무, 급여를 출력하여라.

select e.ename,d.dname,e.job,e.sal from emp e, dept d where e.deptno =d.deptno  and job = (select job from emp where ename ='FORD'); 

18.부서이름이 'SALES'인 사원들의 평균 급여보다 많고, 부서 이름이 'RESEARCH'인 사원들의 평균 급여보다 적은 사원들의 이름, 부서번호, 급여, 직무를 출력하여라.

select ename,sal,job from emp where sal > (select avg(e.sal) from emp e,dept d where e.deptno =d.deptno and d.dname ='SALES')

and  sal < (select avg(e.sal) from emp e,dept d where e.deptno =d.deptno and d.dname ='RESEARCH');

19.부서별 평균 급여가 1000보다 적은 부서 사원들의 부서 번호를 출력하여라.

select deptno from emp group by deptno having avg(sal) <1000;

20. 직무가 'ANALYST'인 사원보다 급여가 적으면서, 직무가 'ANALYST'가 아닌 사원들의 사원번호,이름, 직무, 급여를 출력하여라.

select deptno,ename,job,sal from emp where sal < (select sal from emp where job ='ANALYST')

and job != 'ANALYST';-- 모름