oracle完整练习题

最新推荐文章于 2022-03-29 15:20:00 发布
小马哥哥成才记
最新推荐文章于 2022-03-29 15:20:00 发布
阅读量593
点赞数 2
分类专栏： oracle
本文链接：https://blog.csdn.net/dxdxdx11/article/details/108155325
版权
oracle 专栏收录该内容
5 篇文章 0 订阅
订阅专栏
在这里插入代码片**```
**表可直接导入:****
create table CLASSINFO
(
  CLASSID     NUMBER not null,
  CLASSNUMBER VARCHAR2(20) not null,
  CTEACHERID  NUMBER not null,
  CLASSGRADE  VARCHAR2(2) not null
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
alter table CLASSINFO
  add constraint PK_CLASSID primary key (CLASSID)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
alter table CLASSINFO
  add constraint CK_CLASSGRADE
  check (ClassGrade in('S1','S2','Y2'));

create table STUDENTEXAM
(
  EXAMID      NUMBER not null,
  EXAMNUMBER  VARCHAR2(32) not null,
  ESTUID      NUMBER not null,
  EXAMSUBJECT VARCHAR2(20) not null,
  EXAMRESULT  NUMBER
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
alter table STUDENTEXAM
  add constraint PK_EXAMID primary key (EXAMID)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
alter table STUDENTEXAM
  add constraint CK_EXAMRESULT
  check (EXAMRESULT>=0 and EXAMRESULT<=100);


create table STUDENTINFO
(
  STUID       NUMBER not null,
  STUNUMBER   VARCHAR2(10) not null,
  STUNAME     VARCHAR2(32) not null,
  STUAGE      NUMBER,
  STUSEX      VARCHAR2(3) default '男' not null,
  STUCARD     VARCHAR2(20),
  STUJOINTIME DATE not null,
  STUADDRESS  VARCHAR2(50),
  SCLASSID    NUMBER
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );


create table TEACHERINFO
(
  TEACHERID    NUMBER not null,
  TEACHERNAME  VARCHAR2(20) not null,
  TEACHERTEL   VARCHAR2(20),
  TEACHEREMAIL VARCHAR2(20)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
alter table TEACHERINFO
  add constraint PK_TEACHERID primary key (TEACHERID)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
alter table TEACHERINFO
  add constraint CK_TEACHEREMAIL
  check (TeacherEmail like '%@%');


insert into CLASSINFO (CLASSID, CLASSNUMBER, CTEACHERID, CLASSGRADE)
values (5, '070315', 4, 'S2');
insert into CLASSINFO (CLASSID, CLASSNUMBER, CTEACHERID, CLASSGRADE)
values (1, '07034', 1, 'S1');
insert into CLASSINFO (CLASSID, CLASSNUMBER, CTEACHERID, CLASSGRADE)
values (2, '07038', 2, 'S1');
commit;

insert into STUDENTEXAM (EXAMID, EXAMNUMBER, ESTUID, EXAMSUBJECT, EXAMRESULT)
values (6, 'S1_2007070801', 8, 'SQL', 70);
insert into STUDENTEXAM (EXAMID, EXAMNUMBER, ESTUID, EXAMSUBJECT, EXAMRESULT)
values (7, 'S1_2007070801', 8, 'Java', 78);
insert into STUDENTEXAM (EXAMID, EXAMNUMBER, ESTUID, EXAMSUBJECT, EXAMRESULT)
values (8, 'S1_2007070801', 9, 'SQL', 68);
insert into STUDENTEXAM (EXAMID, EXAMNUMBER, ESTUID, EXAMSUBJECT, EXAMRESULT)
values (9, 'S1_2007070801', 9, 'Java', 85);
insert into STUDENTEXAM (EXAMID, EXAMNUMBER, ESTUID, EXAMSUBJECT, EXAMRESULT)
values (1, 'S1_2007070801', 1, 'SQL', 80);
insert into STUDENTEXAM (EXAMID, EXAMNUMBER, ESTUID, EXAMSUBJECT, EXAMRESULT)
values (2, 'S1_2007070801', 1, 'Java', 56);
insert into STUDENTEXAM (EXAMID, EXAMNUMBER, ESTUID, EXAMSUBJECT, EXAMRESULT)
values (3, 'S1_2007070801', 4, 'SQL', 90);
insert into STUDENTEXAM (EXAMID, EXAMNUMBER, ESTUID, EXAMSUBJECT, EXAMRESULT)
values (4, 'S1_2007070801', 6, 'SQL', 95);
insert into STUDENTEXAM (EXAMID, EXAMNUMBER, ESTUID, EXAMSUBJECT, EXAMRESULT)
values (5, 'S1_2007070801', 6, 'Java', 80);
commit;

insert into STUDENTINFO (STUID, STUNUMBER, STUNAME, STUAGE, STUSEX, STUCARD, STUJOINTIME, STUADDRESS, SCLASSID)
values (8, '005', '孙悟空', 19, '男', '430106198801010001', to_date('02-03-2007', 'dd-mm-yyyy'), '花果山水帘洞', 1);
insert into STUDENTINFO (STUID, STUNUMBER, STUNAME, STUAGE, STUSEX, STUCARD, STUJOINTIME, STUADDRESS, SCLASSID)
values (9, '006', '大黄蜂', 20, '男', '430106198701010002', to_date('08-03-2007', 'dd-mm-yyyy'), '湖南汽车大世界', 2);
insert into STUDENTINFO (STUID, STUNUMBER, STUNAME, STUAGE, STUSEX, STUCARD, STUJOINTIME, STUADDRESS, SCLASSID)
values (11, '007', '貂蝉', 16, '女', '430120199138380438', to_date('12-03-2007', 'dd-mm-yyyy'), '马王堆博物馆', 5);
insert into STUDENTINFO (STUID, STUNUMBER, STUNAME, STUAGE, STUSEX, STUCARD, STUJOINTIME, STUADDRESS, SCLASSID)
values (12, '008', '关羽', null, '男', '430122198011111111', to_date('14-03-2007', 'dd-mm-yyyy'), '湖南长沙', 5);
insert into STUDENTINFO (STUID, STUNUMBER, STUNAME, STUAGE, STUSEX, STUCARD, STUJOINTIME, STUADDRESS, SCLASSID)
values (1, '001', '火云邪神', 18, '男', '430105198905022032', to_date('01-03-2007', 'dd-mm-yyyy'), '长沙市开福区', 1);
insert into STUDENTINFO (STUID, STUNUMBER, STUNAME, STUAGE, STUSEX, STUCARD, STUJOINTIME, STUADDRESS, SCLASSID)
values (2, '002', '东方不败', 20, '男', '430104198703012011', to_date('10-03-2007', 'dd-mm-yyyy'), '湖南湘潭', 2);
insert into STUDENTINFO (STUID, STUNUMBER, STUNAME, STUAGE, STUSEX, STUCARD, STUJOINTIME, STUADDRESS, SCLASSID)
values (4, '003', '小李飞车', 19, '男', '420106198812064044', to_date('02-03-2007', 'dd-mm-yyyy'), '广东佛山', 1);
insert into STUDENTINFO (STUID, STUNUMBER, STUNAME, STUAGE, STUSEX, STUCARD, STUJOINTIME, STUADDRESS, SCLASSID)
values (6, '004', '樱桃肉丸子', 18, '女', '420106198908061085', to_date('06-03-2007', 'dd-mm-yyyy'), '开福区长沙市岳麓区', 2);
commit;

insert into TEACHERINFO (TEACHERID, TEACHERNAME, TEACHERTEL, TEACHEREMAIL)
values (4, '刘备', '13507458168', 'lb@sina.com');
insert into TEACHERINFO (TEACHERID, TEACHERNAME, TEACHERTEL, TEACHEREMAIL)
values (1, '唐三藏', '13907311119', 'tsz@yahoo.com');
insert into TEACHERINFO (TEACHERID, TEACHERNAME, TEACHERTEL, TEACHEREMAIL)
values (2, '擎天柱', '13907315200', 'qtz@yahoo.com');
commit;







**--练习二：单行函数练习**
--1、查询所有学员从入学到今天，一共度过了多少天
select s.stuname, round((sysdate-stujointime))共度过天数 from studentinfo s; 
--2、查询每月2号入学的学员信息
select to_char(sysdate,'dd') from dual
 select s.*, to_char(stujointime,'dd') 日期 from studentinfo s where to_char(stujointime,'dd')=2;
--3、查询所有学员的毕业日期，假定按每个学员入学时间1年半之后将毕业。
select s.* ,(stujointime+548)  毕业日期 from studentinfo s;
--4、查询星期四入学的学员姓名，性别，年龄，班级编号
     select  s.stuname,s.stusex,s.stuage,c.classnumber from studentinfo s,classinfo c where 
     s.sclassid=c.classid and to_char(stujointime,'day')='星期四';
--5、查询‘2007-3-10’之前入学的学员信息
select * from studentinfo where to_char(stujointime,'yyyy"-"mm"-"dd')<'2007-03-10';
--6、查询所有学员姓名的长度
select s.stuname,length(s.stuname) 名字长度 from studentInfo s
--7、查询身份证中第9，10位为‘89’的学员信息（要求使用字符串函数）
select * from studentinfo where  instr(stucard,'8',9)=9 and instr(stucard,'9',10)=10;
--第二种方式
select * from studentinfo where substr(stucard,9,2)='89';
--8、修改班主任信息，将邮箱中的‘yahoo’替换为‘accp’
select * from teacherinfo ;
 select t.*,replace(teacheremail,'yahoo','accp') from teacherinfo t;
--9、查询所有班主任的邮箱的用户名
select substr(teacheremail,1 ,instr(teacheremail ,'@')-1) from teacherinfo; 
--10、查询所有班主任的邮箱的所属网站
select t.teachername, substr(teacheremail,instr(teacheremail,'@')+1,instr(teacheremail,'.')-5)from teacherinfo t;
--提示：如果邮箱为qtz@yahoo.com，用户名即qtz，所属网站即yahoo。可先查找出‘@’和‘.’的下标，再截取

--11、编写查询语句去掉字符串‘   爱你  要你  我  爱  你   ’中的空格
select replace('   爱你  要你  我  爱  你   ',' ','') from dual;
--12、计算每个学员身份证中字符‘1’出现的次数
select length(stucard)- length(replace(stucard,'1','')) from Studentinfo s;
--13、求小于-58.9的最大整数
select floor(-58.9) from dual;
--14、求大于78.8的最小整数
select ceil(78.8) from dual;
--15、求64除以7的余数
select mod(64,7）from dual;
--16、查询所有学员入学时间，要求显示格式为‘2007年03月02日’
select to_char(s.stujointime,'yyyy"年" mm"月" dd"日" ')from studentinfo s ;
--17、查询当前时间，要求显示格式为‘22时57:37’
select to_char(sysdate,'hh"时" mm":" ss') from dual;
--18、查询2007年入学的学员信息
select * from studentinfo s where to_char(stujointime,'yyyy')='2007';

--练习三：分组函数练习
1、查询所有学员的平均年龄（要求保留两位小数）
select trunc(avg(stuage),2)from studentinfo ;    --trunc()
--2、查询所有考试的总成绩
 select sum(se.examresult) 总成绩 from studentexam se ;
--3、查询SQL考试的最低分数
select * from studentexam;
select min(examresult)from studentexam se where se.examsubject='SQL' group by examsubject ;

--4、查询Java考试成绩最高的学员姓名
select * from studentinfo;
 select s.stuname from studentexam se ,studentinfo s where s.stuid=se.estuid and se.examsubject = 'Java'
  and se.examresult=(select max(examresult)from studentexam se where se.examsubject='Java'  ) ;
  --第二种方法
   select s.stuname from studentexam se ,studentinfo s where s.stuid=se.estuid 
    and se.examresult=(select max(examresult)from studentexam se where se.examsubject='Java' group by examsubject ) ;
    --第三种方法
     select * from
      (select s.stuname,se.examresult, row_number()over( order by examresult desc ) re from studentexam se,studentinfo s 
       where s.stuid=se.estuid and se.examsubject='Java') where re=1;
     
--5、查询学员‘火云邪神’一共参加了几次考试
select count(examsubject) from studentinfo s,studentexam se where s.stuid=se.estuid and s.stuname='火云邪神';
--6、查询各科目的平均成绩
select  se.examsubject,avg(se.examresult) from studentexam se group by se.examsubject;
--7、查询每个班级学员的最小年龄
select * from studentinfo;
select * from classinfo;
select * from  teacherinfo;
 select c.classnumber 班级, min(s.stuage) 最小年龄 from studentinfo s,classinfo c where s.sclassid=c.classid  group by c.classnumber ;
--8、查询考试不及格的人数
select count(*) from studentexam se where se.examresult<60;
--9、查询各学员的总成绩，要求筛选出总成绩在140分以上的
select sum(se.examresult) from studentexam se group by se.estuid having sum(se.examresult)>140;
--10、查询男女学员的平均年龄
select s.stusex, avg(s.stuage) from studentinfo s  group by s.stusex;
--11、查询每门功课的平均分，要求显示平均分在80分以上的(包括80分)
select se.examsubject, avg(examresult) from studentexam se group by examsubject having avg(examresult)>80  ;

--12、按班主任姓名分组，查所带班级的总成绩分（假定每个班主任只带一个班级）(提示：4表连接)
select t.teachername, nvl(sum(se.examresult),0)  from teacherinfo t,classinfo c, studentexam se,studentinfo s
 where s.sclassid=c.classid and c.cteacherid=t.teacherid and se.estuid(+)=s.stuid 
group by t.teachername;



--练习四：分析函数练习
--查询学员成绩，按成绩排序，并计算出名次
--1、要求不论成绩是否相同，名次是连续的序号

select s.stuname,se.examsubject,se.examresult,row_number()over( order by nvl(se.examresult,0) desc )rank from
 studentexam se,studentinfo s where se.estuid=s.stuid;

--2、要求成绩相等的排位相同，名次随后跳跃
select s.stuname,se.examsubject,se.examresult,rank()over( order by nvl(se.examresult,0) desc ) rank from
 studentexam se,studentinfo s where se.estuid=s.stuid;
--3、要求成绩相等的排位相同，名次是连续的
select s.stuname,se.examsubject,se.examresult,dense_rank()over( order by nvl(se.examresult,0) desc ) rank from
 studentexam se,studentinfo s where se.estuid=s.stuid;