PRACTICE QUIZ
Data analysts choose SQL for which of the following reasons? Select all that apply.
Data analysts choose SQL because it can handle huge amounts of data. SQL is also a well-known standard in the professional community.
In which of the following situations would a data analyst use SQL instead of a spreadsheet? Select all that apply.
A data analyst would use SQL instead of a spreadsheet to work with a huge amount of data. SQL can also quickly pull information from many different sources in a database and record queries and changes throughout a project.
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?
The analyst can use the DROP TABLE IF EXISTS query to delete the tables so they don’t clutter the database.
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? d Julia (CORRECT)
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?
The analyst is typecasting. Typecasting means converting data from one type to another.
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?
The analyst can use the CAST function to convert text strings to floats.
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?
The analyst can use the COALESCE function to eliminate the null values from the results.
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?
The statement SUBSTR(state, 1, 2) AS new_state will retrieve the first 2 letters of each state name and store the result in a new column as new_state. The complete query is SELECT customer_id, SUBSTR(state, 1, 2) AS new_state FROM customer ORDER BY state DESC. The SUBSTR function extracts a substring from a string. This function instructs the database to return 2 characters of each state name, starting with the first character. The customer ID number 47 appears in row 9 of your query result.
What are some of the benefits of using SQL for analysis? Select all that apply.
Some benefits of SQL include tracking changes across a team, interacting with database programs, and pulling information from different database sources.
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. SELECT ... FROM invoice ORDER BY invoice_id What billing state appears in row 17 of your query result?
The clause DISTINCT billing_state will remove duplicate entries from the billing_state column. The complete query is SELECT DISTINCT billing_state FROM invoice ORDER BY invoice_id. The DISTINCT clause removes duplicate entries from your query result. The billing state AZ appears in row 17 of your query result.
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. SELECT * FROM customer WHERE What country appears in row 1 of your query result?
The function LENGTH(state) > 2 will return any state names that are greater than 2 characters long. The complete query is SELECT * FROM customer WHERE LENGTH(state) > 2. The LENGTH function counts the number of characters a string contains. The country Ireland appears in row 1 of your query result.
Fill in the blank: _____ refers to the process of converting data from one type to another.
Typecasting refers to the process of converting data from one type to another.
The CAST function can be used to convert the DATE datatype to the DATETIME datatype.
The CAST function can be used to convert the DATE datatype to the DATETIME datatype. CAST can be used to convert any database field from one datatype to another.
What SQL function lets you add strings together to create new text strings that can be used as unique keys?
The CONCAT function lets you add strings together to create new text strings that can be used as unique keys.
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?
SQL will handle the data most efficiently. SQL can handle huge amounts of data.
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?
The analyst can use the INSERT INTO command to add a new customer to the database.
In SQL databases, what data type refers to a number that contains a decimal?
In SQL databases, the float data type refers to a number that contains a decimal.
Fill in the blank: In SQL databases, the _____ function can be used to convert data from one datatype to another.
The CAST function can be used to convert data from one datatype to another.
In which of the following situations would a data analyst use spreadsheets instead of SQL? Select all that apply.
An analyst would choose to use spreadsheets instead of SQL when visually inspecting data or working with a small dataset.
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.
The analyst can save the data by downloading the data as a spreadsheet or creating a new table for the data.
Fill in the blank: Data analysts usually use _____ to deal with very large datasets.
Data analysts usually use SQL to deal with very large datasets.
Fill in the blank: The _____ function can be used to return non-null values in a list.
The COALESCE function can be used to return non-null values in a list.
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.
The function LENGTH(postal_code) > 7 will return any postal codes that are greater than 7 characters long. The complete query is SELECT * FROM customer WHERE LENGTH(postal_code) > 7. The LENGTH function counts the number of characters a string contains. Hughes is the last name of the customer that appears in row 10 of your query result.
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?
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?
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.
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?
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.
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.
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?
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?
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?
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.
Feedback: The statement SUBSTR(last_name, 1, 3) AS new_last_name will retrieve the first 3 characters of each postal code and store the result in a new column as new_last_name. The complete query is SELECT employee_id, SUBSTR(last_name, 1, 3) AS new_last_name FROM employee ORDER BY postal_code. The SUBSTR function extracts a substring from a string. This function instructs the database to return 3 characters of each postal code, starting with the first character. The employee ID number 3 is in row 5 of your query result.
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?
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?
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.
The clause DISTINCT invoice_date will remove duplicate entries from the billing_state column. The complete query is SELECT DISTINCT invoice_date FROM invoice.
Fill in the blank: The _____ function can be used to change the data type of a column.
Fill in the blank: The _____ function can be used to join strings to create a new column.
Ready to test your recall?
Data analysts choose SQL for which of the following reasons? Select all that apply.
How confident are you in this answer?