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]

//自连接

解二：

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)

//distinct+取反


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


//竟然没想起用group by having分一下组来满足条件