sql语句创建表查询等

--创建部门表
create table depts(
  did int primary key,
  dname varchar2(25)
);
--创建员工表
create table emps(
    eid int primary key,
    ename varchar2(50),
    salary int,
    status int,
    hiredate date,
    did int
);

--创建事故记录表
create table histcord(
  hid int primary key,
  eid int,
  cost int,
  count int
);
insert into depts values(1,'教务处');
insert into depts values(2,'学管处');
insert into depts values(3,'保卫处');

insert into emps values(1,'张三',11111,1,to_date('2020-1-13','yyyy-mm-dd'),1);
insert into emps values(2,'李四',10000,2,to_date('2020-1-23','yyyy-mm-dd'),2);
insert into emps values(3,'王五',22222,1,to_date('2020-3-13','yyyy-mm-dd'),3);
insert into emps values(4,'赵六',22552,2,to_date('2020-4-14','yyyy-mm-dd'),1);
insert into emps values(5,'天气',1500,2,to_date('2020-4-14','yyyy-mm-dd'),2);
insert into emps values(8,'s士大夫',1500,2,to_date('2020-8-14','yyyy-mm-dd'),3);
insert into emps values(9,'二分',1500,2,to_date('2020-4-24','yyyy-mm-dd'),3);

insert into histcord values(1,2,33,3);
insert into histcord values(2,1,23,1);
insert into histcord values(3,3,33,3);
insert into histcord values(4,4,36,2);
insert into histcord values(5,5,33,3);
insert into histcord values(6,6,43,2);
insert into histcord values(7,7,53,3);
select * from depts;
select * from emps;
select * from histcord;
--用sql往俩表中添加数据，让部门表和员工表关联起来
select a.dname,b.* from depts a,emps b where a.did=b.did;
--把教务处部门的薪资都加5元
update emps set salary= salary+5 where did =1;
select a.dname,b.salary+5  from depts a,emps b where a.did=b.did and a.dname='教务处';
--删除员工中薪资为1500块钱的数据
delete from emps where salary=1500;
--把人事部门的入职日期都延后5天入
update emps set hiredate=hiredate+5 where did=2;
--保卫处部门的员工，根据入职日期升序排列
select * from emps where did=3 order by hiredate asc;
--最近三天内添加事故的数据查出来
select * from emps a,histcord b where a.eid=b.eid  and a.hiredate between  trunc(sysdate) and  trunc(sysdate-3); --当前时间减三
select * from emps a,histcord b where a.eid=b.eid
select * from depts a left hiscord b on depts c where 
--查事故的总金额是多少？
select sum(cost) from histcord;
select sum(cost*count) from histcord;
--人事部门总共有过多少次事故和总共罚了多少钱
select sum(b.cost*b.count),sum(b.count) from emps a,histcord b where a.eid=b.eid and a.did=3
--把事故超过3次的员工查出来
select * from emps a,histcord b where a.eid=b.eid and b.count>2
select b.cost,b.count,a.* from emps a,histcord b where a.eid=b.eid and b.count>2
--事故次数最少的员工前五名
select * from (select a.ename,a.status,b.* from emps a,histcord b where a.eid=b.eid order by b.count asc) e where rownum<=5
--用视图功能写出这个sql，创建出来，能够用（人事部门总共有过多少次事故和事故罚款总共多少钱）
create view sss as
 select sum(b.cost*b.count) a ,sum(b.count) b  from emps a,histcord b where a.eid=b.eid and a.did=3
   
select * from sss
--可以正常执行，确定没问题，就把这个视图给删掉
drop view sss
      

select a.eid from emps a join depts b on a.did=b.did join histcord c on a.eid=c.eid where b.dname= '保卫处'
   and a.status=2

delete from emps where eid = 2;