1. In the Setup menu, click Run Query.

The screen reroutes to the Run Query screen, as shown in the image below.

2. Enter queries in the text editor at the top of the screen.

a. While entering the query or viewing results, to extend the text editor or extend the results display, click the gray line and move the arrow up or down.

b. To start a new query with new filters, parameters, etc., without losing your current query data, click the Addition (+) icon to add a new tab.

c. Click and drag tabs to rearrange them.

d. Hover over the tab and click the black X to close the tab.

i. In the confirmation pop-up window, click OK.

e. Click on any SQL syntax element within the query to highlight all matching instances throughout the query. For example, clicking on last_name will highlight every occurrence of last_name, making it easier to track column names, table references, or variables within the code.

f. Formatted queries have additional settings. Anywhere the arrow displays, you can collapse the range of code, such as functions, loops, or large blocks of SQL, so you can focus on specific parts without being distracted by the entire script. This is especially useful for navigating complex or lengthy queries, as you can hide unnecessary parts of the code temporarily and only view what's relevant.

Once collapsed, click the arrow again to expand the range of code.

3. To aid in writing queries, the Tables tab defaults to open, displaying a complete list of available tables. This feature provides quick access to table structures, column names, and data types, helping users construct queries more efficiently.

a. Click the listed table of interest to open associated fields. You can then click Create Select Query to automatically generate a query to pull data as it pertains to the select table.

b. Start typing the name of a table or a field to find the corresponding table in the Search tables or columns text box, then click the magnifying glass icon or press Return/Enter.

c. The page numbers of tables available displays. In the example displayed, there are 55 pages of tables. Click the displayed page numbers to jump to that page.

4. Click the History tab to open a history of the last queries ran, which includes the date, time, and query.

a. To run the query again, click the Run Query button.

b. To edit the query, click the Edit Query button.

c. Press and hold the Shift key while clicking the Edit Query button to append the query instead of replacing the existing one. In other words, if you are working on a query and want to add a query from the History tab to the existing query, you can hold the Shift key and click the Edit Query button to add it to the query editor.

5. Click the Parameters tab to add parameters and view existing parameters, which allows for dynamic value substitution in queries. This feature is particularly useful for reusable queries that require different inputs. Parameters are especially helpful for filtering data, setting date ranges, or dynamically updating query criteria.

a. Click Add Parameter to add a new parameter.

b. Enter the parameter's Name and Value in the provided text boxes.

You can also use the workspace below to enter the name and value.

c. Click Add Parameter again to add another parameter. You can add as many parameters as you need to a single query.

d. Click the gray x to delete a parameter.

e. Click the gray line and move the arrow up or down to extend the listed parameters name and value or workspace.

In the example used, if you set the grade parameter to 10, the query will retrieve all students in grade 10. This approach allows you to reuse the same query for different grade levels without editing the SQL code each time.

As shown in the image below, the parameter for grade 10 has been used to retrieve student enrollment records for all students' whose next grade level is grade 10.

6a. Select Readonly from the pull-down to run queries without making actual changes to the database, which is useful when running queries that will be used to update, insert, or delete data.

6b. Select Transaction from the pull-down to make all of the queries you've typed in the run text box into a single transaction instead of having a separate transactions for each query. This is useful if you're testing something and need to be able to use the ROLLBACK, TRY, etc. functionality of SQL. I.e. The essential point of a transaction is that it bundles multiple steps into a single, all-or-nothing operation. The intermediate states between the steps are not visible to other concurrent transactions, and if some failure occurs that prevents the transaction from completing, then none of the steps affect the database at all.

Suppose you're testing a process that inserts a new student record and updates their enrollment status. Using a transaction ensures both actions succeed together  or neither action happens at all if something goes wrong.

• BEGIN; starts the transaction.
• The INSERT and UPDATE statements are part of the same transaction.
• COMMIT; finalizes the transaction, applying both changes to the database.

If something goes wrong  for example, the INSERT works but the UPDATE fails  you can add a ROLLBACK to undo everything.

6c. Select No Transaction from the pull-down to run the queries without initiating any transaction context, meaning no changes will be made to the database. This is useful when you're simply retrieving data or testing a query that doesn't require the transactional features of SQL, such as ROLLBACK or TRY. Without a transaction, each query is executed independently, and no intermediate state is saved, so if something goes wrong, you dont have the ability to undo changes across multiple queries as you would in a transaction. This option ensures that the query runs purely for data retrieval or analysis purposes, with no impact on the database's state.

7a. Select Export as INSERT Statements and click the Run Query button to format the output of a SELECT query into an INSERT query syntax with a [table] placeholder. This allows you to generate INSERT statements based on the data returned by the SELECT query, where you can later replace the [table] placeholder with the actual table name. This is useful for quickly creating bulk data insertions into a table, especially when you need to replicate or migrate data from one database to another, or when preparing test data for new tables. Once you have the INSERT statements, you can modify or run them to insert the queried data into the desired table.

7b. Select Export CSV from the pull-down and click Run Query to download the query results in a CSV file to your computer.

7c. Select Export JSON from the pull-down and click Run Query to download the query results in a JSON file (JavaScript Object Notation).

7d. Select No Export from the pull-down to run your queries without exporting the results to an external file or format. This option allows you to execute the query and view the results directly within the system without generating an output file, such as CSV or INSERT statements. It's useful when you're only interested in viewing or testing the query results internally, without the need to save or export the data.

8. Click the Format button after entering a query in the provided text box in order to format the query.

The query displayed below is an example of non-formatted.

The query displayed below is an example of formatted.

9. Select the Dark Mode toggle to apply the dark mode to Run Query.

Click the Dark Mode toggle again to turn it off.

10. Select the Explain Query check box and click Run Query to enable the feature that provides an analysis of your SQL query's execution plan. This option allows you to view detailed insights into how the database engine processes your query, including which indexes are being used, estimated costs, and potential performance bottlenecks. It helps you understand how to optimize your query for better performance, especially useful when working with complex queries or large datasets.

In the example used, the following information is contained in the explanation:

Execution Plan:

• Insert on students: (cost=0.00..0.03 rows=1 width=17092) (actual time=3.501..3.502 rows=0 loops=1)
• Result: (cost=0.00..0.03 rows=1 width=17092) (actual time=0.080..0.081 rows=1 loops=1)
• Planning Time: 0.267 ms
• Trigger: f:t:b:i:students: time=0.936 calls=1
• Execution Time: 3.963 ms

Explanation:

• Cost: The estimated cost for the query is between 0.00 and 0.03, which reflects the minimal computational effort required for the insert operation.
• Insert on students: The actual operation is an insert into the students table. This step is relatively simple since the operation involves adding a single row to the table.
• Result: The result node represents the successful insertion of the values into the table, with an actual time of 0.0800.081 ms.
• Planning Time: It took 0.267 ms to plan the query before execution.
• Trigger: A trigger on the students table was called during the insert operation, taking 0.936 ms.
• Execution Time: The total execution time for the query was 3.963 ms, which is very fast, indicating that the insert was successful and efficient.

If there is an error with your query, the explanation displays information about the error.

Explanation:

• The error message indicates that the column "enrollment_status" does not exist in the student_enrollment table.
• The error occurs during the EXPLAIN ANALYZE execution, as the database cannot find the specified column to update.
• SQLSTATE[42703] refers to an undefined column error, which means the column name provided in the query does not match any existing column in the table.
• The caret (^) symbol shows the position of the issue within the SQL query, pointing to the column name enrollment_status.

a. Upon applying Explain Query or running an erroneous query, click the AI Helper to leverage AI assistance for improving or troubleshooting your query.

AI Error Assistant

• Explains why an error occurred in your query.
• In some cases, it will provide a corrected version of the query.
• Includes the Additional Notes section for alternate solutions or best practices.

AI Optimize Assistant

• Provides recommendations to improve query performance, even when no errors are present.
• Suggests a stronger, optimized version of your query that you can either copy or insert directly into Run Query.
• Includes Key Changes for important adjustments and offers Additional Recommendations for further insights.

i. Click Try Again if the original response isn't to your satisfaction. Clicking Try Again will regenerate a new explanation and suggestion.

ii. If a query is provided, click the Insert into editor button to insert the query into Run Query for additional editing or to run the query.

iii. Click Cancel or click the black X to close the AI Helper.

11. Select the Single Query check box and click Run Query to execute your entire SQL block as a single query. This is essential for creating objects like stored procedures or functions, which contain multiple SQL statements that must run together as a unit.

If Single Query is not selected, the PDO driver (which transmits SQL code to the database) treats each statement separately. This can cause errors when executing multi-statement logic that relies on being processed as a single block.

When creating a function in SQL, it must be processed as a single query because functions--or any query containing double dollar signs ($$)are treated as a block of code. By default, the Run Query tool attempts to parse and execute queries by splitting them at semicolons (;). However, functions often contain multiple semicolon-terminated statements within a single block, meaning they should not be executed individually.

Enabling Single Query ensures the function is recognized as a single executable unit. Running multiple unrelated queries in Single Query mode will result in an error.

Example show below: Creating a Function to Retrieve the Current School Year

This function retrieves the current school year (syear) from the program_config table and allows an optional integer input ($1) to modify the returned year. See the image below on how to use this function in a query, which will retrieve student enrollment records for the current school year:

12. Select the Word Wrap check box to enable word wrapping in the query results. This feature ensures that long lines of text automatically break and continue on the next line within the results display area.

Shown below is an example of a query with the Work Wrap applied.

Shown below is an example of a query without the Work Wrap applied.

13. Select the Paginate check box to break down query results into pages as opposed to having all results displays on a single page where you would have to scroll through all of the results.

The example displayed below shows results when not paginated.

The image below shows results when the Paginate check box is selected.

14. Select the Preprocess check box to adjust SQL syntax for compatibility, which can be helpful when copying SQL from code. It converts Postgres type casts, handles empty arrays, and modifies LIMIT/OFFSET clauses for cross-DB execution.

15. Select the Autocomplete check box to enable real-time query suggestions as you type. This feature helps streamline your workflow by providing recommended table names, column names, and SQL keywords based on what you've entered.

For example:

• Typing SEL will suggest SELECT.
• Typing student_ may display options like student_enrollment, student_grades, or student_attendance.

16. Once a query has been entered and all selections have been made, click Run Query for query results, which display on the bottom of the screen.

a. Click the Stop button to stop the query from running. This is an option as long as the query is running.

17. The results displayed is based on the query displayed. If the query is changed in the provided text box, you can repopulate the text box with the previously run query by clicking the query link.

18. Click the Export CSV button to download the query results to an Excel spreadsheet.

19. The Time is took for the query to pull the applicable results displays, as well as the number of Rows, and the number of records Showing.

20. If the query results are paginated, click the page number to jump to the page or click the double arrows to jump to the last set of pages.

21. To sort results, you can click the headers. Click once for ascending results; click twice for descending results.

22. Click the Hide Sidebar arrow to close the sidebar and hide the Table, History, and Parameters tabs.

Click the Show Sidebar arrow to display the tabs again.

1. In the Setup menu, click Run Query.

2. Enter your query, and apply all applicable filters, parameters, etc.

3. Click the Run Query button.

4. Upon running an erroneous query, click the AI Helper to leverage AI assistance for improving or troubleshooting your query.

The AI Error Assistant explains why an error occurred in your query. In some cases, it provides a corrected version of the query and includes an Additional Notes section with alternate solutions or best practices.

a. Click Try Again if the original response isn't to your satisfaction. Clicking Try Again will regenerate a new explanation and suggestion.

b. If a query is provided, click the Insert into editor button to insert the query into Run Query for additional editing or to run the query.

i. Press and hold the Shift key while clicking the Insert into editor button to append the query instead of replacing the existing one. In other words, if you are working on a query and want to add the listed query from the AI Helper to the existing query, you can hold the Shift key and click the Insert into editor button to add it to the query editor.

c. Click Cancel or click the black X to close the AI Helper.

In this example, the AI Error Assistant made it clear that the parameter was not set for the SYEAR. Upon adding the parameter, the query now runs.

In another example, the AI Error Assistant identifies a missing comma as the cause and provides a corrected version of the query.

Scroll down to Key Changes to review the modifications made.

1. In the Setup menu, click Run Query.

2. Enter your query, and select the Explain Query check box.

3. Click the Run Query button.

4. Upon applying Explain Query, click the AI Helper to leverage AI assistance for improving your query.

The AI Optimize Assistant provides recommendations to improve query performance, suggests a stronger, optimized version of your query that you can copy or insert directly into Run Query, and highlights Key Changes for important adjustments, along with Additional Recommendations for further insights.

a. Click Try Again if the original response isn't to your satisfaction. Clicking Try Again will regenerate a new explanation and suggestion.

b. If a query is provided, click the Insert into editor button to insert the query into Run Query for additional editing or to run the query.

i. Press and hold the Shift key while clicking the Insert into editor button to append the query instead of replacing the existing one. In other words, if you are working on a query and want to add the listed query from the AI Helper to the existing query, you can hold the Shift key and click the Insert into editor button to add it to the query editor.

c. Click Cancel or click the black X to close the AI Helper.

1. In the Setup menu, click Run Query.

2. Enter your query in a new tab.

3. Click the Parameters tab to add parameters and view existing parameters, which allows for dynamic value substitution in queries.

4. Click Add Parameter to add a new parameter.

5. Enter the parameter's Name and Value in the provided text boxes.

You can also use the workspace below to enter the name and value.

6. Click Add Parameter again to add another parameter. You can add as many parameters as you need to a single query.

7. Click the gray x to delete a parameter.

8. Click the gray line and move the arrow up or down to extend the listed parameters name and value or workspace.

9. Click Run Query.

In the example used, if you set the grade parameter to 10, the query will retrieve all students in grade 10. This approach allows you to reuse the same query for different grade levels without editing the SQL code each time.

As shown in the image below, the parameter for grade 10 has been used to retrieve student enrollment records for all students' whose next grade level is grade 10.