.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 onir.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 20i.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 onir.infoID=i.inforID

group byi.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 setinfoReplyCount=infoReplyCount+1 where infoID=@infoID

end

（5）   写出返回每一条信息的评论总数的SQL语句

   Select i.infoID,count(ir.infoID)

From Info inner joinInfoReply ir on i.infoID=ir.infoID

Group by i.infoID,ir.infoID

（6）   假设发现Info表有多条记录丢失了，如何最大程度地找出有多少条记录丢失了，写出SQL语句。（不使用InfoReply表）

       Select count(*) fromInfo --选出表中的总数

       Select max(infoID) fromInfo   --选出表中最大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