oracle初学者必备scott用户脚本创建及表结构及函数练习实例.docx

1、oracle初学者必备scott用户脚本创建及表结构及函数练习实例oracle初学者必备_scott用户脚本创建及表结构及函数练习实例Oracle数据库的Scott用户创建脚本 - - Copyright (c) Oracle Corporation 1988, 2000. All Rights Reserved. - - NAME - demobld.sql - - DESCRIPTION - This script creates the SQL*Plus demonstration tables in the - current schema. It should be STARTed

2、by each user wishing to - access the tables. To remove the tables use the demodrop.sql - script. - - USAGE - From within SQL*Plus, enter: - START demobld.sql SET TERMOUT ON PROMPT Building demonstration tables. Please wait. SET TERMOUT OFF DROP TABLE EMP; DROP TABLE DEPT; DROP TABLE BONUS; DROP TABL

3、E SALGRADE; DROP TABLE DUMMY; CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL, ENAME VARCHAR2(10), JOB VARCHAR2(9), MGR NUMBER(4), HIREDATE DATE, SAL NUMBER(7, 2), COMM NUMBER(7, 2), DEPTNO NUMBER(2); INSERT INTO EMP VALUES (7369, SMITH, CLERK, 7902, TO_DATE(17-DEC-1980, DD-MON-YYYY), 800, NULL, 20); INS

4、ERT INTO EMP VALUES (7499, ALLEN, SALESMAN, 7698, TO_DATE(20-FEB-1981, DD-MON-YYYY), 1600, 300, 30); INSERT INTO EMP VALUES (7521, WARD, SALESMAN, 7698, TO_DATE(22-FEB-1981, DD-MON-YYYY), 1250, 500, 30); INSERT INTO EMP VALUES (7566, JONES, MANAGER, 7839, TO_DATE(2-APR-1981, DD-MON-YYYY), 2975, NULL

5、, 20); INSERT INTO EMP VALUES (7654, MARTIN, SALESMAN, 7698, TO_DATE(28-SEP-1981, DD-MON-YYYY), 1250, 1400, 30); INSERT INTO EMP VALUES (7698, BLAKE, MANAGER, 7839, TO_DATE(1-MAY-1981, DD-MON-YYYY), 2850, NULL, 30); INSERT INTO EMP VALUES (7782, CLARK, MANAGER, 7839, TO_DATE(9-JUN-1981, DD-MON-YYYY)

6、, 2450, NULL, 10); INSERT INTO EMP VALUES (7788, SCOTT, ANALYST, 7566, TO_DATE(09-DEC-1982, DD-MON-YYYY), 3000, NULL, 20); INSERT INTO EMP VALUES (7839, KING, PRESIDENT, NULL, TO_DATE(17-NOV-1981, DD-MON-YYYY), 5000, NULL, 10); INSERT INTO EMP VALUES (7844, TURNER, SALESMAN, 7698, TO_DATE(8-SEP-1981

7、, DD-MON-YYYY), 1500, 0, 30); INSERT INTO EMP VALUES (7876, ADAMS, CLERK, 7788, TO_DATE(12-JAN-1983, DD-MON-YYYY), 1100, NULL, 20); INSERT INTO EMP VALUES (7900, JAMES, CLERK, 7698, TO_DATE(3-DEC-1981, DD-MON-YYYY), 950, NULL, 30); INSERT INTO EMP VALUES (7902, FORD, ANALYST, 7566, TO_DATE(3-DEC-198

8、1, DD-MON-YYYY), 3000, NULL, 20); INSERT INTO EMP VALUES (7934, MILLER, CLERK, 7782, TO_DATE(23-JAN-1982, DD-MON-YYYY), 1300, NULL, 10); CREATE TABLE DEPT (DEPTNO NUMBER(2), DNAME VARCHAR2(14), LOC VARCHAR2(13) ); INSERT INTO DEPT VALUES (10, ACCOUNTING, NEW YORK); INSERT INTO DEPT VALUES (20, RESEA

9、RCH, DALLAS); INSERT INTO DEPT VALUES (30, SALES, CHICAGO); INSERT INTO DEPT VALUES (40, OPERATIONS, BOSTON); CREATE TABLE BONUS (ENAME VARCHAR2(10), JOB VARCHAR2(9), SAL NUMBER, COMM NUMBER); CREATE TABLE SALGRADE (GRADE NUMBER, LOSAL NUMBER, HISAL NUMBER); INSERT INTO SALGRADE VALUES (1, 700, 1200

10、); INSERT INTO SALGRADE VALUES (2, 1201, 1400); INSERT INTO SALGRADE VALUES (3, 1401, 2000); INSERT INTO SALGRADE VALUES (4, 2001, 3000); INSERT INTO SALGRADE VALUES (5, 3001, 9999); CREATE TABLE DUMMY (DUMMY NUMBER); INSERT INTO DUMMY VALUES (0); COMMIT; SET TERMOUT ON PROMPT Demonstration table bu

11、ild is complete. EXIT SCOTT用户四张表结构 表一:部门表DEPT(使用DESC DEPT;查询) 名称 类型 描述 NO 表示部门编号有两位数字所组成 1 DEPTNO NUMBER(2) VARCHAR2(14) 表示部门名称最多由14个字符所组成 2 DNAME VARCHAR2(13) 表示部门所在位置 3 LOC (SELECT * FROM DEPT;) NO DEPTNO DNAME LOC ACCOUNTING(财NEWYORK(纽约) 1 10 务部，会计部) RESEARCH(调研DALLAS(达拉斯) 2 20 部) SALES(营业部，市CHIC

12、AGO(芝加哥) 3 30 场部) OPERATIONS(运营BOSTON(波士顿) 4 40 部) 表二:雇员表EMP(使用DESC EMP;查询) 名称 类型 描述 NUMBER(4) 表示雇员编号，由四个数字组成 EMPNO VARCHAR2(10) 表示雇员姓名，由10个字符组成 ENAME VARCHAR2(9) 表示雇员的职位，由9个字符组成 JOB NUMBER(4) 表示雇员对应的领导编号，领导也是雇员 MGR 表示雇员的雇佣日期 HIREDATE DATE NUMBER(7,2) 表示雇员的基本工资，由两位小数5位整数SAL 和2位小数组成，共7位 NUMBER(7,2) 表

13、示雇员的奖金 COMM NUMBER(2) 表示雇员所在部门的编号 DEPTNO (SELECT * FROM EMP;) NO EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO CLERK(办17-12月-80 1 7369 SMITH7902 800 20 (史密事员) 斯) 20-2月-81 2 7499 ALLENSALESMAN7698 1600 300 30 (艾伦) (销售员) 22-2月-81 3 7521 WARD SALESMAN 7698 1250 500 30 (沃德) 02-4月-81 4 7566 JONES MANAGER7

14、839 2975 20 (琼斯) (经理主管) 28-9月-81 5 7654 MARTINSALESMAN 7698 1250 1400 30 ()马丁 01-5月-81 6 7698 BLAKE MANAGER 7839 2850 30 (布雷克) 09-6月-81 7 7782 CLARKMANAGER 7839 2450 10 (克拉克) 19-4月-87 8 7788 SCOTTANALYST7566 3000 20 (斯科(分析员) 特) 17-11月-81 9 7839 KINGPRESIDENT 5000 10 (金) (总经理，总裁) 08-9月-81 10 7844 TUR

15、NERSALESMAN 7698 1500 0 30 (特纳) 23-5月-87 11 7876 ADANSCLERK 7788 1100 20 (奥丹斯) 03-12月-81 12 7900 JAMESCLERK 7698 950 30 (詹姆斯) 03-12月-81 13 7902 FORDANALYST 7566 3000 20 (福特) 23-1月-82 14 7934 MILLERCLERK 7782 1300 10 (米勒) 表三:工资等级表:(DESC SALGRADE) 名称 类型 描述 NO 工资的等级 1 GRADE NUMBER 此等级的最低工资 2 LOSAL NUMBER 此等级的最高工资 3 HISAL NUMBER (SELECT * FROM SALG