数据库原理及应用习题2

四、分析与设计题（4）1．请依据下表内容完成题目要求。(40分)1.建立数据库student。（2分）2.按照图表中给出的表定义，请在student数据库中创建学生表。(4分)3.查询学生表中女同学的基本信息。（2分）4.查询成绩表中选修了课程号为‘002’的所有学生的学号及成绩，并按成绩降序排列。（3分）5.查询成绩表中课程号为‘003’课程的成绩最高分。（2分）6.查询所有学生的学号、姓名、所选课程的课程名称及相应成绩（4分）7.查询学生表中各系的的学生人数，结果显示系别和人数两列。（3分）8.向成绩表成绩中插入一行数据，列值分别为：（’20090101’，’003’，89）（2分）9.修改课程表中‘数据结构’课程的学分，将其学分改为6。（2分）10.删除学生表中姓张的学生记录（2分）11.根据学生表创建视图View1，视图包含计算机系所有学生的基本信息。（3分）12.查询视图View1所包含的数据。（2分）13.创建存储过程Proc1，使其完成如下功能：根据任意输入的学生学号，查询成绩表中该学生的学号、课程号及成绩。（使用输入参数）（5分）14.执行第13小题中创建的存储过程Proc1，执行时输入的学生学号为‘20090101’（2分）15、删除成绩表。（2分）答案：完成如下所要求所用的操作命令：（共40分）1、创建一个存放在D：\SQL路径下Test数据库，该数据库的主数据文件逻辑名称为Test_data，物理文件名为Test_data.mdf，初始大小为4MB，最大尺寸为10MB，增长速度为10%；数据库的日志文件逻辑名称为Test_log，物理文件名为Test_log.ldf，初始大小为1MB，最大尺寸为5MB，增长速度为1MB。（4分）2、依据表结构创建score表。（3分）3、查看表中所的的数据行。（2分）4、查看表中姓名、SQL两列数据，并按成绩降序排列。。（2分）5、查看表中姓王学生的基本信息。（3分）6、查看所有学生的学号、姓名及总分（三门课相加）。（2分）7、向score表中插入一行数据，值分别为：（2分）（1005，‘赵强’，64，82，69）8、修改表中姓名为王英的数据，使VB的值改为：85（2分）9、创建视图xs1，使其包含学号、姓名、SQL三列。（3分）10、创建存储过程pjf，用它来按姓名查询score表中任一学生的平均成绩。（4分）11、执行第10小题中创建的存储过程pjf。(2分)12、建立触发器tr1p，防止用户对score表有删除、修改及插入操作。(4分)13、显示score中各门课的平均值。（3分）14、删除score表中姓王的所有数据行。（2分）15、删除test数据库。（2分）答案：1、（4分）createdatabaseteston--建立主数据文件(name=test_data,--逻辑文件名filename='d:\sql\test_data.mdf',--物理文件路径和名字size=4mb,--初始大小maxsize=10mb,--最大容量filegrowth=10%--增长速度)logon--建立日志文件(name=test_log,filename='d:\sql\test_log.ldf',size=1mb,maxsize=5mb,filegrowth=1mb)2、（3分）createtablescore(学号char(8)primarykey,姓名char(6)notnull,sqlfloatcheck(sqlbetween0and100),aspfloatcheck(aspbetween0and100),vbfloatcheck(aspbetween0and100))3、（2分）select*fromscore4、(2分)select姓名，sqlfromscoreorderbysql5、（2分）select*fromscorewhere姓名like‘王%’6、（2分）select学号，姓名,sql+asp+vbas总分fromscore7、（2分）insertintoscorevalues（1005，‘赵强’，64，82，69）8、（2分）updatescoresetvb=85where姓名=‘王英’9、（3分）createviewxs1asselect学号，姓名，sqlfromscore10、（5分）createprocedurepjf@xmchar(8)asselectavg(asp+vb+sql)‘平均分’fromscorewhere姓名=@xm11、（2分）executepjf12、（4分）createtriggertr1onscorefordelete,update,insertasPrint’不允许执行上述操作’RollbacktransactionReturngo13、(3分)selectavg(sql),avg(asp),avg(vb)fromscore14、（2分）deletefromscorewhere姓名like‘王%’15、（2分）dropdatabasetest3．完成如下所要求所用的操作命令：（共40分）有一表名为”图书信息表”的表，依据此表内容完成以下题目要求：1、创建一个存放在D：\SQL路径下的Book数据库，该数据库的主数据文件逻辑名称为Book_data，物理文件名为Book_data.mdf，初始大小为4MB，最大尺寸为10MB，增长速度为10%；数据库的日志文件逻辑名称为Book_log，物理文件名为Book_log.ldf，初始大小为1MB，最大尺寸为5MB，增长速度为1MB。（5分）2、依据图1的内容，创建“图书信息表”。（4分）3、向表中插入一行数据，列值分别为：（3分）（’1006’，’数据库原理’，’王珊’，’高等教育出版社’，25，20,null）4、查看表中所有的数据行（2分）5、显示表中单价的最大值（3分）6、查看表中单价大于30且库存量小于10的数据行（3分）7、查看表中的书号、书名及总金额列（由单价*库存量计算得出）（3分）8、修改表中书名为‘多媒体技术’的库存量，使其库存量为38（3分）9、根据表创建视图AA，使该视图包含书号、书名及单价（3分）10、创建存储过程xxx，使其完成如下功能：查询表中高等教育出版社出版的图书的数目。（4分）11、执行第8小题中创建的存储过程XXX。（2分）12、删除表中作者姓张的数据行（3分）13、删除该图书表（2分）答案：1.（4分）createdatabasebookon--建立主数据文件(name=book_data,--逻辑文件名filename='d:\sql\book_data.mdf',--物理文件路径和名字size=4mb,--初始大小maxsize=10mb,--最大容量filegrowth=10%--增长速度)logon--建立日志文件(name=book_log,filename='d:\sql\book_log.ldf',size=1mb,maxsize=5mb,filegrowth=1mb)2.（3分）createtable图书信息表(书号varchar(10)primarykey,书名varchar(20)notnull,作者varchar(20)notnull,单价intnull,库存量intdefault‘10’)3.（2分）insertinto图书values（1005，‘数据库原理’，‘王珊’，‘高等教育出版社’，25，20）4.（2分）select*from图书5.(2分)selectmax(单价)as‘最高价’from图书6.（3分）select*from图书where单价〉30and库存量〈107.（3分）select书号，书名，单价*库存量as‘总金额’from图书8.（2分）update图书set库存量=38where书名=‘多媒体技术’9.（3）createviewAAasselect书号，书名，单价from图书信息表10(4分)createprocedurexxxasselectcount(*)from图书where出版社=‘高等教育出版社’11.（2分）executexxx12.（2分）deletefrom图书where姓名like‘张%’13.（2分）droptable图书4．请根据下表内容完成题目要求：（表的名字为：商品表）编号商品名单价数量状态001电视机120050002洗衣机1650130003空调560081004自行车180431005电视机2580181006洗衣机36002301、查询表中所有的数据行。2、查询表中前两行数据。3、显示表中商品名和单价,并去掉重复行的数据。4、查看表中数量低于10的商品信息。5、查看表中价格最高的的商品名。6、查看表中单价在1000和3000之间的商品信息。7、显示表中商品名和单价两列数据，且按单价降序排列。8、显示表中商品名以电开头的数据行。9、向表中添加一行数据,其值为（‘007’，‘电冰箱’，4560，56）10、更改表中的数据,将自行车的单价改为280。11、删除表中商品名为电冰箱的数据行。12、依据此表创建视图aa，使该视图包含编号，商品名，及单价*0.8三列。13、删除该表.答案：1、select*from商品表2、selecttop2*from商品表3、selectdistinct商品名,单价from商品表4、select*from商品表where数量105、selecttop1商品名from商品表oderby单价desc6、select*from商品表where单价between1000and30007、select商品名,单价from商品表oderby单价desc8、select*from商品表where商品名like‘电%’9、insertinto商品表values(‘007’，‘电冰箱’，4560，56）10、update商品表set单价=280where商品名=’自行车’11、deletefrom商品表where商品名=’电冰箱’12、createviewaaasselect编号，商品名，单价*0.8from商品表13、droptable商品表