数据库第三次作业.pdf

《数据库第三次作业.pdf》由会员分享，可在线阅读，更多相关《数据库第三次作业.pdf（8页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、 第 1 页/共 8 页 2011-2012 学年第二学期课程实验报告 课程名称：数据库系统原理 实验名称：交互式 SQL（二）：数据查询 姓名 陈宇放 班级 软件一班 学号 1001030020 实验台编号 同组同学 实验课表现 出勤、表现得分 25%25 实验报告 得分 50%实验总分 操作结果得分 25%25 实验目的 1 掌握 SELECT 语句的基本语法。2 掌握集合函数的作用及使用方法。3 熟悉普通连接、外连接和自身连接的概念。4 能够熟练使用连接查询从多个表中查询数据。5 能够熟练地使用子查询查询数据。实验内容（实验步骤和实验结果的简单描述，方便同学自己以后阅读）-一、简单查询-

2、1.查询年龄最小的前个同学的姓名、专业和年龄 SELECT TOP 3 姓名,专业,2012-YEAR(出生日期)年龄 FROM XS ORDER BY 出生日期 DESC -2.查询XS表中所有同学的学号、姓名和总学分，结果中各列的标题分别指定为num，name和mark。SELECT 学号 num,姓名 name,总学分 mark FROM XS -3.查询XS表中的学生数据来自哪些专业（使用DISTINCT子句消除结果集中的重复行）。SELECT DISTINCT 专业 FROM XS -4.查询XS表中专业为“计算机”的同学的情况。SELECT*FROM XS 第 2 页/共 8 页

3、WHERE 专业=计算机 -5.查询XS表中年出生的学生姓名和专业情况。SELECT 姓名,专业 FROM XS WHERE YEAR(出生日期)=1992 -6.查询XS表中姓“张”或“王”或“李”且单名的学生的情况。SELECT*FROM XS WHERE 姓名 LIKE 张_ OR 姓名 LIKE 王_ OR 姓名 LIKE 李_ -7.查询XS表中专业为“计算机”且总学分尚未确定的学生情况。SELECT*FROM XS WHERE 专业 LIKE 计算机 AND 总学分 IS NULL -8.从XS表中查询学生的基本信息，要求按照总学分从高到低排序，学分相同时，按学号由低到高排序。SE

4、LECT*FROM XS ORDER BY 总学分 DESC,学号 ASC -二、数据汇总-1.求选修了“”课程的学生的平均成绩。SELECT XS_KC.课程号,AVG(成绩)平均成绩 FROM KC,XS_KC WHERE XS_KC.课程号=KC.课程号 GROUP BY XS_KC.课程号 HAVING XS_KC.课程号 LIKE 101 -3.求学号为“”学生的总成绩。SELECT XS.学号,SUM(成绩)总成绩 FROM XS,XS_KC WHERE XS.学号=XS_KC.学号 GROUP BY XS.学号 HAVING XS.学号 LIKE 4102101 -4.求专业为“

5、计算机”的学生的总人数。第 3 页/共 8 页 SELECT 专业,COUNT(专业)专业人数 FROM XS WHERE 专业 LIKE 计算机 GROUP BY 专业 -5.求选修了“”课程的学生的人数。SELECT 课程号,COUNT(学号)选修人数 FROM XS_KC WHERE 课程号 LIKE 101 GROUP BY 课程号 -6.求选修了任意一门课程的学生的人数。SELECT 课程号,COUNT(学号)选修人数 FROM XS_KC GROUP BY 课程号 -7.统计各个专业的学生数。（按专业分组）SELECT 专业,COUNT(学号)学生数 FROM XS GROUP B

6、Y 专业 -8.统计各个专业的男女生人数。格式如下：-专业 性别 人数-SELECT 专业,性别,COUNT(学号)人数 FROM XS GROUP BY 性别,专业 -9.查找平均成绩在分以上的学生的学号和平均成绩。SELECT 学号,AVG(成绩)平均成绩 FROM XS_KC GROUP BY 学号 HAVING AVG(成绩)80 /*提问为什么不能用WHERE?用别名代替列名不能代表列的属性吗?*/-10.查找选修的课程中超过门成绩在分以上的学生的学号和成绩高于分的门数。格式如下：-学号 成绩高于分的门数-SELECT 学号,COUNT(成绩)成绩高于分的门数 第 4 页/共 8 页

7、 FROM XS_KC WHERE 成绩=80 GROUP BY 学号 HAVING COUNT(成绩)=2 -三、连接查询-1.查询每个学生的基本情况及选修的课程情况，格式如下：-学号 姓名 专业 课程号 成绩-SELECT XS.学号,XS.姓名,XS.专业,XS_KC.课程号,XS_KC.成绩 FROM XS,XS_KC WHERE XS.学号=XS_KC.学号 -2.查询选修了课程号为的每个学生的姓名及成绩。SELECT 姓名,成绩 FROM XS,XS_KC WHERE XS.学号=XS_KC.学号 AND 课程号=101 -3.查询“计算机”专业且选修了“计算机基础”课程的学生的学

8、号、姓名及成绩。SELECT XS.学号,姓名,成绩 FROM XS,KC,XS_KC WHERE XS.学号=XS_KC.学号 AND KC.课程号=XS_KC.课程号 AND 专业=计算机 AND 课程名=计算机基础 -4.查询选修了课程号为的每个学生的基本情况及成绩，若学生未选修号课程，也包括其情况。WITH NEW_TABLE(成绩,学号)AS (SELECT 成绩,XS.学号 FROM XS,XS_KC WHERE XS.学号=XS_KC.学号 AND 课程号=101 )SELECT XS.*,NEW_TABLE.成绩 FROM NEW_TABLE RIGHT OUTER JOIN

9、XS ON(XS.学号=NEW_TABLE.学号)-5.查询生日相同的学生的姓名和出生日期。格式如下-姓名 姓名 出生日期-其中：“姓名”和“姓名”中的姓名不能相同。SELECT S1.姓名 姓名,S2.姓名 姓名,S1.出生日期 第 5 页/共 8 页 FROM XS S1,XS S2 WHERE S1.出生日期=S2.出生日期 AND S1.姓名 S2.姓名 -6.显示每门课程的平均成绩并显示总平均成绩。格式如下：-课程名 平均成绩-（总平均）SELECT KC.课程名,AVG(XS_KC.成绩)平均成绩 FROM KC,XS_KC WHERE KC.课程号=XS_KC.课程号 GROUP

10、 BY XS_KC.课程号,KC.课程名 SELECT AVG(成绩)AS 总平均 FROM XS_KC -7.查询各个专业各门课程的平均成绩。格式如下-专业 课程名 平均成绩-SELECT 专业,课程名,AVG(成绩)平均成绩 FROM XS,XS_KC,KC WHERE XS.学号=XS_KC.学号 AND XS_KC.课程号=KC.课程号 GROUP BY 课程名,专业 -8.查询“计算机”专业平均成绩在分以上的学生的学号、姓名和平均成绩。SELECT XS.学号,姓名,AVG(成绩)平均成绩 FROM XS,XS_KC WHERE XS.学号=XS_KC.学号 AND 专业=计算机 G

11、ROUP BY 姓名,XS.学号 HAVING AVG(成绩)85 -四、子查询的使用-1.查询平均成绩高于号课程平均分的课程号及平均成绩。SELECT 课程号,AVG(成绩)平均成绩 FROM XS_KC GROUP BY 课程号 HAVING SUM(成绩)(SELECT SUM(成绩)FROM XS_KC WHERE 课程号=101)-2.查找比所有计算机专业的学生年龄都大的学生的情况。第 6 页/共 8 页 SELECT*FROM XS WHERE 2012-YEAR(出生日期)(SELECT MAX(2012-YEAR(出生日期)FROM XS WHERE 专业 LIKE 计算机)-

12、3.查找未选修“C语言”课程的学生的情况。SELECT*FROM XS WHERE NOT EXISTS(SELECT*FROM KC,XS_KC WHERE 学号=XS.学号 AND 课程名=C语言 AND XS_KC.课程号=KC.课程号 )-4.查询每个专业年龄最小的学生的基本信息。SELECT*FROM XS S1 WHERE YEAR(出生日期)=(SELECT MAX(YEAR(出生日期)FROM XS S2 GROUP BY 专业 HAVING S1.专业=S2.专业)-5.查询“计算机基础”成绩高于“计算机基础”平均分的学生的学号，姓名和成绩，并按“计算机基础”成绩从高到低排序

13、。SELECT XS.学号,姓名,成绩 FROM XS,XS_KC,KC S1 WHERE XS.学号=XS_KC.学号 AND XS_KC.课程号=S1.课程号 AND 课程名=计算机基础 GROUP BY XS.学号,姓名,S1.课程号,成绩 HAVING 成绩 (SELECT AVG(成绩)FROM XS_KC S2 WHERE S1.课程号=S2.课程号)ORDER BY 成绩 DESC -6.查询选修全部课程的学生的基本信息。SELECT*FROM XS WHERE NOT EXISTS(SELECT*FROM KC 第 7 页/共 8 页 WHERE NOT EXISTS(SELE

14、CT*FROM XS_KC WHERE XS.学号=XS_KC.学号 AND XS_KC.课程号=KC.课程号 )-7.查询至少选修了“”同学选修课程的学生的基本信息。SELECT*FROM XS WHERE NOT EXISTS(SELECT*FROM XS_KC S1 WHERE S1.学号=4102102 AND NOT EXISTS(SELECT*FROM XS_KC S2 WHERE S2.学号=XS.学号 AND S2.课程号=S1.课程号)实验过程中遇到的问题以及如何解决的？（可以写多条，是否认真填写将影响实验成绩）能顺利写出语句,但是对于语句表达的简洁度不够 本次实验的体会（可以写多条，是否认真填写将影响实验成绩）通过本实验，我理解/了解/熟悉了.1.我熟悉了解了 SELECT 语句的语法 2.能分清楚 WHERE 和 HAVING 的用法区别 3.思考题 教师评价 第 8 页/共 8 页 实验态度 A.很认真 B.认真 C.比较认真 D.不认真 独立完成情况 A.很好 B.好 C.比较好 D.不好 收获和体会 A.多 B.比较多 C.少 D.很少 思考题回答情况 A.很好 B.好 C.比较好 D.不好 评阅教师：陈红顺 日 期：