Informix Guide to SQL: Syntax

1 Informix Guide to SQLS yntaxVersion 1996 Part No. 000-7859 AiiInformix Guide to SQL: SyntaxPublished by Informix PressInformix Software, Bohannon DriveMenlo Park, CA 94025 The following are worldwide trademarks of Informix Software, Inc., or its subsidiaries, registered in theUnited States of America as indicated by , and in numerous other countries worldwide: Informix ; C-ISAM ; Informix -OnLine Dynamic Server The following are worldwide trademarks of the indicated owners or their subsidiaries, registered in theUnited States of America as indicated by , and in numerous other countries worldwide:Regents of the University of California: BSD Microsoft Corporation: Microsoft ; MS ; MS-DOS ;( DOS as used herein refers to MS-DOS and/or PC-DOS operating systems.)

2 X/OpenCompany Ltd.: UNIX ; X/Open Some of the products or services mentioned in this document are provided by companies other than products or services are identified by the trademark or servicemark of the appropriate company. If youhave a question about one of those products or services, please call the company in question Team: Diana Chase, Geeta Karmarkar, Dawn Maneval, Tom Noronha, Patrice O NeillCopyright 1981-1996 by Informix Software, Inc. All rights part of this work covered by the copyright hereon may be reproduced or used in any form or by anymeans graphic, electronic, or mechanical, including photocopying, recording, taping, or information storageand retrieval systems without permission of the the extent that this software allows the user to store, display, and otherwise manipulate various forms ofdata, including, without limitation, multimedia content such as photographs, movies, music and other binarylarge objects (blobs), use of any single blob may potentially infringe upon numerous different third-partyintellectual and/or proprietary rights.

3 It is the user's responsibility to avoid infringements of any such third-party RIGHTS LEGENDS oftware and accompanying materials acquired with United States Federal Government funds or intended foruse within or for any United States federal agency are provided with Restricted Rights as defined in (c)(1)(ii) or FAR of ContentsTable ofContentsIntroductionProducts Covered in This This of This of Features of This 16 Typographical 16 Icon 17 Syntax 19 Sample-Code 24 Terminology 25 Additional 26 Printed 26On-Line 27 Related 29 Compliance with Industry 30 Informix Welcomes Your 30 Chapter 1 SQL StatementsHow to Enter SQL 1-6 How to Enter SQL 1-9 Categories of SQL Compliance and Guide to SQL.

4 SyntaxCHECK PROCEDURE of ContentsvRECOVER DEBUG FILE LOCK SESSION VIOLATIONS VIOLATIONS Field Field Guide to SQL: SyntaxTable 2 SPL Covered in This This of This of About Your Database About Your Features of This and Platform That Can Appear on the to Read a Syntax of of Product Guide to SQL: SyntaxAdditional Message Notes, Documentation Notes, Machine with Industry Welcomes Your chapter introduces theInformix Guide toSQL: Syntax manual. Readthis chapter for an overview of the information provided in this manual andfor an understanding of the conventions used throughout this Covered in This ManualAll the information presented in this manual is valid for the following prod-ucts.

5 Differences in their use ofSQL are indicated where appropriate:nINFORMIX-ESQL/C, Version , Version , Version , Version :This manual does not cover the product calledINFORMIX-SQL or anyother Informix application development tool. Each of these tools is covered in its ownmanual set. This manual is valid only for the products in the preceding This ManualTheInformix Guide toSQL: Syntax is intended to be used as a companionvolume to theInformix Guide to SQL: Reference andInformix Guide to SQL:Tutorial. This manual and theInformix Guide to SQL: Reference are referencesthat you can use on a daily basis after you finish reading and experimentingwith theInformix Guide to SQL: Guide to SQL: SyntaxOrganization of This ManualThis manual contains all the Syntax descriptions for Structured QueryLanguage (SQL) and Stored Procedure Language (SPL) statements.

6 TheInformix Guide to SQL: Tutorial explains the philosophy and concepts behindrelational databases, and theInformix Guide to SQL: Reference providesreference information for aspects ofSQL other than the language of This ManualTheInformix Guide toSQL: Syntax includes the following chapters:nThe Introduction explains the purpose and organization of this man-ual, introduces the demonstration database from which the productexamples in the manual are drawn, lists the new features forVersion of Informix database server products that useSQL,anddescribes the Syntax conventions followed in the 1, SQL Statements, describesSQL statements and seg-ments. The chapter is divided into six sections.

7 The first four sectionsprovide an introduction to the statements and segments. These sec-tions cover the following subjects: entry ofSQL statements, entry ofSQL comments, categories ofSQL statements, and categories ofANSI compliance. The fifth and sixth sections, Statements and Seg-ments, are the major sections of the Statements explains the workings of all theSQLstatementsthat Informix products support. Detailed Syntax diagrams walkyou through every clause of eachSQL statement, and syntaxtables explain the input parameters for each clause. Thoroughusage instructions, pertinent examples, and references to relatedmaterial complete the description of eachSQL Segments explains all theSQL segments arelanguage elements, such as table names and expressions, thatoccur in manySQL statements.

8 Instead of describing eachsegment in each statement where it occurs, this manual providesa comprehensive stand-alone description of each a segment occurs within a given Syntax diagram, thediagram points to the stand-alone description of the segment inthis section for further of UsersnChapter 2, SPL Statements, presents all the detailed Syntax dia-grams and explanations forSPLstatements. You can use stored pro-cedures to perform any function you can perform inSQL as well asto expand what you can accomplish withSQL alone. You write astored procedure usingSPL andSQLstatements. For task-orientedinformation about using stored procedures, see theInformix Guide toSQL: Index is a combined index for the manuals in theSQL page reference in the index ends with a code that identifies themanual in which the page appears.

9 The same index also appears intheInformix Guide to SQL: Reference and theInformix Guide to following items in theSQL manual series are an integral part of theInformix Guide toSQL: Syntax even though they do not appear physically inthis manual:nA description of thestores7 database appears in Appendix A of theInformix Guide to SQL: Reference. This appendix describes thestructure and contents of the demonstration database that is installedwith the Informix database server products. All the manuals in theSQL manual series use this database for their of terms that are used in theSQL manual series appear inthe Glossary of theInformix Guide to SQL: messages that the database server issues to reflect errors in the executionofSQL statements are described in the section onSQL error codes in theInformix Error Messages of UsersThis manual is written for people who use Informix products andSQL on aregular basis.

10 The primary audience for this manual consists ofSQL devel-opers and database administrators. The secondary audience consists of endusers and anyone else who needs to know the Syntax ofSQL Guide to SQL: SyntaxSoftware DependenciesSoftware DependenciesYou must have the following Informix software to enter and executeSQL andSPL statements:nAnINFORMIX-OnLineDynamicServ er database server or anINFORMIX-SE database database server must be installed either on your computer or onanother computer to which your computer is connected over an Informix application development tool, such asINFORMIX-4GL; an SQLapplication programming interface (API),such asINFORMIX-ESQL/C; or theDB-Access database access utility,which is shipped as part of your database application development tool, theSQL API, orDB-Access enablesyou to compose queries, send them to the database server, and viewthe results that the database server can useDB-Access to try out many of theSQL statementsdescribed in this manual.