A statement to inspect the SQL foundation!
Data table: Student_ID, Name, AGE, Class_ID, Group_ID
[Student ID] [Name] [Age] [Class ID] [Group ID]
Use a SQL statement
1. Since the number of people below the group average age is 50%, the class is higher than the class average age.
2. Students in the largest age and minimum age in the group are not the average age of the class's largest group of those groups in the class.
Molution 1:
Select students.namefrom (/ * is projected to the class average age collection to class, seeking high than the average age of average age * / selects.name, students.class_id, students.group_id from students inner Join (/ * This there are various classes appear at the request of the student's average age * / SELECT class_id, AVG (age) AS avgage FROM students GROUP BY class_id) students_1 ON students.class_id = students_1.class_id AND students.age> students_1.avgage) studentsINNER JOIN / * Substantians in those groups below 50% below group average age than group average ages are lower than those of the group average age. * / select group_id, count (student_id) Numberofgroup from students group by group_id) StudentS_0 Inner Join (/ * Student Collection For the average age collection of the student's group, the number of people below each group is lower than the average age * / Select Students .group_id, COUNT (students.student_id) AS Number FROM students INNER JOIN (SELECT group_id, AVG (age) AS avgage FROM students GROUP BY group_id) students_1 ON students.group_id = students_1.group_id AND students.age --------------------------------- Molution 2: 1. Since the number of people below the group, the number of people below 50%, the class is higher than the class, the class, the class, the class, the class, the class, the average age, SELECT * from School Yere Age> (SELECT AVG (AGE) from school where class_id = y.class_id) And class_id In (Select class_id From School x Where (Select Count (1) From school Where age <(Select AVG (age) From school Where group_id = x.group_id) And group_id = x.group_id) / (Select Count (1) From school where group_id = x.group_id) <0.5) 2. The largest agents and minimally age students in the group are neither class of class, the average age of the smallest group of those groups, AVG (AGE) from school from School X Where (Select Min (age) From school Where group_id = x.groupid And class_id = x.class_id) <> (Select Min (age) From school Where class_id = x.class_id) And (Select Max (age) From school Where group_id = X.Groupid and class_id = x.class_id) <> (select max (age) from school where class_id = x.class_id) group by class_id
