SQL笔试题

1、怎么把下面的表

year month amount
1991 1 1.1
1991 2 1.2
1991 3 1.3
1991 4 1.4
1992 1 2.1
1992 2 2.2
1992 3 2.3
1992 4 2.4
查成这样一个结果
year m1 m2 m3 m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4

解一：

select [year],
(select amount from tab t where [month] = 1 and t.year = tab.year) as 'm1',
(select amount from tab t where [month] = 2 and t.year = tab.year) as 'm2',
(select amount from tab t where [month] = 3 and t.year = tab.year) as 'm3',
(select amount from tab t where [month] = 4 and t.year = tab.year) as 'm4'
from tab
group by [year]

[color=green]//自连接[/color]

解二：

select t1.year,
t1.amount as 'm1',
t2.amount as 'm2',
t3.amount as 'm3',
t4.amount as 'm4'
from tab t1,
tab t2,
tab t3,
tab t4,
where t1.month < t2.month
and t2.month < t3.month
and t3.month < t4.month
and t1.year = t2.year
and t2.year = t3.year
and t3.year = t4.year


2、用一条SQL语句 查询出每门课都大于80分的学生姓名
name kecheng fenshu
张三 语文 81
张三 数学 75
李四 语文 76
李四 数学 90
王五 语文 81
王五 数学 100
王五 英语 90

解：

select distinct [name]
from student
where [name] not in (
select distinct [name]
from student
where fenshu <= 80)

[color=green]//distinct+取反[/color]


3.图书馆管理系统中，有2个表.book表包括book_id, book_name字段. 借阅关系表checkout表包括三个字段reader_name,book_id,days(已借阅时间)。每个人节约期限是30天。求出过期书大于2本的读者的名字，书名和已借阅时间

要求用最短的SQL来实现上面的查询功能。

select b.reader_name,book_name,days
from book as a,
(select reader_name
from checkout
where days>30
group by reader_name
having count(book_id)>2) as b,checkout as c
where a.book_id = c.book_id
and b.reader_name = c.reader_name


[color=green]//竟然没想起用group by having分一下组来满足条件[/color]