vlambda博客
学习文章列表

SQL：实验四 数据库查询语言（二）

实验四  数据库查询语言（二）

一、实验目的

1．掌握Management Studio的使用。

2．掌握SQL中连接查询和嵌套查询的使用。

二、实验内容

1．找出所有任教“数据库”的教师的姓名。



select teac_namefrom teacherwhere teac_id in (select teac_idfrom courseTeacherwhere course_id in(select course_idfrom coursewhere Course_name='数据库')









2．找出学号为“980101011”的学生选修的课程号和课程名。


SQL：实验四 数据库查询语言（二） 



select Course_id, Course_namefrom Coursewhere Course_id in (select Course_idfrom StudentGradewhere Stu_id='980101011')








3．“涂杰杰”所选修的全部课程号及成绩。（注意：school中有同名，即有两名学生叫“涂杰杰”。）


SQL：实验四 数据库查询语言（二） 



select student.Stu_id, Course_id,gradefrom StudentGrade,studentwhere Student.Stu_name ='涂杰杰' and Student.Stu_id=StudentGrade.Stu_id

(因为重名所以个人考虑一下需要经过stu_id加以区分当然题目不要求这样，理解即可！）







4．检索选修课程号为“0109”或“0111”的学生学号、姓名和所在班级编号。


SQL：实验四 数据库查询语言（二） 






select stu_id,stu_name,Class_idfrom Studentwhere Stu_id in(select Stu_idfrom StudentGradewhere Course_id in('0109','0111'))



SQL：实验四 数据库查询语言（二）

学习一下在ＳＱＬ结构化查询语言的注释语句


5．查询“0203”课程的最高分的学生的学号。

SQL：实验四 数据库查询语言（二） 



select Stu_idfrom StudentGradewhere Course_id ='0203' and grade in(select max(grade)from StudentGrade)






  1. 选修了以“01”开头的课程的学生学号，姓名，选课的课程号。



SQL：实验四 数据库查询语言（二） 




select distinct student.stu_id,stu_name,course_idfrom student,studentgradewhere student.stu_id in(select distinct stu_idfrom studentgradewhere Course_id like '01%') and Course_id like '01%'




这道题的表无非就是student和studentgrade表，通过直接连接就好，没必要那么复杂使用嵌套连接。

参考老师的答案：

SQL：实验四 数据库查询语言（二）

--6 right

select Student.Stu_id,Student.Stu_name,StudentGrade.Course_id

from Student,StudentGrade

where Course_id like '01%' and Student.Stu_id =StudentGrade.Stu_id




7．没有选修以“01”开头的课程的学生学号，姓名，选课的课程号。（用子查询完成，提示not in或not exists。需考虑没选课的学生，仔细对比上题）




SQL：实验四 数据库查询语言（二） 


--7 rightselect Student.Stu_id,Student.Stu_name,StudentGrade.Course_idfrom Student,StudentGradewhere Course_id not like '01%' and Student.Stu_id =StudentGrade.Stu_id











8．找出“苏贤兴”同学所学课程的名称和成绩。


SQL：实验四 数据库查询语言（二） 


--8 rightselect Course_name,Gradefrom Student,StudentGrade,Coursewhere Stu_name='苏贤兴' and Student.Stu_id=StudentGrade.Stu_id and Course.Course_id=StudentGrade.Course_id






9．统计年龄大于30岁的学生的人数。



SQL：实验四 数据库查询语言（二） 


--9select COUNT(*) '大于30岁的人数'from Studentwhere YEAR(GETDATE())-YEAR(Student.Birthday)>30









10．统计数据结构有多少人80分或以上。



SQL：实验四 数据库查询语言（二） 


--10select COUNT(*) 'DS80分及以上人数'from Course,StudentGradewhere Course.Course_name ='数据结构' and Course.Course_id=StudentGrade.Course_id and Grade>=80









11．统计各系开设班级的数目(系名称、班级数目)，并创建结果表。（需考虑没有班级的系）


这道题目比较困难，需要用到蛮多的函数尤其是count；而且牵扯的表有点多；要创建一个全新的结果表。

SQL：实验四 数据库查询语言（二）

这是老师给的答案；实验成功了但是我一直没有找到这个结果新表在哪里？！

SQL：实验四 数据库查询语言（二）

屏蔽掉into语句后显示的结果还是比较好的。

老师的这段代码采用连接查询的外连接。详细参考课本P102 3.外连接

SQL：实验四 数据库查询语言（二）

这是我的错误尝试；一开始简单地想用group搞定。


--11 errorselect Depar_id,count(class_id) 'num'from class,Deparmentgroup by Class_idhaving Class.Depar_id=Deparment.Depar_id




最后还是使用老师的代码比较合适：


--11 rightselect depar_name,count(class_id) as 班级数目into newtable1from deparment left outer join class on(deparment.depar_id=class.depar_id)group by depar_name







12.统计各门课程的选修人数，并按人数降序排列，找出排名前三位的课程。（提示：可以使用TOP 3）

SQL：实验四 数据库查询语言（二） 


--12select Top 3 course_id ,count(*) 'num'from StudentGradegroup by course_idorder by count(*) Desc







这道题目使用的是升降序还有就是TOP的技术方法。









13.统计各门课程的选修人数，并按人数降序排列，找出排名前三位的课程。（上述使用TOP 3，却不能处理人数并列的情况。试考虑一种方法能处理人数并列的情况。）


恕我愚昧这道题目我是真的不会啊！老师给的答案里面是在having语句里面在嵌套一个select语句。我是真的没有想到还可以这么玩，真会玩！



SQL：实验四 数据库查询语言（二） 


--13 借鉴老师的代码select course_id,count(*)from studentgradegroup by course_idhaving count(*) in(select distinct top 3 count(*) from studentgrade group by course_id order by count(*) desc)order by count(*) desc






14.统计每科目的最高分、最低分，平均分、总分，并以中文列名显示。


SQL：实验四 数据库查询语言（二） 


--14select Course_name,MAX(grade) 'Highest',MIN(grade) 'Lowest',AVG(grade) 'Avg',SUM(grade) 'Sum'from Course,StudentGradewhere Course.Course_id=StudentGrade.Course_idgroup by studentgrade.Course_id,Course_name








15.所有科目成绩都在70分以上的学生姓名（要求：至少采用两种方法）。

SQL：实验四 数据库查询语言（二）

方法一：这个就是直接连接


--15_1select Stu_namefrom student,StudentGradewhere Student.Stu_id=StudentGrade.Stu_idgroup by Stu_name,Student.Stu_id,StudentGrade.Stu_idhaving min(grade)>70



SQL：实验四 数据库查询语言（二）

方法二：嵌套查询的方式


--15_2select Stu_namefrom Studentwhere Stu_id in (select Stu_idfrom StudentGradegroup by Stu_idhaving min(Grade)>70

)





16.“数据库”课程得最高分的学生的学号、姓名和所在系。


SQL：实验四 数据库查询语言（二）

上图是我的实现结果。但是老师的和我的不一样？！

SQL：实验四 数据库查询语言（二）

这个就是通过老师的实验代码得到的结果。很显然，top1 只能得到一个学生但是若是有多个同分的同学那么应该怎么处理呢？

SQL：实验四 数据库查询语言（二）

重新修改一下代码最终和老师的代码一致——在where语句中缺少一个条件就是student表中的stu_id要和studentgrade表中的stu_id要一致。这个还不是正确的答案。同分问题还是没有解决。

解决的思路：嵌套连接。找到和数据库成绩相同的成绩的同学；然后限制该同学是学习数据库的学生。

SQL：实验四 数据库查询语言（二）

最终的正确结果。


--16 rightselect student.Stu_id,Stu_name,Depar_namefrom Student,StudentGrade,Class,Deparment,coursewhere Course_name='数据库'and course.Course_id=StudentGrade.Course_idand class.Class_id= Student.Class_idand class.Depar_id=Deparment.Depar_idand student.Stu_id=StudentGrade.Stu_idand grade = (select max(grade)from studentgrade,coursewhere Course_name='数据库'and course.Course_id=StudentGrade.Course_id)




17.至少选修了两门课及以上的学生姓名和性别。

SQL：实验四 数据库查询语言（二）
SQL：实验四 数据库查询语言（二）

第一张是我的是我的实验结果，第二张是老师提供的代码的实验结果。两个结果都是一样的。如下为两份不同的代码：

我的：



--17selectStu_name,Stu_sexfrom student,StudentGradewhere student.Stu_id=StudentGrade.Stu_idgroup by studentGrade.Stu_id , student.stu_id,Stu_name,Stu_sexhaving count(*)>=2




老师的：


select stu_name,stu_sexfrom studentwhere stu_id in (select stu_id from studentgrade group by stu_id having count(*)>=2)




总结分析：

  1. 个人更加偏向于老师的代码。

    老师使用的是嵌套的方法。

    在嵌套里面在进入元组的划分group，——顺便提及一下做了那么多关于group的实验，我发现group的划分尽量是主属性这可以唯一表示该元组信息的。

  2. 我的代码是先直接连接再group，这就比较麻烦多了因为需要划分的对象多了。



18.检索既选修课程“数据结构”又选修了“C语言”的学生学号。


SQL：实验四 数据库查询语言（二）

这是我的错误实验结果。什么都没有显示。我的思路有问题，需要重新推翻。

一个元组不可能同时等于C语言和数据结构，所以这个是空的。那么使用嵌套查询可能会好一点。

SQL：实验四 数据库查询语言（二） 


--18 error_1select Stu_idfrom Studentwhere Stu_id in(select Stu_idfrom StudentGradewhere Course_id in(select Course_idfrom coursewhere Course_name in('C语言','数据结构')))



还是存在蛮大的误差的。这个代码是查询选修的C语言或数据结构的学生的代码。这个和题意不符合。我想那么把最后的一个嵌套拆开会是怎么样的呢？

SQL：实验四 数据库查询语言（二）

这回的实验结构就比较满意咯。


--18 rightselect Stu_idfrom Studentwhere Stu_id in(select Stu_idfrom StudentGradewhere Course_id in(select Course_idfrom coursewhere Course_name ='C语言'))and stu_id in(select Stu_idfrom StudentGradewhere Course_id in(select Course_idfrom coursewhere Course_name ='数据结构'))









19.检索选修了‘0103’和‘0105’两门课程，并且‘0103’这门课程成绩高于‘0105’的学生的学号。


这道题目一开始没有太多头绪，参考老师的代码发现可以使用自身连接。

SQL：实验四 数据库查询语言（二） 



--19select x.stu_idfrom StudentGrade x,StudentGrade ywhere x.Stu_id=y.Stu_id and x.Course_id='0103' and y.Course_id='0105' and x.Grade>y.Grade



回归到18题，是不是也可以使用自身连接呢？

SQL：实验四 数据库查询语言（二）

这个也是可以的。正如老师说的实现方法有很多。


--18 try 自我连接select x.Stu_idfrom StudentGrade x,StudentGrade ywhere x.Course_id in (select Course_idfrom coursewhere Course_name ='C语言') and y.Course_id in (select Course_idfrom coursewhere Course_name ='数据结构') and x.Stu_id=y.Stu_id







20.检索选修了课程“数据结构”和“C语言”两门课程并且“数据结构”分数高于“C语言”的学生学号和姓名。

20题其实就是在19的基础上修改一点，两者还是十分相像的。

SQL：实验四 数据库查询语言（二）

上图是老师给的答案。这是有缺漏的，没有学生的姓名只有学号？！看来还是靠自己吧。

SQL：实验四 数据库查询语言（二） 



--20select x.stu_id,Stu_namefrom StudentGrade x,StudentGrade y,Studentwhere x.Course_id in (select Course_idfrom coursewhere Course_name ='C语言')and y.Course_id in (select Course_id from course where Course_name ='数据结构') and x.Stu_id=y.Stu_id and x.Grade>y.Grade and student.Stu_id=x.Stu_id










  1. 列出所有班名、班主任、班长、系名。

    （请使用连接查询；

    进一步考虑使用外连接，因为很多班级可能是没有班长的，考虑需要显示所有班级的信息）

外连接可以参考课本P102 3.外连接


--21 外连接select Class_name,Director,Monitor,Depar_namefrom class left outer join Deparment on(Class.Depar_id=Deparment.Depar_id)









三、实验小结

1．思考简单查询、连接查询与嵌套查询有什么不同？连接查询与嵌套查询有何区别与联系？

Q1：

简单查询一般是在一个表中，通过简单的查询选择所需要的数据；连接查询，有等值与非等值连接外连接内连接等等，多个关系表，通过操作串联起来成为一个新表；嵌套查询是将多个表通过嵌套的方式进行查询，先进行最内的表查询，再一步步向最外层嵌套迈进

Q2:

连接查询与嵌套查询都是将多个表联系起来进行程序，只是实现的方式有所不同，有些嵌套查询，可以通过连接查询代替，但是有些就不能代替




各位帅气漂亮的大大们

你懂得

十八【殷勤脸】


推荐阅读

[5.05] 暗喻幻想港中35周年限定版1.03+DLC+金手指+王中王存档 [5.05] 暗喻幻想港中35周年限定版1.03+DLC+金手指+王中王存档
安琪拉教鲁班学算法之动态规划 安琪拉教鲁班学算法之动态规划

相关文章