Subquery is a query inside another query. While this can be a powerful tool on tables, one is however caution as nested queries may cause ambiguity and confusion.
We can create a simple query on the table above; for instance, if we need to display only students enrolled in Computer or Science, our SQL command shall be:
SELECT * FROM STUDENT where subject ='Computer' or subject = 'Science'
or we can use IN from a given list:
SELECT * FROM STUDENT where subject IN ('Computer', 'Science')
Using a subquery, the commands above can be rewritten as:
SELECT * FROM STUDENT where subject in (select subject from STUDENT where subject ='Computer' or subject ='Science')
To display students below 15 years old using subquery, it shall be:
SELECT * FROM STUDENT where age in (select age from STUDENT where age <15)
Just remember that subquery or inner query is performed first before the subquery and that the field or column that is validated must match the resulting set from the inner query.