Creating the query for a report involves using SQL statements. SQL is a Structured Query Language for retrieving data from databases. SQL statements can be simple or complex, depending on the data that you want to find and how you want it to display. The key is to know what you want to see in your report, and understanding what kind of data is available to you.
For example, here’s a simple SQL statement:
SELECT Owner FROM Device
This query looks for the listed owners of registered mobile devices, as recorded in the Owner column of the Device table.
The main component of a SQL query is the SELECT statement. SELECT does just that - it selects which data to display. You can select one or more columns from one or more tables to retrieve. You can use any of the following SELECT statements in Admin Portal report queries:
SELECT: Selects data from the specified columns in the specified tables.
SELECT *: Selects all records from the specified table.
SELECT DISTINCT: Selects the unique records from the specified columns in the specified tables. The DISTINCT keyword trims out the duplicate records.
If you want to look at columns in different tables, you can also combine the results by using UNION or one of the JOIN statements.
In addition to selecting the database tables to retrieve, you can also provide conditions to further refine your query results. You can use any of the following SQL statements to specify conditions:
AND / OR: Selects data that meets both conditions (
AND) or one of the specified conditions
BETWEENto select results that are within a specified range.
IN / NOT IN: Use
IN or NOT INto specify multiple values in a
LIKEto search for a specified pattern in a column.
WHEREto specify criteria to filter for, such as column values and so forth.
Note: Admin Portal uses a subset of SQL-92 that only supports SELECT statements. SQL commands that change database values are not valid (
CREATE, ALTER, DELETE, DROP, INSERT, SELECT INTO, TRUNCATE, UPDATE, and so forth).