oracle查询测试题

自己做过的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))