That night, a good dream is always urine awake database

1. Query the total number of hours per grade, in ascending order (SUN is the sum)

Select Gradeid as grade number, SUM (classhour) as total hours

From Subject

Group BY Gradeid ( group )

Order by total number of hours ASC

2. Check the average score for each participant in the exam

Select Studenttno as study number, AVG (Studentresult) as average

From result

GROUP BY Studentno

3-Query the average score for each course and sort in descending order (avg is averaging)

Select  as  as Average score    from Resultgroup by Subjectid    (group) Order by average desc  (descending)

4. Check each student's total score for all exams and arrange them in descending order

Select  as study number, SUM (studentresult) as total score  from resultgroup by Studentorder by total desc     (Descending)

5. Number of courses that are queried for the number of hours per year (count means the number of rows where the conditional judgment statement)

Select  as  as Total courses from Subject where classhour>=GROUP by Gradeid

6. Query the average age of each grade student (DateDiff refers to the interval between 2 dates GETDATE () is the current date and the current average age is out)

Select  as  as average age  from Studentgroup by Gradeid

7. Check the total number of students in each grade including Beijing (% of the word contains Beijing 2 words even if Beijing area)

Select  as grade, COUNT (sudentno)  as total number of people fromwhere'% Beijing  'GROUP by Gradeid

8. For students who are enrolled in the grade test, the average passing student record is arranged in descending order (having the usage is to filter the grouping)

Select  as study number, AVG (studentresult) as average score  from resultgroup by studentnohaving AVG (Student Result)>ORDER by average DESC

9.-Query exam Time is-9-9 course pass average number of courses average

Select  as Course number, AVG (Studentresult) as all courses are divided by average Select  from Result where examdate>='2009-9-9' and examdate<='2009-9-10' GROUP BY subjectidhaving avg (stubentresult)>

10. Statistics of at least one failed student number and number.

Select  as number, COUNT (Studentresult) as  times  from Resultwhere studentresult<GROUP by Studentno

If there is a group by keyword in the statement, then the select can only be followed by columns that appear after group BY, or aggregate functions

--max () min () count (), SUM (), AVG ()

That night, a good dream is always urine awake database