GATE2001-21-b

Question

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.

Answer 1

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.

Comments

What are some other ways of writing it?

Answer 2

SELECT regno
FROM examinee
HAVING score > avg(score)

Comments

As far as I know Having keyword can only be used with aggregate functions (like AVG,COUNT,MIN,MAX). So this is not correct.

---

SELECT regno
FROM examinee
GROUP BY score
HAVING score > avg(score)