,appa.13975 Page 203 Wednesday, November 29, 2000 4:44 PM Table A-5: PostgreSQL Keywords (continued) COLLATION COLUMN COMMIT CONNECT CONNECTION CONSTRAINT CONTINUE CONVERT COPY CORRESPONDING COUNT CREATE CROSS CURRENT CURRENT_DATE CURRENT_SESSION CURRENT_TIME CURRENT_ CURRENT_USER CURSOR TIMESTAMP DATE DEALLOCATE DEC DECIMAL DECLARE DEFAULT DELETE DESC DESCRIBE DESCRIPTOR DIAGNOSTICS DISCONNECT DISTINCT DO DOMAIN DROP ELSE END ESCAPE EXCEPT EXCEPTION EXEC EXECUTE EXISTS EXPLAIN EXTEND EXTERNAL EXTRACT FALSE FETCH FIRST FLOAT FOR FOREIGN FOUND FROM FULL GET GLOBAL GO GOTO GRANT GROUP HAVING IDENTITY IN INDICATOR INNER INPUT INSERT INTERSECT INTERVAL INTO IS JOIN LAST LEADING LEFT LIKE LISTEN LOAD LOCAL LOCK LOWER MAX MIN MODULE MOVE NAMES NATIONAL NATURAL NCHAR NEW NO NONE NOT NOTIFY NULL NULLIF NUMERIC OCTET_LENGTH OFFSET ON OPEN OR ORDER OUTER OUTPUT OVERLAPS PARTIAL POSITION PRECISION PREPARE PRESERVE PRIMARY PRIVILEGES PROCEDURE PUBLIC REFERENCES RESET REVOKE RIGHT ROLLBACK ROWS SCHEMA SECTION SELECT SESSION SESSION_USER SET SETOF SHOW SIZE SOME SQL SQLCODE SQLERROR SQLSTATE SUBSTRING SUBSTRING SYSTEM_USER TABLE TEMPORARY THEN TO TRAILING TRANSACTION TRANSLATE TRANSLATION TRIM TRUE UNION UNIQUE UNKNOWN UNLISTEN UNTIL UPDATE UPPER USAGE USER USING VACUUM VALUE VALUES VARCHAR VARYING VERBOSE VIEW WHEN WHENEVER WHERE WITH WORK WRITE
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
,appa.13975 Page 201 Wednesday, November 29, 2000 4:44 PM Table A-3: MySQL Keywords (continued) PROCESS PROCESSLIST READ REAL REFERENCES REGEXP RELOAD RENAME REPLACE RESTRICT RETURNS REVOKE RLIKE ROW ROWS SECOND SELECT SET SHOW SHUTDOWN SMALLINT SONAME SQL_BIG_RESULT SQL_BIG_SELECTS SQL_BIG_TABLES SQL_LOG_OFF SQL_LOG_UPDATE SQL_LOW_ PRIORITY_ UPDATES SQL_SELECT_LIMIT SQL_SMALL_ SQL_WARNINGS STARTING RESULT STATUS STRAIGHT_JOIN STRING SQL_SMALL_ RESULT TABLES TEMPORARY TERMINATED TEXT THEN TIME TIMESTAMP TINYBLOB TINYINT TINYTEXT TO TRAILING TYPE UNIQUE UNLOCK UNSIGNED UPDATE USAGE USE USING VALUES VARBINARY VARCHAR VARIABLES VARYING WHEN WITH WRITE ZEROFILL Table A-4: Oracle Keywords ACCESS ADD ALL ALTER AND ANY ARRAY AS ACS AUDIT AUTHID AVG BEGIN BETWEEN BINARY INTEGER BODY BOOLEAN BULK BY CHAR CHAR_BASE CHECK CLOSE CLUSTER COLLECT COLUMN COMMENT COMMIT COMPRESS CONNECT CONSTANT CREATE CURRENT CURRVAL CURSOR DATE DAY DECLARE DECIMAL DEFAULT DELETE DESC DISTINCT DO DROP ELSE ELSIF END EXCEPTION EXCLUSIVE EXECUTE EXISTS EXIT EXTENDS FALSE FETCH FILE FLOAT FOR FORALL FROM FUNCTION GOTO GRANT GROUP HAVING HEAP HOUR IDENTIFIED IF IMMEDIATE IN INCREMENT INDEX INDICATOR INITIAL INSERT INTEGER INTERFACE INTERSECT INTERVAL INTO IS ISOLATION Keywords SQL99 and Vendor-Specific Keywords 201
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
,ch05.13852 Page 196 Wednesday, November 29, 2000 4:43 PM Table 5-1: Unimplemented SQL99 Commands (continued) Commands PREPARE SET CATALOG SET CONSTRAINTS MODE SET DESCRIPTOR SET NAMES SET SESSION AUTHORIZATION SET SESSION CHARACTERISTICS Description The PREPARE statement creates a Dynamic SQL from a text variable so that it can be executed many times using the EXECUTE command. This command defines the current catalog, which holds the current schema, used by the current session. This statement determines whether constraints are checked immediately upon completion of a transaction or deferred until the end of the transaction. Constraints also might be checked immediately, meaning after each statement. Many vendors offer alternative methods for achieving this sort of functionality. For example, Oracle offers this functionality through the command ALTER SESSION SET CONSTRAINTS. Research the vendor documentation for other possible alternatives. The SET DESCRIPTOR statement places values into previously allocated cursors or alters the behavior of a descriptor. This command sets the default character set for a session. The SET SESSION AUTHORIZATION statement alters the authorization under which statements are processed. The SET SESSION CHARACTERISTICS statement allows the same properties to be set for all of the transactions processed in an entire session, just like using other SET statements for a single transaction. 196 Chapter 5 Unimplemented SQL99 Commands
Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP Web Hosting services
,appa.13975 Page 197 Wednesday, November 29, 2000 4:44 PM APPENDIX SQL99 and Vendor-Specific Keywords The tables below display the keywords in the SQL99 standard and in the four vendor implementations of SQL that are discussed in this book. SQL keywords are described in more detail in Chapter 2, Foundational Concepts. Table A-1: SQL Keywords ABSOLUTE ACTION ADD ADMIN AFTER AGGREGATE ALIAS ALL ALLOCATE ALTER AND ANY ARE ARRAY AS ASC ASSERTION AT AUTHORIZABEFORE TION BEGIN BINARY BIT BLOB BOOLEAN BOTH BREADTH BY CALL CASCADE CASCADED CASE CAST CATALOG CHAR CHARACTER CHECK CLASS CLOB CLOSE COLLATE COLLATION COLUMN COMMIT COMPLETION CONDITION CONNECT CONNECTION CONSTRAINT CONSTRAINTS CONSTRUCTOR CONTAINS CONTINUE CORRECREATE CROSS SPONDING CUBE CURRENT CURRENT_DATE CURRENT_PATH CURRENT_ROLE CURRENT_TIME CURRENT_ CURRENT_USER TIMESTAMP CURSOR CYCLE DATA DATALINK DATE DAY DEALLOCATE DEC DECIMAL DECLARE DEFAULT DEFERRABLE DELETE DEPTH DEREF DESC DESCRIPTOR DIAGNOSTICS DICTIONARY DISCONNECT DO DOMAIN DOUBLE DROP END-EXEC EQUALS ESCAPE EXCEPT EXCEPTION EXECUTE EXIT EXPAND EXPANDING FALSE FIRST FLOAT 197 Keywords
Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost PHP Web Hosting services
,ch05.13852 Page 194 Wednesday, November 29, 2000 4:43 PM CHAPTER 5 Unimplemented SQL99 Commands The SQL92 and SQL99 standards specify many commands. However, RMBDS vendors are not able to implement all of them immediately. In fact, many commands specified in the new SQL99 standard are nowhere near being implemented by any of the database vendors covered in SQL in a Nutshell. In effect, these commands exist because the standard says they exist, but they cannot currently be executed anywhere. As a result, these commands are described only briefly in Table 5-1. Nonetheless, it should be noted that although these commands are not implemented yet, they could be eventually. Consequently, it is important to check vendor documentation to see if the newest version has implemented any of these commands. Table 5-1: Unimplemented SQL99 Commands Commands ALLOCATE CURSOR ALLOCATE DESCRIPTOR Description The ALLOCATE CURSOR statement is used in Dynamic SQL to link a SELECT statement initialized with the PREPARE statement. This statement differs from DECLARE CURSOR in that ALLOCATE CURSOR creates multiple precompiled cursor statements while DECLARE CURSOR is created and compiled dynamically each time it is run. This statement prepares a dynamic area that stores information about the parameters in a dynamically generated SQL statement. In effect, this command allows the amount of space set aside for dynamic SQL statements to be controlled precisely. 194 Chapter 5 Unimplemented SQL99 Commands
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
,ch04.13730 Page 193 Wednesday, November 29, 2000 4:43 PM Table 4-10: PostgreSQL Supported Functions (continued) Function position(string in string) pow(float8,float8) popen(path) reltime(interval) radians(float8) radius(circle) round(float8) rpad(text,int,text) rtrim(text,text) sin(float8) sqrt(float8) substring(string [from int] [for int]) substr(text,int[,int]) tan(float8) text(char) text(varchar) textpos(text,text) timestamp(date) timestamp(date,time) to_char(timestamp, text) to_char(int, text) to_char(float, text) to_char(numeric, text) to_date(text, text) to_number(text, text) to_timestamp(text, text) translate(text,from,to) trim([leading|trailing| both] [string] from string) trunc(float8) upper(text) varchar(char) varchar(text) width(box) Description Returns location of specified substring Raises a number to the specified exponent Converts path to open path Converts to reltime Converts degrees to radians Returns radius of circle Rounds to nearest integer Converts right pad string to specified length Converts right trim characters from text Returns sine Returns square root Extracts specified substring Extracts specified substring Returns tangent Converts char to text type Converts varchar to text type Locates specified substring Converts to timestamp Converts to timestamp Converts timestamp to string Converts int4/int8 to string Converts float4/float8 to string Converts numeric to string Converts string to date Converts string to numeric Converts string to timestamp Converts character in string Trims characters from string Truncates (towards zero) Converts text to uppercase Converts char to varchar type Converts text to varchar type Returns horizontal size Functions Vendor Extensions 193
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
,ch04.13730 Page 191 Wednesday, November 29, 2000 4:43 PM Table 4-9: Oracle-Supported Functions (continued) Function var_samp(expression) over (analytics) variance([DISTINCT] expression) over (analytics) vsize(expression) Description Returns the sample variance of a set of numbers after discarding the nulls in the expression number set. Analytic functions are covered in the vendor documentation. Returns variance of expression calculated as follows: 0 if the number of rows in expression = 1 VAR_SAMP if the number of rows in expression > 1 Returns the number of bytes in the internal representation of expression. When expression is null, it returns null. PostgreSQL Supported Functions Table 4-10 lists the functions specific to PostgreSQL. Table 4-10: PostgreSQL Supported Functions Function abstime(timestamp) abs(float8) acos(float8) age(timestamp) age(timestamp, timestamp) area(object) asin(float8) atan(float8) atan2(float8,float8) box(box,box) box(circle) box(point,point) box(polygon) broadcast(cidr) broadcast(inet) CASE WHEN expr THEN expr […] ELSE expr END cbrt(float8) center(object) char(text) char(varchar) char_length(string) character_length(string) circle(box) circle(point,float8) COALESCE(list) cos(float8) cot(float8) Description Converts to abstime Returns absolute value Returns arccosine Preserves months and years Preserves months and years Returns area of item Returns arcsine Returns arctangent Returns arctangent Returns intersection box Converts circle to box Returns points to box Converts polygon to box Constructs broadcast address as text Constructs broadcast address as text Returns expression for first true WHEN clause Returns cube root Returns center of item Converts text to char type Converts varchar to char type Returns length of string Returns length of string Converts to circle Converts point to circle Returns first non-NULL value in list Returns cosine Returns cotangent Functions Vendor Extensions 191
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
,ch04.13730 Page 189 Wednesday, November 29, 2000 4:43 PM Table 4-9: Oracle-Supported Functions (continued) Function replace(string, search_ string [,replacement_ string]) round (number, decimal) round (date[, format]) row_number ( ) over ( query_partition ORDER BY ) rowidtochar(rowid) rpad(string1, number [, string2]) rtrim(string[,set]) sign(number) sin(number) sinh(number) soundex(string) sqrt(number) stddev( [DISTINCT] expression) over (analytics) stdev_pop(expression) over (analytics) seddev_samp(expression) over (analytics) substr(extraction_string [FROM starting_position] [FOR length]) substrb(extraction_string [FROM starting_position] [FOR length]) sum([DISTINCT] expression) over (analytics) sys_context ( namespace , attribute [,length]) sys_guid( ) sysdate tan(number) Description Returns string with every occurrence of search_string replaced with replacement_string. Returns number rounded to decimal places right of the decimal point. When decimal is omitted, number is rounded to 0 places. Note that decimal, an integer, can be negative to round off digits left of the decimal point. Returns the date rounded to the unit specified by the format model format. When format is omitted, date is rounded to the nearest day. Assigns a unique number to each row where it is applied in the ordered sequence of rows specified by the ORDER_BY_ clause, beginning with 1. Converts a rowid value to VARCHAR2 datatype, 18 characters long. Returns string1, right-padded to length number with the value of string2, repeated as needed. String2 defaults to a single blank. Returns string, with all the rightmost characters that appear in set removed; set defaults to a single blank. When number < 0, returns 1. When number = 0, returns 0. When number > 0, returns 1. Returns the sine of number as an angle expressed in radians. Returns the hyperbolic sine of number. Returns a character string containing the phonetic representation of string. This function allows words that are spelled differently but sound alike in English to be compared for equality. Returns square root of number, a nonnegative number. Returns sample standard deviation of a set of numbers shown as expression. Computes the population standard deviation and returns the square root of the population variance. Computes the cumulative sample standard deviation and returns the square root of the sample variance. Refer to the earlier section on SUBSTR. SUBSTRB is the same as SUBSTR, except that the arguments m starting_position and length are expressed in bytes, rather than in characters. Returns sum of values of expr; refer to vendor documentation for assistance with analytics and the OVER subclause. Returns the value of attribute associated with the context namespace, usable in both SQL and PL/SQL statements. Generates and returns a globally unique identifier (RAW value) made up of 16 bytes. Returns the current date and time, requiring no arguments. Returns the tangent of number as an angle expressed in radians. Functions Vendor Extensions 189
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
,ch04.13730 Page 187 Wednesday, November 29, 2000 4:43 PM Table 4-9: Oracle-Supported Functions (continued) Function log(base_number, number) lower(string) lpad(string1, number [,string2]) ltrim(string[, set]) make_ref({table_name | view_name} , key [, n]) max([DISTINCT] expression) over (analytics) min([DISTINCT] expression) over (analytics) mod(dividend, divider) months_between (date1, date2) new_time(date, time_ zone1, time_zone2) next_day(date, string) nls_charset_decl_ len(bytecnt, csid) nls_charset_id(text) nls_charset_ name(number) nls_initcap(string [, nlsparameter ]) nls_lower(string, [, nlsparameter ]) nlssort(string [, nlsparameter ]) nls_upper string [, nlsparameter ]) Description Returns the logarithm of any base_number of number. Returns string in the same datatype as it was supplied with all characters lowercase. Returns string1, left-padded to length number using characters in string2; string2 defaults to a single blank. Removes all characters in set from the left of string. Set defaults to a single blank. Creates a reference (REF ) to a row of an object view or a row in an object table whose object identifier is primary key-based. Returns maximum value of expression. It can be used as an aggregate or analytic function (analytic functions are beyond the scope of this text). Returns minimum value of expression. It can be used as an aggregate or analytic function (analytic functions are beyond the scope of this text). Returns remainder of dividend divided by divider; returns the dividend if divider is 0. Returns number of months between dates date1 and date2. When date1 is later than date2, the result is positive. If it is earlier, the result is negative. Returns the date and time in time_zone2 when date and time in time_zone1 are date. Time_zones 1 and 2 may be any of these text strings: AST, ADT: Atlantic Standard or Daylight Time BST, BDT: Bering Standard or Daylight Time CST, CDT: Central Standard or Daylight Time EST, EDT: Eastern Standard or Daylight Time GMT: Greenwich Mean Time HST, HDT: Alaska-Hawaii Standard Time or Daylight Time MST, MDT: Mountain Standard or Daylight Time NST: Newfoundland Standard Time PST, PDT: Pacific Standard or Daylight Time YST, YDT: Yukon Standard or Daylight Time Returns the date of the first weekday named by string that is later than date. The argument string must be either the full name or the abbreviation of a day of the week in the date language of the session. Returns the declaration width (bytecnt)ofan NCHAR column using the character set ID (csid ) of the column. Returns the NLS character set ID number corresponding to text. Returns the VARCHAR2 name for the NLS character set corresponding to the ID number. Returns string with the first letter of each word in uppercase and all other letters in lowercase. The nlsparameter offers special linguistic sorting features. Returns string with all letters lowercase. The nlsparameter offers special linguistic sorting features. Returns the string of bytes used to sort string. The nlsparameter offers special linguistic sorting features. Returns string with all letters uppercase. The nlsparameter offers special linguistic sorting features. Functions Vendor Extensions 187
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
,ch04.13730 Page 184 Wednesday, November 29, 2000 4:43 PM Table 4-8: MySQL-Supported Functions (continued) Function substring_index (str,delim,count) substring(str,pos) substring(str FROM pos) sum(expr) tan(X). time_format (time,format) time_to_sec(time) to_days(date) trim([[BOTH | LEADING | TRAILING] [remstr] FROM] str) truncate(X,D) ucase(str) upper(str) unix_timestamp( ) unix_timestamp(date) user( ) system_user( ) session_user( ) version( ) week(date) week(date,first) weekday(date) year(date) yearweek(date) yearweek(date,first) Description Returns the substring from string str after count occurrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned. Returns a substring from string str starting at position pos. Returns the sum of expr. Note that if the return set has no rows, it returns NULL. Returns the tangent of X, where X is given in radians. This is used like DATE_FORMAT( ), but the format string may contain only those format specifiers that handle hours, minutes, and seconds. Other specifiers produce a NULL value or 0. Returns the time argument, converted to seconds. Given a date, returns a daynumber (the number of days since year 0). Returns the string str with all remstr prefixes and/or suffixes removed. If none of the specifiers BOTH, LEADING, or TRAILING are given, BOTH is assumed. If remstr is not specified, spaces are removed. Returns the number X, truncated to D decimals. If D is 0, the result has no decimal point or fractional part. Returns the string str with all characters changed to uppercase according to the current character set mapping (default is ISO-8859-1 Latin1). If called with no argument, returns a Unix timestamp (seconds since 1970-01-01 00:00:00 GMT). If UNIX_ TIMESTAMP( ) is called with a date argument, it returns the value of the argument as seconds since 1970-01-01 00:00:00 GMT. These functions return the current MySQL username. Returns a string indicating the MySQL server version. With a single argument, returns the week for date, in the range 0 to 53. (The beginning of a week 53 is possible during some years.) The two-argument form of WEEK( ) allows the user to specify whether the week starts on Sunday (0) or Monday (1). Returns the weekday index for date (0 = Monday, 1 = Tuesday, . . . 6 = Sunday). Returns the year for date, in the range 1000 to 9999. Returns year and week for a date. The second argument works exactly like the second argument to WEEK( ). Note that the year may be different from the year in the date argument for the first and the last week of the year. Oracle SQL Supported Functions Table 4-9 provides an alphabetical listing of the SQL functions specific to Oracle. 184 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