SQL考题

//复习时候，最好能不答案写下来，SQL语言光看不行，还要自己写

问题描述：
本题用到下面三个关系表：
CARD　　 借书卡。　 CNO 卡号，NAME　姓名，CLASS 班级
BOOKS　　图书。　　 BNO 书号，BNAME 书名,AUTHOR 作者，PRICE 单价，QUANTITY 库存册数
BORROW　 借书记录。 CNO 借书卡号，BNO 书号，RDATE 还书日期
备注：限定每人每种书只能借一本；库存册数随借书、还书而改变。
要求实现如下15个处理：&
　1． 写出建立BORROW表的SQL语句，要求定义主码完整性约束和引用完整性约束。//表名borrow
　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 TABLEBORROW(
　　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'水浒'
　　　　ANDa.CNO=c.CNO)//建立表关联

4. 查询过期未还图书，输出借阅者（卡号）、书号及还书日期
--实现代码：
SELECT * FROM BORROW
WHERERDATE<GETDATE()//获得日期函数

5. 查询书名包括"网络"关键词的图书，输出书号、书名、作者
--实现代码：
SELECT BNO,BNAME,AUTHOR FROM BOOKS
WHERE BNAMELIKE 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 SETRDATE=DATEADD(Day,7,b.RDATE)//dateadd函数
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)
-------------------------------------------------------------------------------------------------------------------
问题描述：
为管理岗位业务培训信息，建立3个表:
S (S#,SN,SD,SA)　 S#,SN,SD,SA 分别代表学号、学员姓名、所属单位、学员年龄
C (C#,CN )　　　　C#,CN　　　 分别代表课程编号、课程名称
SC ( S#,C#,G )　　S#,C#,G　　 分别代表学号、所选修的课程编号、学习成绩

要求实现如下5个处理：
　1. 使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名
　2. 使用标准SQL嵌套语句查询选修课程编号为’C2’的学员姓名和所属单位
　3. 使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员姓名和所属单位
　4. 使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位
　5. 查询选修了课程的学员人数
　6. 查询选修课程超过5门的学员学号和所属单位

1. 使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名
--实现代码：
SELECT SN,SD 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(DISTINCT [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)