多表的使用问题

2019独角兽企业重金招聘Python工程师标准>>>

--9: 查询所有同学的学号、姓名、选课数、总成绩；
--select st1.Sid,st1.Sname,sc2.score from Student st1,SC sc2
-- where st1.Sid = sc2.sid
 
select t.Sid 学号,t.Sname 姓名,COUNT(Sid) 选课数,SUM(score) 成绩
 from (select st1.Sid,st1.Sname,sc2.score from Student st1,SC sc2
   where st1.Sid = sc2.sid) t
  group by t.Sname ,t.Sid

--10: 查询全部学生都选修的课程的课程号和课程名
/*
select sc.cid,COUNT(sc.score)
 from SC sc,Student st
  group by sc.cid having COUNT(sc.sid) = COUNT(st.Sid)

select cid ,COUNT(cid) from SC sc group by sc.cid

select sc.cid,COUNT(sc.sid) from SC sc group by sc.cid
 having COUNT(sc.sid)=(select count(st.Sid) from Student st )

*/
select co.Cid,co.Cname
 from (select sc.cid from SC sc group by sc.cid
  having COUNT(sc.sid)=(select count(st.Sid) from Student st)) t
  join Course co
   on co.Cid = t.cid
   
--11: 查询课程名称为“数据库”，且分数低于60的学生姓名和分数
/*
select Cid from Course where Cname like '数据库'

select sc.sid ,sc.score,sc.cid
 from SC sc,Course co
  where (co.Cname='数据库' and sc.score<60)
  
select co.Cid
 from Course co
  where co.Cname like '数据库'
  
  select sc.sid , sc.score ,sc.cid
 from SC sc
  where sc.score<60 and sc.cid = (select co.Cid
   from Course co where co.Cname like '数据库')
  
*/  

     
select st.Sname ,sc.score
 from (select sc.sid , sc.score ,sc.cid from SC sc
   where sc.score<60 and sc.cid = (select co.Cid
    from Course co where co.Cname like '数据库')) sc
  join Student st
   on st.Sid = sc.sid
  

转载于:https://my.oschina.net/u/586106/blog/68237