/**//* Question 1 Can you use a batch SQL or store procedure to calculating the Number of Days in a Month? I am trying to find a way to calculate the total number of days in each month. I am trying to avoid using a case statement, as it fails for February on leap years. Is there a way to do it using the date functions in SQL Server? */ --(1) createproc sp_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这样的日期处理函数。 但是由于传入的参数是整数而非日期类型,当传入参数错误时缺乏对异常的捕获和控制处理。 例如: exec sp_DayOfthisMonth 2006, 13 exec sp_DayOfthisMonth 2006, -1 将会发生数据类型转换错误。 并且由于在计算下一月份时没有使用DateAdd函数,而是简单的让@month加1, 当传入月份参数为12时将会出错,也即该存储过程无法计算12月份的天数。 exec sp_DayOfthisMonth 2006, 12 --(2) createprocedure pro_monthcalucate(@monthint,@daysnumint output) 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'the wrong month' set@daysnum=0 end declare@numofmonthint exec pro_monthcalucate -2,@numofmonth output print@numofmonth 思维不够严谨,只简单的想到月份,没有考虑到闰年的情况。 2004年的2月份是29天,而2006年的2月份只有28天。 对SQL Server的内置日期函数不熟悉,没有考虑到可以使用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),代码简洁易读。 -- 穷举法 createproc sp_getMonthDays @yearint as selectdatediff(day,'January 1,2006','February 1,2006') as'January', datediff(day,'February 1,'+convert(varchar,@year),'March 1,'+convert(varchar,@year)) as'February', datediff(day,'March 1,2006','April 1,2006') as'March', datediff(day,'April 1,2006','May 1,2006') as'April', datediff(day,'May 1,2006','June 1,2006') as'May', datediff(day,'June 1,2006','July 1,2006') as'June', datediff(day,'July 1,2006','August 1,2006') as'July', datediff(day,'August 1,2006','September 1,2006') as'August', datediff(day,'September 1,2006','October 1,2006') as'September', datediff(day,'October 1,2006','November 1,2006') as'October', datediff(day,'November 1,2006','December 1,2006') as'November', datediff(day,'December 1,2006','January 1,2007') as'December' --(3) CREATEFUNCTION fun_daysofmonth (@DATEdatetime) RETURNSint WITHEXECUTEAS CALLER AS BEGIN DECLARE@daysofmonthint set@daysofmonth=datediff(day,@Date,dateadd(month,1,@Date)) RETURN(@daysofmonth) END; 想到了使用DateDiff和DateAdd函数解题,但思维还不够严谨细致。 当下月天数比上月少时,会出错。 例如: select dbo.fun_daysofmonth('2006-1-31') 参考答案: createproc sp_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 exec sp_getDaysOfMonth '2006-4-6' /**//* Question 2 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? You can use the table titles in database pubs. */ --(1) select title_id,title, price =case when price <20and price >10then'10 to 20' when price isnullthen'unknown' else'other' end from dbo.titles 大多数的人都忽略了<=和>=。 参考答案: select title, 'Price Category'= case when price between10and20then'10 to 20' when price isnullthen'unknown' else'other' end from titles /**//* Question 3 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 database pubs. I want to get the result as below: Output: au_lname number_dups ---------------------------------------- ----------- Ringer 2 (1 row(s) affected) */ --(1) select au_lname, count(*) number_dups from authors groupby au_lname 大多数人都只是取出au_lname出现的次数,而没有使用having子句选择出重复的au_lname。 --(2) select au_lname,number_dups from (select au_lname,number_dups=count(1) from authors groupby au_lname) as tmp where number_dups>=2 虽然答案是正确的,但明显没有掌握having子句的用法。 参考答案: select au_lname, count(*) as'number_dups' from authors groupby au_lname havingcount(*) >1 /**//* Question 4 Can you create a cross-tab report in my SQL Server? How can I get the report about sale quantity for each store and each quarter and the total sale quantity for each quarter at year 1993? You can use the table sales and stores in database pubs. Table Sales record all sale detail item for each store, column store_id is the id of each store, ord_date is the order date of each sale item, and column qty is the sale quantity. Table stores record all store information. I want to get the result look like as below: Output: stor_name Total Qtr1 Qtr2 Qtr3 Qtr4 ---------------------------------------- ----------- ----------- ------------------------------- Barnum's 50 0 50 0 0 Bookbeat 55 25 30 0 0 Doc-U-Mat: Quality Laundry and Books 85 0 85 0 0 Fricative Bookshop 60 35 0 0 25 Total 250 60 165 0 25 */ --(1) droptable #stor_qty selectisnull(stores.stor_name,'totle') as stor_name,isnull(datename(qq,sales.ord_date),0) as quater,sum(sales.qty) as qty into #stor_qty from stores join sales on stores.stor_id=sales.stor_id whereyear(sales.ord_date)=1993 groupby (stores.stor_name),datename(qq,sales.ord_date) with cube select stor_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 (select stor_name,qty,quater from #stor_qty ) as pro pivot (sum(qty) for quater in([0],[1],[2],[3],[4])) as pvt 基本掌握了with cube和pivot的用法,并会使用,但技巧还有待提升,结果集多了一列total。 在这个查询中并不需要使用临时表来完成,用子查询就可以了。 参考答案: --SQL Server 2000生成交叉表 SELECT stor_name, SUM(qty) AS'Total', SUM(CASEdatepart(qq, ord_date) WHEN1THEN qty ELSE0END) AS Qtr1, SUM(CASEdatepart(qq, ord_date) WHEN2THEN qty ELSE0END) AS Qtr2, SUM(CASEdatepart(qq, ord_date) WHEN3THEN qty ELSE0END) AS Qtr3, SUM(CASEdatepart(qq, ord_date) WHEN4THEN qty ELSE0END) AS Qtr4 FROM sales s INNERJOIN stores st ON s.stor_id = st.stor_id WHEREyear(ord_date) =1993 GROUPBY stor_name Union SELECT'Total', SUM(qty) AS'Total', SUM(CASEdatepart(qq, ord_date) WHEN1THEN qty ELSE0END) AS Qtr1, SUM(CASEdatepart(qq, ord_date) WHEN2THEN qty ELSE0END) AS Qtr2, SUM(CASEdatepart(qq, ord_date) WHEN3THEN qty ELSE0END) AS Qtr3, SUM(CASEdatepart(qq, ord_date) WHEN4THEN qty ELSE0END) AS Qtr4 FROM sales s INNERJOIN stores st ON s.stor_id = st.stor_id WHEREyear(ord_date) =1993 --SQL Server 2005生成交叉表 select stor_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' from sales s join stores t on s.stor_id = t.stor_id whereyear(s.ord_date) =1993 groupbydatepart(qq, ord_date), t.stor_name with cube ) as tmp pivot ( sum(qty) for Qtr in ([0], [1], [2], [3], [4]) ) as pvt /**//* Question 5 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 */ --(1) declare@tmptable ( line_no intidentity, title_id varchar(6) ) insertinto@tmp select title_id from titles select*from@tmp droptable #tmp selectidentity(int,1,1) as'line-no',title_id into #tmp from titles orderby title_id select*from #tmp 参考答案: --SQL Server 2000 selectcount(*) as'line-no',a.title_id from titles a join titles b on a.title_id > b.title_id groupby a.title_id --SQL Server 2005 select row_number() over (orderby title_id asc) as'line-no', title_id from titles /**//* Question 6 How can I list all California authors regardless of whether they have written a book? In database pubs, have a table authors and titleauthor , table authors has a column state, and titleauhtor have books each author written. CA behalf of california in table authors. */ --(1) select au_lname,au_fname,titau.* from authors au join titleauthor titau on au.au_id = titau.au_id and au.state ='CA' 如果使用内连接,那么就只有在titleauthor表中有记录的作者才会被取出。 --(2) select au_id,au_lname,au_fname from authors a where state='CA' andexists(select au_id from titleauthor t where a.au_id=t.au_id) select au_lname,au_fname,state from dbo.authors where state='CA'and au_id in ( select au_id from dbo.titleauthor ) 与内连接类似。 参考答案: select a.au_fname ,a.au_lname, b.title_id from authors a leftouterjoin titleauthor as b on a.au_id = b.au_id where a.state ='CA' /**//* Question 7 How can I get a list of the stores that have bought both 'bussiness' and 'mod_cook' type books? In database pubs, use three table stores,sales and titles to implement this requestment. Now I want to get the result as below: stor_id stor_name ------- ---------------------------------------- ... 7896 Fricative Bookshop ... ... ... */ -- score : 0 select st.stor_id,st.stor_name from dbo.stores st join dbo.sales sl on st.stor_id = sl.stor_id join dbo.titles tl on sl.title_id = tl.title_id and (tl.type ='business'and tl.type ='trad_cook') -- score : 0 selectdistinct a.stor_id , a.stor_name from stores a innerjoin sales b on(a.stor_id = b.stor_id) innerjoin titles c on(b.title_id = c.title_id and c.type ='bussiness'or c.type ='mod_cook') select sal.stor_id, stor_name from sales sal join stores sto on sal.stor_id = sto.stor_id join titles til on sal.title_id = til.title_id where til.[type]='bussiness'or til.[type]='mod_cook' selectdistinct stores.stor_id,stor_name from stores innerjoin sales on stores.stor_id = sales.stor_id innerjoin titles on sales.title_id = titles.title_id where type in ('business','mod_cook') -- another one , score: 0 begintran Question7 select stor_id, stor_name from stores where stor_id in ( select stor_id from sales where title_id in ( select title_id from titles where type ='business'or type ='mod_cook' ) ) committran Question7 -- score: 6 with Stor_TilteType(stor_id,stor_name,title_type) as (select st.stor_id,st.stor_name,ti.type from stores st join sales sa on st.stor_id=sa.stor_id join titles ti on sa.title_id=ti.title_id ) selectdistinct stor_id,stor_name from Stor_TilteType where title_type='business' and stor_id in(select stor_id from Stor_TilteType where title_type='mod_cook' ) -- score: 8 selectdistinct stores.stor_id, stor_name from stores innerjoin sales on stores.stor_id = sales.stor_id innerjoin titles on sales.title_id = titles.title_id and titles.type ='business' intersect selectdistinct stores.stor_id, stor_name from stores innerjoin sales on stores.stor_id = sales.stor_id innerjoin titles on sales.title_id = titles.title_id and titles.type ='mod_cook' -- score: 10 selectdistinct st.stor_id,st.stor_name from stores st innerjoin sales ss1 on ss1.stor_id = st.stor_id innerjoin titles tit1 on ss1.title_id = tit1.title_id where tit1.[type]='business'andexists ( select* from sales ss2 innerjoin titles tit2 on ss2.title_id = tit2.title_id where st.stor_id = ss2.stor_id and tit2.[type]='mod_cook' ) -- 参考答案 select s.stor_id, st.stor_name from sales s join titles t on s.title_id = t.title_id and t.type in ('mod_cook', 'business') join stores st on s.stor_id = st.stor_id groupby s.stor_id, st.stor_name havingcount(distinct t.type) =2 select stor_id, stor_name from stores where exists ( select*from sales join titles on sales.title_id = titles.title_id where titles.[type]='business' and stores.stor_id = sales.stor_id ) and exists ( select*from sales join titles on sales.title_id = titles.title_id where titles.[type]='mod_cook' and stores.stor_id = sales.stor_id ) /**//* Question 8 How can I list non-contignous data? In database pubs, I create a table test using statement as below, and I insert several row as below create table test ( id int primary key ) go insert into test values (1 ) insert into test values (2 ) insert into test values (3 ) insert into test values (4 ) insert into test values (5 ) insert into test values (6 ) insert into test values (8 ) insert into test values (9 ) insert into test values (11) insert into test values (12) insert into test values (13) insert into test values (14) insert into test values (18) insert into test values (19) go Now I want to list the result of the non-contignous row as below,how can I do it? Missing after Missing before ------------- -------------- 6 8 9 11 ... */ -- 2 declare@temptable (id int) declare@countint set@count=1 while@count<=20 begin insertinto@tempvalues(@count) set@count=@count+1 end select id-1as'Missing after',id+1as'Missing before' from(select id from@temp except select id from test ) as t select id as'Missing after', id+2as'Missing before ' from test t1 wherenotexists ( select*from test t2 where t1.id = t2.id-1 ) -- score: 6 createproc proc_ShowMissing as declare@MissingInfotable ( Missing_after intprimarykey, Missing_before int ) declare@num_1int, @num_2int declare cNum cursor FAST_FORWARD for select id from test open cNum fetch cNum into@num_1 fetch cNum into@num_2 -- print cast(@num_1 as varchar(10)) + '''' + cast(@num_2 as varchar(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 fetch cNum into@num_2 end close cNum deallocate cNum select*from@MissingInfo execute proc_ShowMissing -- print('Missing after Missing before') print('------------- -------------- ') declare@iLastint,@iNextint,@iCurCountint declare cTest CURSOR FAST_FORWARD forselect id from test open cTest fetch cTest into@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 fetch cTest into@iCurCount end close cTest deallocate cTest -- score:10 selecttemp.[Missing after],min(test.ID) as[Missing before]from ( select ID as[Missing after]from test where ID +1< (selectmin(ID) from test t2 where t2.ID > test.ID) ) astemp join test ontemp.[Missing after]< test.ID groupbytemp.[Missing after] -- score: 10 select t1.id as'Missing after',t2.id as'Minssing before'from ( select row_number() over (orderby id) as'row_number',id from test)as t1 join (select row_number() over (orderby id) as'row_number',id from test) as t2 on t1.row_number=t2.row_number-1 where t1.id<>t2.id-1 -- 参考答案 selectmax(a.id) as "Missing After", min(b.id) as "Missing Before" from test a ,test b where a.id<b.id groupby b.id havingmin(b.id)-max(a.id)>1 /**//* Question 9 How can I list all book with prices greather than the average price of books of the same type? In database pubs, have a table named titles , its column named price mean the price of the book, and another named type mean the type of books. Now I want to get the result as below: type title price ------------ --------------------------------------------------------------------------------- business The Busy Executive's Database Guide 19.9900 ... ... ... ... */ --(1) Select type,price,title from titles Where Price > (selectavg(price) from titles) 这样取得的是所有书的平均价格,而不是某一类书的平均价格。 --(2) with avgprice(type,price) as ( select type,avg(price) as price from titles t2 groupby type ) selectdistinct titles.type,titles.title,titles.price from titles innerjoin avgprice on (titles.type = avgprice.type and titles.price> avgprice.price) 使用CTE也可以实现,但如果取出的数据量很大会影响性能。 为什么要用distinct呢? --(3) select a.type, a.title, a.price from titles a where a.price > ( selectavg (isnull (price, 0)) -- 有价格为null的情况,算平均值时应该算上价格为null的书本 from titles b where a.type = b.type ) 因为没有具体提及统计的需求,但这样严谨的作风非常值得大家学习。 参考答案: select t.type, t.title, t.price from titles t where t.price > (selectavg(price) from titles tt where tt.type = t.type)
/**//* Question 1 Can you use a batch SQL or store procedure to calculating the Number of Days in a Month?I am trying to find a way to calculate the total number of days in each month. I am tryin