Comfortable, is left to the dead! Only 1W away from the comfort of the day! These tired don't want to live SQL query!

Kindergarten class, Silent

Attention:

The following code is simple and easy to understand please watch carefully!

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

1 Select Gradeid,SUM as  Total number of hours 2 from Subject3Group  by Gradeid 4 Order  by sum (Classhour)

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

1 Select studentno,AVG as draw score 2 from Result3  Group by Studentno

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

1 Select subjectid,avg(studentresult) as average score 2 from Result3Group by subjectid4orderby  desc

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

1 Select Studentno,SUM as  total 2 from Result3 Group  by Studentno 4 Order  by desc

Two

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

1 Select Gradeid,count  as course number 2 from Subject3 where  Classhour>4Group by Gradeid

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

1 Select Gradeid,avg(DATEDIFF(yy,birthday,GETDATE as average age  2 from student3Group by

3--inquiry for students in each grade including Beijing

1 Select Gradeid,COUNT(1 as total number 2 from student 3 where  like ' % 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 SelectStudentno,AVG(Studentresult) asAverage score2  fromResult3 Group  byStudentno4  having AVG(Studentresult)>= -5 Order  byAverage scoredesc 

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

1 SelectSubjectid,AVG(Studentresult) asAverage score2  fromResult3 whereExamdate>='2009-9-9'  andExamdate<'2009-9-10'4 Group  bySubjectid5  having AVG(Studentresult)>= -

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

1 Select studentno,Count(1 as   times 2 from Result  3where studentresult<4Groupby Studentno

Friends sharing ends here

Please wait for the next share

　　　　　　　　　　　　　　　　　　　　　　　　　　

Comfortable, is left to the dead! Only 1W away from the comfort of the day! These tired don't want to live SQL query!