CONTENTS

  Title Page

  Copyright Page

  Send Us Your Comments

  Preface

  Technical Changes and New Features

  1      Getting Started with Interactive SQL
    1.1 Creating a Sample Database
    1.2 Invoking Interactive SQL
    1.3 Using HELP
    1.4 Typing SQL Statements
    1.5 Attaching to a Database
    1.6 Correcting Mistakes
    1.7 Displaying Information About a Database
      1.7.1      Summarizing Database Structures in a Diagram
    1.8 Detaching from a Database
    1.9 Making Interactive SQL Easier to Use
      1.9.1      Displaying Examples in 132-Character Width
      1.9.2      Defining a Logical Name for the Database
      1.9.3      Using SQL Command Procedures
      1.9.4      Controlling Session Output
      1.9.5      Using Editors with SQL
      1.9.6      Tailoring the Interactive SQL Environment

  2      Retrieving Data
    2.1 Using Examples in This Chapter
    2.2 Retrieving Data from a Table or View
      2.2.1      Using Alternative Column Names
    2.3 Displaying Value Expressions and Literal Strings
    2.4 Displaying Concatenated Strings
    2.5 Eliminating Duplicate Rows (DISTINCT)
      2.5.1      Using the ALL Keyword to Include All Rows Explicitly
    2.6 Retrieving Rows in Sorted Order (ORDER BY)
    2.7 Retrieving a Limited Number of Rows (LIMIT TO)
    2.8 Retrieving a Subset of Rows (WHERE)
      2.8.1      Understanding Predicates
      2.8.2      Using Comparison Predicates
      2.8.3      Using the Range Test Predicate ([NOT] BETWEEN)
      2.8.4      Using the Set Membership Predicate ([NOT] IN)
      2.8.5      Using String Comparison Predicates
      2.8.6      Using the Pattern Matching Predicate ([NOT] LIKE)
      2.8.7      Using the Null Value Predicate (IS [NOT] NULL)
    2.9 Using Conditional and Boolean Operators
      2.9.1      Evaluating Search Conditions
    2.10 Summary Queries
      2.10.1     Performing Calculations on Columns
      2.10.2     Computing a Total (SUM)
      2.10.3     Computing an Average (AVG)
      2.10.4     Finding Minimum and Maximum Values (MIN and MAX)
      2.10.5     Counting Rows (COUNT)
      2.10.6     When Functions Return Empty Rows
    2.11 Built-in Functions
      2.11.1     Converting Data Types (CAST)
      2.11.2     Returning String Length (CHARACTER_LENGTH and OCTET_LENGTH)
      2.11.3     Displaying a Substring (SUBSTRING)
      2.11.4     Changing Character Case (UPPER and LOWER)
      2.11.5     Translating Character Strings (TRANSLATE)
    2.12 Using Column Functions on Groups of Rows (GROUP BY)
      2.12.1     Using a Search Condition to Limit Groups (HAVING)
    2.13 Retrieving Data from Multiple Tables (JOINS)
      2.13.1     The Cartesian Product of Two Tables
      2.13.2     Joining Two Tables
      2.13.3     Using Correlation Names
      2.13.4     Using Explicit Join Syntax
      2.13.5     Combining a Join Condition with a Regular Condition
      2.13.6     Joining More than Two Tables
      2.13.7     Using a Table As a ``Bridge'' Between Two Other Tables
      2.13.8     Joining a Table with Itself to Answer Reflexive Questions
    2.14 Testing SQL Statements Before Accessing the Database
    2.15 Summary of the SELECT Statement

  3      Inserting, Updating, and Deleting Data
    3.1 Transactions
      3.1.1      Starting a Transaction
      3.1.2      Ending a Transaction
    3.2 Inserting New Rows
      3.2.1      Default Column Values
      3.2.2      Using the INSERT Statement to Copy Data from Another Table
      3.2.3      Inserting the Results of a Calculated Column Expression
    3.3 Updating Rows
      3.3.1      Changing Data Using Views
    3.4 Conversion of Data Type in INSERT and UPDATE Statements
    3.5 Deleting Rows
    3.6 Using Special SQL Keywords
      3.6.1      Using the CURRENT_USER Keyword
      3.6.2      Using the CURRENT_TIMESTAMP Keyword
    3.7 The Effect of Constraints on Write Operations
      3.7.1      Displaying Defined Constraints
    3.8 Write Operations that Activate Triggers

  4      Advanced Data Manipulation
    4.1 Using Subqueries to Answer Complex Questions
      4.1.1      Developing Subqueries
      4.1.2      Subqueries and Joins
      4.1.3      General Format for Using Subqueries
      4.1.4      Building a Subquery Structure
      4.1.5      Using Different Values with Each Evaluation of the Outer Query
      4.1.6      Checking for the Existence of Rows
      4.1.7      Using Several Levels of Subqueries
      4.1.8      Using a Quantified Predicate to Compare Column Values with a Set of Values
      4.1.9      Using the ORDER BY and LIMIT TO Clauses in Subqueries
    4.2 UNION: Combining the Result of SELECT Statements
      4.2.1      Using the UNION Operator with the ALL Qualifier
      4.2.2      Using the UNION Operator Without the ALL Qualifier
    4.3 Using Outer Joins
    4.4 Derived Tables
    4.5 Retrieving Data from System Tables
    4.6 Creating Views
      4.6.1      Simple and Complex Views

  5      Creating a DEC Rdb Database
    5.1 Designing a Relational Database
      5.1.1      Analyzing the Requirements for the Database
      5.1.2      Developing the Conceptual Design
      5.1.3      Developing the Logical Design
      5.1.4      Developing the Physical Design
      5.1.5      Implementing the Database
    5.2 Creating a Database Using SQL
      5.2.1      Creating a Repository and Directories
      5.2.2      Defining Logical Names for the Database Files
      5.2.3      Creating the Physical Database
      5.2.4      Creating Domains
      5.2.5      Creating Tables
      5.2.6      Assigning Tables and Indexes to Storage Areas
      5.2.7      Creating More Sorted Indexes
      5.2.8      Creating Triggers
    5.3 Deleting a Database

  6      Using Multischema Databases
    6.1 The Multischema Sample Database
    6.2 Multischema Database Structure
    6.3 Accessing a Multischema Database
      6.3.1      Attaching to a Multischema Database
      6.3.2      Detaching from the Database
    6.4 Displaying Multischema Database Information
      6.4.1      Displaying Specific Schema Elements
      6.4.2      Using the SHOW Command with a Full Element Name
      6.4.3      Using the SET Statement to Access a Specific Catalog and Schema
      6.4.4      Setting a New Default Schema
    6.5 Querying a Multischema Database with SQL
      6.5.1      Joining Tables in a Multischema Database
      6.5.2      Using an SQL Command File to Set the Default Catalog and Schema
    6.6 Multischema Access Modes
      6.6.1      Multischema Database Element Naming
      6.6.2      Assigning Stored Names
      6.6.3      Matching SQL Names to Stored Names
        6.6.3.1      Using the SHOW Command to Match SQL Names to Stored Names
        6.6.3.2      Using the System Tables to Match SQL Names to Stored Names

  7      Using Date-Time Data Types
    7.1 Date-Time Data Types and Functions
      7.1.1      The DATE VMS Data Type
      7.1.2      The DATE ANSI Data Type
      7.1.3      The TIMESTAMP Data Type
      7.1.4      The TIME Data Type
      7.1.5      The INTERVAL Data Type
      7.1.6      Using the INTERVAL Data Type
    7.2 The Date-Time Data Type Literal Formats
    7.3 Using the EXTRACT Function
    7.4 Rules for Performing Date-Time Arithmetic

  8      SQL Programming Overview
    8.1 Using SQL with a High-Level Programming Language
      8.1.1      Using the SQL Module Language
      8.1.2      Using Precompiled SQL
    8.2 Steps for Creating Applications Using Host Programs and SQL
    8.3 Developing Host Language Programs
      8.3.1      Creating a Prototype
      8.3.2      Prototyping with a Command File
      8.3.3      Prototyping with a Session Log
      8.3.4      Converting a Prototype to an Application Program