Creating SQL Statements
SQL (Structured Query Language) enables you to select data from database systems, such as Oracle, Microsoft SQL Server, and Microsoft Access. This topic provides a basic introduction to using SQL to retrieve data from a Site Database created by Vector Asset Management.
The Select Statement
When creating SQL statements which retrieve data, use the SELECT keyword. The syntax for a simple SELECT
statement is:
SELECT <fields to select> FROM <table name>
A simple SQL statement that selects all the records and fields in the NODES table. Note that the
asterisk ( * ) is a wildcard character that is used to specify all fields:
SELECT * FROM NODES
Similarly, you can use an asterisk to select all records in the Packages table. This table lists all
the applications which can be identified by Vector AM:
SELECT * FROM PACKAGES
Adding Criteria to Select Statements
You can add criteria to SQL statements using the WHERE keyword. For example, to show all Clients that
belong to a Department called Sales:
SELECT * FROM NODES WHERE DEPT LIKE 'sales%'
In this example, the LIKE operator is specified so that the SQL statement evaluates which records
of data should be retrieved. The percentage ( % ) character is used as a wildcard to pick up any characters
which follow the specified text (in this case sales) so that "sales1", "sales2" and
"salesteam" could all be detected.
The following table summarizes the simple operators you can use:
Operator |
Description |
= |
Equal to |
<> |
Not equal to |
< |
Less than |
> |
Greater than |
<= |
Less than or equal to |
>= |
Greater than or equal to |
LIKE |
A more flexible version of "equal to". The percent symbol ( % ) is used to represent any possible character (number, letter, or punctuation), or set of characters, which might appear before or after a given character. For example, to find Clients belonging to departments beginning with "A", such as ADMIN and ACCOUNTS, use 'A%'. The percent symbol can be used to represent any characters in the same position relative to the given characters: SELECT * FROM NODES WHERE DEPT LIKE 'A%' |
Advanced Criteria in Select Statements
Additional, logical and compound Operators enable you to create advanced criteria in your SQL statements:
Operator |
Description |
AND |
Joins two or more conditions, and displays a row only if that row's data satisfies ALL conditions listed. |
OR |
Joins two or more conditions, but returns a row if any of the conditions listed hold true. |
IN |
Simplifies creating compound statements by enabling you to create a list of possible matches. For example: SELECT * FROM NODES WHERE DOMAINNAME IN ('ENGINEERING', 'SALES') |
BETWEEN |
Enables you to specify the outer values of a range. For example, to find all Clients which have a department beginning with a letter in the first half of the alphabet (in other words, between a and n): SELECT * FROM NODES WHERE DEPT BETWEEN 'a%' AND 'n%' |
NOT |
Usually positioned before a Operator such as IN, BETWEEN or LIKE, this can be used to exclude, rather than include, records which match the criteria. For example, to select all Clients except for those in the Accounts department: SELECT * FROM NODES WHERE DEPT NOT LIKE 'Accounts%' Note that AND and OR operators can also be combined in a single SQL statement. In such cases, SQL evaluates all of the conditions, then evaluates the ANDs, and then evaluates the ORs (where both operators evaluate from left to right). |
Selecting Data from Multiple Tables
When working with multiple tables in SQL, you must consider any relationships
that exist between the data in the tables. There are two alternate methods of selecting data in multiple
tables using SQL.
Using JOINs
The first method is using JOIN syntax, in which the table relationships are defined in the SQL by
specifying equivalent fields in the tables you want to join. To join two or more tables, there needs to
be data that is common to both tables. In many instances, these joined fields include the NODENAME field.
This method is used in many SQL definitions for dynamic groups. For example:
SELECT * FROM NODES INNER JOIN PKGRPTDB ON PKGRPTDB.NODENAME = NODES.NODENAME WHERE PKGRPTDB.PACKAGE
LIKE 'WinZip%' AND PKGRPTDB.STATUS NOT LIKE 'Deleted'
The JOIN statement is saying: The NODES table is joined to the PKGRPTDB table via the NODENAME field
which exists in each table.
While this syntax can seem daunting, the basic approach and SQL structure can easily be modified to
accommodate querying requirements.
The following example uses a JOIN statement to select Client Properties data:
SELECT * FROM NODES INNER JOIN NODEPROP ON NODEPROP.NODENAME = NODES.NODENAME WHERE NODEPROP.PROPNAME
= '$LOCATION' AND NODEPROP.PROPVALUE LIKE 'upstairs%'
Specifying Relationships in Criteria
An alternative to using complex JOIN statements is to specify the relationship using comparative field
criteria. This states which fields in one table must match which fields in another table.
SELECT NODES.NODENAME FROM NODES, PKGRPTDB WHERE PKGRPTDB.NODENAME = NODES.NODENAME AND PKGRPTDB.PACKAGE
LIKE 'WinZip%' AND PKGRPTDB.STATUS NOT LIKE 'Removed'
The 'FROM....' clause lists the tables from which the data is to be selected
The relationship between the two tables is defined as part of the criteria. In the above example,
the NODENAME field in the PKGRPTDB table must equal the NODENAME field in the NODES table. Many users
and many Database Management Systems prefer this approach.
Selecting DISTINCT Records
The SQL keyword DISTINCT eliminates duplicate results from your query results.
Related Topics