Consider a relation examinee (regno, name, score), where regno is the primary key to score is a real number.
Write an SQL query to list the regno of examinees who have a score greater than the average score.
There are many ways to write a query, all of which will perform the same task. One way is:
SELECT regno FROM examinee WHERE score > (SELECT AVG(score) FROM examinee )
Here, the inner query is returning the average of the scores. And outer query selects those regno that have a score greater than this average.
SELECT regno FROM examinee HAVING score > avg(score)
SELECT regno FROM examinee GROUP BY score HAVING score > avg(score)