SQL数据库的十条命令

--(1)查询每个总学时数
select GradeId,SUM(classHour) from subject
group by GradeId
order by(SUM(classHour))

--(2)查询每个考试学员的平均分
select studentNo,AVG(studentresult) from result
group by studentNo 

--(3)查询每门课程的平均分,降序排列
select subjectId,AVG(studentresult) from result
group by subjectId
order by AVG(studentresult) desc

--(4)查询每个学生考试总分,降序排序
select studentNo,sum(studentresult) from result
group by studentNo
order by sum(studentresult) desc

--(5)每学期学时超过50的课程数
select GradeId,count(classHour) as 学时超过50课程数 from subject
where classHour>50
group by gradeId

--(6)查询每学期学生的平均年龄
select GradeId,AVG(DATEDIFF(yyyy,birthday,GETDATE())) as 平均年龄 
from student
group by GradeId

--(7)查询北京地区每学期学生人数
select GradeId,COUNT(1) as 北京地区学生人数
from student
where address like '%北京%'
group by GradeId

--(8)查询学生平均成绩及格的学生记录,降序排列
select studentNo,AVG(studentresult) as 平均成绩
from result
group by studentNo
having AVG(studentresult)>=60
order by 平均成绩 desc

--(9)考试日期内的及格课程平均分
select * from result
select subjectid as 课程,AVG(studentresult) as 平均分
from result
--where examdate>'2013-2-15' and examdate<'2013-2-16'  可以写成这个
where datepart(yyyy,examdate)=2013 and datepart(mm,examdate)=06 and datepart(dd,examdate)=30
group by subjectId
having AVG(studentresult)>=60

--(10)查询至少一次考试成绩不合格的学生学号,不及格次数
select studentNo as 学号,COUNT(0) as 考试不及格次数
from result
where studentResult<60
group by studentNo

 

好了, 就这些了, 十条命令分别完成了在MySchool数据库中的各个表中的各类查询, 至于各自的功能见代码内的注释