数据库设计--第七章上机题和课后简答题

练习1：使用存储过程查看表信息

USE MySchool
GO
EXEC sp_columns Student  --查看表Student中列的信息
EXEC sp_help Student  --查看表Student的所有信息
EXEC sp_helpconstraint Student --查看表Student的约束

练习2：查询获得各学期课程信息

/*---创建存储过程----*/
CREATE PROCEDURE usp_grade_subject
AS 
   SELECT GradeName,SubjectName,ClassHour FROM Grade 
   INNER JOIN Subject
   ON Grade.GradeId=Subject.GradeId
   ORDER BY Subject.GradeId,SubjectNo
GO
/*---调用执行存储过程---*/
EXEC usp_grade_subject 

练习3：用存储过程查指定学期课程

CREATE PROCEDURE usp_query_subject
  @GradeName VARCHAR(50) = NULL
AS 
  IF @GradeName IS NULL
    SELECT GradeName,SubjectName,ClassHour FROM Grade 
    LEFT JOIN Subject   ON Grade.GradeId=Subject.GradeId 
    UNION 
    SELECT GradeName,' ',SUM(ClassHour)FROM Grade
    LEFT JOIN Subject  ON Grade.GradeId=Subject.GradeId 
    GROUP BY GradeName
  ELSE
    SELECT GradeName,SubjectName,ClassHour FROM Grade
    LEFT JOIN Subject  ON Grade.GradeId=Subject.GradeId 
    WHERE GradeName=@GradeName
    UNION
    SELECT GradeName,' ',SUM(ClassHour)FROM Grade
    LEFT JOIN Subject  ON Grade.GradeId=Subject.GradeId 
    WHERE GradeName=@Grade