View the Exhibit and examine the structure of the PROMOTIONS table.
Examine the following two SQL statements:
Statement 1
SQL>SELECT promo_category,SUM(promo_cost)
FROM promotions
WHERE promo_end_date-promo_begin_date > 30
GROUP BY promo_category;
Statement 2
SQL>SELECT promo_category,sum(promo_cost)
FROM promotions
GROUP BY promo_category
HAVING MIN(promo_end_date-promo_begin_date)>30;
Which statement is true regarding the above two SQL statements?
A.
statement 1 gives an error, statement 2 executes successfully
B.
statement 2 gives an error, statement 1 executes successfully
C.
statement 1 and statement 2 execute successfully and give the same output
D.
statement 1 and statement 2 execute successfully and give a different output
What is the mechanism in 2 that gives me min and max?
In 2, it means the minimum of the results of min(promo_end_date – promo_begin_date).
So consider the result of promo_end_date-promo_begin_date, and take the minimum value of those results.
The answer C could be correct, as the result is data dependent. Try running it with all rows having age (promo_end_date – promo_begin_date) greater than 30 days and C is correct. Bad question….
Indeed!
D is the correct answer bcos the first statement applies the condition using WHERE and then doing GROUP BY. But in the second one, it is grouping the results and then applying the condition using HAVING clause. So the results may vary. Just recall the order of statement execution FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY. Hope this would help.
In first statement, WHERE clause filters the difference (promo_end_date-promo_begin_date) based on >30 and then GROUPing. In second statement, HAVING MIN(promo_end_date-promo_begin_date) returns the minimum difference (in fact days) of that particular group. If it is <30 then nothing will come for this group although many of the difference values are higher than 30 in this category. These groups will be omitted. So, the outcomes of these two statements are different.
Though the HAVING clause specifies a condition that is similar to the purpose of a WHERE clause, the two clauses are not interchangeable. Listed below are some differences to help distinguish between the two:
The WHERE clause specifies the criteria which individual records must meet to be selcted by a query. It can be used without the GROUP BY clause. The HAVING clause cannot be used without the GROUP BY clause.
The WHERE clause selects rows before grouping. The HAVING clause selects rows after grouping.
The WHERE clause cannot contain aggregate functions. The HAVING clause can contain aggregate functions.
That may be the end of this article. Right here youll locate some websites that we assume youll appreciate, just click the links.