查询平均分最高的学生学号和平均分，下列语句可以实现（）

实验三：复杂的单表查询 一、实验目的 1. 掌握SELECT语句的基本语法； 2. 掌握GROUP BY 子句的作用和使用方法； 3. 掌握HAVING子句的作用和使用方法； 4. 掌握聚集函数的作用和使用方法。 二、实验环境 已安装SQL Server 2005 或2012以及更高版本的计算机； 三、实验学时 2学时 四、实验要求 1. 了解单表的复杂查询和实现方式； 2. 完成实验报告； 五、实验原理 1. 在查询语句中用Group by子句进行分组； 2. 用Having子句对分组进行筛选。 3. 使用MAX（），MIN（），COUNT（），SUM（），AVG（）等函数在查询结果集中生成汇总值。 六、实验示例 l 针对S_T数据库 1．计算出英语课程的平均分,最高分和最低分。 select avg(grade) 平均分,max(grade) 最高分,min(grade) 最低分 from sc where cno='1' 2. 查询数据表SC的学生的学号、课程号及成绩，对于成绩按以下规定显示：成绩在60分以下显示为不及格；60-79之间显示为良好；80以上显示为优秀。 SELECT Sno,Cno,GradeLevel= case when Grade<60 then '不及格' when grade>=60 and Grade<=79 then '良好' when grade>=80 then '优秀' end FROM SC l 针对bookshop数据库 1、统计员工表中薪水在40000-60000之间的人数 select count(*) as 人数 from employee where salary between 40000 and 60000 2、计算图书编号为'20652'图书的平均销售单价 select avg(unit_price) from sale_item where book_no='20652' 3、找出订单总金额超过300的订单信息，并按订单总金额从大到小排序。 Select order_no,sum(qty*unit_price) tot_amt from sale_item group by order_no having sum(qty*unit_price)>300 order by sum(qty*unit_price) desc 4、查询订购了三种以上产品的订单号。 Select order_no ,count(*) from sale_item Group by order_no Having count(*)>=3 七、实验内容及步骤 请使用T-SQL 语句实现以下操作： l 针对S_T数据库 1. 显示所有院系（要求不能重复，不包括空值），并在结果集中增加一列字段“院系规模”，其中若该院系人数>=5则该字段值为“规模很大”，若该院系人数大于等于4小于5则该字段值为“规模一般”， 若该院系人数大于等于2小于4则该字段值为“规模稍小”，否则显示“规模很小”； 2. 显示学生信息表中的学生总人数及平均年龄，在结果集中列标题分别指定为“学生总人数，平均年龄”； 3. 显示选修的课程数大于3的各个学生的选修课程数； 4. 按课程号降序显示选修各个课程的总人数、最高成绩、最低成绩及平均成绩； 5. 列出有二门以上课程（含两门）不及格的学生的学号及不及格门数； l 针对bookshop数据库 1、在员工表employee中统计员工人数。 2、统计各部门员工的员工人数及平均薪水。 3、查询销售业绩超过300元的员工编号。 4、计算每种图书的销售数量总和与平均销售单价。 5、统计各部门不同性别、或各部门、或不同性别或所有员工的平均薪水。（在GROUP BY 子句中使用CUBE关键字） 6、统计各部门不同性别、或各部门或所有员工的平均薪水。（在GROUP BY 子句中使用ROLLUP关键字） 7、计算出一共销售了几种图书。 8、显示sale_item表中每种图书的订购金额总和，并且依据销售金额由大到小排列来显示出每一种图书的排行榜。 9、显示sale_item表中销售金额在300元以上的订单的销售数量，销售金额，并按降序排序。 10、统计各个类型图书的种类数量。 11、统计各个客户的消费金额。 12、统计各个客户的订单数量。 八、出现问题及解决办法 如：某些查询操作无法执行，如何解决？

ST[JDENT(SNO，SNAME，SEX，BIRTFtDAY，CLASS)

TEAC腿R(1FNO，TNAME．SEX．BIRTHDAY，PROFESSION，DEPARTMENT)

CO[JRSE(CNO，CNAME，TNO)

SCORE(SNO，CNO，DEGREE)

说明：学生关系模式中：SNO：学号；SNAME：姓名；SEX：性别；BIRTHDAY：出生口期；CI．ASS：班级：教师关系模式巾：TNO：教师号；TNAME：姓名；SEX：性别；BIRTHDAY：I叶{生口期；PRO—FESSION：职称；DEPARTMENT：所在系；

课程关系模式中：CNO：课程号；CNAME：课程名；TNO：任课教师号；

成绩关系模式中：SNO：学号；CNO：课程号；DEGREE：分数。

请用SQI。查询语句实现下列操作：

1)显示教师所有的单位，即不重复的DEPARTMENT列。

2)显示STIJDENT表中"95031．'班或性别为“女”的同学记求。

3)以CNO升序，DEGREE降序显示SCORE表的所有记录。

4)显示"9503l”班的学生人数。

5)显示SCORE表中至少有5名学生选修的并I：2 3 H：头的课程号的平均分。

6)显示最低分大于70，最高分小于90的SNO列。

7)显示选修“3一105”课程的成绩高于“109”号同学成绩的所有同学的记录。

8)显示SCORE表中选学多门课程的同学中分数为非最高分成绩的记录。

9)列出学号为“108'’的同学同年出生的所有学生的SNO、SNAME和BIRTHDAY。

10)显示“张三”教师任课的学生成绩。

填入几条示例数据完成下面查询。 1 ） 查询年龄大于 25 岁的女学生的学号和姓名。 2 ） 查询学生姓名和出生年份（并给出生年份取别名） 。 3 ）统计选修 2 号课程的人数。 4 ）查询选修了 2 号课程的平均分。 5) 统计计科系的学生人数。 6) 查询成绩在 60 到 80 之间的所有记录。 7) 查询成绩为 85 ， 86 或 88 的记录。 8 ）查询年龄不在 19 到 21 岁之间的学生姓名和年龄。 9) 查询所有不姓 “ 王 ” 的学生记录。 10) 查询所有姓“王”并且名字只有两个字的学生名字。 11 ）查询所有课程名是“ db_ ”开头的课程名字。 12) 以系别和年龄从大到小的顺序查询 Student 表中的全部记录。 12) 查询姓名的第二个字为“小”字的女生信息。 13) 查询成绩最高的三个学生的学号和成绩。 （取前几条数据用 top 数字） 14) 查询学生的成绩信息，先按学号升序排序，再按成绩降序排序。 填入几条示例数据完成下面查询。 1 ） 查询年龄大于 25 岁的女学生的学号和姓名。 2 ） 查询学生姓名和出生年份（并给出生年份取别名） 。 3 ）统计选修 2 号课程的人数。 4 ）查询选修了 2 号课程的平均分。 5) 统计计科系的学生人数。 6) 查询成绩在 60 到 80 之间的所有记录。 7) 查询成绩为 85 ， 86 或 88 的记录。 8 ）查询年龄不在 19 到 21 岁之间的学生姓名和年龄。 9) 查询所有不姓 “ 王 ” 的学生记录。 10) 查询所有姓“王”并且名字只有两个字的学生名字。 11 ）查询所有课程名是“ db_ ”开头的课程名字。 12) 以系别和年龄从大到小的顺序查询 Student 表中的全部记录。 12) 查询姓名的第二个字为“小”字的女生信息。 13) 查询成绩最高的三个学生的学号和成绩。 （取前几条数据用 top 数字） 14) 查询学生的成绩信息，先按学号升序排序，再按成绩降序排序。 利用课程数据库S-C: Student (Sno, Sname,Ssex ,Sage, Sdept )； Course (Cno ，Cname ,Cpno , Ccredit ); SC (Sno , Cno , Grade ) 完成下面查询。 1） 查询年龄大于25岁的女学生的学号和姓名。 2） 查询学生姓名和出生年份（并给出生年份取别名）。 3）统计选修2号课程的人数。 4）查询选修了2号课程的平均分。 5)统计IS系的学生人数。 6)查询成绩在60到80之间的所有记录。 7)查询成绩为85，86或88的记录。 8）查询所有姓“王”并且名字只有两个字的学生名字。 9) 查询学生的成绩信息，先按学号升序排序，再按成绩降序排序。 10)查询姓名的第二个字为“小”字的女生信息。

上机实践7 MySql数据查询操作（三） 实验目的: 1、掌握复杂数据查询操作、子查询操作； 2、掌握视图的创建及使用操作。 实验步骤: 在数椐库 course中建好的5张表( Department,student, teacher, course, choose)中输入测试数据（见文档-上机实践用表）,然后完成如下实验内容。 实验内容: 1、子查询 （1）查询成绩比学生“林慧”平均分低的所有学生信息。 （2）查询“会计系”所有学生的成绩（用子查询）。 （3）查询至少教授了一门课程的教师信息。 （4）查询没有授课的教师信息。 （5）查询课程平均分高于70分的课程及该课程的最高分，输出该课程名及最高分两列数据。 2、视图操作 （1）创建视图v_view1：列出学生表（student）中1998年以前（含1998年）出生学生的学号、姓名、性别、出生日期。 （2）创建视图v_view2：以课程编号分组统计选修的人数和平均分。。 （3）在视图v_view1中查询所有男生记录。 （4）向视图v_view1中插入如下两条记录： 201610101101 张超 男 1994-11-1 武汉 201610101102 刘红 女 1995-5-10 成都 （5）删除视图v_view1中学号为“1312054909”的学生记录 （6）删除视图v_view1 备注:查询数据库数据参考发布的文档－上机实践用表，按要求写出上述查询语句，将主要操作步骤及结果以图片、文字的方式填写在题目后，用“班级-姓名-上机7结果.doc”提交作业。

针对教材中的学生—课程数据库，用关系代数完成以下操作： 1、查询“CS”系所有学生的学号和姓名。 2、查询年龄超过18岁的男生的姓名及所在系。 3、查询年龄在18至20岁之间的学生信息。 4、查询不在“CS”系也不在“MA”系的学生的所有信息。 5、查询“CS”系所有学生的平均年龄。 6、查询没有先行课的课程名。 7、查询先行课为“6”号课程的课程信息。 8、查询间接先行课为“5”号课程的课程号及课程名。 9、查询选修了“1”号课程的学生选课信息。 10、查询成绩为90分以上的学生姓名和课程名。 11、对student及sc表做等值连接。 12、对student及sc表做自然连接。 13、查询“李勇”同学所选课程的平均成绩。 14、查询“操作系统”这门课的最高分及最低分。 15、查询被选修了的课程号及课程名。 16、查询没有选修课程的学生学号及姓名。 17、查询没有选修“1”号课程的学生姓名。 18、查询既选修了“数据结构”又选修了“操作系统”的学生姓名。 19、查询既选修了“2”号又选修了“4”号课程的学生学号。 20、查询选修了“2”号或“4”号课程的学生学号。 21、查询至少选修了“95002”学生所选课程的学生学号。 22、查询至少选修了一门其间接先行课为“7”号课程的学生姓名。 23、查询选修了所有课程的学生姓名。 24、查询“CS”系的所有学生的学号、姓名、课程名及成绩。 25、查询“CS”系选修课程的成绩在90分以上的所有女生的姓名、课程名和成绩。

学生成绩管理系统V4.0 某班有最多不超过30人（具体人数由键盘输入）参加期末考试，最多不超过6门（具体门数由键盘输入）。参考学生成绩管理系统V3.0，用二维数组作函数参数编程实现如下菜单驱动的学生成绩管理系统： （1）录入每个学生的学号、姓名和各科考试成绩； （2）计算每门课程的总分和平均分； （3）计算每个学生的总分和平均分； （4）按每个学生的总分由高到低排出名次表； （5）按每个学生的总分由低到高排出名次表； （6）按学号由小到大排出成绩表； （7）按姓名的字典顺序排出成绩表； （8）按学号查询学生排名及其各科考试成绩； （9）按姓名查询学生排名及其各科考试成绩； （10）按优秀（90~100）、良好（80~89）、中等（70~79）、及格（60~69）、不及格（0~59）5个类别，对每门课程分别统计每个类别的人数以及所占的百分比； （11）输出每个学生的学号、姓名、各科考试成绩、总分、平均分，以及每门课程的总分和平均分。 要求程序运行后先显示如下菜单，并提示用户输入选项： Management for Students' scores 1.Input record 2.Caculate total and average score of every course 3.Caculate total and average score of every student 4.Sort in descending order by score 5.Sort in ascending order by score 6.Sort in ascending order by number 7.Sort in dictionary order by name 8.Search by number 9.Search by name 10.Statistic analysis 11.List record 0.Exit Please Input your choice: 然后，根据用户输入的选项执行相应的操作。 请按照下面的定义及函数原型编程 #define MAX_LEN 10 /* 字符串最大长度 */ #define STU_NUM 30 /* 最多的学生人数 */ #define COURSE_NUM 6 /* 最多的考试科目数 */ int Menu(void); void ReadScore(long num[], char name[][MAX_LEN], float score[][COURSE_NUM], int n, int m); void AverSumofEveryStudent(float score[][COURSE_NUM], int n, int m, float sum[STU_NUM], float aver[STU_NUM]); void AverSumofEveryCourse(float score[][COURSE_NUM], int n, int m); void SortbyScore(long num[], char name[][MAX_LEN], float score[][COURSE_NUM], float sum[], float aver[], int n, int m, int (*compare)(float a, float b)); int Ascending(float a, float b); int Descending(float a, float b); void SwapFloat(float *x, float *y); void SwapLong(long *x, long *y); void SwapChar(char x[], char y[]); void AsSortbyNum(long num[], char name[][MAX_LEN], float score[][COURSE_NUM], float sum[], float aver[], int n, int m); void SortbyName(long num[], char name[][MAX_LEN], float score[][COURSE_NUM], float sum[], float aver[], int n, int m); void SearchbyNum(long num[], char name[][MAX_LEN], float score[][COURSE_NUM], float sum[], float aver[], int n, int m); void SearchbyName(long num[], char name[][MAX_LEN], float score[][COURSE_NUM], float sum[], float aver[], int n, int m); void StatisticAnalysis(float score[][COURSE_NUM], int n, int m); void PrintScore(long num[], char name[][MAX_LEN], float score[][COURSE_NUM], float sum[], float aver[],int n, int m) ; 输入格式: （ 1 ） 录入学生的人数： **要求输入数据格式为："%d" **提示信息为："Input student number(n<30):\n" 0 1 2 3 48 50 55 65 67 69 72 75 78 80 82 86 87 89 90 92 93 94 95 96 98 100 165 206 233 258 280 289 11003001 11003002 11003003 11003004 11003005 11003006 （ ）录入课程数： **要求输入数据格式为："%d" **提示信息为："input course ）录入每个学生的学号、姓名和考试成绩： **要求学号、姓名的输入数据格式为："%ld%s" **要求考试成绩的输入数据格式为："%f" student's id, name and score:\n" 输出格式： 计算每门课程的总分和平均分： **要求输出总分与平均分格式为："course %d:sum="%.0f,aver=%.0f\n&quot;" 计算每个学生的总分和平均分： **要求输出总分与平均分格式为："student 按成绩由高到低排出名次表： **要求学号、姓名的输出格式为："%ld\t%s\t" **要求成绩的输出格式为："%.0f\t" **要求总分及平均分的输出格式为："%.0f\t%.0f\n" **提示信息为："sort in descending order by 按成绩由低到高排出名次表: ascending 按学号由小到大排出成绩表： number:\n" 按姓名的字典顺序排出成绩表 dictionary name:\n" 按学号查询学生排名及其考试成绩： **如果未查到此学号的学生，提示信息为："not found!\n"； **如果查询到该学生 # 要求学号、姓名的输出格式为："%ld\t%s\t" 要求成绩的输出格式为："%.0f\t" 要求总分及平均分的输出格式为："%.0f\t%.0f\n" the number you want to search:\n" 按姓名查询学生排名及其考试成绩； 按优秀（90~100）、良好（80~89）、中等（70~79）、及格（60~69）、不及格（0~59）5个类别，统计每个类别的人数以及所占的百分比： **成绩="100输出格式为：&quot;%d\t%d\t%.2f%%\n&quot;；" **其他要求输出百分比格式为："%d-%d\t%d\t%.2f%%\n" **提示信息为： "for %d:\n" 输出每个学生的学号、姓名、考试成绩，以及课程总分和平均分 选择退出（菜单项0） **提示信息："end of program!" 菜单项选择错误（不在0-11之间） **提示信息："input error!\n" 下面是程序运行示例： input student 6↙ management for students' scores 1.input record 2.caculate total average score every 3.caculate 4.sort 5.sort 6.sort 7.sort 8.search 9.search 10.statistic analysis 11.list 0.exit please your choice: 1↙ 3↙ score: 11003001↙ lisi↙ 87↙ 82↙ 89↙ 11003005↙ heli↙ 98↙ 92↙ 90↙ 11003003↙ ludi↙ 75↙ 78↙ 80↙ 11003002↙ dumo↙ 48↙ 50↙ 67↙ 11003004↙ zuma↙ 65↙ 69↙ 72↙ 11003006↙ suyu↙ 100↙ 95↙ 94↙ 2↙ 1:sum="473,aver=79" 2:sum="466,aver=78" 3:sum="492,aver=82" 4:sum="165,aver=55" 5:sum="206,aver=69" 6:sum="289,aver=96" 4↙ sort suyu heli lisi ludi zuma dumo 5↙ number: 7↙ name: 8↙ search: 11003007↙ not found! 9↙ lili↙ 10↙ 1: 16.67% 60-69 70-79 80-89 90-99 2: 33.33% 0.00% 3: 11↙ 12↙ error! 0↙ end>

实验1：学生成绩管理系统V1.0 某班有最多不超过30人（具体人数由键盘输入）参加某门课程的考试，用一维数组作函数参数编程实现如下学生成绩管理： （1）录入每个学生的学号和考试成绩； （2）计算课程的总分和平均分； （3）按成绩由高到低排出名次表； （4）按学号由小到大排出成绩表； （5）按学号查询学生排名及其考试成绩； （6）按优秀（90~100）、良好（80~89）、中等（70~79）、及格（60~69）、不及格（0~59）5个类别，统计每个类别的人数以及所占的百分比； （7）输出每个学生的学号、考试成绩。 输入格式: （ 1 ）录入学生的人数： **输入数据格式："%d" **提示信息："Input student number(n<30):\n" 1 2 48 65 75 87 98 100 11003001 11003002 11003003 11003004 11003005 11003006 （ ）录入每个学生的学号和考试成绩： **输入数据格式："%ld%f" **提示信息："input student's id, name and score:\n" 输出格式： 菜单项的输出显示： management for students' scores 1.input record 2.caculate total average score of course 3.sort in descending order by 4.sort ascending number 5.search 6.statistic analysis 7.list 0.exit please input your choice: 计算课程的总分和平均分： **输出总分与平均分格式："sum="%.0f,aver=%.2f\n&quot;" 按成绩由高到低排出名次表： **输出格式："%ld\t%.0f\n" **提示信息："sort 按学号由小到大排出成绩表： number:\n" 按学号查询学生排名及其考试成绩： **如果未查到此学号的学生，提示信息："not found!\n" **如果查询到该学生，输出格式："%ld\t%.0f\n" 按优秀（90~100）、良好（80~89）、中等（70~79）、及格（60~69）、不及格（0~59）5个类别，统计每个类别的人数以及所占的百分比： **成绩="100输出格式：&quot;%d\t%d\t%.2f%%\n&quot;" **其他输出百分比格式："%d-%d\t%d\t%.2f%%\n" 程序运行结果示例： student 6↙ 1↙ score: 87↙ 98↙ 75↙ 48↙ 65↙ 100↙ 2↙ sum="473,aver=78.83" 3↙ sort 4↙ number: 5↙ the you want to search: 16.67% 60-69 70-79 80-89 90-99 7↙ 8↙ error! 0↙ end>