DataSplice Releases Mobile Integration Suite 3.0
Basic SQL Information
Generating DataSplice View Data with SQL
The DataSplice Administration Client application uses SQL queries to determine which records, and which fields in those records, will be included in a particular view. A base SQL statement is used in the base SQL statement section of the view. This determines which fields will be included in the view. SQL statements can also be used for editing filters, queries, and relationships. The DataSplice Remote Client application uses SQL for filters as well. However, the Remote Client filter is applied only to the view of that particular user, not all users with access to that view.
SQL stands for "Structured Query Language" and is often pronounced "sequel" instead of "es-que-el". SQL is a standardized, non-procedural language used to communicate with a SQL-compliant database in order to read, write, and delete information from the database.
Basic SQL Queries
SELECT
A SELECT query is used to extract information from the database. In its simplest form, it states what data is to be extracted and from which table the data should be extracted.
Information regarding a customer named Nelson is needed. Assuming the database table (called CUSTOMERS) looks like this:
| CUST_ID | FIRST_NAME | LAST_NAME | LAST_PURCHASE |
|---|---|---|---|
| 1 | Tom | Servo | December 5, 1996 |
| 4 | Mike | Nelson | January 3, 1999 |
| 22 | Joel | Robinson | October 1, 1988 |
The query would look like this:
SELECT * FROM CUSTOMERS
WHERE LAST_NAME = "Nelson";
And would return these results:
| CUST_ID | FIRST_NAME | LAST_NAME | LAST_PURCHASE |
|---|---|---|---|
| 4 | Mike | Nelson | January 3, 1999 |
The query itself can be broken down into several sections:
This particular query is looking for all customers with the last name beginning with "Nel"
1. SELECT FIRST_NAME
2. FROM CUSTOMERS
3. WHERE LAST_NAME
4. LIKE "Nel*"
| 1 | SELECT | Tells the system what type of query will be run. The field after it (FIRST_NAME) tells it what will be viewed. |
|---|---|---|
| 2 | FROM | Chooses the database table to be examined. |
| 3 | WHERE | Sets the criteria, or parameters, for the data within the field. |
| 4 | LIKE | Sets the conditions of the query, whether it is an exact or a close match. This could also be any of a number of logical operators. |
Logical Operators
Logical operators allow you to choose how you would like to compare information in your query.
| = | Equal To |
|---|---|
| < | Less Than |
| > | Greater Than |
| <= | Less Than or Equal To |
| >= | Greater Than or Equal To |
| <> | Not Equal To |
| LIKE | Compares an item using a wildcard |
| IS NULL | Returns values which are null |
| IS NOT NULL | Returns values which are not null |
Payroll needs to know who accrued overtime in the 12th week of the year. The query would look like this:
SELECT NAME FROM PAYROLL
WHERE HOURS_WORKED > 40 AND WEEK = 12;
This will return all of the employees who have worked more than 40 hours in week 12.
Conversely, payroll may need to know who did not accrue overtime. In that case, the query would look like this:
SELECT NAME FROM PAYROLL
WHERE HOURS_WORKED <= 40 AND WEEK = 12;
This will provide the desired names.
Complex SQL Queries
Wildcards
Wildcards allow queries to be performed using partial information.
Data on a client is needed, but no one can remember the name of the client. Someone remembers the last name begins with "Ser". The query would look like this:
SELECT * FROM CUSTOMERS
WHERE LAST_NAME LIKE "Ser%";
This will return all the data on any customers with a last name beginning with "Ser", including "Servo", the fellow being sought.
Notice the use of the comparison LIKE. Additionally, the wildcard used in this case, as in most cases, is the percent sign. Some databases use other wildcards such as an asterisk. Check the documentation for the database to be used for information regarding which wildcard to use.
Distinct
The keyword DISTINCT, when used in an SQL query, will return only one instance of each item searched for, regardless of how many times it appears in the database.
A company would like to know in how many states they have clients. The query would look like this:
SELECT DISTINCT STATE FROM CUSTOMERS
The query could return the following results, even if the company has 10,000 customers in each state, because DISTINCT has removed the duplicates:
| STATE |
|---|
| TX |
| CO |
| MN |
| IL |
| OH |
Additional Information
SQL queries are capable of accessing information in multiple tables and performing operations on that information such as averaging. For more in-depth information regarding SQL queries, please go to http://www.sql.org.
Last modified 2005-12-08 12:33 PM
