1. Overview
   1. What to expect from this section
2. Schema Navigator
3. SQL Editor panel and manager bar
   1. Results tab
   2. Query history tab
   3. Multi-tab queries

Overview

SQL is a programming language designed for managing data stored in a relational database management system. By writing SQL queries one can communicate with the database to manipulate its data.

The following sections will introduce the user to BI Navigator’s “SQL Lab”. Through “SQL Lab” a user can write their own SQL queries to transform and manipulate their data. This feature allows queries to be saved and to be used in the creation of virtual datasets.

“SQL Editor”, a subset of “SQL Lab”, allows the user to run queries using either a Schema Navigator or an SQL editing panel.

What to expect from this section

This guide will cover the following topics:

• Schema Navigator
• SQL Editor panel and manager bar

Schema Navigator

To access the Schema Navigator, open the “SQL Lab” dropdown (found in the top navigation bar) and select “SQL Editor”:

1. Once the selected “Query” tab is open, the user will be able to choose options from three dropdowns found on the left side of the page:

   • Database
   • Schema
   • See Table Schema

2. Once all three selections have been populated, the query will automatically run and a list of the available columns, as well as a preview table of the query data, will be shown.

Once a user runs a query in Schema Navigator, they can see a preview of the tables that were selected under “See Table Schema”.

SQL Editor panel and manager bar

“SQL Editor” is an editing panel that runs scripts which can manipulate data according to the user’s preference. These queries can be saved with the SAVE AS button for future use. There is also a search and replace function that can replace keywords in queries. This can be accessed by pressing Ctrl + f. Queries can also be used to create charts (virtual datasets) from this view.

To access the “SQL Editor”, open the “SQL Lab” dropdown (found in the top navigation bar) and select “SQL Editor”:

1. Enter the script necessary to run the query (in the SQL editing panel found next to the Schema Navigator panel).
2. Click on RUN.
3. The results of the query will show in the row below the SQL editing panel.

Both the Schema Navigator and the “SQL Editor” panel are found under “SQL Lab” -> “SQL Editor”. Once on the “SQL Editor” page the user can choose to run queries using either the Schema Navigator (found on the left of the page) or the “SQL Editor” panel to the right of the Schema Navigator.

The “SQL Editor” panel includes the “Results” and “Query History” tabs. The sections below will discuss these tabs in more detail.

Results tab

The “Results” tab shows a query’s output. There are a variety of ways in which the query results can be used:

• To create a chart - this prompts the user to save a query as a virtual dataset and open the chart creation view to explore the data visually.
• To download the data to a CSV file - this pulls a copy of the raw data and saves it to a CSV file.
• To copy the data to a clipboard - this copies the results from the query to the user’s clipboard.

Open the “SQL Lab” dropdown (found in the top navigation bar) and select “SQL Editor”:

1. Enter the required script (into the script input box) to run a query.
2. Click on the RUN button to run the query.
3. The “Results” tab can be found underneath the script input box, next to the “Query History” tab.
4. Click on the “Results” tab title. This opens the query results with the “Create Chart”, “Download to CSV”, and “Copy to Clipboard” options. Generally the query’s results will already have an open “Results” tab.
5. To save a query, click on the SAVE AS button found at the middle-right of the page. This will open the “Save query” window.
6. Name and describe the query in the input boxes provided.
7. Click on the SAVE button to save the query.

- The results shown in the “Results” tab will be the results of the query that was run most recently. When changes are made to the query, the query must be run again before the results can be explored or saved.

- There is a limit to the number of rows that can be shown in the “Results” tab.

Query history tab

Users can access historical data from previously run queries by navigating to the “Query History” tab.

Navigate to the “SQL Lab” link found in the top navigation bar and select “Query History” from the dropdown:

Once the “Query History” tab is open, use the filters found in this section (“Database”, “State”, “User”, “Time Range” and “Search by Query Text”) to find previously run queries that might not have been saved.

The “Query History” tab also provides important information about selected queries, including (but not limited to):

• When the query was run.
• Whether the query was successful.

Multi-tab queries

Multiple queries can be created with tabs, enabling the user to switch between and compare queries.

Open the “SQL Lab” dropdown (found in the top navigation bar) and select “SQL Editor”:

1. Click on the + icon next to the current open query or type Ctrl + q.
2. Navigate between tabs to compare queries.