Question: How can I list non-contignous data?
In database pubs, I create a table test using statement as below, and I insert several row as below

 程序代码
create table test
( id int primary key )
go

insert into test values (1 )
insert into test values (2 )
insert into test values (3 )
insert into test values (4 )
insert into test values (5 )
insert into test values (6 )
insert into test values (8 )
insert into test values (9 )
insert into test values (11)
insert into test values (12)
insert into test values (13)
insert into test values (14)
insert into test values (18)
insert into test values (19)
go

Now I want to list the result of the non-contignous row as below,how can I do it?

 程序代码
Missing after Missing before
------------- --------------
6             8
9             11
…

Answer ：
select id from test t where not exists(select 1 from test where id=t.id+1)
or not exists(select 1 from test where id=t.id-1)

Question: How can I list all book with prices greather than the average price of books of the same type?
In database pubs, have a table named titles , its column named price mean the price of the book, and another named type mean the type of books.
Now I want to get the result as below:

 程序代码
type         title                                                                            price                
------------ -------------------------------------------------------------------------------- ---------------------
business     The Busy Executive's Database Guide                                              19.9900
…
…
…
…

Answer ：

 程序代码
select a.type,a.title,a.price from titles a,
(select type,price=avg(price) from titles group by type)b
where a.type=b.type and a.price>b.price