Table of Contents |
2.2 Categories of SyntaxTo begin to use SQL, you must understand how statements are written. SQL syntax falls into four main categories. Each category is introduced in the following list and then explained in further detail in the sections that follow:
2.2.1 IdentifiersKeep in mind that RDBMSs are built upon set theory. In ANSI terms, clusters contain sets of catalogs, catalogs contain sets of schemas, schemas contain sets of objects, and so on. Most database platforms use corollary terms: instances contain one or more databases; databases contain one or more schemas; schemas contain one or more tables, views, or stored procedures, and the privileges associated with each object. At each level of this structure, items require a unique name (that is, an identifier) so that they can be referenced by programs and system processes.This means that each object (whether a database, table, view, column, index, key, trigger, stored procedure, or constraint) in a RDBMS must be identified. When issuing the command that creates a database object, you must specify an identifier (i.e., a name) for that new object. There are two important categories of rules that experienced programmers keep in mind when choosing an identifier for a given item:
2.2.1.1 Naming conventionsNaming conventions establish a standard baseline for choosing object identifiers. In this section, we show a list of naming conventions (rules for picking your identifiers) that are based on long years of experience. The SQL standard has no comment on naming conventions outside of the uniqueness of an identifier, its length, and the characters that are valid within the identifier:
There are several benefits to following these naming conventions. First, your SQL code becomes, in a sense, self-documenting because the chosen names are meaningful and understandable to other users. Second, your SQL code and database objects are easier to maintain-especially for other users who come after you-because your objects are consistently named. Finally, maintaining consistency increases database functionality. If the database ever has to be transferred or migrated to another RDMS, consistent and descriptive naming saves both time and energy. Giving a few minutes of thought to naming SQL objects in the beginning can prevent problems later on. 2.2.1.2 Identifier rulesIdentifier rules are rules for identifying objects, enforced by the database platforms, within the database. These rules apply to normal identifiers, not quoted identifiers. Rules specified by the SQL2003 standard generally differ somewhat from those of a specific database vendor. Table 2-1 contrasts the SQL2003 rules with those of the five RDMS platforms covered in this book.
Identifiers must be unique within their scope. Thus, in our earlier discussion of the hierarchy of database objects, database names must be unique on a given instance of a database server, while the names of tables, views, functions, triggers, and stored procedures must be unique within a given schema. On the other hand, you can have a table and a stored procedure with the same name since they are different object types. The names of columns, keys, and indexes must be unique on a single table or view, and so forth. Check with the database platform documentation for more information - some platforms require unique identifiers where others may not. For example, DB2 requires all index identifiers to be unique throughout the database, while SQL Server requires that the index identifier be unique only for the table it depends on. Remember, quoted identifiers (object names encapsulated within a special delimiter, usually double quotes) may be used to break some of the identifier rules specified earlier. Specifically, quoted identifiers may be used to bestow a reserved word as a name, or to allow normally unusable characters and symbols within a name. For example, you normally can't use the percent sign (%) in a table name. However, you can, if you must, use the percent sign in a table name so long as you always enclose that table name within double quotes. To name a table expense%%ratios, you would specify the name in quotes as "expense%%ratios". Again, remember that in SQL2003, such names are sometimes known as delimited identifiers.
2.2.2 LiteralsSQL evaluates literal values as any explicit numeric, character string, temporal value (like a date or time), or Boolean value that is not an identifier or a keyword. SQL databases allow a variety of literal values in a SQL program. Literal values are allowed for most of the numeric, character, Boolean, and date datatypes. For example, SQL Server numeric datatypes include (among others) INTEGER, REAL, and MONEY. Thus, numeric literals can look like: 30 -117 +883.3338 -6.66 $70000 2E5 7E-3 As the example illustrates, SQL Server allows signed or unsigned numerals, in scientific or normal notation. And since SQL Server has a money datatype, even a dollar sign can be included. SQL Server does not allow other symbols in numeric literals (besides 0 1 2 3 4 5 6 7 8 9 + - $ . E e), so do not include commas (or periods in Europe). Most databases interpret a comma in a numeric literal as a list item separator. Thus, the literal value 3,000 would be interpreted by the database as 3 and, separately, 000. Boolean, character string, and date literals look like: TRUE 'Hello world!' 'OCT-28-1966 22:14:30:00' Character string literals should always be enclosed by single quotation marks (' '), the standard delimiter for all character string literals. Character string literals are not restricted just to the alphabet. In fact, any character in the character set can be represented as a string literal. All of the following are string literals: '1998' '70,000 + 14000' 'There once was a man from Nantucket,' 'Oct 28, 1966' All of these examples are, in fact, compatible with the CHARACTER datatype. Remember not to confuse the string literal `1998' with the numeric literal 1998. Once string literals are associated with CHARACTER datatypes, it is poor practice to use them in arithmetic operations without explicitly converting them to a numeric datatype. Some database products will perform automatic conversion of string literals containing numbers when compared against any DATE or NUMBER datatype values. By doubling the delimiter, you can effectively represent a single quotation mark in a literal string, if necessary. That is, use two quotation marks each time a single quotation mark is part of the value. This example taken from SQL Server illustrates the idea: SELECT 'So he said ''Who''s Le Petomaine?''' This gives the result: ----------------- So he said 'Who's Le Petomaine?' 2.2.3 OperatorsAn operator is a symbol specifying an action that is performed on one or more expressions. Operators are used most often in DELETE, INSERT, SELECT, or UPDATE statements, but also are used frequently in the creation of database objects, such as stored procedures, functions, triggers, and views. Operators typically fall into these categories:
2.2.3.1 Arithmetic operatorsArithmetic operators perform mathematical operations on two expressions of any datatypes in the numeric datatype category. See Table 2-2 for a listing of the arithmetic operators.
2.2.3.2 Assignment operatorsExcept in Oracle, which uses :=, the assignment operator (=) assigns a value to a variable or the alias of a column heading. In SQL Server, the keyword AS may serve as an operator for assigning table- or column-heading aliases. 2.2.3.3 Bitwise operatorsMicrosoft SQL Server provides bitwise operators as a shortcut to perform bit manipulations between two-integer expressions (see Table 2-3). Valid datatypes that are accessible to bitwise operators include binary, bit, int, smallint, tinyint, and varbinary.
2.2.3.4 Comparison operatorsComparison operators test whether two expressions are equal or unequal. The result of a comparison operation is a Boolean value: TRUE, FALSE, or UNKNOWN. Also, note that the ANSI standard behavior for a comparison operation where one or more of the expressions are NULL is NULL. For example, the expression 23 + NULL returns NULL, as does the expression Feb 23, 2002 + NULL. See Table 2-4 for a list of the comparison operators.
Boolean comparison operators are used most frequently in a WHERE clause to filter the rows that qualify for the search conditions. The following Microsoft SQL Server example uses the greater than or equal to comparison operation: SELECT * FROM Products WHERE ProductID >= @MyProduct 2.2.3.5 Logical operatorsLogical operators are commonly used in a WHERE clause to test for the truth of some condition. Logical operators return a Boolean value of either TRUE or FALSE. Logical operators also are discussed in SELECT Statement. Not all database systems support all operators. See Table 2-5 for a list of logical operators.
2.2.3.6 Unary operatorsUnary operators perform an operation on only one expression of any of the datatypes of the numeric datatype category. Unary operators may be used on integer datatypes, though positive and negative may be used on any numeric datatype (see Table 2-6).
2.2.3.7 Operator precedenceSometimes operator expressions become rather complex. When an expression has multiple operators, operator precedence determines the sequence in which the operations are performed. The order of execution can significantly affect the resulting value. Operators have the precedence levels listed below. An operator on a higher level is evaluated before an operator on a lower level. The following listing denotes operators from highest to lowest precedence:
Operators are evaluated from left to right when they are of equal precedence. However, parentheses are used to override the default precedence of the operators in an expression. Expressions within a parentheses are evaluated first, while operations outside the parentheses are evaluated next. For example, the following expressions in an Oracle query return very different results: SELECT 2 * 4 + 5 FROM dual -- Evaluates to 8 + 5 which yields an expression result of 13. SELECT 2 * (4 + 5) FROM dual -- Evaluates to 2 * 9 which yields an expression result of 18. In expressions with nested parentheses, the most deeply nested expression is evaluated first. This next example contains nested parentheses, with the expression 5 - 3 in the most deeply nested set of parentheses. This expression yields a value of 2. Then the addition operator (+) adds this result to 4, which yields a value of 6. Finally, the 6 is multiplied by 2 to yield an expression result of 12: SELECT 2 * (4 + (5 - 3) ) FROM dual -- Evaluates to 2 * (4 + 2) which further evaluates to 2 * 6, --and yields an expression result of 12. RETURN 2.2.3.8 System delimiters and operatorsString delimiters mark the boundaries of a string of alphanumeric characters. System delimiters are those symbols within the character set that have special significance to your database server. Delimiters are symbols that are used to judge the order or hierarchy of processes and list items. Operators are those delimiters used to judge values in comparison operations, including symbols commonly used for arithmetic or mathematical operations. Table 2-7 lists the system delimiters and operators allowed by SQL.
2.2.4 Keywords and Reserved WordsJust as certain symbols have special meaning and functionality within SQL, certain words and phrases have special significance. SQL keywords are words whose meanings are so closely tied to the operation of the RDBMS that they should not be used for any other purpose; generally, they are words used in a SQL statement. (Note that they can be used as an identifier on most platforms, but they shouldn't be.) For example, the word "SELECT" is a reserved word and should not be used as a table name.
Reserved words, on the other hand, do not have special significance now, but they probably will in a future release. To emphasize the fact that keywords should not be used as an identifier, but nevertheless could be, the SQL standard calls them "nonreserved keywords." Reserved words and keywords are not always words used in SQL statements, but may be words commonly associated with database technology. For example, CASCADE is used to describe data manipulations that allow their action, such as a delete or update, to "flow down," or cascade, to any subordinant tables. Reserved words and keywords are widely published so that programmers will not use them as identifiers that will, at some later revision, cause a problem. SQL2003 specifies its own list of reserved words and keywords. In addition, the database platform specify their own list of reserved words and keywords because they each have their own extensions to the SQL command set. SQL standard keywords, as well as the keywords in the different vendor implementations, are shown in Appendix B. |
Table of Contents |