SQLServer实验二

1、实验二实验4student表：course表：student_course表：(1) 简单查询。练习编写简单查询的t-sql语句，并查看运行结果。可参照如下问题进行练习。1 查询计算机系学生的学号和姓名select sno,snamefrom studentwhere dept = 计算机系; 2 查询选修了课程的学生学号select snofrom student_course;3 查询选修课程号为0101的学生姓名和成绩，查询结果按成绩降序排列select sname,scorefrom student join student_course on student_course.sno =

2、 student.snoorder by score desc;4 查询选修课程号为0101的成绩在80-90分之间的学生学号和成绩，并将成绩乘以系数0.9输出select sno,score*0.9from student_course where score between 80 and 90;5 查询网络系姓徐的学生的信息select student.sno,sname,sex,birthday ,dept,student.spno,classno,co,cname,lecture,semester,credit,scorefrom student_coursejoin

3、student on student_course.sno=student.snojoin course on student_o=owhere dept=网络系 and sname like 徐%; select sno,cno from student_coursewhere score is null;(2) 连接查询。练习编写实现连接查询的t-sql语句，并查看运行结果。可参照如下问题进行练习。1 查询每个学生的情况及他(她)所选的课程select * from student join student_course on student.sno=s

4、tudent_course.sno2 查询学生的学号、姓名选修的课程名及成绩select student.sno,sname,ame,scorefrom student_course join course on student_o=ojoin student on student.sno=student_course.sno3 查询选修计算机网络课程且成绩为80分以上的学生学号、姓名和成绩加徐家兴：查询结果：select student.sno,sname,scorefrom student_coursejoin course on st

5、udent_o=ojoin student on student.sno=student_course.snowhere cname=计算机网络 and score80;实验5(1) 在查询编辑器中编写完成实现查询功能t-sql语句，并查看运行结果。可参照如下问题进行练习。1 查询选修了计算机网络的学生学号和姓名。select student.sno,snamefrom student_coursejoin course on student_o=ojoin student on student.sno=student_c

6、ourse.snowhere cname=计算机网络;2 查询0101课程的成绩高于刘招香的学生学号和成绩select student.sno,scorefrom student_coursejoin course on student_o=ojoin student on student.sno=student_course.snowhere student_o=0101 and score(select score from student_coursejoin student on student.sno=student_course

7、.snowhere sname=刘招香 and student_o=0101);3 查询同刘招香数据库原理课程分数相同的学生学号select snofrom student_coursewhere score=(select score from student_coursejoin student on student.sno=student_course.snojoin course on student_o=owhere sname=刘招香 and cname=数据库原理);4 查询选修0206课程的学生姓名select studen

8、t.snamefrom student_coursejoin student on student.sno=student_course.snowhere student_o=0206;5 查询没有选修0206课程的学生姓名select distinct snamefrom student_coursejoin student on student.sno=student_course.snowhere sname not in (select sname from student_coursejoin student on student.sno=student_cours

9、e.snowhere student_o=0206);6 查询选修了全部课程的学生姓名select snamefrom studentwhere not exists(select * from course where not exists(select * from student_course where student_course.sno=student.sno and student_o=o);7 查询与学号0705010119的学生所选修的全部课程相同的学生学号和姓名select distinct student.sno,sn

10、amefrom studentjoin student_course on student.sno = student_course.snowhere not exists(select * from student_course sc1 where sno = 0705010119and not exists(select * from student_course sc2 where sc1.cno=sc2.cno and sc2.sno=sc2.sno)and not exists(select * from student_course sc1 where sc1.sno=studen

11、t.snoand not exists(select * from student_course sc2 where sno = 0705010119 and sc2.cno=sc1.cno);（2）在查询编辑器中编写完成实现统计查询功能t-sql语句，并查看运行结果。可参照如下问题进行练习。1 查询选修“计算机基础”课程的学生的平均成绩。select avg(score)from student_coursejoin course on student_o=owhere cname=计算机基础;2 查询选修“计算机基础”课程成绩比此课程平均成绩高的学生学号

12、和成绩select student.sno,scorefrom student_coursejoin course on student_o=ojoin student on student.sno=student_course.snowhere cname=计算机基础 and score (select avg(score)from student_coursejoin course on student_o=owhere cname=计算机基础);3 查询选修计算机基础和计算机网络的学生学号和成绩select student.sno,scorefrom student_coursejoin course on student_o=ojoin student on student.sno=student_course.snowhere cname=计算机基础 or cname=计算机网络;4 统计各系各门课的平均成绩select dept, cname,avg(score) as scorefrom student_coursejoin student on st