《数据库原理与应用》综合练习及参考答案2
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 V ARCHAR(50) NOT NULL,
Publisher V ARCHAR(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 Amount
FROM Book, Sales
WHERE Sales.ISBN=Book.ISBN
GROUP BY S_data, Title
(5)
SELECT Title SUM(S_quantity) AS Quantity
FROM Book, Sales
WHERE Sales.ISBN=Book.ISBN AND (S_date BETWEEN GETDATE( )-30 AND GETDATE( )) GROUP BY Title HA VING SUM(S_quantity)>100
ORDER BY SUM(S_quantity) DESC
2、设有如下四个基本表
S:
S#学号SN姓名AGE年龄DEPT所在系
S1 丁一20计算机
S2 王二19计算机
S3 张三19外语
……
C:
C#课程号CN课程名称
C1 数据库
C2 操作系统
C3 微机原理
……
SC:
S#学号C#课程号GR成绩
S1 C180
S1 C289
S2 C359
……
T:
T#教师号TN教师姓名SAL工资COMM职务津贴C#所讲课程T1 王力800C1
T2 张兰1200300C2
T3 李伟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 V ARCHAR(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,SC
WHERE SN=‘张三’ AND S.S#=SC.S#
(6)
SELECT SN,SC.C# C#,GR
FROM S,SC,T
WHERE (SAL+COMM)>1000
AND S.S#=SC.S# AND SC.C#=T.C#
(7)
SELECT * INTO S_Temp
FROM S
WHERE ‘C1’ NOT IN (SELECT C# FROM SC WHERE S#=S.S#) SELECT SN, A VG(GR) AS A VG
FROM S_Temp JOIN SC ON S_Temp.S#=SC.S#
GROUP BY S_Temp.S# HA VING COUNT(*)=2
ORDER BY A VG(GR) DESC
SELECT SN, A VG(GR) AS A VG
FROM S, SC
WHERE S.S#=SC.S#
GROUP BY S.S#
HA VING (COUNT(*)=2) AND
(S.S# NOT IN(SELECT S#
FROM SC
WHERE C#=‘C1’)) ORDER BY A VG(GR) DESC
SELECT SN, A VG(GR) AS A VG
FROM S, SC
WHERE S.S#=SC.S# AND
(SC.S# <> ALL(SELECT S#
FROM SC
WHERE C#=‘C1’))
GROUP BY SC.S#
HA VING (COUNT(*)=2)
ORDER BY A VG(GR) DESC
(8)
SELECT SN, CN
FROM S,SC,C
WHERE S.S# = SC.S# AND SC.C# = C.C# AND
SC.C# IN (SELECT C#
FROM S,SC
WHERE S.S# = SC.S# AND SN=‘张三’)
(9)
INSERT INTO SC (S#,C#)
V ALUES (‘S1’, ‘C3’)
(10)
DELETE
FROM S
WHERE S# NOT IN (SELECT DISTINCT S# FROM SC)