Common SQL Scripts

1.查询表中第n到第m行的数据(m>n)[ID为Identity Column & Primary Key]
  <a>  SELECT *
           FROM (SELECT * ,ROW_NUMBER() OVER(ORDER BY ID ASC) as RowNumber 
                          FROM TableName) as TableAlias
           WHERE RowNumber BETWEEN n and m
  <b>  SELECT TOP(m-n+1) *
           FROM (SELECT TOP(m) *
                         FROM TableName 
                         ORDER BY ID ASC) AS TableAlias
           ORDER BY ID DESC
2.随机抽取表中的n行数据
  SELECT top (n) *                            SELECT top (n) *,newid()
  FROM TableName          <==>       FROM TableName
  ORDER BY newid()                        ORDER BY newid()
3.列出数据库里所有的表名 
  SELECT name
  FROM sysobjects
  WHERE type='U'
4.StudentAchievement (table--学生成绩)
   --StudentID (varchar(8),学生编号)
   --CourseID (varchar(10),课程编号)
   --Achievement   (int ,成绩)

  <a.1>每门课程的前两名(包括并列)
     SELECT CourseID, Achievement, StudentID
     FROM   StudentAchievement a
     WHERE  Achievement = (SELECT MAX(Achievement)
                                                   FROM StudentAchievement b
                                                   WHERE a.CourseID=b.CourseID)
                     OR
                    Achievement = (SELECT MAX(Achievement)
                                                  FROM (SELECT Achievement,CourseID
                                                                 FROM StudentAchievement c 
                                                                 WHERE c.Achievement <> (SELECT MAX(Achievement) 
                                                                                                                    FROM StudentAchievement d
                                                                                                                    WHERE c.CourseID=d.CourseID)) as e
                                                  WHERE e.CourseID=a.CourseID )

     ORDER BY CourseID,Achievement DESC
  <a.2>每门课程的前两名(当各科成绩没有并列时)
     SELECT CourseID, Achievement, StudentID 
     FROM   StudentAchievement a
     WHERE  StudentID IN (SELECT TOP(2) StudentID
                                                FROM StudentAchievement b
                                                WHERE b.CourseID=a.CourseID
                                                ORDER BY Achievement DESC)
     ORDER BY CourseID, Achievement DESC
  <b>每门课程的第一名(包括并列):
     SELECT CourseID, Achievement, StudentID
     FROM   StudentAchievement a
     WHERE  Achievement = (SELECT MAX(Achievement)
                                                   FROM StudentAchievement b
                                                   WHERE a.CourseID = b.CourseID)
     ORDER BY CourseID, Achievement DESC
5.Count number of tables in a MS Sql Server
   SELECT COUNT(*) AS TableCount
   FROM sysobjects
   WHERE xtype = 'U'