WGU D465 - Data Applications OA Questions and Correct Answers

R and Python similarities - Both widely used in data science with extensive libraries. R unique challenges - Steeper learning curve, limited web dev capabilities. Advantage of storing code in R - Allows reproducibility and collaboration among analysts. Programming languages and use cases - Python: web dev, data science, ML, automation. MIN function in spreadsheets - Returns the smallest value in a cell range. COUNTIF function in spreadsheets - Counts cells meeting a specified condition. Pivot table elements - Rows, columns, values, filters for data aggregation. SELECT command in SQL - Retrieves data from one or more database tables. JOIN commands in SQL - INNER, LEFT, RIGHT, FULL OUTER JOIN types. Delimiter for code chunks - Triple backticks or markup to define code sections. Output formats for documents - HTML, PDF, Word (docx), Markdown. Presentation formats - Slides, Dashboards, Interactive web apps. Knit button in R - Compiles R Markdown into desired output formats. Symbol for comments in R - Pound sign (`#`) precedes comments in R. Nested function usage - Simplifies operations, improves code readability. Logical operators - AND (&&), OR (||), NOT (!). Advantage of tidyverse - Cohesive data manipulation packages in R. Functions in ggplot2 - ggplot(), geom_point(), geom_line(), aes(). Plus sign in ggplot2 - Adds layers to ggplot objects for customization. Common errors in ggplot2 - Incorrect aesthetic mappings, syntax misunderstanding. Basic aesthetic attributes in ggplot2 - x-axis, y-axis, color for plot customization. Smoothing line usage - Visual representation of trends in data. dplyr filter() function - Subset rows based on specific conditions in R. VLOOKUP function in spreadsheets - Searches for values in a vertical column. Locking table array in VLOOKUP - Prevents range changes for formula accuracy. Different JOIN functions in SQL - INNER, LEFT, RIGHT, FULL OUTER JOIN types. COUNT vs. COUNT DISTINCT in SQL - COUNT: total rows, COUNT DISTINCT: unique values. SELECT statement usage in SQL - Retrieving data from one or more tables. FROM statement in SQL - Specifies tables for data retrieval in SQL queries. Tibbles vs. data frames - Modernized data frames with improved features. Main operators in R - Arithmetic, relational, logical, assignment operators. sample() function for biased data - Creates random unbiased data samples. Fill in the blank: The spreadsheet function _____ returns the number of cells within a range that match a specified value. COUNTIF ARRAY COUNT DISTINCT VALUE - COUNTIF What is an example of an array in a spreadsheet? All cells with number values Cells D7, E14, and F20 The values in cells B2 through B31 All cells with values greater than 100 - The values in cells B2 through B31 Which function will calculate the sum of the products of the corresponding items in the arrays M1:M4 and P1:P4? =SUMPRODUCT(M1:M4, P1:P4) =MULTIPLY(M1:M4, P1:P4) =PRODUCT(M1:M4, P1:P4) =ARRAY(M1:M4, P1:P4) - =SUMPRODUCT(M1:M4, P1:P4) Fill in the blank: The _____ of a pivot table organize and group the selected data horizontally. columns rows filters values - rows Which spreadsheet tool finds an average value using values generated within a pivot table? Filter Data validation Conditional formatting Calculated field - Calculated field In a SQL query, what is the purpose of the modulo (%) operator? Return the remainder of a division calculation Convert a decimal to a percent Apply an exponent to a value Find the square root of a number - Return the remainder of a division calculation A data professional writes a query that uses more than one arithmetic operator. What do they add to the query to control the order of the calculations? Dollar sign ($) Parenthesis [()] Colon [:] Backslash [/] - Parenthesis [()] Spreadsheet cell D5 contains the decimal .74. Which formula will convert it to a percentage? =D5%100 =D5,100 =D5(100) =D5*100 - =D5*100 What will GROUP BY do in this query? GROUP BY apartment; SELECT apartment, AVG(price) AS apt_prices FROM rent_data Group together the apartment and rent_data tables Group only the rows in the apt_prices table Group together the rent_data by apartment prices Group the rows in the table by apartment - Group the rows in the table by apartment When working with a temporary table in a SQL database, at what point will the table be automatically deleted? After completing all calculations in the table After running a report from the table After ending the session in the SQL database After running the query in the SQL database - After ending the session in the SQL database What data will appear in the temporary table created through this query? 1. WITH plant_variety AS ( 2. SELECT * 3. FROM s.African_species 4. WHERE daily_growth_rate_percentage = 0.05 5. ) Plant varieties that grow exactly 0.05 percent per day A random subset of African plant species Plant varieties that are equal to 0.05 inches tall All plant species that exist in the public dataset - Plant varieties that grow exactly 0.05 percent per day Fill in the blank: A data analyst uses _____ to copy data from one table into a temporary table without adding the new table to the database. TEMP COPY TO WITH SELECT INTO - SELECT INTO Why might a data professional add a CREATE TABLE statement to a temporary table? Include metadata about the data in the table Automate calculations in the table Give multiple people access to the table Create a second table within the temporary table - Give multiple people access to the table Array - A collection of values in spreadsheet cells Calculated field - A new field within a pivot table that carries out certain calculations based on the values of other fields Data security - Protecting data from unauthorized access or corruption by adopting safety measures Data validation process - The process of checking and rechecking the quality of data so that it is complete, accurate, secure and consistent GROUP BY - A SQL clause that groups rows that have the same values from a table into summary rows Modulo - An operator (%) that returns the remainder when one number is divided by another Profit margin - A percentage that indicates how many cents of profit has been generated for each dollar of sale Summary table - A table used to summarize statistical information about data SUMPRODUCT - A function that multiplies arrays and returns the sum of those products Temporary table - A database table that is created and exists temporarily on a database server Underscores - Lines used to underline words and connect text characters A data analyst at a recycling company manually recalculates the new column materials_sorter. They want to identify any rows with values that do not match those in the original column, compost_sorter. Which SQL clauses would enable them to do so? Select all that apply. WHERE materials_sorter !! compost_sorter WHERE materials_sorter >< compost_sorter WHERE materials_sorter <> compost_sorter WHERE materials_sorter != compost_sorter - WHERE materials_sorter <> compost_sorter WHERE materials_sorter != compost_sorter Fill in the blank The SQL command _____ combines table rows with the same values into summary rows. WITH GROUP BY TABLE ORDER BY - GROUP BY What will this spreadsheet function return? =SUMIF(K20:K70, ">=50", L20:L70) The sum of all values in cells L20 to L70 that correspond to values in cells K20 to K70 that are greater than or equal to 50. The sum of any values in cells K20 to K70 and cells L20 to L70 that are greater than or equal to 50. The sum of all values in cells K20 to K70 for which the value in cells L20 to L70 is greater than or equal to 50. The count of the number of cells in the array K20:K70 that have a value greater than or equal to 50. - The sum of all values in cells L20 to L70 that correspond to values in cells K20 to K70 that are greater than or equal to 50. Which of the following statements accurately describe pivot tables? Select all that apply. The calculated field in a pivot table is used to apply filters based on specific criteria. The values in a pivot table are used to calculate and count data. A pivot table is a data summarization tool. The rows of a pivot table organize and group data horizontally. - The values in a pivot table are used to calculate and count data. A pivot table is a data summarization tool. The rows of a pivot table organize and group data horizontally. A data analyst at an engineering company calculates the number of spreadsheet rows that contain the value turbine. Which function do they use? =COUNTIF(C1:C100,"turbine") =COUNTIF(C1:C100,turbine) =COUNTIF(C1:C100,"=turbine") =COUNTIF(turbine=C1:C100) - =COUNTIF(C1:C100,"turbine") Fill in the blank: To copy data from one table into a _____, a data professional uses the SELECT INTO statement. temporary table new table defined function table view - temporary table Fill in the blank: To combine rows from two or more tables based on a _____ column, data professionals use the SQL JOIN clause. unique dissimilar foreign related - related A data analyst wants to retrieve only records from a database that have matching values in two different tables. Which JOIN function should they use? OUTER JOIN RIGHT JOIN INNER JOIN LEFT JOIN - INNER JOIN You write a SQL query that will count values in a specified range. Which function should you include in your query to only count each value once, even if it appears multiple times? COUNT RANGE COUNT DISTINCT COUNT COUNT VALUES - COUNT DISTINCT Fill in the blank: Aliasing involves _____ naming a table or column to make a query easier to read and write. permanently perpetually temporarily privately - temporarily Which of the following queries contain subqueries? Select all that apply. 1. SELECT call 2. FROM recordings 3. ORDER BY yee_id, _time 1. SELECT employee_id 2. FROM employees 3. WHERE department_id IN (SELECT department_id 4. FROM departments 5. WHERE location_id = 1000) 1. SELECT product_name, 2. CASE 3. WHEN price < 10 THEN 'Low price' 4. WHEN price >= 10 AND price < 20 THEN 'Medium price' 5. ELSE 'High price' 6. END AS price_category 4. FROM products 1. SELECT price 2. FROM sales 3. WHERE price = (SELECT MAX (salary) 4. FROM sales) - 1. SELECT price 2. FROM sales 3. WHERE price = (SELECT MAX (salary) 4. FROM sales) 1. SELECT employee_id 2. FROM employees 3. WHERE department_id IN (SELECT department_id 4. FROM departments 5. WHERE location_id = 1000) When working with subqueries, which query will execute first? Rightmost Outermost Innermost Leftmost - Innermost Which HAVING clause indicates to only retrieve products that have been sold more than 100 times? HAVING COUNT(order_ct_id) > 100 HAVING COUNT(order_ct_id) < 100 HAVING (order_ct_id) > 100 HAVING (order_ct_id > 100) - HAVING COUNT(order_ct_id) > 100