SelectNot.txt

-- Total number of Tests
SELECT COUNT(numtest) FROM Test;

-- List of score, specifying for each the name and first name of the Student who obtained it
SELECT Notation.score, Student.name, Student.firstname FROM Notation
    INNER JOIN Student ON Student.numstud = Notation.numstud;

-- List of score, specifying for each the name and first name of the Student who obtained it and the wording of the Matter concerned
SELECT Notation.score, Student.name, Student.firstname, Matter.wording FROM Notation
    INNER JOIN Student ON Student.numstud = Notation.numstud
    Inner JOIN Test ON Test.numtest = Notation.numstud
    INNER Join Matter ON Test.codemat = Matter.codemat;

-- List of score greater than or equal to 10
SELECT score FROM Notation WHERE score >= 10;

-- Last name and first name of the students who obtained at least a score equal to 20
SELECT Student.name, Student.firstname FROM Student
    INNER JOIN Notation ON Notation.numstud = Student.numstud
    WHERE score = 20;

-- Average scores for each student (indicate name and first name) 
SELECT Student.name, Student.firstname, AVG(Notation.score) FROM Student
    INNER JOIN Notation ON Notation.numstud = Student.numstud
    GROUP BY Student.name, Student.firstname;

-- List of tests whose date is between January 1 and June 30, 2014
SELECT * FROM Test WHERE testdate BETWEEN '2014-01-01' AND '2014-06-30';

-- List of tests (number, date and place) including the matter of the subject
SELECT Test.numtest, testdate, place, wording FROM Test
    INNER JOIN Matter On Matter.codemat = Test.codemat;

-- Average scores for each student (indicate name and first name), ranked from best to worst
SELECT name, firstname, AVG(score) FROM Student
   INNER Join Notation On Notation.numstud = Student.numstud
   GROUP By name, firstname
   ORDER BY AVG(score) DESC;
    
-- Average scores for matter (indicate the wording) comprising more than one test
SELECT AVG(score), COUNT(DISTINCT Test.codemat) FROM Notation
    INNER Join Test ON Notation.numtest=Test.numtest
    GROUP By Test.codemat
    HAVING COUNT(DISTINCT Test.numtest) > 1;
    
-- Average of score obtained in the tests (indicate the number of the test) where less than 6 students were rated
SELECT AVG(score), count(numtest) FROM Notation
    Group BY numtest
    HAVING COUNT(numtest) < 6;