oracle初学者必备scott用户脚本创建及表结构及函数练习实例.docx
- 文档编号:6747904
- 上传时间:2023-01-09
- 格式:DOCX
- 页数:13
- 大小:18.97KB
oracle初学者必备scott用户脚本创建及表结构及函数练习实例.docx
《oracle初学者必备scott用户脚本创建及表结构及函数练习实例.docx》由会员分享,可在线阅读,更多相关《oracle初学者必备scott用户脚本创建及表结构及函数练习实例.docx(13页珍藏版)》请在冰豆网上搜索。
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*FROMSALGRADE;查询)
NOGRADELOSALHISAL1170012002212011400331401200044200130005530019999
表四:
工资表BONUS:
(DESCBONUS)
名称类型描述NO
VARCHAR2(10)雇员姓名1ENAME
雇员职位2JOBVARCHAR2(9)
雇员基本工资3SALNUMBER
奖金,提成4COMMNUMBER
SCOTT用户语句与函数练习
--1.列出至少有一个员工的所有部门
selectd.dname,t1.co
from(selecte.deptno,count(e.deptno)cofromempegroupbye.deptno)t1,
deptd
whered.deptno=t1.deptnoandt1.co>1;
--2.列出薪金比‘SMITH’多的所有员工
select*
fromemp
wheresal>(selecte.salfromempewheree.ename='SMITH');
--3.列出所有员工的姓名以及直接上级的姓名
selecte1.ename,e2.enamefromempe1,empe2wheree1.mgr=e2.empno;
--4.列出受雇日期早于直接上级入职日期的职工编号,姓名,部门名称selecte1.empno员工号,
e1.ename员工姓名,
e1.hiredate入职日期,
e2.ename上级姓名,
e2.hiredate上级入职日期
fromempe1,empe2
wheree1.mgr=e2.empno
ande1.hiredate fromempe rightouterjoindeptd one.deptno=d.deptno orderbyd.dname; --6.列出所有职位为'CLERK'的姓名和部门名称,部门人数 selecte.ename,d.dname fromempe,deptd wheree.deptno=d.deptno ande.job='CLERK'; --7.列出最低工薪大于1500的各种工作以及从事此工作的全部雇员的人数selectdistincte.job,count(*)fromempe wheree.sal>1500 groupbye.job; --8.列出在部门'SALES'(销售部)工作的员工信息,假设不知道销售部门的部门号selecte.enamefromempewheree.job='SALESMAN'; --9.列出薪金高于公司平均薪金的所有员工,部门,上级领导,工资级别selecte2.ename,e2.deptno,e3.ename,s.grade fromempe2,empe3,salgrades wheree2.sal>(selectavg(e.sal)fromempe)and(e2.salbetweens.losalands.hisal)ande2.mgr=e3.empno; --10.列出与'SCOTT'从事相同工作的所有员工及部门名称 selecte.ename,d.dname fromempe,deptd wheree.jobin(selecte2.jobfromempe2wheree2.ename='SCOTT') ande.deptno=d.deptno; --11.列出薪金等于部门20中的员工的薪金的所有员工和薪金 select* fromemp wheresalin(selecte.salfromempewheree.deptno=20); --12.列出薪金高于部门30中的员工的薪金的所有员工和薪金,部门名称 selecte2.ename,e2.sal,d.dname fromempe2,deptd wheresal> (selectmax(t1.sal) from(selecte.salfromempewheree.deptno=30)t1) andd.deptno=e2.deptno; --13.列出每个部门员工的数量,平均工资和平均年限 selectt2.*,t1.ro3平均年限 from(selecte2.deptno,count(*)员工数量,avg(e2.sal)平均工资 fromempe2 groupbye2.deptno)t2, (selectro2.deptno,avg(ro2.ro)ro3from(selecte3.deptno, round(months_between(sysdate,e3.hiredate)/12)ro fromempe3)ro2 groupbyro2.deptno)t1 wheret1.deptno=t2.deptno;--14.列出所有员工的姓名,工资,部门名称 selecte.ename,e.sal,d.dnamefromempe,deptd whered.deptno=e.deptno;--15.列出所有部门信息和部门人数 selectd.*,nvl(t1.c,0)部门人数 fromdeptd leftouterjoin(selecte.deptno,count(e.deptno)c fromempe groupbye.deptno)t1 ond.deptno=t1.deptno; --16.列出各种工作的最低工薪以及从事此工作的员工信息 select* fromemp,(selecte.jobj,min(e.sal)sfromempegroupbye.job)t1 whereemp.job=t1.j andemp.sal=t1.s; --17.列出各个部门职位为'MANAGER'(经理)的最低工薪 selecta.dname,min(a.sal)from(selectd.dname,e.salfromempe,deptd wheree.deptno=d.deptnoande.job='MANAGER')a groupbya.dname; --18.列出所有员工的年薪,按照年薪由低到高排序 selecte.ename,e.sal*12fromempeorderbye.sal; --19.查出每个员工的上级领导,并且求出这些主管的工资超过3000selecte1.ename本人姓名,e2.ename领导姓名,e2.sal领导工资fromempe1,empe2 wheree1.mgr=e2.empno ande2.sal>3000; --20.求出部门名称中带有'S'的部门员工的工资合计,部门人数selectd.dname部门名称,t2.su工资合计,t2.co部门人数from(selecte.deptno,sum(e.sal)su,count(e.empno)co fromempe groupbye.deptno)t2, deptd wheret2.deptnoin(selectd.deptnofromdeptdwhered.dnamelike'%S%') andd.deptno=t2.deptno;--21.给任职日期超过10年的员工加薪10% updateemp setsal=sal*1.1 whereempnoin(selectt1.empnofrom(selecte.empno, round(months_between(sysdate,e.hiredate)/12)ro fromempe)t1 wheret1.ro>10); --1.列出所有雇员的姓名及其上级的姓名 selecte1.ename,e2.enamefromempe1,empe2wheree1.mgr=e2.empno; --2.列出入职日期早于其直接上级的所有雇员 selecte1.empno员工号, e1.ename员工姓名, e1.hiredate入职日期, e2.ename上级姓名, e2.hiredate上级入职日期 fromempe1,empe2 wheree1.mgr=e2.empnoande1.hiredate wheree.deptno=d.deptnoande.job='CLERK'; --4.列出各种工作类型的最低薪金,并使最低薪金大于1500selecte.job,min(e.sal) fromempe groupbye.job havingmin(e.sal)>1500;--5.查询从事"SALES"(销售)工作的雇员的姓名(假定不知道销售部的部门编号)selecte.enamefromempewheree.job='SALESMAN'; --6.列出从事同一种工作但属于不同部门的雇员的不同组合selectdistincte.job,d.dnamefromempe,deptd wheree.deptno=d.deptnoorderbyjob; --7.列出各个部门的MANAGER(经理)的最低薪金 selecta.dname,min(a.sal)from(selectd.dname,e.salfromempe,deptd wheree.deptno=d.deptnoande.job='MANAGER')agroupbya.dname; --8.列出部门号为20和30的雇员名称,部门名称和薪金selecte.ename,e.sal,d.dnamefromempe,deptd where(e.deptnoin(20,30))ande.deptno=d.deptno;--9.显示雇员姓名,根据其服务年限,将最老的雇员排在最前面(拓展: 查询那个最老雇员的信息) selecte.ename,e.hiredatefromempeorderbye.hiredate;selectb.ename,b.empno,b.dname,b
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- oracle 初学者 必备 scott 用户 脚本 创建 结构 函数 练习 实例