SQL学习（5）查询选修全部课程的学生

         在网上发现的一个经典问题，有标准的答案，分享下自己当时的思路。

         问题：查询选修全部课程的学生姓名。

                     学生信息表S：students(sno,sname,sex,age,sdept)

                     课程信息表C：courses(cno,cname,cdept,tname)

                     选课成绩表G：grades(sno,cno,score)

         我的思路：(如果有错误的地方，希望有人能提出来)

        1.通过grades表得出学号和对应选修课的数量。

           select sno,count(cno)as cnum from grades group by sno

        2. 通过courses表得出选修课的数量。

            with cte as (select  count(*)  as c from courses group by cno) select  count(*) as cnum from cte

        3.查找选修全部课程的学号。

           select sno from 结果1 where 结果1的cnum=结果2的cnum      

        4.查找选修全部课程的学生姓名。

           连接结果3和students表即可得到。   

 

         我的答案：

        with cte1 as (select sno,count(cno)as cnum from grades group by sno),

        with cte2 as (select  count(*)  as c from courses group by cno)

        select sname from students

        join cte1 on studenfts.sno=cte1.sno where cte1.sno in( 

                      select sno from cte1 where cnum=(select  count(*) as cnum from cte2)

                                                                                                            )

 

         标准答案：

         select sname  

         from students
         where not exists (

                          select *

                          from courses
                          where not exists(

                                 select *
                                 from grades
                                 where sno=students.sno  and cno=courses.cno

                                                         )

                                           )

         标准答案分析：1.查找学生所选的所有课程及成绩

                        select *from grades where sno=students.sno and cno=courses.cno

                     2.查找学生没有选修的课程

                        select * from courses where not exists(select...)

                     3.查找选修全部课程的学生姓名

                        select sname  from students  where not exists (select * from courses where not exists(select...))