Logo Bases de données
Français     English

Applications of Databases to Humanities and Social Sciences

   
Introduction


SQL: Subqueries


Nested SELECT

Subquerie, or nested SELECT, allow to realize complex queries which would normaly require several queries with storage of the intermediate results. They are characterized by an interrogation (SELECT clause) included in the WHERE clause, the HAVING clause or the SELECT clause of another query.

-Determine the name sex and age of the oldest student.

SELECT Nometu, Cdsexe, DATEDIFF(CURRENT_DATE,Dtnaiss)/365 AS Age
FROM ETUDIANT
WHERE DATEDIFF(CURRENT_DATE,Dtnaiss) /365 =
( SELECT MAX(DATEDIFF(CURRENT_DATE,Dtnaiss) /365) FROM ETUDIANT );

Predicates IN, ANY, ALL, EXISTS

A subquery can return a subset of zero to n values. According to the conditions which one wants to express, one can use the predicates IN, ANY, ALL or EXISTS.

Predicate
Meaning
IN
The comparison operatror is the equality and the logical operation between values is OR.
ANY

Allows to check if at least a value of the list satisfies condition.

ALL
Allows to check if condition is realized for all the values of the list.
EXISTS
If the subquery returns a result, the value returned is True otherwise the value returned is False.


-Display the marks of the student number 1 which are equal to the marks of the student number 2.

SELECT Note
FROM NOTES
WHERE Numetu=1 AND Note IN
( SELECT Note FROM NOTES WHERE Numetu=2);

-Display the marks of the student number 1 which are greater than the marks of the student number 2.

SELECT Note
FROM NOTES
WHERE Numetu=1 AND Note > ANY
( SELECT Note FROM NOTES WHERE Numetu=2);

-Display the marks of the student number 1 which are lower than all the marks of the student number 9.

SELECT Note
FROM NOTES
WHERE Numetu=1 AND Note < ALL
( SELECT Note FROM NOTES WHERE Numetu=9);

-Display all information on the students who do not have any mark.

SELECT *
FROM ETUDIANT E
WHERE NOT EXISTS
( SELECT Numetu FROM NOTES WHERE Numetu=E.Numetu);

Test queries examples

SQL summary

Q11: Display the number the name and the mark of the student who has the best mark in data processing.

SQL summary

Q12: Display, for the student number 5, the name of the teacher who marked him in History.

 

© Marc Grange, February 2001 | Last update: March 28, 2012