某外企SQL Server面试题（英文，答案）

Question 1：Can you use a batch SQL or store procedure to calculating the Number of Days in a Month

　　Answer 1：找出当月的天数
select datepart(dd,dateadd(dd,-1,dateadd(mm,1,cast(cast(year(getdate()) as varchar)+'-'+cast(month(getdate()) as varchar)+'-01' as datetime))))

 select datepart(dd,'2007-10-31')

--result:31

select dateadd(mm,1,'2007-10-01')

--result:2007-11-01 00:00:00.000

select dateadd(dd,-1,dateadd(mm,1,'2007-10-01'))

--result:2007-10-31 00:00:00.000

select datepart(dd,dateadd(dd,-1,dateadd(mm,1,'2007-10-01')))

--result:31

Answer 2：
　　select bookid,bookname,price=case when price is null then 　　'unknown'
when  price between 10 and 20 then '10 to 20' else price end
from books

　　Question3：Can you use a SQL statement to finding duplicate 　　values!
　　How can I find authors with the same last name?
You can use the table authors in datatabase pubs. I want to get the result as below:
Output:
au_lname   number_dups
---------------------------------------- -----------
Ringer  2
(1 row(s) affected)

　　Answer 3
　　select au_lname,number_dups=count(1) from authors group by au_lname

 

Question6: How can I add row numbers to my result set?
In database pubs, have a table titles , now I want the result shown as below,each row have a row number, how can you do that?
Result:

line-no  title_id ----------- -------- 1  BU1032 2  BU1111 3  BU2075 4  BU7832 5  MC2222 6  MC3021 7  MC3026 8  PC1035 9  PC8888 10 PC9999 11 PS1372 12 PS2091 13 PS2106 14 PS3333 15 PS7777 16 TC3218 17 TC4203 18 TC7777

　　Answer 6：
　　--SQL 2005的写法
　　select row_number() as line_no ,title_id from titles
　　--SQL 2000的写法
　　select identity(int,1,1)as line_no,title_id into #t from titles
　　select * from #t
　　drop table #t