Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
548 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
Written by Andrew Gould
In this tutorial
Subqueries are often used with aggregate functions such as MAX - this part of the series will also show a couple of the other useful functions that you can use.
You might find this tutorial on grouping data useful as a reminder of which aggregate functions are available in SQL.
We've already seen how to use the MAX function to match records against the largest value in a column. You can also use the MIN function to find the smallest value in a field. The query below would find the film or films with the shortest running time:
SELECT
FilmName
,FilmRunTimeMinutes
FROM
tblFilm
WHERE
FilmRunTimeMinutes =
(SELECT
MIN(FilmRunTimeMinutes)
FROM
tblFilm)
The results of this query are shown below:
Short duration, long title!
You can use the AVG function in a subquery to return the average of a column of numbers. You can then combine this with the greater-than and less-than operators to find records that are above or below average. The query below shows all of the films whose running time is longer than the average running time of all the films in the table:
SELECT
FilmName
,FilmRunTimeMinutes
FROM
tblFilm
WHERE
FilmRunTimeMinutes >
(SELECT
AVG(FilmRunTimeMinutes)
FROM
tblFilm)
You can add further criteria to the WHERE clause of both the inner and outer queries to make your queries even more specific. The example below looks for all films which have been released since the year 2000, and whose budget is greater than the highest budget of any film released before the year 2000.
SELECT
FilmName
,FilmBudgetDollars
FROM
tblFilm
WHERE
FilmReleaseDate >= '2000-01-01'
AND
FilmBudgetDollars >
(SELECT
MAX(FilmBudgetDollars)
FROM
tblFilm
WHERE
FilmReleaseDate < '2000-01-01')
The results of the query are shown below:
Three films in our database cost more to make than the most expensive film made before the year 2000.
Technically, we don't need the criterion which asks for films released on or after January 1st 2000. The only films that could be more expensive than the most expensive film made prior to that date must have been made after that date! I've included it here just to show how to use multiple criteria.
Now that you've seen how to use basic criteria with subqueries it's time to take a look at some of the more exotic comparisons using keywords such as ALL, ANY and IN.
You can learn more about this topic on the following Wise Owl courses:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.