View the Exhibit and examine the structure of the PRODUCT_INFORMATION table.
Which two queries would work? (Choose two.)
A.
SELECT product_name
FROM product_information
WHERE list_price = (SELECT AVG(list_price)
FROM product_information);
B.
SELECT product_status
FROM product_information
GROUP BY product_status
WHERE list_price < (SELECT AVG(list_price)
FROM product_information);
C.
SELECT product_status
FROM product_information
GROUP BY product_status
HAVING list_price > (SELECT AVG(list_price)
FROM product_information);
D.
SELECT product_name
FROM product_information
WHERE list_price < ANY(SELECT AVG(list_price)
FROM product_information
GROUP BY product_status);
the option a and d is correct
It seems Answer B will work too.
I ran the query on SQL Developer and successfully queried.
It is confused. Please explain why Answer B is not correct choice. Thanks.
B. The GROUP BY comes before WHERE. It is incorrect
select sal from emp where sal < any (select avg(sal) from emp group by deptno);
select sal from emp where sal < any (select avg(sal) from emp group by deptno);
select sal from emp group by deptno where sal < any (select avg(sal) from emp);
select sal from emp where sal < (select avg(sal) from emp) group by sal;
select empno from emp where sal < (select avg(sal) from emp) group by empno ;
select deptno from emp where sal < (select avg(sal) from emp) group by deptno ;
select sal from emp
where sal < (select avg(sal) from emp);
select sal from emp
having sal < (select avg(sal) from emp group by job);
Hi Please I want to no while the Answer C is false. Plese can you explain to me
C. Aggregate functions (SUM, AVG, MAX, ….) come after HAVING clause. So HAVING list_price > is incorrect. It should be HAVING SUM(list_price) > .. :
a and D is correct.
A and D is correct
B is incorrect syntax order is wrong
C is incorrect because having should have functions like SUM, COUNT, MIN, MAX, or AVG functions.
C is also incorrect because, if an aggregate function is not used in the HAVING clause, the column used in HAVING must be then also part of the SELECT and GROUP BY clauses.
A and D
A and D