/*

环境：Microsoft SQL Server 2012

工具: Microsoft SQL Server Management Studio

数据库: Interview

数据表: grade

详情:

name    class    score

张三    语文    81

张三    数学    75

李四    语文    76

李四    数学    90

王五    语文    81

王五    数学    100

王五    英语    90

*/

--查询出每门课都大于80分的学生姓名:

--方法1

SELECT DISTINCT name FROM grade WHERE name NOT IN(SELECT DISTINCT name FROM grade WHERE score <= 80);

--方法2

SELECT name FROM grade GROUP BY name HAVING MIN(score) > 80;

--查询平均分大于80的学生的姓名：

--方法1

SELECT name FROM (SELECT COUNT(*) AS t,SUM(score) AS num,name FROM grade GROUP BY name) AS a WHERE a.num > 80*t;

--方法2

SELECT name,AVG(score) AS sc FROM grade g1 GROUP BY name HAVING AVG(score) > 80;

--查询出每门课都大于80分的学生姓名:

--方法1

SELECT DISTINCT name FROM grade WHERE name NOT IN(SELECT DISTINCT name FROM grade WHERE score <= 80);

----------------

SELECT DISTINCT name FROM grade WHERE score <= 80    　　　　　　　　-->查询结果:name 李四 张三

SELECT DISTINCT name FROM grade WHERE name NOT IN('张三','李四')    -->查询结果:name 王五

SELECT name FROM grade WHERE name NOT IN('张三','李四')        　　　-->查询结果:name 王五 王五 王五

--方法2

SELECT name FROM grade GROUP BY name HAVING MIN(score) > 80;

----------------

SELECT name FROM grade GROUP BY name    　　　　　　　　　　　　　　　　-->查询结果:name 李四 王五 张三

SELECT name FROM grade GROUP BY name HAVING AVG(score) < 80;    　　-->查询结果:name 张三 (平均成绩小于80的姓名)

--查询平均分大于80的学生的姓名：

--方法1

SELECT name FROM (SELECT COUNT(*) AS t,SUM(score) AS num,name FROM grade GROUP BY name) AS a WHERE a.num > 80*t;

----------------

SELECT COUNT(*) AS t,SUM(score) AS num,name FROM grade GROUP BY name

    -->查询结果:

                t    num    name

                2    166    李四

                3    271    王五

                2    156    张三

SELECT COUNT(*) AS t,SUM(score) AS num,class FROM grade GROUP BY class

    -->查询结果:

                t    num    class

                3    265    数学

                1    90     英语

                3    238    语文

--方法2

SELECT name,AVG(score) AS sc FROM grade g1 GROUP BY name HAVING AVG(score) > 80;

----------------

SELECT name,AVG(score) AS sc FROM grade AS g1 GROUP BY name HAVING AVG(score) > 80;

    -->查询结果:

                name    sc

                李四    83

                王五    90