Nested SELECT
Subquery, or nested SELECT, is used to make complex queries that would normally require multiple queries, with intermediate output storage. These are characterized by a query (SELECT clause) included in the WHERE clause, the HAVING clause or the SELECT clause of another query.
-Determine the gender 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 from 0 to n values. Depending on what conditions we want to express, we can use IN, ANY, ALL or EXISTS.
| Predicate | Meaning |
| IN | The comparator is the equality and the logical operation between the values is OR. |
| ANY | Allows to check if at least one value of the list satisfies the condition. |
| ALL | Check whether the condition is fulfilled for all values in the list. |
| EXISTS | If the subquery returns a result, the returned value is True or the returned value is False. |
-Display marks for Student Number 1 which are equal to Student Number 2.
SELECT Note FROM NOTES WHERE Numetu=1 AND Note IN (SELECT Note FROM NOTES WHERE Numetu=2);
-View student number 1 notes that are higher than student number 2.
SELECT Note FROM NOTES WHERE Numetu=1 AND Note > ANY (SELECT Note FROM NOTES WHERE Numetu=2);
-Display marks from Student Number 1 which are less than all marks from Student Number 9.
SELECT Note
FROM NOTES
WHERE Numetu=1 AND Note < ALL
(SELECT Note FROM NOTES WHERE Numetu=9);
-Display all information about students who have no marks.
SELECT * FROM ETUDIANT E WHERE NOT EXISTS ( SELECT Numetu FROM NOTES WHERE Numetu=E.Numetu);
Test queries examples
Q11: Displays the number, the name and mark of the student who has the highest mark in data processing.
Q12: Show the name of the teacher who marked history for student #5.