Module 3: Data Cleaning with SQL Answers (Part 1: Q1–15)

Looking answers for ‘process data from dirty to clean module 3 challenge’?

In this post, I provide accurate answers and detailed explanations for Module 3: Data cleaning with SQL of Course 4: Process Data from Dirty to Clean – Google Data Analytics Professional Certificate.

Whether you’re preparing for quizzes or brushing up on your knowledge, these insights will help you master the concepts effectively. Let’s dive into the correct answers and detailed explanations for each question.

Here, we’ll walk through questions 1 to 15 with detailed explanations to support your learning.

To find answers to the remaining questions, check out the full module breakdown below:

Test your knowledge on SQL

Practice Quiz

1. Which of the following are benefits of using SQL? Select all that apply.

  • SQL can be used to program microprocessors on database servers.
  • SQL can handle huge amounts of data. ✅
  • SQL offers powerful tools for cleaning data. ✅
  • SQL can be adapted and used with multiple database programs. ✅

Explanation:

  • SQL is designed to manage large datasets efficiently, which is why it’s widely used in data analytics and database management.
  • SQL has tools like TRIM(), REPLACE(), and conditional logic that make it useful for data cleaning.
  • SQL is versatile and works across different database systems (e.g., MySQL, PostgreSQL, Oracle) with minor adaptations.
  • Incorrect option: SQL is not used for programming microprocessors; it is a query language for databases.

2. Which of the following tasks can data analysts do using both spreadsheets and SQL? Select all that apply.

  • Join data ✅
  • Perform arithmetic ✅
  • Use formulas ✅
  • Process huge amounts of data efficiently

Explanation:

  • Both spreadsheets (e.g., Excel) and SQL can join data through merging tables or using lookup functions.
  • Arithmetic operations such as addition, subtraction, and aggregation are possible in both.
  • Formulas or functions (e.g., SUM() in Excel, SUM() in SQL) can be used in both tools.
  • Incorrect option: While SQL excels at handling large datasets, spreadsheets struggle with huge data due to memory limitations.

3. SQL is a language used to communicate with databases. Like most languages, SQL has dialects. What are the advantages of learning and using standard SQL? Select all that apply.

  • Standard SQL is automatically translated by databases to other dialects.
  • Standard SQL works with a majority of databases.  ✅
  • Standard SQL requires a small number of syntax changes to adapt to other dialects. ✅
  • Standard SQL is much easier to learn than other dialects.

Explanation:

  • Standard SQL is compatible with most relational databases, which makes it a valuable skill for analysts.
  • Adapting standard SQL to dialects like T-SQL (Microsoft SQL Server) or PL/SQL (Oracle) usually requires minor syntax adjustments.
  • Incorrect options:
    • Standard SQL is not automatically translated by databases into other dialects.
    • Learning standard SQL is not necessarily easier than learning other dialects but provides a strong foundational understanding.

Test your knowledge on SQL queries

Practice Quiz

4. Which of the following SQL functions can data analysts use to clean string variables? Select all that apply.

  • LENGTH
  • SUBSTR ✅
  • TRIM ✅
  • COUNTIF

Explanation:
 The SUBSTR and TRIM functions are used to clean string variables in SQL. SUBSTR extracts a substring from a string, while TRIM removes leading and trailing spaces.

5. You are working with a database table that contains data about playlists for different types of digital media. The table includes columns for playlist_id and name. You want to remove duplicate entries for playlist names and sort the results by playlist ID.

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

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

What playlist name appears in row 6 of your query result?

  • Music Videos ✅
  • Audiobooks
  • Movies
  • TV Shows

Explanation:
 The DISTINCT clause removes duplicate entries from the name column, ensuring each playlist name appears only once. The query will return the sorted list, with “Music Videos” appearing in row 6.

6. You are working with a database table that contains data about music albums. The table includes columns for album_id, title, and artist_id. You want to check for album titles that are less than 4 characters long.

You write the SQL query below. Add a LENGTH function that will return any album titles that are less than 4 characters long.

What album ID number appears in row 3 of your query result?

  • 239
  • 236
  • 182 ✅
  • 131

Explanation:
 The LENGTH(title) < 4 condition checks for album titles that are less than 4 characters long. The album with ID 182 appears in row 3 of the result.

7. You are working with a database table that contains customer data. The table includes columns about customer location such as city, state, and country. You want to retrieve the first 3 letters of each country name. You decide to use the SUBSTR function to retrieve the first 3 letters of each country name, and use the AS command to store the result in a new column called new_country.

You write the SQL query below. Add a statement to your SQL query that will retrieve the first 3 letters of each country name and store the result in a new column as new_country.

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

What customer ID number appears in row 2 of your query result?

  • 47
  • 55 ✅
  • 3
  • 28

Explanation:
The SUBSTR(country, 1, 3) function extracts the first 3 characters of the country name, which is stored in a new column called new_country. Customer ID 55 appears in row 2 of the result.

Module 3 challenge

Graded Quiz

8. Fill in the blank: Data analysts usually use _____ to deal with very large datasets.

  • web browsers
  • spreadsheets
  • SQL ✅
  • word processors

Explanation:
SQL is optimized for managing and analyzing large datasets efficiently, which makes it the go-to tool for big data tasks over alternatives like spreadsheets or word processors.

9.What are some of the benefits of using SQL for analysis? Select all that apply.

  • SQL interacts with database programs. ✅
  • SQL tracks changes across a team. ✅
  • SQL has built-in functionalities.
  • SQL can pull information from different database sources. ✅

Explanation:
SQL is ideal for accessing, querying, and manipulating data from various database systems. It supports version control and change tracking in team environments and can integrate with different data sources.

10. A data analyst creates many new tables in their company’s database. When the project is complete, the analyst wants to remove the tables so they don’t clutter the database. What SQL commands can they use to delete the tables?

  • CREATE TABLE IF NOT EXISTS
  • DROP TABLE IF EXISTS ✅
  • UPDATE
  • INSERT INTO

Explanation:
DROP TABLE IF EXISTS removes a table from the database if it exists, preventing errors and keeping the database clean once a project is finished.

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

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

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

What customer ID number appears in row 12 of your query result?

  • 23
  • 42
  • 16 ✅
  • 8

Explanation:
Using DISTINCT(customer_id) removes duplicates, and ordering by invoice_id arranges them in ascending order. The 12th row contains customer ID 16.

12. 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. You want to check for postal codes that are greater than 7 characters long.

You write the SQL query below. Add a LENGTH function that will return any postal codes that are greater than 7 characters long.

What is the last name of the customer that appears in row 10 of your query result?

  • Rocha
  • Brooks
  • Hughes ✅
  • Ramos

Explanation:
Using LENGTH(postal_code) > 7 filters out longer postal codes. The query returns customers matching the condition, and Hughes appears in the 10th row.

13. A data analyst is cleaning transportation data for a ride-share company. The analyst converts the data on ride duration from text strings to floats. What does this scenario describe?

  • Visualizing
  • Processing
  • Calculating
  • Typecasting ✅

Explanation:
Typecasting refers to converting data from one type to another, such as strings to floats, which is necessary for accurate numerical analysis.

14. The CAST function can be used to convert the DATE datatype to the DATETIME datatype.

  • True ✅
  • False

Explanation:
The CAST() function is used in SQL to change one data type into another, including from DATE to DATETIME.

15. Fill in the blank: The _____ function can be used to return non-null values in a list.

  • TRIM
  • COALESCE ✅
  • CAST
  • CONCAT

Explanation:
COALESCE() returns the first non-null value in a list, which is useful when dealing with missing data in databases.

That’s it for Part 1! Continue your learning journey with the next set of answers.

Next Part: Module 3: Data Cleaning with SQL Answers (Part 2: Q16–30)

Part 2: Q16–30

Leave a Reply