(SQL기초강좌)실습데이터 설치, 실습데이터 구조

(SQL기초강좌)실습데이터 설치, 실습데이터 구조

http://ojc.asia/bbs/board.php?bo_table=LecSQLnPlSql&wr_id=587

 

(SQL기초강좌)실습데이터 설치, 실습데이터 구조

(SQL기초강좌)실습데이터 설치, 실습데이터 구조1-4. 실습데이터 설치 및 SQL*PLUS 실행환경 설정Oracle11g 설치 후 우리가 사용할 SCOTT 계정은 계정에 락이 걸려있고 비밀번호가 만료 되어 있으므로 아래 SQL을 실행하자. C:>SQLPLUS / AS SYSDBA SQL>ALTER USER SCOTT ACCOUNT UNLOCK;…

ojc.asia

1-4. 실습데이터 설치 및 SQL*PLUS 실행환경 설정

Oracle11g 설치 후 우리가 사용할 SCOTT 계정은 계정에 락이 걸려있고 비밀번호가 만료 되어 있으므로 아래 SQL을 실행하자.

C:>SQLPLUS / AS SYSDBA SQL>ALTER USER SCOTT ACCOUNT UNLOCK; SQL>ALTER USER SCOTT IDENTIFIED BY TIGER;

아래 URL에서 실습데이터 설치

http://ojc.asia/bbs/board.php?bo_table=LecOrccleTun&wr_id=77

SQL*PLUS에서 실습을 원할히 하기 위해 아래 명령을 실행하고 SQL*PLUS 접속시 마다 ONJ.SQL 파일을 실행하자.

SQL> EDIT ONJ

SET LINESIZE 130

SET TIMING ON

SET AUTOTRACE ON

SET PAGESIZE 100

SQL> @ONJ

SQL*Plus에서 한글이 깨진다면 아래처럼 NLS_LANG 환경변수를 설정하자.

(컴퓨터 à 속성 à 고급 à 환경변수)

AMERICAN_AMERICA.KO16KSC5601

쿼리를 익히기 위해서는 데이터 건수가 별로 없는 EMP, DEPT 테이블을 이용하여 쿼리를 실습할 것이며 성능이나 수행시간, 통계정보등 분석을 위한 쿼리는 MYEMP1, MYDEPT1 위주로 실습이 진행될 것이다.

1-4. 실습데이터 구조(테이블 정의서)

테이블 정의서란? 데이터베이스 설계시 테이블의 구조와 칼럼의 특성을 알기 쉽게 요약한 내용이며 테이블의 칼럼 이름, 데이터 타입, 키 종류, NULL이나 중복 값의 허용 여부, 외래 키 그리고 칼럼에 대한 기타 설명으로 구성되어 있다.

- MYEMP1(사원 테이블) 10,000,002건

칼럼이름	데이터 타입	Key Type	NN/ Unique	FK table	FK column	설명
EMPNO	NUMBER	PK	NN,U			사번
ENAME	VARCHAR2(100)		NN			이름
DEPTNO	VARCHAR2(1)	FK		MYDEPT1	DEPTNO	부서코드
ADDR	VARCHAR2(100)					주소
SAL	NUMBER(7)					급여
JOB	VARCHAR2(20)					직무
COMM	NUMBER(7)					수당
SUNGBYUL	VARCHAR2(1)					성별 (M : 남, F : 여)
HIREDATE	DATE					입사일자
OUTDATE	VARCHAR2(8)					퇴사일자
MGR	NUMBER	FK		MYEMP1	EMPNO	관리자사번

SQL> SELECT COUNT(*) FROM MYEMP1; COUNT(*) --------- 10000002 SQL> SET LINESIZE 130 SQL> COLUMN ADDR FORMAT A10 SQL> COLUMN ENAME FORMAT A10 SQL> COLUMN JOB FORMAT A10 SQL> COLUMN DEPTNO FORMAT A4 SQL> SELECT EMPNO, ENAME,DEPTNO, ADDR, SAL, JOB, COMM, SUNGBYUL,HIREDATE, OUTDATE FROM MYEMP1 WHERE ROWNUM < 5; EMPNO ENAME DEPT ADDR SAL JOB COMM S HIREDATE OUTDATE ---------------------------------------------------------------------------------------------------------------------------- 541 홍길동541 1 서울541 541 CLERK M 13/11/06 15/05/01 542 다길동542 2 부산542 542 SALESMAN F 13/11/05 543 나길동543 3 대구543 543 MANAGER M 13/11/04 544 마길동544 0 광주544 544 ANALYST F 13/11/03

- MYEMP1_OLD(퇴사자 테이블) 1,666,667건

(사원테이블 데이터 중 퇴사자만 별도로 저장, 이름이 ‘홍길동’으로 시작하는 사원이 퇴사자임)

칼럼이름	데이터 타입	Key Type	NN/ Unique	FK table	FK column	설명
EMPNO	NUMBER	PK, FK	NN,U			사번
ENAME	VARCHAR2(100)		NN			이름

SQL> SELECT COUNT(*) FROM MYEMP1_OLD; COUNT(*) ---------- 1666667 SQL> select empno, ename from myemp1_old where rownum < 5; EMPNO ENAME ---------- ---------- 8521 홍길동8521 8527 홍길동8527 8533 홍길동8533 8539 홍길동8539

- MYDEPT1(부서테이블) 7건

칼럼이름	데이터 타입	Key Type	NN/ Unique	FK table	FK column	설명
DEPTNO	VARCHAR2(1)	PK	NN,U			부서코드
DNAME	VARCHAR2(100)					부서명
UP_DEPTNO	VARCHAR2(1)	FK		MYDEPT1	DEPTNO	상위 부서코드

SQL> COLUMN DNAME FORMAT A20 SQL> SELECT * FROM MYUDEPT1; D DNAME UP_DEPT - ---------------------------- -------- 0 개발1팀 4 1 개발2팀 4 2 기획1팀 5 3 기획2팀 5 4 개발본부 6 5 교육본부 6 6 오라클자바커뮤니티 7 개의 행이 선택되었습니다.

- MYLECTURE1(강좌 테이블) 7건

칼럼이름	데이터 타입	Key Type	NN/ Unique	FK table	FK column	설명
LECTURE_ID	NUMBER(2)	PK				강좌코드
LECTURE_NM	VARCHAR2(100)					강좌명
TEACHER	VARCHAR2(20)					강사
ROOM	VARCHAR2(1)					강의장

SQL> COLUMN LECTURE_NM FORMAT A30 SQL> SELECT * FROM MYLECTURE1; LECTURE_ID LECTURE_NM TEACHER ROOM --------------------------------------------------------------------------------------- 1 스프링개발자과정 이종철 A 2 SQL기초실무과정 김종철 B 3 ORACLE HINT과정 안종철 C 4 닷넷개발자과정 박종철 D 5 웹퍼블리실개발자과정 최종철 E 6 자바기초에서스프링까지과정 김종철 F 7 안드로이드과정 이종철 G 7 개의 행이 선택되었습니다.

- MYSUGANG1(수강 테이블) 6,500,000건

칼럼이름	데이터 타입	Key Type	NN/ Unique	FK table	FK column	설명
EMPNO	NUMBER	PK, FK		MYEMP1	EMPNO	사번
LECTURE_ID	NUMBER(2)	PK, FK		LECTURE	LECTURE_ID	강좌코드
SEQ	NUMBER(2)	PK				순번
START_DATE	DATE					시작일
END_DATE	DATE					종료일

SQL> SELECT COUNT(*) FROM MYSUGANG1; COUNT(*) ---------- 6500000 SQL> SELECT * FROM MYSUGANG1 WHERE ROWNUM < 5; EMPNO LECTURE_ID SEQ START_DATE END_DATE --------------------------------------------------------------- 940 1 1 15/05/01 15/05/31 941 1 1 15/05/01 15/05/31 942 1 1 15/05/01 15/05/31 943 1 1 15/05/01 15/05/31

- MYSALGRADE1(급여등급 테이블) 6건

칼럼이름	데이터 타입	Key Type	NN/ Unique	FK table	FK column	설명
GRADE	NUMBER(1)					급여등급
LOSAL	NUMBER(8)					하한값
HISAL	NUMBER(8)					상한값

SQL> SELECT * FROM MYSALGRADE1; GRADE LOSAL HISAL -------------------------------------- 1 0 1000000 2 1000001 2000000 3 2000001 3000000 4 3000001 4000000 5 4000001 5000000 6 5000001 6000000 6 개의 행이 선택되었습니다.

다음 3개의 테이블은 오라클을 설치하면 기본적으로 생성되는 scott 계정에 있는 EMP, DEPT, SALGRADE 테이블로 기본 쿼리를 익히는데 사용되는 건수가 적은 테이블 이다.

- EMP(사원 테이블) 14건

칼럼이름	데이터 타입	Key Type	NN/ Unique	FK table	FK column	설명
EMPNO	NUMBER(4)	PK	NN,U			사번
ENAME	VARCHAR2(10)					이름
JOB	VARCHAR2(9)					직무
MGR	NUMBER(4)	FK		EMP	EMPNO	관리자사번
HIREDATE	DATE					입사일자
SAL	NUMBER(7,2)					급여
COMM	NUMBER(7,2)					수당
DEPTNO	NUMBER(2)	FK		DEPT	DEPTNO	부서번호

SQL> SELECT * FROM EMP; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 80/12/17 800 20 7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30 7521 WARD SALESMAN 7698 81/02/22 1250 500 30 7566 JONES MANAGER 7839 81/04/02 2975 20 7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30 7698 BLAKE MANAGER 7839 81/05/01 2850 30 7782 CLARK MANAGER 7839 81/06/09 2450 10 7788 SCOTT ANALYST 7566 87/07/13 3000 20 7839 KING PRESIDENT 81/11/17 5000 10 7844 TURNER SALESMAN 7698 81/09/08 1500 0 30 7876 ADAMS CLERK 7788 87/07/13 1100 20 7900 JAMES CLERK 7698 81/12/03 950 30 7902 FORD ANALYST 7566 81/12/03 3000 20 7934 MILLER CLERK 7782 82/01/23 1300 10 14 개의 행이 선택되었습니다.

- DEPT(부서 테이블) 4건

칼럼이름	데이터 타입	Key Type	NN/ Unique	FK table	FK column	설명
DEPTNO	NUMBER(2)	PK	NN,U			부서코드
DNAME	VARCHAR2(14)					부서이름
LOC	VARCHAR2(13)					위치

SQL> SELECT * FROM DEPT; DEPTNO DNAME LOC ----------------------------------------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 4 개의 행이 선택되었습니다.

- SALGRADE(급여등급 테이블) 5건

칼럼이름	데이터 타입	Key Type	NN/ Unique	FK table	FK column	설명
GRADE	NUMBER					등급
LOSAL	NUMBER					급여하한값
HISAL	NUMBER					급여상한값

SQL> SELECT * FROM SALGRADE; GRADE LOSAL HISAL ----------------------------------------- 1 700 1200 2 1201 1400 3 1401 2000 4 2001 3000 5 3001 9999

5 개의 행이 선택되었습니다.