User's Manual Contents

Chapter 1 Introduction

Documentation Objectives
Prerequisites
Organization of this Manual
Related Mimer SQL Publications
Suggestions for Further Reading
Acronyms, Terms and Trademarks

Chapter 2 Basic Concepts of Mimer SQL

The Mimer SQL Relational Database
The Data Dictionary
Databanks
Idents
Schemas
Tables
Base Tables and Views
Unique Constraints and Indexes
Routines - Functions and Procedures
Triggers
Modules
Synonyms
Shadows
Sequences
Data Integrity
Domains
Foreign Keys - Referential Integrity
Check Conditions
Check Options in View Definitions
Access Rights and Privileges
System Privileges
Object Privileges
Access Privileges
About Privileges
Mimer SQL Statements
Data Definition Statements
Access Control Statements
Data Manipulation Statements
Connection Statements
Transaction Control Statements
Database Administration Statements

Chapter 3 Managing Database Connections

Connecting to a Database
Changing Connections
Disconnecting
Program Idents - ENTER and LEAVE

Chapter 4 Retrieving Data

Simple Retrieval
Examples of Simple Retrieval
Result Order
Quick Select
Table Names
Setting Column Labels
Eliminating Duplicate Values
Selecting Specific Rows
Comparison Conditions and WHERE
Pattern Conditions
More about Searching for Character Strings
Set Conditions
Retrieving Computed Values
Labels and Computed Values
Constant Values
Padding Concatenated Strings
Using Set Functions
About Set Functions
Example of Set Functions
More Set Functions Examples
Grouped Set Functions - the GROUP BY Clause
Restrictions when Using GROUP BY
Column References
Null Values
Selecting Groups - the HAVING Clause
Ordering the Result Table
Ordering by More than One Column
Ordering by Set Function or Computed Value
Using Scalar Functions
Examples of Scalar Functions
Using CASE Expression
Case Expression Examples
Using CAST Specification
Datetime Arithmetic and Functions
About Intervals
Extracting Values
DAYOFWEEK
Retrieving Data from More than One Table
The Join Condition
Simple Joins
Outer Joins
Nested Selects
Correlation Names
Retrieving Data Using EXISTS and NOT EXISTS
Retrieval with ALL, ANY, SOME
Union Queries
Handling NULL Values
Searching for NULL
Null values in ALL, ANY, IN and EXISTS Queries
Conceptual Description of the Selection Process
Query Used
Selection Process

Chapter 5 Data Manipulation

Access Privileges
Inserting Data
Inserting Explicit Values
Inserting Results of Expressions
Inserting with a Subselect
Inserting Sequence Values
Inserting NULL Values
Updating Tables
Deleting Rows from Tables
Calling Procedures
Examples of Calling Procedures
Updatable Views

Chapter 6 Managing Transactions

Transaction Principles
Transaction Phases
Logging Transactions
Logging Options
Handling Transactions
SQL Statement Restrictions in Transactions
Transaction Handling in BSQL
Optimizing Transactions
Consistency Within a Transaction
Exception Diagnostics Within Transactions
Default Transaction Options

Chapter 7 Defining a Database

Creating Idents and Schemas
Ident Names
Passwords
Schemas
Creating Idents and Schemas, Examples
Creating Databanks
Create Databank Statement
Creating Tables
Create Table Statement
Column Definitions
The Primary Key Constraint
Unique Constraints
Foreign Keys - Referential Constraints
Check Constraints
Creating Sequences
Examples of Sequences
Creating Domains
Create Domain Statement
Domains with a Default Value
Domains with a Check Clause
Creating Modules, Functions, Procedures and Triggers
Creating Views
Creating a View
Check Options
Creating Secondary Indexes
Examples of Secondary Index
Sorting Indexes
Creating Synonyms
Synonym Examples
Commenting Objects
Comment Example
Altering Databanks, Tables and Idents
Altering a Databank
Altering Tables
Altering Idents
Objects Which May Not Be Altered
Dropping Objects from the Database
Dropping Databanks and Tables
Dropping Sequences
Dropping Domains
Dropping Idents
Dropping Functions, Modules, Procedures and Triggers

Chapter 8 Defining Privileges

Granting and Revoking Privileges
Ident Structure
SYSADM Privileges
About System Utilities
Recommendations for Ident Structure
Granting Privileges
Granting System Privileges
Examples
Granting Object Privileges
Granting Access Privileges
Revoking Privileges
Revoking System Privileges
Revoking Object Privileges
Revoking Access Privileges
Recursive Effects of Revoking Privileges

Chapter 9 Mimer BSQL

Other Windows SQL Tools
Running BSQL
Running BSQL from a Batch Job
Running BSQL via a Terminal
Installing the Example Database
BSQL Commands
CLOSE
DESCRIBE
EXIT
LIST
LOAD
LOG
READ INPUT
SET ECHO
SET LINECOUNT
SET LINESPACE
SET LINEWIDTH
SET LOG
SET MESSAGE
SET OUTPUT
SET PAGELENGTH
SET PAGEWIDTH
SHOW SETTINGS
UNLOAD
WHENEVER

Chapter 10 Variables in BSQL

Writing Host Variables in SQL
Scope of Host Variables
Using Host Variables
Including Indicator Variables

Chapter 11 Error Handling

Errors in BSQL
Semantic Errors
Syntax Errors
Error Messages

Appendix A Example Database

Tables in the Database
Table Descriptions
Table Contents
Hotel Table
ROOMSTATUS Table
ROOMTYPES Table
ROOMS Table
ROOM_PRICES Table
CHARGES Table
BOOK_GUEST Table
BILL Table
WAKE_UP Table
EXCHANGE_RATE Table
CREATE Statements
CREATE DATABANK
CREATE DOMAIN
CREATE TABLE
CREATE Procedures
CREATE View
CREATE Trigger

Index