第3章 SQL综合实习答案.doc

简答题第5题：(1) 创建S表，S#为主键，SN不能为空。CREATE TABLE S ( S#CHAR(8) PRIMARY KEY,SN CHAR(8) NOT NULL, AGEINT,DEPT VARCHAR(20);INSERT INTO SVALUES(S1,丁一,20,计算机)；INSERT INTO SVALUES(S2,王一,19,计算机)；(2) 创建计算机系学生的视图，该视图的属性列由学号、姓名、课程名和任课教师号组成。CREATE VIEW computer_student(S#, SN, C#,T#)ASSELECT S.S#, SN,SC.C#,T# FROM S,SC,TWHERE S.S#= SC.S# AND SC.C#= T.C# AND DEPT=计算机(3) 检索计算机系年龄在20岁以上的学生学号。SELECT S# FROM S WHERE AGE20 AND DEPT=计算机(4) 检索姓王的教师所讲课程的课程号和课程名称。SELECT C.C#,CN FROM C, TWHERE C.C#= T.C# AND TN LIKE 王%(5) 检索张三同学所学课程的成绩，列出SN, C#,GRSELECT SN,C#,GR FROM S,SCWHERE S.S#= SC.S# AND SN= 张三(6) 检索选修总收入超过1000元的教师所讲课程的学生姓名、课程号和成绩。SELECT SN,T.C#,GR FROM T,SC,SWHERE T.C# =SC.C# AND S.S#= SC.S# AND (SAL+COMM)1000(7) 检索没有选修C1课程且选修课程数为两门的学生的姓名和平均成绩，并按平均成绩降序排列。SELECT SN, AVG(GR) AS AVGSCORE FROM S,SCWHERE S.S#= SC.S# AND C#C1GROUP BY SN HAVING COUNT(C#)=2ORDER BY AVG(GR) DESC(8) 检索选修和张三同学所选课程中任意一门相同的学生姓名，课程名或：CN=ANYSELECT SN,CN FROM S,SC,CWHERE S.S#= SC.S# AND C.C#=SC.C# AND C# IN (SELECT C# FROM S,SC WHERE S.S#= SC.S# AND SN= 张三) AND SN 张三(9) S1同学选修了C3，将此信息插入SC表中。INSERT INTO SC(S#,C#) VALUES(S1,C3) 或 INSERT INTO SC VALUES(S1,C3)(10) 删除S表中没有选修任何课程的学生的记录。DELETE FROM S WHERE S# NOT IN (SELECT DISTINCT S# FROM SC)三、设计题1. 图书（Book）书号类型书名作者单价出版社号BNoBTypeBNameBAuthBPricePNo出版社（Publish）出版社号出版社名称所在城市电话PNoPNamePCityPTel(1) 查找在“高等教育出版社”出版，书名为“操作系统”的图书的作者名。SELECT BAuth FROM Book, PublishWHERE Book.PNo= Publish.PNo AND BName=操作系统 AND PName=高等教育出版社(2) 查找为作者“张欣”出版全部“小说”类图书的出版社的电话。SELECT PTel FROM Book, PublishWHERE Book.PNo= Publish.PNo AND BType =小说 AND BAuth=张欣(3) 查询“电子工业出版社”出版的“计算机”类图书的价格，同时输出出版社名称及图书类别。SELECT BPrice, PName, BType FROM Book, PublishWHERE Book.PNo= Publish.PNo AND PName =电子工业出版社 AND BType =计算机(4) 查找比“人民邮电出版社”出版的“高等数学”价格低的同名书的有关信息。SELECT * FROM BookWHERE BName =高等数学 AND BPriceANY(SELECT BPrice FROM Book,Publish WHERE Book.PNo= Publish.PNo AND PName =人民邮电出版社 AND BName =高等数学）AND PName 人民邮电出版社(5) 查找书名中有“计算机”一词的图书的书名及作者。SELECT BName, BAuth FROM BookWHERE BName LIKE %计算机%(6) 在“图书”表中增加“出版时间”（BDate）项，其数据类型为日期型。ALTER TABLE Book ADD BDate datetime(7) 在“图书”表中以“作者”建立一个索引。CREATE INDEX Name ON Book(BAuth) desc2. 存书（BookStore）书号书名出版社版次出版日期作者书价进价数量BNoBNamePublishVersionPDateBAuthBPriceBInPriceBCount销售（BookSell）日期书号数量金额SDateBNoSCountSMoney(1) 建立存书表和销售表。CREATE TABLE BookStore ( BNo CHAR(8) PRIMARY KEY, BName VARCHAR(20), Publish VARCHAR(20), Version INT,PDate DATETIME,BAuthVARCHAR(10), BPrice NUMERIC(4,1),BInPrice NUMERIC(4,1),BCount INT);CREATE TABLE BookSell ( BNoCHAR(8) PRIMARY KEY,SDate DATETIME, SCountINT,PDate DATETIME,SMoneySMALLMONEY);(2) 掌握书的库存情况，列出当前库存的所有书名、数量、余额（余额=进价*数量，即库存占用的资金）SELECT BName, BCount, BPrice* BCount AS TOTALCOUNT FROM BookStore(3) 统计总销售额。SELECT SUM(SCount * SMoney) AS TOTALMONEY FROM BookSell(4) 列出每天的销售报表，包括书名、数量和合计金额（每一种书的销售总额）SELECT BNo, BName , SDate , BCount , SCount * SMoney AS TOTALMONEY FROM BookStore, BookSellWHERE BookStore. BNo= BookSell. Bno GROUP BY BNo, BName , SDate , BCount(5) 分析畅销书，即列出当期（从当前起，向前30天）销售数量大于100的书名、数量。SELECT BName , SCount FROM BookStore, BookSellWHERE BookStore. BNo= BookSell. BNo AND SCount100 A