《数据库原理与应用》综合练习及参考答案2.doc

1、假设有一个书店，书店的管理者要对书店的经营状况进行管理，需要建立一个数据库，其中包括两个表：存书（书号，书名，出版社，版次，出版日期，作者，书价，进价，数量）销售（日期，书号，数量，金额）请用SQL实现书店管理者的下列要求：（1）建立存书表和销售表；（2）掌握书的库存情况，列出当前库存的所有书名、数量、余额（余额进价数量，即库存占用的资金）；（3）统计总销售额；（4）列出每天的销售报表，包括书名、数量和合计金额（每一种书的销售总额）；（5）分析畅销书，即列出本期（从当前日期起，向前30天）销售数量大于100的书名、数量。答：(1)Book (ISBN, title, publisher, edition, publication date, author, book price, purchase price, quantity)CREATE TABLE Book ( ISBN CHAR(17) PRIMARY KEY, Title VARCHAR(50) NOT NULL, Publisher VARCHAR(50) , Edition CHAR(2), P_date CHAR(10), Author CHAR(8), B_price MONEY, P_price MONEY, B_Quantity INT CHECK Quantity=0)Sales (date, ISBN, quantity, amount)CREATE TABLE Sales (SNo CHAR(12) PRIMARY KEY, S_date DATATIME, ISBN CHAR(17) NOT NULL, S_quantity INT, S_amount MONEY)(2) SELECT Title, B_Quantity , P_price B_Quantity AS Balance FROM Book(3) SELECT SUM(S_amount ) AS Amount(4) SELECT S_date, Title, SUM(S_quantity ) AS Quantity , SUM(S_amount ) AS AmountFROM Book, Sales WHERE Sales.ISBN=Book.ISBN GROUP BY S_data, Title (5)SELECT Title SUM(S_quantity) AS QuantityFROM Book, SalesWHERE Sales.ISBN=Book.ISBN AND (S_date BETWEEN GETDATE( )-30 AND GETDATE( )GROUP BY Title HAVING SUM(S_quantity)100ORDER BY SUM(S_quantity) DESC2、设有如下四个基本表S:S# 学号 SN姓名 AGE年龄 DEPT所在系 S1 丁一 20计算机 S2 王二 19计算机 S3 张三 19外语 C:C# 课程号 CN 课程名称 C1 数据库 C2 操作系统 C3 微机原理 SC:S# 学号 C# 课程号 GR 成绩 S1 C180S1 C289S2 C359 T:T#教师号 TN教师姓名 SAL工资 COMM职务津贴 C# 所讲课程 T1 王力 800C1T2 张兰 1200300C2T3 李伟 700150C1 (1)用SQL创建S表，S#为主键，SN不能为空。 (2)创建计算机系学生的视图，该视图的属性列由学号、姓名、课程号和任课教师号组成。 (3)检索计算机系年龄在20岁以上的学生学号。 (4)检索姓王的教师所讲课程的课程号及课程名称。 (5)检索张三同学所学课程的成绩，列出SN、C#、GR。 (6)检索选修总收入超过1000元的教师所讲课程的学生姓名、课程号和成绩。 (7)检索没有选修C1课程且选修课程数为两门的学生的姓名和平均成绩，并按平均成绩降序排列。 (8)检索选修和张三同学所选修课程中任意一门相同的学生姓名，课程名。 (9)S1同学选修了C3,将此信息插入SC表中。 (10)删除S表中没有选 修任何课程的学生记录。 答：(1)CREATE TABLE S( S# CHAR(6) CONSTRAINT S_Prim PRIMARY KEY, SN CHAR(8) CONSTRAINT SN_CONT NOY NULL, AGE INT, DEPT VARCHAR(20)(2) CREATE VIEW S_SC_T (S#,SN,C#,T#)AS SELECT S.S#, SN, SC.C#, T# FROM S, SC, T WHERE S.S#=SC.S# AND SC.C#=T.C# (3)SELECT S# FROM S WHERE DEPT=计算机 AND AGE 20(4)SELECT T.C# C#, CN FROM T,C WHERE T.C#=C.C# AND TN LIKE 王%(5)SELECT SN,C#,GR FROM S,SCWHERE SN=张三 AND S.S#=SC.S#(6)SELECT SN,SC.C# C#,GR FROM S,SC,TWHERE (SAL+COMM)1000 AND S.S#=SC.S# AND SC.C#=T.C#(7)SELECT * INTO S_Temp FROM SWHERE C1 NOT IN (SELECT C# FROM SC WHERE S#=S.S#)SELECT SN, AVG(GR) AS AVG FROM S_Temp JOIN SC ON S_Temp.S#=SC.S#GROUP BY S_Temp.S# HAVING COUNT(*)=2 ORDER BY AVG(GR) DESCSELECT SN, AVG(GR) AS AVG FROM S, SC WHERE S.S#=SC.S#GROUP BY S.S# HAVING (COUNT(*)=2) AND (S.S# NOT IN(SELECT S# FROM SC WHERE C#=C1)ORDER BY AVG(GR) DESCSELECT SN, AVG(GR) AS AVG FROM S, SC WHERE S.S#=SC.S# AND (SC.S# ALL(SELECT S# FROM SC WHERE C#=C1)GROUP BY SC.S# HAVING (COUNT(*)=2) ORDER BY AVG(GR) DESC(8)SELECT SN, CN FROM S,SC,CWHERE S.S# = SC.S# AND SC.C# = C.C# AND SC.C# IN (S