Advanced work with queries

After working through the tutorial you should be able to:

Requirements

To complete this tutorial you need to have:

If you need help creating the project or linking the dataset to it, detailed instructions are in the tutorial Create a project to work with data.

As you work in projects with other people, you might find a query that you want to use and edit, but it's not your query so you don't have permissions to save changes to it. The easiest thing to do is to make a copy of the query by selecting the three dots to the right of the query name in the project workspace and selecting Save a copy:

You'll be prompted to name the query and write a description. For tracking lineage it's a good idea to include the name of the original query in the description. Other save options include where--the current project or the original dataset--and who can see the new query (hidden by the location selection in the image below:

Note that if the original query is a project query (listed in the queries at the bottom of the Project directory in the left sidebar), you can only save the query to the original project--if you want to use it in a different project, you will have to copy the query text and paste it into a new query window in the other project. Here, too, it's a good idea to copy the original source of the query in case you want to go back to it later.

Most of the time you'll want your queries to be associated with a project. However if the query would be useful to others who create projects from a dataset, it might make more sense to save it directly to the dataset. Saving it to the dataset and sharing it with everyone enables anyone with access to the dataset to use it in their own projects. It's also the easiest way for others to save the query to their own projects using the dataset. One note: queries saved to datasets show up in the Connected datasets section of the Project directory in the left sidebar--not under Queries:

To save a query to a dataset, click the menu icon to the right of the field Where will the query be saved? and select the dataset where you want to save it:

Another way to use a query is to make a template from it so you can change the input strings to various parts of it. For example, if we had a query that we could turn the query we just saved as Bee colony census by state and year into a query template by selecting the New template link at the top right of the query panel:

A dialog window will pop up giving you basic instructions to how query templates work and linking you to the documentation for query templates:

Click the Add a sample statement button which will add a sample of the DECLARE statement used in query templates, a sample comment, and a link to the documentation to the top of your query window:

With just a couple of modifications to your SQL you can have a query that other users can use to input values for a field without ever having to know SQL themselves. For example, to change the query above into a query template that returns the results for any specified census year, all you need to do is:

The contents of the query editor will look like this:

You can preview the query to see what other users will see and to test the query yourself by selecting the Preview query template link in the upper right corner of the query panel. Then fill in a value and hit Run query:

Another way to use a query is to download the results from it in either a csv or an xlsx file. This option is useful if you want to work with your data file in a spreadsheet program. After running your query, click the Download link below the query window and select the option you want:

Another way to work with your query results is to save them as a new table in a dataset or project. For those familiar with creating a View in a SQL database, this is quite similar. When the results are saved into a table they can be queried just like any other file--eliminating the need for extremely complex queries. You can save the table in your current project, in an existing dataset, or in a new dataset. Select Save to dataset or project from the dropdown menu on Download, name your file, and either choose a project or dataset from the dropdown menu, or begin typing the name of another one:

There are other reasons you might want to save the results of your query in a new tabular file. See the article on working with query results for more information.

Two other ways you can use the results of a query are to make them available for download from a URL, or to embed them on a Markdown page. Both options are found in the Download menu under Copy URL or embed code. The URL is a download link for a csv file of the results, and the embed code option renders the results table in a Markdown page.

There are many applications you can use to analyze and display the results of your queries, and we make it easy to work with your favorites. You can find out about the applications we integrate with on our integrations page. If you already have an integration configured that you want to use or want to integrate a new application, the name of the first application in your list of integrations will show up as a menu to the right of the Download button:

The variety of ways to access data and the ease of querying it are some of the most powerful features of data.world. We introduced the basics of querying in the tutorial Query your data, and in this tutorial we dove into the ways you can use queries to get the most out of your data. The best way to become comfortable with all the options available when querying data is to practice. If you haven't looked through our SQL or SPARQL documentation yet, now would be a good time to do it to get a feel for just how much you can do. If you're new to SQL, try our SQL tutorial to get the most out of querying with it on data.world.