西安石油大学 数据库实验2 数据查询

西安石油大学 数据库实验2 数据查询

实验2 数据查询

一、实验目的

1. 加深对SQL语言和Transact-SQL语言的查询语句等数据操纵语句的理解。 2. 熟练掌握SQL Server查询分析器的使用方法。

3. 熟练掌握数据的简单查询、连接查询、嵌套查询和集合查询； 4. 熟练掌握数据查询中的分组统计、排序等操作； 5. 熟练掌握多表数据之间的内连接、外连接等操作； 6. 熟练掌握利用查询进行的数据的增加、修改和删除操作。 二、实验环境

硬件环境：PC机

软件环境：操作系统为Microsoft Windows 2000或以上版本。

数据库管理系统为Microsoft SQL Server 2000标准版或企业版。

三、实验内容

1. 熟悉SQL Server企业管理器和查询分析器工具的使用方法。

2. 利用Transact-SQL实现数据的简单查询、连接查询、嵌套查询和集合查询； 3. 利用Transact-SQL实现数据查询中的分组统计、排序等操作； 4. 利用Transact-SQL实现多表数据之间的内连接、外连接等操作； 5. 利用Transact-SQL实现通过查询进行的数据的增加、修改和删除操作。 四、实验步骤

1. 熟悉SQL Server企业管理器和查询分析器工具的使用方法。 2. 利用企业管理器实现：

(1)向已经建好的学生_课程数据库中的学生、课程和选课3个表中增加数据、修改数据、删除数据。

(2)进入SQL Server查询分析器，首先打开利用实验1已经建立并保存过的学生_课程数据库及三个表的语句序列并执行，然后利用Transact-SQL实现：

1)检索未选修任何课程的学生学号； select sno from S

where sno not in (

select sno from SC

)

西安石油大学 数据库实验2 数据查询

检索赵老师所授的课程号，课程名； select cno,cname from C

where tname like'赵

%'

检索所有姓李的同学的基本信息； select * from S where sname like'李

%'

西安石油大学 数据库实验2 数据查询

检索选修“数据结构”课程的学生的学号； select sno from SC,C

where http://doc.guandang.neto=http://doc.guandang.neto and cname='数据结构

'

检索年龄介于张强同学年龄和28岁之间的学生的基本信息； select * from S

where age between '28'and(select age from S where sname='张强') or age between(select age from S where sname='张强') and

'28'

检索至少选修了一门朱虹所讲授课程的学生姓名，学号； select distinct sname,S.sno from C,SC,S

where tname='朱虹'and http://doc.guandang.neto=http://doc.guandang.neto and SC.sno=S.sno

西安石油大学 数据库实验2 数据查询

将学生表中查询的数据保存到一张临时表newtable中。

select * into newtable from S

2) 统计所有学生选课的门数；

select sno 学生号,count(*)选课门数 from SC

group by sno

西安石油大学 数据库实验2 数据查询

统计教师李兰所授每门课程学生的平均成绩；

select cno,'平均成绩'=avg(grade) from SC group by cno having cno in (select cno from C

where tname='李兰'

)

统计所有选修人数多于3人的课程号和选课人数，并按人数降序排列，若人数相等则按课程号升序排列；

select cno,'人数'=count(distinct sno) from SC group by cno having count(distinct sno)>3

order by count(distinct sno) desc,cno asc

西安石油大学 数据库实验2 数据查询

检索“高等数学”成绩高于该课平均成绩的同学学号；

select distinct sno from SC,C

where cname='高等数学' and (grade>( select avg(grade)

from SC,C where cname ='高等数学

')) and http://doc.guandang.neto=http://doc.guandang.neto

检索所有成绩不及格的同学的学号，姓名和课程号。

select S.sno,sname,cno from S,SC

where S.sno=SC.sno and grade<'60'

update SC set grade=grade+5 where sno in (select sno from S

where dept = '软件工程')

and cno='720'

3）将“软件工程”专业选修课程号为“720”的成绩全部提高5分；

西安石油大学 数据库实验2 数据查询

创建一个新表，表名为new,用来存放成绩大于60分得学生信息，此表包括：学号，姓名，课程名和成绩。然后从利用实验1已经建立并保存过的学生表、课程表、选课表中查找相关数据信息装入此表中。

create table new1( sno varchar(9),

sname varchar(15) not null, cname varchar(45) not null, grade int null)

select S.sno,sname,http://doc.guandang.netame,grade into new from S,C,SC

where S.sno=SC.sno and http://doc.guandang.neto=http://doc.guandang.neto and grade>'60'

4）检索所有学生的学号、姓名、课程号和成绩（注：没选某门课的学生成绩相应为NULL）。

select S.sno,sname,cno grade

西安石油大学 数据库实验2 数据查询

from S left join SC

on S.sno=SC.sno