Informix Guide to SQL: Tutorial

Informix Guide to SQL Tutorial Version 7.2 April 1996 Part No. 000-7883A®Published by INFORMIX Press Informix Software, Inc. 4100 Bohannon Drive Menlo Park, CA 94025 The following are worldwide trademarks of Informix Software, Inc., or its subsidiaries, registered in the United States of America as indicated by “®,” and in numerous other countries worldwide: INFORMIX ; NewEra ; ViewPoint ; C-ISAM -OnLine Dynamic Server ; INFORMIX -SuperView (SuperView technology Patent Pending) The following are worldwide trademarks of the indicated owners or their subsidiaries, registered in the Adobe Systems Incorporated: PostScript International Business Machines Corporation: DRDA ; IBM Microsoft Corporation: Microsoft ; MS ; MS-DOS ; CodeView ; MS Windows ; Windows ; Windows NT ; ODBC ; Visual Basic ; Visual C++ Microsoft Memory Management Product: HIMEM.SYS (“DOS” as used herein refers to MS-DOS and/or PC-DOS operating systems.) X/OpenCompany Ltd.: UNIX ; X/Open Some of the products or services mentioned in this document are provided by companies other than Informix. These products or services are identified by the trademark or servicemark of the appropriate company. If you have a question about one of those products or services, please call the company in question directly. Documentation Team: Diana Chase, Geeta Karmarkar, Tom Noronha, Kami Shahi Copyright © 1981-1996 by Informix Software, Inc. All rights reserved. No part of this work covered by the copyright hereon may be reproduced or used in any form or by any means—graphic, electronic, or mechanical, including photocopying, recording, taping, or information storage and retrieval systems—without permission of the publisher. To the extent that this software allows the user to store, display, and otherwise manipulate various forms of data, including, without limitation, multimedia content such as photographs, movies, music and other binary large objects (blobs), use of any single blob may potentially infringe upon numerous different third-party intellectual and/or proprietary rights. It is the user's responsibility to avoid infringements of any such third- party rights. RESTRICTED RIGHTS LEGEND Software and accompanying materials acquired with United States Federal Government funds or intended for use within or for any United States federal agency are provided with “Restricted Rights” as defined in DFARS 252.227-7013(c)(1)(ii) or FAR 52.227-19. ii Informix Guide to SQL: TutorialTable of Contents Table of Contents Introduction About This Manual.................. 3 Organization of This Manual............. 3 Types of Users 5 Software Dependencies............... 5 Demonstration Database .............. 6 New Features of This Product 9 Conventions .................... 14 Typographical Conventions 15 Icon Conventions................. 15 Sample-Code Conventions 18 Terminology Conventions 19 Additional Documentation ............... 19 Printed Documentation 20 On-Line Documentation .............. 21 Related Reading 2 Compliance with Industry Standards ........... 23 Informix Welcomes Your Comments............ 24 Section I Using Basic SQL Chapter 1 Informix Databases The Data Illustration of a Data Model ...........1-3 Concurrent Use and Security.............1-8 Centralized Management ..............1-8 Important Database Terms ...............1-10 The Relational Model1-10 Structured Query Language1-14 Standard SQL ..................1-14Informix SQL and ANSI SQL ............ 1-15 ANSI-Compliant Databases............. 1-16 GLS Databases ................. 1-16 The Database Software ................ 1-16 The Applications 1-16 The Database Server 1-17 Interactive SQL 1-17 Reports and Forms 1-17 General Programming............... 1-18 Applications and Database Servers .......... 1-19 Summary..................... 1-19 Chapter 2 Composing Simple SELECT Statements Introducing the SELECT Statement............ 2-4 Some Basic Concepts 2-5 The Forms of SELECT 2-10 Special Data Types ................2-1 Single-Table SELECT Statements............. 2-12 Selecting All Columns and Rows ........... 2-12 Selecting Specific Columns 2-18 Using the WHERE Clause.............. 2-28 Creating a Comparison Condition 2-29 Expressions and Derived Values 2-46 Using Functions in SELECT Statements......... 2-52 Using Stored Procedures in SELECT Statements...... 2-64 Multiple-Table SELECT Statements............ 2-66 Creating a Cartesian Product............. 2-66 Creating a Join.................. 2-68 Some Query Shortcuts............... 2-75 Summary..................... 2-80 Chapter 3 Composing Advanced SELECT Statements Using the GROUP BY and HAVING Clauses ........ 3-4 Using the GROUP BY Clause ............ 3-4 Using the HAVING Clause ............. 3-8 Creating Advanced Joins ............... 3-10 Self-Joins ...................3-1 Outer Joins 3-19 Subqueries in SELECT Statements 3-29 Using ALL 3-30 Using ANY 3-31 Single-Valued Subqueries.............. 3-32 iv Informix Guide to SQL: TutorialCorrelated Subqueries ...............3-33 Using EXISTS ..................3-34 Set Operations ...................3-38 Union .....................3-39 Intersection3-47 Difference....................3-49 Summary3-51 Chapter 4 Modifying Data Statements That Modify Data ..............4-3 Deleting Rows ..................4-4 Deleting a Known Number of Rows...........4-5 Inserting Rows4-7 Updating Rows4-12 Database Privileges4-16 Displaying Table Privileges4-18 Data Integrity....................4-19 Entity Integrity4-19 Semantic Integrity.................4-20 Referential Integrity ................4-21 Object Modes and Violation Detection..........4-25 Interrupted Modifications ...............4-27 The Transaction..................4-28 Transaction Logging4-28 Specifying Transactions4-29 Backups and Logs4-30 Backing Up with INFORMIX-SE............4-30 Backing Up with INFORMIX-OnLine Dynamic Server ....4-31 Concurrency and Locks ................4-32 Data Replication...................4-33 INFORMIX-OnLine Dynamic Server Data Replication ....4-34 Summary .....................4-34 Chapter 5 Programming with SQL SQL in Programs5-4 SQL in SQL APIs .................5-4 SQL in Application Languages ............5-5 Static Embedding5-6 Dynamic Statements................5-6 Program Variables and Host Variables..........5-6 Table of Contents vCalling the Database Server .............. 5-9 The SQL Communications Area ........... 5-9 The SQLCODE Field ............... 5-12 The SQLERRD Array 5-13 The SQLWARN Array 5-13 The SQLSTATE Value 5-14 Retrieving Single Rows ................ 5-14 Data Type Conversion 5-16 Working with Null Data .............. 5-17 Dealing with Errors 5-18 Retrieving Multiple Rows ............... 5-20 Declaring a Cursor 5-21 Opening a Cursor 5-22 Fetching Rows.................. 5-22 Cursor Input Modes................ 5-24 The Active Set of a Cursor ............. 5-25 Using a Cursor: A Parts Explosion........... 5-27 Dynamic SQL ................... 5-30 Preparing a Statement............... 5-30 Executing Prepared SQL .............. 5-32 Dynamic Host Variables 5-34 Freeing Prepared Statements 5-34 Quick Execution................. 5-35 Embedding Data Definition Statements .......... 5-35 Embedding Grant and Revoke Privileges ........ 5-36 Summary..................... 5-38 Chapter 6 Modifying Data Through SQL Programs Using DELETE................... 6-3 Direct Deletions 6-4 Deleting with a Cursor............... 6-7 Using INSERT 6-9 Using an Insert Cursor 6-9 Rows of Constants ................ 6-12 An Insert Example 6-12 Using UPDATE 6-15 Using an Update Cursor .............. 6-15 Cleaning Up a Table 6-17 Summary..................... 6-18 vi Informix Guide to SQL: TutorialChapter 7 Programming for a Multiuser Environment Concurrency and Performance..............7-3 Locking and Integrity .................7-3 Locking and Performance ...............7-4 Concurrency Issues..................7-4 How Locks Work7-6 Kinds of Locks7-7 Lock Scope ...................7-7 The Duration of a Lock7-10 Locks While Modifying7-10 Setting the Isolation Level7-1 Comparing SET TRANSACTION with SET ISOLATION . . . 7-12 ANSI Read Uncommitted and Informix Dirty Read Isolation . . 7-13 ANSI Read Committed and Informix Committed Read Isolation . 7-14 Informix Cursor Stability Isolation ...........7-14 ANSI Serializable, ANSI Repeatable Read, and Informix Repeatable Read Isolation...............7-16 Controlling Data Modification with Access Modes .......7-17 Setting the Lock Mode.................7-18 Waiting for Locks7-18 Not W7-18 Waiting a Limited Time7-19 Handling a Deadlock................7-19 Handling External Deadlock .............7-20 Simple Concurrency7-20 Locking with Other Database Servers ...........7-21 Isolation While Reading...............7-22 Locking Updated Rows7-22 Hold Cursors ....................7-23 Summary .....................7-24 Section II Designing and Managing Databases Chapter 8 Building Your Data Model Why Build a Data Model ................8-3 Entity-Relationship Data-Model Overview ........8-4 Identifying and Defining Your Principal Data Objects ......8-5 Discovering Entities8-5 Defining the Relationships..............8-9 Identifying Attributes ...............8-17 Table of Contents viiDiagramming Your Data Objects............. 8-19 Translating E-R Data Objects into Relational Constructs..... 8-22 Rules for Defining Tables, Rows, and Columns ...... 8-23 Determining Keys for Tables 8-25 Resolving Your Relationships.............. 8-29 Normalizing Your Data Model 8-31 Summary..................... 8-36 Chapter 9 Implementing Your Data Model Defining the Domains ................ 9-3 Data Types................... 9-4 Null Values 9-25 Default Values.................. 9-25 Check Constraints 9-26 Creating the Database 9-26 Using CREATE DATABASE............. 9-27TE TABLE............... 9-30 Using Command Scripts .............. 9-32 Populating the Tables 9-33 Fragmenting Tables and Indexes 9-36 Creating a Fragmented Table 9-36 Fragmenting a New Table