SQL50题选解（oracle版）

SQL50题中选取以下编号题目：1、4、7、8、9、10、11、14、15、16、33、35、36、42 

1.查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数

 查询同时存在"01"课程和"02"课程的情况

SELECT  student.sname,student.sage,student.ssex,course.cname,sc.score    
from sc , student ,course
WHERE sc.sid IN
(
SELECT a.sid from 
(SELECT * from sc t WHERE t.cid='01') a,
(SELECT * from sc t WHERE t.cid='02') b
WHERE a.sid=b.sid AND a.score>b.score
)
AND sc.sid=student.sid(+) AND sc.cid=course.cid(+);

分析："01"课程和"02"课程同时存在，说明有公共字段，连接sc表和student表的字段为sid。

4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩 (没成绩的显示为 null)

方法1：分组统计出结果后，再去关联student表

SELECT a.*,b.qty,b.sum1 
from student a,
(
SELECT t.sid,COUNT(t.cid) qty,SUM(t.score) sum1 from sc t GROUP BY t.sid
) b
WHERE a.sid=b.sid(+);

方法2：表关联后直接分组

SELECT a.sid, COUNT(b.cid) qty,nvl(SUM(b.score),0) sum1
from student a,sc b
WHERE a.sid=b.sid(+)
GROUP BY a.sid
ORDER BY a.sid 

7. 查询没有学全所有课程的同学的信息

SELECT * from student t 
WHERE t.sid NOT IN(SELECT t.sid from sc t 
GROUP BY t.sid 
HAVING COUNT(distinct(t.cid))=(SELECT COUNT(t1.cid) from course t1) );

分析：找对立事件，排除学全所有课程的同学的SID

8. 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息

SELECT * from student t
WHERE t.sid IN(SELECT distinct(k.sid) FROM sc k WHERE k.score IS NOT NULL AND k.sid !='01' )     
AND t.sid NOT IN (SELECT k2.sid from sc k2 WHERE k2.cid NOT IN (SELECT DISTINCT(cid) from sc WHERE sc.sid='01' ));     

分析：找对立事件，没有一门课与‘01’同学相同，not in。双重否定。

9. 查询和" 01 "号的同学学习的课程完全相同的其他同学的信息

SELECT t.sid,COUNT(1) from sc t 
WHERE t.cid IN (SELECT cid FROM sc WHERE sid='01')
GROUP BY t.sid 
HAVING COUNT(1)=(SELECT COUNT(1)FROM sc WHERE sid='01')

分析：课程完全相同表示：课程个数一样；课程编号相互包含。这两个条件下，两个集合是相等的。

10. 查询没学过"张三"老师讲授的任一门课程的学生姓名

错误写法：cid not in 写在里面会出现，记录多一条情况。要以sid为判断标准。

SELECT * from sc
SELECT a.sname from student a WHERE a.sid  IN (
SELECT distinct(sid) FROM sc WHERE cid NOT IN (SELECT cid from course k WHERE k.tid IN(SELECT tid from teacher t WHERE t.tname='张三')
)
);

正确写法：

SELECT a.sname from student a WHERE a.sid NOT IN (
SELECT distinct(sid) FROM sc WHERE cid  IN (SELECT cid from course k WHERE k.tid IN(SELECT tid from teacher t WHERE t.tname='张三')
)
);

11. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

--复杂版
SELECT t.*,a.pingjun from student t ,
(
SELECT k.* from (
SELECT t.sid,SUM(CASE WHEN t.score<60 THEN 1 ELSE 0 END) ji,AVG(t.score) pingjun from sc t GROUP BY t.sid  
)k
WHERE k.ji>=2
) a
WHERE t.sid(+)=a.sid;

--精简版
select sc.sid, student.sname, AVG(sc.score) from student,sc
where 
    student.sid = sc.sid and sc.score<60
group by sc.sid,student.sname 
having count(*)>1;

14. 查询各科成绩最高分、最低分和平均分

SELECT t.cid,a.cname,MAX(t.score) 最高分,MIN(t.score) 最低分,AVG(t.score) 平均分,
SUM(CASE WHEN t.score>=60 THEN 1 ELSE 0 END)/COUNT(t.score) 及格率,
SUM(CASE WHEN t.score>=70AND t.score<80 THEN 1 ELSE 0 END)/COUNT(t.score) 中等率,
SUM(CASE WHEN t.score>=80AND t.score<90 THEN 1 ELSE 0 END)/COUNT(t.score) 优良率,
SUM(CASE WHEN t.score>=90 THEN 1 ELSE 0 END)/COUNT(t.score) 优秀率,
COUNT(t.sid) 选修人数
from sc t, course a
WHERE t.cid=a.cid(+)   
GROUP BY t.cid,a.cname
ORDER BY COUNT(t.sid) DESC,cid;   

15. 按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺

select t.*,dense_rank() over(partition by t.cid order BY t.score DESC) row_number from sc t; 

分组排列技巧

16. 查询学生的总成绩，并进行排名，总分重复时保留名次空缺

SELECT k.*,row_number() over ( order by k.he desc )
from 
(SELECT t.sid,SUM(t.score) he from sc t GROUP BY t.sid) k;

分析：先统计出结果后，在正常排序。

33. 成绩重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

SELECT * from student f WHERE f.sid IN
(
SELECT t.sid from sc t, 
(
SELECT a.cid,max(a.score) zuida
FROM course c,teacher d,sc a
WHERE d.tname='张三'
AND d.tid=c.tid
AND a.cid=c.cid
GROUP BY a.cid --max函数分成分组与不分组两种情况
)k
WHERE t.cid=k.cid AND t.score=k.zuida--匹配两个变量，锁定SID
);

分析：先统计出科目最高分对应的课程号和分数，到SC表中匹配出课程号和分数，锁定SID（可能有多个，因为成绩可能重复）

35. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

SELECT  a.sid,a.cid,a.score from sc a ,sc b
WHERE a.cid!=b.cid --不同学科
AND a.score=b.score
AND a.sid=b.sid --同一个人
GROUP BY a.sid,a.cid,a.score

分析：分组不用统计出数字，只要有这种情况即可。

36. 查询每门成绩最好的前两名

--对排名后数据在筛选
SELECT k.* FROM
(
select t.*, row_number()  over(partition by t.cid order BY t.score DESC) row_number from sc t
)k
WHERE  k.row_number<=2;

--另一种思路 b是用来比较的表，a是对比的表
SELECT a.cid,a.sid,a.score from sc a
WHERE (SELECT COUNT(1)FROM sc b WHERE a.cid=b.cid AND b.score>=a.score)<=2
ORDER BY a.cid ,a.score DESC

分析：思路2理解，a表和b表是同一张表，b表中比a表大的数最多只能有两个，说明a表中去前两名。

42. 查询本周过生日的学生  43. 查询下周过生日的学生

--查询本周过生日的学生
select sname,sage
from Student
where to_char(to_date(sage,'yyyy-mm-dd hh24:mi:ss'),'iw')=to_char(sysdate,'iw');
--查询下周过生日的学生
select sname,sage
from Student
where to_char(to_date(sage,'yyyy-mm-dd hh24:mi:ss'),'iw')=to_char(sysdate+7,'iw');