Week 3

CONCAT()

Adds strings together to create new text strings that can be used as unique keys

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

Analysts can use SQL and spreadsheets to perform arithmetic, use formulas, and join data.

Convert data from one datatype to another

CAST()

Return non-null values in a list

COALESCE

Data analysts use which function to add strings together and create new text strings for unique keys?

CONCAT

Add strings together to create new text strings that can be used as unique keys

CONCAT()

Typecasting

Converting data from one type to another

Remove data from a database

DELETE

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

Data analysts can use the SUBSTR and TRIM functions to clean string variables

The customers table contains the following columns: CustomerId, FirstName, LastName, Company, Address, City, State, Country, PostalCode, Phone, Fax, Email, SupportRepId. Create a query to return the LastName and Country columns from this table for only customers in Germany.

Great job. Zimmermann is the last name of the customer for the 3rd result returned when making the following query: SELECT LastName,Country FROM customers WHERE Country = 'Germany';

Add new data into a database

INSERT INTO

Return the length of a string of text by counting the number of characters it contains

LENGTH()/LEN()

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.

SELECT * FROM album WHERE LENGTH(title) < 4.

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.

SELECT DISTINCT name FROM playlist ORDER BY playlist_id.

Pull data from any table in a database

SELECT FROM

Pull data from a specific place in a table, typically a table column

SELECT FROM WHERE

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.

SELECT customer_id, SUBSTR(country, 1, 3) AS new_country FROM customer ORDER BY country.

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

SQL can handle huge amounts of data, can be adapted and used with multiple database programs, and offers powerful tools for cleaning data.

Return a limited number of characters to create substrings from longer strings of text

SUBSTR()

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 works with a majority of databases and requires a small number of syntax changes to adapt to other dialects.

Remove leading, trailing, and repeated spaces in data

TRIM()

Change existing data in a database

UPDATE

COALESCE()

can be used to return non-null values in a list