SQL Server 2005 笔试题

Question1
CanyouuseabatchSQLorstoreproceduretocalculatingtheNumberofDaysinaMonth?
Iamtryingtofindawaytocalculatethetotalnumberofdaysineachmonth.
Iamtryingtoavoidusingacasestatement,asitfailsforFebruaryonleapyears.
IsthereawaytodoitusingthedatefunctionsinSQLServer?
*/
--(1)
createprocsp_DayOfthisMonth(@yearint,@monthint)
as
begin
declare@startDatechar(10),@endDatechar(10)
set@startDate=cast(@yearaschar(4))+'-'+cast(@monthaschar(2))+'-1'
set@endDate=cast(@yearaschar(4))+'-'+cast(@month+1aschar(2))+'-1'
selectdatediff(day,cast(@startDateasdatetime),cast(@endDateasdatetime))
end

理解题意，也想到了使用DateDiff这样的日期处理函数。

但是由于传入的参数是整数而非日期类型，当传入参数错误时缺乏对异常的捕获和控制处理。
例如：
execsp_DayOfthisMonth2006,13
execsp_DayOfthisMonth2006,-1将会发生数据类型转换错误。

并且由于在计算下一月份时没有使用DateAdd函数，而是简单的让@month加1，
当传入月份参数为12时将会出错，也即该存储过程无法计算12月份的天数。
execsp_DayOfthisMonth2006,12

--(2)
createprocedurepro_monthcalucate(@monthint,@daysnumintoutput)
as
if((@month=1)or(@month=3)or(@month=5)or(@month=7)or(@month=8)or(@month=10)or(@month=12))
set@daysnum=31
else
if(@month=2)
set@daysnum=28
else
if((@month=2)or(@month=4)or(@month=6)or(@month=9)or(@month=11))
set@daysnum=30
else
begin
print'thewrongmonth'
set@daysnum=0
end

declare@numofmonthint
execpro_monthcalucate-2,@numofmonthoutput
print@numofmonth

思维不够严谨，只简单的想到月份，没有考虑到闰年的情况。
2004年的2月份是29天，而2006年的2月份只有28天。
对SQLServer的内置日期函数不熟悉，没有考虑到可以使用DateDiff函数求日期的差值。
编程缺乏技巧，长串的((@month=1)or(@month=3)or(@month=5)or(@month=7)or(@month=8)or(@month=10)or(@month=12))
可以写成@monthin(1,3,5,7,8,10,12)，代码简洁易读。

--穷举法
createprocsp_getMonthDays@yearint
as
selectdatediff(day,'January1,2006','February1,2006')as'January',
datediff(day,'February1,'+convert(varchar,@year),'March1,'+convert(varchar,@year))as'February',
datediff(day,'March1,2006','April1,2006')as'March',
datediff(day,'April1,2006','May1,2006')as'April',
datediff(day,'May1,2006','June1,2006')as'May',
datediff(day,'June1,2006','July1,2006')as'June',
datediff(day,'July1,2006','August1,2006')as'July',
datediff(day,'August1,2006','September1,2006')as'August',
datediff(day,'September1,2006','October1,2006')as'September',
datediff(day,'October1,2006','November1,2006')as'October',
datediff(day,'November1,2006','December1,2006')as'November',
datediff(day,'December1,2006','January1,2007')as'December'

--(3)
CREATEFUNCTIONfun_daysofmonth(@DATEdatetime)
RETURNSint
WITHEXECUTEASCALLER
AS
BEGIN
DECLARE@daysofmonthint
set@daysofmonth=datediff(day,@Date,dateadd(month,1,@Date))

RETURN(@daysofmonth)
END;

想到了使用DateDiff和DateAdd函数解题，但思维还不够严谨细致。
当下月天数比上月少时，会出错。
例如：selectdbo.fun_daysofmonth('2006-1-31')

参考答案：
createprocsp_getDaysOfMonth
@dateInputdatetime
as
declare@dateFirstDaydatetime
set@dateFirstDay=convert(varchar,year(@dateInput))+'-'+convert(varchar,month(@dateInput))+'-01'
selectmonth(@dateInput)as'Month',datediff(day,@dateFirstDay,dateadd(month,1,@dateFirstDay))as'Days'
go

execsp_getDaysOfMonth'2006-4-6'
/**//**//**//*Question2
CanyouuseaSQLstatementtocalculatingit?
HowcanIprint"10to20"forbooksthatsellforbetween$10and$20,
"unknown"forbookswhosepriceisnull,and"other"forallotherprices?
Youcanusethetabletitlesindatabasepubs.
*/
--(1)
selecttitle_id,title,
price=case
whenprice<20andprice>10then'10to20'
whenpriceisnullthen'unknown'
else'other'
end
fromdbo.titles
大多数的人都忽略了<=和>=。

参考答案：
selecttitle,'PriceCategory'=
case
whenpricebetween10and20then'10to20'
whenpriceisnullthen'unknown'
else'other'
end
fromtitles


/**//**//**//*Question3
CanyouuseaSQLstatementtofindingduplicatevalues?
HowcanIfindauthorswiththesamelastname?
Youcanusethetableauthorsindatabasepubs.Iwanttogettheresultasbelow:

Output:
au_lnamenumber_dups
---------------------------------------------------
Ringer2

(1row(s)affected)
*/

--(1)
selectau_lname,count(*)number_dups
fromauthors
groupbyau_lname
大多数人都只是取出au_lname出现的次数，而没有使用having子句选择出重复的au_lname。

--(2)
selectau_lname,number_dups
from(selectau_lname,number_dups=count(1)fromauthorsgroupbyau_lname)astmp
wherenumber_dups>=2
虽然答案是正确的，但明显没有掌握having子句的用法。


参考答案：
selectau_lname,count(*)as'number_dups'
fromauthors
groupbyau_lname
havingcount(*)>1


/**//**//**//*Question4
Canyoucreateacross-tabreportinmySQLServer?
HowcanIgetthereportaboutsalequantityforeachstoreandeachquarter
andthetotalsalequantityforeachquarteratyear1993?

Youcanusethetablesalesandstoresindatabasepubs.
TableSalesrecordallsaledetailitemforeachstore,columnstore_idistheidofeachstore,
ord_dateistheorderdateofeachsaleitem,andcolumnqtyisthesalequantity.
Tablestoresrecordallstoreinformation.

Iwanttogettheresultlooklikeasbelow:

Output:

stor_nameTotalQtr1Qtr2Qtr3Qtr4
---------------------------------------------------------------------------------------------
Barnum's5005000
Bookbeat55253000
Doc-U-Mat:QualityLaundryandBooks8508500
FricativeBookshop60350025
Total25060165025

*/

--(1)
droptable#stor_qty
selectisnull(stores.stor_name,'totle')asstor_name,isnull(datename(qq,sales.ord_date),0)asquater,sum(sales.qty)asqty
into#stor_qty
fromstores
joinsalesonstores.stor_id=sales.stor_id
whereyear(sales.ord_date)=1993
groupby(stores.stor_name),datename(qq,sales.ord_date)withcube

selectstor_name,isnull([0],0)as'totle',isnull([1],0)as'Qtr1',isnull([2],0)as'Qtr2',isnull([3],0)as'Qtr3',isnull([4],0)as'Qtr4',(isnull([1],0)+isnull([2],0)+isnull([3],0)+isnull([4],0))as'totle'
from
(selectstor_name,qty,quaterfrom#stor_qty
)aspro
pivot
(sum(qty)forquaterin([0],[1],[2],[3],[4]))aspvt

基本掌握了withcube和pivot的用法，并会使用，但技巧还有待提升，结果集多了一列total。
在这个查询中并不需要使用临时表来完成，用子查询就可以了。


参考答案：
--SQLServer2000生成交叉表
SELECTstor_name,
SUM(qty)AS'Total',
SUM(CASEdatepart(qq,ord_date)WHEN1THENqtyELSE0END)ASQtr1,
SUM(CASEdatepart(qq,ord_date)WHEN2THENqtyELSE0END)ASQtr2,
SUM(CASEdatepart(qq,ord_date)WHEN3THENqtyELSE0END)ASQtr3,
SUM(CASEdatepart(qq,ord_date)WHEN4THENqtyELSE0END)ASQtr4
FROMsalessINNERJOINstoresstONs.stor_id=st.stor_id
WHEREyear(ord_date)=1993
GROUPBYstor_name
Union
SELECT'Total',
SUM(qty)AS'Total',
SUM(CASEdatepart(qq,ord_date)WHEN1THENqtyELSE0END)ASQtr1,
SUM(CASEdatepart(qq,ord_date)WHEN2THENqtyELSE0END)ASQtr2,
SUM(CASEdatepart(qq,ord_date)WHEN3THENqtyELSE0END)ASQtr3,
SUM(CASEdatepart(qq,ord_date)WHEN4THENqtyELSE0END)ASQtr4
FROMsalessINNERJOINstoresstONs.stor_id=st.stor_id
WHEREyear(ord_date)=1993

--SQLServer2005生成交叉表
selectstor_name,isnull([0],0)as'Total',
isnull([1],0)as'Qtr1',isnull([2],0)as'Qtr2',
isnull([3],0)as'Qtr3',isnull([4],0)as'Qtr4'
from
(
selectisnull(t.stor_name,'Total')as'stor_name',
isnull(datepart(qq,ord_date),0)as'Qtr',sum(qty)as'qty'
fromsaless
joinstorestons.stor_id=t.stor_id
whereyear(s.ord_date)=1993
groupbydatepart(qq,ord_date),t.stor_namewithcube
)astmp
pivot
(
sum(qty)forQtrin([0],[1],[2],[3],[4])
)aspvt



/**//**//**//*Question5
HowcanIaddrownumberstomyresultset?

Indatabasepubs,haveatabletitles,nowIwanttheresultshownasbelow,
eachrowhavearownumber,howcanyoudothat?

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

--(1)

declare@tmptable
(
line_nointidentity,
title_idvarchar(6)
)
insertinto@tmp
selecttitle_idfromtitles
select*from@tmp


droptable#tmp
selectidentity(int,1,1)as'line-no',title_idinto#tmpfromtitles
orderbytitle_id
select*from#tmp



参考答案：
--SQLServer2000
selectcount(*)as'line-no',a.title_id
fromtitlesajointitlesbona.title_id>b.title_id
groupbya.title_id

--SQLServer2005
selectrow_number()over(orderbytitle_idasc)as'line-no',title_id
fromtitles




/**//**//**//*Question6
HowcanIlistallCaliforniaauthorsregardlessofwhethertheyhavewrittenabook?

Indatabasepubs,haveatableauthorsandtitleauthor,tableauthorshasacolumnstate,andtitleauhtorhavebookseach

authorwritten.

CAbehalfofcaliforniaintableauthors.
*/

--(1)
selectau_lname,au_fname,titau.*
fromauthorsau
jointitleauthortitauonau.au_id=titau.au_idandau.state='CA'
如果使用内连接，那么就只有在titleauthor表中有记录的作者才会被取出。

--(2)
selectau_id,au_lname,au_fnamefromauthorsa
wherestate='CA'
andexists(selectau_idfromtitleauthort
wherea.au_id=t.au_id)

selectau_lname,au_fname,state
fromdbo.authors
wherestate='CA'andau_idin
(
selectau_idfromdbo.titleauthor
)

与内连接类似。


参考答案：
selecta.au_fname,a.au_lname,b.title_id
fromauthorsa
leftouterjointitleauthorasbona.au_id=b.au_id
wherea.state='CA'


/**//**//**//*Question7
HowcanIgetalistofthestoresthathaveboughtboth'bussiness'and'mod_cook'typebooks?
Indatabasepubs,usethreetablestores,salesandtitlestoimplementthisrequestment.
NowIwanttogettheresultasbelow:

stor_idstor_name
-----------------------------------------------
...
7896FricativeBookshop
...
...
...
*/

--score:0
selectst.stor_id,st.stor_name
fromdbo.storesst
joindbo.salesslonst.stor_id=sl.stor_id
joindbo.titlestlonsl.title_id=tl.title_idand(tl.type='business'andtl.type='trad_cook')


--score:0
selectdistincta.stor_id,a.stor_name
fromstoresa
innerjoinsalesbon(a.stor_id=b.stor_id)
innerjointitlescon(b.title_id=c.title_idandc.type='bussiness'orc.type='mod_cook')

selectsal.stor_id,stor_name
fromsalessal
joinstoresstoonsal.stor_id=sto.stor_id
jointitlestilonsal.title_id=til.title_id
wheretil.[type]='bussiness'ortil.[type]='mod_cook'


selectdistinctstores.stor_id,stor_name
fromstores
innerjoinsalesonstores.stor_id=sales.stor_id
innerjointitlesonsales.title_id=titles.title_id
wheretypein('business','mod_cook')


--anotherone,score:0
begintranQuestion7

selectstor_id,stor_name
fromstores
wherestor_idin(
selectstor_id
fromsales
wheretitle_idin(
selecttitle_id
fromtitles
wheretype='business'ortype='mod_cook'
)
)

committranQuestion7


--score:6
withStor_TilteType(stor_id,stor_name,title_type)as
(selectst.stor_id,st.stor_name,ti.type
fromstoresst
joinsalessaonst.stor_id=sa.stor_id
jointitlestionsa.title_id=ti.title_id
)
selectdistinctstor_id,stor_name
fromStor_TilteType
wheretitle_type='business'
andstor_idin(selectstor_id
fromStor_TilteType
wheretitle_type='mod_cook'
)

--score:8
selectdistinctstores.stor_id,stor_name
fromstores
innerjoinsalesonstores.stor_id=sales.stor_id
innerjointitlesonsales.title_id=titles.title_id
andtitles.type='business'
intersect
selectdistinctstores.stor_id,stor_name
fromstores
innerjoinsalesonstores.stor_id=sales.stor_id
innerjointitlesonsales.title_id=titles.title_id
andtitles.type='mod_cook'

--score:10
selectdistinctst.stor_id,st.stor_name
fromstoresst
innerjoinsalesss1onss1.stor_id=st.stor_id
innerjointitlestit1onss1.title_id=tit1.title_id
wheretit1.[type]='business'andexists
(
select*
fromsalesss2
innerjointitlestit2onss2.title_id=tit2.title_id
wherest.stor_id=ss2.stor_idandtit2.[type]='mod_cook'
)


--参考答案
selects.stor_id,st.stor_name
fromsaless
jointitlestons.title_id=t.title_idandt.typein('mod_cook','business')
joinstoresstons.stor_id=st.stor_id
groupbys.stor_id,st.stor_name
havingcount(distinctt.type)=2

selectstor_id,stor_namefrom
stores
where
exists
(
select*from
salesjointitlesonsales.title_id=titles.title_id
wheretitles.[type]='business'
andstores.stor_id=sales.stor_id
)
and
exists
(
select*from
salesjointitlesonsales.title_id=titles.title_id
wheretitles.[type]='mod_cook'
andstores.stor_id=sales.stor_id
)

/**//**//**//*Question8
HowcanIlistnon-contignousdata?
Indatabasepubs,Icreateatabletestusingstatementasbelow,andIinsertseveralrowasbelow

createtabletest
(idintprimarykey)
go

insertintotestvalues(1)
insertintotestvalues(2)
insertintotestvalues(3)
insertintotestvalues(4)
insertintotestvalues(5)
insertintotestvalues(6)
insertintotestvalues(8)
insertintotestvalues(9)
insertintotestvalues(11)
insertintotestvalues(12)
insertintotestvalues(13)
insertintotestvalues(14)
insertintotestvalues(18)
insertintotestvalues(19)
go

NowIwanttolisttheresultofthenon-contignousrowasbelow,howcanIdoit?

MissingafterMissingbefore
---------------------------
68
911
...
*/

--2
declare@temptable(idint)
declare@countint
set@count=1
while@count<=20
begin
insertinto@tempvalues(@count)
set@count=@count+1
end
selectid-1as'Missingafter',id+1as'Missingbefore'
from(selectidfrom@temp
except
selectidfromtest
)ast

selectidas'Missingafter',id+2as'Missingbefore'
fromtestt1
wherenotexists
(
select*fromtestt2
wheret1.id=t2.id-1
)

--score:6
createprocproc_ShowMissing
as
declare@MissingInfotable
(
Missing_afterintprimarykey,
Missing_beforeint
)
declare@num_1int,@num_2int

declarecNumcursorFAST_FORWARD
for
selectidfromtest

opencNum
fetchcNuminto@num_1
fetchcNuminto@num_2
--printcast(@num_1asvarchar(10))+''''+cast(@num_2asvarchar(10))
while@@Fetch_Status=0
begin
if(@num_1+1)<>(@num_2)
begin
insertinto@MissingInfovalues(@num_1,@num_2)
end
else
print'contignous'
set@num_1=@num_2
fetchcNuminto@num_2
end
closecNum
deallocatecNum
select*from@MissingInfo

executeproc_ShowMissing

--
print('MissingafterMissingbefore')
print('---------------------------')
declare@iLastint,@iNextint,@iCurCountint

declarecTestCURSORFAST_FORWARD
forselectid
fromtest
opencTest
fetchcTestinto@iCurCount
set@iLast=@iCurCount
while@@Fetch_Status=0
begin
set@iNext=@iCurCount
if@iLast+1<>@iNextand@iLast<>@iNext
printcast(@iLastasvarchar)+''+cast(@iNextasvarchar)
set@iLast=@iNext
fetchcTestinto@iCurCount
end
closecTest
deallocatecTest

--score:10
selecttemp.[Missingafter],min(test.ID)as[Missingbefore]from
(
selectIDas[Missingafter]fromtest
whereID+1<(selectmin(ID)fromtestt2wheret2.ID>test.ID)
)astemp
jointestontemp.[Missingafter]<test.ID
groupbytemp.[Missingafter]

--score:10
selectt1.idas'Missingafter',t2.idas'Minssingbefore'from
(
selectrow_number()over(orderbyid)as'row_number',id
fromtest)ast1
join(selectrow_number()over(orderbyid)as'row_number',id
fromtest)ast2
ont1.row_number=t2.row_number-1
wheret1.id<>t2.id-1

--参考答案
selectmax(a.id)as"MissingAfter",
min(b.id)as"MissingBefore"
fromtesta,testb
wherea.id<b.id
groupbyb.id
havingmin(b.id)-max(a.id)>1


/**//**//**//*Question9
HowcanIlistallbookwithpricesgreatherthantheaveragepriceofbooksofthesametype?

Indatabasepubs,haveatablenamedtitles,itscolumnnamedpricemeanthepriceofthebook,
andanothernamedtypemeanthetypeofbooks.
NowIwanttogettheresultasbelow:

typetitleprice
---------------------------------------------------------------------------------------------
businessTheBusyExecutive'sDatabaseGuide19.9900
...
...
...
...
*/
--(1)
Selecttype,price,title
fromtitles
WherePrice>(selectavg(price)fromtitles)
这样取得的是所有书的平均价格，而不是某一类书的平均价格。

--(2)
withavgprice(type,price)as
(
selecttype,avg(price)asprice
fromtitlest2
groupbytype
)
selectdistincttitles.type,titles.title,titles.price
fromtitles
innerjoinavgpriceon(titles.type=avgprice.typeandtitles.price>avgprice.price)
使用CTE也可以实现，但如果取出的数据量很大会影响性能。
为什么要用distinct呢？

--(3)
selecta.type,a.title,a.price
fromtitlesa
wherea.price>(
selectavg(isnull(price,0))--有价格为null的情况，算平均值时应该算上价格为null的书本
fromtitlesb
wherea.type=b.type
)
因为没有具体提及统计的需求，但这样严谨的作风非常值得大家学习。





参考答案：
selectt.type,t.title,t.price
fromtitlest
wheret.price>
(selectavg(price)fromtitlesttwherett.type=t.type)