Week 3 – Cleaning data with SQL – Shuffle Q/A 3

34. In SQL databases, what function can be used to convert data from one datatype to another?

  • CAST
  • LENGTH
  • TRIM
  • SUBSTR

35. After a company merger, a data analyst receives a dataset with billions of rows of data. They need to leverage this data to identify insights for upper management. What tool would be most efficient for the analyst to use?

  • Spreadsheet
  • Word processor
  • SQL
  • CSV

36. You are working with a database table that contains customer data. The table includes columns about customer location such as city, state, country, and postal_code. The state names are abbreviated. You want to check for state names that are greater than 2 characters long.

You write the SQL query below. Add a LENGTH function that will return any state names that are greater than 2 characters long.

What country is in row 1 of your query result?

NOTE: The query index starts at 1 not 0.

  • Ireland
  • India
  • France
  • Chile

37. You are working with a database table that contains employee data. The table includes columns about employee location such as city, state, country, and postal_code. You use the SUBSTR function to retrieve the first 3 characters of each last_name, and use the AS command to store the result in a new column called new_last_name.

You write the SQL query below. Add a statement to your SQL query that will retrieve the first 3 characters of each last_name and store the result in a new column as new_last_name.

NOTE: The three dots (...) indicate where to add the statement.

NOTE: SUBSTR takes in three arguments being column, starting_index, ending_index

What employee ID number is in row 8 of your query result?

NOTE: The query index starts at 1 not 0.

  • 7
  • 3
  • 1
  • 8

38. A data analyst runs a SQL query to extract some data from a database for further analysis. How can the analyst save the data? Select all that apply.

  • Download the data as a spreadsheet.
  • Use the UPDATE query to save the data.
  • Create a new table for the data.
  • Run a SQL query to automatically save the data.

39. A data analyst notices their Boolean column is incorrectly storing True/False values as strings. What function can the analyst use to convert the data type from a string to Boolean?

  • CAST
  • TRIM
  • LENGTH
  • SUBSTR

40. A data analyst is cleaning survey data. The results for an optional question contain many nulls. What function can the analyst use to eliminate the null values from the results?

  • COALESCE
  • CONCAT
  • CAST
  • LENGTH

41. A data analyst is analyzing medical data for a health insurance company. The dataset contains billions of rows of data. Which of the following tools will handle the data most efficiently?

  • A word processor
  • CSV
  • A spreadsheet
  • SQL

42. You are working with a database table that contains invoice data. The table includes columns for invoice_id and billing_city. You want to remove duplicate entries for billing_city and sort the results by invoice_id.

You write the SQL query below. Add a DISTINCT clause that will remove duplicate entries from the billing_city column.

NOTE: The three dots (...) indicate where to add the clause.

What billing city appears in row 15 of your query result?

NOTE: The query index starts at 1 not 0.

  • London
  • Reno
  • Santiago
  • Oslo

Shuffle Q/A 4

43. You are working with a database table that contains invoice data. The table includes columns about billing location such as billing_city, billing_state, and billing_postal_code. You use the SUBSTR function to retrieve the first 4 numbers of each billing_postal_code, and use the AS command to store the result in a new column called new_postal_code.

You write the SQL query below. Add a statement to your SQL query that will retrieve the first 4 numbers of each billing postal code and store the result in a new column as new_postal_code.

NOTE: The three dots (...) indicate where to add the statement.

NOTE: SUBSTR takes in three arguments being column, starting_index, ending_index

What invoice id is located in row 4?
NOTE: The query index starts at 1 not 0.

  • 32
  • 104
  • 52
  • 206

44. In which of the following situations would a data analyst use SQL instead of a spreadsheet? Select all that apply.

  • When quickly pulling information from many different sources in a database
  • When recording queries and changes throughout a project
  • When working with a huge amount of data
  • When using the COUNTIF function to find a specific piece of information
Previous
Next

Devendra Kumar

Project Management Apprentice at Google

Leave a Reply