,ch04.13730 Page 164 Wednesday, November 29, 2000 4:43 PM Types of Functions There are several basic types and categories of functions in SQL99 and vendor implementations of SQL. The basic types of functions are: Aggregate functions Operate against a collection of values, but return a single, summarizing value. Scalar functions Operate against a single value, and return a single value based on the input value. Some scalar functions, CURRENT_TIME for example, do not require any arguments. Aggregate Functions Aggregate functions return a single value based upon a set of other values. If used among many other expressions in the item list of a SELECT statement, the SELECT must have a GROUP BY clause. No GROUP BY clause is required if the aggregate function is the only value retrieved by the SELECT statement. The supported aggregate functions and their syntax are listed in Table 4-1. Table 4-1: SQL99 Aggregate Functions Function AVG(expression) COUNT(expression) COUNT(*) MIN(expression) MAX(expression) SUM(expression) Usage Computes the average value of a column by the expression Counts the rows defined by the expression Counts all rows in the specified table or view Finds the minimum value in a column by the expression Finds the maximum value in a column by the expression Computes the sum of column values by the expression Technically speaking, ANY, EVERY, and SOME are considered aggregate functions. However, they have been discussed as range search criteria since they are most often used that way. Refer to the SELECT … WHERE topic in the previous chapter for more information on these functions. The number of values processed by an aggregate varies depending on the number of rows queried from the table. This behavior makes aggregate functions different from scalar functions, which require a fixed number and fixed type of parameters. The general syntax of an aggregate function is: aggregate_function_name ( [ALL | DISTINCT] expression ) The aggregate function name may be AVG, COUNT, MAX, MIN,or SUM. The ALL clause, which is the default behavior and does not actually need to be specified, evaluates all rows when aggregating the value of the function. The DISTINCT clause uses only distinct values when evaluating the function. AVG and SUM The AVG function computes the average of values in a column or an expression. SUM computes the sum. Both functions work with numeric values and ignore 164 Chapter 4 SQL Functions
Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP Web Hosting services
,ch04.13730 Page 165 Wednesday, November 29, 2000 4:43 PM NULL values. They also can be used to compute the average or sum of all distinct values of a column or expression. AVG and SUM are supported by Microsoft SQL Server, MySQL, Oracle, and PostgreSQL. Example The following query computes average year-to-date sales for each type of book: SELECT type, AVG( ytd_sales ) AS “average_ytd_sales” FROM titles GROUP BY type; This query returns the sum of year-to-date sales for each type of book: SELECT type, SUM( ytd_sales ) FROM titles GROUP BY type; COUNT The COUNT function has three variations. COUNT(*) counts all the rows in the target table whether they include nulls or not. COUNT(expression) computes the number of rows with non-NULL values in a specific column or expression. COUNT(DISTINCT expression) computes the number of distinct non-NULL values in a column or expression. Examples This query counts all rows in a table: SELECT COUNT(*) FROM publishers; The following query finds the number of different countries where publishers are located: SELECT COUNT(DISTINCT country) “Count of Countries” FROM publishers MIN and MAX MIN(expression) and MAX(expression) find the minimum and maximum value (string, datetime, or numeric) in a set of rows. DISTINCT or ALL may be used with these functions, but they do not affect the result. MIN and MAX are supported by Microsoft SQL Server, MySQL, Oracle, and PostgreSQL. MySQL also supports the functions LEAST( ) and GREATEST( ), providing the same capabilities. Examples The following query finds the best and worst sales for any title on record: SELECT ‘MIN’ = MIN(ytd_sales), ‘MAX’ = MAX(ytd_sales) FROM titles; Functions MIN and MAX 165
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 160 Wednesday, November 29, 2000 4:42 PM There are times when it s required to update values in a given table based on the values stored in another table. For example, if it is necessary to update the publication date for all the titles written by a certain author, it is also necessary to find the author and list of titles first through subqueries: UPDATE titles SET pubdate = ‘Jan 01 2002′ WHERE title_id IN (SELECT title_id FROM titleauthor WHERE au_id IN (SELECT au_id FROM authors WHERE au_lname = ‘White’)) Microsoft Syntax and Variations UPDATE {table_name | view_name} [WITH (table_hint [,…n])] SET {column_name | variable_name} = {DEFAULT | expression | NULL} [,…n] [FROM {table [,…n]}] WHERE {conditions | CURRENT OF [GLOBAL] cursor_name} [OPTION (query_hint [,…n])] Microsoft SQL Server is capable of updating both views and tables. Table-and query-level optimizer hints may be declared using the WITH table_hint and OPTION clauses. Optimizer hints override the default functionality of the query optimizer. Consult the vendor documentation for a full discussion of optimizer hints. Microsoft SQL Server supports the FROM clause in an UPDATE statement. The chief benefit of this variation is much easier multitable joins. The following is a sample of table joins using both styles of syntax: — ANSI style UPDATE titles SET pubdate = GETDATE() WHERE title_id IN (SELECT title_id FROM titleauthor WHERE au_id IN (SELECT au_id FROM authors WHERE au_lname = ‘White’)) — Microsoft Transact-SQL style UPDATE titles SET pubdate = GETDATE() FROM authors a, titleauthor t2 WHERE a.au_id = t2.au_id AND t2.title_id = titles.title_id AND a.au_lname = ‘White’ To perform the update using the Transact-SQL style is simply a matter of three table joins between authors, titles, and titleauthor. But to perform the same operation using ANSI-compliant code, first the au_id in author must be found and 160 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 161 Wednesday, November 29, 2000 4:42 PM passed up to the titleauthors table, where the title_id must be identified and then passed up to the main update statement. The clause WHERE CURRENT OF cursor_name tells SQL Server, when used in combination with a cursor, to update only the single record where the cursor is currently positioned. The cursor may be a global or local cursor as designated by the keyword GLOBAL. This example updates the state column for the first 10 authors from the authors table: UPDATE authors SET state = ‘ZZ’ FROM (SELECT TOP 10 * FROM authors ORDER BY au_lname) AS t1 WHERE authors.au_id = t1.au_id MySQL Syntax and Variations UPDATE [LOW PRIORITY] table_name SET {column_name | variable_name} = {DEFAULT | expression} WHERE conditions [LIMIT integer] MySQL supports the SQL99 standard with two variations: the LOW PRIORITY clause and the LIMIT clause. The LOW PRIORITY clause tells MySQL to delay the execution of the UPDATE statement until no other client is reading from the table. The LIMIT clause restricts the UPDATE action to a specific number of rows as designated by the integer value. Oracle Syntax and Variations UPDATE [schema.]{view_name | snapshot_name | table_name [@database_link] {[PARTITION partition_name] | [SUBPARTITION subpartition_name]} | subquery [WITH {[READ ONLY] | [CHECK OPTION [CONSTRAINT constraint_name] ] SET {column [,…] = {expression [,…n] | subquery} | VALUE value} WHERE conditions | CURRENT OF cursor_name} RETURNING expression [,…n] INTO variable [,…n]; The Oracle implementation of UPDATE allows updates against views, snapshots, and tables in an allowable schema. When updating tables, the table can be a local table or one made available via @dblink. Updates always occur against the partition; however, the UPDATE command supports updates against a named PARTITION or SUBPARTITION, if preferred. When updating against a subquery, the WITH clause becomes available. The WITH READ ONLY clause specifies that the subquery cannot be updated. The WITH CHECK OPTION tells Oracle to abort any changes to the updated table that would not appear in the result set of the subquery. The CONSTRAINT subclause tells Oracle to further restrict changes based upon a specific constraint. The SET VALUE clause allows the user to set the entire row value for any table datatype values. In Oracle, the WHERE CURRENT OF clause indicates that the UPDATE should be performed only on the current record within the cursor context. Statements UPDATE 161
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 162 Wednesday, November 29, 2000 4:42 PM RETURNING retrieves the rows affected by the command. When used for a single- row update, the values of the row can be stored in PL/SQL variables and bind variables. When used for a multirow delete, the values of the rows are stored in bind arrays. The INTO keyword indicates that the updated values should be stored in the variables list. PostgreSQL Notes PostgreSQL supports the SQL99 standard. Refer to the earlier section, SQL99 Syntax and Description, for a full description of the UPDATE command. Conclusion The breadth and scope of the SQL commands provide the capability to create and manipulate a wide variety of database objects using the various CREATE, ALTER, and DROP commands. Those database objects then can be loaded with data using commands such as INSERT. The data can be manipulated using a wide variety of commands, such as SELECT, DELETE, and TRUNCATE, as well as the cursor commands, DECLARE, OPEN, FETCH, and CLOSE. Transactions to manipulate the data are controlled through the SET command, plus the COMMIT and ROLLBACK commands. And finally, other commands covered in this chapter include those that control a user s access to database resources through commands such as GRANT and REVOKE. 162 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 158 Wednesday, November 29, 2000 4:42 PM TRUNCATE TABLE The TRUNCATE TABLE command is a non-ANSI statement that removes all rows from a table without logging the individual row deletes. It is a very handy command because it quickly erases all the records in a table without altering the table structure, while taking very little space in the redo logs or transaction logs. However, it has a dark side; since it is not logged, it cannot be recovered or backed up. Vendor SQL Server MySQL Oracle PostgreSQL Command Supported Not supported Supported Supported SQL99 Syntax and Description TRUNCATE TABLE name The TRUNCATE TABLE statement has the same effect as a DELETE statement with no WHERE clause; both erase all rows in a given table. However, there are two important differences. TRUNCATE TABLE is faster, and it is non-logged, meaning it cannot roll back if issued in error. Typically, TRUNCATE TABLE does not activate triggers and does not function when foreign keys are in place on a given table. Example This example removes all data from the publishers table: TRUNCATE TABLE publishers Oracle Syntax and Variations TRUNCATE { CLUSTER [owner.]cluster | TABLE [owner.]table [{PRESERVE | PURGE} SNAPSHOT LOG]} [{DROP | REUSE} STORAGE] Oracle allows a table or an indexed cluster (but not a hash cluster) to be truncated. When truncating a table, Oracle allows the option of preserving or purging the snapshot log, if one is defined on the table. PRESERVE maintains the snapshot log when the master table is truncated, while PURGE clears out the snapshot log. If the DROP STORAGE clause is added, the disk space freed by the deleted rows is deallocated. If the REUSE STORAGE clause is added, the space of the deleted rows allocated to the table or cluster is left in place. Microsoft SQL Server and PostgreSQL Note Both of these implementations support the SQL99 default syntax. 158 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 159 Wednesday, November 29, 2000 4:42 PM UPDATE The UPDATE command changes existing data in a table. Vendor SQL Server Supported, with variations MySQL Supported, with variations Oracle Supported, with variations PostgreSQL Supported Command SQL99 Syntax and Description UPDATE {table_name | view_name} SET {column_name | variable_name} = {DEFAULT | expression} [,…n] WHERE conditions As with the DELETE statement, an UPDATE command is seldom issued without a WHERE clause, since the statement affects every row in the entire table. Statements It is good practice to issue a SELECT command using the same WHERE clause before issuing the actual UPDATE statement. This checks all rows in the result set before actually performing the UPDATE. Whatever rows are returned by the SELECT are modified by the UPDATE. Examples A basic UPDATE statement without a WHERE clause looks like this: UPDATE authors SET contract = 0 Without a WHERE clause, all authors in the authors table have their contract status set to 0 (meaning they don t have a contract any more). Similarly, values can be adjusted mathematically with an UPDATE statement: UPDATE titles SET price = price * 1.1 This UPDATE statement would increase all book prices by 10%. Adding a WHERE clause to an UPDATE statement allows records in the table to be modified selectively: UPDATE titles SET type = ‘pers_comp’, price = (price * 1.15) WHERE type = ‘popular_com’ This query makes two changes to any record of the type popular_com . The command increases their price by 15% and alters their type to pers_comp . UPDATE 159
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 157 Wednesday, November 29, 2000 4:42 PM nesting transactions, only the outermost BEGIN . . . COMMIT or BEGIN . . . ROLLBACK pair should reference the transaction name (if it has one). The WITH MARK option logs the transaction to the SQL Server event log. By specifying WITH MARK log_description , a descriptive string may be added for the event to be logged. The PostgreSQL syntax is: BEGIN [ WORK | TRANSACTION ] PostgreSQL normally runs in autocommit mode where each data modification or query is its own transaction. PostgreSQL normally applies an implicit COMMIT or ROLLBACK at the end of the transaction. Using the BEGIN statement allows the next COMMIT or ROLLBACK to be declared explicitly. Be sure to issue BEGIN in a pair with either COMMIT or ROLLBACK. Otherwise, the DBMS does not complete the command(s) until it encounters COMMIT or ROLLBACK. This could lead to potentially huge transactions with unpredictable results on the data. Manually coded transactions are much faster in PostgreSQL than are autocommitted transactions. The SET TRANSACTION ISOLATION LEVEL should be set to SERIALIZABLE just after the BEGIN statement to bolster the transaction isolation. There could be many data-modification statements (INSERT, UPDATE, DELETE) within a BEGIN . . . COMMIT block. When the COMMIT command is issued, either all or none of the transactions takes place, depending on the success or failure of the command. Example In the following example, the three INSERT statements all be treated as a single transaction: BEGIN TRANSACTION INSERT INTO sales VALUES(’7896′,’JR3435′,’Oct 28 2001′,25, ‘Net 60′,’BU7832′) INSERT INTO sales VALUES(’7901′,’JR3435′,’Oct 28 2001′,17, ‘Net 60′,’BU7832′) INSERT INTO sales VALUES(’7907′,’JR3435′,’Oct 28 2001′,6, ‘Net 60′,’BU7832′) COMMIT GO However, the entire group of transactions would fail, for example, if a primary key restraint is in any one of the INSERT statements. Statements START TRANSACTION 157
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 155 Wednesday, November 29, 2000 4:42 PM Dirty reads Occur when a transaction reads the altered records of another transaction before the other transaction has completed. This allows a data modification to occur on a record that might not be committed to the database. Non-repeatable reads Occur when one transaction reads a record while another modifies it. So, if the first transaction attempts to reread the record, it can t find it. Phantom records Occur when a transaction reads a group of records, but a data modification adds or changes the data so that more records satisfy the first transaction. Setting the isolation level impacts these anomalies as depicted in Table 3-10. Table 3-10: Isolation Level and Anomaly Impact Isolation Level Dirty Reads Non-Repeatable Reads Phantom Records READ COMMITTED No Yes Yes READ UNCOMMITTED Yes Yes Yes REPEATABLE READ No No Yes SERIALIZABLE No No No For SQL99, SERIALIZABLE is the default isolation level. READ WRITE transactions may not be READ UNCOMMITTED. Microsoft SQL Server Syntax and Variations SET TRANSACTION ISOLATION LEVEL {READ COMMITTED | READ UNCOMMITTED | REPEATABLE READ | SERIALIZABLE} READ COMMITTED is the SQL Server default, as opposed to serializable as the default in SQL99. The isolation level is established for the duration of the entire session, not just the transaction as in SQL99. Oracle SQL Server Syntax and Variations SET TRANSACTION READ ONLY; Oracle does not support the full syntax of the SET TRANSACTION statement, and its implementation of READ ONLY differs somewhat as well. Oracle only supports READ COMMITTED and SERIALIZABLE. READ COMMITTED is the default behavior. In Oracle, this command starts a transaction in SERIALIZABLE isolation level. Oracle allows only the SELECT commands when the following commands are set: READ ONLY, ALTER SESSION, ALTER SYSTEM, LOCK TABLE, and SET ROLE. PostgreSQL Syntax and Variations SET TRANSACTION ISOLATION LEVEL {READ COMMITTED | SERIALIZABLE}; PostgreSQL does not support the full syntax of the SET TRANSACTION statement. In PostgreSQL, SET TRANSACTION ISOLATION LEVEL READ COMMITTED specifies Statements SET TRANSACTION 155
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 156 Wednesday, November 29, 2000 4:42 PM that the current transaction s read-only rows committed before the transaction began. This is the default. SERIALIZABLE, which is the ANSI-default isolation level, specifies that the current transaction s read-only rows committed before the first data modification in the batch is executed. START TRANSACTION New in SQL99, the START TRANSACTION statement allows all the functions of SET TRANSACTION to be performed and allows a new transaction to be initiated. Vendor SQL Server MySQL Oracle PostgreSQL Command Not supported; see BEGIN TRAN later Not supported Not supported Not supported; see BEGIN TRAN later SQL99 Syntax and Description START TRANSACTION { {READ ONLY | READ WRITE}[,…] | ISOLATION LEVEL {READ COMMITTED | READ UNCOMMITTED | REPEATABLE READ | SERIALIZABLE}[,…] | DIAGNOSTIC SIZE INT}; The only difference between SET and START is that SET is considered outside of the current transaction, while START is considered the marking of a new transaction. BEGIN TRANSACTION The command BEGIN TRANSACTION provides similar functionality to START TRANSACTION. Both Microsoft SQL Server and PostgreSQL support BEGIN TRANSACTION, though they have slight variations in their syntax. Oracle supports implicit, but not explicit, transactions. MySQL doesn t support atomic transactions at all. BEGIN TRANSACTION declares an explicit transaction, but it does not set isolation levels. The Microsoft SQL Server syntax is: BEGIN TRAN[SACTION] [transaction_name | @transaction_variable [WITH MARK [ ‘log_description’ ] ] ] Microsoft SQL Server allows a name to be assigned to a transaction or to reference transactions using a variable. It does not affect or add to functionality. When 156 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