学生成绩管理系统数据库

...wd......wd......wd...附录A学生成绩管理系统数据库学生成绩管理系统数据库是对比简单的一个数据库。基于读者都对学校的学生管理方式十分了解，所以这个数据库作为上课时的实际演例如子。A.1学生表(Tbl_StudentInfo)伦理名物理名属性主键外键学号StunoChar(5)非空是姓名stunameVarchar(20)非空生日StubirthDate性别Stusexchar(1)家庭住址StuAddrVarchar(200)手机StuTelVarchar(11)图A.1学生表的表构造学号姓名生日性别家庭住址手机05001张三1988-12-120江苏南京1234505002李四1987-06-051上海1234605003王五1987-12-010北京1234705004赵六1986-02-231广东深圳1234805005张三1988-04-010重庆1234905006孙七1988-07-031湖北武汉图A.2学生表的表数据A.2课程表(Tbl_ClassInfo)伦理名物理名属性主键外键课程号classnoChar(3)notnull是课程名ClassnameVarchar(10)notnull图A.3课程表的表构造课程号课程名001计算机002日语003英语图A.4课程表的表数据A.3成绩表(Tbl_ScoreInfo)伦理名物理名属性主键外键学号StunoChar(5)notnull是参照学生表课程号ClassnoVarchar(3)notnull是参照课程表考试成绩scoreNumeric(3,1)图A.5成绩表的表构造学号课程号成绩0500100195050010029005001003880500200191050020029305002003880500300195050030027305003003580500400147050040036105005002590500500347图A.6成绩表的表数据附录B网上书店管理系统数据库网上书店管理系统数据库是对比复杂的一个数据库。基于网上销售图书的方式，作为课后习题。B.1客户表(Tbl_Customer)伦理名物理名属性主键外键顾客编号CustomerInt(4)非空是顾客姓名Customer_NameVarchar(10)顾客地址AddressVarchar(20)所在城市CityVarchar(20)所在省StateVarchar(20)邮政编码ZipVarchar(6)推荐会员ReferredInt(4)图B.1客户表的表构造顾客编号顾客姓名顾客地址所在城市所在省邮政编码推荐会员1001张三白下区南京江苏2100001002李四徐汇区上海上海1100001003王五朝阳区北京北京1100001004赵六北大街无锡江苏1100001005李三平江路苏州江苏1100001006陈三升州路南京江苏1100001007朱三三牌楼合肥安徽11000010031008梁三徐汇区上海上海1100001009宫三徐汇区上海上海11000010031010马三海淀区北京北京1100001011刘三崇文区北京北京1100001012齐三宣武区北京北京1100001013陆三鼓楼区南京江苏11000010061014陈三雨花区南京江苏1100001015人民币三新桥村无锡江苏1100001016高三利农村无锡江苏11000010101017郝三杨浦区上海上海1100001018黄三三家庄合肥安徽1100001019黄四常青镇合肥安徽11000010031020梁四余老庄合肥安徽110000图B.2客户表的表数据B.2图书表(Tbl_Books)伦理名物理名属性主键外键图书编号IsbnVarchar(10)非空是书名TitleVarchar(300)出版日期PubdateDate出版社IDPubidInt(2)成本CostNumeric(5,2)售价RetailNumeric(5,2)种别CategoryVarchar(12)图B.3图书表的表构造图书编号书名出版日期出版社ID成本售价种别1059831198一天中10分钟的身体成分2001-01-21418.7530.95安康0401140733米奇的复仇2001-12-12114.222家庭生活4981341710用牙签造车2002-08-18237.859儿童8843172113数据库的实现1999-06-0433155计算机3437212490用蘑菇烹饪2000-02-28412.519.95烹饪3957136468数据库法宝2001-11-3034775计算机1915762492电脑手册2001-01-2132125计算机9959789321电子商务的道路2002-03-0223754计算机2491748320教育孩子_轻松方法2000-07-1754889.95家庭生活0299282519烹饪之路2000-09-1141928烹饪8117949391大熊和小宝贝2001-11-0855.328.95儿童0132149871若何最快的制作比萨2002-11-11417.8529.95自助9247381001若何成为经理人1999-05-09115.431.95商务2147428890最短小精悍的诗2001-05-01521.8539.95文学图B.4图书表的表数据B.3订单表(Tbl_Orders)伦理名物理名属性主键外键订单号OrderInt(4)非空是顾客号CustomerInt(4)参照客户表订单日期OrderdateDate发货日期ShipdateDate发货地点ShipstreetVarchar(18)发货城市ShipcityVarchar(15)发货省ShipstateVarchar(2)ShipzipVarchar(6)图B.5订单表的表构造订单号顾客号订单日期发货日期发货地点发货城市发货省100010052003-03-312003-04-02奥体大街南京江苏210019100110102003-03-312003-04-01和会街南京江苏210001100210112003-03-312003-04-01南京路上海上海201100100310012003-04-012003-04-01南京路上海上海201100100410202003-04-012003-04-05欧风街无锡江苏214002100510182003-04-012003-04-02南大街常州江苏213000100610032003-04-012003-04-02洋人街重庆重庆400015100710072003-04-012003-04-04洋人街重庆重庆400015100810042003-04-022003-04-03欧风街无锡江苏214002100910052004-04-032004-04-05南大街常州江苏213000101010192004-04-032004-04-04和平街北京北京100000101110102003-04-032003-04-05长安街北京北京100000101210172005-04-03北京东路南京江苏210001101310142005-04-032005-04-04德化街郑州河南450007101410072003-04-032003-04-05和平街北京北京100000101510202005-03-31欧风街无锡江苏214002101610032003-03-31长阳街苏州江苏215500101710152005-03-312005-04-05太合路合肥安徽230001101810012003-03-31江苏路南京江苏210001101910182003-03-31青阳路合肥安徽230001102010082003-03-31梅山新村合肥安徽230001图B.6订单表的表数据B.4订单详细表(Tbl_Orderitems)伦理名物理名属性主键外键订单号OrderInt(4)非空是参照订单表明细编号ItemInt(2)非空是图书编号IsbnVarchar(10)数量QuantityInt(3)图B.7订单详细表的表构造订单号明细编号图书编号数量10001343721249011001192473810011100122491748320110021884317211321003188431721131100321059831198110033343721249011004124917483202100512147428890110061995978932111007139571364683100729959789321110073811794939111007488431721131100813437212490210091343721249011009204011407331101018843172113110111249174832011012181179493911101221915762492210123249174832011012404011407331101318843172113110141040114073321015134372124901101612491748320110171811794939111018134372124902101828843172113110191040114073311020134372124901图B.8订单详细表的表数据B.5作者表(Tbl_Author)伦理名物理名属性主键外键作者编号AuthorIdVarchar(4)非空是姓名NameVarchar(20)图B.9作者表的表构造作者编号姓名S100薛明J100陈洪A100张二M100刘虎K100陈康P100王立A105吴明B100洪海P105李强W100宫立W105龚佩R100张青F100钟汉W110韩青图B.10作者表的表数据B.6图书作者表(Tbl_BookAuthor)伦理名物理名属性主键外键图书编号IsbnVarchar(10)非空是作者编号AuthorIdVarchar(4)非空是图B.11图书作者表的表构造图书编号作者编号1059831198S1001059831198P1000401140733J1004981341710K1008843172113P1058843172113A1008843172113A1053437212490B1003957136468A1001915762492W1001915762492W1059959789321J1002491748320R1002491748320F1002491748320B1000299282519S1008117949391R1000132149871S1009247381001W1002147428890W105图B.12图书作者表的表数据B.7出版社表(Tbl_Publisher)伦理名物理名属性主键外键出版社编号PubidInt(10)非空是出版社名NameVarchar(23)联系人ContactVarchar(15)联系PhoneVarchar(12)图B.13出版社表的表构造出版社编号出版社名联系人联系1新华出版社陈明000-714-83212扬子出版社张译010-410-00103人民教育出版社元力800-555-12114北京大学出版社郑爽800-555-97435机械工业出版社陆华800-555-8284图B.14出版社表的表数据B.8促销表(Tbl_Promotion)伦理名物理名属性主键外键礼物GiftVarchar(10)最低售价MinretailNumeric(5,2)最高售价MaxretailNumeric(5,2)图B.15促销表的表构造礼物最低售价最高售价书签012书题标签12.0125图书封面25.0156免费购物券56.01999.99图B.16促销表的表数据第一章数据库概念1.理解附录B网上书店订单系统的数据库，并答复如下问题。1.1可以访问哪个表和字段在当月订单中确定客户已经购置了哪些书通过哪些表可以确定哪些订单没有发货。通过哪些表及字段确定每月订单产生的总利润。订单表和明细表的订单编号是什么样的关系。2.参照教材附录C有关数据库的安装过程，把数据库安装到本机，从中学习安装数据库。〔考察：数据库安装预估：50分钟〕第二章表格及数据约束1.熟悉附录B中各个表及各字段含义，并且能说明此字段属性的设置原因。〔考察：表格和字段的理解预估：30分钟〕2.创立附录B所示的客户表，图书表，订单表，订单详细表，作者表，图书作者表，出版社表及促销表。3.为客户表中增加客户字段(字段名：TEL_NO,属性：Varchar(15))。4.为客户表中将客户地址修改为可以存储50个中文汉字的属性。5.为客户表，图书表，订单表，订单详细表，作者表，图书作者表创立主键约束。6.为订单表的客户编号工程创立外键约束，为订单详细表的订单编号和图书编号创立外键约束；以及为教材附录A表的考试成绩表的课程号创立外键约束。7.为促销表的最低售价和最高售价指定检查约束，最高售价必须大于最低售价。为订单详细表的数量指定检查约束，数量>0。为图书表的成本和售价指定检查约束，售价必须大于成本。〔考察：检查约束预估：40分钟〕8.为客户表的客户工程设置唯一约束。第三章数据操作及事务处理1.参照教材附录B表插入数据。〔保存住插入的sql文〕2.参照教材附录B，修改朱三的客户信息，地址变为‘徐汇区’，城市变为‘上海’，省变为‘上海’，变为‘110000’3.参照附录B，删除订单明细表中书名为《用蘑菇烹饪》的信息。4.修改订单1016的发货地点为‘江苏路‘，城市‘南京’，省‘江苏’，‘210001’5.修改订单1006的图书为‘电子商务的道路‘，数量‘5’6.修改书签礼物的最高售价为18，书题标签的最低售价改为18.01。7.修改人民教育出版社的联系为800-555-1398。〔考察：更新数据预估：5分钟〕第四章基本SQL参看教材附录A表，完成以下习题：查询所有没的学生信息。2.查询所有成绩优秀(大于90)和成绩不及格(低于60)的学生学号,和课程号。3.查询所有学生信息,按照生日从大到小排序。4.查询所有姓孙的学生信息。参看教材附录B表，完成以下习题：1.查询儿童和烹饪种类的所有图书〔两种方式查询〕。〔考察：IN和OR查询的使用预估：10分钟〕2.查询所有图书信息,按照出版社ID从小到大，出版日期从大到小排序。〔考察：排序的复杂使用预估：10分钟〕3.查询由其他客户向其推荐书店的所有客户。〔考察：NULL的使用预估：10分钟〕4.查询售价在20到50元之间的图书信息〔用2种方式实现〕。〔考察：BETWEEN和运算符的使用预估：15分钟〕5.查询不属于计算机的图书信息〔用2种方式实现〕。〔考察：NOTIN和运算符的使用预估：10分钟〕6.查询居住在上海和南京的顾客信息，并按照姓名升序排列〔用2种方式实现〕。〔考察：IN和OR以及排序的使用预估：15分钟〕7.查询发货城市在北京和常州的订单信息，并按照发货日期升序排列〔用2种方式实现〕。〔考察：IN和OR以及排序的使用预估：15分钟〕8.查询发货地点以“南〞开头的的订单信息，并按照发货日期升序排列。〔考察：模糊查询以及排序的使用预估：15分钟〕9.查询书名中含有‘_‘的图书信息，并按照出版日期升序排列。〔考察：模糊查询以及排序的使用预估：15分钟〕第五章内置函数1.查询所有课程的英文名称〔分别用CASE和DECODE方法〕。〔考察：CASE和DECODE方法预估：15分钟〕2.查询所有学生的成绩〔用等级表示：低于60表示不及格；60~70表示及格；70~80表示中等；80~100表示优秀〕〔用CASE方法〕。〔考察：CASE方法预估：15分钟〕参照教材附录B表完成下面习题：3.根据所在城市查询顾客所在的省〔分别用CASE和DECODE方法〕。〔考察：CASE和DECODE方法预估：15分钟〕4.创立所有客户的列表，如果一个客户不是由其他客户介绍来的，则显示字符‘NOREFERRED’。〔考察：CASE方法预估：10分钟〕5.显示所有图书的毛利〔百分数〕的列表，毛利应该显示为一个没有小数位的整数〔就是乘以100〕，后面带有百分号〔例如：0.2793=28%〕。〔考察：ROUND方法预估：15分钟〕6.显示所有图书的成本的最小整数。〔考察：CEIL方法预估：5分钟〕7.显示所有图书的成本和售价均截断小数点后的数值。〔考察：TRUNC方法预估：5分钟〕8.求出所有图书的成本和售价的余数。〔考察：MOD方法预估：5分钟〕9.显示所有图书的种别和书名，中间用'-’连接。〔考察：CONCAT方法预估：5分钟〕10.显示所有顾客的姓名，地址，城市，中间用'/’连接，显示长度到80。〔考察：CONCAT和VARCHAR方法预估：10分钟〕11.显示出版社名的开头4位以及联系人的姓。〔考察：SUBSTR方法以及字节数预估：10分钟〕12.显示把顾客所在城市是上海的替换成英文。〔考察：REPLACE方法预估：10分钟〕13.显示所有顾客的姓名，对于是直辖市的，需显示空白，否则显示城市名称。〔考察：NULLIF方法预估：10分钟〕14.重复显示发货城市4遍。〔考察：REPEAT方法预估：5分钟〕15.显示订单订单日期和发货日期的年。〔考察：YEAR方法预估：5分钟〕16.显示订单日期和发货日期。〔考察：DATE方法预估：5分钟〕17.显示当前系统时间。〔考察：TIME方法预估：5分钟〕18.显示当前系统时间的秒。〔考察：SECOND方法预估：5分钟〕19.显示订单日期和发货日期的月份。〔考察：MONTH方法预估：5分钟〕20.显示当前系统时间的分。〔考察：MINUTE方法预估：5分钟〕21.显示当前系统时间的小时。〔考察：HOUR方法预估：5分钟〕22.显示书名从第2个汉字的书名以及图书编号的后四位。〔考察：SUBSTR方法预估：5分钟〕23.显示书名中‘子’出现的位置。〔考察：POSSTR方法预估：5分钟〕第六章多表查询参照附录B完成练习1.创立一个列表，显示每本书的书名及出版社办公室中你再次订购每本书时需要联系的人的姓名和。〔传统和JOIN方法〕。〔考察：一般关联查询预估：30分钟〕2.确定哪些订单还没发货以及下达这些订单的客户的姓名，将结果按下达订单的日期排序〔传统和JOIN方法〕。〔考察：一般关联查询预估：30分钟〕3.列出已经购置了计算机种类的所有人的客户号和姓名以及书名和订单号。3.1查询计算机种类的图书编号。3.2查询订单详细表中是图书编号是3.1得到编号的订单号。3.3查询订单中订单号是3.2得到编号的顾客编号。3.4根据3.3的顾客编号得到顾客的姓名。〔传统和JOIN方法〕〔考察：一般关联查询预估：40分钟〕4.确定李三已经购置了哪些书。4.1查询李三的顾客编号。4.2查询订单表中李三编号对应的订单号。4.3查询订单详细表中4.2的订单号对应的图书编号。4.4根据图书编号取得书名。〔传统方法〕〔考察：一般关联查询预估：30分钟〕5.确定销售给王五的每一本书的利润。将结果按订单日期排序。如果订购了多本书，那么将结果按利润的降序排列。〔传统和JOIN方法〕〔考察：一般关联查询预估：30分钟〕6.哪一本书是由叫洪海的作者编写的〔传统和JOIN方法〕〔考察：一般关联查询预估：30分钟〕7.得到居住在南京并且订购了计算机图书的所有客户的列表。〔传统和JOIN方法〕〔考察：一般关联查询预估：30分钟〕8.查询所有订单购置的图书书名。〔JOIN方法〕〔考察：外关联查询预估：30分钟〕9.查询所有图书列表，及被哪些顾客购置以及该顾客的信息。〔JOIN方法〕〔考察：外关联查询预估：30分钟〕10.查询所有作者编写了哪些图书。〔JOIN方法〕〔考察：外关联查询预估：30分钟〕11.查询所有作者编写了哪些图书以及购置该图书的顾客信息。〔JOIN方法〕〔考察：外关联查询预估：30分钟〕12.得到所有居住在南京的顾客以及他订购图书的信息。〔JOIN方法〕〔考察：外关联查询预估：30分钟〕13.得到订单年在2003和2004年的订单信息以及顾客姓名。〔UNION，UNIONALL，传统和与JOIN方法〕〔考察：UNION查询预估：40分钟〕第七章分组查询1.查询单科成绩最高的分数和课程名，按照课程名排序〔多表关联,GroupBy子句〕〔传统和JOIN方法〕。1.1查询单科成绩最高的课程编号和成绩。1.2用1.1得到的课程号得出课程名。〔考察：多表关联查询预估：40分钟〕2.查询单科成绩最低分数和课程名,按照课程名排序〔多表关联,GroupBy子句〕。〔传统和JOIN方法〕〔考察：多表关联查询预估：10分钟〕3.查询平均分大于80分的学生的姓名，按照姓名排序。〔多表关联,having子句〕〔传统和JOIN方法〕〔考察：多表关联查询预估：40分钟〕4.查询科目中,80分以上或者不及格的人的人数。按课程分组。〔考察：多表关联查询预估：20分钟〕5.英语课的平均分数，最低，最高分数。〔考察：多表关联查询预估：20分钟〕6.最低分比最高分低40分的课程名。〔考察：多表关联查询预估：20分钟〕7.查询每门课最高分的人的学号和科目，成绩。〔考察：GroupBy查询预估：10分钟〕8.日语课不及格的人数。〔考察：COUNT查询预估：10分钟〕9.查询李四的考试总分数。〔考察：SUM查询预估：10分钟〕10，所有考试有过不及格的学生人数〔考察：COUNT查询预估：10分钟〕11.所有的学生姓名，及他们的平均分数，总分数。〔考察：AVG，SUM,GROUPBY查询预估：10分钟〕12.所有的图书书名，及平均成本，平均售价，最低成本，最高成本最低售价，最高售价，总成本，总售价，书的总数。〔考察：AVG，SUM,GROUPBY查询预估：20分钟〕13.订单中所有的图书书名，及其销售的数量。〔考察：SUM,GROUPBY查询预估：20分钟〕14.查询每个图书种类数量。〔考察：COUNT,GROUPBY查询预估：10分钟〕15.查询每个出版社出版的图书数量。〔考察：COUNT,GROUPBY查询预估：10分钟〕第八章子查询1.查询没参加过考试的学生的姓名,性别。〔两种方式〕〔考察：IN，EXISTS条件子查询预估：30分钟〕参看教程附录B表，完成以下习题：2.确定哪些书的零售价低于销售所有图书的平均零售价。〔考察：