Which three are true about scalar subquery expressions? (Choose three.)
Which three are true about scalar subquery expressions? (Choose three.)
Scalar subqueries can be nested within other scalar subqueries or within other SQL expressions. A scalar subquery expression that returns zero rows evaluates to NULL. Scalar subqueries cannot be used in GROUP BY clauses because they do not return a single value that can be used to group rows.
A. They can be nested. Scalar subqueries can be nested within other scalar subqueries or within other SQL expressions. E. A scalar subquery expression that returns zero rows evaluates to NULL. If a scalar subquery returns no rows, it evaluates to NULL. F. They cannot be used in GROUP BY clauses. Scalar subqueries cannot be used in GROUP BY clauses because they do not return a single value that can be used to group rows.
AEF are the correct answers https://docs.oracle.com/cd/B19306_01/server.102/b14200/expressions010.htm
I agree with you, but this would be better docs. https://docs.oracle.com/database/121/SQLRF/expressions014.htm#SQLRF52093 this is 12c docs.
According to both of your links, wouldn't B also correct because INSERT is part of a DML statement? B says "CANNOT" be used in the value of an INSERT statement. Double negative makes the statement positive. Just saying...
Its ABE. A. They can be nested. --> True B. They cannot be used in the VALUES clause of an INSERT statement. --> True C. A scalar subquery expression that returns zero rows evaluates to zero. --> False D. They can be used as default values for columns in a CREATE TABLE statement.--> False E. A scalar subquery expression that returns zero rows evaluates to NULL. --> True F. They cannot be used in GROUP BY clauses.--> False https://www.oratable.com/scalar-subquery/
AEF There is no way you could use it in GROUP BY clauses, but it says nothing about VALUES clause. https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Scalar-Subquery-Expressions.html#GUID-475D80C3-C873-4475-AB1A-8837C5CF8CE4
I don't think they can be nested. Tried: select employee_id, (select department_name, (select city from locations l where l.location_id=d.location_id) from departments d where d.department_id=e.department_id) from employees e; But got 'too many values error'.
Changing my answer to AEF. I found B to be incorrect. Still not sure how to nest them (A).
select order_id, order_total_value, (select avg(order_total_value from orders where order_date > (select sysdate - 30 from dual)) as average_30_days, order_total_value - (select avg(order_total_value from orders where order_date > (select sysdate - 30 from dual)) as above_average_30_days from orders where order_total_value > (select avg(order_total_value) from orders where order_date > (select sysdate - 30 from dual))
This query is returning more than 1 row: select city from locations l where l.location_id=d.location_id so 'too many values error', try select DISTINCT city from locations l where l.location_id=d.location_id
Why is D incorrect?
D. They can be used as default values for columns in a CREATE TABLE statement. >> True, but there might be one of two issues. 1) The website has marked it incorrectly as "False" 2) It is an SQL exam, where wording matters. "You can use it as a default value (IN columns) / (for ROWS) during CREATE TABLE statement"
Sorry to say I don't share your point here. D is falso since, as official doc from Oracle highlights, scalar subqueries are not valid expressions as default values for columns. https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Scalar-Subquery-Expressions.html#GUID-475D80C3-C873-4475-AB1A-8837C5CF8CE4
I also think AEF are the correct answers