SQL面试题--(26~46)

--26、查询每门课程被选修的学生数 
select sc.cid,cname,count(*) 学生数
from sc left join course
on sc.cid = course.cid
group by sc.cid ,cname 

--27、查询出只选修了一门课程的全部学生的学号和姓名 
select sid,sname from 
(
       select sc.sid,student.sname,count(*) cnt from sc,student
       where sc.sid = student.sid(+)
       group by sc.sid,student.sname
) t
where t.cnt = 1

--28、查询男生、女生人数 
select sum(decode(Ssex,'男',1,0)) 男生人数,
       sum(decode(Ssex,'女',1,0)) 女生人数 
from student

--29、查询姓“张”的学生名单 
select sid,sname from student
where sname like '张%'

--30、查询同名同性学生名单，并统计同名人数 
select t1.sid,t1.sname,count(*)+1 人数 from student t1,student t2
where t1.sname = t2.sname 
and t1.Ssex = t2.Ssex 
and t1.sid != t2.sid
group by t1.sid,t1.sname
order by t1.sname 

--32、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列 
select cid,to_char(round(avg(score),2),'fm999.00')平均分
from sc
group by cid
order by avg(score) asc,cid desc    

--33、查询平均成绩大于85的所有学生的学号、姓名和平均成绩 
select sc.sid,sname,avg(score) 平均成绩 from sc,student
where sc.sid = student.sid(+)
group by sc.sid,sname
having avg(score) > 85    

--34、查询课程名称为“数据库”，且分数低于60的学生姓名和分数 
select sname,score from sc,course,student
where sc.sid = student.sid(+)
and course.cid = sc.cid
and course.cname = '数据库'
and score < 60 ;

--35、查询所有学生的选课情况； 
select sc.sid,sname,count(*) 选课数 from sc,student
where sc.sid = student.sid(+)
group by sc.sid,sname
order by sc.sid asc

 --36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数； 
select sname,course.cid,score
from sc,student,course
where sc.sid = student.sid(+)
and course.cid = sc.cid
and score >  70    

--37、有查询不及格的课程，并按课程号从大到小排列 
select distinct cid from sc
where score < 60
order by cid    

--38、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名； 
select sc.sid,sname from sc,student
where sc.sid = student.sid
and  score > 80
and cid = '003'    

--39、求选了课程的学生人数 
select sc.cid,cname,count(*) 选修人数 from sc,course
where sc.cid = course.cid
group by sc.cid,cname    

--40、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩 
select sname,score from sc,student,course,teacher
where sc.sid = student.sid(+)
and sc.cid = course.cid
and course.tid =  teacher.tid
and teacher.tname = '叶平'
and rownum = 1

--41、查询各个课程及相应的选修人数 
select sc.cid,cname,count(*) 选修人数 from sc,course
where sc.cid = course.cid
group by sc.cid,cname     

--42、查询不同课程成绩相同的学生的学号、课程号、学生成绩 
select distinct(sc1.cid),sc1.sid,sc1.score from sc sc1,sc sc2
where sc1.score = sc2.score
and sc1.sid = sc2.sid
and sc1.cid != sc2.cid  

--43、查询每门功成绩最好的前两名 
select sc.sid,sname,score 
from sc,course,student
where sc.cid = course.cid
and sc.sid = student.sid(+)
and rownum <= 2    

--44、统计每门课程的学生选修人数（超过10人的课程才统计）。
--要求输出课程号和选修人数，查询结果按人数降序排列，
--查询结果按人数降序排列，若人数相同，按课程号升序排列
select cid,count(distinct(sid)) 选修人数
from sc
group by cid
order by count(distinct(sid)) desc,cid asc  

--45、检索至少选修两门课程的学生学号 
select sid,count(*) cnt
from sc
group by sid
having count(*) >= 2

--46、查询全部学生都选修的课程的课程号和课程名 
select cid,cname from
(
select sc.cid cid,cname,count(sid) cnt,t.studentCnt studentCnt
from sc,course,(select count(distinct(sid)) studentCnt from sc) t
where sc.cid = course.cid
group by sc.cid,cname,studentCnt
) b
where b.cnt = b.studentCnt