6、查询每一门课程的课程名称，授课教师姓名，课程平均成绩；

    mysql> select avg(b.score) as AvgScore,c.CourseName,d.teaname from tblstudent a,tblscore b,tblcourse
    c,tblteacher d where a.stuid=b.stuid and b.courseid=c.courseid
    and c.teaid=d.teaid group by b.courseid ;
    +——————-+—————-+———-+
    | AvgScore | CourseName | teaname |
    +——————-+—————-+———-+
    | 67.7 | 企业管理 | 叶平 |
    | 79.14285714285714 | 马克思 | 赵志敬 |
    | 71.375 | UML | 裘千仞 |
    | 43.4 | 数据库 | 裘千尺 |
    | 70 | 逻辑电路 | 裘千仞 |
    | 56.4 | 英语 | 叶开 |
    | 72.5 | 电子电路 | 独孤求败 |
    | 92 | 毛泽东思想概论 | 孟星魂 |
    | 24.5 | 西方哲学史 | 白展堂 |
    | 79.25 | 线性代数 | 乔丹 |
    | 53 | 计算机基础 | 吕轻侯 |
    | 59.5 | AUTO CAD制图 | 花无缺 |
    | 97 | 平面设计 | 佟湘玉 |
    | 75 | Flash动漫 | 姚明 |
    | 85 | Java开发 | 阿紫 |
    | 76.66666666666667 | C#基础 | 叶平 |
    +——————-+—————-+———-+
    16 rows in set

    mysql>

    7、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
    Select StuId,StuName From tblStudent st
    Where (Select Count() From tblScore s1 Where s1.StuId=st.StuId And s1.CourseId=’001’)>0
    And
    (Select Count(    ) From tblScore s2 Where s2.StuId=st.StuId And s2.CourseId=’002’)>0;

    【执行情况】
    mysql> Select StuId,StuName From tblStudent st
    Where (Select Count() From tblScore s1 Where s1.StuId=st.StuId And s1.CourseId=’001’)>0
    And
    (Select Count(    ) From tblScore s2 Where s2.StuId=st.StuId And s2.CourseId=’002’)>0;
    +——-+———+
    | StuId | StuName |
    +——-+———+
    | 1001 | 周芷若 |
    | 1002 | 杨过 |
    | 1004 | 小龙女 |
    | 1009 | 韦小宝 |
    | 1010 | 康敏 |
    | 1013 | 郭靖 |
    +——-+———+
    6 rows in set

    方法二：
    【执行情况】
    mysql> SELECT C.stuid,C.stuName from tblStudent C where C.stuid in (SELECT A.stuid from
    tblScore A where A.CourseId=’001’) and C.stuid
    in(SELECT b.stuid from tblScore b where b.CourseId=’002’);
    +——-+———+
    | stuid | stuName |
    +——-+———+
    | 1001 | 周芷若 |
    | 1002 | 杨过 |
    | 1004 | 小龙女 |
    | 1009 | 韦小宝 |
    | 1010 | 康敏 |
    | 1013 | 郭靖 |
    +——-+———+
    6 rows in set

    8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；

    Select StuId,StuName From tblStudent s1
    Where (Select Score From tblScore t1 Where t1.StuId=s1.stuId And t1.CourseId=’001’)>
    (Select Score From tblScore t2 Where t2.StuId=s1.stuId And t2.CourseId=’002’);

    【执行情况】
    mysql> Select StuId,StuName From tblStudent s1
    Where (Select Score From tblScore t1 Where t1.StuId=s1.stuId And t1.CourseId=’001’)>
    (Select Score From tblScore t2 Where t2.StuId=s1.stuId And t2.CourseId=’002’);
    +——-+———+
    | StuId | StuName |
    +——-+———+
    | 1001 | 周芷若 |
    | 1009 | 韦小宝 |
    | 1010 | 康敏 |
    +——-+———+
    3 rows in set

    mysql>
    9、查询已经学完所有课程的同学的学号、姓名；
    Select StuId,StuName From tblStudent st
    Where (Select Count() From tblScore sc Where st.StuId=sc.StuId)=
    (Select Count(    ) From tblCourse)
    【执行情况】 无结果
    mysql> Select StuId,StuName From tblStudent st
    Where (Select Count() From tblScore sc Where st.StuId=sc.StuId)=
    (Select Count(    ) From tblCourse);
    Empty set
    10、查询课程补考过的学生学号，课程号；[同一门课程成绩存在两次代表补考]
    Select AcourseId,Astuid From tblStudent st,
    (Select Count(*) as CountNum,courseId as AcourseId,stuid as Astuid From tblScore sc group by sc.courseId,stuid) A where st.stuid=A.Astuid and A.CountNum>1;

    mysql> Select AcourseId,Astuid From tblStudent st,
    (Select Count(*) as CountNum,courseId as AcourseId,stuid as Astuid From tblScore sc group by sc.courseId,stuid) A where st.stuid=A.Astuid and A.CountNum>1;
    +———–+——–+
    | AcourseId | Astuid |
    +———–+——–+
    | 009 | 1011 |
    | 016 | 1013 |
    +———–+——–+
    2 rows in set