MySQL练习题3
时间：2021-08-31
题目一
问题描述：
为管理岗位业务培训信息，建立3个表:
S (S#,SN,SD,SA) S#,SN,SD,SA 分别代表学号、学员姓名、所属单位、学员年龄
C (C#,CN ) C#,CN 分别代表课程编号、课程名称
SC ( S#,C#,G ) S#,C#,G 分别代表学号、所选修的课程编号、学习成绩
1. 使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名
--实现代码：
SELECT SN,S# FROM S
WHERE [S#] IN(
SELECT [S#] FROM C,SC
WHERE C.[C#]=SC.[C#]
AND CN=N\'税收基础\')
2. 使用标准SQL嵌套语句查询选修课程编号为’C2’的学员姓名和所属单位
--实现代码：
SELECT S.SN,S.SD FROM S,SC
WHERE S.[S#]=SC.[S#]
AND SC.[C#]=\'C2\'
3. 使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员姓名和所属单位
--实现代码：
SELECT SN,SD FROM S
WHERE [S#] NOT IN(
SELECT [S#] FROM SC
WHERE [C#]=\'C5\')
4. 使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位
--实现代码：
SELECT SN,SD FROM S
WHERE [S#] IN(
SELECT [S#] FROM SC
RIGHT JOIN
C ON SC.[C#]=C.[C#] GROUP BY [S#]
HAVING COUNT(*)=COUNT([S#]))
5. 查询选修了课程的学员人数
--实现代码：
SELECT 学员人数=COUNT(DISTINCT [S#]) FROM SC
6. 查询选修课程超过5门的学员学号和所属单位
--实现代码：
SELECT SN,SD FROM S
WHERE [S#] IN(
SELECT [S#] FROM SC
GROUP BY [S#]
HAVING COUNT(DISTINCT [C#])>5)
题目二
问题描述：
本题用到下面三个关系表：
CARD 借书卡。 CNO 卡号，NAME 姓名，CLASS 班级
BOOKS 图书。 BNO 书号，BNAME 书名,AUTHOR 作者，PRICE 单价，QUANTITY 库存册数
BORROW 借书记录。 CNO 借书卡号，BNO 书号，RDATE 还书日期
备注：限定每人每种书只能借一本;库存册数随借书、还书而改变。
要求实现如下15个处理：
1. 写出建立BORROW表的SQL语句，要求定义主码完整性约束和引用完整性约束。
2. 找出借书超过5本的读者,输出借书卡号及所借图书册数。
3. 查询借阅了\"水浒\"一书的读者，输出姓名及班级。
4. 查询过期未还图书，输出借阅者(卡号)、书号及还书日期。
5. 查询书名包括\"网络\"关键词的图书，输出书号、书名、作者。
6. 查询现有图书中价格最高的图书，输出书名及作者。
7. 查询当前借了\"计算方法\"但没有借\"计算方法习题集\"的读者，输出其借书卡号，并按卡号降序排序输出。
8. 将\"C01\"班同学所借图书的还期都延长一周。
9. 从BOOKS表中删除当前无人借阅的图书记录。
10.如果经常按书名查询图书信息，请建立合适的索引。
11.在BORROW表上建立一个触发器，完成如下功能：如果读者借阅的书名是\"数据库技术及应用\"，就将该读者的借阅记录保存在BORROW_SAVE表中(注ORROW_SAVE表结构同BORROW表)。
12.建立一个视图，显示\"力01\"班学生的借书信息(只要求显示姓名和书名)。
13.查询当前同时借有\"计算方法\"和\"组合数学\"两本书的读者，输出其借书卡号，并按卡号升序排序输出。
14.假定在建BOOKS表时没有定义主码，写出为BOOKS表追加定义主码的语句。
15.对CARD表做如下修改：
a. 将NAME最大列宽增加到10个字符(假定原为6个字符)。
b. 为该表增加1列NAME(系名)，可变长，最大20个字符。
1. 写出建立BORROW表的SQL语句，要求定义主码完整性约束和引用完整性约束
--实现代码：
CREATE TABLE BORROW(
CNO int FOREIGN KEY REFERENCES CARD(CNO),
BNO int FOREIGN KEY REFERENCES BOOKS(BNO),
RDATE datetime,
PRIMARY KEY(CNO,BNO))
2. 找出借书超过5本的读者,输出借书卡号及所借图书册数
--实现代码：
SELECT CNO,借图书册数=COUNT(*)
FROM BORROW
GROUP BY CNO
HAVING COUNT(*)>5
3. 查询借阅了\"水浒\"一书的读者，输出姓名及班级
--实现代码：
SELECT * FROM CARD c
WHERE EXISTS(
SELECT * FROM BORROW a,BOOKS b
WHERE a.BNO=b.BNO
AND b.BNAME=N\'水浒\'
AND a.CNO=c.CNO)
4. 查询过期未还图书，输出借阅者(卡号)、书号及还书日期
--实现代码：
SELECT * FROM BORROW
WHERE RDATE
5. 查询书名包括\"网络\"关键词的图书，输出书号、书名、作者
--实现代码：
SELECT BNO,BNAME,AUTHOR FROM BOOKS
WHERE BNAME LIKE N\'%网络%\'
6. 查询现有图书中价格最高的图书，输出书名及作者
--实现代码：
SELECT BNO,BNAME,AUTHOR FROM BOOKS
WHERE PRICE=(
SELECT MAX(PRICE) FROM BOOKS)
7. 查询当前借了\"计算方法\"但没有借\"计算方法习题集\"的读者，输出其借书卡号，并按卡号降序排序输出
--实现代码：
SELECT a.CNO
FROM BORROW a,BOOKS b
WHERE a.BNO=b.BNO AND b.BNAME=N\'计算方法\'
AND NOT EXISTS(
SELECT * FROM BORROW aa,BOOKS bb
WHERE aa.BNO=bb.BNO
AND bb.BNAME=N\'计算方法习题集\'
AND aa.CNO=a.CNO)
ORDER BY a.CNO DESC
8. 将\"C01\"班同学所借图书的还期都延长一周
--实现代码：
UPDATE b SET RDATE=DATEADD(Day,7,b.RDATE)
FROM CARD a,BORROW b
WHERE a.CNO=b.CNO
AND a.CLASS=N\'C01\'
9. 从BOOKS表中删除当前无人借阅的图书记录
--实现代码：
DELETE A FROM BOOKS a
WHERE NOT EXISTS(
SELECT * FROM BORROW
WHERE BNO=a.BNO)
10. 如果经常按书名查询图书信息，请建立合适的索引
--实现代码：
CREATE CLUSTERED INDEX IDX_BOOKS_BNAME ON BOOKS(BNAME)
11. 在BORROW表上建立一个触发器，完成如下功能：如果读者借阅的书名是\"数据库技术及应用\"，就将该读者的借阅记录保存在BORROW_SAVE表中(注ORROW_SAVE表结构同BORROW表)
--实现代码：
CREATE TRIGGER TR_SAVE ON BORROW
FOR INSERT,UPDATE
AS
IF @@ROWCOUNT>0
INSERT BORROW_SAVE SELECT i.*
FROM INSERTED i,BOOKS b
WHERE i.BNO=b.BNO
AND b.BNAME=N\'数据库技术及应用\'
12. 建立一个视图，显示\"力01\"班学生的借书信息(只要求显示姓名和书名)
--实现代码：
CREATE VIEW V_VIEW
AS
SELECT a.NAME,b.BNAME
FROM BORROW ab,CARD a,BOOKS b
WHERE ab.CNO=a.CNO
AND ab.BNO=b.BNO
AND a.CLASS=N\'力01\'
13. 查询当前同时借有\"计算方法\"和\"组合数学\"两本书的读者，输出其借书卡号，并按卡号升序排序输出
--实现代码：
SELECT a.CNO
FROM BORROW a,BOOKS b
WHERE a.BNO=b.BNO
AND b.BNAME IN(N\'计算方法\',N\'组合数学\')
GROUP BY a.CNO
HAVING COUNT(*)=2
ORDER BY a.CNO DESC
14. 假定在建BOOKS表时没有定义主码，写出为BOOKS表追加定义主码的语句
--实现代码：
ALTER TABLE BOOKS ADD PRIMARY KEY(BNO)
15.1 将NAME最大列宽增加到10个字符(假定原为6个字符)
--实现代码：
ALTER TABLE CARD ALTER COLUMN NAME varchar(10)
15.2 为该表增加1列NAME(系名)，可变长，最大20个字符
--实现代码：
ALTER TABLE CARD ADD 系名 varchar(20)
题目三
假设有下面两个关系模式：
职工(职工号、姓名、年龄、职务、工资、部门号)，其中职工号为主码;
部门(部门号、名称、部门经理、电话)，其中部门号为主码;
用SQL语言定义这两个关系模式，要求在模式中完成以下完整性约束条件的定义：
定义每个模式的主码和非空约束;
定义参照完整性;
定义职工年龄不得超过60岁。
答：Create table bm
(bmno char(10) primary key，
Bmname char(20) not null，
Bmjl char(10)not null，
Bmtel char(10)unique);
Create table zhigong
(zgno char(10)，
Zgname char(20)，
Zgage int，
Zgsal numeric(10),
Bmno char(10),
Primary key(zgno),
Foreign key(bmno) reference bm(bmno)
On delete no action
On update cascade,
Constraint zg1 check(zgage<=60));
题目四
1、有以下三个关系模式：
学生关系S，包括学号SNo、姓名SN、年龄SA、系别SD;
课程关系C，包括课程号CNo、课程名CN、学分CC;
学生选课关系SC，包括SNo、CNo和成绩G。
(1)用SQL语言定义SC表，注意：说明主键码和外键码(如果有的话)。
(2)对于学生选课关系SC，授予李小兰删除权限以及对SNo和CNo的修改权限。
(3)删除学号为“9900205”的学生。
(4)从学生选课关系SC中，删除王云(学生关系中可能有重名)的所有选课。
答：(1)CREATE TABLE SC {
SNo INT,
CNo INT,
G INT,
PRIMARY KEY( SNo, CNo),
FOREIGN KEY(SNo) REFERENCES S( SNo),
FOREIGN KEY(CNo) REFERENCES C(CNo)
}; (3分)
(2)GRANT DELETE, UPDATE( SNo, CNo) ON SC TO 李小兰; (3分)
(3)DELETE FROM S WHERE Sno = 9900205; (3分)
(4) DELETE FROM SC
WHERE SNo IN
( SELECT SNo
FROM S
WHERE SN = ‘王云’
);
题目五
设某商业集团关于商店销售商品的数据库中有三个基本表：
商店SHOP(S#，SNAME，AREA，MGR_NAME)
其属性是商店编号，商店名称，区域名，经理姓名。
销售SALE(S#，G#， QUANTITY)
其属性是商店编号，商品编号，销售数量。
商品GOODS(G#，GNAME，PRICE)
其属性是商品编号，商品名称，单价。
(1)写出下列查询的关系代数表达式：检索销售“冰箱”的商店的编号和商店名称。
(2)从SALE表中，把“开开商店”中销售单价高于1000元的商品的销售元组全部删除。
(3)写出下列操作的SQL语句：统计区域名为“EAST”的所有商店销售的每一种商品的总数量和总价值。
(1)关系代数表达式：πS#，SNAME(σGNAME='冰箱'(SHOP⋈SALE⋈GOODS)) (3分)
(2)DELETE FROM SALE
WHERE S# IN(SELECT S#
FROM SHOP
WHERE SNAME='开开商店')
AND G# IN(SELECT G#
FROM GOODS
WHERE PRICE>1000);
(3)SELECT C.G#，GNAME，SUM(QUANTITY)AS SUM_QUANTITY，
PRICE*SUM(QUANTITY)AS SUM_VALUE
FROM SHOP A，SALE B，GOODS C
WHERE A.S#=B.S# AND B.G#=C.G# AND AREA='EAST'
GROUP BY C.G#，GNAME;
题目六：
某学校有若干系，每个系有若干学生，若干课程，每个学生选修若干课程，每门课有若干学生选修，某一门课可以为不同的系开设，今要建立该校学生选修课程的数据库，数据库中要存放以下信息：
系：系名，系代号，系主任名，电话
学生：学号，姓名，年龄，性别，所在系代号
课程：课程号码，课程名称
每个学生选修某门课的日期，成绩
每个系开设的课程。
请你设计：
关于此学校数据库的E-R图
并把此E-R图转换为关系模型。
指出各关系的主码。
答：
学生(学号,姓名,年龄,性别,系代码)
系(系代码,系名,系主任名,电话)
课程(课程号,课程名称)
学生选课(学号,课程号,选课日期,成绩)
系-课程(系代码,课程号)
指出各关系的主码。 学号/系代码/课程号/学号，课程号/系代码，课程号
题目七
假设某商业集团数据库中有一关系模式R如下：R (商店编号，商品编号，数量，部门编号，负责人)
如果规定：每个商店的每种商品只在一个部门销售;每个商店的每个部门只有一个负责人; 每个商店的每种商品只有一个库存数量。
试回答下列问题：
(1) 根据上述规定，写出关系模式R的基本函数依赖。(
(2) 找出关系模式R的候选码。
(3) 试问关系模式R最高已经达到第几范式?为什么?
(4) 如果R不属于3NF，请将R分解成3NF模式集。
答：(1) 有三个函数依赖：
(商店编号，商品编号) →部门编号
(商店编号，部门编号) →负责人
(商店编号，商品编号) →数量
(2) R的候选码是 (商店编号，商品编号)
(3) 因为R中存在着非主属性“负责人”对候选码 (商店编号、商品编号)的传递函数依赖，所以R属于2NF，R不属于3NF。
(4) 将R分解成：
R1 (商店编号，商品编号，数量，部门编号)
R2 (商店编号，部门编号，负责人)
题目八
假设某商业集团数据库中有三个实体集。
“仓库”实体集，属性有仓库号、仓库名和地址等;
“商店”实体集，属性有商店号、商店名、地址等;
“商品”实体集，属性有商品号、商品名、单价。
设仓库与商品之间存在“库存”联系，每个仓库可存储若干种商品，每种商品存储在若干仓库中，每种商品可在若干商店里销售，每个商店销售一种商品有月份和月销售量两个属性;仓库、商店、商品之间存在着“供应”联系，有月份和月供应量两个属性。
(1)试画出ER图。
(2)将ER图转换成关系模型。
(3)说明主键和外键。
答1.
2. 仓库(仓库号，仓库名，地址)
商品(商品号，商品名，单价)
商店(商店号，商店名，地址)
库存(仓库号，商品号，日期，库存量)
销售(商店号，商品号，月份，月销售量)
供应(仓库号，商店号，商品号，月份，月供应量)
3.仓库号/商品号/商店号/仓库号，商品号/商店号，商品号/仓库号，商店号，商品号
