T-SQL 第十一章

--上机练习1
SELECT   [GradeId] AS 年级,SUM( [ClassHour]) as 总学时数
  FROM [MySchool].[dbo].[Subject]
  GROUP BY [GradeId]
  order by SUM( [ClassHour])
  
 ---查询每个参加考试的学员平均分
 SELECT AVG(StudentResult) as 平均分,StudentNo
 FROM [MySchool].[dbo].[Result]
GROUP BY StudentNo
--查询每门课程的平均分,并按照降序排列
SELECT AVG(StudentResult) as 平均分,SubjectId as 每门课程
 FROM [MySchool].[dbo].[Result]
GROUP BY SubjectId
order by AVG(StudentResult) DESC
--查询每个学生参加的所有考试的总分,并按照降序排列
SELECT sum(StudentResult) as 每个学生参加考试总分,StudentNo as 每个学生
 FROM [MySchool].[dbo].[Result]
GROUP BY StudentNo
order by sum(StudentResult) DESC 
--上机练习2
SELECT
     sum([ClassHour]) as 课程数
      ,[GradeId]AS 每学期
  FROM [MySchool].[dbo].[Subject]
  group by   [GradeId],[ClassHour]
  having  sum([ClassHour])>50
  
  SELECT GradeId as 年级, avg(DATEDIFF(DD,BornDate,GETDATE()))/365 AS 平均年龄
   FROM [MySchool].[dbo].[Student] 
   group by GradeId
   
   
 SELECT (GradeId) ,COUNT(*) AS 人数
 FROM  [MySchool].[dbo].[Student] 
 WHERE Address LIKE '北京%'
 group by (GradeId)
  
  
SELECT StudentNo,AVG(StudentResult) AS 平均分
FROM   [MySchool].[dbo].[Result] 
group by StudentNo
having AVG(StudentResult)>60
order by AVG(StudentResult) desc


SELECT SubjectId,AVG(StudentResult)AS 课程及格平均分
FROM [MySchool].[dbo].[Result] 
WHERE ExamDate='2013-3-22' AND StudentResult>=60
GROUP BY SubjectId


SELECT StudentNo,COUNT(StudentResult) AS 不及格次数
FROM [MySchool].[dbo].[Result] 
WHERE StudentResult<60
GROUP BY StudentNo

--上机练习3
SELECT [StudentName],T.[GradeId],T.SubjectName,S.[Phone]
  FROM [MySchool].[dbo].[Student] S,[MySchool].[dbo].[Subject] T
  WHERE S.GradeId=T.GradeId
  
   SELECT S.GradeId,S.GradeName,ClassHour
   FROM [MySchool].[dbo].[Grade] S,[MySchool].[dbo].[Subject] T
   WHERE S.GradeId=T.GradeId AND S.GradeId=1
   
   SELECT S.StudentName,R.StudentResult,R.ExamDate
   FROM  Student S,Result R
   WHERE S.StudentNo=R.StudentNo AND R.SubjectId=1
   
   
   SELECT R.StudentNo,S.SubjectName,R.StudentResult,R.ExamDate
   FROM Subject S,Result R 
   WHERE S.SubjectId=R.SubjectId AND R.StudentNo='S1101007'
   
   
   SELECT R.StudentNo,S.SubjectName,R.StudentResult,R.ExamDate
   FROM Subject S,Result R
   WHERE S.SubjectId=R.SubjectId
   
   

--上机练习4
SELECT  S.StudentNo,S.StudentName,R.StudentResult,SQ.SubjectName
FROM Student S,Subject SQ,Result R
WHERE S.StudentNo=R.StudentNo  AND SQ.SubjectId=R.SubjectId

SELECT  S.StudentName,R.StudentResult,R.ExamDate
FROM Student S,Subject SQ,Result R
WHERE S.StudentNo=R.StudentNo  AND SQ.SubjectId=R.SubjectId AND SQ.SubjectName='走进JAVA编程世界'
--上机练习5
SELECT  S.SubjectName,r.StudentNo,R.StudentResult
FROM Subject S
LEFT JOIN   Result R 
ON S.SubjectId=R.SubjectId


SELECT  S.SubjectName,r.StudentNo,R.StudentResult
FROM Subject S
LEFT JOIN   Result R 
ON S.SubjectId=R.SubjectId
where StudentNo  is null and StudentResult is null



SELECT G.GradeName,S.StudentNo,S.StudentName
FROM Grade G
LEFT JOIN  Student S
ON G.GradeId=S.GradeId