某外企SQL Server面试题

核心提示： elsebeginSELECT 'We have publishers in New York'endStatement 2:if EXISTS ( select * from publishers where state = 'NY')beginSELEC

else

begin

SELECT 'We have publishers in New York'

end

Statement 2:

if EXISTS ( select * from publishers where state = 'NY')

begin

SELECT 'We have publishers in New York'

end

else

begin

SELECT 'Sales force needs to penetrate New York market'

end

Answer 7：不同点:执行时的事务数,处理时间,从客户端到服务器端传送的数据量大小

Question8: How can I list all California authors regardless of whether they have written a book?

In database pubs, have a table authors and titleauthor , table authors has a column state, and titleauhtor have books each author written.

CA behalf of california in table authors.

Answer 8：

select * from　authors where state='CA'

　　Question9: How can I get a list of the stores that have bought both 'bussiness' and 'mod_cook' type books?

In database pubs, use three table stores,sales and titles to implement this requestment.

Now I want to get the result as below:

　　stor_id stor_name　　　　　　　　　　　　　　　　
------- ----------------------------------------
...
7896　　Fricative Bookshop
...
...
...
Answer 9：
select distinct a.stor_id, a.stor_name from stores a,sales b,titles c
where a.stor_id=b.stor_id and b.title_id=c.title_id and c.type='business' and
exists(select 1 from sales k,titles g where stor_id=b.stor_id
and k.title_id=g.title_id and g.type='mod_cook')　　Question10: How can I list non-contignous data?