Do you remember the games we played in groups? _ MySQL

UseMyschool -------------------- machine Exercise 1 --------------------- query the total number of hours of each grade, and sort selectgradeidas in ascending order, sum (Classhour) hour (Classhour)... use Myschool

------------------ Practice 1 -------------------

-- Query the total learning hours of each grade and sort them in ascending order

Select gradeid as grade, sum (Classhour)

From subject

Group by gradeid

Order by SUM (Classhour)

-- Query the average score of each student taking the test

Select studentno as student ID, AVG (studentresult)

From result

Group by studentno

Select * from subject

-- Query the average score of each course and sort them in order

Select subjectid as course, AVG (studentresult)

From result

Group by subjectid

Order by AVG (studentresult) desc

-- Query the total scores of all the exams each student takes and sort them in descending order.

Select studentno as student ID, SUM (studentresult)

From result

Group by studentno

Order by SUM (studentresult) desc

--------- Machine Exercise 2 ------------------

-- Query the number of courses exceeding 50 per semester

Use myschool

Select gradeid as grade, COUNT (subjectid) as number of courses

From subject

Where classhour> 50

Group by gradeid

-- Query all information about the course schedule

Select * from subject

-- Query the average age of students per semester

Select * from student

Select gradeid as grade, AVG (DATEDIFF (yy, birthday, getdate () as average age

From student

Group by gradeid

-- Query the number of students per semester in Beijing

Select gradeid as grade, COUNT (1) as COUNT

From student

Where address like ('% Beijing % ')

Group by gradeid

-- Query the average passing student records of the students taking the test, and sort them in descending order according to the score

Select studentno, AVG (StudentResult) as average score

From Result

Group by StudentNo

Having AVG (StudentResult)> = 60

Order by average desc

-- Query all information in the orders table

Select * from result

-- Query the average passing score of the Course whose start date is February 22, 2014.

Select subjectid, AVG (studentresult) as average score

From Result

Where ExamDate> = '2014-2-22 'and ExamDate <'2014-2-23'

Group by SubjectId

Having AVG (StudentResult)> = 60

-- Calculate the number and number of times of failing students at least once.

Select studentno, COUNT (1) as times

From Result

Where StudentResult <60

Group by StudentNo

Note: (1) the where clause cannot be followed by Aggregate functions.

(2) having is the second filtering or filtering of grouped data, that is, having is not performed without group.

(3) If the statement contains the group by keyword, select can only be followed by the column after group by, or aggregate function

SQL statement writing sequence: execution sequence:

Projection results of select column names or aggregate functions (4)

From table name (1) locate table

Where condition (2) first filter before grouping

Group by column name (3) group

Having aggregate function or column Name After grouping (5) The second filter after grouping

Order by (6) Final sorting