52563.htm

一本很基础的SQL讲解

<TD class=code bgColor=#e6e6e6><PRE>FirstName: Chris <BR>LastName: Kringle <BR>City: Henryville <BR>State: IN <BR>Zip: 47126 <BR>ProductID: 3 <BR>SalePrice: 205 <BR>SaleDate: 12/31/2005</PRE></TD></TR></TBODY></TABLE></P>
<P>答案见列表F：</P>
<P>
<TABLE cellSpacing=0 borderColorDark=#ffffff cellPadding=2 width=400 align=center borderColorLight=black border=1>
<TBODY>
<TR>
<TD class=code bgColor=#e6e6e6><PRE>INSERT INTO Customers(FirstName, LastName, City, State, Zip) <BR>VALUES('Chris', 'Kringle', 'Henryville', 'IN', '47126') <BR>INSERT INTO Sales(CustomerID, ProductID, SalePrice, SaleDate) <BR>VALUES(SCOPE_IDENTITY(), 3, 205, '12/31/2005')</PRE></TD></TR></TBODY></TABLE></P>
<P><STRONG>测试项目#6：</STRONG>从数据库中删除来自缅因洲(‘ME’)的客户，答案见列表G：</P>
<P>
<TABLE cellSpacing=0 borderColorDark=#ffffff cellPadding=2 width=400 align=center borderColorLight=black border=1>
<TBODY>
<TR>
<TD class=code bgColor=#e6e6e6><PRE>DELETE s <BR>FROM <BR>Sales s <BR>JOIN Customers c ON s.CustomerID = c.CustomerID <BR>WHERE <BR>c.State = 'ME' <BR>DELETE c <BR>FROM <BR>Customers c <BR>WHERE <BR>c.State = 'ME'</PRE></TD></TR></TBODY></TABLE></P>
<P><STRONG>测试项目#7：</STRONG>返回客户购买了两个或多个产品的平均售价和产品类别，答案见列表H：</P>
<P>
<TABLE cellSpacing=0 borderColorDark=#ffffff cellPadding=2 width=400 align=center borderColorLight=black border=1>
<TBODY>
<TR>
<TD class=code bgColor=#e6e6e6><PRE>SELECT <BR>p.Category, AVG(s.SalePrice) <BR>FROM <BR>Sales s <BR>INNER JOIN <BR>( <BR>SELECT s.CustomerID <BR>FROM <BR>Sales s <BR>GROUP BY s.CustomerID <BR>HAVING COUNT(CustomerID) &gt;= 2 <BR>) x ON s.CustomerID = x.CustomerID <BR>INNER JOIN Products p ON s.ProductID = p.ProductID <BR>GROUP BY p.Category</PRE></TD></TR></TBODY></TABLE></P>
<P>#p#</P>
<P><STRONG>测试项目#8：</STRONG>将销售在2005年6月10日到6月20日之间的产品的销售价格升级为建议售价，答案见列表I：</P>
<P>
<TABLE cellSpacing=0 borderColorDark=#ffffff cellPadding=2 width=400 align=center borderColorLight=black border=1>
<TBODY>
<TR>
<TD class=code bgColor=#e6e6e6><PRE>UPDATE s <BR>SET SalePrice = p.RecommendedPrice <BR>FROM <BR>Sales s <BR>INNER JOIN Products p ON s.ProductID = s.ProductID <BR>WHERE <BR>SaleDate &gt;= '6/10/2005' AND <BR>SaleDate &lt; '6/21/2005'</PRE></TD></TR></TBODY></TABLE></P>
<P><STRONG>测试项目#9：</STRONG>根据产品种类计算建议售价超过实际售价10元及以上的销售数量，答案见列表J：</P>
<P>
<TABLE cellSpacing=0 borderColorDark=#ffffff cellPadding=2 width=400 align=center borderColorLight=black border=1>
<TBODY>
<TR>
<TD class=code bgColor=#e6e6e6><PRE>SELECT <BR>p.Category, COUNT(*) AS NumberOfSales <BR>FROM <BR>Sales s <BR>INNER JOIN Products p ON s.ProductID = p.ProductID <BR>GROUP BY p.Category <BR>HAVING <BR>AVG(p.RecommendedPrice) &gt;= AVG(s.SalePrice)+10</PRE></TD></TR></TBODY></TABLE></P>
<P><STRONG>测试项目#10：</STRONG>不使用叠代构建，返回所由销售产品的销售日期，并按照该日期升序排列，答案见列表K：</P>
<P>
<TABLE cellSpacing=0 borderColorDark=#ffffff cellPadding=2 width=400 align=center borderColorLight=black border=1>
<TBODY>
<TR>
<TD class=code bgColor=#e6e6e6><PRE>SELECT <BR>s.SaleDate, <BR>s.SalePrice, <BR>( <BR>SELECT <BR>SUM(SalePrice) <BR>FROM <BR>Sales s2 <BR>WHERE <BR>s2.SaleDate &lt;= s.SaleDate <BR>) AS RunningTotal <BR>FROM <BR>Sales s <BR>ORDER BY <BR>s.SaleDate ASC</PRE></TD></TR></TBODY></TABLE></P>
<P><STRONG>评分</STRONG></P>
<P>我曾经使用类似的考题去考察很多应聘SQL Server数据库开发职位的人，但是迄今为止，只有2个人可以正确地回答出所有的问题。</P>
<P>平均分大约为50-60%，如果应聘者的表现高于这个平均分，那么我就认为他或她是一位优秀的T-SQL程序员，如果应聘者获得了90%以上的得分，那么他或她就是一位非常优异的程序员。</P>
<P>如果您对我的答案有任何问题或者想发表评论，请在文章的讨论区进行讨论。</P>
<P><FONT size=4>【相关文章】</FONT></P>
<UL type=disc>
<LI><A href="http://database.51cto.com/art/200707/50622.htm" target=_blank><U><FONT color=blue>SQL Server 2008对T-SQL语言的增强</FONT></U></A></LI></UL>
<UL type=disc>
<LI><A href="http://www.51cto.com/art/200511/12389.htm" target=_blank><U><FONT color=blue>SQL Server 2005中的T-SQL</FONT></U></A></LI></UL>
<UL type=disc>
<LI><A href="http://www.51cto.com/art/200510/8649.htm" target=_blank><U><FONT color=blue>T-SQL实用例句</FONT></U></A></LI></UL>
<DIV align=right>【责任编辑：<A class=ln href="mailto:[e-mail]">火凤凰</A> TEL：[phone]】 </DIV></td>
      </tr>
      <tr>
        <td class="d_font4">&nbsp;</td>
      </tr>
    </table>