Logo Bases de données
Français     English

Applications of Databases to Humanities and Social Sciences

Introduction


SQL Recapitulative exercise

To treat the different questions or to obtain the associated answers, proceed as follows :

To treat a question
To have an answer
1-Select the question
2-Enter your SQL query
3-Click on "Execute"
1-Select the question
2-Click on "Answer"
3-Click on "Execute"

Note to english readers : The value of a string attribute must be spell in french. To see the real content of string attributes, use the command :

SELECT Attribute_name FROM Name_of_the_table;

With the STUDENTS database, defined in the column DB design, write the SQL queries allowing to answer the following questions.

E01:
Display which was the average age of the men and the women at January first, 2000.
E02:
Display the name and the rank of the teachers of history.
E03:
Display the names and number of the students who do not have marks in Sociology.
E04:
Display the name and the coefficient of the subjects which are taught by senior lecturers (MCF) or assistants (ASS).
E05:
Display for each student (name and number), in alphabetical order, the average which he obtained in each subject.
E06:
Display the name, the age and the sex of the students who had a mark in data processing greater than the average mark of the class.
E07:
Display, for each student (name and number) which has a mark in each subjects, the average he obtained to the diploma.
E08:
Display the name rank and seniority of the teachers who teach more than one subject.
E09:
Display the number of men and the number of women who succeeded in the diploma : average mark > = 10.
E10:
Display the name, the number and the sex of the students who have a mark in data processing greater than their mark of Mathematics.
E11 :
Display the name of the couples,a man a woman, for which the difference in age does not exceed 6 months (180 days).
E12 :
For the students not having a mark in a subject, Display the number the name of the student and the name of the subject concerned.
E13 :
Display the subjects for which the average of the marks is lower than 10. Display also the name of the corresponding teacher.
E14 :
Display, for each subject, which is the best mark and which is the student who obtained it.
E15 :
Display, for each sex (Man, Woman) the average of the marks in the subject taught by Julie Boyer.

Selected query :

 

© Marc Grange, February 2001 | Last update: January 29, 2009