MySQL Child query Job

#数据准备
drop table if exists class;
CREATE TABLE Class (
class_no Int (2) unsigned Zerofill primary key auto_increment comment ' class number ',
Class_name varchar (+) NOT null comment ' class name '
);
INSERT into class values (1, ' Pui gifted class ');
INSERT into class values (2, ' normal class ');

drop table if exists student;
CREATE TABLE Student (
Stu_no Int (2) unsigned Zerofill primary key auto_increment comment ' student number ',
Stu_name varchar (+) NOT null comment ' learner name ',
Stu_sex varchar (3) NOT null comment ' trainee sex ',
Stu_age tinyint (2) unsigned zerofill comment ' student age ',
Grade double (5,2) zerofill comment ' score ',
class_no Int (2) unsigned zerofill comment ' class number ',
Foreign KEY (CLASS_NO) references class (CLASS_NO)
);
INSERT into student values (01, ' Li Bai ', ' male ', 18, 60, 01);
INSERT into student values (02, ' Du Fu ', ' Male ', 20, 76, 01);
INSERT into student values (03, ' Zhang Fei ', ' Male ', 32, 80, 02);
INSERT into student values (04, ' Han Xin ', ' Male ', 26, 98, 02);
INSERT into student values (05, ' Dragon ', ' Male ', 27, 56, 02);
INSERT into student values (06, ' Big Joe ', ' female ', 17, 88, 01);
INSERT into student values (07, ' Little Joe ', ' female ', 16, 96, 01);
INSERT into student values (08, ' Little Joe ', ' female ', 16, 90, 01);
INSERT into student values (09, ' Guan brother ', ' male ', 32, 80, 02);
INSERT into student values (10, ' Liu Bei ', ' Male ', 98, NULL);
ALTER TABLE student drop foreign key ' student_ibfk_1 ';
*************************************************************************************************************** ********************
Class NULL means not in any class, except for students who have class null
1: What is the minimum score for each class?
Select Class_no,min (grade) from student where (class_no are not null) group by CLASS_NO;

2: What is the number of each class?
Select Class_no,count (*) from student where (class_no are not null) group by CLASS_NO;

3: The average score of each class is queried, the demand is sorted by the average descending order
Select Class_no,avg (grade) as AVG from student where (class_no are not null) group by class_no ORDER by avg Desc;

4: Find out the average number of male and female students in each class, sorted by the average descending order
Select Class_no,stu_sex,avg (grade) as AVG from student group by Class_no,stu_sex ORDER by avg Desc;

5: Check out the number of students in each class from 80 to 95
Select Class_no, COUNT (*) from student where (class_no are NOT null) and (grade between) group by Class_no;

6: Search for classes with an average score of less than 80
Select Class_no,avggrade from (select Class_no,avg (grade) as Avggrade from student group by class_no) as temp where (class _no is not null) and Avggrade < 80; (create temp table)
Mysql> Select Class_no,avg (grade) as AVG from student GROUP BY CLASS_NO has avg < 80;
7: Find out the average grade of class No. 01 and the total score of 02 class (using a statement)---use and query
(Select Class_no,concat ("Average score:", AVG (grade)) as ' score ' from student where class_no = 1) Union (select Class_no,concat ("Total:" , sum (grade)) as ' score ' from student where class_no = 2);
Check the average grade and total score for each class:
Select Class_no, AVG (grade), sum (grade) from student where (class_no was not null) group by CLASS_NO;

8: Find the class with the lowest average score
Multiple virtual tables
Select Class_no from (select Class_no,avg (grade) as grade from student where (class_no was not null) group by CLASS_NO) a s temp WHERE grade = (select min (t1_grade) from (select AVG (grade) as T1_grade from student group by class_no) as TEMP1);
Select Class_no,avg (grade) from student group by Class_no ORDER by AVG (grade) ASC limit 1;
Select Class_no,avg (grade) as AVG from student GROUP BY CLASS_NO have avg = (select AVG (grade) from student group by CLA Ss_no ORDER by AVG (grade) ASC limit 1);
9: Check out the student number for 4,8,9
SELECT * FROM student where stu_no in (4,8,9);

10: Check out the students who score less than average in each class
SELECT * FROM Student, (select Class_no as CNO, AVG (grade) as Agrade from student where (class_no was not null) group by CLA SS_NO) as temp where grade < Agrade and class_no = CNO;

MySQL Child query Job