数据库查询资料
- 格式：doc
- 大小：141.50 KB
- 文档页数：15
一、 视图的创建：
1．创建计算机科学系学生信息的视图C_student
use xsgl
go
create view c_student
as
select sno,sname,ssex,birth,depart,dname from student s join department d on
s.depart=d.deno
where dname='计算机科学系'
2. 创建计算机科学系选修了ZB171401号课程的学生的视图C_course：
use xsgl
go
create view c_course
as
select s.sno,sname,ssex,birth,depart,dname,cno from student s join department
d on s.depart=d.deno
join sc on sc.sno=s.sno
where dname='计算机科学系' and cno='ZB171401'
3. 建立计算机科学系选修了ZB171401号课程且成绩在90分以上的学生的视图：
use xsgl
go
create view c_course
as
select s.sno,sname,ssex,birth,depart,dname,cno,grade from student s join
department d on s.depart=d.deno
join sc on sc.sno=s.sno
where dname='计算机科学系' and cno='ZB171401'and grade>90
4. 创建一个反映学生出生年份的视图：
use xsgl
go
create view s_year
as
select sno,sname,datepart(yyyy,birth) 出生年份from student
5. 将所有女生的记录定义为一个视图：
use xsgl
go
create view s_women
as
select *from student where ssex='女'
6. 将所有学生的学号和他的平均成绩定义为一个视图S_G：
use xsgl
go
create view S_G as
select S.sno ,avg(grade) 平均分from student S join sc on sc.sno=s.sno
group by S.sno
二、 视图结构的修改：
6. 将视图C_student修改为计算机科学系的所有女生的视图
use xsgl
go
ALTER view c_student
as
select sno,sname,ssex,birth,depart,dname from student s join department d on
s.depart=d.deno
where dname='计算机科学系' AND SSEX='女'
三、查询视图
7. 在计算机科学系的学生视图c_student中查询年龄小于22岁的学生:
use xsgl
go
select datediff(yy,birth,getdate()) as 年龄
from C_student
where datediff(yy,birth,getdate())<22
8. 查询计算机科学系选修了ZB171401号课程的学生:
use xsgl
go
SELECT * from c_course
9. 在视图S_G中查询平均成绩在90分以上的学生的学号和平均成绩:
select *
from S_G
where 平均分> 90
四、更新视图：
10．将计算机科学系学生视图C_student中学号为”061713101”的学生姓名改为”刘辰”:
use xsgl
go
update C_student
set sname='刘辰'
where sno='061713101'
比较: update C_Student set sname=‟刘辰‟ where sno=‟091714150‟ 此语句不能实现数据的更新.
(为什么呢? 因为在视图中并没有” 091714150”这条记录)
11. 向计算机科学系学生视图C_Student中插入一个新的学生记录, 学号为091714145,姓名为”赵新”, 出生日期为1991-1-2:
此操作能成功吗？
不能成功，格式不匹配，INSERT 语句与FOREIGN KEY 约束"FK_Student_Department"冲突。该冲突发生于数据库"xsgl"，表"dbo.Department", column 'Deno'。
12. 删除计算机科学系学生视图C_Student中学号为091714145的学生的记录:
use xsgl
go
delete from c_student
where sno='091714145'
五、删除视图：
13. 删除视图S_G:
Use xsgl
go
drop view s_g
思考:
1. 创建所有学生的基本信息和选课信息的视图
Select view s_sc as select student.* cno, grade from student, sc where student.sno=sc.sno
2. 基于上述视图查询各系学生各门功课的平均成绩.
Select sdept, cno, avg(grade) as avg_grade from s_sc group by cno, sdept.
1、使用XSGL数据库中适当的表，创建一个自定义函数—kccj，该函数可以根据输入的学生姓名返回该学生选修的课程名称和成绩。并调用该函数。
create function kcci(@sname varchar(10))
returns table
as
return (
select sname,s.sno,cname,grade from student s join
sc on sc.sno=s.sno join
course c on o=o
where sname=@sname
)
go
select * from kcci('张三')
go
2、使用XSGL数据库中适当的表，创建一个自定义函数—xbxs，该函数可以根据输入的系部名称返回该系学生的学号、姓名和出生日期。并调用该函数。
create function xbxs(@dname varchar(20))
returns table
as
return (
select sname,s.sno,birth from student s join
department d on d.deno=s.depart
where dname=@dname
) go
select * from xbxs('计算机科学系')
go
3、使用系统存储过程sp_helptext查看kccj函数的文本信息。
sp_helptext kcci
4、修改kccj函数，使该函数根据输入的学生学号返回该学生的姓名、选修课程名称和成绩。
alter function kcci(@sno char(9))
returns table
as
return (
select sname,cname,grade from student s join
sc on sc.sno=s.sno join
course c on o=o
where sno=@sno
)
Go
5、删除xbxs函数。
drop function xbxs
6、自定义一个函数，其功能是将一个百分制的成绩按范围转换为“优秀”，“良好”，“通过”，“不及格”。并调用该函数。
CREATE FUNCTION sc_pass
(@grade1 int)
RETURNS CHAR(8)
AS
BEGIN
DECLARE @info char(8)
IF @grade1>=90 SET @info='优秀'
ELSE IF @grade1>=75 SET @info='良好'
ELSE IF @grade1>=60 SET @info='通过'
ELSE SET @info='不通过'
RETURN @info
END
select dbo.sc_pass(95)
7、定义一个内嵌表值函数，通过课程名、系名称可以查询某系中选修了该课程的全部学生名单和成绩。并调用该函数。
CREATE FUNCTION D_courseG(@dept char(20),@Cna varchar(20))
RETURNS TABLE
AS
RETURN (SELECT Sname,Cname,Grade FROM Student S,SC,Course C
WHERE S.Sno=SC.Sno AND o=o AND Depart=@dept AND @Cna=ame)
GO
Select * from d_courseg('17','操作系统')
8、创建自定义函数top_grade( ),根据输入的系代码统计出该系平均成绩最高的前三名同学的信息。并调用
