View Full Snowflake SnowPro Core COF-C03 Exam Dumps and Practice Test Dumps.

 

Question 261

Which join returns all rows from both tables, including unmatched rows from either side?

1. INNER JOIN
2. LEFT JOIN
3. CROSS JOIN
4. FULL OUTER JOIN

Correct Answer: 4

Explanation

A FULL OUTER JOIN returns matching rows from both tables and also preserves unmatched rows from either side. When a row has no corresponding match, the columns from the other table are represented with NULL values. This is useful when an analysis needs a complete view of both datasets, including records that do not have relationships in the other table. An INNER JOIN keeps only matches, while a LEFT JOIN preserves all rows from only the left side. FULL OUTER JOIN is therefore appropriate for identifying matches and unmatched records together.

Question 262

Which join produces a result containing combinations of every row from one table with every row from another table?

1. CROSS JOIN
2. INNER JOIN
3. LEFT JOIN
4. FULL JOIN

Correct Answer: 1

Explanation

A CROSS JOIN produces the Cartesian product of two tables, meaning every row from the first table is combined with every row from the second table. If one table contains five rows and another contains four rows, the resulting combination can contain twenty rows before any additional filtering. Because the number of rows can grow quickly, CROSS JOIN should be used intentionally. INNER JOIN requires matching conditions, while LEFT and FULL joins preserve rows according to their outer-join semantics. CROSS JOIN is useful when every possible combination is required.

Question 263

Which SQL construct can assign an alias to a table or column for easier reference within a query?

1. Alias
2. Constraint
3. Stage
4. Policy

Correct Answer: 1

Explanation

An alias provides an alternative name for a table or column within a SQL statement. Table aliases are particularly useful when queries involve multiple tables, because they make join conditions shorter and can clarify which table each column belongs to. Column aliases can provide meaningful names for calculated expressions in query results. An alias generally affects the current query rather than permanently renaming the underlying database object. Constraints, stages, and policies serve different purposes. Aliases therefore improve readability and reference management within individual SQL statements.

Question 264

Which SQL keyword removes duplicate rows from a query result?

1. UNIQUE
2. DISTINCT
3. DEDUP
4. REMOVE

Correct Answer: 2

Explanation

DISTINCT removes duplicate rows from the result produced by a SELECT statement based on the selected expressions. It is useful when a query needs unique combinations of values rather than every occurrence present in the underlying data. DISTINCT should not be confused with deleting duplicate records from a table because it affects the query result rather than modifying stored data. UNION also removes duplicates when combining compatible result sets, but DISTINCT is the direct SELECT keyword for eliminating repeated result rows from a query.

Question 265

Which SQL clause restricts the rows considered before grouping and aggregation?

1. HAVING
2. ORDER BY
3. WHERE
4. QUALIFY

Correct Answer: 3

Explanation

WHERE filters individual rows before grouping and aggregation are performed. This makes it appropriate when the query should exclude records based on row-level conditions before calculating aggregate values. HAVING operates after grouping and is generally used to filter grouped results based on aggregate conditions. ORDER BY controls result ordering, while QUALIFY is used for filtering results of window functions. Understanding the processing role of each clause helps users construct efficient and logically correct analytical queries, especially when a statement combines filtering, grouping, aggregation, and window calculations.

Question 266

Which Snowflake SQL clause is specifically useful for filtering the results of window functions without requiring a separate subquery?

1. QUALIFY
2. WHERE
3. GROUP BY
4. FROM

Correct Answer: 1

Explanation

QUALIFY filters query results after window functions have been evaluated. This allows users to write conditions directly against expressions such as ROW_NUMBER(), RANK(), or other window-function results without first wrapping the query in another subquery solely for filtering. WHERE cannot directly serve the same role for a window-function result because window calculations occur later in query processing. GROUP BY creates aggregation groups, while FROM identifies source objects. QUALIFY is therefore especially useful for selecting top-ranked or otherwise conditionally filtered rows produced by window calculations.

Question 267

Which window function assigns a sequential number to rows within each partition according to the specified ordering?

1. RANK()
2. DENSE_RANK()
3. ROW_NUMBER()
4. COUNT()

Correct Answer: 3

Explanation

ROW_NUMBER() assigns a unique sequential number to each row within the window defined by the query. When PARTITION BY is used, numbering restarts for each partition, and ORDER BY determines the sequence. This makes ROW_NUMBER() useful for tasks such as selecting the latest record for each customer when combined with an appropriate ordering and QUALIFY condition. RANK() and DENSE_RANK() handle ties differently and can assign the same ranking to multiple rows. COUNT() calculates counts rather than assigning sequential row identifiers.

Question 268

How does RANK() differ from ROW_NUMBER() when multiple rows have the same ordering value?

1. RANK() can assign the same rank to tied rows
2. RANK() always removes tied rows
3. ROW_NUMBER() assigns identical numbers to all ties
4. ROW_NUMBER() ignores the ORDER BY clause

Correct Answer: 1

Explanation

RANK() can assign the same ranking value to multiple rows when they are tied according to the specified ordering. After a tie, subsequent ranking values can contain gaps. ROW_NUMBER() instead assigns a distinct sequential number to each row, even when ordering values are equal. This distinction matters when selecting top results because using RANK() can return multiple rows for the same ranking position, while ROW_NUMBER() selects a unique sequence. The appropriate function depends on whether tied records should share a ranking or receive separate row numbers.

Question 269

Which window function assigns rankings without gaps after tied values?

1. RANK()
2. ROW_NUMBER()
3. DENSE_RANK()
4. NTILE()

Correct Answer: 3

Explanation

DENSE_RANK() assigns the same rank to tied rows but does not leave gaps in the ranking sequence afterward. For example, if two rows share rank one, the next distinct value receives rank two rather than rank three. RANK() also assigns the same rank to ties but leaves gaps after them. ROW_NUMBER() gives every row a distinct sequential number, while NTILE() distributes rows into a specified number of groups. DENSE_RANK() is useful when equal values should share a rank while maintaining consecutive ranking numbers.

Question 270

Which window-function clause divides rows into separate groups before the window calculation is performed?

1. ORDER BY
2. PARTITION BY
3. GROUP BY
4. HAVING

Correct Answer: 2

Explanation

PARTITION BY divides the rows processed by a window function into independent groups. The window calculation is then performed separately within each partition. For example, ROW_NUMBER() can restart from one for every customer when PARTITION BY customer_id is specified. ORDER BY determines the ordering within the window, while GROUP BY performs traditional aggregation and can reduce rows into groups. HAVING filters grouped results. PARTITION BY is therefore important when calculations such as rankings, running totals, or comparisons must be performed independently for categories.

Question 271

Which aggregate function counts rows in a result set regardless of whether a particular column contains NULL?

1. COUNT(*)
2. COUNT(column_name)
3. SUM(column_name)
4. AVG(column_name)

Correct Answer: 1

Explanation

COUNT() counts rows in the result set, including rows where individual columns contain NULL values. By contrast, COUNT(column_name) counts only rows where the specified column is not NULL. This distinction is important when calculating record counts because the two expressions can produce different results when missing values exist. SUM and AVG perform numeric aggregation and do not serve as general row-counting functions. COUNT() is therefore appropriate when the requirement is to determine how many rows are present after the query’s applicable filtering conditions.

Question 272

Which aggregate function calculates the total of numeric values in a column?

1. AVG()
2. SUM()
3. COUNT()
4. MIN()

Correct Answer: 2

Explanation

SUM() calculates the total of numeric values across the rows included in the aggregation. It is commonly used for measures such as sales amounts, quantities, costs, or other additive metrics. When combined with GROUP BY, SUM() can calculate totals separately for categories such as customers, products, or regions. AVG() calculates an average, COUNT() counts rows or values depending on its form, and MIN() identifies the smallest value. SUM() is therefore the appropriate aggregate function when a cumulative numeric total is required.

Question 273

Which aggregate function calculates the arithmetic mean of numeric values?

1. AVG()
2. SUM()
3. MAX()
4. COUNT()

Correct Answer: 1

Explanation

AVG() calculates the arithmetic mean of numeric values in the selected group or result set. It adds the applicable values and divides the result according to the function’s aggregation semantics. AVG() is commonly used for measures such as average order value, average score, or average duration. SUM() calculates a total, MAX() returns the largest value, and COUNT() counts rows or values. When NULL values are present, aggregate-function behavior regarding those values should also be considered when interpreting the resulting average.

Question 274

Which aggregate function returns the largest value within the selected group?

1. MIN()
2. SUM()
3. MAX()
4. AVG()

Correct Answer: 3

Explanation

MAX() returns the largest value among the values considered by the aggregation. It can be used with numeric, date, and other supported comparable data types depending on the expression. For example, MAX() can identify the most recent date in a group or the highest numeric measurement. MIN() performs the opposite operation by returning the smallest value, while SUM() calculates totals and AVG() calculates averages. MAX() can be combined with GROUP BY when the largest value needs to be determined separately for each category or business entity.

Question 275

Which aggregate function returns the smallest value within a selected group?

1. MIN()
2. MAX()
3. AVG()
4. SUM()

Correct Answer: 1

Explanation

MIN() returns the smallest value among the values included in the aggregation. Depending on the data type, it can identify the lowest numeric value, earliest date, or another minimum supported by Snowflake’s comparison semantics. When used with GROUP BY, MIN() can produce a separate minimum for each group. MAX() identifies the largest value, AVG() calculates the arithmetic mean, and SUM() produces a numeric total. MIN() is useful for finding earliest dates, lowest measurements, minimum prices, and other lower-bound values.

Question 276

Which SQL expression can replace a NULL value with a specified fallback value?

1. NULLIF
2. COALESCE
3. RANK
4. DISTINCT

Correct Answer: 2

Explanation

COALESCE can replace a NULL expression with a fallback value by returning the first non-NULL expression from its arguments. It can accept multiple alternatives, allowing a query to select the first available value from several possible sources. NULLIF has a different purpose because it returns NULL when two expressions are equal. RANK is a window function, and DISTINCT removes duplicate result rows. COALESCE is therefore useful when missing values need a default or alternate representation for reporting, calculations, or downstream processing.

Question 277

Which function converts a string representation into a specified date or timestamp type using parsing rules?

1. TO_DATE
2. COUNT
3. OBJECT_KEYS
4. ARRAY_SIZE

Correct Answer: 1

Explanation

TO_DATE converts an expression to a DATE value using Snowflake’s supported conversion and parsing behavior. It is useful when source data contains dates represented as strings or other compatible forms and the query needs a proper date type for comparisons, filtering, or calculations. Timestamp-specific conversion functions can be used when time-of-day information must also be preserved. COUNT performs aggregation, OBJECT_KEYS works with objects, and ARRAY_SIZE returns array length. TO_DATE is therefore appropriate when the target requirement is a calendar date rather than a general string.

Question 278

Which function can split a string into an array using a specified delimiter?

1. PARSE_JSON
2. SPLIT
3. FLATTEN
4. OBJECT_CONSTRUCT

Correct Answer: 2

Explanation

SPLIT divides a string into an array using a specified separator. This is useful when a single text value contains multiple delimiter-separated elements that need to be processed individually. For example, a comma-separated string can be converted into an array before further semi-structured processing. FLATTEN can then be used when array elements need to be expanded into rows. PARSE_JSON converts compatible text into JSON-based semi-structured data, while OBJECT_CONSTRUCT creates an object. SPLIT therefore provides a direct method for converting delimited text into an array.

Question 279

Which function converts a JSON-formatted string into a Snowflake VARIANT value?

1. PARSE_JSON
2. TO_CHAR
3. SPLIT
4. ARRAY_SIZE

Correct Answer: 1

Explanation

PARSE_JSON parses a JSON-formatted string and returns a VARIANT value representing the resulting semi-structured data. Once parsed, the value can be accessed using path expressions and processed with functions such as FLATTEN. This is useful when JSON arrives as text but needs to be queried as structured semi-structured data. TO_CHAR performs conversion to character representation, SPLIT separates strings into arrays, and ARRAY_SIZE measures array length. PARSE_JSON is therefore the appropriate function for interpreting valid JSON text within Snowflake SQL.

Question 280

Which function returns the number of elements in a Snowflake ARRAY value?

1. OBJECT_KEYS
2. GET_PATH
3. ARRAY_SIZE
4. PARSE_JSON

Correct Answer: 3

Explanation

ARRAY_SIZE returns the number of elements in an ARRAY value. It is useful when queries need to determine array length before processing elements or when validating semi-structured data. OBJECT_KEYS returns the field names contained in an object, GET_PATH retrieves a value from a semi-structured structure using a path, and PARSE_JSON converts JSON text into a VARIANT representation. ARRAY_SIZE therefore directly addresses array cardinality. When working with nested data, it can also help determine whether an array contains values before applying additional transformations.