SQL: Subqueries

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

SQL summary

Q11: Displays the number, the name and mark of the student who has the highest mark in data processing.

SQL summary

Q12: Show the name of the teacher who marked history for student #5.

Next section | Previous section | SQL summary