MySQL数据库高级查询和多表查询(二)

机试测试试卷(MySQL数据库应用与开发)

注意：考试结束试卷必须交回，不交回试卷者成绩无效

题目：MySQL 数据库高级查询和多表查询

一、语言和环境

A、实现技术

SQL 语句练习

B、环境要求

Mysql5.7+Navicat

二、实验要求

1、创建名为 s_t 的数据库，参数全部使用 UTF-8

实验目的：

通过上机实验验证数据库的多表的高级查询操作。

实验内容：

1、将s_t数据库还原。

2、在s_t数据库中，完成以下多表连接查询的操作。

（1）查询计算机系学生的修课情况，要求列出学生的名字、所修课的课程号和成绩。

（2）查询“信息管理系”修了“计算机文化学”的学生姓名和成绩。

（3）查询所有选修了Java课程的学生情况，列出学生姓名和所在系。

（4）统计每个系的学生的考试平均成绩。

（5）统计计算机系学生每门课程的选课人数、平均成绩、最高成绩和最低成绩。

（6）查询与刘晨在同一个系学习的学生的姓名和所在的系。

（7）查询与“数据结构”在同一个学期开设的课程的课程名和开课学期。

（8）查询至少被两个学生选的课程的课程号。

（9）查询全体学生的选课情况，包括选修了课程的学生和没有选修课程的学生。

（10）查询没人选的课程的课程名。

（11）查询计算机系没有选课的学生，列出学生姓名和性别。

（12）统计计算机系每个学生的选课门数，包括没有选课的学生。

（13）查询信息管理系选课门数少于3门的学生的学号和选课门数，包括没有选课的学生。查询结果按选课门数递增排序。

（14）查询考试成绩最高的三个成绩，列出学号、课程号和成绩。

(15) 查询Java考试成绩最高的前三名的学生的姓名、所在系和VB考试

成绩。

（16）查询选课人数最少的两门课程（不包括没有人选的课程），列出课程号和选课人数。

（17）查询计算机系选课门数超过2门的学生中，考试平均成绩最高的前2名（包括并列的情况）学生的学号、选课门数和平均成绩。

（18）将计算机系的学生信息保存到#ComputerStudent局部临时表中。

（19）将选了Java课程的学生的学号及成绩存入永久表Java_Grade中。

（20）统计每个学期开设的课程总门数，将结果保存到永久表Cno_Count 表中

（21）利用例19题生成的新表，查询第2学期开设的课程名、学分和课程总门数。

3、在s_t数据库中，完成以下多表的高级查询操作。

（1）查询选了Java课程的学生的学号、姓名、所在系和成绩，并对所在系进行如下处理：“计算机系”：显示“CS”；“信息管理系”：显示“IM”；“通信工程系”：显示“COM”。

（2）查询“C001”课程的考试情况，列出学号和成绩，对成绩进行如下处理：如果成绩大于等于90，则在查询结果中显示“优”；如果成绩在80到89分之间，则在查询结果中显示“良”；如果成绩在70到79分之间，则在查询结果中显示“中”；如果成绩在60到69分之间，则在查询结果中显示“及格”；如果成绩小于60分，则在查询结果中显示“不及格”。

（3）统计每个学生的考试平均成绩，列出学号、考试平均成绩和考试情况，其中考试情况的处理为：如果平均成绩大于等于90，则考试情况为“好”；

如果平均成绩在80～89，则考试情况为“比较好”；如果平均成绩在70～79，则考试情况为“一般”；如果平均成绩在60～69，则考试情况为“不太好”；

如果平均成绩低于60，则考试情况为“比较差”。

（4）统计计算机系每个学生的选课门数，包括没有选课的学生。列出学号、选课门数和选课情况，其中对选课情况的处理为：如果选课门数超过4，则选课情况为“多”；如果选课门数在2~4，则选课情况为“一般”；如果选课门数少于2，则选课情况为“少”；如果学生没有选课，则选课情况为“未选”。并将查询结果按选课门数降序排序。

（5）查询与“刘晨”在同一个系学习的学生。

（6）查询考试成绩大于90分的学生的学号和姓名。

（7）查询计算机系选了“C002”课程的学生，列出姓名和性别。

（8）查询选修了“Java”课程的学生的学号和姓名。

（9）统计选了Java课程的这些学生的选课门数和平均成绩。

（10）查询选了“JAVA”课程的学生学号、姓名和JAVA成绩。

（11）查询选了“C004”号课程且成绩高于此课程的平均成绩的学生的学号和成绩。

（12）查询考试平均成绩高于全体学生的总平均成绩的学生的学号和平均成绩。

（13）查询没选“C001”号课程的学生姓名和所在系。

（14）查询计算机系没选JAVA课程的学生姓名和性别。

(15) 查询其他学期开设的课程中比第1学期开设课程的学分少的课程名、开课学期和学分。

（16）查询至少有一次成绩大于等于90的学生的姓名，所修的课程号和成绩。

（17）查询比第1学期开设的所有课程的学分都小的其他学期开设的课程名、开课学期和学分。

（18）查询每个学期学分最低的课程的课程名、开课学期和学分。（19）查询每门课程考试成绩最高的两个学生的学号以及相应的课程号和成绩。不包括没考试的课程。

（20）查询每门课程中，考试成绩低于该门课程的平均成绩的学生的学号和成绩。

（21）查询有最高学分超过本学期平均学分1.5倍的学期。

（22）查询学生姓名、所在系和该学生选的课程门数。

（23）查询课程名、开课学期及选该门课的学生人数、平均成绩。不包括没人选的课程。

(24) 使用exists查询选了“C002”课程的学生姓名。

（25）使用exists查询选了JAVA课程的学生姓名和所在系。

（26）使用exists查询没有选修“C001”课程的学生姓名和所在系。

(27) 使用exists查询计算机系没选JAVA的学生姓名和性别。

(28) 使用exists查询至少选了全部课程的学生的学号、姓名和所在系。

(29) 使用exists查询至少选了“0811102”学生所选的全部课程的学生的学号和所选的课程号。

实验要求：

（1）在MySql中完成上述操作。