D465 Data Applications - Set 1 - Part 1

Correct Answer: COUNT DISTINCT

The COUNT DISTINCT function is used in SQL to return only unique values from the specified column. The regular COUNT function would return the total number of values, including duplicates.

Correct Answer: CREATE

The CREATE command in SQL is used to create a new table in the database. ALTER is used to modify existing tables, while INSERT and UPDATE are used to add and modify data in the table.

Correct Answer: To search for a value vertically

The VLOOKUP function is used to search for a value in the first column of a vertical table or range and return a value in the same row from a specified column.

Correct Answer: COUNTIFS

The COUNTIFS function allows an analyst to count the number of cells that meet multiple conditions. SUMIFS would sum values based on conditions, while COUNTIF and SUMIF only apply one condition.

Correct Answer: INNER JOIN

The INNER JOIN function returns only the rows where there is a match in both tables. LEFT JOIN and RIGHT JOIN include all rows from one table, and FULL OUTER JOIN returns all rows from both tables.

Correct Answer: clean_names()

The clean_names() function from the janitor package is used to clean column names by removing special characters and spaces.

Correct Answer: GROUP BY

The GROUP BY clause groups rows that have the same values into summary rows. It is often used with aggregate functions like SUM, COUNT, and AVG.

Correct Answer: SUMIF

SUMIF adds up all the numbers in a range that meet a single condition specified by the user.

Correct Answer: ggplot2

The ggplot2 package is primarily used for creating advanced data visualizations in R, including scatterplots, line charts, and bar charts.

Correct Answer: LEFT JOIN

LEFT JOIN returns all rows from the left table and the matching rows from the right table. If no match exists, NULL values are returned for columns from the right table.

Correct Answer: Cross-field validation

Cross-field validation ensures that multiple fields in a dataset meet specific conditions or dependencies.

Correct Answer: WHERE

The WHERE clause in SQL is used to filter records that meet a specified condition.

Correct Answer: MIN

The MIN function returns the smallest value from a specified range of numbers.

Correct Answer: Using parentheses ()

Parentheses are used in SQL to control the order of operations in mathematical expressions, ensuring the calculations are performed in the correct sequence.

Correct Answer: ALTER

The ALTER command in SQL allows a user to modify an existing table by adding, deleting, or modifying columns.

Correct Answer: typeof()

The typeof() function in R is used to determine the type of a variable or data structure.

Correct Answer: LIKE

The LIKE operator in SQL is used to search for records that match a specified pattern. It is often used with wildcard characters like '%' and '_'.

Correct Answer: mean()

The mean() function calculates the average of all values in a vector or dataset.

Correct Answer: SUMPRODUCT

SUMPRODUCT multiplies corresponding values in arrays and returns the sum of those products.

Correct Answer: geom_point()

The geom_point() function in the ggplot2 package is used to create scatterplots by plotting points based on the specified x and y coordinates.