sql练习题（6）

-- 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 

SELECT DISTINCT b.s_id,b.c_id,b.s_score FROM score a,score b WHERE a.c_id != b.c_id AND a.s_score = b.s_score

-- 42、查询每门课程成绩最好的前两名 
-- mark
SELECT a.s_id,a.c_id,a.s_score FROM score a
		WHERE (SELECT COUNT(1) FROM score b WHERE b.c_id=a.c_id AND b.s_score>=a.s_score)<=2 ORDER BY a.c_id

-- 43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
SELECT c_id 课程号, COUNT(1) 选修人数
FROM score
GROUP BY 课程号 
HAVING 选修人数 > 5
ORDER BY 选修人数 DESC, 课程号 ASC

-- 44、检索至少选修两门课程的学生学号 
SELECT s_id, COUNT(s_score) AS nums FROM score
GROUP BY s_id 
HAVING nums >= 2

-- 45、查询选修了全部课程的学生信息
SELECT s.* FROM student s 
WHERE s.s_id IN (
SELECT s_id  FROM score
GROUP BY s_id 
HAVING COUNT(1) = (SELECT COUNT(1) FROM course) )