The Cold Fusion Web Database Construction Kit
You now should be familiar with the basic aspects and concepts of SQL and what this powerful language can do for you and your application. In this chapter you have the opportunity to explore the more advanced features of SQL and learn how this database language can reduce the amount of code you need in your application.
SQL enables you to search multiple tables simultaneously. You'll learn how to do this by using sub-queries or joins. You will also learn how to take advantage of many of SQL's built-in functions, which can save you numerous hours of coding. Functions such as aggregates, provided by SQL, can save you a significant amount of time writing code, and in some instances provide you with flexibility that you could not otherwise obtain within your application.
This chapter will provide you with enough information to further explore the potential SQL can have on your Cold Fusion application.
A sub-query is a SQL SELECT statement within another SQL SELECT, INSERT, UPDATE, or DELETE statement, or within another sub-query. This functionality within SQL takes advantage of one of the most powerful concepts within the language: its ability to simultaneously search two or more tables. Another way for you to think of a sub-query is as a SQL SELECT statement dependent upon the result of another SQL SELECT statement. Generally speaking, you evaluate sub-queries from the inside out, meaning that the statement evaluated first is the last statement, or inner query in your sub-query.
The outer query uses a WHERE or HAVING clause to connect itself to the inner query. These clauses can use the IN or NOT IN expression to determine the existence of a field value in the inner query. The inner query must be a SELECT statement which closely follows the syntactical rules of the SELECT statement with the noticeable exception of the SELECT INTO ability. You cannot modify data within an inner query. The ORDER BY clause is also restricted from use within a sub-query.
Outer query--An outer query is the outside, or first query in the sub-query. In Figure 16.1, it is the portion of the query that selects from the Employees table.
Inner query--An inner query is the inside, or last query in the sub-query. In Figure 16.1, it is the portion of the query that selects from the Vacations table.
TIP: It's a good idea to indent the inner queries a couple of spaces to the right of the outer query. This will help you read the query and determine what the query is doing. You may also find that placing Boolean operators on a separate line and indenting them will help you read and write queries. Notice in Figure 16.1, the inner query is indented a space to the right to make it easier to read.
Figure 16.1 This query statement lists employee ID, first name, and last name from the Employees table for corresponding employee IDs in the Vacations table.
Figure 16.1 shows the inner query returning a subset of all employees who have taken or scheduled vacation. The outer statement will return the ID, first name, and last name for each ID returned from the inner query. The inner query returns the set of employees who have taken or scheduled vacations, and the outer query lists selected attributes for rows corresponding to the set derived by the inner query. In this case only one employee was scheduled to or has taken a vacation. Figure 16.2 shows the results.
Figure 16.2 The query in Figure 16.1 returns only one record because there is only one record in the Vacations table that corresponds with an employee ID in the Employees table.
TIP: You may want to initially write your queries in an interactive SQL environment to ensure they will return the expected results before placing them into your Cold Fusion application. You can use MS Access to do this. From the Database window, select the Query tab and then click New. This causes the New Query window to appear. Next, highlight the Design View option and click OK. The Show Table window will appear and from here you can select the table or tables you wish to use in your query by highlighting the table and clicking Add. When you're finished selecting tables, click the Close button. To enter Access's SQL environment, click the View menu and select SQL. Access will write a SELECT query for you; however, you can change the query. To execute the query, click the red exclamation mark on the menu bar. If the query runs successfully, results will be returned. When you exit from this environment you will be prompted to save the query. You can then Copy and Paste the SQL into your Cold Fusion Template but some tweaking is required, like changing quotes to apostrophes.
Another way you can evaluate sub-queries is with an expression. Expressions return a value by using an arithmetic or comparison operator. The expression should return only a single value when used within a sub-query. You can also use Boolean expressions. Boolean expressions can be used to connect expressions together. See Table 16.1 for a list of commonly used Boolean operators.
| Operator | Returns When |
| AND | both values are true |
| OR | one or both values are true |
| NOT | a value is not true |
Figure 16.3 uses "=" as an expression to evaluate an exact match for rows with OrderID in the Orders table equal to the same column in the OrderItems table.
Figure 16.3 This query uses the `=' operator as an expression to select an exact match on the OrderID field.
Figure 16.4 shows the results of the sub-query in Figure 16.3. This query failed because the inner query returned more than one result. This happened because there are multiple records in the OrderItem tables with the same OrderID as an OrderID in the Orders table. Note that this query would have worked if the IN keyword had been used instead of =.
Figure 16.4 This query failed because it tried to return more than one row with the same value for OrderID.
Correlated sub-queries are sub-queries where the query is dependent upon the outer query for its values. You'll find it necessary to use correlated sub-queries a lot. The inner query of the sub-query in Figure 16.5 is dependent on the outer query for the search condition, which in this case is "AL".
Figure 16.5 The search on the Orders table in this statement is dependent on criteria established in the outer query, in this case customer ID from the Customers table.
Many times when you use a sub-query to extract data from a database, all you need from the inner query is to determine the existence of data for a certain condition. SQL provides you with a predicate that does just this: EXISTS, or NOT EXISTS, checks for the existence or absence of data. The use of an EXISTS statement almost always constitutes a correlated sub-query. The following statement in Figure 16.6 returns the same result set as the previous statement in the correlated example.
Figure 16.6 Sub-query with an EXISTS statement.
Data manipulation using sub-queries works similarly to a SELECT statement. The rule that you must follow here is that you can only manipulate data in the outer query. You can use the INSERT, UPDATE, and DELETE statements in your outer query to modify data based on conditions in your inner queries. In the following example you'll see how to insert data using a sub-query; keep in mind that later in this chapter you'll see easier ways to do this using a join statement.
Figure 16.7 illustrates a DELETE statement as a sub-query. In this instance, all customers who had books shipped to Alabama will be deleted from the Customers table. When using the INSERT, UPDATE, and DELETE statements as a sub-query you will need to adhere to the syntactical rules that normally apply to them.
Figure 16.7 This query deletes records from the Customers table where the customer ID is in the Orders table and the Orders state field has `AL' in it.
When you have more than two queries forming a sub-query, it is called a nested sub-query. The same rules apply--you can only manipulate data in the outer query. You can nest many queries together; however, after the third or fourth query they can become difficult to write. Nested sub-queries are very good candidates for testing results within an interactive SQL environment.
TIP: Queries nested more than three deep can become very cumbersome to debug and interpret. Should you become lost within a sub-query, try writing the query incrementally and testing each increment.
You'll want to limit the number of queries you nest to three or four. When you nest more queries than this they become difficult to comprehend. Take a few seconds to walk through how your database will have to retrieve the data.
The nested query illustrated in Figure 16.8 returns the list of all books ordered by customers from Alabama. This query illustrates the foreign key relationship of the CustomerID in the Orders table to the CustomerID column in the Customers table. It also shows the foreign key relationship of the OrderID column between the Orders and OrderItems tables.
Figure 16.8 This nested query selects a list of book IDs ordered from Alabama.
Another useful predicate available to you from SQL is DISTINCT. DISTINCT enables you to select unique or distinct rows. Should you choose to use this keyword in a sub-query, you are restricted to using it in the outer query.
The query in Figure 16.9 selects all the unique values in the State column of the Employee table, in effect giving you all the states in which company employees live.
Figure 16.9 In this query the DISTINCT predicate provides a list of distinct states from the Customers table.
SQL provides you with numerous functions you can use to retrieve data from your applications database that will save you significant time in coding. A subset of these functions is called aggregates. You can use aggregates to return a count of the number of records you have in a table, or for a specific condition. Aggregates will also sum, average, and return minimum and maximum values for a retrieval. Table 16.2 lists commonly used aggregate functions.
| Name | Function |
| AVG | Determines AVG on non-NULL numeric fields |
| COUNT | Derives count of a row for a condition |
| COUNT(*) | Derives a total count |
| MAX | Returns maximum value within a column |
| MIN | Returns minimum value within a column |
| SUM | Derives the sum of a column |
CAUTION: Be careful when using aggregates such as sum or average on large data sets. These functions can take a significant amount of time to return a result for a large data set. This is because your database engine is forced to do a sequential scan on the table to determine the result of the query.
You can use the AVG function to average numeric columns. The AVG function will ignore null values in deriving a result. The DISTINCT statement can also be used in conjunction with this function.
The example shown in Listing 16.1 returns the average for all the non-NULL values within the SalesPrice column.
SELECT AVG(SalesPrice) FROM OrderItems
You can select a count of the number of records within a table that meet a specific condition. The DISTINCT statement can be used with this statement.
The example shown in Listing 16.2 selects the count of all the unique values within the State column in the Customer table. Functionally, this statement tells you how many different states in which you have customers.
SELECT COUNT(DISTINCT State) FROM Customer
This aggregate function enables you to select the number of rows within a table.
The example shown in Listing 16.3 returns a count of the total number of records within the Customer table.
SELECT COUNT(*) FROM CUSTOMERS
This aggregate function enables you to select the maximum value of a column within a table.
The example shown in Listing 16.4 returns the largest value in the SalesPrice column. Functionally, this returns the highest price of any item sold.
SELECT MAX(SalesPrice) FROM OrderItems
This function is the opposite of the MAX function; it returns to you the smallest value in a row for a column.
The example shown in Listing 16.5 returns the smallest value in the SalesPrice column. Functionally, this returns the lowest price of any item sold.
SELECT MIN(SalesPrice) FROM OrderItems
The SUM aggregate function gives you the ability to sum numeric columns. This function is handy for deriving numbers quickly.
The example shown in Listing 16.6 returns the sum of the SalesPrice column for all rows in the OrderItems table.
SELECT SUM(SalesPrice) FROM OrderItems
Like sub-queries, joins allow you to take advantage of SQL's most powerful function; that is, they allow you to simultaneously search two or more tables at one time to retrieve a desired data set. When you need to search two tables, sub-queries may be easier to conceptualize; however, when the need arises to search more than two tables for a specific data set, joins tend to be easier to conceptualize and work with. You can join two or more tables together, or you can join a table with itself. This is called a self-join. You use comparison operators to join the tables together. With joins you can also select fields to display from more than one table and, of course, you can modify data using joins. You can also perform what is known as an outer join. This is where you select all the rows from one table and restrict the other table in the join to only rows passing the selection criteria. An `*' is used to indicate an outer join. If the `*' is to the left of the `=' sign the join statement selects all the records from the first table. If the `*' is on the right side, it selects all the records in the second table.
TIP: SQL enables you to assign aliases to tables. This functionality can reduce the amount of typing you need to do, or allow you to assign more descriptive names to tables within a SQL statement. All you have to do to use them is assign the table an alias in the FROM portion of the SELECT statement. This is done immediately after you state the table name. You can then reference the table with the alias. Figure 16.10 uses aliases to reference tables. In this example, `a' represents the Employees table, and `b' represents the Vacations table.
The capability to join two or more tables in SQL is what makes this database language popular. You'll find that when the need arises to retrieve data from two or more tables at one time the join statement will be less complicated to conceptualize, write, and test. Joins also provide you with the ability to display columns from multiple tables in one select statement. In order to write a join statement, the tables you use should have a common column that they can match rows from each table on. The relationship of two or more tables is accentuated by their keys. Generally speaking, tables are usually joined by the primary key of one table and a designated foreign key of the other table(s). You may want to refer back to the chapter on Database Fundamentals to refresh your understanding of primary keys and foreign keys, and how to use indexes to enforce them. Relational databases use primary keys and foreign keys to establish and maintain referential integrity between tables. Comparison operators are used to join the tables; these operators are used in the WHERE clause of the select statement. Table 16.3 lists comparison operators that are used frequently.
There are times when you wish to select all records of one of the tables you are joining, even if there are no corresponding records in the other table. You can use an outer join to do this.
| Operator | Function |
| = | equals |
| > | greater than |
| < | less than |
| <> | not equal to |
CAUTION: Should you select from two or more tables and neglect to join them, you will create what is known as a Cartesian product. A Cartesian product is the number of rows in the first table times the number of rows in the second column, and so on for each table in the statement. What will happen in your SELECT statement is that every column in every table will be evaluated against all other columns in the tables selected from each row. If your table has a relatively high number of rows within them, this statement will take a significant amount of time to complete.
The join statement in Figure 16.10 is similar to the sub-query in Figure 16.1. They both return one row and they both return the employees' ID number and name; however, the join enables you to also return the dates for the vacation. The WHERE clause in Figure 16.10 joins the two tables together using the EmployeeID column.
Figure 16.10 This figure illustrates a join on the Employees table and the Vacations table, joining them with the EmployeeID field.
Self-joins allow you to join a table to itself. When you use a self-join you have to assign aliases to the table name for each occurrence within the FROM portion of your SELECT statement. You also have to preface the fields used in the statement with the alias. This is needed so that the database engine can separate the fields for comparison.
As you are probably aware of by now, there are many examples of data retrieval problems that can be solved by either the sub-query or the join statement. So which method do you use? The method you are most comfortable with! If performance is of concern then you are better off using a sub-query to extract the rows than by using a join statement, especially if there are several tables involved. You will, however, encounter situations that only a join can solve and, conversely, situations only a sub-query can solve. The choice here is obvious. For most people the join statement becomes the preferred method of simultaneously searching multiple tables at a time because join statements are easier to conceptualize.
A view provides you with the ability to create a pseudo-table limited to a specific subset of the table or tables from which you create the VIEW. One advantage of VIEWs is that they allow you to select only the subset of data you need, simplifying your queries and allowing you to concentrate only on the data you're interested in. You can also create VIEWs to limit the data selected for specific groups of users, the view saving you the effort of specifying the select conditions for each query. This is because you can provide the select conditions within the VIEW. VIEWs can also act as an additional measure of security for your data by restricting data selected to only the data you wish to be viewed. There are limitations to views. You cannot use a SELECT INTO statement to update a table within a VIEW. Listing 16.7 illustrates the syntax for creating a VIEW.
NOTE: Microsoft Access does not support the ANSI implementation of the CREATE VIEW statement.
CREATE VIEW view name [(column name [column name]...) AS SELECT [Column name]...) FROM TABLE WHERE condition
Once a view is created, you use a SELECT statement to retrieve data from it. Syntactically, the SELECT statement works similarly to one used with a table--the notable exception being that you cannot use a SELECT INTO statement with a VIEW.
Stored Procedures are a collection of SQL statements to which you can pass and from which you can receive parameters. Some Relational Databases allow you to compile them, allowing you to essentially create a SQL program that can be executed from within your application. MS SQL Server 6.5 compiles stored procedures the first time they are used. They are extremely efficient and provide you with the ability to standardize specific functions your applications need to do within the database. You'll find that stored procedures provide an excellent mechanism for enforcing referential integrity within the database and ensuring business rules are adhered to.
MS SQL Server is Microsoft's high volume relational database. This database server is capable of handling numerous simultaneous users at a time. The 6.5 release of MS SQL Server is a rather significant upgrade from the 6.0 release. Improvements include row level locking option on inserts as opposed to page level locking, which was previously the only choice. Microsoft also enhanced SQL Server to work with Exchange. Bundled with SQL Server you'll find a set of procedures that will allow you to interface SQL Server with e-mail. In this section you explore the capabilities of triggers and procedures that allow you to interface SQL Server with e-mail.
Triggers allow you to have an event performed on a table automatically, based on some action performed on that table. This event takes place automatically at the database level and is rather efficient. It is an excellent tool to enforce relational integrity and business rules. SQL Server allows you to have up to three triggers on a table. One UPDATE trigger, one INSERT trigger, and one DELETE trigger. A good application for triggers is in building a history system. You could have a trigger insert a record into a history table when it is deleted from a primary table; the trigger would handle the complete transaction for you. Triggers maintain two temporary tables, INSERTED and DELETED. Triggers can be nested, or perform an action on another table which has a trigger on it, but only if the Nested Trigger option is selected in MS SQL Server's configuration page. You can use UPDATE and DELETE triggers to insert `before change' images to a history table. For example, if you wanted to delete customer records if they have not ordered a book within a year, but wanted to retrieve customer information should an old customer order a book after a year, you could use a DELETE trigger to write the deleted record to history and use an INSERT trigger to check the history table for a customer before inserting them into the Customer table. If they are in the history table, the customer's record can be retrieved from history.
Perhaps the most important method of interfacing SQL Server with your Cold Fusion application is through e-mail. SQL Server 6.5 uses a collection of extended stored procedures to handle calls to Microsoft Mail or Exchange. You'll have the ability to trigger e-mail to go to a particular user. You can also e-mail the results of queries. SQL Server provides you with a couple of stored procedures to send and process mail.
Using sp_processmail. SQL Server uses this stored procedure to process incoming
mail. It will process the mail's text as a single query. You can specify which database
to process your query against by specifying a database in the @dbuse qualifier. You
can also specify file type and separator with the @filetype and the @separator parameters.
The @subject parameter limits the mail processed to just mail with the specified
header. You can use CFMAIL to mail SQL Server a query and schedule sp_processmail
to run it at certain intervals. Listing 16.8 illustrates an example of sp_processmail.
EXEC sp_processmail @subject='COLD FUSION', @set_user = `SQLAdmin', ¬@dbuse='Sales'
Using xp_sendmail. This extended stored procedure is for sending mail. You can place attachments or query results on the message. Listing 16.9 is an example of using the xp_sendmail stored procedure.
xp_sendmail @recipient=boss@company.com,@query='select sum(sales_amt) from sales',@subject='SALES', @attach_results='TRUE',@width=250
In this chapter, you have explored some of the more powerful features of the Structured Query Language. As you become more familiar with this language you will find that it can save you time developing software and provide you functionality that you could not get out of the development software. You've learned that sub-queries and join statements allow you to extract data from one or more tables. This chapter also explored how you can use aggregates to save you time coding and how stored procedures and triggers can help you enforce business rules.
© Copyright, Macmillan Computer Publishing. All rights reserved.
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}