D+31~33 Oracle (서버 설치하기, SQL SELECT문)

2016. 09. 23. (Fri) 서른한 번째 수업

2016. 09. 26. (Mon) 서른두 번째 수업

2016. 09. 27 (Tue) 서른세 번째 수업

 수업내용 

오라클 설치하기 

SQL Select문 연습하기 

안타깝게도 Mac에는 오라클 서버를 설치할 수 없기 때문에 포스팅 어려워졌다...

설치하는 과정은 패스하고 수업 중에 연습해보는 SQL문 위주로 포스팅 하겠다.

oracle 11g xe 설치 및 설정

1. oracle 11g xe 다운로드 및 설치

1. http://www.oracle.com

2. 회원가입 및 로그인

3. Downloads 메뉴에서 Oracle Database 11g Express Edition1을 선택

4. Accept License Agreement 체크 후

5. Oracle Database Express Edition 11g Release

6. 압축풀고 setup -> password : oracle

2. scott 계정 설정

1. sqlplus system/oracle  //SYSTEM 계정으로 SQL*Plus

2. @C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\admin\scott.sql

//내장 계정인 scott 계정을 사용하겠다고 명령

3. show user; //USER is "SCOTT"이란 말이 뜨면 성공

4. alter user scott identified by tiger; //tiger로 비밀번호 변경

5. exit //나간후 sqlplus scott/tiger로 다시 접속

*) set linesize 120;

*)  set pagesize 20;

L01 SELECT

1. sqlplus scott/tiger //scott 계정이 가지고 있는 tablespace로 실습

2. select * from tab; //mysql> show tables;

3. desc emp;; // dept 라는 table 구조 보기

4. 대표적인 자료형

1. NUMBER(PRECISION,SCALE)

1. 숫자를 저장하기 위한 데이터 형

2. PRECISION: 소수점을 포함한 전체 자리 수

3. SCALE: 소수점 이하 자리 수

예) 72.5는 NUMBER(3,1), 10은  NUMBER(2),

     7788은 NUMBER(4), 어떤 숫자도 상관없다면 NUMBER

2. DATE

1. 날짜와 시간 데이터를 저장하기 위한 데이터 형

3. VARCHAR2(숫자)

1. 가변적인 문자열을 저장하기 위한 데이터 형

2. 괄호안은 바이트 수를 의미한다.

3. 만일 컬럼의 데이터 형이 VARCHAR(15)라면

4. 최대 15바이트의 문자열을 저장할 수 있다.

(오라클 캐리터셋이 KO16KSC5601이나KO16MSWIN949이면

한글은 2바이트, 영어는 1바이트. UTF8이나 AL32UTF8이면

한글은 3바이트, 영어는 1바이트이다.)

4. CHAR (!=JAVA.lang.Char)

1. 문자 데이터를 저장하기 위한 자료형

2. 고정 길이 문자 데이터를 저장

3. CHAR(20) 20BYTE의 고정 길이를 저장 공간으로 할당 받는다.

(때문에 저장 공간을 낭비하지 않기 위해 VARCHAR2를 사용)

5. LONG

1. 2기가 바이트의 가변 길이 문자 저장

2. VARCHAR2와 유사한 특징을 가지나 아래와 같은 제한 사항이 있다.

3. 하나의 테이블에 하나의 LONG 타입만 사용 가능 (NOT) NULL을 제외한 다른 제약 조건은 지정할 수 없다.

4. 인덱스를 만들 수 없다.

5. scott 계정의 emp 테이블 구조

EMPNO(사원번호)

ENAME(이름)

JOB(직책)

MGR(직속상사 사원번호)

HIREDATE(입사일)

SAL(급여)

COMM(커미션)

DEPTNO(부서번호)

6. EMP 테이블에 담긴 모든 사원 레코드를 조회해보자

select * from emp;

7. SELECT 구조

SELECT 컬럼, 컬럼…

FROM 테이블명

WHERE 조건

GROUP BY 구문

UNION/UNION ALL/INTERSECT/MINUS 구문

ORDER BY 구문

8. 문제1) 사원명과 입사일을 조회하시오.

답) select ename, hiredate from emp;

9. 문제2) 사원번호와 이름을 조회하시오.

답) select empno, ename from emp;

10. 문제3) 총 사원수를 구하시오.

답) select count(empno) from emp;

11. 문제4) 부서번호가 10인 사원을 조회하시오.

힌트) 위 SQL문은 WHERE 조건을 사용

답) select ename from emp where deptno='10';

12. 문제5) 월급여가 2500이상 되는 사원을 조회하시오.

힌트) WHERE 조건에 =,>,>=,<=,< 비교 연산자를 사용

답) select ename from emp where sal>=2500 order by sal asc(desc);

13. 문제6) 사원들 중 이름이 S로 시작하는 사원의 사원번호와 이름을 조회하시오.

힌트) LIKE는 %와 _문자와 함께 검색시 사용

답) select empno, ename from emp where ename like 'S%';

14. 문제7) 사원 이름에 T가 포함된 사원이 사원번호와 이름을 조회하시오.

답) select empno, ename from emp where ename like 'T%';

15. 문제8-1) 커미션이 300, 500, 1400이 사원의 사번, 이름, 커미션을 조회하시오.

힌트) 2가지 경우의 SQL을 만드세요. WHERE절에 OR과 IN()을 사용

답) select empno, comm from emp where comm=300 or comm=500 or comm=1400;

     select empno, comm from emp where comm in(300, 500, 1400);

16. 문제8-2) 커미션이 300, 500, 1400이 아닌 사원을 NOT IN()으로 조회

답)  select * from emp where comm not in(300, 500, 1400);

17. 문제9) 부서번호가 10번이 아닌 사원을 조회하시오.

1. 비교연산자를 이용하는 방법

답) select * from emp where deptno!=10;

     select * from emp where deptno <>10;

     select * from emp where deptno^=10; //MySQL에서 사용불가

2. NOT을 이용

답) select * from emp where not deptno = 10;

18. 문제10) 급여가 2000이상이고 3000이하인 직원을 모두 조회하시오.

1. 비교연산자를 이용

답) select ename, sal from emp where sal>=2000 and sal<=3000;

2. BETWEEN

답) select ename, sal from emp where sal between 2000 and 3000;

19. 문제11) 급여가 2000이하 이거나 3000이상인 직원을 모두 조회하시오.

1. 비교연산자를 이용

답) select ename, sal from emp where sal<=2000 or sal>=3000;

20. HW) 사원의 실제 급여는 SAL*10이다. 사원 이름과 커미션, 사원의 실제급여,

사원의 실제급여 + 커미션을 조회하세요.

힌트1) COMM에 NULL 때문에 실제급여 + 커미션이 되지 않는다.

NULL도 중요한 데이터로 취급되기 때문이다.

nvl() 내장함수로 NULL을 0으로 바꾸고 계산하면 된다.

답) select ename, comm frome emp nvl(comm, 0)

힌트2) 산술 연산자(+,-,*,/)를 사용하여 조회하시오.

L02 SELECT 문제

1. SQLPLUS 시스템 변수 조작

1. SET HEADING OFF //칼럼 제목 출력 끄기

2. SET HEADING ON //칼럼 제목 출력

3. SET LINESIZE 100 //ROW의 길이를 100으로 조정(기본80)

4. SET PAGESIZE 20 //PAGE의 크기를 20으로 조정 (기본 14)

5. COLUMN ENAME FORMAT A25

//문자형식의 칼럼이 출력하는 길이를 25자리로 변경

6. COLUMN SAL FORMAT 9,999,999

//숫자는 길이가 자동으로 변경되므로 길이 조정이 필요 없음

//대신 표현 방법을 바꿀 수 있다.

//예) 1400 -> 1,400

7. COLUMN COMM FORMAT 0,000,000 //예) 1400 -> 0,001,400

2. 문제1) 이름이 A를 포함하는 사원을 모두 찾으세요.

답)  select * from emp where ename like'%A%';

3. 문제2) 이름의 두 번째 글자가 A인 사원을 찾으세요.

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

4. UPDATE EMP SET ENAME = ‘A_LLEN’ WHERE EMPNO = 7499;

문제3) 이름에 ‘A’를 포함하는 사원을 찾으세요.

1. ESCAPE를 사용해’_’를 문자로 인식하도록 하세요.

답) select EMPNO, ENAME from EMP WHERE ENAME LIKE ‘%A\_%’ESCAPE’\’;

5. 문제4) 이름에 ‘A’를 포함하지 않는 사원을 검색하세요.

답) SELECT * FROM EMP WHERE ENAME NOT LIKE ‘%A%’;

6. 문제5) 커미션이 NULL인 사원을 검색하세요.

1. NULL은 연산, 할당, 비교가 불가능

답) select * from emp where comm is null;

7. 문제6) 커미션이 NULL이 아닌 사원을 검색하세요.

답)  select * from emp where comm is not null;

8. 정렬을 위한 ORDER BY : ASC(오름차순), DESC(내림차순)

9. 문제7) 급여 칼럼을 기준으로 사원을 오름차순으로 정렬하세요.

답) select * from emp order by sal;

10. 문제8) 급여 칼럼을 기준으로 사원을 내림차순으로 정렬하세요.

답) select * from emp order by sal desc;

11. 문제9) 사원의 이름을 알파벳 순으로 정렬하세요.

답) select * from emp order by ename asc;

12. 문제10)  입사한 지 오래된 순으로 사원을 정렬하세요.

답) select * from emp order by hiredate asc;

13. 문제11) 급여를 많이 받는 순으로 사원을 정렬할 때 급여가 같은 사람이 존재할 경우 이름의 철자가 빠른 사람부터 정렬하세요.

답) select * from emp order by sal desc, ename asc;

L03 내장함수

1. DUAL TABLE

1. 더미 테이블로 최대 칼럼 1을 가지는 테스트 테이블

2. 예) SELECT SYSDATE FROM DUAL;

2. 숫자 함수

1. ABS() 절대값을 구한다.

i. SELECT -10, ABS(-10) FROM DUAL;

2. ROUND() //반올림

i. SELECT 34.5678 ROUND(34.5678) FROM DUAL;

0 : 1, default = 0

ii. SELECT 34.5678, ROUND(34.5678, 2) FROM DUAL;

1: 1/10, 2: 1/100, 3: 1/1000

iii. SELECT 34.5678, ROUND(34.5678, -1) FROM DUAL;

-1: 10,  -2: 100,  -3: 1000

3. FLOOR() //반내림(소수점을 없앤다.)

i. SELECT 34.5678 FLOOR(3.4.5678) FROM DUAL;

4. TRUNC() // 특정 자릿수 반내림

i. 사용법은 ROUND()와 동일

5. MOD() //나머지

i. SELECT MOD(27, 2), MOD(27, 5) MOD(27, 7) FROM DUAL;

3. 문제1) 사원 번호가 홀수인 사람들을 검색하세요.

1. 홀수는 2로 나눈 나머지가 1

답) SELECT * FROM EMP WHERE MOD(EMPNO,2) =1;

4. 문자 처리 함수

1. LOWER() //소문자로 변환

i. SELECT ‘Oracle’, LOWER(‘Oracle’) FROM DUAL;

2. UPPER() //대문자로 변환

i. SELECT ‘Oracle’, UPPER(‘Oracle’) FROM DUAL;

3. INITCAP() //첫 글자만 대문자로 나머지 글자는 소문자로 변환

i. SELECT ‘ORACLE’, LOWER(‘ORACLE’) FROM DUAL;

4. CONCAT() //문자의 값을 연결

5. LENGTH() // 문자의 길이를 반환 (한글 1byte)

i.  SELECT LENGTH(oracle), LENGTH(‘오라클’) FROM DUAL;

6. LENGTHB() // 문자의 길이를 반환 (한글 2byte)

i.  SELECT LENGTHB(oracle), LENGTHB(‘오라클’) FROM DUAL;

7. SUBSTR() //문자를 잘라서 추출 (한글 1byte)

i. SELECT SUBSTR(‘Welcome to Oracle’, 4, 3) FROM DUAL;

ii. SELECT SUBSTR(‘Welcome to Oracle’, -4, 3) FROM DUAL;

iii. SUBSTR(대상, 시작 위치, 추출할 개수)

시작 위치가 음수면 끝에서부터 시작

8. SUBSTRB //문자를 잘라서 추출 (한글 2byte)

i. SELECT SUBSTR(“오라클수업”, 4, 3), SUBSTRB(“오라클수업”, 4, 3)

  FROM DUAL;

ii. 한글을 1Byte로 본 것과 2Byte로 본 것의 차이

iii. 영어는 무조건 1Byte

iv. 오(1)라(2)클(3)수(4)업(5) // 오(1,2)라(3,4)클(5,6)수(7,8)업(7,8)

5. 문제2) 직급이 ‘manager’인 사원을 소문자로 검색하세요.

답) select * from emp where LOWER(job) = 'manager';

6. 문제3) 사원에 이름, 입사 년도, 입사한 달을 출력하세요.

답) select ename, substr(hiredate,1,2) YEAR, substr(hiredate,4,2) MON from emp;

7. 문제4) 9월에 입사한 사원을 검색하세요.

답) select * from emp where substr(hiredate,4,2)=’09’;

8. 문제5) 81년도에 입사한 사원을 검색하세요.

a. 연산자, BETWEEN, SUBSTR() 3가지 경우를 만드세요.

답) 1. select * from emp where substr(hiredate,1,2)=’81’;

     2. select * from emp where hiredate >= '1981/01/01' AND hiredate <='1981/12/31';

     3. select * from emp where hiredate between '1981/01/01' AND '1981/12/31';

9. 문제6) ‘E’로 끝나는 사원을 SUBSTR()로 검색하세요.

답) select * from emp where substr(ename, -1,1) = 'E';

10. 문자처리함수2

1. LPAD()/RPAD() 특정 기호를 채우는 함수

i. SELECT LPAD(‘ORACLE’,20,’#’) FROM DUAL;

ii. SELECT LPAD(‘ORACLE’,20,’#’) FROM DUAL;

2. LTRIM(), RIRIM() 공백문자를 삭제하는 함수 //자주쓰는 함수

i. SELECT LTRIM(‘ORACLE’) FROM DUAL; //왼쪽 공백을 제거

ii. SELECT RTRIM(‘ORACLE’) FROM DUAL; //오른쪽 공백을 제거

3. TRIM() 특정 문자를 잘라내는 함수 //왼쪽 오른쪽 다 지움

i. SELECT TRIM(‘a’ FROM ’ aaaaaOracleaaaa’) FROM DUAL;

ii. SELECT TRIM(‘ORACLE’) FROM DUAL;