SQL经典练习题目初学者必看.docx
《SQL经典练习题目初学者必看.docx》由会员分享，可在线阅读，更多相关《SQL经典练习题目初学者必看.docx（15页珍藏版）》请在冰豆网上搜索。
SQL经典练习题目初学者必看
本题用到下面三个关系表：
CARD借书卡。
CNO卡号，NAME姓名，CLASS班级
BOOKS图书。
BNO书号，BNAME书名,AUTHOR作者，PRICE单价，QUANTITY库存册数
BORROW借书记录。
CNO借书卡号，BNO书号，RDATE还书日期
备注：
限定每人每种书只能借一本；库存册数随借书、还书而改变。
要求实现如下个处理：
1．写出建立BORROW表的SQL语句，要求定义主码完整性约束和引用完整性约束。
2．找出借书超过本的读者,输出借书卡号及所借图书册数。
3．查询借阅了"水浒"一书的读者，输出姓名及班级。
4．查询过期未还图书，输出借阅者（卡号）、书号及还书日期。
5．查询书名包括"网络"关键词的图书，输出书号、书名、作者。
6．查询现有图书中价格最高的图书，输出书名及作者。
7．查询当前借了"计算方法"但没有借"计算方法习题集"的读者，输出其借书卡号，并按卡号降序排序输出。
8．将"C01"班同学所借图书的还期都延长一周。
9．从BOOKS表中删除当前无人借阅的图书记录。
10．如果经常按书名查询图书信息，请建立合适的索引。
11．在BORROW表上建立一个触发器，完成如下功能：
如果读者借阅的书名是"数据库技术及应用"，就将该读者的借阅记录保存在BORROW_SAVE表中（注ORROW_SAVE表结构同BORROW表）。
12．建立一个视图，显示"力"班学生的借书信息（只要求显示姓名和书名）。
13．查询当前同时借有"计算方法"和"组合数学"两本书的读者，输出其借书卡号，并按卡号升序排序输出。
14．假定在建BOOKS表时没有定义主码，写出为BOOKS表追加定义主码的语句。
15．对CARD表做如下修改：
a.将NAME最大列宽增加到个字符（假定原为个字符）。
b.为该表增加列NAME（系名），可变长，最大个字符。
--创建数据库
createdatabaselib
onprimary
（name='lib',
filename='D:
\lib.mdf',
size=3Mb,
maxsize=10Mb,
filegrowth=1Mb
）,
（name='lib_ndf',
filename='D:
\lib_ndf.ndf',
size=3Mb,
maxsize=10Mb,
filegrowth=1Mb
）
logon
（name='lib_log',
filename='D:
\lib_log.ldf',
size=3Mb,
maxsize=10Mb,
filegrowth=10%）
--使用自己创建的数据库
uselib;
/*
CARD借书卡。
CNO卡号，NAME姓名，CLASS班级
BOOKS图书。
BNO书号，BNAME书名,AUTHOR作者，PRICE单价，QUANTITY库存册数
BORROW借书记录。
CNO借书卡号，BNO书号，RDATE还书日期
*/
createtablecard
（
cnointprimarykeyidentity（1,1）,
cnamevarchar（20）notnull,
classvarchar（20）notnull
）
createtablebooks
（
bnointprimarykeyidentity（1,1）,
bnamevarchar（20）,
authorvarchar（20）,
pricefloat,
quantityint
）
createtableborrow
（
cnointnotnullforeignkeyreferencescard（cno）,
bnointnotnullforeignkeyreferencesbooks（bno）,
rdatedatetime
）
droptableborrow;
--限定每人每种书只能借一本
altertableborrow
addconstraintPK_borrow_cno_bnoprimarykey（cno,bno）;
insertintocard
select'张三','test01'union
select'李四','test01'union
select'王五','java01'union
select'赵六','java02'union
select'孙七','java01'
insertintobooks
select'SQL','李华',89,50union
select'oracle','唐僧',80,15union
select'java','孙悟空',78,23union
select'c','猪八戒',80,34union
select'c++','沙僧',67,56union
select'测试','白骨精',34,45union
select'水浒','小白龙',45,55union
select'网络技术','牛魔王',34,65
insertintoborrow
select1,1,nullunion
select1,2,nullunion
select1,3,nullunion
select1,4,nullunion
select1,5,nullunion
select2,3,nullunion
select2,4,nullunion
select2,5,nullunion
select2,6,nullunion
select3,1,nullunion
select3,2,nullunion
select3,3,null
insertintoborrow
values（3,7,null）
insertintoborrow
values（2,7,null）
--SQL题目的秘籍
--
（1）看题目中间要求我们“显示|查询|列出|输出”什么内容。
那么select后面就接什么内容。
--
（2）看这些字段出自哪些表，那么from后面就接哪些表
--（3）如果from后面有多张表，首先考虑表之间的关联关系（隐士关系）。
然后再考虑题目中显示的告诉我们的关系（条件）。
--（4）看是否需要分组，那么就考虑是否要使用groupby，
--看select子句后面是否出现了聚合函数，如果出现了聚合函数，那么没有放在聚合函数里面的字段，全部放到groupby后面去。
--（5）看是否需要对查询出来的结果集再次进行筛选，如果需要，那么使用having
--（6）看是否需要排序，考虑使用orderby
--（7）如果一个题目，觉得比较复杂，那么将题目拆分，采用子查询。
createtablecard
（
cnointnotnull,
namevarchar（20）,
classvarchar（20）,
）
altertablecard
addconstraintPK_card_cnoprimarykey（cno）;
createtablebooks
（
bnointnotnull,
bnamevarchar（20）,
authorvarchar（20）,
pricemoney,
quantityint
）
altertablebooks
addconstraintPK_books_bnoprimarykey（bno）;
createtableborrow
（
cnointnotnull,
bnointnotnull,
rdatedatetime
）
altertableborrow
addconstraintPK_card_books_cno_bnoprimarykey（cno,bno）;
usehuiting
insertintobooks
select1,'语文','张三',20union
select2,'数学','李四',30union
select3,'英语','王五',40union
select4,'物理','马六',50
select*frombooks
altertablebooks
addconstraintCK_books_pricecheck（pricebetween20and100）;
usehuiting
insertintoborrow
select9,1,'2011-07-0800:
00:
00'union
select8,2,'2011-07-0800:
00:
01'union
select7,3,'2011-07-0800:
00:
02'union
select6,4,'2011-07-0800:
00:
03'
select*fromborrow
insertintocard
select9,'张三',0union
select8,'李四',1union
select7,'王五',2union
select6,'马六',3
select*fromcard
--1．写出建立BORROW表的SQL语句，要求定义主码完整性约束和引用完整性约束。
--2．找出借书超过本的读者,输出借书卡号及所借图书册数。
selecto"卡号",cname"姓名",t.num"所借图书册数"
fromcard,（selectcno,count（bno）asnum
fromborrow
groupbycno
havingcount（bno）>=5）t
whereo=o
--表示借书查过本的卡号以及借书数目
selectcno,count（bno）asnum
fromborrow
groupbycno
havingcount（bno）>=5;
select*frombooks;
select*fromcard;
select*fromborrow;
--3．查询借阅了"水浒"一书的读者，输出姓名及班级。
selectcname,class
fromcard
wherecnoin（
selectcnofromborrowwherebno=（selectbnofrombookswherebname='水浒'）
）
--in拿一个字段和多个值进行比较，表示在某个范围里面
--=一个字段和一个值比较
--首先查看“水浒”这本书的编号
selectbnofrombookswherebname='水浒'
--到borrow表中看哪些卡号借了号这本书
selectcnofromborrowwherebno=（selectbnofrombookswherebname='水浒'）;
4．查询过期未还图书，输出借阅者（卡号）、书号及还书日期。
--5．查询书名包括"网络"关键词的图书，输出书号、书名、作者。
selectbno,bname,author
frombooks
wherebnamelike'%网络%'
--6．查询现有图书中价格最高的图书，输出书名及作者。
selectbname,author
frombooks
wherepricein（selectmax（price）frombooks）
--7．查询当前借了"计算方法"但没有借"计算方法习题集"的读者，输出其借书卡号，
--并按卡号降序排序输出。
Card（cno,name,class）
Books（bno,bname,author,price,quantity）
Borrow（cno,bno,rdate）
selectcno
fromborrow
wherebno=（selectbnofrombookswherebname='c'）
selectcno
fromborrow
wherebnoin（selectbnofrombookswherebname='水浒'）
orderbycnodesc
--自身连接查询
select*fromborrow;
select*fromborrow;
--查询既接了号书又借了号书的卡号
selecto
fromborrowb1,borrowb2
whereo=oandb1.bno=（selectbnofrombookswherebname='水浒'）andb2.bno=（selectbnofrombookswherebname='c'）
--o=o表示同一个人
selectdistinctt.bcno
from
（selectobcno,b1.bno,b1.rdate
fromborrowb1,borrowb2
whereo=oandb1.bno<>7andb2.bno=1andonotin（
selecto
fromborrowb1,borrowb2
whereo=oandb1.bno=7andb2.bno=1
））t
--查看计算方法这本书的编号
selectbnofrombookswherebname='SQL'
--查看计算方法习题集这本书的编号
selectbnofrombookswherebname='网络技术'
--8．将"C01"班同学所借图书的还期都延长一周。
updateborrowsetrdate=dateadd（dd,7,rdate）;
--9．从BOOKS表中删除当前无人借阅的图书记录。
--首先：
查看有哪些书已经被借了
selectdistinctbno
fromborrow
--再去删除
deletefrombookswherebnonotin（selectdistinctbno
fromborrow）
10．如果经常按书名查询图书信息，请建立合适的索引。
11．在BORROW表上建立一个触发器，完成如下功能：
如果读者借阅的书名是"数据库技术及应用"，就将该读者的借阅记录保存在BORROW_SAVE表中（注ORROW_SAVE表结构同BORROW表）。
12．建立一个视图，显示"力"班学生的借书信息（只要求显示姓名和书名）。
--13．查询当前同时借有"计算方法"和"组合数学"两本书的读者，输出其借书卡号，
--并按卡号升序排序输出。
selecto
fromborrowb1,borrowb2
whereo=oandb1.bno=（selectbnofrombookswherebname='计算方法'）
andb2.bno=（selectbnofrombookswherebname='组合数学'）
--14．假定在建BOOKS表时没有定义主码，写出为BOOKS表追加定义主码的语句。
altertableborrow
addconstraintPK_borrow_cno_bnoprimarykey（cno,bno）;
--15．对CARD表做如下修改：
--a.将NAME最大列宽增加到个字符（假定原为个字符）。
altertablecard
altercolumncnamevarchar（10）notnull
--b.为该表增加列NAME（系名），可变长，最大个字符。
altertablecard
addDeptNamevarchar（20）
复制代码执行，创建好表，然后往里面插入数据，完成下面个题目：
CREATETABLESTUDENT
（
SNOVARCHAR（3）NOTNULL,--学号
SNAMEVARCHAR（4）NOTNULL,--姓名
SSEXVARCHAR
（2）NOTNULL,--性别
SBIRTHDAYDATETIME,--出生日期
CLASSVARCHAR（5）--班级
）
go
CREATETABLECOURSE
（
CNOVARCHAR（5）NOTNULL,--课程号
CNAMEVARCHAR（10）NOTNULL,--课程名
TNOVARCHAR（10）NOTNULL--教师编号
）
go
CREATETABLESCORE
（
SNOVARCHAR（3）NOTNULL,--学号
CNOVARCHAR（5）NOTNULL,--课程号
DEGREENUMERIC（10,1）NOTNULL--成绩
）
go
CREATETABLETEACHER
（
TNOVARCHAR（3）NOTNULL,--教师编号
TNAMEVARCHAR（4）NOTNULL,TSEXVARCHAR
（2）NOTNULL,--教师姓名
TBIRTHDAYDATETIMENOTNULL,PROFVARCHAR（6）,--出生日期
DEPARTVARCHAR（10）NOTNULL--系部名称
）
--测试数据
INSERTINTOSTUDENT（SNO,SNAME,SSEX,SBIRTHDAY,CLASS）VALUES（108,'曾华','男',1977-09-01,95033）;
INSERTINTOSTUDENT（SNO,SNAME,SSEX,SBIRTHDAY,CLASS）VALUES（105,'匡明','男',1975-10-02,95031）;
INSERTINTOSTUDENT（SNO,SNAME,SSEX,SBIRTHDAY,CLASS）VALUES（107,'王丽','女',1976-01-23,95033）;
INSERTINTOSTUDENT（SNO,SNAME,SSEX,SBIRTHDAY,CLASS）VALUES（101,'李军','男',1976-02-20,95033）;
INSERTINTOSTUDENT（SNO,SNAME,SSEX,SBIRTHDAY,CLASS）VALUES（109,'王芳','女',1975-02-10,95031）;
INSERTINTOSTUDENT（SNO,SNAME,SSEX,SBIRTHDAY,CLASS）VALUES（103,'陆君','男',1974-06-03,95031）;
GO
INSERTINTOCOURSE（CNO,CNAME,TNO）VALUES（'3-105','计算机导论',825）
INSERTINTOCOURSE（CNO,CNAME,TNO）VALUES（'3-245','操作系统',804）;
INSERTINTOCOURSE（CNO,CNAME,TNO）VALUES（'6-166','数据电路',856）;
INSERTINTOCOURSE（CNO,CNAME,TNO）VALUES（'9-888','高等数学',100）;
GO
INSERTINTOSCORE（SNO,CNO,DEGREE）VALUES（103,'3-245',86）;
INSERTINTOSCORE（SNO,CNO,DEGREE）VALUES（105,'3-245',75）;
INSERTINTOSCORE（SNO,CNO,DEGREE）VALUES（109,'3-245',68）;
INSERTINTOSCORE（SNO,CNO,DEGREE）VALUES（103,'3-105',92）;
INSERTINTOSCORE（SNO,CNO,DEGREE）VALUES（105,'3-105',88）;
INSERTINTOSCORE（SNO,CNO,DEGREE）VALUES（109,'3-105',76）;
INSERTINTOSCORE（SNO,CNO,DEGREE）VALUES（101,'3-105',64）;
INSERTINTOSCORE（SNO,CNO,DEGREE）VALUES（107,'3-105',91）;
INSERTINTOSCORE（SNO,CNO,DEGREE）VALUES（108,'3-105',78）;
INSERTINTOSCORE（SNO,CNO,DEGREE）VALUES（101,'6-166',85）;
INSERTINTOSCORE（SNO,CNO,DEGREE）VALUES（107,'6-106',79）;
INSERTINTOSCORE（SNO,CNO,DEGREE）VALUES（108,'6-166',81）;
GO
INSERTINTOTEACHER（TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART）
VALUES（804,'李诚','男','1958-12-02','副教授','计算机系'）;
INSERTINTOTEACHER（TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART）
VALUES（856,'张旭','男','1969-03-12','讲师','电子工程系'）;
INSERTINTOTEACHER（TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART）
VALUES（825,'王萍','女','1972-05-05','助教','计算机系'）;
INSERTINTOTEACHER（TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART）
VALUES（831,'刘冰','女','1977-08-14','助教','电子工程系'）;
练习题目
1、查询Student表中的所有记录的Sname、Ssex和Class列。
2、查询教师所有的单位即不重复的Depart列。
3、查询Student表的所有记录。
4、查询Score表中成绩在到之间的所有记录。
5、查询Score表中成绩为，或的记录。
6、查询Student表中"95031"班或性别为"女"的同学记录
升级会员 