搜索
三、设计题1、设有以下两个数据表，各表的结果及字段名如下：图书（Book）包括书名（BNo）、类型（BType）、书名（BName）、作者（BAuth）、单价（BPrice）、出版社（PNo）出版社（Publish）包括出版社号（PNo）、出版社名称（PName）、所在城市（PCity）、电话（PTel）。用SQL实现下述功能：（1）在“”高等教育出版社出版、书名为“操作系统”的图书的作者名；答：selectBAuthfromBook,PublishwhereBook.PNo=Publish.PNoandBName='操作系统'andPName='高等教育出版社出版'（2）查找为作者“张欣”出版全部“小说”类图书的出版社的电话；答：selectPTelfromBook,PublishwhereBook.PNo=Publish.PNoandBAuth='张欣'andBType='小说'（3）查询“电子工业出版社”出版的“计算机”类的图书的价格，同时输出版社名称及图书类别；答：selectBPrice,PName,BTypefromBook,PublishwhereBook.PNo=Publish.PNoandPName='电子工业出版社'andBType='BType'（4）查找比“人民邮电出版社”出版的“高等数学”价格低的同名书的有关信息；答：select*fromBookwhereBName='高等数学'andBPriceANY(selectBPricefromBook,PublishwherePName='人民邮电出版社'andBName='高等数学'andPublish.PNo=Book.PNo)（5）查找书名中有“”计算机一词的图书的书名及作者；答：selectBName,BAuthfromBookwhereBNamelike'%计算机%'（6）在“图书”表中正增加“出版时间”（BDate）项，其数据类型为日期型；答：altertableBookaddBDatedatetime（7）在“图书”表中以“作者”建立一个索引。答：createuniqueindexBAuth_BookonBook(BAuth)2、假设有一个书店，书店的管理者要对书店的经营状况进行管理，需要建立一个数据库，其中包括两个表：存书（书号，书名，出版社，版次，出版日期，作者，进价，数量）销售（日期，书号，数量，金额）请用SQL实现书店管理者的下列要求：（1）建立存书表和销售表；答：createtableBook(BNoint,BNamechar(10),Publishchar(20),Editionint,Datedatetime,Authchar(10),Priceint,Inpriceint,Numberint)createtableSale(Datedatetime,BNoint,Numberint,Moneyint)（2）掌握书的库存情况，列出当前库存的所有书名、数量、余额（余额=进价数量，即库存占用的资金）；答：selectBName,Number,Inprice*Numberas'余额（进价X数量）'fromBook（3）统计总销售额；答：selectSUM(Number*Money)as'总销售额'fromSale（4）列出每天的销售报表，包括书名、数量、和合计金额（每一种书的销售总额）；答：selectBName,Sale.Number,Sale.Number*Moneyas'合计金额'fromBook,SalewhereBook.BNo=Sale.BNo（5）分析畅销书，即列出本期（从当前日期起，向前30天）销售数量大于100的书名、数量。答：selectBName,Sale.NumberfromBook,SalegroupbyBName,Sale.Number,Sale.Datehavingcount(Sale.Number)100andSale.Date=getdate()-30四、简答题1、简述SQL支持的三级逻辑结构。答：1、SQL语言支持的关系数据库三级逻辑结构由外层、概念层和内存构成。2、在概念层，对应于概念模式的概念记录型的是基本表。基本表是这样的一种表，它本身实际存在。一个基本表就是一个关系，它不是由其他表导出的表。基本表是使用CREATETABLE语句建立的。3、在外层，用户所看到的可以是基本表，也可以是视图，也可以是基本表＋视图。视图是一个虚拟表，它是由一个或几个基本表导出的表，它不直接存在于物理存储器上的表。视图是使用CREATESQLVIEW语句建立的。3、解释本章所涉及的有关基本概念的定义：基本表、视图、索引、系统权限、对象权限、角色、并说明视图、角色的作用。答：基本表：一个关系对应一个基本表；一个或多个基本表对应一个存储文件视图：视图是从一个或几个基本表导出的表，是一个虚拟的表索引：记录的关键字与其相应地址的对应表系统权限：数据库用户能够对数据库系统进行某种特定的操作的权利对象权限：数据库用户在指定的数据库对象上进行某种特定的操作的权利角色：是多种权限的集合，可以把角色授予用户或其他角色视图的作用：通过构造视图，能够在用户和应用程序与实际的基本表之间提供更好的数据独立性。角色的作用：当把某个角色授予用户（和角色）或从用户（或角色）处收回时，就同时授予或收回了该角色代表的全部权限。这样就避免了为每个用户重复授予每一项权限的繁琐工作，可以大大简化管理数据库用户权限的工作。5、设有如下四个基本表S，C，SC，T，结构如图所示：SS#学号SN学生姓名AGE年龄DEPT所在系S1丁一20计算机S2王二19计算机S3张三19外语...CC#课程号CN课程名称C1数据库C2操作系统C3微机原理….SCS#学号C#课程号GR成绩S1C180S2C289S3C359….TT#教师号TN教师姓名SAL工资COMM职务津贴C#所讲课程T1王力800C1T2张兰1200300C2T3李伟700150C1.….（1）用SQL的DDL语言创建S表，S#为主键，SN不能为空。答：createtableS(S#char(10)primarykeynotnull,SNvarchar(10)notnull,AGEtinyintnull,DEPTnchar(10))createtableC(C#char(6)primarykey,CNvarchar(20)notnull)createtableSC(S#char(10)primarykeynotnull,C#char(6)primarykeynotnull,GRtinyint)createtableT(T#char(5)primarykeynotnull,TNvarchar(10)notnull,PROFvarchar(10),SALmoney,COMMmoney,C#char(6))（2）创建计算机系学生的视图，该视图的属性列由学号、姓名、课程号和任课教师号组成。答：)createviewComputerDEPT(S#,SN,C#,T#)asselectS#,SN,C#,T#fromS,C,SC,TwhereDEPTin('计算机')andS.S#=SC.S#andC.C#=SC.C#andT.C#=C.C#（3）检索计算机系年龄在20岁以上的学生学号。答：selectS#fromSwhereDEPTin('计算机')andAGE20（4）检索姓王的教师所讲课程的课程号及课程名称。答：selectC#,CNfromC,TwhereT#like'王%'andC.C#=T.C#（5）检索张三同学所学课程的成绩，列出SN，C#，GR。答：selectSN,C#,GRfromS,C,SCwhereSN='张三'andS.S#=SC.S#andC.C#=SC.C#（6）检索选修总收入超过1000元的教师所讲课程的学生姓名、课程号和成绩。答：selectSN,C#,GRfromS,SC,TwhereSAL+COMM1000andS.S#=SC.S#andSC.C#=T.C#（7）检索没有选修C1课程且选修课程数为两门的学生的姓名和平均成绩，并按平均成绩降序排列。答：selectSN,avg(GR)fromS,SCwhere(S.S#=SC.S#)and('C1'notin(selectC#fromSCwhereS.S#=SC.S#))groupbySNhavingcount(SC.S#)=2orderbyavg(gr)desc（8）检索选修和张三同学所课程中任意一门相同的学生姓名，课程名。答：selectSN,CNfromS,C,SCwhereS.S#=SC.S#andC.C#=SC.C#andC#in(selectC#fromSCwhereS#='S3')（9）S1同学选修了C3,将此信息插入SC表中。答：insertintoSC(S#,C#)values('S1','C3')（10）删除S表中没有选修任何课程的学生记录。答：deletefromSwhereS#notin(selectS#fromSC)