实验8 数据库合查询.docVIP

实验8 数据库综合查询 一、实验目的 掌握SELECT语句的基本语法和查询条件表示方法； 掌握查询条件种类和表示方法； 掌握连接查询的表示及使用； 掌握嵌套查询的表示及使用； 了解集合查询的表示及使用。 二、实验环境 已安装SQL Server 2005的计算机；具有局域网环境,有固定IP。 三、实验学时 2学时 四、实验要求 了解SELECT语句的基本语法格式和执行方法； 了解连接查询的表示及使用； 了解嵌套查询的表示及使用； 了解集合查询的表示及使用； 完成实验报告； 五、实验内容及步骤 以实验8数据为基础，请使用T-SQL 语句实现进行以下操作： 查询以‘数据_’开头,且倒数第3个字符为‘结’的课程的详细情况； select * from course where Cname like 数据%结__; 查询名字中第2个字为‘阳’的学生姓名和学号及选修的课程号、课程名； select Cno,Cname from course where exists (select * from sc where exists (select * from student where course.Cno = sc.Cno and Sname like _阳%)); select student.Sno,Sname,Sdept,course.Cno,Grade from student,sc,course where Cname in (数学,大学英语) and course.Cno = sc.Cno and student.Sno = sc.Sno; ; select * from student where Sno in (select distinct Sno from sc where Grade IS NULL); 查询与‘张力’(假设姓名唯一)年龄不同的所有学生的信息； select * from student where Sage !=any (select Sage from student where Sname = 张力);select student.Sno,Sname,avg(Grade) as 平均成绩 from student inner join sc on student.sno=sc.sno group by student.Sno,Sname having avg(Grade) (select avg(Grade) from student inner join sc on student.sno=sc.sno where sc.Sno = student.Sno and Sname = 张力);select student.Sno,Sname,Sdept,sum(case when grade60 then ccredit end) as 已修学分 from student,sc,course group by student.Sno,Sname,Sdept;select student.Sno,Sname,Sdept,Grade from student,sc where student.Sno = sc.Sno and student.Sno in(select sc.Sno from student,sc where student.Sno = sc.Sno group by sc.Sno having count(sc.Sno) = 1); 查找选修了至少一门和张力选修课程一样的学生的学号、姓名及课程号； select student.Sno,Sname,Cno from student,sc where student.Sno = sc.Sno and Cno in(select Cno from student,sc where Sname = 张力and student.Sno = sc.Sno);select S.Sno,S.Sname,S.Ssex,S.Sage,S.Sdept from student S where S.Sno in (select S1.Sno xuehao from sc S1,sc S2 where S1.Sno=S2.Sno and S1.Cno=( select Cno fro