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 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'.
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
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.
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
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...
I agree with you, but this would be better docs.
https://docs.oracle.com/database/121/SQLRF/expressions014.htm#SQLRF52093
this is 12c docs.
upvoted 2 times
...
...
Log in to ExamTopics
Sign in:
Community vote distribution
A (35%)
C (25%)
B (20%)
Other
Most Voted
A voting comment increases the vote count for the chosen answer by one.
Upvoting a comment with a selected answer will also increase the vote count towards that answer by one.
So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.
alelejaja
2 weeks, 4 days agoAshkush
2 months agoNSilva
1 month, 3 weeks agoalelejaja
2 weeks, 4 days agojm9999
8 months, 2 weeks agojm9999
8 months, 1 week agoTofOra
4 months, 1 week agoTofOra
4 months, 1 week agozouve
11 months, 1 week agoChandra1104
1 year, 2 months agoJ4vi
1 year, 1 month agoholdfaststrongly
1 year, 8 months agoArtur1991
1 year, 9 months agoheoj10272
1 year, 8 months ago