三个表：图书信息表、读者信息表、借阅表
图书信息（书号、书名、作者、出版日期、出版社、图书存放位置、总数量）
读者信息表（图书证号、姓名、所在系、借书上限数）
借阅表（图书证号、书号、借出日期、应还日期）
数据库表结构如下：
BOOK(BNO, BN, BAU, BDA, BPU, BPL, BNU)
READER(RNO, RN, RDE, RUP)
BORROW(RNO, BNO, BDA, RDA)
1. 查询被借阅的图书号以’TP’开头的图书信息
select * from book
where bno in (select bno from borrow where bno like 'TP%')
2. 查和“S0701026”读者借了相同图书的读者的图书证号和姓名
Select rno,rn from reader
Where rno in
(select rno from borrow
Where bno in
(select bno from borrow
Where rno=’ S0701026’))
3. 检索至少借阅了“数据结构”和“操作系统教程”的读者图书证号
select borrow.rno from borrow,book
where borrow.bno=book.bno
and bn='数据结构'
and rno in (
select borrow.rno from borrow,book
where borrow.bno=book.bno
and bn='操作系统教程')
4. 查询没有借阅“C程序设计”的读者姓名
select rn from reader
where rno not in(
select rno from borrow
where bno=(
select bno from book where bn='C程序设计'))
5．查询张朝阳和李丽都借阅的图书书号
select A.bno from borrow as A, borrow as B
where A.rno in
(select rno from reader where rn='李丽' )
and B.rno in
(select rno from reader where rn='张朝阳')
and A.bno=B.bno
6.查询借阅了图书的读者信息
Select *
From reader
Where rno in(
Select rno from borrow)
7.查没有被借阅的图书信息
select * from book
where bno not in(
select bno from borrow)
8.查没有借书的读者的图书证号和姓名
select rno,rn from reader
where rno not in(
select rno from borrow)
9. 查询借阅图书数量达到2本的读者信息
select * from reader
where rno in (
select rno from borrow
group by rno
having count(bno)>=2)
10. 查询有过期未还图书的读者的书号、姓名、所在系
select borrow.bno,reader.rn,reader.rde from borrow,reader
where borrow.rda < getdate()
and reader.rno = borrow.rno
11. 统计各个系读者的数量，显示系名和数量
select rde 系名,count(*) 数量
from reader
group by rde
设学生选课库中有三个数据表：
S（sno，sname，birthday，sdepartment，tel，sex）
SC（sno，cno，grade）
C（cno，cname，teacher，pcno）
完成下列查询任务：
1、检索至少选修课程“数据结构”和“C语言”的学生学号。
Select x.sno
From SC AS x, SC As y
Where x.sno=y.sno and
x.cno=(Select cno From C Where cname='数据结构') and
y.cno=(Select cno From C Where cname='C语言')
2、检索学习课程号为C02的学生学号和姓名。
Select S.sno,sname
From S,SC
Where SC.sno=S.sno and SC.cno='C02'
3、检索选修课程“数据库”的学生学号和姓名。
Select S.sno,sname
From S,SC,C
Where SC.sno=S.sno and SC.cno=C.cno and C.cname='数据库'
4、检索选修课程号为C02或C04的学生学号。
Select distinct S.sno
From S,SC
Where SC.sno=S.sno and (SC.cno='C02' or SC.cno='C04')
5、检索至少选修课程号为C02和C04的学生姓名。
Select sname From S Where sno in
(Select x.sno
From SC AS x, SC As y
Where x.sno=y.sno and x.cno='C02' and y.cno='C04')
6、检索没有选修课程“数据库”的学生姓名和年龄。
Select sname,year(getdate()) - year(birthday)
From S where not Exists(
Select * From C, SC
Where S.sno = SC.sno and C.cno = SC.cno and cname='数据库')
7、在SC表中检索男生选修的课程名。
Select distinct cname From S, SC, C
Where S.sex='男' and S.sno=SC.sno and SC.cno=C.cno
13、列出选修课程超过3门的学生姓名及选修门数。
Select sname, count(*) From SC, S
Where S.sno=SC.sno
Group By sname
Having count(*) > 3
14、求选修了各课程的学生人数。
Select cname, count(*) From SC, C
Where C.cno=SC.cno
Group By cname
15、在SC中，求选修课程C01的学生的学号和得分，并将结果按分数降序排序。
Select sno, grade From SC Where cno='C01' order by Grade Desc
16、查找每个同学的学号及选修课程的平均成绩情况。
Select sno, AVG(Grade) From SC
Group By sno
17、列出学生所有可能的选课情况。
Select S.sno, sname, cname From S, SC, C
Where S.sno=Sc.sno and SC.cno=C.cno
18、列出每个同学的学号及选修课程的平均成绩情况，没有选修的同学也列出。
Select s.sno,AVG(Grade)
From SC right Join S ON sc.sno=s.sno
Group By s.sno
19、列出每个同学的学号及选修课程号，没有选修的同学也列出。
Select s.sno,cno
From SC right Join S ON sc.sno=s.sno
24、检索至少有两名男生选修的课程名。
Select cname From C Where cno in
(Select cno From SC, S
Where SC.sno=S.sno and sex='男'
Group by SC.cno
Having Count(SC.sno) > 1)
25、检索S中不姓“王”同学记录。
Select * From S Where sname not Like '王%'
26、检索和“李军”同性别并同班的同学姓名。
Select sname From S
Where sex=(Select sex From S Where sname='李军')
and sdepartment=(Select sdepartment From S Where sname='李军')
and sno <>(Select sno From S Where sname='李军')
27、统计被学生选修的课程门数。
Select Count(distinct cno) From SC
28、求选修C04课程的学生的平均年龄。
Select AVG(Year(getdate()) - Year(Birthday)) From SC, S
Where SC.cno='C04' and SC.sno=S.sno
29、求LIU老师所授课程的每门课程的学生平均成绩。
Select cno, AVG(Grade) From SC
Where cno in(
Select cno From C Where Teacher='Liu')
Group By cno
30、统计每门课程的学生选修人数（超过10人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。
Select cno, Count(sno) as rs
From SC
Group by cno
Having Count(sno)> 10
Order By rs Desc,cno Asc
31、求年龄大于所有女同学年龄的男同学姓名和年龄。
Select sname,Year(getdate()) - Year(Birthday) From S
Where Year(Birthday) <
(Select Min(Year(Birthday)) From S Where sex='女')
参考资料：http://hi.baidu.com/枫叶慧志/blog/item/cbaa76d7009b3426960a1634.html?timeStamp=1294115586062