收藏 分销(赏)

数据库原理及应用期末考试...doc

上传人:快乐****生活 文档编号:4675361 上传时间:2024-10-09 格式:DOC 页数:13 大小:120.02KB 下载积分:8 金币
下载 相关 举报
数据库原理及应用期末考试...doc_第1页
第1页 / 共13页
数据库原理及应用期末考试...doc_第2页
第2页 / 共13页


点击查看更多>>
资源描述
综合题 一、 1.使用Transact—SQL语言创建教学管理数据库,创建两个表学生信息(学号,姓名,性别,籍贯,班级编号)和成绩表(学号,课程编号,成绩,是否重修)。其中,学生信息表中学号为主键,其他信息都不允许为空。通过学号与成绩表形成一个一对多的关系,成绩表中的成绩的默认值为0,但必须在0~100之间。 注:将学号设置为主键,主键名为pk_xh; 为成绩添加默认值约束,约束名为df_xb,其值为“0”; 为成绩添加check约束,约束名为诚恳ck_xb, 在0~100之间。 CREATE  DATABASE   教学管理  use 教学管理  go  create  table  学生信息 (      学号  varchar(20)  NOT  NULL  PRIMARY  KEY,     姓名  varchar(20)  NOT  NULL ,     性别  varchar(10)  NOT  NULL,     籍贯  varchar(20)  NOT  NULL,     班级编号  varchar(20)  NOT  NULL )  go  create  table  成绩表  (       学号  varchar(20)  NOT  NULL  FOREIGN   KEY  REFERENCES  学生信息(学号),      课程编号 varchar(20)  NOT  NULL,      成绩  numeric(5,2)  DEFAULT (0)  CHECK(成绩)=0  and  成绩<=100) , 是否重修 varchar(8)  NOT  NULL )  go 2.写出实现各查询功能的SELECT语句。 (1)统计班级编号为1002的学生人数。 (2)查询课程编号为“003”的课程成绩高于该门课程平均分的学生信息。 (3)在“学生信息”表中找出姓“王”的学生信息。 (4)将“成绩”表中的课程编号为“001”的成绩增加10分。 (5)列出每个同学的学号、姓名及选修课程的平均成绩情况,没有选修的同学也列出。 3.创建一个名为stud_view2的视图,该视图可以用来查看每个学生的选修课程的门数和平均成绩。 create view stud_ view2 as select count(*) as 课程门数,avg(grade) as 平均成绩 from成绩表 group by 学号 select * from stud_view2  4.(1)在“教学管理”数据库中创建一个带参数的存储过程proc_score。该存储过程的作用是:当输入某门课程的课程名称时,检索该门课程成绩,并将查询结果以“学号” ,“姓名” ,“课程名称” ,“成绩等级”输出(成绩划分成四个等级:高于或等于85分者为优秀,高于或等于75分者为良好,高于或等于60分者为及格,低于60分者为不及格)。 (2)执行存储过程proc_score,查询“003”课程的成绩信息。 二、 假设现有数据库“学生信息”,其中包含“学生”、“课程”、“学期成绩”三个表。表结构如下: “学生”表结构 字段名 数据类型 长度 是否为空 约束 学号 int 4 否 主键 姓名 char 10 否 唯一约束 性别 char 2 是 默认约束 专业 char 20 是 班级 char 12 是 备注 text 是 “课程”表结构 课程(课号,课名,学分,先修课) “学期成绩”表结构 选课(学号,课号,成绩) 根据各题功能要求写出相应的T-SQL语句。 1.写出在“学生信息”数据库中创建“学生”表的T-SQL语句,并在建表的同时创建所需约束。约束要求如下: 将学号设置为主键,主键名为pk_xh;为姓名设置唯一约束,约束名为uk_xm; 为性别添加默认约束,约束名为df_xb,其值为“男” 。 USE 学生信息                                       GO  CREATE TABLE 学生                                  (学号 INT CONSTRAINT pk_xh PRIMARY KEY,                姓名 CHAR(10) NOT NULL CONSTRAINT uk_xm UNIQUE,         性别 CHAR(2) NULL CONSTRAINT df_xb DEFAULT '男',        专业 CHAR(20) NULL,       班级 CHAR(12) NULL,  备注 TEXT NULL) 2.写出实现各查询功能的SELECT语句。 (1)计算机专业不及格学生的学号和姓名。 (2)选修了数据库课程的学生的学号、姓名和成绩,查询结果按分数的降序排列。 (3)在“课程”表中找出“课程名”中包含“计算机”三个字的课程。 (4)将“课程”表中的课程编号为“001”的学分增加1学分; (5)列出每个同学的学号、姓名及选修课程的平均成绩情况,没有选修的同学也列出。 3.创建一个名为stud_view2的视图,该视图可以用来查看每个学生的选修课程的门数和平均成绩。 4.(1)在“学生信息”数据库中创建一个带参数的存储过程proc_score。该存储过程的作用是:当输入某门课程的课程名称时,检索该门课程成绩,并将查询结果以“学号” ,“姓名” ,“课程名称” ,“成绩等级”输出(成绩划分成四个等级:高于或等于85分者为优秀,高于或等于75分者为良好,高于或等于60分者为及格,低于60分者为不及格)。 CREATE PROC[EDURE] proc_score @KCMC CHAR(20) AS SELECT 学生.学号,姓名,课程名称,成绩等级= CASE    WHEN 成绩>=85 THEN '优秀'  WHEN 成绩>=75 THEN '良好'  WHEN 成绩>=60 THEN '及格'  WHEN 成绩<60  THEN '不及格' (或:ELSE '不及格')  END  FROM 学生 JOIN 学期成绩 ON 学生.学号=学期成绩.学号   JOIN 课程注册 ON 课程注册.课程代码=学期成绩.课程代码  WHERE 课程名称=@KCMC   GO  (2)执行存储过程proc_score,查询“计算机网络”课程的成绩信息。 EXEC  proc_score ‘计算机网络’ 5.定义一事务tran_stu,在学生“学期成绩”表中,为所有成绩高于50分的同学的成绩增加10分,并提交事务。 三、 假设现有数据库“学生信息”,其中包含“学生”、“课程注册”、“学期成绩”三个表。表结构如下: “学生”表结构: 字段名 数据类型 长度 是否为空 约束 学号 int 4 否 主键 姓名 char 10 否 唯一约束 性别 char 2 是 默认约束 专业 char 20 是 班级 char 12 是 备注 text 是 ┋┋┋┋┋┋┋┋┋┋┋┋┋┋┋装┋┋┋┋┋┋┋┋┋订┋┋┋┋┋┋┋┋┋┋线┋┋┋┋┋┋┋┋┋┋┋┋┋┋┋┋┋┋┋┋ “课程注册”表结构: 字段名 数据类型 长度 是否为空 约束 课程代码 char 4 否 主键 课程名称 char 20 否 “学期成绩”表结构: 字段名 数据类型 长度 是否为空 约束 学号 int 4 否 外键 课程代码 char 4 否 外键 成绩 float 8 是 根据各题功能要求写出相应的T-SQL语句。 1.写出在“学生信息”数据库中创建“学生”表的T-SQL语句,并在建表的同时创建所需约束。约束要求如下:将学号设置为主键,主键名为pk_xh;为姓名设置唯一约束,约束名为uk_xm;为性别添加默认约束,约束名为df_xb,其值为“男”。 USE 学生信息                                       GO  CREATE TABLE 学生                                  (学号 INT CONSTRAINT pk_xh PRIMARY KEY,            姓名 CHAR(10) NOT NULL CONSTRAINT uk_xm UNIQUE,         性别 CHAR(2) NULL CONSTRAINT df_xb DEFAULT '男',        专业 CHAR(20) NULL,       班级 CHAR(12) NULL,  备注 TEXT NULL)  2.完成以下查询。 (1)统计学生表中各班级的学生人数。 SELECT 班级,COUNT(*) AS 人数  FROM  学生  GROUP  BY  班级  (2) 查询“软件工程”课程成绩高于该门课程平均分的学生信息。 SELECT 学号,课程名称,成绩    FROM 学期成绩 JOIN 课程注册   ON 学期成绩.课程代码=课程注册.课程代码  WHERE 课程名称='软件工程' AND 成绩>  (SELECT AVG(成绩)      FROM 学期成绩 JOIN 课程注册   ON 学期成绩.课程代码=课程注册.课程代码       WHERE 课程名称='软件工程') (3)查询男同学选修的课程号。 (4)查询至少选修课程号为“C01”和“C02”的学生学号。将所有学生的“01”课程的成绩增加10分。 3.在“学生信息”数据库中创建一个带参数的存储过程st_xsxx。该存储过程的作用是:当输入某门课程的课程名称时,检索该门课程成绩,并将查询结果以“学号” ,“姓名” ,“课程名称” ,“成绩等级”输出(成绩划分成四个等级:高于或等于85分者为优秀,高于或等于75分者为良好,高于或等于60分者为及格,低于60分者为不及格)。 CREATE PROC[EDURE] st_xsxx  (1分)  @KCMC CHAR(20)   (1分)  AS  SELECT 学生.学号,姓名,课程名称,成绩等级= CASE      WHEN 成绩>=85 THEN '优秀'  WHEN 成绩>=75 THEN '良好'  WHEN 成绩>=60 THEN '及格'  WHEN 成绩<60  THEN '不及格' (或:ELSE '不及格')  END  FROM 学生 JOIN 学期成绩 ON 学生.学号=学期成绩.学号   JOIN 课程注册 ON 课程注册.课程代码=学期成绩.课程代码  WHERE 课程名称=@KCMC (1分)  GO 四、 现有关系数据库如下: 数据库名:教师数据库 教师表(编号 char(6),姓名,性别,民族,职称,身份证号) 课程表(课号 char(6),名称) 任课表(ID,教师编号,课号,课时数) 用SQL语言实现下列功能的sql语句代码: 1. 写出创建数据库代码,创建教师表代码; 要求使用:编号(主键)、默认(民族“汉”)、民族和姓名 (非空)、身份证号 (唯一)、性别(检查约束:只能是男或女)。 create database [教师数据库]                 --(2分)  use [教师数据库]  go  create table 教师表                          --(6分)  ([编号] char(6) primary key,  [姓名] nchar(4) not null,  [性别] nchar(1) check([性别] in ('男', '女')),  [民族] nchar(8) default '汉族' not null,  [职称] nchar(12),  [身份证号] char(18) unique )   create table 课程表                          --(2分)  ([课号] char(6) primary key,  [名称] char(40) not null )   create table 任课表                          --(4分)  (ID IDENTITY(1, 1),   [教师编号] char(6) references 学生表(学号),  [课号] char(6) references 课程表(课号),  [课时数] integer check([课时数] between 0 and 200) )  2.写出下列课程记录添加到课程表的代码 课号 课程名称 100001 SQL Server数据库 100002 数据结构 100003 VB程序设计 修改:把课号为100003的课程名称改为“Visual Basic程序设计”; 删除:课号为100003的课程信息 insert 课程表 values('100001', 'SQL Server数据库')   insert 课程表 values('100002', '数据结构')  insert 课程表 values('100003', 'VB程序设计')  update 课程表 set 名称='Visual Basic程序设计' where 课号='100003'  delete 课程表 where 课号='100003' 3.创建视图:任课表 (教师编号,姓名,课号,课程名称,课时数); create view [任课表视图]  as  select 教师编号,姓名,课号, 课程名称,课时数 from 教师表,任课表  where 教师表.编号=任课表.教师编号  4.查询:所有教“SQL Server数据库”这门课程的老师姓名; create function [某门课任课教师] (@课程名 varchar(15))  returns table  as  return (select 课程名称, 课时数, 教师姓名=姓名 from 任课表视图  where 课程名=@课程名)  go  select * from [某门课任课教师]('SQL Server数据库') 5.查询:最大课时数、最低课时数、平均课时的; create procedure [统计课时数]  as  select  最大课时数=max(课时) ,最小课时数=min(课时),平均课时数=avg(课时) from 任课表  go  6.检索有一门或一门以上课程课时数大于90的所有教师的信息,包括编号、姓名; select 编号, 姓名 from 教师表  where编号 in (select distinct 教师编号 from 任课表 where课时数>=90)  7.修改教师表结构:教师表的职称列上,规定取值为('教授','副教授','讲师', '助教')之一。 create rule zhicheng _rule  as @zhicheng  in ('教授','副教授','讲师', '助教')  go  sp_bindrule zhicheng_rule, '教师表.职称'  8.计算某教师代课总课时,并将值返回的存储过程以及执行代码。 执行:计算“郭老师”的总课时。 create procedure [统计课时]  @教师名 nchar(16),  as  begin     declare @总课时 int      select @总课时=sum (课时) from 任课表视图    where 姓名 = @教师名   end  go  execute [统计课时] '郭老师' 五、 现在要为学校建立一个数据库,设学校的环境如下:一个系有若干个专业,每个专业一年只招一个班,每个班有若干个学生,现要建立关于系、学生、班级的数据库,关系模式为: 学生表student(学号,姓名,性别,班级,生源地,高考成绩,出生日期) 班级表class(班级名,专业名,系号,入学年份) 系表 department(系号,系名) 使用SQL语句完成一下功能: 1. 建立数据库名为school并激活当前新建的数据库,要求主数据文件的逻辑文件名为school,实际文件名为school.mdf,事务日志文件的逻辑文件名为school,,实际文件名分别为school.ldf,上述文件的初始容量均为5MB,最大容量均为20MB,递增量均为1MB。 create database school  on primary  ( name = school,  filename = 'SCHOOL.mdf', SIZE = 5MB, MAXSIZE = 20MB, filegrowth = 1MB)  log on ( name= school_log,  filename =  ‘SCHOOL_log.ldf', SIZE = 2MB, maxsize = 20MB, filegrowth = 10%)  go 2.建立表student(学号,姓名,性别,班级,生源地,高考成绩,出生日期)要求:(1)表要有主键 (2)学生的性别只能取‘男’或者‘女’ (3)学生姓名不能为空 use SCHOOL  create table student -- 学生表定义  ( 学号char(10) primary key , -- 主键为学号  姓名varchar(20) not null , -- 姓名不能为空值  性别char(4) default '男' ,   -- 性别默认为男性  生源地varchar(12) , --生源地 出生日期datetime ,  check((性别= '男') or (性别= '女'))  -- 性别必须为男性或女性  ) 3.在student表中插入一条记录:(学号:8101,姓名:张三,性别:男,生源地:山东) insert into student values(8101,'张三','男','山东') 4.根据student表完成以下的查询功能 (1)通过对学生信息表的查询,查询出所有学生的学号,姓名和性别。 select 学号,姓名,性别 from student  (2)通过对学生信息表的查询,查询来自福建的学生的记录。 select * from student where 生源地= '福建' (3)在查询语句中使用聚合函数计算出所有学生总数(起别名为学生人数)和平均高考成绩(起别名为平均成绩)。 select count(*) as '学生人数' from student  (4)更新学号为8103的这位学生的姓名为张玲。 update student set 姓名= '张玲'  where 学号= '8103' (5)查询高考成绩最高的前5条学生记录。 Select top 5 * from student order by 高考成绩 desc (6)在学生表中统计班级列表及各班级的人数。 Select 班级,count(*) from student group by 班级 (7)查询高考成绩在90到100之间的学生的学号、姓名、专业名。 Select 学号,姓名,专业名 from 学生表,班级表  where 学生表.班级=班级表.班级名 and 高考成绩 between 90 and 100 (8)删除姓名为李飞的学生记录;删除数据表student。 delete from student where 姓名= '李飞' 六、 已知逻辑结构见下表,写出各题的T-SQL语句。 表readers(读者信息表)结构 属性名 数据类型 宽度 小数位数 编号(主码) char 8 姓名 char 8 读者类型 int 2 已借数量 int 2 books表(图书信息表)结构 属性名 数据类型 宽度 编号(主键) char 15 书名 char 42 作者 char 8 出版社 char 28 出版日期 datetime 8 定价(>0) decimal 8 borrowinf表(借阅信息表)结构 属性名 数据类型 宽度 读者编号(外键) char 8 图书编号(外键) char 15 借期 datetime 8 还期 datetime 8 主键(读者编号,图书编号,借期) 视图overdue(超期信息) 列名 对应基本表的列名 读者编号 Reaers.编号 读者姓名 Readers.姓名 图书编号 Books.书名 书名 Books. 应还日期 Borrwinf.借期+readrtype期限 1. 创建基本表books。 CREATE TABLE books  (   图书编号 CHAR(15) PRIMARY KEY, 书名 CHAR(42),   作者 CHAR(8),   出版社 CHAR(28),   出版日期 DATETIME,   价格 float(8)  ); 2. 创建基本表borrowinf。 CREATE TABLE borrowinf (  读者编号CHAR(10),  图书编号CHAR(15),  借期DATETIME,  还期DATETIME,  PRIMARY KEY (读者编号,图书编号),  FOREIGN KEY (读者编号) REFERENCES readers(读者编号),  FOREIGN KEY (图书编号) REFERENCES books(图书编号)  );  3. 创建视图overdue(超期信息)。 Create view overdue as SELECT 图书编号 FROM borrowinf WHERE 还期>0 4. 查询姓名为’王立群’的读者的借阅信息。 SELECT 图书编号,借期,还期 FROM borrowinf  WHERE 读者编号 IN (SELECT 读者编号 FROM readers WHERE 姓名='王立'  5. 查询书名中包含文字‘程序设计’的图书信息。 SELECT * FROM books WHERE 书名 LIKE'%程序设计' 6. 查询各出版社的馆藏书数量。 SELECT COUNT(DISTINCT 图书编号) FROM books 7. 查询2004-1-1和2004-12-31之间作者为‘梁晓峰’的图书借阅情况。 SELECT * FROM borrowinf WHERE 借期>'2007-1-1'AND 还期< '2007-12-31' AND 图书编号 IN (SELECT 图书编号 FROM books WHERE 作者='梁晓峰') 8.创建存储过程计算用户指定图书的价格,并将价格作为返回值。再执行调用该存储过程的命令。 USE 图书管理 Go   CREATE PROCEDURE proc_BooksPrice  @sm char(42),  @dj float output As       SELECT @dj=定价 FROM books  WHERE 书名=@sm  Go  调用存储过程: Declare @price float  EXEC proc_BooksPrice '计算机文化基础',@price output SELECT @price  GO
展开阅读全文

开通  VIP会员、SVIP会员  优惠大
下载10份以上建议开通VIP会员
下载20份以上建议开通SVIP会员


开通VIP      成为共赢上传

当前位置:首页 > 教育专区 > 其他

移动网页_全站_页脚广告1

关于我们      便捷服务       自信AI       AI导航        抽奖活动

©2010-2025 宁波自信网络信息技术有限公司  版权所有

客服电话:4009-655-100  投诉/维权电话:18658249818

gongan.png浙公网安备33021202000488号   

icp.png浙ICP备2021020529号-1  |  浙B2-20240490  

关注我们 :微信公众号    抖音    微博    LOFTER 

客服