Test your knowledge on sql

1. What is the maximum value in the price column of the car_info table?

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

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

4. 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.

5. In your last query, you processed 415.8 GB of data. How many rows were returned by the query?

Test your knowledge on SQl Queries

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

2. 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?

3. 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?

4. 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?

GOOGLE DATA ANALYTICS COURSERA ANSWERS AND STUDY GUIDE

Weekly Breakdown | Google Study Guides | Back to Top

Process Data from Dirty to Clean Weekly Challenge 3

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

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

3. A data analyst is managing a database of customer information for a retail store. What SQL command can the analyst use to add a new customer to the database?

4. You are working with a database table that contains invoice data. The table includes columns for invoice_id and billing_state. You want to remove duplicate entries for billing state 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_state column.

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

What billing state appears in row 17 of your query result?

5. 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 appears in row 1 of your query result?

6. In SQL databases, what data type refers to a number that contains a decimal?

7. Fill in the blank: In SQL databases, the _____ function can be used to convert data from one datatype to another.

8. 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?

9. 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_country. You want to retrieve the first 4 letters of each city name. You decide to use the SUBSTR function to retrieve the first 4 letters of each city name, and use the AS command to store the result in a new column called new_city.

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

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

10. What invoice ID number appears in row 7 of your query result?

11. Data analysts choose SQL for which of the following reasons? Select all that apply. 

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

13. 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? 

14. 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 city names that are greater than 9 characters long. 

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

SELECT 

*

FROM

customer

WHERE

What is the first name of the customer that appears in row 7 of your query result?

15. 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? 

16. A data analyst is working with product sales data. They import new data into a database. The database recognizes the data for product price as text strings. What SQL function can the analyst use to convert text strings to floats?

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

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

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

SELECT

customer_id,

…

FROM

customer

ORDER BY

state DESC

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

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

19. Fill in the blank: _____ refers to the process of converting data from one type to another. 

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

21. What SQL function lets you add strings together to create new text strings that can be used as unique keys? 

22. 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? 

23. A data analyst is managing a database of customer information for a retail store. What SQL command can the analyst use to add a new customer to the database? 

24. 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. 

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

26. You are working with a database table named customer 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_code that is greater than 7 characters long.

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

1 SELECT

2 *

3 FROM

4 customer

5 WHERE …

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

NOTE: The query index starts at 1 not 0.

27. 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?

28. As a data analyst, you are working on a quick project containing a small amount of data. As the data was emailed to you, there is no need to query the data. What tool should you use to perform your analysis?

29. You are working with a database table named invoice 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.

1 SELECT …

2 FROM

3 invoice

4 ORDER BY

5 invoice_id

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

NOTE: The query index starts at 1 not 0.

30. You’re working with a dataset that contains a float column with a significant amount of decimal places. This level of granularity is not needed for your current analysis. How can you convert the data in the float column to be integer data?

31. 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_country. You use the SUBSTR function to retrieve the first 4 letters of each billing city name, and use the AS command to store the result in a new column called new_city.

You write the SQL query below. Add a statement to your SQL query that will retrieve the first 4 letters of each billing city name and store the result in a new column as new_city. 

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

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

1 SELECT

2 invoice_id,

3 …

4 FROM

5 invoice

6 ORDER BY

7 billing_city

What invoice ID number is in row 7 of your query result? 

NOTE: The query index starts at 1 not 0.

32. A junior data analyst joins a new company. The analyst learns that SQL is heavily utilized within the organization. Why would the organization choose to invest in SQL? Select all that apply.

33. Your manager tasks you with analyzing a dataset and visually inspecting the data. Upon initial inspection you realize that this is a small dataset. What tool should you use to analyze the data?

34. A data analyst creates a database to store information on the company’s customer data. When completing the initial import the analyst notices that they forgot to add a few customers into the table. What command can the analyst use to add these missed customers?

35. You are working with a database table named invoice that contains invoice data. The table includes a column for customer_id. You want to remove duplicate entries for customer_id and get a count of total customers in the database. 

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.

1

2

3

SELECT COUNT(…)

FROM

invoice

Run

Reset

What is the total number of customers in the database?

36. 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

1 SELECT

2 employee_id,

3 …

4 FROM

5 employee

6 ORDER BY

7 postal_code

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

NOTE: The query index starts at 1 not 0.

37. A data analyst is tasked with identifying what orders are still in transit. The current list of orders contains trillions of rows. What is the best tool for the analyst to use?

38. You’ve been working on a large project for your organization that has spanned many months. Throughout the project you have created multiple tables to save your progress and store data you may need later on. Because the project is ending soon, you decide to do some housekeeping and clean up the tables you will no longer need. What command will you use to accomplish this task?

39. You are working with a database table named invoice that contains invoice data. The table includes a column for invoice_date. You want to remove duplicate entries for invoice_date. 

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

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

1 SELECT …

2 FROM

3 invoice

What invoice_date is in row 17 of your query result?

NOTE: The query index starts at 1 not 0.

40. You’re working with a dataset that contains a float column with a significant amount of decimal places. This level of granularity is not needed for your current analysis. How can you convert the data in the float column to be integer data?

41.  Fill in the blank: The _____ function can be used to change the data type of a column.

42. Fill in the blank: The _____ function can be used to join strings to create a new column.

Cleaning Data with SQL conclusion