oracle公司部门划分,Oracle数据库4

1。--删除表droptablework;droptabledep;droptableemployee;--雇员createtableemployee(empidchar(3)primarykey,empnamevarchar2(10)notnull,--雇员姓名不能为空agenumber(2)check(agebetween18and55),--雇员的年龄介于18到55之间addressvarchar2(20),salnumber(5)check(salbetween1000and50000)--薪水取1000到50000之间整数);--部门createtabledep(deptnochar(3)primarykey,dnamevarchar2(20)notnull,--部门名不能为空mgrchar(3)referencesemployee(empid));--工作createtablework(empidchar(3)referencesemployee(empid),deptnochar(3)referencesdep(deptno),worktimenumber(1),primarykey(empid,deptno));②insertintoemployeevalues('E01','tom',35,'海淀',8000);insertintoemployeevalues('E02','dennis',24,'朝阳',5000);insertintoemployeevalues('E03','john',20,'海淀',4000);insertintoemployeevalues('E04','rick',41,'朝阳',7000);insertintoemployeevalues('E05','bill',45,'西城',7500);insertintoemployeevalues('E06','frank',35,'海淀',6000);insertintoemployeevalues('E07','jank',25,'东城',3000);insertintoemployeevalues('E08','mike',23,'东城',3500);insertintodepvalues('D01','人事','E04');insertintodepvalues('D02','信息','E01');insertintodepvalues('D03','销售','E05');insertintodepvalues('D04','财务','E06');insertintoworkvalues('E01','D02',6);insertintoworkvalues('E01','D03',1);insertintoworkvalues('E01','D04',2);insertintoworkvalues('E02','D01',2);insertintoworkvalues('E02','D04',0);insertintoworkvalues('E03','D02',0);insertintoworkvalues('E04','D01',4);insertintoworkvalues('E04','D02',1);insertintoworkvalues('E05','D03',2);insertintoworkvalues('E06','D04',4);insertintoworkvalues('E07','D04',1);insertintoworkvalues('E08','D03',2);③用SQL语句完成下列查询⑴求同时在三个部门工作的雇员姓名selectempnamefromemployeeawhere(selectcount(deptno)fromworkwhereempid=a。

empid)=3;⑵列出各部门的职工的工资总额，并按照总额大小降序排列selectb。dname,sum(a。sal)astotfromemployeea,depb,workcwherea。empid=c。empidandc。deptno=b。

deptnogroupbyb。dnameorderbytotdesc;⑶求和其部门经理住址相同的员工的姓名及经理姓名selectd。empname,c。leaderfrom(selectdeptno,empnameasleader,addressfromemployeea,depbwherea。

empid=b。mgr)c,employeed,workewherec。deptno=e。deptnoande。empid=d。empidandd。address=c。addressandc。leader=5;⑤删除部门工作年限为0的工作记录，若删除后有雇员没有任何部门工作，删除其雇员记录(PL/SQL和语句)deletefromworkwhereworktime=0;deletefromemployeeawhere(selectcount(*)fromworkwhereempid=a。

empid)=0;。

全部