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