SQL Server 2005 考试（包括答案）

 

/**/ /* 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)    
create   proc  sp_DayOfthisMonth ( @year   int  ,  @month   int )
as
begin
  declare   @startDate   char ( 10 ), @endDate   char ( 10 )
  set   @startDate = cast ( @year   as   char ( 4 )) + ' - ' + cast ( @month   as   char ( 2 )) + ' -1 '
  set   @endDate = cast ( @year   as   char ( 4 )) + ' - ' + cast ( @month + 1   as   char ( 2 )) + ' -1 '
  select   datediff ( day , cast ( @startDate   as   datetime ), cast ( @endDate   as   datetime ))
end

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

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

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

-- (2)
create   procedure  pro_monthcalucate( @month   int , @daysnum   int  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   @numofmonth   int
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 ))
可以写成  @month   in  ( 1 ,  3 ,  5 ,  7 ,  8 ,  10 ,  12 )，代码简洁易读。

--  穷举法
create   proc  sp_getMonthDays  @year   int
as
select   datediff ( 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)
CREATE   FUNCTION  fun_daysofmonth ( @DATE   datetime )
RETURNS   int
WITH   EXECUTE   AS  CALLER
AS
BEGIN
      DECLARE   @daysofmonth   int
      set   @daysofmonth =   datediff ( day , @Date , dateadd ( month , 1 , @Date ))

      RETURN ( @daysofmonth )
END ;

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

参考答案：
create   proc  sp_getDaysOfMonth
     @dateInput   datetime
as
     declare   @dateFirstDay   datetime  
     set   @dateFirstDay   =   convert ( varchar ,  year ( @dateInput ))  +   ' - '   +   convert ( varchar ,  month ( @dateInput ))  +   ' -01 '          
     select   month ( @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  <   20   and  price  > 10   then   ' 10 to 20 '
              when   price  is   null   then   ' unknown '
              else   ' other '
              end
from  dbo.titles
大多数的人都忽略了 <= 和 >= 。

参考答案：
select  title,  ' Price Category '   =
         case  
             when  price  between   10   and   20   then   ' 10 to 20 '
             when  price  is   null   then   ' 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
group   by  au_lname
大多数人都只是取出au_lname出现的次数，而没有使用having子句选择出重复的au_lname。

-- (2)
select  au_lname,number_dups 
from  ( select  au_lname,number_dups = count ( 1 )  from  authors  group   by  au_lname)  as  tmp
where   number_dups >= 2
虽然答案是正确的，但明显没有掌握having子句的用法。


参考答案：
select  au_lname,  count ( * )  as   ' number_dups '  
from  authors
group   by  au_lname
having   count ( * )  >   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)
drop   table  #stor_qty
select   isnull (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
where   year (sales.ord_date) = 1993
group   by  (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 ( CASE   datepart (qq, ord_date)  WHEN   1   THEN  qty  ELSE   0    END )   AS  Qtr1,
SUM ( CASE   datepart (qq, ord_date)  WHEN   2   THEN  qty  ELSE   0    END )   AS  Qtr2,
SUM ( CASE   datepart (qq, ord_date)  WHEN   3   THEN  qty  ELSE   0    END )   AS  Qtr3,
SUM ( CASE   datepart (qq, ord_date)  WHEN   4   THEN  qty  ELSE   0    END )   AS  Qtr4
FROM  sales s  INNER   JOIN  stores st  ON  s.stor_id  =  st.stor_id
WHERE   year (ord_date)  =   1993
GROUP   BY  stor_name
Union  
SELECT   ' Total ' ,
SUM (qty)  AS   ' Total ' ,
SUM ( CASE   datepart (qq, ord_date)  WHEN   1   THEN  qty  ELSE   0    END )   AS  Qtr1,
SUM ( CASE   datepart (qq, ord_date)  WHEN   2   THEN  qty  ELSE   0    END )   AS  Qtr2,
SUM ( CASE   datepart (qq, ord_date)  WHEN   3   THEN  qty  ELSE   0    END )   AS  Qtr3,
SUM ( CASE   datepart (qq, ord_date)  WHEN   4   THEN  qty  ELSE   0    END )   AS  Qtr4
FROM  sales s  INNER   JOIN  stores st  ON  s.stor_id  =  st.stor_id
WHERE   year (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
(
     select   isnull (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
     where   year (s.ord_date)  =   1993
     group   by   datepart (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   @tmp   table  
(
line_no  int   identity ,
title_id  varchar ( 6 )
)
insert   into   @tmp  
select   title_id    from  titles
select   *   from   @tmp


drop   table  #tmp
select   identity ( int , 1 , 1 )  as   ' line-no ' ,title_id  into  #tmp  from  titles
order   by  title_id
select   *   from  #tmp



参考答案：
-- SQL Server 2000
select   count ( * )  as   ' line-no ' ,a.title_id 
from  titles a  join  titles b  on  a.title_id  >  b.title_id 
group   by  a.title_id

-- SQL Server 2005
select  row_number()  over  ( order   by  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 '
and   exists ( 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 
left   outer   join  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
select   distinct  a.stor_id , a.stor_name
from  stores a
inner   join  sales b  on (a.stor_id  =  b.stor_id)
inner   join  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 '


select   distinct  stores.stor_id,stor_name
from  stores
inner   join  sales  on  stores.stor_id  =  sales.stor_id
inner   join  titles  on  sales.title_id  =  titles.title_id
where  type  in  ( ' business ' , ' mod_cook ' ) 


--  another one , score: 0
begin   tran  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 '
    )
)

commit   tran  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 
)
select   distinct  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
select   distinct  stores.stor_id, stor_name
from  stores 
inner   join  sales  on  stores.stor_id  =  sales.stor_id
inner   join  titles  on  sales.title_id  =  titles.title_id
and  titles.type  =   ' business '  
intersect
select   distinct  stores.stor_id, stor_name
from  stores 
inner   join  sales  on  stores.stor_id  =  sales.stor_id
inner   join  titles  on  sales.title_id  =  titles.title_id
and  titles.type  =   ' mod_cook '  
 
--  score: 10
select   distinct  st.stor_id,st.stor_name
from  stores st
inner   join  sales ss1  on  ss1.stor_id  =  st.stor_id
inner   join  titles tit1  on  ss1.title_id  =  tit1.title_id 
where  tit1. [ type ]   = ' business '   and   exists
(
  select   *
  from  sales ss2
  inner   join  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
group   by  s.stor_id, st.stor_name
having   count ( 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   @temp   table  (id  int )
declare   @count   int
set   @count = 1
while   @count <= 20
  begin
    insert   into   @temp   values ( @count )
    set   @count = @count + 1
  end  
select  id - 1   as   ' Missing after ' ,id + 1   as   ' Missing before '
from ( select  id  from   @temp
      except
      select  id  from  test
     )  as  t

select  id  as   ' Missing after ' , id + 2   as   ' Missing before  '
from  test t1
where   not   exists
(
select   *   from  test t2
where  t1.id  =  t2.id - 1
)

--  score: 6
create   proc  proc_ShowMissing
as
declare   @MissingInfo   table
(
    Missing_after  int   primary   key ,
    Missing_before  int
)
declare   @num_1   int ,  @num_2   int

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
                 insert   into   @MissingInfo   values  ( @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   @iLast   int , @iNext   int , @iCurCount   int

declare  cTest  CURSOR  FAST_FORWARD
   for   select  id
       from  test
   open  cTest
   fetch  cTest  into   @iCurCount
   set    @iLast   =   @iCurCount
   while   @@Fetch_Status   =   0
      begin
        set   @iNext   =   @iCurCount
        if   @iLast + 1   <>   @iNext   and   @iLast   <>   @iNext
           print   cast ( @iLast   as   varchar ) + '     ' + cast ( @iNext   as   varchar )
        set   @iLast   =   @iNext  
        fetch  cTest  into   @iCurCount
      end
   close  cTest
   deallocate  cTest

--  score:10
select   temp . [ Missing after ] , min (test.ID)  as   [ Missing before ]    from
(
select  ID  as   [ Missing after ]    from  test
where  ID  +   1   <  ( select    min (ID)  from  test t2  where  t2.ID  >  test.ID)
)  as   temp
join  test  on   temp . [ Missing after ]   <  test.ID
group   by   temp . [ Missing after ]

--  score: 10
select  t1.id  as   ' Missing after ' ,t2.id  as   ' Minssing before '   from
(
select  row_number()  over  ( order   by  id)  as   ' row_number ' ,id
from  test) as  t1
join  ( select  row_number()  over  ( order   by  id)  as   ' row_number ' ,id
from  test)  as  t2
on  t1.row_number = t2.row_number - 1
where  t1.id <> t2.id - 1

--  参考答案
select   max (a.id)  as  "Missing After",
        min (b.id)  as  "Missing Before" 
from  test a ,test b 
where  a.id < b.id 
group   by  b.id 
having   min (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  >  ( select   avg (price)  from  titles)
这样取得的是所有书的平均价格，而不是某一类书的平均价格。

-- (2)
with  avgprice(type,price)  as
(
  select  type, avg (price)  as  price
  from  titles t2
  group   by  type
)
select   distinct  titles.type,titles.title,titles.price
from  titles
inner   join  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  >  (
     select   avg  ( 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  >
( select   avg (price)  from  titles tt  where  tt.type  =  t.type)