A query in the context of data is a request for information written in a query language like SQL or SPARQL. Queries use a specified set of terms in a specific order with proscribed punctuation. If you have never written a query for data before, or if you have but you need to improve your skills, data.world has two tutorials--one for SQL and one for SPARQL--to help you get up to speed.
In this article we'll go over how to use the query panel in the project workspace for SQL queries including:
- Creating a new query
- Auto-complete of terms and objects
- Click-to-copy for columns and tables
- Pop-up help text
- How to auto-format queries
- Saving queries
See the article Quickstart to navigating the project workspace if you would like an overview of the entire project workspace.
Creating a new query
To write a SQL query against data in a project go to the upper left corner of the workspace and select SQL Query from the + Add menu:
When the query editor comes up in the center panel of the screen, the Project Schema also comes up in the right side panel. If the project schema isn't showing, select the arrow to the right of the Run query button to expand the sidebar:
Auto-complete of terms and objects
As you type your query, the editor suggests SQL terms--operators, aggregations, and functions--for you. Hitting 'enter' will autocomplete the highlighted suggestion in the list. Arrow down or up to choose another option:
Click-to-copy for columns and tables
You have the option of typing in the table and column names for the data you are querying, but you can also use the click-to-copy feature on the project schema to copy the names for you so you don't have to worry about typos.
Pop-up help text
Help text is provided for all SQL functions and aggregations when you hover over the function name:
How to auto-format queries
While you can type on one line type your SQL query all on one line--it is perfectly legal and will run--it's good practice to split it up based on clauses and punctuation. The line breaks make it easier to see where you may have forgotten a comma or some other element of the query, and it also allows us to give you better error messages if there are problems with your query.
Here's an example of a query before and after running it through our auto-formatter:
To auto-format your query, use the keyboard shortcut Cmd + Option + L . If you forget the command there is a dropdown menu to the right of the Run query button which has a link to a list of all the keyboard shortcuts.
From the dropdown menu you can also manage your query (rename, copy, etc.) as well as get to our SQL tutorial for additional help.
If you try to auto-format your query and it doesn't work, that's a sign that you have an error in the query. If you run it you'll get an error message telling you what the problem is and where to find it:
In the above example the query is written to search for all the records that have the value won in the deal_stage column in the sales_pipeline table. However the way the query is written it looks like it is searching for records where the value in the deal_stage column matches the value in another column named Won. For Won to be recognized as a value, it has to be surrounded by double-quotes. The error message returned from running the query tells you that the problem is in the 4th line and the 36th character (column). The 36th character should have been the opening " on the 4th line:
When your query is complete, save it for later use by selecting Save to the left of the Run query button. You'll be prompted to name the query, add a description, determine where to save it, and indicate who can see it:
It's good practice to give the query a short descriptive name for both your own later use and for use by others working on your project. Adding a description also makes your query more useable as the description shows up on the resource card returned from using search on data.world. For information on all the ways you can use the results of a query see the article Working with queries.