,ch03.13605 Page 132 Wednesday, November 29, 2000 4:42 PM SAVEPOINT after_update; DELETE sales; ROLLBACK TO after_insert; ROLLBACK; SELECT The SELECT statement retrieves rows, columns, and derived values from one or many tables of a database. Vendor SQL Server MySQL Oracle PostgreSQL Command Supported, with variations (ANSI joins supported) Supported, with variations (ANSI joins partially supported) Supported, with variations (ANSI joins not supported) Supported, with variations (ANSI joins partially supported) SQL99 Syntax and Description The full syntax of the SELECT statement is powerful and complex, but can be broken down into these main clauses: SELECT [ALL | DISTINCT] select_list FROM table_name1 [,…, table_nameN] [JOIN join_condition] [WHERE search_condition] [GROUP BY group_by_expression] [HAVING search_condition] [ORDER BY order_expression [ASC | DESC] ] Each clause of the SELECT statement has a specific use. Thus, it is possible to speak individually of the FROM clause, the WHERE clause, or the GROUP BY clause. However, not every query needs every clause. At a minimum, a query needs a SELECT item list and a FROM clause. (Microsoft SQL Server and PostgreSQL both support certain types of queries that do not need a FROM clause. Refer to the examples below for more information.) The SELECT item list The SELECT item list basically includes all items of information a user wants to retrieve from the server. Different types of elements can appear in the select item list. It s possible to retrieve literal strings, aggregate functions, and mathematical calculations. In Microsoft SQL Server, the SELECT item list may contain a subquery. 132 Chapter 3 SQL Statements Command Reference
Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP MySQL Web Hosting services
,ch03.13605 Page 130 Wednesday, November 29, 2000 4:42 PM Oracle Syntax and Variations ROLLBACK [WORK] [TO savepoint_name] [FORCE text]; ROLLBACK clears all data modifications made to the current open transaction or to a specific, existing savepoint. Oracle s implementation closely follows the SQL standard with the exception of the FORCE option. ROLLBACK FORCE rolls back to an in-doubt distributed transaction. These transactions are described in the Oracle system view, DBA_2PC_PENDING. PostgreSQL Syntax and Variations {ROLLBACK | ABORT} [WORK | TRANSACTION]; ROLLBACK clears all data modifications made to the current open transaction or to a specific, existing savepoint. PostgreSQL supports both the SQL99 WORK option and the TRANSACTION option. It does not support rolling back to a savepoint. The ABORT option may be used as a full synonym of ROLLBACK. Example Here is a Transact-SQL batch using COMMIT and ROLLBACK in Microsoft SQL Server. It inserts a record into the sales table. If it fails, the transaction is rolled back; if the statement succeeds, the transaction is committed: BEGIN TRAN - initializes a transaction — the transaction itself INSERT INTO sales VALUES(’7896′,’JR3435′,’Oct 28 1997′,25,’Net 60′,’BU7832′) — some error-handling in the event of a failure IF @@ERROR <> 0 BEGIN — raises an error in the event log and skips to the end RAISERROR 50000 ‘Insert of sales record failed’ ROLLBACK WORK GOTO end_of_batch END — the transaction is committed if no errors are detected COMMIT TRAN — the GOTO label that enables the batch to skip to the end without — committing end_of_batch: GO SAVEPOINT This command creates a savepoint in the current transaction. Transactions can be divided into logical breakpoints using the SAVEPOINT command. Multiple save- points may be specified within a single transaction. The main benefit of the 130 Chapter 3 SQL Statements Command Reference
Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP MySQL Web Hosting services
,ch03.13605 Page 129 Wednesday, November 29, 2000 4:42 PM Access to tables, views, and sequences can be revoked in PostgreSQL. It is otherwise identical to the SQL99 command. Refer to the SQL99 REVOKE syntax discussion, as well as the SQL99 GRANT discussion. ROLLBACK The ROLLBACK statement undoes a transaction to its beginning or a previously declared SAVEPOINT. It closes open cursors and releases locks in the same way as COMMIT. Vendor SQL Server Supported, with variations MySQL Not supported Oracle Supported PostgreSQL Supported Command SQL99 Syntax and Description ROLLBACK [WORK] [TO SAVEPOINT savepoint_name] In addition to finalizing a single or group of data-manipulation operations, the ROLLBACK statement undoes transactions up to the last issued BEGIN or SAVEPOINT statement. SQL99 offers the new, optional keywords AND CHAIN. None of the four vendors yet support this command. This new syntax is: ROLLBACK [WORK] [AND [NO] CHAIN] The AND CHAIN option tells the DBMS to end the current transaction, but to share the common transaction environment (such as transaction isolation level) with the next transaction. The AND NO CHAIN option simply ends the single transaction. The ROLLBACK command is functionally equivalent to the command, ROLLBACK WORK AND NO CHAIN. Microsoft SQL Server Syntax and Variations ROLLBACK [TRAN[SACTION] [transaction_name | @tran_name_variable | savepoint_name | @savepoint_variable] ] ROLLBACK clears all data modifications made to the current open transaction or to a specific, existing savepoint. If ROLLBACK is issued alone, it rolls back the current open transaction. ROLLBACK normally frees locks, but it does not free locks when rolling back to a savepoint. ROLLBACK behaves similarly to COMMIT with regards to nested triggers, decrementing the @@TRANCOUNT system variable by one. ROLLBACK TRANSACTION, when issued in a trigger, undoes all data modifications, including those performed by the trigger, up to the point of the ROLLBACK statement. Nested triggers are not executed if they follow a ROLLBACK within a trigger; however, any statements within the trigger that follow the rollback are not impacted by the rollback. Statements ROLLBACK 129
Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP MySQL Web Hosting services
,ch03.13605 Page 127 Wednesday, November 29, 2000 4:42 PM Example REVOKE CREATE DATABASE, CREATE TABLE FROM emily, sarah GO REVOKE GRANT OPTION FOR SELECT, INSERT, UPDATE, DELETE ON titles TO editors GO MySQL Syntax and Variations REVOKE { ALL PRIVILEGES | SELECT | INSERT [ (column_name [,…n]) ] | UPDATE [ (column_name [,…n]) ] | REFERENCES [ (column_name [,…n]) ] | DELETE | USAGE | ALTER | CREATE | DROP | FILE | INDEX | PROCESS | RELOAD | SHUTDOWN } [,…n] ON {table_name | * | *.* | database_name.*} FROM user_name [,…n] The REVOKE statement rolls back any permissions previously granted to one or more users. Permissions may be revoked globally, as described in the GRANT statement. Furthermore, MySQL s implementation of REVOKE does not explicitly roll back permissions on objects that are dropped. Thus, it is necessary to explicitly REVOKE permissions on a table, even if the table is dropped. MySQL otherwise conforms to the SQL99 standard for the REVOKE command. Example The first command revokes all privileges on the sales table for Emily and Dylan, while the second command revokes all privileges for the user Kelly in the current database: REVOKE ALL PRIVILEGES ON sales FROM emily, dylan; REVOKE * employee FROM kelly; Oracle Syntax and Variations REVOKE {ALL [PRIVILEGES] | [object_privilege] } ON { [schema_name.][object] | [DIRECTORY directory_object_name] } FROM {grantee_name | role | PUBLIC} [,…n] [CASCADE [CONSTRAINTS] ] [FORCE]; REVOKE {system_privilege | role} FROM {grantee_name | role | PUBLIC} [,…n]; Statements REVOKE 127
Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP MySQL Web Hosting services
,ch03.13605 Page 128 Wednesday, November 29, 2000 4:42 PM The REVOKE command not only can revoke object and system privileges, it also can revoke a role from a given user or other role. Refer to the GRANT statement for more information on the specific object and system privileges supported by the REVOKE command. The two forms of the REVOKE command, REVOKE object_privilege and REVOKE system_privilege, are mutually exclusive. Do not attempt to do both operations in a single statement. When a user s privileges are revoked, the privileges of all users who received their privileges from the revoked user also are revoked. Users who are granted GRANT ANY ROLE system privilege also can revoke any role. The REVOKE command can only revoke privileges specifically granted with the GRANT command, not privileges available through roles or the operating system. The ON DIRECTORY clause identifies a directory object where permissions are revoked. The CASCADE CONSTRAINTS clause drops any referential integrity constraints users create if their REFERENCES privilege is revoked. The FORCE clause revokes EXECUTE permissions on dependent user-defined table and type objects. Consequently, those objects are marked as invalid and unusable until they are recompiled. Examples To revoke a user from a role: REVOKE read-only FROM sarah; To revoke a system-command privilege: REVOKE CREATE ANY SEQUENCE, CREATE ANY DIRECTORY FROM read_only; To revoke a REFERENCES privilege: REVOKE REFERENCES ON pubs_new_york.emp FROM dylan CASCADE CONSTRAINTS; PostgreSQL Syntax and Variations REVOKE { ALL | SELECT | INSERT | DELETE | UPDATE | RULE | REFERENCES | USAGE} [,…n] ON {object_name} TO {grantee_name | PUBLIC | GROUP group_name} {CASCADE | RESTRICT} 128 Chapter 3 SQL Statements Command Reference
Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP MySQL Web Hosting services
,ch03.13605 Page 126 Wednesday, November 29, 2000 4:42 PM ON { [TABLE] table_name | DOMAIN domain_name | COLLATION collation_name | CHARACTER SET character_set_name | TRANSLATION translation_name } FROM {grantee_name | PUBLIC} [,…n] {CASCADE | RESTRICT} A specific privilege on a specific database object can be revoked for a single user using REVOKE privilege_name ON object_name FROM grantee_name. A specific privilege on a specific object may be revoked from all users via the PUBLIC clause. As an alternative, the WITH GRANT OPTION can be used to revoke permissions using the REVOKE GRANT OPTION FOR clause. The RESTRICT option revokes only the specified privilege. The CASCADE option revokes the specified privilege and any privileges that are dependent upon the granted privilege. A cascading revocation may exhibit different behavior on different database platforms, so be sure to read the vendor documentation for the correct implementation of this option. Microsoft SQL Server Syntax and Variations REVOKE [GRANT OPTION FOR] {ALL [ PRIVILEGES ] | SELECT | INSERT | DELETE | UPDATE | REFERENCES | EXECUTE | CREATE {DATABASE | DEFAULT | FUNCTION | PROCEDURE | RULE | TABLE | VIEW} | BACKUP {DATABASE | LOG} } [,…n] ON { {table_name | view_name} [(column [,…n])] | stored_procedure_name | extended_stored_procedure_name | user_defined_function_name | [(column [,…n] ON {table_name | view_name} } {TO | FROM} {grantee_name} [,…n] [CASCADE] [AS {group_name | role_name} ] This command is essentially SQL99 compatible, with the exception of the augmentations introduced in the GRANT command. If commands were granted to a user WITH GRANT OPTION enabled, the privilege should be revoked using both WITH GRANT OPTION and CASCADE. REVOKE can only be used in the current database. REVOKE also is used to disable any DENY settings. Microsoft SQL Server additionally supports the DENY statement. DENY is syntactically similar to REVOKE. However, it is conceptually different in that REVOKE neutralizes a user s privileges while DENY explicitly prohibits a user s privileges. Use the DENY statement to keep a user or role from accessing a privilege. 126 Chapter 3 SQL Statements Command Reference
Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP Web Hosting services
,ch03.13605 Page 125 Wednesday, November 29, 2000 4:42 PM Examples This example creates a function. The function returns the value that is stored in the proj_rev variable to the calling session: CREATE FUNCTION project_revenue (project IN varchar2) RETURN NUMBER AS proj_rev NUMBER(10,2); BEGIN SELECT SUM(DECODE(action,’COMPLETED’,amount,0) SUM(DECODE(action,’STARTED’,amount,0) + SUM(DECODE(action,’PAYMENT’,amount,0) INTO proj_rev FROM construction_actions WHERE project_name = project; RETURN (proj_rev); END; This example creates a function that returns a calculated value to the calling session: CREATE FUNCTION metric_volume — Input dimensions in centimeters. (@length decimal(4,1), @width decimal(4,1), @height decimal(4,1) ) RETURNS decimal(12,3) — Cubic Centimeters. AS BEGIN RETURN ( @length * @width * @height ) END GO Statements REVOKE The REVOKE statement removes permissions for a user, group, or role on a specific database object or system command. Vendor SQL Server Supported, with variations MySQL Supported, with variations Oracle Supported, with variations PostgreSQL Supported, with variations Command SQL99 Syntax and Description REVOKE [GRANT OPTION FOR] { ALL PRIVILEGES } | SELECT | INSERT | DELETE | UPDATE | REFERENCES | USAGE }[,…n] REVOKE 125
Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP Web Hosting services
,ch03.13605 Page 124 Wednesday, November 29, 2000 4:42 PM ALL, ANY, BETWEEN, IN, LIKE, OR, SOME = (variable assignment) 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 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 The RETURN statement terminates processing within a SQL-invoked function (as opposed to a host-invoked function) and returns the function s result value. Vendor SQL Server MySQL Oracle PostgreSQL Command Supported Supported Supported Supported SQL99 Syntax and Description RETURNS return_parameter_value | NULL The RETURN function is used within a function to end its processing. Using the NULL clause terminates the function without returning an actual value. Otherwise, the parameter value specified is returned either as a variable or as a literal expression. Although the RETURN statement is categorized as a separate command within SQL, it is deeply intertwined with the CREATE FUNCTION statement. Check the CREATE FUNCTION statement for a more complete understanding of each vendor s implementation of RETURN. 124 Chapter 3 SQL Statements Command Reference
Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP Web Hosting services
,ch03.13605 Page 123 Wednesday, November 29, 2000 4:42 PM Table 3-7: Logical Operators Logical Operators ALL TRUE if all of a set of comparisons are TRUE AND TRUE if both Boolean expressions are TRUE ANY TRUE if any one of a set of comparisons is TRUE BETWEEN TRUE if the operand is within a range EXISTS TRUE if a subquery contains any rows IN TRUE if the operand is equal to one of a list of expressions LIKE TRUE if the operand matches a pattern NOT Reverses the value of any other Boolean operator OR TRUE if either Boolean expression is TRUE SOME TRUE if some of a set of comparisons are TRUE Meaning Unary Operators Unary 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 3-8). Table 3-8: Unary Operators Statements Unary Operators + - ~ Meaning Numeric value is positive Numeric value is negative A bitwise NOT, returns the complement of the number (not in Oracle) Operator Precedence Sometimes 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 following precedence levels. An operator on higher levels is evaluated before an operator on a lower level: ( ) (parenthetical expressions) +, -, ~ (unary operators) *, /, % (mathematical operators) +, - (arithmetic operators) =, >, <, >=, <=, <>, !=, !>, !< (comparison operators) ^ (Bitwise Exclusive OR), & (Bitwise AND), | (Bitwise OR) NOT AND Operators 123
Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP MySQL Web Hosting services
,ch03.13605 Page 122 Wednesday, November 29, 2000 4:42 PM that are accessible to bitwise operators include binary, bit, int, smallint, tinyint, and varbinary. Table 3-5: Bitwise Operators Bitwise Operators & | ^ Meaning Bitwise AND (two operands) Bitwise OR (two operands) Bitwise exclusive OR (two operands) Comparison Operators Comparison 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 3-6 for a list of the comparison operators. Table 3-6: Comparison Operators Comparison Operators = > < >= <= <> != !< !> Meaning Equal to Greater than Less than Greater than or equal to Less than or equal to Not equal to Not equal to (not ANSI standard) Not less than (not ANSI standard) Not greater than (not ANSI standard) 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 Logical Operators Logical 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 under the SELECT topic. Not all RDBMS support all operators. See Table 3-7 for a list of logical operators. 122 Chapter 3 SQL Statements Command Reference
Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP MySQL Web Hosting services