Oracle查询(实用函数)

1、查询所有学员从入学到今天，一共度过了多少天

select (sysdate-stujointime) days from StudentInfo;

2、查询每月2号入学的学员信息

select * from StudentInfo where extract(day from stujointime)=2;

3、查询所有学员的毕业日期，假定按每个学员入学时间1年半之后将毕业

select add_months(stujointime,18),stuname from StudentInfo;

4、查询星期四入学的学员姓名，性别，年龄，班级编号

select stuname,stuage,classnumber from StudentInfo join ClassInfo on
StudentInfo.sclassid=ClassInfo.Classid where to_char(stujointime,‘day’)=‘星期四’;

5、查询‘2007-3-10’之前入学的学员信息

select * from studentinfo where to_char(stujointime,‘yyyy"-“mm”-"dd’)<‘2007-3-10’;

6、查询所有学员姓名的长度

select stuname,length(stuname) 姓名的长度 from StudentInfo;

7、查询身份证中第9，10位为‘89’的学员信息

select * from StudentInfo where instr(stucard,‘8’,9)=9 and instr(stucard,‘9’,10)=10;

8、修改班主任信息，将邮箱中的‘yahoo’替换为‘accp’.

select replace(teacheremail,‘yahoo’,‘accp’) from TeacherInfo;

9、查询所有班主任的邮箱的用户名

select teachername,teacheremail 邮箱的用户名 from TeacherInfo;

10、查询所有班主任的邮箱的所属网站

select substr(TeacherEmail,instr(TeacherEmail,’@’)+1,instr(TeacherEmail,’.’)-1-instr(TeacherEmail,’@’)) from TeacherInfo;

11、编写查询语句去掉字符串‘ 爱你 要你 我 爱 你 ’中的空格

select trim(’ 爱你 要你 我 爱 你 ‘) from dual;
select replace(’ 爱你 要你 我 爱 你 ‘,’ ') from dual;

12、计算每个学员身份证中字符‘1’出现的次数

select stuname,stucard,length(stucard)-length(replace(stucard,‘1’)) “1出现的次数” from StudentInfo;

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、查询所有学员入学时间

select to_char(stujointime,‘yyyy"年"mm"月"dd"日"’),stuname from studentinfo;

17、查询当前时间

select to_char(sysdate,‘hh24"时"mi":"ss’) from dual;

18、查询2007年入学的学员信息

select * from studentinfo where (to_char(stujointime,‘yyyy’))=‘2007’;

19、查询所有学员的平均年龄（要求保留两位小数）

select trunc(avg(stuage),2) from StudentInfo;

20、查询所有考试的总成绩

select sum(examresult) from StudentExam;

21、查询SQL考试的最低分数

select examresult SQL最低分数 from (select * from StudentExam where examsubject=‘SQL’
order by examresult asc) where rownum<2;

22、查询Java考试成绩最高的学员姓名

select stuname from (select * from StudentInfo join StudentExam on StudentInfo.Stuid=StudentExam.Estuid
order by examresult desc) where rownum<2;

23、查询各科目的平均成绩

select avg(examresult),examsubject from StudentExam group by examsubject;

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

select StudentInfo.Sclassid,min(stuage) 最小年龄 from StudentInfo group by sclassid;

25、查询各学员的总成绩，要求筛选出总成绩在140分以上的

select * from (select sum(examresult) T,StudentExam.estuid 学号 from StudentExam group by estuid) where T>140;

26、查询每门功课的平均分，要求显示平均分在80分以上的(包括80分)

select * from (select avg(examresult) T from StudentExam group by examsubject) where T>=80;

27、按班主任姓名分组，查所带班级的总成绩分（假定每个班主任只带一个班级）

select sum(StudentExam.Examresult) 总成绩分 from StudentInfo,TeacherInfo,ClassInfo,StudentExam
where StudentInfo.Stuid=StudentExam.Estuid and StudentExam.Estuid=ClassInfo.Classid
and ClassInfo.Classid=StudentInfo.Stuid and TeacherInfo.Teacherid=ClassInfo.Cteacherid group by TeacherInfo.Teachername;

28、要求不论成绩是否相同，名次是连续的序号

select StudentExam.Estuid,StudentExam.Examid,row_number() over(order by examresult desc) from StudentExam;

29、要求成绩相等的排位相同，名次随后跳跃

select StudentExam.Estuid,StudentExam.Examid,dense_rank() over(order by examresult desc) from StudentExam;

30、要求成绩相等的排位相同，名次是连续的

select StudentExam.Estuid,StudentExam.Examid,rank() over(order by examresult desc) from StudentExam;

31: 在infos2,infos3表中查询出所有男同学的信息（并集）

select * from infos2 where sex=‘男’
union
select * from infos3 where sex=‘男’

32: 查询infos2,infos3表中同时存在的男同学的信息（交集）

select * from infos2 where sex=‘男’
intersect
select * from infos3 where sex=‘男’

33: 查询infos2中存在，但是infos3中不存在的女同学的记录（补集）

select * from infos2 where sex=‘女’
minus
select * from infos3 where sex=‘女’

34.查询学过“001”并且也学过编号“002”课程的同学的学号、姓名

select s.stuid,s.stuname from tbl_student s,tbl_score sc1,tbl_score sc2
where sc1.stuid=sc2.stuid and s.stuid=sc1.stuid
and sc1.courseid=001 and sc2.courseid=002;

35.1981年出生的学生名单

select stuname, stuage
from tblstudent
where to_char(sysdate, ‘yyyy’) - stuage = ‘1981’;