文章目录
已有三张表
student表:
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/6ffa591e8623976e1f10728ef1768e85.png)
选课表SC:
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/408abe1f3d50f003babeaf929c05cf14.png)
课程表Course:
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/62fdf1d0b60d5c0ccc6d39c3d06730ec.png)
1. 查询每个学生的学号、姓名、课程代号及选课成绩,不允许有重复列
SELECT Student.SID,Student.Sname,SC.CID,SC.Scores
FROM Student INNER JOIN SC ON Student.SID=SC.SID
2. 查询每个学生的学号、姓名、课程名及选课成绩
SELECT Student.SID,Student.Sname,Course.Cname,SC.Scores
FROM Student INNER JOIN SC ON Student.SID=SC.SID INNER JOIN Course ON SC.CID=Course.CID
3. 查询哪些课程没人选
SELECT Course.Cname
FROM Course LEFT OUTER JOIN SC ON course.cid = sc.cid
WHERE Scores IS NULL
4. 查询男生中没选课的学号、姓名、性别
SELECT Student.SID,Sname,Sex
FROM Student LEFT OUTER JOIN SC ON Student.SID=SC.SID
WHERE Sex=‘男’ AND Scores IS NULL
5.查询每个男生的选课门数,包括没有选课的男生,结果按照选课门数递减排序。
SELECT Student.Sname,COUNT(SC.Scores) AS 选课门数
FROM Student LEFT OUTER JOIN SC ON Student.SID=SC.SID
WHERE Sex=‘男’
GROUP BY Sname
ORDER BY 选课门数 DESC