,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 202 Wednesday, November 29, 2000 4:44 PM Table A-4: Oracle Keywords (continued) JAVA LEVEL LIKE LIMITED LOCK LONG LOOP MAX MAXEXTENTS MIN MINUS MINUTE MLSLABEL MOD MODE MODIFY MONTH NATURAL NATURALN NEW NEXTVAL NOAUDIT NOCOMPRESS NOCOPY NOT NOWAIT NULL NUMBER NUMBER_BASE OCIROWID OF OFFLINE ON ONLINE OPAQUE OPEN OPERATOR OPTION OR ORDER ORGANIZATION OTHERS OUT PACKAGE PARTITION PCTFREE PLS_INTEGER POSITIVE POSITIVEN PRAGMA PRIOR PRIVATE PRIVILEGES PROCEDURE PUBLIC RAISE RANGE RAW REAL RECORD REF RELEASE RENAME RESOURCE RETURN REVERSE REVOKE ROLLBACK ROW ROWS ROWID ROWLABEL ROWNUM ROWTYPE SAVEPOINT SECOND SELECT SEPERATE SESSION SET SHARE SIZE SMALLINT SPACE SQL SQLCODE SQLERRM START STDDEV SUBTYPE SUCCESSFUL SUM SYNONYM SYSDATE TABLE THEN TIME TIMESTAMP TO TRIGGER TRUE TYPE UID UNION UNIQUE UPDATE USE USER VALIDATE VALUES VARCHAR VARCHAR2 VARIANCE VIEW WHEN WHENEVER WHERE WHILE WITH WORK WRITE YEAR ZONE Table A-5: PostgreSQL Keywords ABORT ADD ALL ALLOCATE ALTER ANALYZE AND ANY ARE AS ASC ASSERTION AT AUTHORIZATION AVG BEGIN BETWEEN BINARY BIT BIT_LENGTH BOTH BY CASCADE CASCADED CASE CAST CATALOG CHAR CHAR_LENGTH CHARACTER CHARACTER_ CHECK LENGTH CLOSE CLUSTER COALESCE COLLATE 202 SQL99 and Vendor-Specific Keywords
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
,appa.13975 Page 200 Wednesday, November 29, 2000 4:44 PM Table A-3: MySQL Keywords ACTION ADD AFTER AGGREGATE ALL ALTER AND AS ASC AUTO_INCREMENT AVG AVG_ROW_ LENGTH BETWEEN BIGINT BINARY BIT BLOB BOOL BOTH BY CASCADE CASE CHANGE CHAR CHARACTER CHECK CHECKSUM COLUMN COLUMNS COMMENT CONSTRAINT CREATE CROSS CURRENT_DATE CURRENT_TIME CURRENT_ TIMESTAMP DATA DATABASE DATABASEs DATE DATETIME DAY DAY_HOUR DAY_MINUTE DAY_SECOND DAYOFMONTH DAYOFWEEK DAYOFYEAR DEC DECIMAL DEFAULT DELAY_KEY_ WRITE DELAYED DELETE DESC DESCRIBE DISTINCT DISTINCTROW DOUBLE DROP ELSE ENCLOSED END ENUM ESCAPE ESCAPED EXISTS EXPLAIN FIELDS FILE FIRST FLOAT FLOAT4 FLOAT8 FLUSH FOR FOREIGN FROM FULL FUNCTION GLOBAL GRANT GRANTS GROUP HAVING HEAP HIGH_PRIORITY HOSTS HOUR HOUR_MINUTE HOUR_SECOND IDENTIFIED IF IGNORE IN INDEX INFILE INNER INSERT INSERT_ID INT INT1 INT2 INT3 INT4 INT8 INTEGER INTERVAL INTO IS ISAM JOIN KEY KEYS KILL LAST_INSERT_ID LEADING LEFT LENGTH LIKE LIMIT LINES LOAD LOCAL LOCK LOGS LONG LONGBLOB LONGTEXT LOW_PRIORITY MATCH MAX MAX_ROWS MEDIUMBLOB MEDIUMINT MEDIUMTEXT MIDDLEINT MIN_ROWS MINUTE MINUTE_SECOND MODIFY MONTH MONTHNAME MYISAM NATURAL NO NOT NULL NUMERIC ON OPTIMIZE OPTION OPTIONALLY OR ORDER OUTER OUTFILE PACK_KEYS PARTIAL PASSWORD PRECISION PRIMARY PRIVILEGES PROCEDURE 200 SQL99 and Vendor-Specific Keywords
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 199 Wednesday, November 29, 2000 4:44 PM Table A-2: Microsoft SQL Server Keywords ADD ALL ALTER ANY AS ASC BACKUP BEGIN BETWEEN BROWSE BULK BY CASE CHECK CHECKPOINT CLUSTERED COALESCE COLLATE COMMIT COMPUTE CONSTRAINT CONTAINSTABLE CONTINUE CONVERT CROSS CURRENT CURRENT_DATE CURRENT_TIMESCURRENT_ USER CURSOR TAMP DBCC DEALLOCATE DECLARE DELETE DENY DESC DISTINCT DISTRIBUTED DOUBLE DUMMY DUMP ELSE ERRLVL EXCEPT EXEC EXISTS EXIT FETCH FILLFACTOR FOR FOREIGN FREETEXTTABLE FROM FULL GOTO GRANT GROUP HOLDLOCK IDENTITY IDENTITY_INSERT IF IN INDEX INSERT INTERSECT INTO JOIN KEY KILL LIKE LINENO LOAD NOCHECK NONCLUSTERED NONCLUSTERED NULLIF OF OF ON OPEN OPENDATASOURCE OPENROWSET OPENXML OPTION ORDER OUTER PERCENT PRECISION PRIMARY PRINT READTEXT RECONFIGURE REFERENCES RESTORE RESTRICT RETURN RIGHT ROLLBACK ROWCOUNT RULE SAVE SCHEMA SESSION_USER SET SETUSER SOME STATISTICS SYSTEM_USER TEXTSIZE THEN TO TRAN TRANSACTION TRIGGER TSEQUAL UNION UNIQUE UPDATETEXT USE USER VARYING VIEW WAITFOR WHERE WHILE WITH AND AUTHORIZATION BREAK CASCADE CLOSE COLUMN CONTAINS CREATE CURRENT_TIME DATABASE DEFAULT DISK DROP END EXECUTE FILE FREETEXT FUNCTION HAVING IDENTITY_INSERT INNER IS LEFT NATIONAL NULL OFFSETS OPENQUERY OR PLAN PROC REPLICATION REVOKE ROWGUID-COL SELECT SHUTDOWN TABLE TOP TRUNCATE UPDATE VALUES WHEN WRITETEXT Keywords SQL99 and Vendor-Specific Keywords 199
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 198 Wednesday, November 29, 2000 4:44 PM Table A-1: SQL Keywords (continued) FOR FOREIGN FREE FROM FUNCTION GENERAL GET GLOBAL GOTO GROUP GROUPING HANDLER HASH HOUR IDENTITY IF IGNORE IMMEDIATE IN INDICATOR INITIALIZE INITIALLY INNER INOUT INPUT INSERT INT INTEGER INTERSECT INTERVAL INTO IS ISOLATION ITERATE JOIN KEY LANGUAGE LARGE LAST LATERAL LEADING LEAVE LEFT LESS LEVEL LIKE LIMIT LOCAL LOCALTIME LOCALTIMELOCATOR LOOP STAMP MATCH MEETS MINUTE MODIFIES MODIFY MODULE MONTH NAMES NATIONAL NATURAL NCHAR NCLOB NEW NEXT NO NONE NORMALIZE NOT NULL NUMERIC OBJECT OF NUMERIC OBJECT OF OFF OLD ON ONLY OPEN OPERATION OPTION OR ORDER ORDINALITY OUT OUTER OUTPUT PAD PARAMETER PARAMETERS PARTIAL PATH PERIOD POSTFIX PRECEDES PRECISION PREFIX PREORDER PREPARE PRESERVE PRIMARY PRIOR PRIVILEGES PROCEDURE PUBLIC READ READS REAL RECURSIVE REDO REF REFERENCES REFERENCING RELATIVE REPEAT RESIGNAL RESTRICT RESULT RETURN RETURNS REVOKE RIGHT ROLE ROLLBACK ROLLUP ROUTINE ROW ROWS SAVEPOINT SCHEMA SCROLL SEARCH SECOND SECTION SELECT SEQUENCE SESSION SESSION_USER SET SETS SIGNAL SIZE SMALLINT SPECIFIC SPECIFICTYPE SQL SQLEXCEPTION SQLSTATE SQLWARNING START STATE STATIC STRUCTURE SUCCEEDS SUM SYSTEM_USER TABLE TEMPORARY TERMINATE THAN THEN TIME TIMESTAMP TIMEZONE_ TIMEZONE_ HOUR MINUTE TO TRAILING TRANSACTION TRANSLATION TREAT TRIGGER TRUE UNDER UNDO UNION UNIQUE UNKNOWN UNTIL UPDATE USAGE USER USING VALUE VALUES VALUES VARIABLE VARYING VIEW WHEN WHENEVER WHERE WHILE WITH WRITE YEAR ZONE 198 SQL99 and Vendor-Specific 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 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
,ch05.13852 Page 195 Wednesday, November 29, 2000 4:43 PM Table 5-1: Unimplemented SQL99 Commands (continued) Commands ALTER DOMAIN CREATE ASSERTION CREATE CHARACTER SET CREATE COLLATION CREATE TRANSLATION DEALLOCATE DESCRIPTOR DEALLOCATE PREPARE DESCRIBE DROP ASSERTION DROP CHARACTER SET DROP COLLATION DROP TRANSLATION GET DESCRIPTOR GET DIAGNOSTICS Description A domain is generally a user-defined datatype. According to the SQL99 standard, the ALTER DOMAIN command allows the definition of a domain to be changed by adding or dropping a default or constraint. Typically, only the owner of the schema that contains the domain may alter it. Any changes specified with the command instantly affect all columns based on the domain. An assertion is a generic CHECK constraint that may be applied over and over again. Thus, a check constraint that controls the emp_id column can be applied as an assertion in every table that holds emp_id. This command is not supported by the database vendors covered in this book; however, Microsoft SQL Server supports the CREATE RULE statement, which is functionally equivalent. This statement creates individualized character sets for improved internationalization of a database platform. However, most vendors ship their products with a wide range of character sets. Although some make it possible to create or change character sets, they do not support this command. A collating sequence determines the order in which text strings are sorted. Like character sets, the database platform normally ships with one or more collations. This command creates new sorting sequences for a given character set. A translation is a descriptor that translates strings from one character set to another. Character strings then can be translated using the TRANSLATE command. New translations based upon old ones can be built using this command. This statement removes a previously declared descriptor created using ALLOCATE DESCRIPTOR. This statement destroys a previously prepared SQL statement created using the PREPARE statement. This command stores parameterized information about a SQL statement held in the named descriptor area created using the ALLOCATE DESCRIPTOR statement. Note: Oracle possesses a DESCRIBE command that is used to report information on a table, describing its columns, datatypes, and so on. This statement removes an assertion from the database that was previously created using the CREATE ASSERTION statement. This command removes a character set from the database that was previously created using the CREATE CHARACTER SET statement. This statement removes a collation from the database that was previously created using the CREATE COLLATION statement. This command removes a translation from the database that was previously created using the CREATE TRANSLATION statement. The GET DESCRIPTOR statement retrieves information from the descriptor areas, primarily about input and output parameters used in Dynamical SQL statements. The GET DIAGNOSTICS command provides information (taken from the diagnostics area) about the last SQL statement executed or about a specific error message encountered while processing a SQL statement. NewCommands Unimplemented SQL99 Commands 195
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