你的位置:首页 > 数据库

[数据库]数据库的有关知识==我们的血泪史之经典练习(1


今天给大家说说数据库的有关知识

抒情一下,想在好困,真的,虽然我在这温暖的教室,身边有知心的盆友, ,很高兴还能是学生的一员,我们还年轻,我们也不会想的太多,高高兴兴上学,快快乐乐回家,每天吃的饱饱,听着老湿讲课,和童鞋打闹....还是幸福的,不是吗?好像有点精神了...

学习:

给大家说一下这些有关关键字的使用顺序和内部运行顺序(切记哟)

下面以如何查询每门课程的平均分为例:

使用顺序                                                                           内部运行顺序

select SubjectId as 课程编号,AVG(studentresult) as 平均分    --04.投影结果
from Result                                                                     --01.定位到表
where SubjectId<3                                                          --02.分组前第一道过滤
group by SubjectId                                                          --03.分组
having COUNT(studentno)>3                                            --05.分组后第二道过滤
order by 课程编号 desc                                                     --06.最后排序

 

 

**************经典练习1*******************


--01.查询每个年级的总学时数,并按照升序排列

select GradeId as 年级编号,SUM(ClassHour) as 总学时数from Subjectgroup by GradeIdorder by sum(ClassHour)

--02.查询每个参加考试的学员平均分

select studentno as 学生编号,AVG(studentresult) as 平局分from Resultgroup by StudentNo

--03.查询每门课程的平均分,并按照降序排列

select subjectid as 学科编号,AVG(studentresult)as 平均分from Resultgroup by SubjectIdorder by 平均分 desc --'平均分'也可以为'AVG(studentresult)'哟!

 --04.查询每个学生参加所有考试的总分,并按照降序排列

select studentno as 学生编号,SUM(studentresult) as 总分from Resultgroup by StudentNoorder by 总分 desc  --' 总分'也可以为'SUM(studentresult)'哟!

--05.每个年级 男女生总人数(gradeid,gender,人数)

select gradeid as 年级编号,gender as 性别,COUNT(1) as 人数from studentgroup by GradeId,Genderorder by GradeId,Gender

--06.每个年级的总人数,满足总人数必须大于等于3

select gradeid as 年级编号,COUNT(1) as 总人数from studentgroup by GradeIdhaving COUNT(1)>=3select * from Student

 *****************经典练习2****************

--01.查询每年级学时数超过50 的课程数 S1

select gradeid as年级编号,COUNT(subjectid) as 课程数from Subjectwhere ClassHour>50group by GradeId

--02.查询每年级学生的平均年龄:

select gradeid as 年级编号,AVG(DATEDIFF(yy,birthday,GETDATE())) as 平均年龄from studentgroup by gradeid

--03.查询每个年级包含北京的学生总数 (like '%北京%')

select gradeid as 年级编号,COUNT(1) as 总人数from studentwhere address like '%北京%'group by Gradeid

 --04.查询参加考试的学生中,平均分及格的学生记录(学号,平均分),按照降序排列

select studentno as 学生编号,AVG(StudentResult) as 平均分from Resultgroup by StudentNohaving AVG(StudentResult)>=60order by 平均分 desc --'平均分'也可改为'AVG(StudentResult)'哟!

--05.查询考试时间为2015-12-20课程的及格平均分 (课程编号    所有课程平均分)

select subjectid as 学科编号,AVG(studentresult) as 平均分from Resultwhere ExamDate>='2015-11-20' and ExamDate<'2015-11-20'group by SubjectIdhaving AVG(StudentResult)>=60

--06.统计至少有一次不及格的学生学号和次数。

select studentno as 学生编号,COUNT(1) as 次数from Resultwhere StudentResult<60group by StudentNo

 

希望我的努力可以帮助到你..,应该会吧!

就这么多吧!回见!求大神赐教!