关闭

某外企SQL Server面试题(英文,答案)

752人阅读 评论(0) 收藏 举报

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

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:9341次
    • 积分:162
    • 等级:
    • 排名:千里之外
    • 原创:2篇
    • 转载:11篇
    • 译文:0篇
    • 评论:3条
    文章分类
    文章存档
    最新评论