现在的位置: 首页 > 综合 > 正文

sql 典型SQL题(多表联查)

2017年08月10日 ⁄ 综合 ⁄ 共 2768字 ⁄ 字号 评论关闭
1.
数据库表A 有个字段a是int类型 
a中有数据有1到9任意(有重复的) 
想取得a中,当 a=1时 a=2时 a=3…… 各一条记录的列表 
select top 1 * from A where a=1 
union 
select top 1 * from A where a=2 
... 
union 
select top 1 * from A where a=9 
2.
S(SNO,SNAME)学生表
C(CNO,CNAME,CTEARCHER)课程表
SC(SNO,CNO,SCGRADE)成绩表 
1.找出没有选修李明老师课程的所有学生姓名
2.找出每门课程成绩在90分以上的学生姓名及成绩
select S.SNAME from STUDENT as S inner join TECHER as T 
inner join Souce as So 
on T.CNO=So.CNO 
on S.SNO=So.SNO 
where T.CTEACHER <>'李明'
select s.SNAME
from STUDENT as S inner join TECHER as T 
inner join Souce as So 
on T.CNO=So.CNO 
on S.SNO=So.SNO 
where not exists(select 1 from sc where sno=a.sno and cno=a.cno and grade<90)
3.
本题用到下面三个关系表: 
CARD     借书卡。   CNO 卡号,NAME  姓名,CLASS 班级 
BOOKS    图书。     BNO 书号,BNAME 书名,AUTHOR 作者,PRICE 单价,QUANTITY 库存册数 
BORROW   借书记录。 CNO 借书卡号,BNO 书号,RDATE 还书日期 
备注:限定每人每种书只能借一本;库存册数随借书、还书而改变。
1. 写出建立BORROW表的SQL语句,要求定义主码完整性约束和引用完整性约束 
CREATE TABLE BORROW( 
    CNO int FOREIGN KEY REFERENCES CARD(CNO), //references后面的是主表
    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     //having子句用于对分组后的数据筛选
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<GETDATE() 
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 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)

抱歉!评论已关闭.