D465 - Data Applications

R and Python similarities

Both widely used in data science with extensive libraries.

Advantage of storing code in R

Allows reproducibility and collaboration among analysts.

R unique challenges

Steeper learning curve, limited web dev capabilities.

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 bigquery-public-data.plants.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 call.employee_id, call.start_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_items.product_id) > 100

HAVING COUNT(order_items.product_id) < 100

HAVING (order_items.product_id) > 100

HAVING (order_items.product_id > 100)

HAVING COUNT(order_items.product_id) > 100

Fill in the blank: A data professional uses the SQL _____ statement to return records that meet conditions by including an if/then statement in a query.

CASE

HAVING

WHEN

CONCAT

CASE

Absolute reference

A reference within a function that is locked so that rows and columns won't change if the function is copied

Aggregation

The process of collecting or gathering many separate pieces into a whole

Aliasing

Temporarily naming a table or column in a query to make it easier to read and write

COUNT DISTINCT

A SQL function that only returns the distinct values in a specified range

Data aggregation

The process of gathering data from multiple sources and combining it into a single, summarized collection

INNER JOIN

A SQL function that returns records with matching values in both tables

JOIN

A SQL function that is used to combine rows from two or more tables based on a related column

LEFT JOIN

A SQL function that will return all the records from the left table and only the matching records from the right table

LIMIT

A SQL clause that specifies the maximum number of records returned in a query

MATCH

A spreadsheet function used to locate the position of a specific lookup value

OUTER JOIN

A SQL function that combines RIGHT and LEFT JOIN to return all matching records in both tables

RIGHT JOIN

A SQL function that will return all records from the right table and only the matching records from the left.

Subquery

A SQL query that is nested inside a larger query

VALUE

A spreadsheet function that converts a text string that represents a number to a numeric value

You use VLOOKUP in a spreadsheet containing weather data. While searching for rainfall levels in Chicago, you encounter an error because your spreadsheet value has a trailing space after the city name. What function should you use to eliminate this space?

CUT

TRIM

NOSPACE

VALUE

TRIM

Fill in the blank: The spreadsheet function _____ can be used to tally the number of cells in a range that are not empty.

RANGE

COUNT

COUNT DISTINCT

RETURN

COUNT

A junior data analyst writes the following formula: =AVERAGE($C$1:$C$100). What are the purposes of the dollar signs ($)? Select all that apply.

Average the values in cells C1 to C100 regardless of whether the formula is copied.

Ensure rows and columns do not change.

Create an absolute reference.

Perform the calculation more efficiently.

Create an absolute reference.

Ensure rows and columns do not change.

What will this query return?

1. SELECT *

2. FROM Books_table

3. LEFT JOIN Biography_table

All records in the biography table and any matching rows from the books table

All records in both the books table and the biography table

All rows from the books table joined together with the biography table

All records in the books table and any matching rows from the biography table

All records in the books table and any matching rows from the biography table

A spreadsheet cell contains the coldest temperature ever recorded in Austria: -37 degrees Celsius. Which function would convert that to Fahrenheit?

=CONVERT(-37, F, C)

=CONVERT(-37, C, F)

=CONVERT(-37, "F", "C")

=CONVERT(-37, "C", "F")

=CONVERT(-37, "F", "C")

Fill in the blank: A data professional uses _____ in order to ensure spreadsheet values are static, rather than carrying over a preexisting formula or function.

conditional formatting

formatting

paste values only

data validation

paste values only

Which data-validation menu option highlights data entry errors to ensure spreadsheet formulas continue to run correctly?

Reject invalid inputs

Forbid entry

Deny text

Remove validation

Reject invalid inputs

A data analyst selects Format Cells and the option Text Is Exactly Baseball. This changes the color of all the cells that contain the word "Baseball." What spreadsheet tool is the analyst using?

Conditional formatting

Filtering

Data validation

CONVERT

Conditional formatting

Which SQL function combines groups of text strings from multiple cells in order to create a new string?

CONCAT

COMBINE

CONSOLIDATE

CONNECT

CONCAT

What SQL clause can be added to this query to ensure only the first 50 results are returned?

1. SELECT *

2. FROM Leaf_Database

3. WHERE tree_type = maple

LIMIT 50

FIRST 50

RETURN 50

ONLY 50

LIMIT 50

A data professional runs a query that will return a dataset containing numbers out to five decimal places. Which SQL function will limit the records to two decimal places?

LEN

NUM

LIMIT

ROUND

ROUND

Which function will return the number of characters in spreadsheet cell F8 in order to confirm it contains exactly 15 characters?

=LEN(F8, 15)

=LEN(15)

=LEN(F8)

=LEN(15, F8)

=LEN(F8)

ROUND

A SQL function that returns a number rounded to a certain number of decimal places

Fill in the blank: A junior data analyst at a healthcare organization uses the spreadsheet _____ function to locate specific characters from insurance provider account numbers.

FROM

FIND

WHERE

IDENTIFY

FIND

A data analyst works with a spreadsheet containing product information that often has very long text strings. To check for consistency, they use a function to count the number of characters in cell P12. What is the correct syntax of the function?

=LEN(P:12)

=LEN(P,12)

=LEN(P:P12)

=LEN(P12)

=LEN(P12)

You prepare a project tracker spreadsheet. Next to each project is the name of the team member responsible. What spreadsheet tool will create a drop-down list with team member names to save you time when assigning the projects?

Data validation

Conditional formatting

Pop-up menus

Find

Data validation