oracle 查询

练习一：导入数据
将上机作业中school.dmp文件中的数据导入到数据库中。
该文件中包含4个表数据，分别是：StudentInfo表，TeacherInfo表，ClassInfo表，StudentExam表。
select * from studentinfo;


练习二：单行函数练习
1、查询所有学员从入学到今天，一共度过了多少天
select studentinfo.*, ceil(sysdate-stujointime) as time from studentinfo;


2、查询每月2号入学的学员信息
select * from
(select studentinfo.*,to_char(stujointime,'mm') from studentinfo ) where to_char(stujointime,'mm')=2

select * from studentinfo where to_char(stujointime,'dd')=2

select * from studentinfo where to_char(stujointime,'mm')=3
3、查询所有学员的毕业日期，假定按每个学员入学时间1年半之后将毕业。
select stuname,add_months(stujointime,18) from studentinfo
4、查询星期四入学的学员姓名，性别，年龄，班级编号
select stuname,stusex,stuage,sclassid from studentinfo where to_char(stujointime,'day')='星期四'


5、查询‘2007-3-10’之前入学的学员信息
select * from studentinfo where stujointime < to_date('2007-3-10','yyyy-mm-dd')
6、查询所有学员姓名的长度
select studentinfo.stuname,length(stuname) from studentinfo
7、查询身份证中第9，10位为‘89’的学员信息（要求使用字符串函数）

select * from studentinfo where substr(stucard,instr(stucard,'89'),2)='89'


8、修改班主任信息，将邮箱中的‘yahoo’替换为‘accp’
select * from teacherinfo
select replace(teacheremail,'yahoo','accp') from teacherinfo


9、查询所有班主任的邮箱的用户名
select substr(teacheremail,1,instr(teacheremail,'@')-1) from teacherinfo
10、查询所有班主任的邮箱的所属网站
提示：如果邮箱为qtz@yahoo.com，用户名即qtz，所属网站即yahoo。可先查找出‘@’和‘.’的下标，再截取
select substr(teacheremail,instr(teacheremail,'@')+1,instr(teacheremail,'.')-instr(teacheremail,'@')-1)
from teacherinfo

11、编写查询语句去掉字符串‘ 爱你 要你 我 爱 你 ’中的空格
select replace(' 爱你 要你 我 爱 你 ',' ','') from dual
12、计算每个学员身份证中字符‘1’出现的次数
select length(stucard)-length(replace(stucard,'1')) from studentinfo where stuid = 8
13、求小于-58.9的最大整数
select ceil(examresult) from studentexam where examresult<60


14、求大于78.8的最小整数
select floor(examresult) from studentexam where examresult>78.8
15、求64除以7的余数
select mod(64,7) from dual
16、查询所有学员入学时间，要求显示格式为‘2007年03月02日’
select to_char(stujointime,'yyyy"年"mm"月"dd日')期
17、查询当前时间，要求显示格式为‘22时57:37’
select to_char(sysdate,'hh"时"mi":"ss') from dual;
18、查询2007年入学的学员信息
select * from studentinfo where to_char(stujointime,'yyyy') = '2007'
triggers
练习三：分组函数练习
1、查询所有学员的平均年龄（要求保留两位小数）
select trunc(avg(stuage),2) from studentinfo ;
2、查询所有考试的总成绩
select examsubject, avg(examresult) from studentexam group by examsubject

3、查询SQL考试的最低分数
select * from studentexam
select examresult from ( select row_number()over(order by examresult)r,studentexam.* from studentexam where examsubject='SQL')
where r=1

4、查询Java考试成绩最高的学员姓名
select examresult from (select dense_rank()over(order by examresult desc)r ,studentexam.* from studentexam where examsubject='Java')
where r=1


5、查询学员‘火云邪神’一共参加了几次考试
select * from studentinfo
select * from studentexam

select count(*) from studentinfo,studentexam where studentinfo.stuid=studentexam.estuid and stuname='火云邪神'
6、查询各科目的平均成绩
select examsubject ,avg(examresult) from studentexam group by examsubject

7、查询每个班级学员的最小年龄

select sclassid,min(stuage) from studentinfo group by sclassid;
8、查询考试不及格的人数
select count(*) from studentexam where examresult<60
9、查询各学员的总成绩，要求筛选出总成绩在140分以上的
select * from studentexam
select * from (select sum(examresult)>140 from studentexam where group by estuid ) group by estuid

select * from ( select sum(examresult),estuid from studentexam
group by estuid )m where


select * from
(select sum(examresult) total,estuid from studentexam
group by estuid
)where total>140

10、查询男女学员的平均年龄
select stusex ,avg(stuage) from studentinfo group by stusex
11、查询每门功课的平均分，要求显示平均分在80分以上的(包括80分)

select * from (select avg(examresult)total from studentexam group by examsubject) where total>80

12、按班主任姓名分组，查所带班级的总成绩分（假定每个班主任只带一个班级）(提示：4表连接)


--查去来的值，类型也是一样的
select sclassid from studentinfo --这个一个真正的集合，值是唯一的
union
select classid from classinfo;

select sclassid from studentinfo
union all
select classid from classinfo;--这是别种一集合，没有过滤相同的值，没有对值进行

select * from studentexam
union all
select * from teacherinfo;


select sclassid from studentinfo
intersect
select classid from classinfo; -- 这是一个交集


select sclassid from studentinfo
minus
select classid from classinfo;


练习四：分析函数练习
查询学员成绩，按成绩排序，并计算出名次
1、 要求不论成绩是否相同，名次是连续的序号
select studentexam.* , row_number()over(order by examresult)rown from studentexam

2、 要求成绩相等的排位相同，名次随后跳跃
select studentexam.* ,rank()over(order by examresult)rank_ from studentexam
3、 要求成绩相等的排位相同，名次是连续的

select studentexam.* , dense_rank()over(order by examresult)denserank from studentexam