Openness (or open data)

• Free access, usage, and sharing of data

SELECT
	usertype,
	CONCAT(start_station_name. " to ", end_station_name) AS route,
	COUNT(*) AS num_trips,
	ROUND(AVG(CAST(tripduration AS int64)/60),2) AS duration
FROM
	bigquery-public-data.new_york.citibike_trips
GROUP BY
	start_station_name, end_station_name, usertype
ORDER BY
	num_trips DESC

In a spreadsheet, a cell contains the date and time value 2/23/2021 7:00. What is the correct syntax to return only the four-digit time portion of the cell value?

• =LEFT(J10, 4)
• =LEFT(4.J10)
• =RIGHT(J10, 4)
• =RIGHT(4,J10)

Correct. To return only the time portion of the cell value, the syntax is =RIGHT(J10, 4). The time, 7:00, is located four characters from the right of the string.

Knowing how to convert and manipulate your data for an accurate analysis is an important part of a data analyst’s job. In this reading, you will learn about different SQL functions and their usage, especially regarding string combinations.

A string is a set of characters that helps to declare the texts in programming languages such as SQL. SQL string functions are used to obtain various information about the characters, or in this case, manipulate them. One such function, CONCAT, is commonly used. Review the table below to learn more about the CONCAT function and its variations.

When adding two strings together such as ‘Data’ and ‘analysis’, it will be input like this:

• SELECT CONCAT (‘Data’, ‘analysis’);

The result will be:

• Dataanalysis

Sometimes, depending on the strings, you will need to add a space character, so your function should actually be:

• SELECT CONCAT (‘Data’, ‘ ‘, ‘analysis’);

And the result will be:

• Data analysis

The same rule applies when combining three strings together. For example,

• SELECT CONCAT (‘Data’,’ ‘, ‘analysis’, ‘ ‘, ‘is’, ‘ ‘, ‘awesome!’);

And the result will be

• Data analysis is awesome!

W3 Schools is an excellent resource for interactive SQL learning, and the following links will guide you through transforming your data using SQL:

• SQL functions: This is a comprehensive list of functions to get you started. Click on each function, where you will learn about the definition, usage, examples, and even be able to create and run your own query for practice. Try it out for yourself!
• SQL Keywords: This is a helpful SQL keywords reference to bookmark as you increase your knowledge of SQL. This list of keywords are reserved words that you will use as your need to perform different operations in the database grows.
• While this reading went through the basics of each of these functions, there is still more to learn, and you can even combine your own strings.

1. Practice using CONCAT
2. Practice using CONCAT WS
3. Practice using CONCAT with +

Pro tip: The functions presented in the resources above may be applied in slightly different ways depending on the database that you are using (e.g. mySQL versus SQL Server). But, the general description provided for each function will prepare you to customize how you use these functions as needed.

TOTAL POINTS 3

Fill in the blank: In SQL, _____ can be used to combine strings from multiple tables in order to create a new string.

• COMBINE
• CONNECT
• CONCATENATE
• CONCAT

Correct. In SQL, CONCAT can be used to combine strings from multiple tables in order to create a create new string.

You are working with a database table that contains data about playlists for different types of digital media. You are only interested in the first 4 playlists.

You write the SQL query below. Add a LIMIT clause that will return only the first 4 playlists.

SELECT
*
FROM
playlist
LIMIT 4

+-------------+------------+
| playlist_id | name       |
+-------------+------------+
|           1 | Music      |
|           2 | Movies     |
|           3 | TV Shows   |
|           4 | Audiobooks |
+-------------+------------+

What playlist appears in row 2 of your query result?

• TV Shows
• Audiobooks
• Movies
• Music

Correct. The clause **LIMIT 4** will return only the first 4 playlists. The complete query is SELECT * FROM playlist LIMIT 4*. The* LIMIT clause sets a limit on the number of rows your query returns. The Movies playlist appears in row 2 of your query result.

What function can be used to return the number of characters in cell B8 so you can confirm that it contains exactly 20 characters?

• =LEN(B8, 20)
• =LEN(20)
• =LEN(20, B8)
• =LEN(B8)

Correct. The function =LEN(B8) will display the number of characters in cell B8. The LEN function returns the length of a string of text by counting the number of characters it contains.

Like a lot of the things you’re learning in this program, spreadsheets will get easier the more you practice. This reading provides you with a list of resources that may help advance your knowledge and experience with spreadsheet functions and functionality. The goal is to provide you with access to a variety of advanced tips and tricks that will help make you more efficient and effective when working with spreadsheets to perform data analysis. Review the description of each resource below, click the links to learn more, and save or bookmark any links that are useful to you. You can immediately start practicing anything that you learn to increase the chances of your understanding and to build your familiarity with spreadsheets. This reading provides a range of resources, so feel free to explore the ones that are applicable to you and skip the ones that aren’t.

• Keyboard shortcuts for Google Sheets: This is a great resource for quickly learning a range of keyboard shortcuts that can make regular tasks quicker and easier, like navigating your spreadsheet or accessing formulas and functions. This list contains shortcuts for the desktop and mobile versions of Google Sheets so that you can apply them to your work no matter what device you are using.
• List of Google Sheets functions: This is a comprehensive list of the Google Sheets functions and syntax. Each function is listed with a link to learn more.
• 20 Google Sheets Formulas You Must Know: This blog article summarizes and describes 20 of the most useful Google Sheets formulas.
• 18 Google Sheets Formula Tips and Techniques: These are tips for using Google Sheets shortcuts when working with formulas.

• Keyboard shortcuts in Excel: Earlier in this list, you were provided with a resource for keyboard shortcuts in Google Sheets. Similarly, this resource provides a list of keyboard shortcuts in Excel that will make performing regular spreadsheet tasks more efficient. This includes keyboard shortcuts for both desktop and mobile versions of Excel, so you can apply them no matter what platform you are working on.
• 222 Excel shortcuts: A compilation of shortcuts includes links to more detailed explanations about how to use them. This is a great way to quickly reference keyboard shortcuts. The list has been organized by functionality, so you can go directly to the sections that are most useful to you.
• List of spreadsheet functions: This is a comprehensive list of Excel spreadsheet functions with links to more detailed explanations. This is a useful resource to save so that you can reference it often; that way, you’ll have access to functions and examples that you can apply to your work.
• List of spreadsheet formulas: Similar to the previous resource, this comprehensive list of Excel spreadsheet formulas with links to more detailed explanations and can be saved and referenced any time you need to check out a formula for your analysis.
• Essential Excel Skills for Analyzing Data: This blog post includes more advanced functionalities of some spreadsheet tools that you have previously learned about, like pivot tables and conditional formatting. These skills have been identified as particularly useful for data analysis. Each section includes a how-to video that will take you through the process of using these functions step-by-step, so that you can apply them to your own analysis.
• Advanced Spreadsheet Skills: Mark Jhon C. Oxillo’s presentation starts with a basic overview of spreadsheet but also includes advanced functions and exercises to help you apply formulas to actual data in Excel. This is a great way to review some basic concepts and practice the skills you have been learning so far.

There are lots of resources online about advanced spreadsheet tips and tricks. You'll probably discover new resources and tools on your own, but this list is a great starting point as you become more familiar with spreadsheets.