某外企SQL Server面试题

核心提示： In database pubs, I create a table test using statement as below, and I insert several row as belowcreate table test( id int primary key )goinser

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 10：

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)

Question11: 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 11：

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

试题点评：通览整个试题，我们不难发现，这份试题是针对SQL Server数据库人员的。而从难度分析上来看，这份试题也属于同类试题中比较难的了。之所以说它难，首先是限定时间的全英文试题；其次，尽管这份试题主要是考核开发能力，但却涉及到了算法的选择和性能的调优；最后，这份试题还夹进了SQL Server数据库的升级问题。因此，综上所述，我们估计这是一家从事程序外包工作的外企招聘后台开发或与后台开发相关的SQL Server高级程序员的试题。