sql练习2

-- 7、查询学过"张三"老师授课的同学的信息
SELECT a.* from student a
,sc b,course c,teacher t
WHERE a.SID=b.SID AND 
b.CID=c.CID AND 
c.TID=t.TID AND
t.Tname='张三'
order by a.SID

-- 8、查询没学过"张三"老师授课的同学的信息
SELECT m.* from student m WHERE m.SID not in(SELECT a.SID from student a
,sc b,course c,teacher t
WHERE a.SID=b.SID AND 
b.CID=c.CID AND 
c.TID=t.TID AND
t.Tname='张三'
order by a.SID)ORDER BY m.SID ASC
-- 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
-- 方法一
select m.* from Student m where SID in
(
  select SID from
  (
    select distinct SID from SC where CID = '01'
    union all
    select distinct SID from SC where CID = '02'
  ) t group by SID having count(1) = 2
)
order by m.SID
-- 方法二
SELECT a.* from student a ,sc b WHERE
a.SID=b.SID and b.CID='01' AND EXISTS(
SELECT 1 from sc c where c.SID=b.SID and c.CID='02')ORDER BY
a.SID

-- 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
SELECT a.* from student a,sc b WHERE
a.SID=b.SID and b.CID='01' and not EXISTS
(SELECT 1 from sc c where c.SID=b.SID AND c.CID='02')ORDER BY
a.SID ASC
-- 11、查询没有学全所有课程的同学的信息
SELECT a.* from student a INNER JOIN sc b
on a.SID=b.SID GROUP BY a.SID HAVING count(b.CID)!=3
-- 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
SELECT DISTINCT b.* from student b,sc c
where b.SID=c.SID AND c.CID IN(SELECT CID from sc a where a.SID='01')
and b.SID <>'01'
-- 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
SELECT a.* from student a,sc b
WHERE a.SID=b.SID and b.CID IN(SELECT CID FROM sc WHERE sc.SID='01')GROUP BY a.SID
HAVING count(b.CID)=(SELECT count(CID) from sc m WHERE m.SID='01')AND
a.SID <>'01'
-- 14、查询没学过"张三"老师讲授的任一门课程的学生姓名
SELECT m.Sname from student m WHERE m.SID NOT IN(SELECT a.SID from student a,sc b,course c,teacher t
WHERE a.SID=b.SID AND c.CID=b.CID AND c.TID=t.TID
and t.Tname='张三')
-- 15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT s.SID,s.Sname,CAST(AVG(m.score) AS DECIMAL(18,2)) avg_score from student s ,sc m where s.SID in(
SELECT a.SID from student a,sc b
WHERE a.SID=b.SID AND b.score<60
GROUP BY a.SID HAVING count(b.CID)>=2
) and s.SID=m.SID GROUP BY s.SID
-- 16、检索"01"课程分数小于60，按分数降序排列的学生信息
SELECT s.* from student s WHERE s.SID IN(
SELECT SID from sc a WHERE a.CID='01' AND a.score<60
ORDER BY a.score DESC)