title: SQL Query to retrieve below average score
body: <p>Hello I'm working on a system for primary school and im stuck on queries in database part. I would like to retrieve:</p>
  
  <ul>
  <li>Number of students below the average score</li>
  <li>Average mark for each student across all tests</li>
  <li>Average mark for each test</li>
  </ul>
  
  <p>I'm not really good at queries and I couldn't work it out. I appreciate any help. Thanks</p>
  
  <p>Below you can find my tables and test data</p>
  
  <p>Tables:</p>
  
  <pre><code>CREATE TABLE Student
      (
      StudentID INT  auto_increment,
      ClassID VARCHAR(10),
      FirstName VARCHAR(25),
      LastName VARCHAR(25),
      DateOfBirth DATE,
      Gender VARCHAR(10),
      PRIMARY KEY (StudentID, ClassID)
      );
  
      create table Subjects
      (
      SubjectID INT,
      SubjectName VARCHAR(25),
      PRIMARY KEY (SubjectID)
      );
  
  
  
      create table Grade
      (
      StudentID INT,
      SubjectID INT,
      ClassID VARCHAR(10),
      Grade DECIMAL(5,1),
      FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
      FOREIGN KEY (SubjectID) REFERENCES Subjects(SubjectID),
      FOREIGN KEY (ClassID) REFERENCES Student(ClassID)
      );
  </code></pre>
  
  <p>Test data:</p>
  
  <pre><code>INSERT INTO Student (StudentID, ClassID, FirstName, LastName, DateOfBirth, Gender)
  VALUES ('', '01', 'John', 'Smith', '15/01/2000', 'Male'),
  ('', '01', 'Michael', 'Black', '15/03/2000', 'Male'),
  ('', '01', 'Dennis', 'White', '07/08/1999', 'Male'),
  ('', '01', 'Emy', 'Blue', '10/04/2000', 'Female'),
  ('', '01', 'Joe', 'Yellow', '09/05/2000', 'Male'),
  ('', '01', 'Aaren', 'Jackson', '09/009/1999', 'Male'),
  ('', '01', 'Marta', 'Harris', '30/01/2000', 'Female'),
  ('', '01', 'Laura', 'Lewis', '09/01/2000', 'Female'),
  ('', '01', 'Michael', 'Jackson', '01/01/2000', 'Male'),
  
  ('', '02', 'Piotr', 'Kowalski', '15/01/2000', 'Male'),
  ('', '02', 'Harris', 'Bialy', '15/03/2000', 'Male'),
  ('', '02', 'Porter', 'Czarny', '07/08/1999', 'Male'),
  ('', '02', 'Maciek', 'Blue', '10/04/2000', 'Female'),
  ('', '02', 'Mateusz', 'Yellow', '09/05/2000', 'Male'),
  ('', '02', 'Aaren', 'Jackson', '09/009/1999', 'Male'),
  ('', '02', 'Marta', 'Harris', '30/01/2000', 'Female'),
  ('', '02', 'Laura', 'Lewis', '09/01/2000', 'Female'),
  ('', '02', 'Chaytan', 'Jackson', '01/01/2000', 'Male');
  
  
  INSERT INTO Subjects (SubjectID, SubjectName)
  VALUES ('01', 'English'),
  ('02', 'Mathematics'),
  ('03', 'Science'),
  ('04', 'Geography'),
  ('05', 'IT'),
  ('06', 'History');
  
  INSERT INTO Grade (StudentID, SubjectID, ClassID, Grade)
  VALUES ('1', '2', '01', '60.5'),
  ('2', '2', '01', '70.0'),
  ('3', '2', '01', '40.0'),
  ('4', '2', '01', '33.5'),
  ('5', '2', '01', '90.0'),
  ('6', '2', '01', '77.5'),
  ('7', '2', '01', '89.0'),
  ('8', '2', '01', '74.0'),
  ('9', '2', '01', '66.5'),
  
  ('10', '2', '02', '30.5'),
  ('11', '2', '02', '50.0'),
  ('12', '2', '02', '30.0'),
  ('13', '2', '02', '73.5'),
  ('14', '2', '02', '70.0'),
  ('15', '2', '02', '57.5'),
  ('16', '2', '02', '69.0'),
  ('17', '2', '02', '34.0'),
  ('18', '2', '02', '76.5'),
  
  ('1', '1', '01', '65.5'),
  ('2', '1', '01', '73.0'),
  ('3', '1', '01', '41.0'),
  ('4', '1', '01', '39.5'),
  ('5', '1', '01', '96.0'),
  ('6', '1', '01', '70.5'),
  ('7', '1', '01', '80.0'),
  ('8', '1', '01', '74.0'),
  ('9', '1', '01', '64.5'),
  
  ('10', '1', '02', '55.5'),
  ('11', '1', '02', '43.0'),
  ('12', '1', '02', '61.0'),
  ('13', '1', '02', '49.5'),
  ('14', '1', '02', '76.0'),
  ('15', '1', '02', '80.5'),
  ('16', '1', '02', '99.0'),
  ('17', '1', '02', '100.0'),
  ('18', '1', '02', '55.5');
  </code></pre>