某外企SQL Server面试题

核心提示：Question 1：Can you use a batch SQL or store procedure to calculating the Number of Days in a MonthAnswer 1：找出当月的天数select datepart(dd,dateadd(dd,-1,dateadd(mm,1,

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 cross-tab report in my SQL Server!

How can I get the report about sale quality for each store and each quarter and the total sale quality for each quarter at year 1993?