Oracle查询题

Oracle查询题

1.查询计算机系职称为教授或副教授的男教师信息，显示姓名，职称信息。

select tname “姓名”,trank “职称” from teacher where trank in(‘副教授’,‘教授’)
and tgender=‘男’ and tdept=(select did from department where dname=‘计算机系’);

2.查询’JAVA程序设计’课程日期最早的3名同学的姓名和学号信息，如果日期相同则按照姓名拼音先后顺序。

select sid “学号”,sname “姓名” from
(select student.sid,sname from student inner join register on student.sid=register.sid
where cid=(select cid from course where cname=‘JAVA程序设计’)
order by rdate asc,nlssort(sname,‘NLS_SORT=SCHINESE_PINYIN_M’) asc) where rownum<4;

3.查询每门课程的最低分的同学的姓名，学号，成绩以及课程编号信息。

select sid “学号”,sname “姓名”,score “成绩”, cid “课程编号”
from register sc natural join
(select cid, min(score) score
from register group by cid order by cid)
natural join student;

4.查询每门课程的最低分的同学的姓名，学号，成绩以及课程名称信息。

select sid “学号”,sname “姓名”,score"成绩",cname “课程名称”
from register sc natural join
(select cid, min(score) score
from student natural join register
group by cid order by cid asc)
natural join student
natural join course;

5.查询每个学生各门课程的平均成绩，显示学生学号，姓名，
平均成绩以及参加考试的门数，没有参加任何课程学习的学生也予显示。

select student.sid “学号”,sname “学生姓名”,nvl(round(avg(score),2),0) “平均成绩”
,count(register.sid) “考试门数”
from register right join student
on register.sid=student.sid group by
student.sid,sname order by student.sid asc;

6.查询每门课程的平均分，显示课程名称，平均成绩信息，并按照平均成绩倒序排列。

select cname “课程名称”,round(avg(score),2) “平均成绩”
from register natural join course
group by cname
order by “平均成绩” desc;

7.查询每门课程的平均分，显示课程名称，平均成绩信息，
只显示平均成绩高于75分以上的课程，并按照平均成绩倒序排列。

select cname “课程名称”,avg(score) “平均成绩”
from register natural join course
group by cname
having avg(score)>=75
order by “平均成绩” desc;

8.查询每个姓氏的学生人数，显示姓氏，人数信息(假设没有复姓)。

select substr(sname,1,1), count(sid) from student
group by substr(sname,1,1);select substr(sname,1,1), count(sid)
from student group by substr(sname,1,1);

9.查询每个月份出生的学生人数，显示月份，出生人数信息，即便没有学生出生的月份也要显示。

select to_char(sbirth,‘mm’) “月份” ,count(1) “人数”
from student
group by to_char(sbirth,‘mm’)
order by 人数 desc;

10.查询男生比女生多的专业，显示专业编号，男、女生人数。

select mname
from major join student on student.smajor=major.mid
where mid in
(select mid
from student join major on student.smajor=major.mid
group by mid
having sum(case when sgender=‘男’ then 1 else 0 end)> sum(case when sgender=‘女’ then 1 else 0 end))
group by mname;