The Run Query tool is used to run custom made queries in order to review student data, user data, scheduling data, and more. The Run Query tool can also be used to update, insert, or delete date depending on profile permissions.
In order to access Run Query, the district must enable permissions either via the configuration file or via Users > Profiles > Setup tab. The commands you can use also depend on the profile permissions enabled here; options include: Select, Explain, Set, Insert, Update, Delete, Truncate, Drop, Create, Alter, Begin, Commit, and Rollback.
For information on obtaining your Level 1 SQL certification and on writing queries, talk to you district's Project Coordinator or Account Executive.
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 box at the top of the screen.
3. To aid in writing queries, click the Table List for a complete list of tables.
4. Click the listed table of interest to open associated fields. You can then click the Create Select Query to automatically generate a query to pull data as it pertains to the select table.
Start typing the name of a table or a field to find the corresponding table in the Filter text box.
To close the Table List click the icon again.
5. Click the Query Log icon to open a history of the last 25 queries ran.
To open the query from your log, click the query link, which will populate the query text box automatically.
Click the X to close the Query Log.
6. Click the Readonly text box 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.
7. Click the Transaction check box 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.
8. Click the Single Query check box to take every line of code and run it all at once as if it were a single query. This is important when you're creating things like stored procedures or functions since they can, and often do, use more than one query. If the Single Query check box is not selected, the PDO driver (the protocol that transmits the code you type to the database) won't know that the code should be run as a single query and will instead try and run every individual query inside it.
9. 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.
10. Select the Preprocess check box to run Postgres type casts in the SQL Server. Empty arrays will be converted to NULL instead of throwing a SQL syntax error. Queries with LIMIT/OFFSET are converted to the cross-DB OFFSET/FETCH syntax automatically.
11. Select the Word Wrap check box to format the text in the query results in word wrap; i.e. if the information goes beyond the provided area, you can wrap the text and extend the box to make the information more readable.
12. Select the Export INSERT statements check box to format the output of a SELECT query into an INSERT query syntax with a [table] placeholder, which allows you to update data using the table to which data is to be inserted.
13. Select the Export CSV check box to download the query results in a CSV file to your computer.
14. Select the Export JSON check box to download the query results in a JSON file (JavaScript Object Notation).
15. Select the Autocomplete check box to allow the system to make recommendation upon typing queries; i.e. when you begin typing the name of a table, several possible options will displays based on the information typed. From here, you can select the applicable option instead of having to type it out.
16. Click the ? icon for Help, which includes Keyboard Shortcuts and Tips.
Click the X to close the Help window.
17. 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.
18. Once a query has been entered and all selections have been made, click Submit for query results, which display on the bottom of the screen.
19. 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.
20. Click the Excel icon to download the query results to an Excel spreadsheet.
The Time is took for the query to pull the applicable results displays, as well as the number of Records, and the number of records Showing.
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.
To sort results, you can click the headers. Click once for ascending results; click twice for descending results. In the image displayed, LAST_NAME has been clicked once.
