实验2-实验报告

1、数据库实验实验二 sql server2008数据查询【实验目的】掌握sql server2008数据查询语言的使用，能独立按要求对数据库进行指定的查询操作。【实验要求】1、每完成一个任务，截取全屏幕快照13张作为中间步骤和结果的贴图，粘贴在最后的实验报告中。2、自己编一些新的数据插入到数据表中，丰富每个人的数据的同时还要保证每个查询的结果集不为空，或结果不为0。【实验任务及实验过程】（内附，每题代码）1、 将自己的信息插入到 stud_info 学生信息表中。学号姓名出生性别地址电话邮编成绩2011211012孙嘉璐1993女天津市河西区022-88296868123456624代码：/*4

2、、查询“计算机工程系”、“计算机网络技术专业”、班全体学生的基本信息程序*/use sunjialu3_datagoinsert into stud_info values ('2011211012','孙嘉璐','1993-01-12 00:00:00.000','女','天津市河西区','#39;,'100876','624')2、 显示 stud_grade 学生成绩表中成绩为 85，86 或 88 的记录。程序：use sunjialu3_d

3、atagoselect * from stud_grade where grade=85 or grade=86 or grade=883、 显示 stud_grade 学生成绩表中的最高分的学生学号和课程号。方法：使用子查询功能，和计算函数max程序: use sunjialu3_datagoselect stud_id course_id from stud_gradewhere grade in(select max(grade)from stud_grade )4、 查询“计算机工程系”、“计算机网络技术专业”、02 班全体学生的基本信息方法：先查找出“计算机网络技术专业”所对应的的编

4、号。在查找与此编号比配的学号，使用了模糊匹配 like。代码：/*4、查询“计算机工程系”、“计算机网络技术专业”、班全体学生的基本信息程序*/use sunjialu3_datagoselect speccode from specialty_codewhere specname='计算机网络技术'use sunjialu3_datagoselect * from stud_infowhere stud_id like '04010202_'5、 查询“计算机工程系”、“计算机网络技术专业”、02 班的学生人数。方法：使用了计算函数count（*）计算元组数量

5、和模糊匹配like 。程序：use sunjialu3_datagoselect count(*) from stud_infowhere stud_id like '04010202_'6、 显示出与学号为“0401040112”的学生同年出生的所有学生的学号、姓名和出生日期。 提示：可以使用函数year()。方法：使用了year函数，同时使用子查询的方法得到与学号0401040112的出生年份，进行比较，得到出生年份相等的同学。代码：/*显示出与学号为“0401040112”的学生同年出生的所有学生的学号、姓名和出生日期*/use sunjialu3_datagoselec

6、t stud_id as 学号, name as 姓名 ,birthday as 出生日期 from stud_infowhere year (birthday)=(select year(birthday) from stud_infowhere stud_id='0401040112')7、 生成绩表中，显示存在有 85 分以上成绩的课程号。方法：使用了where的比较运算符。代码：use sunjialu3_datagoselect course_id from stud_gradewhere grade>858、 在学生成绩表中，显示所有成绩为空的记录信息。方法：

7、所有成绩为空的表示法是，is null代码：/*在学生成绩表中，显示所有成绩为空的记录信息。*/use sunjialu3_datagoselect * from stud_gradewhere grade is null9、 查询所有电话号码的开头为010的同学的姓名、电话、地址和邮政编码。方法:在stud_info中采用模糊匹配。代码：use sunjialu3_datagoselect name ,telcode, address,zipcode from stud_infowhere telcode like '010%'10、 查询所有女教师的平均工资。方法：采用计算

8、函数avg，在teacher_info表中查询，条件是性别为“女“。代码：use sunjialu3_datagoselect avg(salary) from teacher_infowhere gender='女'11、 查询课程号为'0401010103'的课程的最高分、最低分和平均分。方法：采用计算函数avg,max,min，条件是课程号为0401010103。代码：use sunjialu3_datagoselect avg( grade)as 平均成绩 , max(grade) as 最高成绩,min(grade) as 最低成绩 from stud

9、_gradewhere course_id='0401010103'12、 查询选了课且至少有一门课没有成绩的学生人数。方法：代码：/*12、查询选了课且至少有一门课没有成绩的学生人数*/use sunjialu3_datagoselect count ( distinct stud_id) as 学生人数 from stud_gradewhere grade is null and course_id is not null13、 按职称统计各个教研室的教师人数。方法：使用substring函数选出教研室号，根据教研室号和教师职称进行分组。代码：/*按职称统计各个教研室的教师

10、人数*/use sunjialu3_datagoselect substring(teacher_id,3,2) as 教研室, tech_title as 职称 ,count (*) as 教研人数 from teacher_infogroup by substring(teacher_id,3,2),tech_title 14、 将计算机工程系职称为讲师的教师按年龄由低到高排列。方法：采用子查询的方法，先选出计算机工程系的系号，在选出对应系号的课程号，从而选出教授计算机工程系的讲师们，在按年龄由低到高排序，使用order by 的asc语句。代码：use sunjialu3_datagos

11、elect name as 姓名, age as 年龄 from teacher_info where tech_title='讲师' and course_id in(select course_id from teach_schedulewhere deptcode in(select deptcode from dept_codewhere deptname='计算机工程系')order by age asc15、 将所有选了课的学生的学号按平均成绩由高到低排列。代码：use sunjialu3_datagoselect stud_id as 学号, av

12、g(grade) as 平均成绩 from stud_gradewhere stud_id in(select stud_id from stud_gradewhere course_id in(select course_id from stud_gradewhere course_id is not null ) group by stud_idorder by avg(grade) desc17、将 stud_grade 学生成绩表中课程号为“0401010106”的成绩加 5。代码：use sunjialu3_datagoupdate stud_grade set grade=grad

13、e+5where course_id='0401010106'18、删除 stud_info 学生信息表中姓名为“张源”的学生记录。方法：使用delete函数，又条件删除。代码：use sunjialu3_datagodelete from stud_info where name='张源'19、求选修了“数据库原理”的学生的学号及姓名。方法：子查询方法。代码：use sunjialu3_datagoselect stud_id as 学号, name as 姓名 from stud_infowhere stud_id in(select stud_id fro

14、m stud_gradewhere course_id in(select course_id from teach_schedulewhere room_id in (select room_id from classroom_infowhere room_name='数据库')20、查询所有没选修“0401010103”课程的学生学号及姓名。代码：use sunjialu3_datagoselect stud_id as 学号, name as 姓名 from stud_infowhere stud_id in(select stud_id from stud_gradew

15、here course_id !='0401010103')21、查询与王刚在同一个系学习的学生的姓名和所在的系。方法：查找王刚计算机工程系的系号。代码：/*查询与王刚在同一个系学习的学生的姓名和所在的系*/use sunjialu3_datagoselect deptcode as 系号 from dept_codewhere deptname='计算机工程系' 方法：将dept_code， teach_schedule， stud_grade三个表连接起来，查找，系号为01的学生姓名和所在系。代码：/*查询与王刚在同一个系学习的学生的姓名和所在的系*/use

16、 goselect deptname as 所在系, name as 姓名 from dept_code join teach_schedule on teach_schedule.deptcode=dept_code.deptcodejoin stud_grade on stud_grade.course_id=teach_schedule.course_idwhere dept_code.deptcode='01'22、查询成绩为小于60分的学生的学号、姓名。方法：将stud_info，stud_grade表连接起来，找寻条件为成绩小于60分。代码：/*查询成绩为小于分的学

17、生的学号、姓名*/use sunjialu3_datagoselect stud_info.stud_id as 学号, stud_ as 姓名 from stud_grade join stud_info on stud_grade.stud_id=stud_info.stud_idwhere stud_grade.grade<6023、查询修了“0401010103”课程且成绩高于此课程的平均成绩的学生的学号和成绩。方法：先从grade_id表中将course_id='0401010103'的平均成绩选择出来，在选择course_id='0

18、401010103'且成绩>平均成绩的学号和成绩。代码：/*查询修了“0401010103”课程且成绩高于此课程的平均成绩的学生的学号和成绩*/use sunjialu3_datagoselect stud_id as 学号,name as 姓名 from stud_gradewhere course_id='0401010103' and grade > (select avg(grade) from stud_gradewhere course_id='0401010103')24、显示选了课程号为“0401010105”的上课教师姓名。

19、方法：将teacher_info，teach_schedule两个表连接起来，选择课程号是0401010105的上课教师姓名即可。代码：/*显示选了课程号为“0401010105”的上课教师姓名*/use sunjialu3_datagoselect teacher_ as 姓名 from teach_schedule join teacher_infoon teach_schedule.teacher_id= teacher_info.teacher_idwhere teach_schedule.course_id='0401010105'25、统计不及格人

20、数在 3 人以上的课程的数量。方法：子查询方法，选出成绩<60的课程号，再将其根据课程号分组，选出其中人数>3的课程号，用计算函数count输出此类课程的数量即可。代码：/*统计不及格人数在3 人以上的课程的数量*/use sunjialu3_datagoselect count(course_id)as 课程数量 from lesson_infowhere course_id in(select course_id from stud_gradewhere grade<60group by course_idhaving count(stud_id)>3)26、显示“

21、计算机工程系”老师所教课程的成绩表。 方法：将dept_code，teach_schedule，lesson_info，teacher_info四个表连接起来，根据前面做题查找过计算机工程系系号为01，选择出相应课程表。代码：/*显示“计算机工程系”老师所教课程的成绩表*/use sunjialu3_datagoselect deptname as 系 ,name as 老师, course_name as 课程 from dept_codejoin teach_schedule on dept_code.deptcode=teach_schedule.deptcodejoin lesson_

22、info on teach_schedule.course_id=lesson_info.course_idjoin teacher_info on lesson_info.course_id=teacher_info.course_idwhere dept_code.deptcode='01'【思考题】1、 显示课程号为“0401010103”课程成绩高于“0401010104”课程的学生（同时选了两门课）的学号、姓名和课程“0401010103”成绩。要求给出两种以上（包括两种）的答案和相应结果。如果要求同时给出两门课的成绩，你有什么好办法吗？方法一：给stud_grade

23、表赋予两个别名one two，对其进行比较，在one表中选出0401010103课程，同时在two中选出0401010104课程，使其对应相等即得到同时选了两个课程的同学，最后使one中的03课程成绩>two中04课程的成绩，即可完成比较。代码：/*显示课程号为“”课程成绩高于“”课程的学生（同时选了两门课）的学号、姓名和课程“”成绩*/use sunjialu3_datagoselect distinct one.stud_id as 学号, as 姓名,one.grade as 课程成绩from stud_grade one,stud_grade twowhere one.course_id='0401010103' and two.course_id='0401010104'and one.stud_id=two.stud_id and one.grade>two.grade方法二：给stud_grade赋予两个别名，将两个别名代表的成绩表通过学号相同连接起来，同时选出03课程和04课