JDB SQL Reference

About This Document

• Documentation Web Site
• How to Print the Document
• Documentation Conventions
• Contact Us!

1. Introduction to JDB

• Using JDB
• JDB Executables and Utilities
• Unsupported Features
• See Also

2. Introduction to Databases

• Structure of a Relational Database
• Tables
• Columns
• Entering Data
• Rows
• Primary Keys
• Foreign Keys
• Naming Database Tables and Columns
• Designing Your Database

3. Introduction to SQL

• SQL Statements
• SELECT Statement
• WHERE Clause
• UPDATE Statement
• WHERE Clause
• INSERT Statement
• DELETE Statement
• SQL Concepts
• Multi-Table Queries
• Correlation Names
• Aggregate Functions
• Transactions
• Implementation in a JDB Database
• Executing SQL
• Using JISQL
• Using JPL
• See Also

4. Database Elements

• Naming Conventions
• Databases
• Identifiers
• Data Types
• System Tables
• Journal Files
• Configuration
• Specifying an Editor
• Error Messages
• Connecting to a JDB Database

5. Using JISQL

• Starting JISQL
• JDB Database Connections
• To connect to an existing database:
• To connect to a new database:
• To disconnect from the current database:
• Executing Operating System Commands from JISQL
• To execute an operating system command from JISQL:
• Creating a New Database
• Creating Database Tables
• To create a database table:
• Defining Columns in a Database Table
• To add a new column to the table you are creating:
• To change or delete a column's definition:
• To change the order of columns in the table:
• Defining Keys for a Database Table
• Primary Key and Unique Keys
• To add a new primary key or unique key:
• To modify an existing primary key or unique key:
• To delete an existing primary or unique key:
• Foreign Keys
• To add a new foreign key:
• To modify an existing foreign key:
• To delete an existing foreign key:
• Maintaining a Database
• Displaying Database and Table Definitions
• Dropping Tables
• Dropping a Database
• Running SQL Interactively
• Writing SQL Scripts
• To enter a SQL script:
• To edit a SQL script:
• To save SQL script displayed in the scripting area:
• To clear the scripting area:
• Script Format and Syntax
• JISQL Macro Commands
• Executing SQL Scripts
• Output and Execution Options
• To stop execution of a SQL script:
• Capturing and Displaying Query Results
• To save the output to a file:
• To display select sets on the screen:
• Creating and Viewing the Log File
• How to Begin a Log Session
• To view the information stored in the log file for the current session:
• To end a log session:
• Sample Log File

6. SQL Reference

• Reference Organization
• SQL Statements
• SQL Clauses and Keywords
• SQL Concepts
• Notation Conventions
• Aggregate Functions
• BETWEEN Predicate
• CREATE DATABASE Statement
Creating the First Database 9
• CREATE TABLE Statement
Specify Primary and Foreign Keys 12

Maximum Row Length 12

• Data Types
Numeric Columns 16
• DELETE Statement
• DROP DATABASE Statement
• DROP TABLE Statement
• GROUP BY Clause
• HAVING Clause
• INSERT Statement
Inserting Rows Using a Column List 26

Inverting Rows Containing a Null Value 26

Inserting Rows Using a Subquery 26

• Joins
Equi-joins 27

Natural Joins 28

Multiple Table Joins 29

Non equi-joins 30

Self-joins 31

• LIKE Predicate
• Null Values
Using NULL in
an INSERT Statement 35

Using NULL in
a SELECT Statement 35

Using NULL in
an UPDATE Statement 36

• Operators
Arithmetic Operators 37

Comparison Operators 38

Logical Operators 39

• ORDER BY Clause
• SELECT Statement
Specifying a Select List 45

Specifying a WHERE Clause 46

Obtaining Unique Entries 46

Obtaining Data from Multiple Tables 47

• Subqueries
Nested Subqueries 50
• UPDATE Statement
• WHERE Clause
Search Conditions 54

Specifying Joins 57

• SQL Syntax Summary

A. JDB Utilities

• isql
Starting ISQL 2

Creating a New Database 2

Executing SQL Statements 3

Executing ISQL Statements 3

Exiting ISQL 4

• jdbroll
• mksql
• tbldata

B. JDB-Specific Error Messages

• Error Message Listing

C. Keywords in JDB

D. Videobiz Database

• Videobiz Schema

Index