COF-C02 Question #547: Real Exam Question with Answer & Explanation

Question

Which types of subqueries does Snowflake support? (Select TWO).

Options

• AUncorrelated scalar subqueries in WHERE clauses
• BUncorrelated scalar subqueries in any place that a value expression can be used
• CEXISTS, ANY / ALL, and IN subqueries in WHERE clauses: these subqueries can be uncorrelated
• DEXISTS, ANY / ALL, and IN subqueries in where clauses: these subqueries can be correlated only
• EEXISTS, ANY /ALL, and IN subqueries in WHERE clauses: these subqueries can be correlated or

Explanation

Snowflake supports a variety of subquery types, including both correlated and uncorrelated subqueries. The correct answers are B and E, which highlight Snowflake's flexibility in handling subqueries within SQL queries. Uncorrelated Scalar Subqueries: These are subqueries that can execute independently of the outer query. They return a single value and can be used anywhere a value expression is allowed, offering great flexibility in SQL queries. EXISTS, ANY/ALL, and IN Subqueries: These subqueries are used in WHERE clauses to filter of the main query based on the presence or absence of matching rows in a subquery. Snowflake supports both correlated and uncorrelated versions of these subqueries, providing powerful tools for complex data analysis scenarios. Examples and Usage: Uncorrelated Scalar Subquery: SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees); Correlated EXISTS Subquery: SELECT * FROM orders o WHERE EXISTS (SELECT 1 FROM customer c WHERE c.id = o.customer_id AND c.region = 'North America');

No community discussion yet for this question.