SOL第十一章上機題1-5

--            
SELECT SUM(ClassHour)AS     FROM [Subject] GROUP BY GradeId ORDER BY SUM(ClassHour) 
SELECT StudentNo AS   ,AVG(StudentResult) AS       FROM Result GROUP BY StudentNo
SELECT SubjectId AS   ,AVG(StudentResult) AS       FROM Result GROUP BY SubjectId
SELECT StudentNo AS   ,SUM(StudentResult) AS       FROM Result GROUP BY StudentNo ORDER BY SUM(StudentResult) DESC



--         
SELECT GradeId AS   ,COUNT(SubjectId) AS     FROM [Subject] WHERE ClassHour>50 GROUP BY GradeId  
SELECT GradeId AS   ,AVG(DATEDIFF(DD,BornDate,GETDATE())/365) AS      FROM Student  GROUP BY GradeId
SELECT GradeId AS   ,COUNT(StudentNo) AS      FROM Student WHERE [Address] LIKE '  %' GROUP BY GradeId
SELECT StudentNo AS   ,AVG(StudentResult) AS       FROM Result GROUP BY StudentNo HAVING AVG(StudentResult)>60 ORDER BY AVG(StudentResult) DESC
SELECT StudentNo AS   ,AVG(StudentResult) AS       FROM Result WHERE ExamDate='2013-3-22' GROUP BY StudentNo HAVING AVG(StudentResult)>60 
SELECT StudentNo AS   ,COUNT(StudentResult) AS       FROM Result WHERE StudentResult<60 GROUP BY StudentNo 

--         
SELECT S.StudentName AS     , G.GradeName AS     ,S.Phone AS     
FROM Student AS S ,Grade AS G
WHERE S.GradeId=G.GradeId

SELECT G.GradeName AS     , J.SubjectName AS     , J.ClassHour AS   
FROM Subject AS J
INNER JOIN Grade AS G ON J.GradeId=G.GradeId
WHERE G.GradeId=1

SELECT S.StudentName AS     , R.StudentResult AS   , R.ExamDate AS     
FROM Student AS S
INNER JOIN Result AS R ON	S.StudentNo=R.StudentNo
WHERE R.SubjectId=1

SELECT J.SubjectName AS     , R.StudentResult AS   , R.ExamDate AS     
FROM Subject AS J
INNER JOIN Result AS R ON	J.SubjectId=R.SubjectId
WHERE R.StudentNo='S1101007'

SELECT J.SubjectName AS     , R.StudentResult AS   , R.ExamDate AS     
FROM Subject AS J
INNER JOIN Result AS R ON	J.SubjectId=R.SubjectId

--         
SELECT S.StudentNo AS   ,S.StudentName AS     ,J.SubjectName AS     , R.StudentResult AS   
FROM Student AS S
INNER JOIN Result AS R ON (S.StudentNo=R.StudentNo)
INNER JOIN [Subject] AS J ON (R.SubjectId=J.SubjectId)

SELECT S.StudentName AS     , R.StudentResult AS   ,R.ExamDate AS     
FROM Result AS R
INNER JOIN Student AS S ON (S.StudentNo=R.StudentNo)
WHERE SubjectId=2


--         
SELECT J.SubjectName AS     ,R.StudentNo AS   , R.StudentResult AS   
FROM Result AS R
RIGHT OUTER JOIN [Subject] AS J ON R.SubjectId=J.SubjectId

SELECT J.SubjectName AS     ,R.StudentNo AS   , R.StudentResult AS   
FROM Result AS R
RIGHT OUTER JOIN [Subject] AS J ON R.SubjectId=J.SubjectId
WHERE R.StudentResult IS NULL AND R.ExamDate IS NULL


SELECT G.GradeName AS     ,S.StudentNo AS   ,S.StudentName AS     
FROM Student AS S
RIGHT OUTER JOIN Grade AS G ON G.GradeId=S.GradeId