sql练习题 sql server语法实现

use lianxiSql;

-- 1.求出每门课程的学生人数
select COUNT(sid) from sc group by CId;

select Course.Cname as '课程',COUNT(*) as '人数' from Course,SC
where Course.CId = sc.CId group by Course.Cname;


-- 2.查询课程编号为 01 且课程成绩在 80 分及以上的学生的学号和姓名
select Student.SId,Student.Sname,sc.score from SC,Student
where Student.SId = sc.SId and sc.CId = '01' and sc.score >= 80;


-- 3.统计每门课程的学生选修人数（超过 5 人的课程才统计）
select COUNT(sid) from SC group by CId having COUNT(SId) >= 5;


-- 4.检索至少选修两门课程的学生学号
select sid from SC group by SId having COUNT(SId) >= 2; 


-- 5.选修了全部课程的学生信息
select * from Student where SId in (
	select Student.sid from Student,SC
	where Student.SId = SC.SId
	group by Student.sid having count(cid) = (select COUNT(cid) from Course)
);

-- 6.查询存在不及格的课程
select CId,Cname from Course 
where CId in (
	SELECT sc.CId FROM Course, sc
	where Course.CId = sc.CId
	GROUP BY sc.CId HAVING min(sc.score) < 60
);

-- 7.查询任何一门课程成绩在 70 分以上的学生姓名、课程名称和分数
select Student.Sname,Course.Cname,SC.Score from Student,SC,Course
where 
	Student.SId = sc.SId and 
	sc.CId = Course.CId and
	sc.score > 70;


-- 8.查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
select a.Sname,b.Cname,c.score from Student a 
left join SC c on a.SId = c.SId
left join Course b on b.CId = c.CId;


-- tip: 对于空值一般都不会使用 join ,使用 join 会直接忽略空值，需要使用 left joi