D465 Data Applications - Set 2 - Part 1

Correct Answer: COUNT

The COUNT function returns the total number of rows, including duplicates, in the specified column or table. COUNT DISTINCT would return only unique rows.

Correct Answer: TRUNCATE

TRUNCATE removes all rows from a table but keeps the table structure intact. DELETE removes specific rows, and DROP removes the table entirely.

Correct Answer: To filter records after grouping

The HAVING clause is used to filter rows after a GROUP BY operation, whereas WHERE is used to filter rows before grouping.

Correct Answer: MINIFS

The MINIFS function in Excel finds the smallest value in a range that meets one or more criteria.

Correct Answer: BETWEEN

The BETWEEN clause filters the result set to return only the rows where a value falls within a specified range.

Correct Answer: COUNTBLANK

The COUNTBLANK function counts the number of empty cells in a specified range.

Correct Answer: cor()

The cor() function computes the correlation coefficient between two numeric variables, indicating the strength of their relationship.

Correct Answer: ORDER BY

The ORDER BY clause sorts the result set in ascending or descending order based on one or more columns.

Correct Answer: All of the above

Pivot tables are built using rows, columns, values, and sometimes filters to aggregate and summarize data.

Correct Answer: na.omit()

The na.omit() function removes rows that contain missing values from a dataframe in R.

Correct Answer: UNION

UNION combines the result sets of two or more SELECT queries into one result set, excluding duplicate rows.

Correct Answer: HLOOKUP

HLOOKUP searches for a value in the first row of a horizontal table and returns a value in the same column based on the row index.

Correct Answer: mean()

The mean() function calculates the average of numeric values in a vector, and the na.rm argument can be used to exclude missing values.

Correct Answer: GROUP BY

The GROUP BY clause groups rows with the same values into summary rows, often used with aggregate functions like COUNT or SUM.

Correct Answer: SUMIF

The SUMIF function adds all numbers in a range that meet a single specified condition.

Correct Answer: CONCAT

The CONCAT function in SQL is used to combine two or more strings into one.

Correct Answer: sqrt()

The sqrt() function in R returns the square root of a number.

Correct Answer: Removes duplicate rows

The DISTINCT keyword removes duplicate rows from the result set, ensuring that only unique values are returned.

Correct Answer: COUNTIF

The COUNTIF function counts the number of cells that meet a specified condition in a range.

Correct Answer: sd()

The sd() function calculates the standard deviation, which measures the dispersion of data points from the mean.