Oracle 练习作业10.1-1-2

原创 2013年12月05日 17:06:10
--一、现有学生表student,班级表classInfo,表结构如下:
--student表:sid学号,sname姓名,sex性别,birthday生日,age入学年龄,smoney缴费,cid班级ID
--classInfo表:班级编号cid,班级名称cname
--建表
drop table student cascade constraint;
drop table classinfo cascade constraint;

create table student(
       sid number(6) constraint pk_stu_sid primary key,
       sname varchar2(20),
       sex varchar2(4),
       birthday date,
       age number(2),
       smoney number(7,2),
       cid number(4)
);

create table classinfo(
       cid number(4) constraint pk_cls_cid primary key,
       cname varchar2(20)
);

alter table student add constraint fk_stu_cls_cid foreign key(cid) references classinfo(cid);

insert into classinfo values(1101,'工商管理');
insert into classinfo values(1102,'计算机');
insert into classinfo values(1103,'会计');
insert into classinfo values(1104,'药品');

insert into student values(110001,'小明','男',to_date('1999-9-9','yyyy-mm-dd'),10,11220.50,1103);
insert into student values(110002,'小张','女',to_date('1991-6-9','yyyy-mm-dd'),19,10000.00,1102);
insert into student values(110003,'小红','女',to_date('1996-7-3','yyyy-mm-dd'),14,8800.00,1102);
insert into student values(110004,'张三','男',to_date('1996-2-1','yyyy-mm-dd'),14,6600.00,1102);
insert into student values(110005,'小花','女',to_date('1994-6-7','yyyy-mm-dd'),16,11440.50,1101);
insert into student values(110006,'王五',null,to_date('1991-12-5','yyyy-mm-dd'),20,11440.50,1101);
commit;

select * from student; 
select * from classinfo;

--1、 查询入学年龄在18-20的女生或者未输入性别的学生信息,且年龄小的排在后面。
select * from student where age between 18 and 20 and (sex='女' or sex is null);

--2、 查询班级名称、学生姓名、性别、缴费(要求显示单位:元),相同班级的要放在一起,再按姓名升序排列。
select c.cname,s.sname,s.sex,s.smoney||'元' smoney from student s
join classinfo c on s.cid=c.cid order by c.cname,s.sname;

--3、 查询各班名称和人数。
select cname,count(*) from classinfo c join student s on c.cid=s.cid group by cname;

--4、 查询各班名称和人数,但人数必须不少于2,且人数多的放在前面。
select cname,count(*) from classinfo c join student s on c.cid=s.cid 
group by cname having count(*)>=2 order by count(*) desc;

--5、 查询1980年出生的有哪些学生。
select * from student where to_char(birthday,'yyyy')='1980';
select * from student where to_char(birthday,'yyyy')='1991';

--6、 查询男生和女生人数,没有输入性别的当作男生计算。
select sex,count(*) from (select nvl(sex,'男') sex from student) group by sex;

--7、 查询没有人员的班级。
select * from classinfo where cid not in(select distinct cid from student);

--8、 查询入学年龄在20以上的学生信息。
select * from student where age>20;
select * from student where age>=20;

--9、 查询班级平均入学年龄在20及以上的班级名称和平均年龄。
select * from classinfo c join (select cid,avg(age) avg_age from student group by cid) t 
on c.cid=t.cid where avg_age>=20;
select * from classinfo c join (select cid,avg(age) avg_age from student group by cid) t 
on c.cid=t.cid where avg_age>=15;


--二、现有 
--部门表bm:bid部门编号,bname名称
--人员表ry:rid人员编号,rname名称,bid部门编号
--工资表gz:rid人员编号,sal工资金额,rq发放日期
--建表

drop table bm cascade constraint;
drop table ry cascade constraint;
drop table gz cascade constraint;

create table bm(
       bid number(4) constraint pk_bm_bid primary key,
       bname varchar2(10)
);
create table ry(
       rid number(4) constraint pk_ry_rid primary key,
       rname varchar2(20),
       bid number(4)
);
create table gz(
       rid number(4),
       sal number(7,2),
       rq date
);

alter table ry add constraint fk_ry_bm_bid foreign key(bid) references bm(bid);
alter table gz add constraint fk_gz_ry_rid foreign key(rid) references ry(rid);

insert into bm values(1001,'销售');
insert into bm values(1002,'后勤');

insert into ry values(1101,'tom',1001);
insert into ry values(1103,'barbie',1001);
insert into ry values(1102,'jim',1002);

insert into gz values(1101,1200.5,sysdate);
insert into gz values(1101,1200.5,sysdate);
insert into gz values(1102,2000,sysdate);
insert into gz values(1103,5000,sysdate);

select * from bm;
select * from ry;
select * from gz;

--1、 查询员工姓名、部门名称和个人总工资。
select r.rname,b.bname,g.sal from bm b join ry r on b.bid=r.bid
join (select rid,sum(sal) sal from gz group by rid) g on g.rid=r.rid;

--2、 查询本月发了2笔以上工资的员工信息。
select * from ry r join 
(select rid,to_char(rq,'yyyy-mm') rq,count(sal) sals from gz group by rid,rq) t
on r.rid=t.rid where t.rq=to_char(sysdate,'yyyy-mm') and sals=2;

--3、 查询各部门的总工资。ry.bid group by bm.bname;
select bname,sum(sal) from bm b join ry r on b.bid=r.bid join gz g on r.rid=g.rid group by bname;

--4、 查询2009年8月份各部门工资最高的员工信息,显示部门名称、员工姓名和员工工资。
select * from
(select b.bid,max(g.sal) sal from bm b join ry r on b.bid=r.bid
join (select rid,to_char(rq,'yyyy-mm') rq,sum(sal) sal from gz group by rid,rq) g on g.rid=r.rid 
group by b.bid,b.bname) t1
join 
(select b.bid,b.bname,r.rid,r.rname,g.sal from bm b join ry r on b.bid=r.bid
join (select rid,to_char(rq,'yyyy-mm') rq,sum(sal) sal from gz group by rid,rq) g on g.rid=r.rid) t2
on
t1.bid = t2.bid and t1.sal=t2.sal;


--一、现有学生表student,班级表classInfo,表结构如下:
--student表:sid学号,sname姓名,sex性别,birthday生日,age入学年龄,smoney缴费,cid班级ID
--classInfo表:班级编号cid,班级名称cname
--1、 把新进的学生Tom,其学号为x009,年龄19,分配到5班。
--insert into student values(009,'tom',null,null,19,null,5);
insert into student values(009,'tom',null,null,19,null,1103);

--2、 把1班的前5个人分到2班。
select * from student;
update student set cid = 1102 
where sid in (select * from (select sid from student where cid=1101 order by sid) where rownum<=5);
select * from student;

--3、 把学生需要的缴费降低5%。
select * from student;
update student set smoney = smoney*0.95;
select * from student;

--4、 删除年龄不满18岁的学生信息。
select * from student;
delete from student where age < 15;
select * from student;

--5、 查询1990年以前出生的有哪些学生。
select * from student where to_char(birthday,'yyyy')<1990;
select * from student where to_char(birthday,'yyyy')<2000;

--6、 查询班级平均入学年龄在20及以上的班级名称和平均年龄。
select * from classinfo c join
(select cid,avg(age) from student group by cid) t
on c.cid=t.cid;

--二、现有
--部门表bm:bid部门编号,bname名称
--人员表ry:rid人员编号,rname名称,bid部门编号
--工资表gz:rid人员编号,sal工资金额,rq发放日期
--要求:用显式事务实现给销售部中工资>5000的员工降薪5%,同时,工资<1000的员工涨薪10%。
--select * from gz;
--update gz set sal=sal*0.95 where rid in (select r.rid from bm b join ry r on b.bid=r.bid join gz g on g.rid=r.rid where sal >2000);
--update gz set sal=sal*1.1 where rid in (select r.rid from bm b join ry r on b.bid=r.bid join gz g on g.rid=r.rid where sal <2000);
--select * from gz;

declare
  cursor c is
    select b.bid, r.rid, g.sal
      from bm b
      join ry r on b.bid = r.bid
      join gz g on g.rid = r.rid;
  v_temp c%rowtype;
begin
  open c;
  loop
    fetch c
      into v_temp;
    exit when(c%notfound);
    if (v_temp.sal > 5000) then
      update gz set sal = sal * 0.95 where rid = v_temp.rid;
      commit;
      dbms_output.put_line('工资大于5000降薪5%--' || v_temp.sal);
    elsif (v_temp.sal < 1000) then
      update gz set sal = sal * 1.1 where rid = v_temp.rid;
      commit;
      dbms_output.put_line('工资小于1000涨薪10%--' || v_temp.sal);
    end if;
  end loop;
  close c;
end;
版权声明:本文为博主原创文章,未经博主允许不得转载。

小白学习R语言——回归分析实例之男女身高体重

R读取数据 1,read .csv data 默认header=T 2,read .txt data 实例分析 1,数据输入 > c1 2,研究身高分布。 a,身高分布 b...
  • wang5448315
  • wang5448315
  • 2016年03月08日 14:13
  • 1618

Accuracy(准确率)、Precision(精确率)、Recall(回招率)、F1-measure(综合评价指标)

现在我先假定一个具体场景作为例子: 假如某个班级有男生80人,女生20人,共计100人.目标是找出所有女生. 现在某人挑选出50个人,其中20人是女生,另外还错误的把30个男生也当作女生挑选出来了....
  • zheng657555102
  • zheng657555102
  • 2015年07月23日 07:07
  • 173

网易2017春招编程题:调整队形 [python]

''' [编程题] 调整队形 时间限制:1秒 空间限制:32768K 在幼儿园有n个小朋友排列为一个队伍,从左到右一个挨着一个编号为(0~n-1)。 其中有一些是男生,有一些是女生,男生用'B'表示,...
  • qq_34617032
  • qq_34617032
  • 2017年11月16日 09:00
  • 46

将一个表中的总人数,男生人数,女生人数同时输出

例如: 新建一个系部表department(系部名称,总人数,男生人数,女生人数),用T-sql语句从学生表中采集相应信息添加到系部表中 介绍两种方法 1.用到了select into,要注意的...
  • dddd6666qq
  • dddd6666qq
  • 2013年05月09日 22:53
  • 1013

女生的痛,男生不懂。男生的痛,女生不懂

(1)       女生不吵了、不闹了、不叫了、不说话了,就是真的不爱了 。 女生说要离开,是伤心了,是你让他失望了.  女生明知道你们之间没有未来,却情愿留在你身...
  • dreamlovezone
  • dreamlovezone
  • 2014年12月13日 11:41
  • 333

18121 排排坐看电影

再一次能靠自己自己解题,前进!
  • shenshenzhiwen
  • shenshenzhiwen
  • 2017年05月31日 20:19
  • 512

先验概率、似然函数与后验概率

转载:http://www.cnblogs.com/wjgaas/p/4523779.html 来先举一个例子: 如果有一所学校,有60%是男生和40%是女生。女生穿裤子与裙子的数量相同;...
  • rolling0707
  • rolling0707
  • 2017年12月11日 20:30
  • 21

男生给女生最牛的告白

1、别以为平胸就说明自己有脑,别以为自己笨就一定丰满。基本上没有男人会笨到认为脑子和胸部有任何联系。2、爱不爱你?别tm一直问偶这个问题。什么也不说,你丫哭;犹豫了一会说,你丫说不坚决;脱口而出你丫又...
  • gemgin
  • gemgin
  • 2013年06月13日 14:20
  • 368

挖掘机技术哪家强

题目描述 有人问现实中为什么总是男生追求女生,反过来很少。实际上女生也是想主动追求男生的,但是世俗中对于主动追求男生的女生有种歧视,这样就使得女生不大敢主动追求男生。但是面对喜欢的男生,难道就不出手...
  • w_yqts
  • w_yqts
  • 2016年11月14日 15:53
  • 308

男生必学,与女生聊天技巧

原文网址(有修改):http://zhidao.baidu.com/daily/view?id=12104 女神有三宝:呵呵,在忙,去洗澡,是否让你瞬间丧失聊天能力,黯然神伤?并非女神太高冷,多...
  • ltt_1
  • ltt_1
  • 2016年05月02日 19:05
  • 439
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Oracle 练习作业10.1-1-2
举报原因:
原因补充:

(最多只允许输入30个字)