A Structured Query Language Primer
 |  |  |  |
 |
| |||||||||
 |
| ||
As the name implies, SQL is used to query a database to ask for specific data to be returned. As a database interaction language, SQL provides other features as well. Here’s a summary of what a generic SQL interface provides:
-
Data retrieval through the query facility
-
Calculations (SQL expressions) on stored data
-
Filtering, sorting, and grouping of data
-
Creation of the database structure itself
-
Definition of relationships between data elements
-
Insertion of new data
-
Update of existing data
-
Deletion of existing data
-
Enforcement of data-integrity constraints
-
Granting of security through privileges assigned to groups and individuals
Retrieving Data
Crystal Reports and report designers focus on the data-retrieval component of SQL, which is performed by the SELECT statement. This statement is read-only in the sense that executing a SELECT statement does not make any changes to the data or the database. A SELECT statement finds and retrieves columns of data from a database by searching for rows that meet criteria you specify. The basic syntax of a SELECT statement is
SELECT <column(s)> FROM <table>
where column(s) is the actual column name from the database and table is the actual table name. Let’s look at how to use the SELECT statement by working with several business questions on the VistaNations database, which is being accessed as an ODBC data source.
Business Question: In which cities does VistaNation have resorts?
To retrieve the names of all the cities that have resorts in them, the SELECT statement would look like this:
SELECT City FROM Resorts
This statement returns a set of records, also known as a result set, that resembles Figure 11.6. SQL statements are not case-sensitive; the mix of casing used here is for stylistic purposes only. The results in Figure 11.6 were generated using the Crystal SQL Designer add-on tool described in Chapter 10, “Data Sources and the Database Expert.”
| Note | All the queries and results shown in this chapter were generated using Crystal SQL Designer, an add-on utility that is part of the Crystal Reports 9 Data Compatibility toolset. You can download the Data Compatibility tools from Crystal Decisions using http://support.crystaldecisions.com/communityCS/ FilesAndUpdates/cr9_data_tools.zip.asp as the URL address. |
A few things should jump out at you when you view the results of this simple query. First, there are duplicates in the list, and second, the results are not sorted. Also, the bottom of Figure 11.6 shows that we retrieved 201 records. By default in a SELECT statement, records are returned in the order in which they were entered into the database. There are several keywords you can use with a SELECT statement to refine both the number of rows that are returned and the order in which they are returned. Let’s use the DISTINCT keyword to see what difference it makes. The new query is presented below and its results are displayed in Figure 11.7:
SELECT DISTINCT City FROM Resorts
Figure 11.6. Selecting cities
Figure 11.7. Selecting distinct cities
The number of records returned has been reduced to 171 and the list has been sorted. The DISTINCT keyword picks out the first occurrence of a value so that the list contains no duplicates; in addition, it automatically sorts the records in ascending order. This answers the business question a bit better. So the DISTINCT keyword is one way to reduce or refine the number of records returned by your query.
Refining a SELECT Statement
Without the use of keywords to modify the behavior of the SELECT, a query returns every row that matches your criteria. The DISTINCT keyword is one way to limit the rows returned, but it works on only one column. The most powerful way to fine-tune a query is to write a good WHERE clause, which has the general format of
SELECT <column(s)> FROM <table> WHERE column <operator> value
A WHERE clause filters out data records that do not meet the specified condition, which has the potential to reduce the number of records returned by a query. It sets up a condition that must be true in order for the record to be retrieved from the database. The condition is formulated by comparing a column value to either another column value or a specific value that you provide. A WHERE clause can contain multiple condition statements each separated by the logical conditions AND and OR. Table 11.1 lists the operators that are valid in a WHERE clause.
| Operator | Description |
|---|---|
| = | Column value is equal to a value you specify. |
| <> | Column value is not equal to a value you specify. |
| > | Column value is greater than a value you specify. |
| < | Column value is less than a value you specify. |
| >= | Column value is greater than or equal to a value you specify. |
| <= | Column value is less than or equal to a value you specify. |
| BETWEEN | Column value is greater than or equal to one value that you specify while at the same time being less than or equal to a second value that you specify. |
| IN | Column value is one of a set of values that you specify. |
| AND | Both conditions must be true to satisfy the criteria. |
| OR | Either condition can be true to satisfy the criteria. |
As an example of a WHERE clause, let’s try to answer a business question for the VistaNations folks.
Business Question: How many five-star resorts does VistaNation have in the United States?
The query makes use of two conditions that must be met in order for a row to be returned in the result set. The results of the query are shown in Figure 11.8.
SELECT COUNT(ResortCode) FROM Resorts WHERE FiveStarRating = True AND Country = 'US'
Figure 11.8. Using a WHERE clause
This query uses a built-in function called COUNT to operate on the ResortCode column. The result of this query is a single number representing how many rows meet the criteria, in this case 80. Notice in Figure 11.8 that the column name for the result is provided as Expr1000; this is called a SQL expression. Since a SQL expression is a calculation and not an actual column in the table, the column header reflects this. The general syntax for using a built-in SQL function in a SELECT is
SELECT function(column) FROM table
Table 11.2 describes several other popular SQL functions that can be used on column data.
| Function | Description |
|---|---|
| AVG | Returns the average of a column of numeric data. |
| COUNT | Returns a whole number representing how many rows meet the WHERE criteria for the column being counted. |
| COUNT(*) | Returns a whole number representing how many rows meet the WHERE criteria for the entire query. |
| MIN | Returns the smallest value in a column of numeric data. |
| MAX | Returns the largest value in a column of numeric data. |
| SUM | Returns the sum of all the values in a column of numeric data. |
Grouping and Sorting
The result of a SELECT statement is a set of data records. This result set is held in memory in the database until processing is complete and then returned to Crystal Reports. While this temporary result set is in memory, it can be grouped, sorted, and filtered using SQL SELECT keywords. Let’s vary the business question just a little and see how these keywords work.
Business Question: How many five-star resorts are within each state and zip code in the United States? Create the list in reverse order by state.
To following query answers this business question and its results are shown in Figure 11.9:
SELECT COUNT(StateProvince), StateProvince, PostalCode FROM Resorts WHERE FiveStarRating = True AND Country = 'US' GROUP BY PostalCode, StateProvince ORDER BY StateProvince DESC
Figure 11.9. Grouping and sorting
In this query, two actual columns are retrieved from the table and one aggregate function or SQL expression is used. The results are grouped by state and presented in reverse order by state, but within the state the zip codes are sorted in ascending order. Things are getting pretty complex! Having complete control over the SQL statement generating the data helps you guarantee that you’re answering the business question correctly.
SQL Joins
In Crystal Reports, the Database Expert is used to create joins by visually linking one column to another column. In a SELECT statement, keywords are used to perform joins except for the default join type.
In Chapter 9, “Working with Multiple Tables,” we discussed the concept of inner joins, left outer joins, right outer joins, and full outer joins. Depending on which join types are supported by the data source, join conditions can be included in a SELECT statement. If you don’t specify a join condition when using multiple tables with a SELECT statement, a default inner join is performed, meaning that every record in the first table is joined with every record in the second table if the WHERE clause condition is true.
The use of keywords in a SELECT statement pushes a good deal of processing back onto the database and results in fewer data records being sent over the network wires into Crystal Reports.
| Warning | Left and right outer joins performed by ODBC will return different results than left and right outer joins performed by native database drivers due to implementation differences and generic assumptions made in ODBC. |
A summary of the keywords and the syntax for using them is described in Table 11.3.
| SQL Keyword | Syntax |
|---|---|
| DISTINCT | SELECT DISTINCT column FROM table |
| COUNT | SELECT COUNT(column) FROM table |
| WHERE | SELECT column1, column2, column3 FROM table1, table2 WHERE table1.column1 = table2.column2 |
| IN | SELECT column1, column2, column3 FROM table WHERE column1 IN (value1,value2) |
| BETWEEN | SELECT column1, column2, column3 FROM table WHERE column1 BETWEEN value1 AND value2 |
| AND | SELECT * FROM table \ WHERE ((column1<value) AND (column2=value)) |
| OR | SELECT * FROM table WHERE ((column1<value) AND (column2=value)) |
| ORDER BY | SELECT * FROM table ORDER BY column |
| ASC | SELECT * FROM table ORDER BY column ASC |
| DSC | SELECT * FROM table ORDER BY column DESC |
| GROUP BY | SELECT function(column1), column2 FROM table GROUP BY column2 |
| HAVING | SELECT column, SUM(column) FROM table GROUP BY column HAVING SUM(column) condition value |
| INNER JOIN | SELECT column1, column2, column3 FROM table1 INNER JOIN table2 ON table1.keyfield = table2.foreign_keyfield |
| LEFT OUTER JOIN | SELECT column1, column2, column3 FROM first_table LEFT OUTER JOIN second_table ON table1.keyfield = table2.foreign_keyfield |
| RIGHT OUTER JOIN | SELECT column1, column2, column3 FROM first_table RIGHT OUTER JOIN second_table ON table1.keyfield = table2.foreign_keyfield |
| UNION | SELECT * FROM table2 UNION SELECT * FROM table2 |
ODBC and SQL Statements
ODBC is a middle layer between a requesting application and a data source. The data source has its own proprietary SQL dialect. ODBC has its own version of SQL as well. With each call made to an ODBC data source, ODBC makes data requests using a generic version of SQL that has been designed to work in tandem with the data source being queried. This often results in translations from the SQL statements that you would use directly in the data source versus those that ODBC will use. For instance, in the query that counted resort codes with five-star ratings in the United States, the following two SQL syntaxes should be considered:
Query in Microsoft Access directly:
SELECT COUNT(ResortCode) FROM Resorts WHERE FiveStarRating = "Yes" AND Country = "US"
Query using ODBC driver to Microsoft Access:
SELECT COUNT(ResortCode) FROM Resorts WHERE FiveStarRating = True AND Country = 'US'
While both queries return the same result of 51 resorts meeting the criteria, note the syntactic differences that the ODBC driver used, namely:
-
The FiveStarRating field was treated as a Boolean data type instead of character data.
-
The string comparison on Country required single quotes instead of double quotes.
The syntax and conversions required depend on the software tool being used, the version of the ODBC driver, and the ODBC support provided by the database vendor. Do not be surprised by syntactic differences like the ones shown here; an ODBC driver is a different access mechanism into the database than the native SQL drivers. Most of the differences are explained by the need for ODBC to service a multitude of data source types while individual native SQL drivers are tuned for exactly one data source.
| Note | Boolean data is represented with the keywords True and False. It is equivalent to the character values of "Yes" and "No" as well as the numeric values of 1 and 0. |
| ||
 | |
| Use of content on this site is expressly subject to the restrictions set forth in the Membership Agreement | |
| Conello © 2000-2003 Feedback | |
EAN: 2147483647
Pages: 217