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、查询所有班主任的邮箱的所属网站
提示：如果邮箱为[e-mail]，用户名即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