| When you learn the grammar of a new language, it's helpful to categorize the vocabulary of the language by usage and then into the familiar parts of speech. SQL commands, therefore, first are divided into six usage categories: Data Query Language (DQL) consists of commands that obtain data from tables and determines how the results of the retrieval are presented. The SELECT command is the principal instruction in this category. Data Manipulation Language (DML) provides INSERT and DELETE commands, which add or delete entire rows, and the UPDATE command, which changes the values of data in specified columns within rows. Transaction Processing Language (TPL) includes BEGIN TRAN[SACTION], COMMIT [TRAN[SACTION]|WORK], and ROLLBACK [TRAN[SACTION]|WORK], which group multiple DML operations. If one DML operation of a transaction fails, the preceding DML operations are canceled (rolled back). Jet 4.0 SQL and T-SQL implement BEGIN TRANSACTION, COMMIT TRANSACTION|WORK, and ROLLBACK TRANSACTION|WORK; only T-SQL supports the TRAN abbreviation. Data Definition Language (DDL) includes CREATE|ALTER TABLE, ADD|ALTER COLUMN, and CREATE VIEW instructions that define the structure of tables and views. DDL commands also are used to modify tables and to create and delete indexes. The keywords that implement declarative referential integrity (DRI) are used with DDL statements. Jet SQL and T-SQL support the [CREATE|ALTER] TABLE and [CREATE|ALTER} INDEX instructions; T-SQL offers non-ANSI ALTER VIEW, [CREATE|ALTER] FUNCTION, and [CREATE|ALTER] PROCEDURE statements. Cursor Control Language (CCL) can select a single row of a query result set for processing. Cursor control constructs, such as UPDATE WHERE CURRENT, are handled by ADO's cursor engine or the Jet database engine, so these commands aren't discussed in this chapter. Data Control Language (DCL) performs administrative functions that grant and revoke privileges to use the database, such as GRANT and REVOKE, a set of tables within the database, or specific SQL commands. DCL sometimes is called Data Security Language.
Keywords that make up the vocabulary of SQL are identified further in the following categories: Commands, such as SELECT, EXECUTE, CREATE, and ALTER, are verbs that cause an action to be performed. Qualifiers, such as WHERE, limit the range of values of the entities that constitute the query. Clauses, such as ORDER BY, modify the action of an instruction. Predicates, such as IN, ALL, ANY, SOME, LIKE, and UNIQUE, are expressions that test facts about data values. Predicates can return a TRUE, FALSE, or, in some cases, NULL (unknown) result. These three values are SQL keywords. Operators, such as =, <, or >, compare values and specify joins with a WHERE clause or JOIN syntax. Jet SQL and T-SQL use JOIN syntax by default. Operators also are called comparison predicates. Group aggregate functions, such as COUNT(), MAX(), and MIN(), return a single result for a set of values. Data type conversions functions change values from one data type to another. CAST() and CONVERT() are the most commonly-used conversion functions. Utility functions return values determined by expressions. You can use NULLIF(), for example, to return a NULL value if the function's expression evaluates to TRUE. Date/time and string manipulation functions also fit into the utility category.  For more information on the relationship between VBA and T-SQL utility functions, see "VBA Functions That Upsize to SQL Server Functions," p. 917, and "VBA Functions That You Must Manually Convert to Related SQL Server Functions," p. 917.
Other keywords (or reserved words) modify the action of a clause or manipulate cursors that are used to select specific rows of queries. The T-SQL FOR XML [AUTO|RAW|EXPLICIT] modifier, for example, returns an XML document or subdocument instead of a conventional Recordset from a SELECT query. FOR XML isn't included in ANSI SQL.
Note SQL keywords usually are capitalized, but the keywords aren't case sensitive. The uppercase convention is used in this book, and SQL keywords are set in monospace type. You use parameters, such as column_list, to define or modify the action specified by keywords. Names of replaceable parameters are printed in lowercase italicized monospace type. ANSI SQL defines reserved words, such as SELECT, and nonreserved words, such as DATA and FORTRAN. Keywords include both reserved and nonreserved words. All the keywords used in this chapter are reserved words; you can't use a reserved word as the name of an object, such as a table.
|
