查询练习题.doc

网络数据库讲稿查询练习题简单的单表查询例1：检索出1983年6月1日之后出生的学生的姓名、性别和民族，并按降序排列。SELECT 姓名,性别,民族 FROM 学生 WHERE 出生日期1983-6-1 ORDER BY 出生日期 DESC多表查询在FROM后跟随多个表。例1：查找出已修学分达到20分的学生的姓名、出生日期、班级名称、已修学分。SELECT 姓名,出生日期,班级名称,已修学分FROM 学生,班级WHERE 学生.班级编号=班级.班级编号AND 已修学分=20解释：这里的“学生.班级编号=班级.班级编号”是连接条件。如果在FROM短语中有两个表，那么这两个表必须具备可建立联系的字段，否则无法查询。如果FROM短语中的多个表中有同名字段，当使用这些字段名时，需要在字段名左边加上表名和圆点，作为字段名的前缀。练习题01：查找出已修学分低于20分的学生的姓名、性别和班主任。练习题02：查找出学生“王林”的班主任。练习题03：查找出班主任“刘成河”班的全部男生的信息。例2：查找出课程“中国历史”考试及格的全部学生的学号、姓名。SELECT 学生.学号,姓名 FROM 学生,课程,成绩 WHERE 分数=60 AND 课程名称=中国历史 AND 学生.学号=成绩.学号 AND 成绩.课程编号=课程.课程编号练习题04：查找出课程“中国历史”考试及格的全部学生的学号、姓名、班级名称和分数。练习题05：查找出全部同学的所有考试的姓名、课程名称、分数，查找结果的格式如下：嵌套查询 P121-126例1：查找出取得过95分及其以上考试成绩的学生的学号和姓名。SELECT 学号,姓名 FROM 学生 WHERE 学号IN (SELECT 学号 FROM 成绩 WHERE 分数=95)解释：该命令中的IN相当于集合运算的包含运算符。括号外的查询称为外层查询；括号内的查询称为内层查询(也称为“子查询”)。内层查询的结果被当做是一个集合。P104例2：查找出“计算机应用”课程考试取得95分及其以上考试成绩的学生的学号和姓名。SELECT 学号,姓名 FROM 学生 WHERE 学号 IN (SELECT 学号 FROM 成绩 WHERE 分数=95 AND 课程编号 IN (SELECT 课程编号 FROM 课程 WHERE 课程名称=计算机应用)例3：查找出所有学生的考试都及格的班级名称。答案一：SELECT 班级名称 FROM 班级 WHERE 班级编号 NOT IN (SELECT 班级编号 FROM 学生 WHERE 学号 IN (SELECT 学号 FROM 成绩 WHERE 分数60)答案二：select 班级名称 from 班级where not exists (select * from 学生where exists (select * from 成绩where 分数60 and 班级.班级编号=学生.班级编号 and 成绩.学号=学生.学号)答案三：select 班级名称 from 班级where not exists(select * from 成绩,学生 where 班级.班级编号=学生.班级编号 and 学生.学号=成绩.学号 and 分数=60)练习题01：写出可实现例1要求的其他命令。练习题02：写出可实现例2要求的其他命令。二、范围运算符BETWEEN AND P103该范围是一个闭区间，意思是在 和 之间，包括两个端点的值。例3：查找出1983年6月1日至1984年6月1日之间出生的学生的信息。SELECT * FROM 学生 WHERE 出生日期 BETWEEN 1983-6-1 AND 1984-6-1练习题03：查找出已修学分在15和20之间的学生的信息。练习题04：查找出1983年6月1日至1984年6月1日之间出生的少数民族学生的信息。练习题05：查找出已修学分在15和20之间、且“自然辨证法”课程的考试分数达到80分的学生的姓名、性别、所在班级。三、字符串匹配运算符LIKE P105例4：查找出姓名中包含有“林”的学生的信息。SELECT * FROM 学生WHERE 姓名LIKE %林%例5：查找出姓名中第二个字是“林”的学生的信息。SELECT * FROM 学生WHERE 姓名LIKE _林%练习题06：查找出湖北出生的学生的信息。练习题07：查找出武汉出生的学生的信息。练习题08：查找出班主任“郭大壮”的班中出生地是武汉的学生的姓名、班级。练习题09：查找出至少参加了辛有余老师所任课程中一门课程考试的男生的姓名。至少写出两条不同的命令。聚合函数及其分组查询为了完成后续题目，请首先计算出每位同学的已修学分UPDATE 学生 SET 已修学分=(SELECT SUM(学分) FROM 成绩,课程 WHERE 学生.学号=成绩.学号AND 分数=60 AND 成绩.课程编号=课程.课程编号)一、常用统计(聚合)函数 P109表4-3例1：计算出全部学生的平均已修学分。SELECT AVG(已修学分) FROM 学生例2：计算出“软件工程”班全体同学的平均已修学分。SELECT AVG(已修学分) FROM 学生 WHERE 班级编号IN(SELECT 班级编号FROM 班级WHERE 班级名称=软件工程)练习题01：计算出“软件工程”和“计算机应用”两个班全体同学的平均已修学分。注意：当子查询的结果只有一个数据时，可以用等号；为多个数据时，需用 IN。练习题02：计算出李建同学各门课程考试总分。例3：查找出“数学专业”班“数学”课程考试的最高分数。SELECT MAX(分数) FROM 学生, 成绩WHERE 学生.学号=成绩.学号 AND 班级编号=(SELECT 班级编号 FROM 班级 WHERE 班级名称=数学专业) AND 课程编号=(SELECT 课程编号 FROM 课程 WHERE 课程名称=数学)另一个答案SELECT MAX(分数) FROM 成绩 WHERE 学号 IN (SELECT 学号 FROM 学生 WHERE 班级编号=(SELECT 班级编号 FROM 班级 WHERE 班级名称=数学专业) AND 课程编号=(SELECT 课程编号 FROM 课程 WHERE 课程名称=数学)例4：查找出“数学专业”班“数学”课程考试最高得分的同学姓名和分数。SELECT 姓名, 分数 FROM 学生, 成绩WHERE 学生.学号=成绩.学号 AND 班级编号=(SELECT 班级编号 FROM 班级 WHERE 班级名称=数学专业) AND 课程编号=(SELECT 课程编号 FROM 课程 WHERE 课程名称=数学) AND 分数=(SELECT MAX(分数) FROM 学生, 成绩 WHERE 学生.学号=成绩.学号 AND 班级编号=(SELECT 班级编号 FROM 班级 WHERE 班级名称=数学专业) AND 课程编号=(SELECT 课程编号FROM 课程 WHERE 课程名称=数学)附：TOP PERCENT P98二、GROUP BY短语GROUP BY后面跟随的是分组关键字段，按照关键字段的值，将记录分成若干个组，每一组转变为查询结果中的一行。例5：计算出各班学生的平均已修学分。SELECT 班级编号,AVG(已修学分) FROM 学生 GROUP BY 班级编号练习题04：计算出各门课程考试的平均分数。练习题05：计算出各位学生的考试总分。练习题06：计算出辛有余老师所任课程中每门课程的课程编号和学生平均考试分数。三、别名及其应用 P113-114, P99例6：查找出已修学分低于本班平均已修学分的学生信息。SELECT 姓名, 班级编号, 已修学分 FROM 学生WHERE 已修学分1说明：HAVING短语通常与GROUP BY短语配合使用，否则无使用价值。HAVING短语中使用的统计函数应该是SELECT短语中使用的函数。练习题10：查找出考试成绩优秀人数多于3人的课程编号及其学生人数(假定考试分数达到90分为优秀)。练习题11：查找出考试成绩优秀人数多于3人的课程名称及其学生人数(假定考试分数达到90分为优秀)。练习题12：查找出考试成绩优秀人次多于5人次的班级名称及其人次(假定考试分数达到90分为优秀)。练习题13：查找出考试分数标准差小于10、且最高分与最低分之差小于20的各班(班级名称)各门课程(课程名称)的考试人数，考试分数的最高分、最低分、平均分、标准差、方差。常用函数、连接一、常用函数P159二、链接 P114用于多表查询，实现表之间的链接。例6：在多表查询中，下面的命令可查找出已修学分达到17分的学生的姓名、出生日期和班级：SELECT 姓名,出生日期,班级名称 FROM 学生,班级 WHERE 学生.班级编号=班级.班级编号 AND 已修学分=17用INNER JOIN ON ，可将该命令改写为：SELECT 姓名,出生日期,班级名称 FROM 学生 INNER JOIN 班级 ON 学生.班级编号=班级.班级编号 WHERE 已修学分=17或SELECT 姓名,出生日期,班级名称 FROM 学生 INNER JOIN 班级 ON 学生.班级编号=班级.班级编号 AND 已修学分=17解释：这里的“学生.班级编号=班级.班级编号”是连接条件。如果在FROM短语中有两个表，那么这两个表必须具备可建立联系的字段，否则无法查询。如果FROM短语中的多个表中有同名字段，当使用这些字段名时，需要在字段名左边加上表名和圆点，作为字段名的前缀。练习题01：查找出已修学分低于17分的学生的姓名、性别和班主任。练习题02：查找出学生“王林”的班主任。练习题03：查找出班主任“刘成河”班的全部男生的信息。例7：在多表查询中，下面的命令可查找出课程“中国历史”考试及格的全部学生的学号、姓名：SELECT 学生.学号,姓名 FROM 学生,课程,成绩 WHERE 分数=60 AND 课程名称=中国历史 AND 学生.学号=成绩.学号 AND 成绩.课程编号=课程.课程编号用INNER JOIN ON ，可将该命令改写为：SELECT 学生.学号,姓名 FROM 学生 INNER JOIN (成绩 INNER JOIN 课程 ON 成绩.课程编号=课程.课程编号) ON 学生.学号=成绩.学号 WHERE 分数=60 AND 课程名称=中国历史 练习题04：查找出课程“中国历史”考试及格的全部学生的学号、姓名、班级和分数。练习题05：查找出全部同学的所有考试的姓名、课程名称、分数。查找结果的格式如下：练习题06：计算出每个同学实际取得的学分(不是学生表中的已修学分)。查找结果的格式如下：操作查询一、生成表查询 P136例1：SELECT 姓名, 出生日期, 班级名称 INTO XYZFROM 学生, 班级WHERE 学生.班级编号=班级.班级编号 AND 已修学分=17例2：SELECT 班级名称, 课程名称, 分数 INTO 考试分数FROM 课程 INNER JOIN (班级 INNER JOIN 学生 ON 班级.班级编号 = 学生.班级编号) INNER JOIN 成绩 ON 学生.学号 = 成绩.学号) ON 课程.课程编号 = 成绩.课程编号二、更新查询 P137例3：将每位同学的已修学分设置为0。UPDATE 学生 SET 已修学分=0将每位同学的已修学分设置为4。UPDATE 学生 SET 已修学分=4省略WHERE短语，表示无条件修改全部记录。例4：为每个女生增加2学分。UPDATE 学生 SET 已修学分=已修学分+2 WHERE 性别= 女练习题01：(综合练习)首先为“学生”表添加一个整型字段“年龄”，然后用UPDATE命令计算出每位同学的年龄并填入“年龄”字段。例5：根据考试成绩，计算出每位同学的实际已修学分，并填入“学生”表的“已修学分”字段。UPDATE 学生 SET 已修学分=(SELECT SUM(学分) FROM 成绩,课程 WHERE 学生.学号=成绩.学号AND 分数=60 AND 成绩.课