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

转载 2007年10月01日 10:28:00

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

相关文章推荐

某外企SQL Server面试题

某外企SQL Server面试题

sql server 2000 试题汇编答案

  • 2012-12-17 20:07
  • 1.99MB
  • 下载

SQL server 试题汇编2000答案

  • 2016-11-27 13:20
  • 1.31MB
  • 下载

sql server面试题实例

A.输出其中第3,4行: 名字倒序: select top 2 reverse(name) from t where id not in (select top 2 id from t order b...

[20111123] IBM Java英文面试题-附参考答案

1.what is oracle. 2.what is major differenece oracle8i and oracle9i. 4.tell me some thing urself. ...

[SQL Server] 提取数字、提取英文、提取中文

--SQL 判断字段值是否有中文  create  function  fun_getCN(@str  nvarchar(4000))      re...

安装sql server 2005后不能连接服务器,没有studio express以及安装后是英文界面解决办法。

我自己重装了六次sql server 2005,算是经验人士了,网上资料一大堆,都他妈错的! 如何安装sql server 2005,安装sql server 2005后没有SQL Server m...
  • sabic
  • sabic
  • 2012-03-20 17:04
  • 4285

sql server 的增删改查小测试及答案(这对于初学者来说是一件难得的宝物)

create table student( sid varchar(8) primary key, sname varchar(16) not null, sex varcha...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

(最多只允许输入30个字)