.Net開發工程師筆試試題

附上自己做的答案，提出不足之處

現在有一個SQL Server 2000版本的數據庫，里面包含有三個表Info、InfoReply、User，分別表示信息、信息評論和用戶表，包含的字段分別如下：

Info：

InfoReply：

User

三個表的關系可以通過下列模型圖來描述：

現有下列問題：

（1）   請說明在上述模型圖中，PK和FK1、FK2的含義？

  答：pk是主鍵 fk1、fk2分別是外面

  表infoReply有倆個外鍵fk1,fk2

  表info有一個外鍵pk1

（2）   現在有一個查詢，需要返回一個最新發布的20條信息列表，要求包含：信息ID、信息標題、信息發布時間、信息發布人姓名、信息評論總數和最后評論時間，并且按最后評論時間排序，請給出該查詢的SQL語句？

Select top 20 i.InfoID,i.InfoTItle,i.InfoPubDate,u.userName,

count(ir.infoID)as ReplyCount   --信息評論總數總數

ir.infoReplyDate

from Info i

inner join User u on i.infoPubuser =u.userNo

inner join InfoReply ir on ir.infoID=i.inforID

group by i.InfoID,i.InfoTItle,i.InfoPubDate,u.userName

order by ir.inforReplyUser desc

（3）   仍是第（2）中的查詢要求，現在假設Info表和User表之間不存在任何外鍵約束，即infoPubUser表示的用戶帳號可能在User表中已經被刪除，這時如果要繼續返回最新的20條信息列表，包含（2）中相同的列，該SQL語句應該怎么寫？

Select top 20 i.InfoID,i.InfoTItle,i.InfoPubDate,u.userName,

count(ir.infoID)as ReplyCount   --信息評論總數總數

ir.infoReplyDate

from Info i

left join User u on i.infoPubuser =u.userNo

left join InfoReply ir on ir.infoID=i.inforID

group by i.InfoID,i.InfoTItle,i.InfoPubDate,u.userName

order by ir.inforReplyUser desc

（4）   假設Info表有一個字段infoReplyCount（信息評論總數），那我們需要怎么做才能讓用戶每增加一條評論，該字段自動+1，說出大概思路即刻，能寫出SQL語句最好

在表Info創建一個觸發器，原理：當評論表數據插入之后（after關鍵字）就會觸發下面update語句

Create trigger tri_addReply

On InfoReply

After insert

As

Beigin

    Declare @infoID int  --定義一個變量

Select @infoID=infoID from inserted –從插入表選出infoID

Update infoReply set infoReplyCount=infoReplyCount+1 where infoID=@infoID

end

（5）   寫出返回每一條信息的評論總數的SQL語句

   Select i.infoID,count(ir.infoID)

From Info inner join InfoReply ir on i.infoID=ir.infoID

Group by i.infoID, ir.infoID

（6）   假設發現Info表有多條記錄丟失了，如何最大程度地找出有多少條記錄丟失了，寫出SQL語句。（不使用InfoReply表）

       Select count(*) from Info --選出表中的總數

       Select max(infoID) from Info   --選出表中最大infoID

       之后將兩個結果相減得到多少丟失記錄

（7）   假設發現Info表只有1條記錄丟失了，如何知道丟失的記錄的ID，寫出SQL語句（不使用InfoReply表）

首先做個小判斷，這個丟失的一條記錄不可能是最后一條記錄

select rownum from

(select row_number()over (order by id desc) as rownum from Info)—采用sql的分頁函數

as a

where rownum not in(select id from Info)

（8）   仍然是（6）的問題，可以使用InfoReply表，寫出SQL語句。

          Select count（infoID）from  --此語句統計多少條記錄丟失

 (Select infoID from InfoReply

where infoID not in

（select infoID from Info）) as a   --查找出多少條丟失記錄放到a表中

（9）   假設Info表有一個字段infoReplyCount（信息評論總數），infoReplyCount>=20的為熱門帖子，小于20的為非熱門帖子，請根據Info表的內容輸出以下2行數據，要求SQL語句最簡。

Select infoReplyCount as 數量,

(

    Case

When infoReplyCount>=20 then N‘熱門’

Else N‘非熱門’

End

) as 類別

From Info