师中职称为“教授”的教师数量。РSQL:Р统计年轻教师和年长教书的数量РSELECT SUM(Р CASEР WHEN c.AGE > '35' THENР 1Р ELSEР 0Р ENDР?) AS 年长教师数,РSUM(Р CASEР WHEN c.AGE < '35' THENР 1Р ELSEР 0Р ENDР?) AS 年轻教师数РFROM teacher cР结果:Р统计各系年轻教师中职称为“教授”的教师数量。РSQL:РSELECT c.DEPARTMENT AS 系别,COUNT(t.P_TITLE) AS 教授数量 FROM teacher t,classes c WHERE t.ID = c.TEACHER_ID AND t.P_TITLE='教授' GROUP BY c.DEPARTMENTР结果:Р统计每个教师带的学生个数。РSQL:РSELECT t.ID,t.NAME,SUM(c.CURRENT_STUDENTS) FROM classes c,teacher t WHERE c.TEACHER_ID=t.ID GROUP BY c.TEACHER_IDР结果:Р统计每个教师的工作量,教师的工作量按照学生人数*所授课程的学分数进行统计РSQL:РSELECT t.ID,t.NAME,SUM(c.CURRENT_STUDENTS)*c.NUM_CREDITS FROM classes c,teacher t WHERE c.TEACHER_ID=t.ID GROUP BY c.TEACHER_IDР结果:Р根据指定的教师名字或教师工号确定教师现在的授课课程及课程的上课地点(教室号)РSQL:t.ID = 和 t.NAME 都是可以替换的РSELECTР?r.*, temp.DEPARTMENT,Р?temp.COURSEРFROMР?rooms r
全文预览
