CODEWITHSUNDEEP
sqloracle-database
Hairdo
Hairdo

Reputation: 71

SQL Execution Sequence of group by and having - Is Tutorial Quiz wrong or am I wrong?

I am doing some study for work on SQL queries.

I came across the following on TutorialsPoint and I'm confused as to why the answer is B and not C.

I have always been told that the GROUP BY Clause comes before HAVING and that HAVING always comes at the end. There is also a lot of documentation online to support my solution, for example: http://www.dba-oracle.com/t_oracle_group_by_having.htm and SQL query, sequence of execution

Can anyone tell me why B is correct in the following example and not C. The tutorial gives B as correct.

Consider the following schema-

STUDENTS(
    student_code, first_name, last_name, 
    email, phone_no, date_of_birth, 
    honours_subject, percentage_of_marks
);

Which of the following query will correctly list the average percentage of marks in each honours subject, when the average is more than 50 percent?

B.

select honours_subject,
    avg(percentage_of_marks)
from students
having avg(percentage_of_marks) > 50.0
group by honours_subject;

C.

select honours_subject,
    avg(percentage_of_marks)
from students
group by honours_subject
having avg(percentage_of_marks) > 50.0;

Upvotes: 2

Views: 476

Answers (3)

Gurwinder Singh
Gurwinder Singh

Reputation: 39507

Even though Oracle does allows B to work, it gives a wrong idea of what actually happens. Additionally, as pointed out by @a_horse_with_no_name, it is not valid as per SQL standard and may not work on other databases.

I think the C is more clear in terms of what is happening i.e. the having clause is evaluated after group by.

EDIT:

For those who think B doesn't work on Oracle, here is a demo:

Demo

Upvotes: 3

Sandeep
Sandeep

Reputation: 806

B query is wrong.

The syntax of query always needs to follow WGHO

W- where clause

G- Group by

H- Having

O- order by

Where and Order by are optional and you can only use having if you use a group by statement

Upvotes: 1

Dan Field
Dan Field

Reputation: 21661

Your tutorial is off.

HAVING qualifies GROUP BY. I suppose it's possible that there's some non-standard SQL parser out there that allows this syntax, but I don't really see why; I'm fairly certain Oracle would not support it (edit: apparently it does - weird). It'd be like allowing someone to put a WHERE clause before SELECT

Upvotes: 1

Related Questions