Group query using group queries for student-related information and qualifying conditions

One, on the machine practice a

The average score of each course is queried and sorted in descending order

1 Select  as total number of hours 2  from Subject 3 GROUP BY Gradeid 4 ORDER by sum (classhour)

2--check the average score for each participant in the exam

1 Select  as Draw points 2  from Result 3 GROUP by Studentno

3--the average score for each course and in descending order

1 Select subjectid,avg (studentresult) as average score 2 from Result  3GROUP by Subjectid4 ORDER by average DESC

4--Check the total score for each student in all exams and arrange them in descending order

1 Select  as Total 2  from Result 3 GROUP BY Studentno 4 ORDER BY total desc

Second, the computer practice two

Find out the number of courses with more than 50 per year of class hours S1

1 Select  as Number of courses 2  from Subject 3 where  Classhour>4 GROUP by Gradeid

2--Check the average age of each grade student:

1 Select  as average age 2  from Student 3 GROUP by Gradeid

3--inquiry for students in each grade including Beijing

1 Select gradeid,count (1 as total number 2 from student3  where'% beijing%'4 GROUP by Gradeid

4--for students who have taken the exam, the average number of students who have passed the test (student, average), in descending order

1 Select  as Average score 2  from Result 3 GROUP BY Studentno 4 has avg (studentresult) >=5 ORDER by average DESC

5--examination time is the average pass of the 2009-9-9 course

1 Select  as Average score 2  from Result 3 where examdate>='2009-9-9 ' and  examdate<'2009-9-10 ' 4 GROUP BY Subjectid 5 having AVG (studentresult) >=

6--statistics at least one failed student number and number.

1 Select studentno,count (1 as   number 2 from Result 3 where studentresult<4 GROUP by Studentno

Group query using group queries for student-related information and qualifying conditions