Visual C++ 6 Unleashed

By MICKEY WILLIAMS and David Bennett

• Table of Contents
  • Copyright
  • About the Authors
  • About the Contributors
  • Acknowledgments
  • Tell Us What You Think!
  • Introduction
  • How to Use This Book
  • What You Need to Use This Book
  • What's New in Visual C++ 6.0
  • Contacting the Main Author
  • Part I: Introduction
  • Chapter 1. The Visual C++ 6.0 Environment
  • Part II: MFC Programming
  • Chapter 2. MFC Class Library Overview
  • Chapter 3. MFC Message Handling Mechanism
  • Chapter 4. The Document View Architecture
  • Chapter 5. Creating and Using Dialog Boxes
  • Chapter 6. Working with Device Contexts and GDI Objects
  • Chapter 7. Creating and Using Property Sheets
  • Chapter 8. Working with the File System
  • Chapter 9. Using Serialization with File and Archive Objects
  • Part III: Internet Programming with MFC
  • Chapter 10. MFC and the Internet Server API (ISAPI)
  • Chapter 11. The WinInet API
  • Chapter 12. MFC HTML Support
  • Part IV: Advanced Programming Topics
  • Chapter 13. Using the Standard C++ Library
  • Chapter 14. Error Detection and Exception Handling Techniques
  • Chapter 15. Debugging and Profiling Strategies
  • Chapter 16. Multithreading
  • Chapter 17. Using Scripting and Other Tools to Automate the Visual C++ IDE
  • Part V: Database Programming
  • Chapter 18. Creating Custom AppWizards
  • Chapter 19. Database Overview
    • ODBC 4.0
    • OLE DB and ATL's Database Classes
    • ActiveX Data Objects (ADO)
    • MFC's Database Classes
    • Structured Query Language
    • Data Definition Language
    • Data Control Language
    • Data Manipulation Language
    • Summary
  • Chapter 20. ODBC Programming
  • Chapter 21. MFC Database Classes
  • Chapter 22. Using OLE DB
  • Chapter 23. Programming with ADO
  • Part VI: MFC Support for COM and ActiveX
  • Chapter 24. Overview of COM and Active Technologies
  • Chapter 25. Active Documents
  • Chapter 26. Active Containers
  • Chapter 27. Active Servers
  • Chapter 28. ActiveX Controls
  • Part VII: Using the Active Template Library
  • Chapter 29. ATL Architecture
  • Chapter 30. Creating COM Objects Using ATL
  • Chapter 31. Creating ActiveX Controls Using ATL
  • Chapter 32. Using ATL to Create MTS and COM+ Components
  • Part VIII: Finishing Touches
  • Chapter 33. Adding Windows Help
  • Part IX: Appendix

Data Manipulation Language

In the previous sections, you looked at the parts of SQL used to set up your databases. Now you come to the juicy bits where you actually get your hands on the data and look at the real meat of SQL—Data Manipulation Language (DML). DML consists of the following basic types of statements:

• SELECT: Queries the database.
• INSERT: Inserts data into a table.
• DELETE: Deletes data from a table.
• UPDATE: Changes data in a table.

You can probably guess what each of these statements do, but let's look at the specifics of each in the following sections.

SELECT Statements

SELECT is the command that lets you read from your tables. It is one of the most complex SQL commands. Its format is the following:

SELECT  {  DISTINCT  } | select-list  |     *     |
FROM table_list
{ WHERE condition }
{ GROUP BY column_name}
{ HAVING condition }
{ ORDER BY  field_list {DESC }}

The components of the SQL SELECT command are as follows:

• DISTINCT: If DISTINCT is specified, all duplicate rows are eliminated. Any rows that have information that isn't exactly duplicated by another row is returned.
• select-list: The select-list is the list of column names you want selected, separated by commas. An asterisk (*) in the place of the select-list will cause all columns to be selected.

• FROM table_list: The FROM clause lists the tables to SELECT data from.
• WHERE condition. The WHERE clause eliminates rows from the result that don't meet some condition.
• GROUP BY column_list: The GROUP BY clause groups together multiple rows from the database, based on unique values found in your column_list. GROUP BY is necessary for aggregate functions, like SUM, COUNT, and AVG.
• HAVING condition: The HAVING clause must be accompanied by a GROUP BY clause. The condition of the HAVING clause eliminates rows from the result, much like the WHERE clause. Unlike the WHERE clause, the HAVING clause can use aggregate functions, for example, HAVING AVG(Salary) > 20000.
• ORDER BY field_list {DESC}: The ORDER BY clause allows you to arrange the resulting columns of SELECT in a certain order, defined by field_list. The field_list can either be column names or numbers indicating their position in the SELECT statement. The order is ascending unless DESC is specified.

Some SQL SELECT examples are shown in Table 19.1.

Table 19.1. SQL SELECT Examples

SELECT * FROM Employee
ORDER BY Salary, EmpName

SELECT EmpName, Salary
FROM Employee
WHERE Salary > 20000
ORDER BY 2

SELECT Dept, SUM(salary)
FROM Employee
GROUP BY Dept
HAVING COUNT(*) > 10

SELECT Employee.EmpName,
    Department.DeptName
FROM   Employee, Department
WHERE Employee.Salary
     >= 20000
   AND Employee.Dept =
     Department.Dept

Expressions

In place of individual column names, you can also use numerical expressions involving multiple columns combined with the following arithmetic operators: +, -, /, *. Expressions can be used in the select-list, as in the following example:

SELECT PartNum, PartCost + ExtraCost FROM Parts

The WHERE clause can also include expressions, as in the following example:

SELECT PartNum FROM Parts WHERE (ExtraCost / PartCost) > .25

The LIKE Predicate

In addition to the comparison operators, SQL offers a special comparison operator for character strings. The LIKE predicate allows you to select rows based on a string that matches a certain pattern. In the matching pattern, you can include any normal characters, as well as the special characters % and _. You can attempt to match any string of characters of any length with % or any single character with _. For example, to select any rows that contain Database in the Title field, you can use a query like the following:

SELECT ChapterNum FROM Chapters WHERE Title LIKE '%Database%'

You could also search for titles that have ata starting at the second character position with the following statement:

SELECT ChapterNum FROM Chapters WHERE Title LIKE '_ata%'

The IN Predicate

You can also use the IN predicate to simplify some of your WHERE clauses that are used to select rows with a value that belongs to a certain set of values. For example, look at the following query:

SELECT EmpNum FROM Employee
    WHERE Dept = 'MIS'OR Dept = 'HR'OR Dept = 'Sales'

This could be simplified by using the IN predicate, as in the following query:

SELECT EmpNum FROM Employee WHERE Dept IN ('MIS', 'HR', 'Sales')

You can negate the IN predicate, as in the following example, which selects all employees who aren't in the listed departments:

SELECT EmpNum FROM Employee WHERE Dept IN ('MIS', 'HR', 'Sales')

The BETWEEN Predicate

In many cases, you will need to select rows based on column values that fall into a certain range. For example, you could execute a query like the following:

SELECT EmpNum FROM Employee WHERE Salary > 20000 AND Salary < 30000

This query could be simplified by using the BETWEEN predicate, as in the following ex ample:

SELECT EmpNum FROM Employee WHERE Salary BETWEEN 20000 AND 30000

Like the other comparison operators, you can apply BETWEEN to non-numeric columns, as in the following example:

SELECT EmpNum FROM Employee WHERE Name BETWEEN 'Andersen'AND 'Baker'

You can also negate the BETWEEN predicate by adding the NOT modifier to select rows that don't fall into the given range.

Aggregate Functions

In many cases, you will want to compute values based on all the rows returned in the result set, such as the total for a column in all the returned rows. These sorts of computations can be done with the aggregate functions shown here:

• AVG: Mean average of the column values
• COUNT: Number of rows returned
• MAX: Maximum value for the column in the result set
• MIN: Minimum value for the column in the result set
• SUM: Total of all values for this column in the result set

Perhaps the most common use of aggregation is used in retrieving the number of rows returned by a given query, as in the following example, which returns the number of rows in the Employee table:

SELECT COUNT(*) FROM Employee

If you use COUNT(*), all the rows in the query will be counted. However, if you specify a column name, only rows for which the column value is non-null will be counted. For example, if the Dept column was NULL in one or more rows, the following query would return only the number of rows containing a value for Dept:

SELECT COUNT(Dept) FROM Employee

In many cases, you will want to use aggregate functions on certain groups of rows in the result set. This can easily be done by adding a GROUP BY clause, which can specify the column used to group the rows that are included in aggregate computations. The following example will generate a list of department codes, followed by the total of the salaries for each department:

SELECT Dept, SUM(Salary) FROM Employee GROUP BY Dept

Earlier, you saw how the WHERE clause is used to filter the rows that are returned in a query. In a similar fashion, you can restrict the rows returned using aggregation by adding a HAVING clause, as in the following example, which will return only salary totals for departments that have a total salary of less than $200,000:

SELECT Dept, SUM(Salary) FROM Employee
    GROUP BY Dept
    HAVING SUM(Salary) < 200000

In more complicated queries, it can be a bit confusing to see how various WHERE and HAVING clauses work with each other to produce the end result. When working with aggregation, keep in mind that processing is done in the following order:

1. All rows that meet the WHERE clause are selected.
2. Aggregate values are computed.
3. The rows resulting from aggregation are filtered by the HAVING clause.

ODBC SQL Literal Values

In some of the previous examples, you have seen the use of simple literal, or constant, values in the SQL statements. For numeric values, whether integral or floating-point, you need only use the decimal representation. For strings, you simply enclose the string in single quotes. You can also use NULL as a literal when setting a column to a NULL value.

SQL Functions

Databases provide a myriad of functions that allow developers to easily process data. Although each database is different, there are some database functions that seem common to most databases. Table 19.2 defines popular string functions, Table 19.3 defines some popular numeric functions, and Table 19.4 defines some popular date and time functions.

Table 19.2. Popular String SQL Functions

Table 19.3. Popular Numeric SQL Functions

Table 19.4. Popular Date and Time SQL Functions

Joins

One of the more important operations performed with relational databases is the join, which returns rows of data that are gathered from two or more different tables that are joined in the processing of the query. Joins are even more important if you are working with databases that are thoroughly normalized.

To perform a join, select columns from more than one table. For example, assume that you have a Parts table that lists information about parts, including a description, and a separate Prices table that lists pricing information for the parts. The following query would perform a join on these two tables:

SELECT Description, Price FROM Parts, Prices

However, you might be surprised at the results. The preceding example will return the Cartesian product of the two tables, which is a fancy name for all the possible combinations of the rows in the Parts table and the rows in the Prices table. In most cases, this isn't a useful result.

To narrow the set of returned rows to something more useful, you will need to add a WHERE clause. One of the most common cases is when you have a common identifier in both tables. For example, if both the Prices and Parts tables included a PartNum column, you could generate rows that match a part's description to its price with the following query:

SELECT Description, Price FROM Parts, Prices
    WHERE Parts.PartNum = Prices.PartNum

SELECT Description, Price
FROM Parts INNER JOIN Prices ON Parts.PartNum = Prices.PartNum

Correlation Names

To help simplify some of your queries, SQL allows you to use table aliases (also known as correlation names), which can reduce the verbosity of your queries and are necessary for some more complicated queries that you will see later. For example, if you had the following query:

SELECT MyFirstTable.Name, MyFirstTable.Num, MySecondTable.Date,
    MySecondTable.Time FROM MyFirstTable, MySecondTable
    WHERE MyFirstTable.Id = MySecondTable.Id

it could be simplified by using the table aliases f, for MyFirstTable, and s, for MySecondTable, as in the following:

SELECT f.Name, f.Num, s.Date, s.Time
    FROM MyFirstTable f, MySecondTable s,
    WHERE f.Id = s.Id

In this case, the use of table aliases doesn't affect the meaning of the query in any way; it merely simplifies the notation. However, in more complicated queries, you might find yourself selecting from the same table more than once, as when using subqueries. In these cases, table aliases are essential for specifying exactly which table you are using.

Outer Joins

The syntax for joins you have seen so far performs an inner join, which might not generate the full Cartesian product of the joined tables. For example, if you created a join with the following query:

SELECT * FROM Employee, Department
    WHERE Employee.DeptNum = Department.DeptNum

the database would process this statement by collecting all the rows present in the Employee table that match those with the rows in the Department table. This result doesn't include results for which no corresponding entry exists in the Employee table. It also won't return any rows for which a matching entry isn't found in the Department table. To create a query that will return rows that don't include a matching row in one of the tables, you will need to use an outer join.

For example, suppose you wanted to select rows for each employee, including those who have not been assigned to a department yet. You can do so by using an outer join like the following:

SELECT * FROM
    Employee LEFT OUTER JOIN Department ON
    Employee.DeptNum = Department.DeptNum

In the preceding example, you used a LEFT OUTER JOIN, which ensures that all rows of the left table (Employee) will be represented in the result set. You could also use a RIGHT OUTER JOIN to ensure that all rows in the right table are represented, or a FULL OUTER JOIN, which ensures that all rows from both tables are represented, whether or not a matching row appears in the other table.

SELECT * FROM
    Employee,
    Department
WHERE Employee.DeptNum = Department.DeptNum (+)

SELECT * FROM
    Employee,
    Department
WHERE Employee.DeptNum =* Department.DeptNum

Subqueries

In the WHERE clause examples that you have seen so far, you have done comparisons on literal values or comparing two columns, or Boolean combinations of these. However, it is also possible to retrieve information used in a WHERE clause by executing another query, known as a subquery.

The first case in which a subquery could be used is with an EXISTS (or NOT EXISTS) predicate. Suppose you wanted to select a list of departments that had no employees assigned to them. You could do so with the following query:

SELECT DeptName FROM Department
    WHERE NOT EXISTS (SELECT * FROM Employee)

This will return the name of all departments for which the department number isn't found in any of the records in the Employee table.

You could also select a list of departments that have at least one particularly well-paid employee with a query like the following:

SELECT DeptName FROM Department d
    WHERE EXISTS
        (SELECT * FROM Employee e
        WHERE e.Salary > 100000 AND d.DeptNum = e.DeptNum)

The second case in which subqueries are useful is when using the IN or NOT IN predicates. For example, you could return a list of all the departments that currently have personnel assigned to them by using a query like the following:

SELECT DeptName FROM Department
    WHERE DeptNum IN (SELECT DeptNum FROM Employee)

The third case in which subqueries are often used involve comparisons that use the ANY or ALL keywords. For example, if you kept separate tables for employees and executives, and wanted to select a list of executives who were paid more than all the regular employees (taken individually, not totaled), you could use the following query:

SELECT Ex.Name FROM Executives Ex
    WHERE Ex.Salary > ALL
        (SELECT Emp.Salary FROM Employee Emp)

Similarly, you could use the ANY modifier to select a list of executives for which at least one regular employee was better paid with the following query:

SELECT Ex.Name FROM Executives Ex
    WHERE Ex.Salary < ANY
        (SELECT Emp.Salary FROM Employee Emp)
							

Union Queries

SQL allows you to generate a single result set from two independent queries by combin ing them with the UNION keyword. By default, duplicate rows are removed from the result set, although you can disable this by using UNION ALL instead of UNION. Also, you may choose to sort the end result by specifying an ORDER BY clause, which must come after the last query in the union.

For example, you could generate a list of employees in the MIS and SALES departments with the following query:

SELECT * FROM Employee WHERE Dept = 'MIS'
UNION
SELECT * FROM Employee WHERE Dept = 'SALES'

Of course, there are several other ways that you could more efficiently generate the same results as this query, but this shows a simple example of how UNION works.

INSERT Statements

INSERT puts additional rows into a table. Its format is the following:

INSERT INTO table_name {(column_list)}
| VALUES (values_list)     | SELECT command       |

The parts of the INSERT command are as follows:

• table_name: This is where you are going to insert new rows. It can be as fully qualified as your database allows.
• column_list: This is a list of columns that you are going to fill with your INSERT. If omitted, the INSERT command assumes all fields will be entered in the order they appear on your table.

• values_list: This is a list of values (either constants or host variables) that you want to insert.
• SELECT_command: This is a SELECT command that returns values in the same order as the column_list.

Some examples of the INSERT command are shown in Table 19.5.

Table 19.5. SQL INSERT Examples

INSERT INTO Employee
(EmpName, Salary)
VALUES ('Joe Schmoe',
50000);

INSERT INTO Employee
VALUES (123, 'Joe Schmoe',
     50000, 'MIS')

INSERT INTO FormerEmployee
     (EmpId, EmpName, Reason)
  SELECT EmpId, EmpName,
           'Outsourced'
  FROM Employees
  WHERE Dept = 'MIS'

DELETE Statements

The DELETE command is used to delete existing rows from a table. Its format is as follows:

DELETE FROM table_name
{WHERE search_condition}

The table_name is the name of the table in which you can delete rows. The search_ condition in the WHERE clause is the same as in the SELECT statement.

Some examples of the DELETE statement are shown in Table 19.6.

Table 19.6. SQL DELETE Examples

UPDATE Statements

The UPDATE command is used to update existing rows on a table with new data. Its format is as follows:

UPDATE table_name
SET   column_name1 = expression1,
column_name2 = expression2,...
WHERE condition;

The table_name is the name of the table you want to update. The condition in the WHERE clause is the same as in the SELECT statement. The column_name is a name of a column, and expression is the constant or host variable whose value will be placed inside the column.

Some examples of the UPDATE statement are shown in Table 19.7.

Table 19.7. SQL UPDATE Examples

UPDATE Employee
  SET Salary = Salary + 100

UPDATE Employee
  SET Salary = Salary + 100,
    dept = NULL
  WHERE dept = 'Payroll'

Share This