SQL instructions are written 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 allows 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.) An asterisk (*) can also be used 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 that include:
- The FROM clause indicates the table(s) from which data is to be retrieved. The FROM clause can include optional SQL Join Optimization sub-clauses to specify the rules for joining tables.
- The WHERE clause includes one or more expressions, which 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.
- The ORDER BY clause identifies which columns are used 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>
For more information about each SQL clause, see the following sections:
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.
ATTENTION: SQL queries should be written by engineers who have the appropriate experience and knowledge of the SQL language. Running a badly designed SQL query can have an adverse affect on the system. All queries should include suitable restrictions to limit the number of records that are retrieved from the database. An SQL query that queries vast amounts of data will affect the performance of the server while that query executes.
Further Information
Restrictions that apply to queries used for GIS Map Markers: see Designing Queries for GIS Map Markers in the Geo SCADA Expert Guide to Mapping and Geographic Information.