自己做过的oracle查询题目:
问题:
1, 找出没选过“黎明”老师的所有学生姓名。
2, 列出2门以上(含2门)不及格学生姓名及平均成绩。
3, 既学过1号课程又学过2号课所有学生的姓名。
4, 查询每门功课成绩最好的前两名
5, 查询出只选修了一门课程的全部学生的学号和姓名
6, 查询课程编号“c002”的成绩比课程编号“c001”课程低的所有同学的学号、姓名
7, 查询和“s001”号的同学学习的课程完全相同的其他同学学号和姓名
8,查询选修‘张三’老师所授课程中成绩最好的学生姓名,课程编号和成绩。
9, 查询所有学生都选修的课程编号和课程名称(选作)
10, 查询学完所有课程的学生学号和姓名(选作)
自己的答案:
--1题 找出没选过“黎明”老师的所有学生姓名
select s.sname from s where sno notin(select t1.sno from sc t1 join c t2 on t1.cno = t2.cno where t2.cteacher='黎明')
--2题 列出2门以上(含2门)不及格学生姓名及平均成绩
select s.sname,t1.avgfen from s,(selectsc.sno,avg(scgrade) as avgfen from sc where sc.sno in (select sno from sc wherescgrade<60 group by sno having count(cno)>=2) group by sc.sno) t1 wheret1.sno = s.sno
--3题 既学过1号课程又学过2号课所有学生的姓名
--使用intersect关键字
select sname from s,sc
where s.sno=sc.sno and sc.cno =(select cnofrom c where c.cname='高数')
intersect
select sname from s,sc
where s.sno=sc.sno and sc.cno =(select cnofrom c where c.cname='化学');
--4题 查询每门功课成绩最好的前两名
select s.sname,s.sno,t.cno from (selectsno,cno,scgrade,row_number() over(partition by cno order by scgrade desc) asrowno from sc) t ,s where t.sno = s.sno and rowno<3
--用over partition by 我就可以查询到每位员工本来的具体信息和它所在部门的总工资:
--select name,dept,salary,sum(salary) over(partition by dept) total_salary from salary;
--用goup by 就没办法做到这点,只能查询到每个部门的总工资:
--select dept,sum(salary) total_salary fromsalary group by dept
--第五题 查询出只选修了一门课程的全部学生的学号和姓名
select s.sno,s.sname from s where s.snoin(select sc.sno from sc group by sc.sno having count(sc.cno)=1)
--第六题 查询课程编号“c002”的成绩比课程编号“c001”课程低的所有同学的学号、姓名
--感觉这一题有歧义,可能比的是一个学生的2001成绩比2002成绩高,也肯能比的是2001成绩比2002成绩高德学生
--同一学生的比较
select s.sno,s.sname
from s, (select s.sno,scgrade from s,scwhere s.sno=sc.sno and sc.cno='2001') r1,(select s.sno,scgrade from s,sc wheres.sno=sc.sno and sc.cno='2002') r2
where r1.sno=r2.sno and s.sno = r1.sno ands.sno = r2.sno and r1.scgrade > r2.scgrade
group by s.sno,s.sname;
--不同的学生
select sno from sc where cno = 'c001' andsc.score >any (select score from sc where cno = 'c002');
--第七题查询和“s001”号的同学学习的课程完全相同的其他同学学号和姓名
--下面这个不对
select distinct s.sno,s.sname
from sc,s
where s.sno=sc.sno and cno in (select cnofrom sc where sno='1001');
--这个是正确的
--使用了自连接的思想
select s.sno,s.sname from s where s.sno in(SELECT a.sno FROM sc a,sc b WHERE a.sno<> '1001' AND b.sno = '1001' AND a.cno = b.cno GROUP BY a.sno HAVING COUNT(*) = (SELECT COUNT(*) FROM sc c WHERE c.sno = '1001'))
--第八题查询选修‘张三’老师所授课程中成绩最好的学生姓名,课程编号和成绩
--这个语句中rownum这里出现问题
select s.sname,c.cno,sc.scgrade
from s,c,sc
where s.sno=sc.sno and c.cno=sc.cno andc.cteacher='黎明' and rownum =1
group by c.cno,s.sname,sc.scgrade;
--自己写的
select t.sno,t.scgrade,t.cno,s.sname from(select t1.*,rownum rowno from(select sc.sno,sc.scgrade,sc.cno from sc where sc.cno=(select c.cno from cwhere c.cteacher = '黎明') order by sc.scgrade desc) t1) t ,s where s.sno = t.sno androwno<2
--第九题
--查询所有学生都选修的课程编号和课程名称(选作)
--有歧义,一种意思是该课程所有学生都选了,另一种意思所有学生都选过的课,至少每门课都被选了一次。
select c.cno,c.cname from c wherec.cno in (select sc.cno from sc group bysc.cno having count(*)>=1)--另一种意思所有学生都选过的课,至少每门课都被选了一次。
select c.cno,c.cname from c where c.cnoin(select cno from sc group by sc.cno having count(*)=(select count(*) from s))
--第十题
-- 查询学完所有课程的学生学号和姓名(选作)
select s.sno,s.sname from s where s.sno in(select sc.sno from sc group by sc.sno having count(*) = (select count(*) fromc))