最新MYSQL练习题及答案

1、精品文档题目一问题描述：为管理岗位业务培训信息，建立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 SWHERE S# IN(SELECT S# FROM C,SCWHERE C.C#=SC.C#AND CN=N' 税收基础 ')2.

2、 使用标准SQL嵌套语句查询选修课程编号为C2'的学员姓名和所属单位-实现代码：SELECT S.SN,S.SD FROM S,SCWHERE S.S#=SC.S#AND SC.C#='C2'3. 使用标准SQL嵌套语句查询不选修课程编号为C5'的学员姓名和所属单位-实现代码：SELECT SN,SD FROM SWHERE S# NOT IN(精品文档SELECT S# FROM SCWHERE C#='C5')4. 使用标准 SQL 嵌套语句查询选修全部课程的学员姓名和所属单位 -实现代码：SELECT SN,SD FROM SWHERE S

3、# IN(SELECT S# FROM SCRIGHT JOINC ON SC.C#=C.C# GROUP BY S#HAVING COUNT(*)=COUNT(S#)5. 查询选修了课程的学员人数-实现代码：SELECT 学员人数 =COUNT(DISTINCT S#) FROM SC6. 查询选修课程超过 5 门的学员学号和所属单位-实现代码：SELECT SN,SD FROM SWHERE S# IN(SELECT S# FROM SCGROUP BY S#HAVING COUNT(DISTINCT C#)>5)题目二问题描述：本题用到下面三个关系表：CARD借书卡。 CNO 卡号

4、， NAME 姓名， CLASS 班级BOOKS图书。 BNO 书号，BNAME 书名 AUTHOR 作者，PRICE 单价，QUANTITY 库存册数BORROW 借书记录。 CNO 借书卡号， BNO 书号， RDATE 还书日期 备注：限定每人每种书只能借一本；库存册数随借书、还书而改变。要求实现如下 15 个处理：I .写出建立BORROW表的SQL语句，要求定义主码完整性约束和引用完整性约束。2 找出借书超过 5本的读者 ,输出借书卡号及所借图书册数。3. 查询借阅了 "水浒 "一书的读者，输出姓名及班级。4. 查询过期未还图书，输出借阅者（卡号） 、书号及还书日

5、期。5. 查询书名包括 "网络"关键词的图书，输出书号、书名、作者。6. 查询现有图书中价格最高的图书，输出书名及作者。7. 查询当前借了 "计算方法 "但没有借 "计算方法习题集 "的读者， 输出其借书卡号， 并按 卡号降序排序输出。8 .将"C01"班同学所借图书的还期都延长一周。9 从 BOOKS 表中删除当前无人借阅的图书记录。10 如果经常按书名查询图书信息，请建立合适的索引。II 在 BORROW 表上建立一个触发器，完成如下功能：如果读者借阅的书名是"数据库技术及应用 "，就将该读

6、者的借阅记录保存在 BORROW_SAVE 表中（注 ORROW_SAVE 表结构同 BORROW 表）。12建立一个视图，显示 "力 01"班学生的借书信息（只要求显示姓名和书名） 。13 查询当前同时借有 "计算方法 "和 "组合数学 "两本书的读者，输出其借书卡号，并按 卡号升序排序输出。14 假定在建 BOOKS 表时没有定义主码，写出为 BOOKS 表追加定义主码的语句。15 对 CARD 表做如下修改：a. 将 NAME 最大列宽增加到 10 个字符(假定原为 6 个字符)。b. 为该表增加 1 列 NAME (系名)，可

7、变长，最大 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 BORROWGROUP BY CNOHAVING COUNT

8、(*)>53. 查询借阅了 "水浒 "一书的读者，输出姓名及班级-实现代码：SELECT * FROM CARD cWHERE EXISTS(SELECT * FROM BORROW a,BOOKS bWHERE a.BNO=b.BNOAND b.BNAME=N' 水浒 'AND a.CNO=c.CNO)4. 查询过期未还图书，输出借阅者(卡号) 、书号及还书日期-实现代码：SELECT * FROM BORROWWHERE RDATE<GETDATE()5. 查询书名包括 "网络 "关键词的图书，输出书号、书名、作者-实现代

9、码：SELECT BNO,BNAME,AUTHOR FROM BOOKSWHERE BNAME LIKE N'% 网络 %'6. 查询现有图书中价格最高的图书，输出书名及作者-实现代码：SELECT BNO,BNAME,AUTHOR FROM BOOKSWHERE PRICE=(SELECT MAX(PRICE) FROM BOOKS)并按卡7. 查询当前借了 "计算方法 " 但没有借 " 计算方法习题集 "的读者， 输出其借书卡号 号降序排序输出-实现代码：SELECT a.CNOFROM BORROW a,BOOKS bWHERE a

10、.BNO=b.BNO AND b.BNAME=N'计算方法 'AND NOT EXISTS(SELECT * FROM BORROW aa,BOOKS bbWHERE aa.BNO=bb.BNOAND bb.BNAME=N' 计算方法习题集 'AND aa.CNO=a.CNO)ORDER BY a.CNO DESC8. 将 "C01" 班同学所借图书的还期都延长一周-实现代码：UPDATE b SET RDATE=DATEADD(Day,7,b.RDATE)FROM CARD a,BORROW bWHERE a.CNO=b.CNOAND a.

11、CLASS=N'C01'9. 从 BOOKS 表中删除当前无人借阅的图书记录-实现代码：DELETE A FROM BOOKS aWHERE NOT EXISTS(SELECT * FROM BORROWWHERE BNO=a.BNO)10. 如果经常按书名查询图书信息，请建立合适的索引 -实现代码：CREATE CLUSTERED INDEX IDX_BOOKS_BNAME ON BOOKS（BNAME）11. 在 BORROW 表上建立一个触发器，完成如下功能：如果读者借阅的书名是" 数据库技术及应用 " ，就将该读者的借阅记录保存在 BORROW_SA

12、VE 表中（注 ORROW_SAVE 表 结构同 BORROW 表）-实现代码：CREATE TRIGGER TR_SAVE ON BORROWFOR INSERT,UPDATEASIF ROWCOUNT>0INSERT BORROW_SAVE SELECT i.*FROM INSERTED i,BOOKS bWHERE i.BNO=b.BNOAND b.BNAME=N' 数据库技术及应用 '12. 建立一个视图，显示 "力 01" 班学生的借书信息（只要求显示姓名和书名）-实现代码：CREATE VIEW V_VIEWASSELECT a.NAME,

13、b.BNAMEFROM BORROW ab,CARD a,BOOKS bWHERE ab.CNO=a.CNOAND ab.BNO=b.BNOAND a.CLASS=N' 力 01'13. 查询当前同时借有 "计算方法 "和 "组合数学 "两本书的读者，输出其借书卡号，并按卡 号升序排序输出-实现代码：SELECT a.CNOFROM BORROW a,BOOKS bWHERE a.BNO=b.BNOAND b.BNAME IN(N' 计算方法 ',N' 组合数学 ')GROUP BY a.CNOHAVING

14、 COUNT(*)=2ORDER BY a.CNO DESC14. 假定在建 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)题目三假设有下面两个关系模

15、式：职工（职工号、姓名、年龄、职务、工资、部门号） ，其中职工号为主码；部门（部门号、名称、部门经理、电话） ，其中部门号为主码；用 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

16、 （ 10 ），Zgname char （20 ），Zgage int ，Zgsal numeric（10）,Bmno char（10）,Primary key（zgno）,Foreign key（bmno） reference bm（bmno）On delete no actionOn update cascade,Constraint zg1 check（zgage<=60）;题目四1、有以下三个关系模式：学生关系S，包括学号SNo、姓名SN、年龄SA、系别SD ;课程关系C ,包括课程号 CNo、课程名CN、学分CC ;学生选课关系 SC ，包括 SNo 、 CNo 和成绩 G。(

17、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)

18、 GRANT DELETE, UPDATE( SNo, CNo) ON SC TO 李小兰;(3分)(3) DELETE FROM SWHERE Sno = 9900205;(3分)(4) DELETE FROM SCWHERE SNo IN( SELECT SNoFROM SWHERE SN = 王云'）;题目五 设某商业集团关于商店销售商品的数据库中有三个基本表： 商店 SHOP （S# ，SNAME ，AREA ， MGR_NAME ） 其属性是商店编号，商店名称，区域名，经理姓名。销售 SALE （S#， G#， QUANTITY ） 其属性是商店编号，商品编号，销售数量。商品

19、 GOODS （ G#，GNAME ，PRICE ） 其属性是商品编号，商品名称，单价。（1）写出下列查询的关系代数表达式：检索销售“冰箱”的商店的编号和商店名称。（2 ）从 SALE 表中，把“开开商店”中销售单价高于 1000 元的商品的销售元组全部删除。（3 ）写出下列操作的 SQL 语句：统计区域名为“ EAST ”的所有商店销售的每一种商品的总 数量和总价值。（1）关系代数表达式：n S# , SNAME （cGNAME='冰箱'（SHOP ?SALE ?GOODS ）（ 3分）（ 2） DELETE FROM SALEWHERE S# IN （ SELECT S#F

20、ROM SHOPWHERE SNAME=' 开开商店 '）AND G# IN （SELECT G#FROM GOODSWHERE PRICE>1000 )；(3) SELECT C.G# ，GNAME ，SUM (QUANTITY )AS SUM_QUANTITY ，PRICE*SUM (QUANTITY )AS SUM_VALUEFROM SHOP A ，SALE B ，GOODS CWHERE A.S#=B.S# AND B.G#=C.G# AND AREA='EAST'GROUP BY C.G# ， GNAME ；题目六：某学校有若干系，每个系有若干

21、学生，若干课程， 每个学生选修若干课程， 每门课有若干学 生选修， 某一门课可以为不同的系开设， 今要建立该校学生选修课程的数据库， 数据库中要 存放以下信息：系：系名，系代号，系主任名，电话 学生：学号，姓名，年龄，性别，所在系代号 课程：课程号码，课程名称每个学生选修某门课的日期，成绩每个系开设的课程。请你设计：关于此学校数据库的 E-R 图并把此 E-R 图转换为关系模型。指出各关系的主码。答：学生（学号,姓名，年龄，性别，系代码）系（系代码,系名，系主任名，电话）课程（课程号，课程名称）学生选课（学号，课程号，选课日期，成绩）系-课程（系代码，课程号）指出各关系的主码。学号/系代码/课程号/学号，课程号/系代码，课程号题目七假设某商业集团数据库中有一关系模式R如下：R （商店编号，商品编号，数量， 部门编号,负责人）如果规定：每个商店的每种商品只在一个部门销售；每个商店的每个部门只有一个负责人；每个商店的每种商品只有一个库存数量。试回