某外企SQL Server面試題

--> Title  : 某外企SQL Server面試題
--> Author : wufeng4552
--> Date   : 2010-1-15
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))))
Question2：Can you use a SQL statement to calculating it!
How can I print "10 to 20" for books that sell for between $10 and $20，"unknown" for books whose price is null, and "other" for all other prices?
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
Question4：Can you create a cro