--一、現有學生表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 sjoin 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.bidjoin (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) ton 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.bidjoin (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) t1join?(select b.bid,b.bname,r.rid,r.rname,g.sal from bm b join ry r on b.bid=r.bidjoin (select rid,to_char(rq,'yyyy-mm') rq,sum(sal) sal from gz group by rid,rq) g on g.rid=r.rid) t2ont1.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) ton 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;
declarecursor c isselect b.bid, r.rid, g.salfrom bm bjoin ry r on b.bid = r.bidjoin gz g on g.rid = r.rid;v_temp c%rowtype;beginopen c;loopfetch cinto v_temp;exit when(c%notfound);if (v_temp.sal > 5000) thenupdate 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) thenupdate 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;

總結

以上是生活随笔為你收集整理的Oracle 练习作业10.1-1-2的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。