题目:查询出只选修两门课程的学生学号和姓名
有如下几张表:
Student
Course
SC
查询出只选修两门课程的学生学号和姓名
SELECT
student.SID, Sname
FROM
student,sc
WHERE
student.SID = sc.SID
GROUP BY student.SID
HAVING COUNT(1) = 2
SELECT
SID,
Sname
FROM
student
WHERE
SID IN (
SELECT
SID
FROM
sc
GROUP BY
SID
HAVING
COUNT(1) = 2
)
查询课程01成绩大于课程02的学生信息
SELECT *
from sc a, sc b,student
where a.SID = b.SID AND a.CID=01 AND b.CID=02 and a.score > b.score and a.SID = student.SID
查询平均成绩大于60的学生学号和平均成绩
SELECT SID 学生学号, AVG(score) 平均成绩 from sc GROUP BY SID HAVING AVG(score) > 60
查询所有学生的学号,姓名,选课数,总成绩
注意需要用到的是left jion ,因为要保证所有学生,如果只是通过student和sc中的sid相等,会缺失数据(当有个学生没有选课时)
select student.SID,student.Sname, COUNT(sc.CID),SUM(case when sc.score is null then 0 else sc.score END)
from student
LEFT JOIN sc
on student.SID = sc.SID
GROUP BY student.SID
查询姓“猴”的老师的个数
%:表示任意个数的字符
_:表示一个字符
select COUNT(TID) from teacher where Tname LIKE '张%'
查询没学过“张三”老师课的学生的学号、姓名
思路:先查出学过张三老师的课程,之后在student中再排除。
select student.SID, student.Sname from student where SID not in
(
select DISTINCT sc.SID
from course,teacher,sc
where course.TID = teacher.TID AND teacher.Tname='张三' and sc.CID = course.CID
)
7. 查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名(重点)
select *
from sc s1
join sc s2 on s1.SID = s2.SID
join student on s1.sid = student.SID