1월 8일 국비교육 28일차

#1

Oracle의 ojdbc14.jar파일을

C:\Program Files\Java\jdk1.8.0_192\jre\lib\ext

C:\Program Files\Java\jre1.8.0_192\lib\ext

에 붙여놓기

#2

C:\oraclexe\app\oracle\product\10.2.0\server\NETWORK\ADMIN의 sqlnet.ora파일은 손대지 않는다.

listener.ora, tnsnames.ora만 건든다

#3

tnsnames.ora와 OracleServiceXE는 관련이 있다

#4

C:\oraclexe\app\oracle\product\10.2.0\server\RDBMS\ADMIN에 있는 scott.sql 예제파일을 샘플로 사용한다.

DROP TABLE DEPT;

CREATE TABLE DEPT

       (DEPTNO NUMBER(2), 

DNAME VARCHAR2(14) ,

LOC VARCHAR2(13) ) ;

위에 있는 문장 다 지워주고 Constraint를 지운다

#5

DDL(Data Definition Language) : 데이터 정의 언어, 데이터베이스 개체의 생성/변경/삭제를 목적으로 사용하는 언어

1. Create : 데이터베이스, 테이블, 뷰, 스토어드 프로시저 등의 생성

2. Alter : 데이터베이스, 테이블, 뷰, 스토어드 프로시저 등의 수정

3. Drop : 데이터베이스, 테이블, 뷰, 스토어드 프로시저 등의 삭제

DCL(Data Control Language) : 데이터 제어 언어, 데이터 제어를 정의하고 기술하는 언어, 누가 데이터를 제어할 수 있는지를 지정하는 언어

1. Grant : 허락, 특정 사용자에게 데이터 조작을 허락

2. Deny : 거부, 특정 사용자에게 데이터 조작을 거부

3. Revoke : 허락/거부 권한을 빼앗아 온다.

DML(Data Manipulation Language) : 데이터 조작 언어, 데이터 자체를 처리하고 조작하기 위한 언어

1. Select : 선택, 테이블로부터 데이터를 불러온다.

2. Update : 갱신, 테이블에 있는 데이터를 갱신

3. Insert : 삽입, 테이블에 새로운 데이터를 삽입

4. Delete : 삭제, 테이블에 있는 데이터를 삭제

#6

DML  - SELECT 기본 형식

SLECT SELECT_LIST OR (*)

FROM TABLE_NAME

특정 테이블(TABLE_NAME)으로부터 특정 데이터(SELECT_LIST)를 불러와서 보여준다.

#7

ⓐ 사원 테이블의 전체 내용을 출력하자

SELECT *

FROM EMP;

ⓑ 사원테이블에서 사원의 번호, 사원의 이름을 출력하자

SELECT EMPNO, ENAME

FROM EMP;

ⓒ 사원테이블에서 사번, 사원의 이름을 봉급을 출력하자

SELECT EMPNO, ENAME, SAL

FROM EMP;

ⓓ 사원테이블에서 사원의 이름과 부서 번호를 출력하자.

SELECT ENAME, DEPTNO

FROM EMP;

ⓔ 사원테이블에서 사원의 이름과 매니저, 봉급을 출력하자.

SELECT ENAME, MGR, SAL

FROM EMP;

ⓕ 부서테이블의 모든 내용을 출력하자.

SELECT *

FROM DEPT;

ⓖ 사원테이블과 부서테이블의 모든 내용을 출력하자

SELECT * 

FROM EMP, DEPT;

ⓗ 사원테이블에서 사원의 이름과 봉급 커미션을 출력해보자.

SELECT ENAME, SAL, COMM

FROM EMP;

ⓘ 사원테이블에서 별칭을 주자.

SELECT ENAME AS "이름"

FROM EMP;

SELECT ENAME 이름

FROM EMP;

SELECT ENAME AS 이름

FROM EMP;

SELECT ENAME  "이   름"

FROM EMP;

모두 가능한 케이스임

ⓙ 사원이름, 봉급, 매니저라고 출력해보자

SELECT ENAME AS 사원이름, SAL 봉급, MGR 매니저

FROM EMP;

ⓚ + ||

사원의 이름 님이라고 출력해보자.

SELECT ENAME||'님'

FROM EMP;

EX) 000님은 000에 입사를 하고 000의 봉급을 받습니다.

SELECT ENAME||'님은' || HIREDATE||'에 입사를 하고 ' || SAL ||'의 봉급을 받습니다'

FROM EMP;

ⓛ 중복데이터 제거 DISTINCT

SELECT DISTINCT MGR FROM EMP;

ⓜ 사원테이블에서 사원의 이름, 봉급, 연봉을 출력해보자.

SELECT ENAME, SAL, (SAL*12+NVL(COMM, 0))

FROM EMP;

ⓝ 사원테이블에서 사원의 이름, 봉급, 연봉을 출력하자(단, 연봉은 (봉급 +커미션)*12)

SELECT ENAME, COMM, (SAL + NVL(COMM, 0))* 12

FROM EMP;

ⓞ NVL(대상컬럼, 초기값) : 대상컬럼이 NULL인 경우에 초기값으로 대입된다.

SELECT NVL(COMM, 0)

FROM EMP;

SELECT NVL(COMM, SAL)도 가능 -> NULL을 SAL로 대체

ⓟ 테이블의 별칭

사원테이블에서 사원의 이름, 부서번호, 부서명을 출력해보자

SELECT ENAME, DEPTNO, DNAME

FROM EMP, DEPT;

이렇게 입력하면 

1행에 오류:

ORA-00918: 열의 정의가 애매합니다

이런 오류가 뜸

SELECT ENAME, EMP.DEPTNO, DNAME

FROM EMP, DEPT;

SELECT E.ENAME, E.DEPINO, D.DNAME

FROM EMP E, DEPT D;

SELECT 사원.ENAME, 사원.DEPTNO, 부서.DNAME

FROM EMP 사원, DEPT 부서;

#8

연산자

1. 산술연산자 : ( + , - , * , / , ( ) )

2. 비교연산자 : ( =, > , < , >=, <=, != or <>)

3. 논리연산자 : AND, OR, NOT

WHERE 절 : 조건절

실행순서 : FROM, WHERE, SELECT

SELECT SELECT_LIST ---3

FROM TABLE        ----1

WHERE              -----2

Q1. 사원테이블에서 사원번호가 '7788'인 사원의 사원번호, 이름, 월급을 출력하라

SELECT EMPNO, ENAME, SAL

FROM EMP

WHERE EMPNO = 7788;

Q2. 사원테이블에서 'SMITH'의 사원번호, 이름, 월급을 출력하라.

SELECT EMPNO, ENAME, SAL

FROM EMP

WHERE ENAME = SMITH;

Q3. 사원테이블에서 입사일이 1980년 12월 17일인 사원의 모든 데이터를 출력하라.

SELECT *

FROM EMP

WHERE HIREDATE = '1980-12-17'; (' ' 을 안붙이면 NUMBER로 인식하기에 오류 뜸)

SELECT *

FROM EMP

WHERE HIREDATE = '80/12/17'; 도 가능

SELECT *

FROM EMP

WHERE HIREDATE = '80-12-17'

SELECT *
FROM EMP

WHERE HIREDATE = '1980/12/17'도 가능

Q4. 직업이 'SALESMAN'인 사원의 이름, 월급, 직업을 출력하라.

SELECT ENAME, SAL, JOB

FROM EMP

WHERE JOB = 'SALESMAN';

Q5. 부서번호가 10인 사원의 부서번호, 이름, 월급을 출력하라.

SELECT DEPTNO, ENAME, SAL

FROM EMP

WHERE DEPTNO = '10';

#9

비교연산자

Q1. 사원테이블에서 월급이 3000 이상인 사원의 이름과 월급을 구하자

SELECT ENAME, SAL

FROM EMP

WHERE SAL>= 3000;

Q2. 사원테이블에서 직위가 'SALESMAN'인 사원이 아닌 사원의 이름과 직업을 출력하자.

SELECT ENAME, JOB

FROM EMP

WHERE JOB != 'SALESMAN';

Q3. 사원테이블에서 연봉이 20000이하인 사원의 이름과 연봉을 출력하자.

SELECT ENAME, (SAL*12 + NVL(COMM, 0)), (SAL*12 + NVL(COMM,0))

FROM EMP

WHERE (SAL*12 + NVL(COMM,0)) <= 20000;

Q4. 직업이 'SALESMAN'이고 월급이 2000이상인 사원의 이름과 월급을 출력하자.

SELECT ENAME, SAL

FROM EMP

WHERE JOB = 'SALESMAN' AND SAL >= 2000;

Q5. 사원테이블에서 월급이 1000이상 3000 이하인 사원의 이름과 월급을 출력하자.

SELECT ENAME, SAL

FROM EMP

WHERE SAL >= 1000 AND SAL <= 3000;

SELECT ENAME, SAL

FROM EMP

WHERE SAL BETWEEN 1000 AND 3000;

IN : 주어진 값이 하위쿼리(서브쿼리) 또는 임의의 값과 일치하는지 확인

Q6. 사원테이블에서 사원번호가, 7788, 7902, 7369인 사원의 번호와 이름을 출력해보자.

SELECT EMPNO, ENAME

FROM EMP

WHERE EMPNO IN(7788,7902,7369);

Q7. 사원테이블에서 커미션이 책정되지 않는 사원의 이름과 봉급, 커미션을 출력하시오

SELECT ENAME, SAL, COMM

FROM EMP

WHERE NVL(COMM,0) = 0;

OR

SELECT ENAME, SAL, COMM

FROM EMP

WHERE COMM IS NULL; // ==NULL은 안됨. NVL(COMM, 0)과는 결과가 다름 

Q8. 사원테이블에서 커미션이 책정된 사원의 이름과 봉급, 커미션을 출력하시오.

SELECT ENAME, SAL, COMM

FROM EMP

WHERE NVL(COMM, 0) != 0;

--10. 사원테이블에서 이름의 첫글자가 B이고 마지막 글자가 N이 아닌 사원의

-- 이름을 출력하라

SELECT ENAME

FROM EMP

WHERE ENAME LIKE 'B%' AND ENAME NOT LIKE '%N';

#10

DESC : 구조보기

기본 자료형 

숫자 NUMBER(N), NUMBER(M, N)

문자 CHAR, VARCHAR, VARCHAR2

날짜 DATE

정렬

SELECT

FROM

WHERE

ORDER BY

#11

사원테이블에서 사원의 이름, 사원의 번호, 부서번호를 출력하자

단 이름을 역순으로 출력하자

SELECT ENAME, EMPNO, DEPTNO

FROM EMP

ORDER BY ENAME DESC;

SELECT ENAME, EMPNO, DEPTNO

FROM EMP

ORDER BY 1 DESC;

#12

GROUP

개수, 합, 평균, 최대값, 최소값, 중간값

COUNT(*)

COUNT(컬럼명);

SELCT COUNT(*)

FROM EMP;

항상 오라클에서는 NULL값을 주의해야 한다!

#13

Q1. 사원테이블에서 평균 월급을 구하라.

SELECT AVG(SAL) 평균월급

FROM EMP;

Q2. 부서번호가 10번인 사원의 평균 월급을 구하라

SELECT AVG(SAL)

FROM EMP

WHERE DEPTNO = 10;

Q3. 직업이 'SALESMAN'인 사원의 평균 월급을 구하라

SELECT AVG(SAL)

FROM EMP

WHERE JOB = 'SALESMAN';

#14

Q1. 부서별 평균 월급을 구하라.

SELECT

FROM

WHERE

GROUP BY

ORDER BY

SELECT AVG(SAL)

FROM EMP

GROUP BY DEPTNO;

Q2. 직업별 사원의 월급의 합을 구하라

Q3. 직업별 사원의 월급의 합을 구하라. 단 월급의 합이 4000이상만 출력하자.

SELECT SUM(SAL)

FROM EMP

HAVING SUM(SAL) >= 4000

GROUP BY JOB;

SELECT

FROM 

WHERE 

HAVING

GROUP BY

ORDER BY

#15

ROLLUP : ROLLUP 연산자는 GROUP BY 문과 함께 사용되며 GROUP BY 문에서 명시한 컬럼 순서대로 추가적인 요약정보를 단계적으로 만들어준다.