Introduction to SQL Query Structure

You write SQL instructions in the form of statements, consisting of a specific statement and additional parameters and operands that apply to that statement. SQL statements and their modifiers are based upon official SQL standards and certain extensions relating to the specific database.

The most common operation in SQL is the query, which is performed with the declarative SELECT statement. SELECT enables you to retrieve data from one or more tables, or expressions.

A query contains the SELECT keyword, followed by a list of columns that are to appear in the resultant output. (Each column comprises the result of an expression, which can optionally reference one or more database fields.) You can use an asterisk (*) to specify that the query should return all columns of the queried tables. SELECT is perhaps the most complex statement in SQL, with optional keywords and clauses. These can include the following:

• Use the FROM clause to indicate the table(s) from which the query is to retrieve data. You can include optional JOIN sub-clauses in the FROM clause to specify the rules for joining tables.
• Use the WHERE clause to include one or more expressions to restrict the rows returned by the query. The WHERE clause eliminates all rows from the result set for which an expression does not evaluate to True.
• Use the ORDER BY clause to identify the columns by which the query is to sort the resulting data, and in which direction they should be sorted (options are ascending or descending). Without an ORDER BY clause, the order of rows returned by an SQL query is undefined

In order to retrieve data from the Geo SCADA Expert database and display that data, an SQL SELECT query should take the following basic format:

SELECT

<field>, <field>, <field>

FROM

<database table>

WHERE

<field> = 'Value'

ORDER BY

<field>

The SQL syntax and execution system in Geo SCADA Expert functions closely to that of a general purpose SQL database but with some differences:

• The SQL syntax includes unique variations and omissions designed specifically to enhance its usefulness for SCADA applications.
• Performance of the SCADA system is paramount. Therefore, we recommend that you design your queries offline from production to avoid or minimize complex or long-running queries.
• The performance of complex JOINs is particularly notable. We advise that you use QueryPad's reporting data to review SQL execution. The execution plans reported in QueryPad are a guide to how the query is run, but there may be variations when queries are actually executed.
• Geo SCADA Expert uses various indexing techniques to enhance performance, but inefficient queries can still occur. To optimize performance, use identity fields rather than names or strings for JOIN operations, and monitor indexing performance through the Server Status Tool. For more information about the latter, see Query Processor Status Information.

For more information about each SQL clause, see the following sections:

• SELECT Clause
• FROM Clause
• WHERE Clause
• ORDER BY Clause.

Be aware that you may also want to consider including other SQL clauses, such as the GROUP BY clause, and the HAVING clause in your SQL query.