[제어판] -> [시스템 및 보안] -> [관리도구] -> [서비스]

tnsnames.ora 내에서 우리가 필요한 부분

XE =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = kitri-PC)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = XE)

)

)

샘플 SQL이 저장된 경로

C:\oraclexe\app\oracle\product\10.2.0\server\RDBMS\ADMIN

접속

http://127.0.0.1:8080/apex/

테이블 - Entity : 컬럼 네임

==================================

name kor eng mat

==================================

kim 90 50 90

lee 80 40 80

JI 70 50 70

[쿼리파일 불러오는 방법]

select * from tab; -> 생성된 테이블 확인하는 방법

====================================================

DB - Server, Client / Program : PL-SQL / xml 맵핑, 분석코드

계정 권한 / sys, sysdba(ID, PW), system 등

SID(XE) : 기본 DB의 영역, 작업공간, 메모리, 임시기억장소 등

Create(crud), Table(crud)

Read

Updata

Delete

====================================================

host help - 도스 모드 도움말

SAVE A.SQL -> 내문서에 저장됨

HELP SET -> 커멘드창에서 구현할 수 있는 명령어

DESC 테이블명 -> 테이블의 구조를 본다

VARCHAR2 - 4000자까지 가능하다

1. 생성된 Table 안에 자료를 추가한 후, 자료의 속성을 통해서 Read하는 구문

- 테이블의 구조를 확인한다. DESC 테이블명;

* 속성을 이용한 구문 처리

SELECT 칼럼리스트,,,,

FROM 테이블명;

Q1) 사원 테이블의 모든 내용을 보자.

SELECT * FROM EMP;

Q2) 사원 테이블의 사원의 이름과 직업을 출력하자.

Q3) 사원 테이블의 사원의 이름과 직업 월급을 출력하자.

Q4) 사원 테이블의 사원의 이름과 사번, 부서번호를 출력하자.

Q5) 부서테이블의 구조를 보자.

DESC DEPT;

Q6) 부서테이블의 전체 내용을 보자.

SELECT * FROM DEPT;

Q7) 사원테이블과 부서테이블의 내용을 보자.

SELECT * FROM EMP, DEPT;

Q8) 컬럼의 별칭을 줄 수 있다.

SELECT 칼럼명 별칭 FROM EMP; (오라클에서, 오라클에서 문자열은 ‘홍길동’ VARCHAR2)

SELECT 칼럼명 AS “별칭” EMP; (다른 DB)

사원테이블의 ENAME을 사원이름이라고 출력해보자.

SELECT ENAME 사원이름 FROM EMP;

Q9) 사원테이블에서 사원번호, 봉급, 부서번호 라고 출력하자.

SELECT EMPNO 사원번호, SAL 봉급, EMPNO 부서번호 FROM EMP;

Q10) SELECT EMP.ENAME, DEPT.DNAME, EMP.DEPTNO FROM EMP, DEPT;

SELECT E.ENAME, D.NAME, E.DEPTNO FROM EMP E, DEPT D;

Q11) 연결문자열 ||

SELECT ENAME || '님‘ FROM EMP;

데이터 뒤에 님이 붙는다

SELECT ENAME || '님의 월급은 ' || SAL || '이고 입사일은 ' || HIREDATE || '입니다.' FROM EMP;

FROM 뒤에가 먼저 수행되고 SELECT가 나중에 수행된다.

COL 컬럼명 FOR A 숫자크기; // 문자

COL 컬럼명 FOR 999; // 숫자

컬럼에 산술 연산, 논리, 비교 등을 할 수 있다.

Q12) 사원의 이름과, 월급, 연봉을 출력해보자.

SELECT ENAME, SAL, SAL*12 AS 연봉 FROM EMP;

Q13) 사원의 이름과 커미션을 출력해보자.

SELECT ENAME, COMM FROM EMP;

Q14) 봉급을 구하되 월급 + 커미션

이름 월급 커미션 봉급

SELECT ENAME 이름, SAL 월급, COMM 커미션, SAL+COM 봉급 FROM EMP;

NULL값은 연산이 안된다!

해결방안? -> NVL(컬럼명, 대처값)

SELECT SAL, NVL (SAL, 0) FROM EMP;

SELECT SAL, NVL(COMM, SAL) FROM EMP;

SELECT ENAME 이름, SAL 월급, COMM 커미션, SAL+NVL(COMM, 0) 봉급 FROM EMP;

[결과]

[조건부]

SELECT FROM WHERE 컬럼과 비교 연산

Q15) 사원 테이블에서 월급을 2000 이상 받는 사원을 출력하자.

SELECT * FROM EMP WHERE SAL>=2000;

Q16) 사원 테이블에서 부서번호가 10인 사원의 이름과 부서번호를 출력하자.

SELECT ENAME, DEPTNO FROM EMP WHERE DEPTNO=10;

Q17) 사원 테이블에서 입사년도가 82년 이후에 입사한 사원의 이름과 입사년도

SELECT ENAME, HIREDATE FROM EMP WHERE HIREDATE>'82/01/01';

SELECT ENAME, HIREDATE FROM EMP WHERE HIREDATE>'82-01-01';

SELECT ENAME, HIREDATE FROM EMP WHERE HIREDATE>'1982-01-01';

Q18) 사원의 봉급이 2000에서 4000 사이의 사원의 이름과 봉급을 출력하세요.

SELECT ENAME, SAL FROM EMP WHERE (SAL>=2000 AND SAL<=4000);

SELECT ENAME, SAL FROM EMP WHERE SAL BETWEEN 2000 AND 4000;

Q19) 사번이 7521, 7566, 7788인 사원의 사번과 이름을 출력하자.

SELECT EMPNO, ENAME FROM EMP WHERE EMPNO= 7521 OR EMPNO=7566 OR EMPNO=7588;

SELECT EMPNO, ENAME FROM EMP WHERE EMPNO IN(7521, 7566, 7588);

[그룹핑]

SELECT 컬럼 ----2

FROM 테이블 ----1

GROUP BY 컬럼(그룹) ----3

Q20) 사원 테이블에서 부서별로 출력해보자.

SELECT DEPTNO FROM EMP GROUP BY DEPTNO;

Q21) 사원 테이블에서 직업별로 출력해보자.

SELECT JOB FROM EMP GROUP BY JOB;

[집계 함수]

SUM(), AVG(), MAX(), MIN(), COUNT(*);

Q22) 사원 테이블에서 인원수를 출력해보자.

SELECT COUNT(*) FROM EMP;

SELECT COUNT(COMM) FROM EMP; (NULL값은 출력하지 않는 것이 특징)

Q23) 사원 테이블에서 봉급의 합계, 평균, 큰값, 작은값을 출력해보자.

SELECT SUM(SAL), AVG(SAL), MAX(SAL), MIN(SAL) FROM EMP;

Q24) 사원 테이블에서 직업별 봉급의 합을 구하시오

SELECT JOB, SUM(SAL) FROM EMP GROUP BY JOB;

SELECT ENAME, JOB, SUM(SAL) FROM EMP GROUP BY JOB; (X)

-> GROUP BY를 할 때 SELECT에 올 수 있느 것은 GROUP BY 대상과 집계 함수만 올 수 있다!

Q25) SELECT ENAME FROM EMP ORDER BY ENAME;

SELECT ENAME FROM EMP ORDER BY ENAME ASC; (기본)

SELECT ENAME FROM EMP ORDER BY ENAME DESC;

SELECT ENAME, JOB FROM EMP ORDER BY 2; -> JOB으로 정렬

SELECT ENAME, JOB FROM EMP ORDER BY 1; -> ENAME으로 정렬

Q26) 사원 테이블에서 이름은 오름차순 직업은 내림차순으로 출력해보자.

SELECT ENAME, JOB FROM EMP ORDER BY ENAME, JOB DESC;

Q27) 부서별로 출력하되 같은 직업의 개수를 출력해라.

SELECT DEPTNO, COUNT(JOB) AS “직업의 개수” FROM EMP GROUP BY DEPTNO ORDER BY 1;

SELECT JOB, COUNT(JOB) AS “직업의 개수” FROM EMP GROUP BY JOB ORDER BY 2;

Q28) 10번 부서의 사원의 이름, 사번, 부서명을 출력하자.

SELECT E.ENAME, E.EMPNO, D.DNAME FROM EMP E, DEPT D WHERE E.DEPTNO=D.DEPTNO AND E.DEPTNO=10;

Q29) 20번 부서에서 1982년에 입사한 사원의 이름과 부서명을 출력하자.

SELECT E.ENAME, D.DNAME FROM EMP E, DEPT D WHERE E.DEPTNO=D.DEPTNO AND D.DEPTNO=20 AND E.HIREDATE BETWEEN '82-01-01' AND '82-12-31';

[SELECT 구문정리]

<실행순서>

SELECT ------------5

FROM -------------1

WHERE ------------2

GROUP BY ---------3

HAVING -----------4

ORDER BY ---------6

Q1. 직업별 총 월급의 합을 구하고, 총 직업별 월급의 합이 5000 이상인 것만 출력하라

SELECT SUM(SAL) FROM EMP WHERE SUM(SAL)>=5000 GROUP BY JOB; (X)

*** 집계함수 비교는 WHERE 뒤에 올 수 없다.

SELECT JOB, SUM(SAL)

FROM EMP

GROUP BY JOB

HAVING SUM(SAL)>=5000;

Q2. 부서별 월급의 합을 구하고 그 총합이 10000 이상인 것을 출력하라.

SELECT DEPTNO, SUM(SAL)

FROM EMP

GROUP BY DEPTNO

HAVING SUM(SAL)>=10000;

[WILD CARD]

_ (Underscore) : 한문자

% : 모든 거, 아무 것도 없느 sruddn도 포함

Q1. 이름이 S로 끝나는 경우 : ‘%S'

SELECT ENAME

FROM EMP

WHERE ENAME LIKE '%S'

Q2. 이름의 두 번째 글자가 A인 경우 : ‘_A%'

SELECT ENAME

FROM EMP

WHERE ENAME LIKE '_A%'

Q3. 이름에 T가 두 번 들어가는 경우 : ‘%T%T%'

SELECT ENAME

FROM EMP

WHERE ENAME LIKE '%T%T%';

Q4. 이름의 첫 글자가 A ~ S로 시작하는 경우 :

SELECT ENAME

FROM EMP

WHERE ENAME BETWEEN 'A%' AND 'S%';

Q5. 이름의 첫 글자가 A, S로 시작하고 두 번째 글자가 C가 아닌 경우 :

SELECT ENAME

FROM EMP

WHERE ENAME LIKE 'A%' OR ENAME LIKE 'S%'

AND ENAME NOT LIKE '_C%';

Q6. 10%라는 문자열을 포함한 모든 문자열

LIKE '%10E%%' ESCAPE 'E' (E 뒤에 오는 %는 문자열로 간주함!)

: 이 문자열을 찾으려면 %가 WILD CARD 문자이기 때문에 ESCAPE 문자라는 것을 삽입해야 한다.

즉, LIKE '%10E%%' ESCAPE 'E'라고 하면, ‘%’가 출력된다.

여기서 E 대신에 어떤 문자라도 삽입할 수 있다.

SELECT * FROM EMP WHERE JOB LIKE '%\%AL\%%' ESCAPE '\';