实验三,,数据库中数据查询及视图操作实验报告[精选合集]

第一篇：实验三,,数据库中数据查询及视图操作实验报告

长春大学计算机学院 科学与技术 专业

数据库原理

实验报告

实验名称：

实验 三

数据库中的数据查询及视图操作(1)班 班

级：

姓 姓

名：

学 学

号：

实验地点：

日 日

期：

一、实验目的：

1．继续熟练 SQL SERVER 202_/202_ 系统或 KingBase ES V7.0 的使用； 2．掌握 SELECT 语句的使用。

3.掌握单表查询，多表查询以及嵌套查询。

二、实验内容、要求和环境：

【 实验要求】

注：将完成的实验报告重命名为：班级+学号+姓名+（实验三），（如：041540538张三（实验三）），提交到SPOC学堂。

1.实验课要携带教材、学习辅导、老师下发的实验报告文档等。

2.课前要对实验内容和步骤部分进行预习。

【实验环境】

1.SQL SERVER 202_/202_； 2.KingBase ES V7.0，人大金仓。

【实验内容和步骤】

针对实验一建立的数据库的表，用 select 语句完成如下查询操作，写出 select 语句，并给出操作结果。

1.针对 SPJ 数据库中的四个表，实现如下查询：

(1)求供应工程 J1 零件的供应商号码 SNO；(2)求供应工程 J1 零件 P1 的供应商号码 SNO；(3)求供应工程 J1 零件的供应商名 SNAME;(4)求供应工程 J1 零件 P1 的供应商名 SNAME；(5)求供应工程 J1 零件为红色的供应商号码 SNO；(6)求没有使用天津供应商生产的红色零件的工程号 JNO；(7)求至少用了供应商 S1 所供应的全部零件的工程号 JNO；(8)找出使用上海产的零件的工程名称； 2.针对学生-课程数据库中的三个表，用嵌套方法查询实现如下查询：

（1）查询选修了数据库的学生姓名。

（2）查询比计算机系 CS 所有学生年龄都大的学生信息。

（3）列出“李勇”选修的所有课程的课名和成绩。

（4）查询数据库的先行课的课程名。

3．针对实验一建立的 SPXS 数据库中的三个表，实现如下查询：

精选文档

(1)查询与商品“电视”颜色相同的商品名；(2)查询不销售商品 P2 的商店名；(3)查询至少销售商品 P1、P2 两种商品的商店名；(4)所有商店都销售的商品号。

(5)只销售一种商品P4的商店名。

三、实验结果与分析：

写出操作语句，粘贴查询结果(粘贴结果要求粘贴 SQL Server Managemet Studio 整个窗口)：

1.(1)

(2)

精选文档

(3)

精选文档

(4)

精选文档

(5)

精选文档

(6)

精选文档

(7)

精选文档

精选文档

(8)

精选文档

2.(1)

精选文档

(2)

精选文档

(3)

精选文档

(4)

精选文档

3.(1)

精选文档

(2)

精选文档

(3)

精选文档

(4)

精选文档

(5)

精选文档

四、思考题：

1.在操作中遇到什么问题？如何解决的？

第三题第二问不知道如何将在全部的 sno 中剔除当 pno=’p2’是的 sno.语句不知道怎样写出。

五、教师评语：

实验成绩：

：

教师：

（签名））

****年**月**日 日

创新活动

精选文档

第二篇：数据库--数据查询和操作

《数据查询与操作》实验

一、实验目的与要求

1、理解简单查询和复合查询的意义。

2、掌握SELECT语句和各子句的使用。

3、掌握多表查询和子查询的使用。

4、掌握INSERT、UPDATE和DELETE语句的使用。

二、实验平台

1、操作系统：Windows XP或Windows 202_

2、数据库管理系统：SQL Server 202_

三、实验内容

1.在“人事管理系统”中，新增一个员工信息（员工编号„100508‟、员工姓名„小龙女‟、所在部门编号„10001‟、籍贯„河南‟）。

insert into 员工信息(员工编号,员工姓名,所在部门编号,籍贯)values('100508','小龙女','10001','河南')

2.将“人事管理系统”数据库的“员工信息”表中籍贯为“河南”并且所在部门编号为”10001”数据插入到“新员工信息”表中。

insert into 新员工信息(员工编号,员工姓名,所在部门编号)select 员工编号,员工姓名,所在部门编号from 员工信息 where 籍贯='河南'and 所在部门编号='10001'

3.在“人事管理系统”数据库“部门信息”表中，将部门的员工人数设置为10。

4.在“人事管理系统”数据库“员工信息”表中，将文化程度为“大专”，并且在“202_-05-01”到“202_-05-01”之间入职的所有员工调动到编号为“10006”的部门去。

5.在“人事管理系统”数据库中对部门进行了重组和调整，原来编号为10006的部门名称变为“市场开发部”，人数也调整为20人。

6.在“人事管理系统”数据库中，编号为“100503”的新员工升级为正式员工，需要在“新员工信息”表中删除他的记录。

7.在“人事管理系统”数据库中，删除“新员工信息”表中所有在编号为“10005”部门工作的员工记录。

delete 20 percent from 员工信息

8.在“人事管理系统”数据库中，需要删除20%的员工信息。

9.在“教务管理系统”数据库“班级信息”表中，查询分配了班主任的班级信息。

10.在“教务管理系统”数据库“学生信息”表中，根据籍贯查询各省学生人数，并显示省份和人数信息。

11.在“教务管理系统”数据库中，根据相关表的内容，查询平均成绩大于70的课程信息。

select *

from 课程信息 where 课程编号In(select 课程编号

from 成绩表

group by 课程编号 having avg(成绩)>70)

12.在commidtysell数据库中，根据相关表的内容，查询销售同类产品的商场编号（去除相同的行）。

select distinct e1.empID as 商场编号 from EmporiumSell e1,EmporiumSell e2 where e1.ProdID=e2.ProdID

13.在commidtysell数据库中，根据相关表的内容，查询买出产品数量最多的商场编号和销售总量信息。

select e1.EmpID,sum(e1.SellAmout)as 销售总量 from EmporiumSell e1 group by e1.EmpID having sum(e1.SellAmout)>=all(select sum(SellAmout)from EmporiumSell group by EmpID

14.在TeachingManageSYS数据库中，根据相关表的内容，统计年龄大于平均年龄的教师的Tname、Tsex、Tage、Trank，所教授课程的Cname，Credit，CouresHour。

select T.Tname,T.Tsex,T.Tage,T.Trank,Cname,Credit,CouresHour from Teach,CourseInfo,TeacherInfo T where T.Tno=Teach.Tno and Teach.Cno=CourseInfo.Cno and Tage>(select avg(Tage)from TeacherInfo)

15.在“人事管理系统”数据库“员工信息”表中，查询工龄大于4年的员工信息

16.在“人事管理系统”数据库“员工信息”表中，把政治面貌是“党员”的职工信息保存到表名为“党员信息”新表中。

将学生“陈霞”所在班级的其他学生并且成绩不合格的学生成绩删除

select 成绩表.学号,成绩表.成绩,学生信息.姓名,学生信息.班级编号 from 成绩表,学生信息

where 成绩表.成绩<60 and 学生信息.班级编号in(select 班级编号 from 学生信息 where 姓名='陈霞')

17.在“人事管理系统”数据库中，使用TRUNCATE TABLE语句删除“党员信息”表中的所有记录。

18.在“人事管理系统”数据库“员工信息”表中，把员工“李朋”所任职位更改为“副经理”。

19.查询“学生信息”表中姓名包含“红”字的学生的学号、姓名、性别、民族、籍贯和班级编号信息。

20.查询“学生信息”表中姓氏为“徐”的学生的学号、姓名、性别、民族、籍贯和班级编号信息。

21.从“教务管理系统”数据库中，查询人数超过45人所在班级中的学生的学号、姓名和班级名称。（使用子查询）

22.查询“学生信息”表中姓名不包含“红”字的学生的学号、姓名、性别、民族、籍贯和班级编号信息。

23.在“教务管理系统”数据库中，查询还未分配班主任的班级信息。

24.在“人事管理系统”数据库“员工信息”表中，将文化程度为“大专”，并且在“202_-05-01”到“202_-05-01”之间入职的所有员工调动到编号为“10006”的部门去。

25.查询数据表ProductInfo中前5条记录。

26.从ProductInfo表中，查询产品名称为“丙电视机”的产品信息。

27.从ProductInfo表中，查询FactAddr为“四川绵阳”或“广东东莞”，同时StockAmount大于110的产品的ProdID、StockAmount和FactAddr信息，并且给列名分别取别名为产品编号、库存数量和厂商地址。

28.从ProductInfo表中，查询StockAmount在250和400(包含200和400)之间的产品的ProdName和StockAmount。

29.创建一个学生补考表，表中数据来自成绩表，条件为成绩小于60分，要求补考表中仅显示学生的学号和补考课程的编号。编写SELECT INTO语句完成这操作。

30.在“教务管理系统”数据库中，查询出所有非计算机系的学生信息，并显示出这些学生的考试成绩，再按成绩进行降序排列显示，要求返回学生的学号、姓名、班级编号、课程编号和成绩信息。（多表查询）

31.在“教务管理系统”数据库中，查询学生姓名为“朱文娟”所在班级的学生信息，要求返回学生的学号、姓名、性别、班级编号、班级名称和年级信息。

select 学生信息.学号,学生信息.姓名,学生信息.性别,班级信息.班级编号,班级信息.班级名称,班级信息.年级 from 学生信息,班级信息

where 学生信息.班级编号=班级信息.班级编号

and 学生信息.姓名='朱文娟'

32.查询空调产品在ProductInfo的相关信息，要求该产品在EmporiumSell中的销售总量超过10台。

33.在“教务管理系统”数据库中，查询出任意一个大于平均成绩的学生的学号、姓名、班级编号、课程编号和成绩信息。

select 学生信息.学号,学生信息.姓名,学生信息.班级编号,成绩表.课程编号,成绩表.成绩 from 学生信息,成绩表

where 学生信息.学号=成绩表.学号and 成绩>any(select avg(成绩)from 成绩表

group by 课程编号)

34.在“教务管理系统”数据库中，对于成绩大于等于90的数据行进行查询，并返回考试成绩的学生的学号、姓名、性别、籍贯和所在班级名称及年级信息。

select 学生信息.学号,姓名,性别,籍贯,班级名称,班级信息.年级 from 学生信息,成绩表,班级信息

where 学生信息.班级编号=班级信息.班级编号and 学生信息.学号=成绩表.学号and 成绩>=90

35.在“人事管理系统”数据库的“员工信息”表中，查询“所任职位”为“经理”的员工编号和员工姓名信息，并为其增加新列“所在位置”，列的内容为“员工信息表”；从“部门信息”表中查询所有的部门编号和部门名称信息，并定义新增列的内容为“部门信息表”；最后将两个查询结果联合在一起。

select 员工编号,员工姓名 from 员工信息

where 所任职位='经理' alter table 员工信息 add 所在位置 nvarchar(20)

select 部门编号,部门名称 from 部门信息

alter table 部门信息

add 部门信息表 nvarchar(20)

select 员工编号,员工姓名

from 员工信息 FULL join 部门信息

on 员工信息.所在部门编号=部门信息.部门编号

36.在“教务管理系统”数据库的“学生信息”表和“成绩”表中，查询出所有考试及格的学生的成绩信息，包括学生的学号、姓名、性别、年级、班级编号及考试成绩，并且按照成绩进行降序排列。

select 学生信息.学号,姓名,性别,年级,班级编号,成绩 from 学生信息,成绩表 group by 学生信息.学号 having 成绩>60 order by 成绩desc

37.从“教务管理系统”数据库中的“学生信息”和“班级信息”两表中，查询学生和对应的班级信息，要求返回的结果中包含学生的学号、姓名、性别和班级名称及年级。

select 学号,姓名,性别,班级信息.年级,班级名称 from 学生信息,班级信息

where 学生信息.班级编号=班级信息.班级编号

38.在“教务管理系统”数据库的“学生信息”表和“成绩”表中，查询所有学生的考试成绩信息，包括学生学号、姓名、课程编号和成绩信息。(左、右、全外连接)

select 学生信息.学号,姓名,课程编号,成绩 from 学生信息left join 成绩表 on 学生信息.学号=成绩表.学号

select 学生信息.学号,姓名,课程编号,成绩 from 学生信息right join 成绩表 on 学生信息.学号=成绩表.学号

select 学生信息.学号,姓名,课程编号,成绩 from 学生信息full join 成绩表 on 学生信息.学号=成绩表.学号

39.在“人事管理系统”数据库中，对“员工信息”表和“部门信息”表进行交叉查询，要求查询返回所任职位为“职员”的员工编号、员工姓名、所任职位、文化程度和部门名称，并按照员工编号降序排序列。

select 员工编号,员工姓名,所任职位,文化程度,部门名称 from 员工信息,部门信息

where 员工信息.所在部门编号=部门信息.部门编号

四、实验过程

写出相应的T-SQL语句，并写出T-SQL语句执行结果的前两行记录。

五、实验总结

简述实验完成情况、实验过程中各自碰到的问题、各自对实验目的掌握情况。

第三篇：数据库实验(集合查询与视图的创建)

实验五 集合查询和视图的创建

一、实验目的

1.掌握集合查询涉及的SQL语句 2.掌握视图创建的SQL语句

二、实验内容

1.查询信息系的男生或者是计算机系的女生名单，输出姓名，性别，系名。（并操作UNION）

2.查询计算机系年龄不大于20岁的学生。（交操作INTERSECT）

3.查询计算机系年龄大于19岁的学生。（差操作EXCEPT）

4.创建一个视图view1，要求输出学号，姓名，课程号，分数。

5.基于view1和Course查询每个人各门课程的平均分，输出学号，姓名，平均分，按平均分降序排列。

6.基于view1和Course查询每个人选课的总学分数，输出学号，姓名，总学分数。

三．心得体会

此次试验中要注意集合查询中的各种谓词的正确用法和视图的创建；另外应该掌握有视图的查询方法。

第四篇：实验三 数据库安全性实验报告

一 实验目的1.熟悉通过SQL语句对数据进行安全性控制；

2.针对具体应用要求，完成授权和收回权限的操作；

二 实验工具SQL Server 202_

利用SQL Server 202_ SSMS及其SQL查询编辑器。

三 实验内容和要求

使用SQL对数据进行安全性控制，包括：授权和权力回收。操作完成后看看已授权的用户是否真正具有授予的数据操作的权力了；权力收回操作之后的用户是否确实丧失了收回的数据操作的权力。根据以上要求认真填写实验报告，记录所有的实验用例。具体操作可以参照课件中的实例。

具体操作内容：

创建以下两个关系模式，并分别插入相应的数据。

职工（职工号，姓名，年龄，职务，工资，部门号）

部门（部门号，名称，经理名，地址，电话号码）

（1）请用SQL的Grant和Revoke语句（加上视图机制）完成以下授权定义或存取控制功能，并进行相应的安全性测试；

（a）用户王明对两个表有select权力；

（b）用户李勇对两个表有insert和delete权力；

（c）每个职工只对自己的记录有select权力；

（d）用户刘星对职工表有select权力，对工资字段具有更新权力；

（e）用户张新具有修改这两个表的结构的权力；

（f）用户周平具有对两个表所有权力（对，插，改，删数据），并具有给其他用户授权的权利；

（g）用户杨兰具有从每个部门职工中select最高工资，最低工资，平均工资的权力，他不能查看每个人的工资。

（2）把上面（a）～（g）的每一种情况，撤销各用户所授予的权力。

四 实验报告

4.1 实验环境：

Windows XP

MicrosoftSQL server Management Studio 202_

4.2 实验内容与完成情况：

--新建数据库

create database work

go

use work

--创建以下两个关系模式，并分别插入相应的数据。

--Worker（职工号，姓名，年龄，职务，工资，部门号）

go

create table Worker（职工号 varchar(10),姓名 char(10),年龄 int,职务 char(15),工资 int,部门号 char(50))

--Section（部门号，名称，经理名，地址，电话号码）

create table Section（职工号 char(10),名称 char(10),经理名 char(10),地址 char(50),电话 char(15));

go

--插入部门信息

insert into Section values('a001','市场部门','李永','白理工','***');

insert into Section values('a002','营销部门','李刚','白理工','***');

--插入职工信息

insert into Worker values('a001001','王明',20,'市场营销员',202_,'a001');

insert into Worker values('a001002','李村',29,'市场营销员',202_,'a001');

insert into Worker values('a001003','李永',20,'经理',5000,'a001');

insert into Worker values('a002001','罗杰',18,'销售员',202_,'a002');

insert into Worker values('a002002','李红',24,'销售员',202_,'a002');

insert into Worker values('a002003','李刚',32,'经理',5000,'a002');

--查询部门表和职工表的数据

select *from Worker;

select *from Section;

--授权

use work

go

--（a）用户王明对两个表有select权力；

create login [王明] with password=N'1234';

create user [王明] for login [王明];

grant select on Worker to [王明];

grant select on Section to [王明];

--插入数据权限测试

insert into Worker values('a002004','罗红',28,'销售助理',4000,'a002');

--查询数据权限测试

select *from Worker;

--（b）用户李勇对两个表有insert和delete权力；

use work

go

create login [李勇] with password=N'1234';

create user [李勇] for login [李勇];

grant insert,delete on Worker to [李勇];

grant insert,delete on Section to [李勇];

--插入数据权限测试

insert into Worker values('a002005','罗莉',52,'销售助理',4000,'a002');

--查询数据权限测试

select *from Worker;

--删除权限测试

delete Worker from where 职工号='a002001';

--（c）每个职工只对自己的记录有select权力；

use work

go

create view Myself as select * from Worker

where 姓名=user;

grant select on Myself to public;

--（d）用户刘星对职工表有select权力，对工资字段具有更新权力；

use work

go

create login [刘星] with password=N'1234';

create user [刘星] for login [刘星];

grant select,update(工资)on Worker to [刘星];

--更新修改权限测试

update Worker set 工资=3500 where 职工号='a002001'

--查询数据权限测试

select *from Worker;

--（e）用户张新具有修改这两个表的结构的权力；

use work

go

create login [张新] with password=N'1234';

create user [张新] for login [张新];

grant all PRIVILEGES on Worker to 张新;

grant all PRIVILEGES on Section to 张新;

--（f）用户周平具有对两个表所有权力（对，插，改，删数据），并具有给其他用户授权的权利；

use work

go

create login [周平] with password=N'1234';

create user [周平] for login [周平];

grant all PRIVILEGES on Worker to 周平with grant option;

grant all PRIVILEGES on Section to 周平with grant option;

--（g）用户杨兰具有从每个部门职工中select最高工资，最低工资，平均工资的权力，他不能查看每个人的工资。

use work

go

create login [杨兰] with password=N'1234';

create user [杨兰] for login [杨兰];

grant view Wage(max(工资)，min(工资)，avg(工资))

as select max(工资)，min(工资)，avg(工资)from Worker Group by 部门号； grant select on Wage to 杨兰;

--（2）把上面（a）～（g）的每一种情况，撤销各用户所授予的权力。

use work

go

--注销用户王明，收回权力

revoke select on WOrker from [王明];

revoke select on Section from [王明];

drop user [王明];

drop login [王明];

use work

go

--注销用户李勇，收回权力

revoke select on Worker from [李勇];

revoke select on Section from [李勇];

drop user [李勇];

drop login [李勇]

--撤销用户对自己的记录有select权力

use work

go

revoke select on Myself from public;

drop view Myself cascade;

use work

go

--注销用户刘星，收回权力

revoke select,update(工资)on Worker from [刘星];

drop user [刘星];

drop login [刘星];

use work

go

--注销用户张新，收回权力

revoke alter on table Worker,Section from [张新];

drop user [张新];

drop login [张新];

use work

go

--注销用户周平，收回权力

revoke all PRIVILEGES on table Worker from [周平] cascade;

revoke all PRIVILEGES on Section from [周平] cascade;

drop user [周平];

drop login [周平];

--注销用户杨兰，收回权力

revoke select on Wage from [杨兰];

drop view Wage cascade;

drop user [杨兰];

drop login [杨兰];

use master

go

--删除数据库work

drop database work;

4.3 出现的问题：

在更改连接的时候使用SQL server身份验证登陆，用新建的用户名登陆失败，显示

该用户与可信SQL server无关联

4.4 解决方案（列出遇到的问题和解决办法，列出没有解决的问题）： 启用SQL Server身份验证

SQL Server Management Studio--对象资源管理器--右键 你的服务器(.SQLExpress或者localhost)--属性--安全性--服务器身份验证--SQL Server和Windows身份验证模式，最后重启一下数据库

第五篇：数据库-第四次实验报告-视图-t-sql语句

实验十报告 创建视图

实验目的

1．掌握创建视图的SQL语句的用法。

2．掌握使用企业管管理器创建视图的方法。3．掌握使用创建视图向导创建视图的方法。4．掌握查看视图的系统存储过程的用法。5．掌握修改视图的方法。一． 实验准备

1．了解创建视图方法。

2．了解修改视图的SQL语句的语法格式。3．了解视图更名的系统存储过程的用法。4．了解删除视图的SQL语句的用法。二． 实验要求

1.用不同的方法创建视图。

2.提交实验报告，并验收实验结果。三． 实验内容 1.创建视图

（1）使用企业管理器创建视图

① 在EDUC库中以“student”表为基础，建立一个名为“V_计算机系学生”的视图。在使用该视图时，将显示“student”表中的所有字段.兰州大学数据库实验报告

视图如下：

(2)使用SQL语句创建视图 ① 在查询分析器中建立一个每个学生的学号、姓名、选修的课名及成绩的视图S_C_GRADE； Create VIEW S_C_GRADE AS

SELECT student.sno,sname,cname,score FROM student,course,student_course WHERE student.sno=student_course.sno AND course.cno=student_course.cno;

视图如下：

②建立一个所有计算机系学生的学号、选修课程号以及平均成绩的视图COMPUTE_AVG_GRADE;Create VIEW COMPUTE_AVG_GRADE AS

SELECT student.sno,course.cno,AVG(score)'平均成绩' FROM student,course,student_course WHERE student.sno=student_course.sno

AND course.cno=student_course.cno AND student.dno='CS' GROUP BY student.sno,course.cno;

视图如下：

2.修改视图

(1)使用企业管理器修改视图

在企业管理器中将视图COMPUTE_AVG_GRADE中改成建立在数学系的学生学号、选修课程号以及平均成绩的视图。（IS设为数学系）

(2)使用SQL语句修改视图

① 在查询分析器中使用更改视图的命令将上面建立的视图“V_计算机系学生”更名为“V_计算机系男生”。

SP_RENAME 'V_计算机系学生','V_计算机系男生';

3.删除视图

（1）使用企业管理器删除视图

用企业管理器删除视图“V_计算机系学生”

（2）使用SQL语句删除视图

用SQL语句删除视图COMPUTE_AVG_GRADE;DROP VIEW COMPUTE_AVG_GRADE;

实验十一 使用视图

一． 实验目的

1.熟悉和掌握对数据表中视图的查询操作和SQL命令的使用；

2.熟悉和掌握对数据表中视图的更新操作和SQL命令的使用，并注意视图更新与基本表更新的区别与联系

3.学习灵活熟练的进行视图的操作，认识视图的作用 二． 实验准备

1．熟悉SQL SERVER 工作环境。2．能连接到EDUC数据库。

3．复习有关视图操作的SQL语言命令。三． 实验要求

1.在实验开始之前做好准备工作。

2.实验之后提交实验报告，思考视图和基本表的区别 四． 实验内容 一． 定义视图

在EDUC数据库中，已Student Course 和Student_course 表为基础完成一下视图定义 １． 定义计算机系学生基本情况视图V_Computer；

CREATE VIEW V_Computer AS SELECT student.* FROM student WHERE student.dno='CS';

视图如下：

２． 将Student Course 和Student_course表中学生的学号，姓名，课程号，课程名，成

绩定义为视图V_S_C_G CREATE VIEW V_S_C_G AS SELECT student.sno,sname,course.cno,course.cname,score FROM student,course,student_course WHERE student.sno=student_course.sno course.cno=student_course.cno;

AND

视图如下：

３． 将各系学生人数，平均年龄定义为视图V_NUM_AVG CREATE VIEW V_NUM_AVG AS SELECT COUNT(dno)'各系人数',AVG(sage)'平均年龄' FROM student GROUP BY dno;

视图如下：

４． 定义一个反映学生出生年份的视图V_YEAR CREATE VIEW V_YEAR AS SELECT sname,202_-sage '出生年份' FROM student;

视图如下：

５． 将各位学生选修课程的门数及平均成绩定义为视图V_AVG_S_G

CREATE VIEW V_AVG_S_G AS SELECT student.sno '学号',sname '姓名',COUNT(*)'选修门数',AVG(score)'平均成绩' FROM student,student_course WHERE student.sno=student_course.sno GROUP BY student.sno,sname;

视图如下：

６． 将各门课程的选修人数及平均成绩定义为视图V_AVG_C_G CREATE VIEW V_AVG_C_G AS SELECT cno '课程号',COUNT(cno)'选修人数',AVG(score)'平均成绩' FROM student_course GROUP BY cno;

视图如下：

二． 使用视图

1.查询以上所建的视图结果。

所建视图结果如上题截图所示

2.查询平均成绩为90分以上的学生学号、姓名和成绩；

SELECT 学号,姓名,平均成绩 FROM V_AVG_S_G WHERE平均成绩>90;

3.查询各课成绩均大于平均成绩的学生学号、姓名、课程和成绩；

SELECT V_S_C_G.sno,V_S_C_G.sname,V_S_C_G.cname,V_S_C_G.score FROM V_S_C_G,V_AVG_S_G WHERE V_S_C_G.sno=V_AVG_S_G.学号 AND V_S_C_G.score>V_AVG_S_G.平均成绩;

4.按系统计各系平均成绩在80分以上的人数，结果按降序排列；

1．先创建学生学号-系别视图V_STUDENT CREATE VIEW V_STUDENT AS SELECT sno,dno FROM student;2．进行查询

SELECT V_STUDENT.dno '系别',COUNT(V_STUDENT.sno)'人数' FROM V_STUDENT,V_AVG_S_G WHERE V_STUDENT.sno=V_AVG_S_G.学号 AND V_AVG_S_G.平均成绩>80 GROUP BY V_STUDENT.dno;

三． 修改视图 并查询结果;UPDATE V_IS SET sname='S1_MMM' WHERE SNO='32011';UPDATE V_IS SET sname='S4_MMM' WHERE SNO='32014';SELECT * FROM V_IS WHERE sno='32011' OR sno='32014';1.通过视图V_IS，分别将学号为“S1”和“S4”的学生姓名更改为“S1_MMM”,”S4_MMM”

2.通过视图V_IS，新增加一个学生记录('S12','YAN XI',19,'IS')，并查询结果

INSERT INTO V_IS(sno,sname,sex,dno,sage)VALUES('S12','YAN XI','女','IS','19')SELECT *

FROM V_IS

WHERE sno='S12';

3.要通过视图V_AVG_S_G，将学号为“S1”的平均成绩改为90分，是否可以实现？并说明原因

答：不能实现，因为视图或函数'V_AVG_S_G' 包含聚合、DISTINCT 或GROUP BY 子句或者PIVOT 或UNPIVOT 运算符，所以无法进行更新。

UPDATE V_AVG_S_G SET 学号='32011' WHERE平均成绩='90';

实验十二 更新语句

一． 实验目的

1． 熟悉使用UPDATE/INSERT/DELETE语句进行表操作； 2． 能将这些更新操作应用于实际操作中去； 二． 三． 实验准备 实验要求 1．了解这些更新语句的基本语法和用法； 1． 完成下面的实验内容，并提交实验报告； 2． 在实验报告中附上相应的代码； 四． 实验内容

1． 对于student表，将所有专业号为‘001’的，并且入学年份为202_的学生，或是专业号为‘003’，并且年龄小于20岁的学生的班级号改为‘001’。

UPDATE student SET classno='001'

WHERE(mno='001'AND YEAR(sctime)='202_')OR(mno='003'AND(year(getdate())-year(sdate))<20);

2． 对于student表，删掉所有年龄小于20岁，并且专业号为‘003’的学生的记录。

DELETE FROM student WHERE mno='003'AND(year(getdate())-year(sdate))<20

3． 对于student表，插入一条新记录，它的具体信息为，学号：2007110011、姓名：张

三、性别：男、出生日期：19880808、院系编号：‘001’、专业编号： ‘001’、班级号：‘001’、入学时间：20070901。INSERT INTO student(sno,sname,sex,sdate,dno,mno,classno,sctime)VALUES('2007110011','张三','男','1988-8-8','001','001','001','202_-9-1');

4． 对于student表，将入学时间最晚的学生和年龄最小的学生的联系方式去掉。

UPDATE student SET tel=null

WHERE sctime>=ALL(select sctime FROM student)OR year(sdate)>=ALL(select year(sdate)from student)

5． 对于student表，将平均年龄最小的一个院系的院系编号改为‘008’。

UPDATE student SET dno='008' where dno in(SELECT top 1 dno FROM student

GROUP BY dno

ORDER BY AVG(year(getdate())-year(sdate)))

T-SQL编程

一、【实验目的】

1、掌握T_SQL编程的基本语法

2、常用函数的使用方法

二、【实验要求】

1、使用查询分析器练习T-SQL编程方法

2、练习函数的使用

三、【实验准备】

1、复习与本次实验内容相关知识

2、预习相关函数（请预查阅CONVERT、DATENAME、GFTDATE函数的用法）

四、【实验内容】

1、条件结构

自己编写一段程序判断一个年份(比如1900年)是否是闰年,是则显示1900年为闰年,否则显示1900年不是闰年.DECLARE @year INT SELECT @year=202_ if @year%4=0 and @year%100<>0 print CONVERT(CHAR(4),@year)+'是闰年' else print CONVERT(CHAR(4),@year)+'不是闰年'

2、循环结构（1）下面是计算1~100和的循环结构，执行之，体会循环结构程序，注意语句块标志BEGIN...END

DECLARE @SUM INT,@I INT SELECT @I=1,@SUM=0 WHILE @I<=100 BEGIN SELECT @SUM=@SUM+@I SELECT @I=@I+1 END PRINT '1...100的和为：'+CONVERT(CHAR(4),@SUM)

（3）编写一个程序用于计算10的阶乘

DECLARE @SUM INT,@I INT SELECT @I=1,@SUM=1 WHILE @I<=10 BEGIN SELECT @SUM=@SUM*@I SELECT @I=@I+1 END PRINT '阶乘!的结果为：'+CONVERT(CHAR(10),@SUM)

（4）自己编制一个程序,要求其能打印出100内的素数(只能被其自身和1整除的数).--循环结构+条件结构

SELECT main.number FROM master..spt_values AS main WHERE type='P'

AND number BETWEEN 3 AND 100 AND NOT EXISTS(SELECT 1 FROM master..spt_values AS sub WHERE

type='P'

AND sub.number BETWEEN 2 AND main.number-1 AND main.number % sub.number = 0)

3、CASE结构

（1）下面SQL查询图书的信息，并根据图书定价判断是否适合作为教材

select 书名, 出版社, 作者, case

when 定价>50 then '定价太高，不适合作教材'

else '定价' + CAST(定价 as varchar(5))+ '，可以作教材'

end 可否作为教材 from 图书

（2）请自己编程实现各位同学的成绩以等级分显示即:90分及以上为优,80分及以上到90以下为良,70分及以上到80分以下为中,60分及以上到70分以下为及格,其余为不及格.SELECT student.sno ,sname ,cname, CASE WHEN student_course.score >=90 THEN '优' when student_course.score>=80 and student_course.score <=90 THEN '良'

when student_course.score>=70 and student_course.score <=80 THEN '中'

when student_course.score>=60 and student_course.score<=70 THEN '及格'

else '不及格' END GRADE FROM student_course,course,student WHERE student_course.cno=course.cno AND student_course.sno=student.sno

4、函数使用

下面查询，显示当前日期，显示格式为：“今天是XXXX年XX月XX日，星期X”,执行之,体会系统函数的用法.SELECT '今天是'+DATENAME(YEAR,GETDATE())+'年' +DATENAME(MONTH,GETDATE())+'月' +DATENAME(DAY,GETDATE())+'日，' +DATENAME(WEEKDAY,GETDATE())