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)