oracle初学者必备scott用户脚本创建及表结构及函数练习实例.docx
《oracle初学者必备scott用户脚本创建及表结构及函数练习实例.docx》由会员分享,可在线阅读,更多相关《oracle初学者必备scott用户脚本创建及表结构及函数练习实例.docx(13页珍藏版)》请在冰豆网上搜索。
![oracle初学者必备scott用户脚本创建及表结构及函数练习实例.docx](https://file1.bdocx.com/fileroot1/2022-10/12/c7d16e60-93b3-46bd-bc56-642bd6e7f4ca/c7d16e60-93b3-46bd-bc56-642bd6e7f4ca1.gif)
oracle初学者必备scott用户脚本创建及表结构及函数练习实例
oracle初学者必备_scott用户脚本创建及表结构及函数练习实例
Oracle数据库的Scott用户创建脚本
--
--Copyright(c)OracleCorporation1988,2000.AllRightsReserved.
--
--NAME
--demobld.sql
--
--DESCRIPTION
--ThisscriptcreatestheSQL*Plusdemonstrationtablesinthe
--currentschema.ItshouldbeSTARTedbyeachuserwishingto
--accessthetables.Toremovethetablesusethedemodrop.sql
--script.
--
--USAGE
--FromwithinSQL*Plus,enter:
--STARTdemobld.sql
SETTERMOUTON
PROMPTBuildingdemonstrationtables.Pleasewait.SETTERMOUTOFF
DROPTABLEEMP;
DROPTABLEDEPT;
DROPTABLEBONUS;
DROPTABLESALGRADE;
DROPTABLEDUMMY;
CREATETABLEEMP
(EMPNONUMBER(4)NOTNULL,
ENAMEVARCHAR2(10),
JOBVARCHAR2(9),
MGRNUMBER(4),
HIREDATEDATE,
SALNUMBER(7,2),
COMMNUMBER(7,2),
DEPTNONUMBER
(2));
INSERTINTOEMPVALUES
(7369,'SMITH','CLERK',7902,
TO_DATE('17-DEC-1980','DD-MON-YYYY'),800,NULL,20);
INSERTINTOEMPVALUES
(7499,'ALLEN','SALESMAN',7698,
TO_DATE('20-FEB-1981','DD-MON-YYYY'),1600,300,30);INSERTINTOEMPVALUES
(7521,'WARD','SALESMAN',7698,
TO_DATE('22-FEB-1981','DD-MON-YYYY'),1250,500,30);INSERTINTOEMPVALUES
(7566,'JONES','MANAGER',7839,
TO_DATE('2-APR-1981','DD-MON-YYYY'),2975,NULL,20);INSERTINTOEMPVALUES
(7654,'MARTIN','SALESMAN',7698,
TO_DATE('28-SEP-1981','DD-MON-YYYY'),1250,1400,30);INSERTINTOEMPVALUES
(7698,'BLAKE','MANAGER',7839,
TO_DATE('1-MAY-1981','DD-MON-YYYY'),2850,NULL,30);INSERTINTOEMPVALUES
(7782,'CLARK','MANAGER',7839,
TO_DATE('9-JUN-1981','DD-MON-YYYY'),2450,NULL,10);INSERTINTOEMPVALUES
(7788,'SCOTT','ANALYST',7566,
TO_DATE('09-DEC-1982','DD-MON-YYYY'),3000,NULL,20);INSERTINTOEMPVALUES
(7839,'KING','PRESIDENT',NULL,
TO_DATE('17-NOV-1981','DD-MON-YYYY'),5000,NULL,10);INSERTINTOEMPVALUES
(7844,'TURNER','SALESMAN',7698,
TO_DATE('8-SEP-1981','DD-MON-YYYY'),1500,0,30);INSERTINTOEMPVALUES
(7876,'ADAMS','CLERK',7788,
TO_DATE('12-JAN-1983','DD-MON-YYYY'),1100,NULL,20);INSERTINTOEMPVALUES
(7900,'JAMES','CLERK',7698,
TO_DATE('3-DEC-1981','DD-MON-YYYY'),950,NULL,30);INSERTINTOEMPVALUES
(7902,'FORD','ANALYST',7566,
TO_DATE('3-DEC-1981','DD-MON-YYYY'),3000,NULL,20);INSERTINTOEMPVALUES
(7934,'MILLER','CLERK',7782,
TO_DATE('23-JAN-1982','DD-MON-YYYY'),1300,NULL,10);
CREATETABLEDEPT
(DEPTNONUMBER
(2),
DNAMEVARCHAR2(14),
LOCVARCHAR2(13));
INSERTINTODEPTVALUES(10,'ACCOUNTING','NEWYORK');INSERTINTODEPTVALUES(20,'RESEARCH','DALLAS');INSERTINTODEPTVALUES(30,'SALES','CHICAGO');INSERTINTODEPTVALUES(40,'OPERATIONS','BOSTON');
CREATETABLEBONUS
(ENAMEVARCHAR2(10),
JOBVARCHAR2(9),
SALNUMBER,
COMMNUMBER);
CREATETABLESALGRADE
(GRADENUMBER,
LOSALNUMBER,
HISALNUMBER);
INSERTINTOSALGRADEVALUES(1,700,1200);INSERTINTOSALGRADEVALUES(2,1201,1400);INSERTINTOSALGRADEVALUES(3,1401,2000);INSERTINTOSALGRADEVALUES(4,2001,3000);INSERTINTOSALGRADEVALUES(5,3001,9999);
CREATETABLEDUMMY
(DUMMYNUMBER);
INSERTINTODUMMYVALUES(0);
COMMIT;
SETTERMOUTON
PROMPTDemonstrationtablebuildiscomplete.
EXIT
SCOTT用户四张表结构
表一:
部门表DEPT(使用DESCDEPT;查询)
名称类型描述NO
表示部门编号有两位数字所组成1DEPTNONUMBER
(2)
VARCHAR2(14)表示部门名称最多由14个字符所组成2DNAME
VARCHAR2(13)表示部门所在位置3LOC
(SELECT*FROMDEPT;)
NODEPTNODNAMELOC
ACCOUNTING(财NEWYORK(纽约)110
务部,会计部)
RESEARCH(调研DALLAS(达拉斯)220
部)
SALES(营业部,市CHICAGO(芝加哥)330
场部)
OPERATIONS(运营BOSTON(波士顿)440
部)
表二:
雇员表EMP(使用DESCEMP;查询)
名称类型描述
NUMBER(4)表示雇员编号,由四个数字组成EMPNO
VARCHAR2(10)表示雇员姓名,由10个字符组成ENAME
VARCHAR2(9)表示雇员的职位,由9个字符组成JOB
NUMBER(4)表示雇员对应的领导编号,领导也是雇员MGR
表示雇员的雇佣日期HIREDATEDATE
NUMBER(7,2)表示雇员的基本工资,由两位小数5位整数SAL
和2位小数组成,共7位
NUMBER(7,2)表示雇员的奖金COMM
NUMBER
(2)表示雇员所在部门的编号DEPTNO
(SELECT*FROMEMP;)
NOEMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
CLERK(办17-12月-8017369SMITH790280020
(史密事员)
斯)
20-2月-8127499ALLENSALESMAN7698160030030
(艾伦)(销售员)
22-2月-8137521WARDSALESMAN7698125050030
(沃德)
02-4月-8147566JONESMANAGER7839297520
(琼斯)(经理主管)
28-9月-8157654MARTINSALESMAN76981250140030
()马丁
01-5月-8167698BLAKEMANAGER7839285030
(布雷
克)
09-6月-8177782CLARKMANAGER7839245010
(克拉
克)
19-4月-8787788SCOTTANALYST7566300020
(斯科(分析员)
特)
17-11月-8197839KINGPRESIDENT500010
(金)(总经理,总
裁)
08-9月-81107844TURNERSALESMAN76981500030
(特纳)
23-5月-87117876ADANSCLERK7788110020
(奥丹
斯)
03-12月-81127900JAMESCLERK769895030
(詹姆
斯)
03-12月-81137902FORDANALYST7566300020
(福特)
23-1月-82147934MILLERCLERK7782130010
(米勒)
表三:
工资等级表:
(DESCSALGRADE)
名称类型描述NO
工资的等级1GRADENUMBER
此等级的最低工资2LOSALNUMBER
此等级的最高工资3HISALNUMBER
(SELECT*FROMSALG