• SQL Guide
•  Database concepts
  •  Relational databases
    • Tables, rows, and columns
    • Relating data in different tables
  • Client-server architecture
  • Multi-user capability
  • Transactions
  •  Transaction logging and recovery
    • Background
  • Summary
•  Getting started with SQL
  • Tables, rows, and columns
  • SQL
  • The mathematical origins of SQL
  •  Creating tables with related data
    • Table aliases
    • Subqueries
  •  Which formats are used for each data type
    • BLOBs (or binary data types)
    • NULL IS NOT NULL (or “how to say ‘none of the above’ in SQL”)
    • NOT NULL
    • Expressions and casts
    • Row value constructors
  • More about transactions
  • Summary
  • Where to find additional information about SQL
•  SQL extensions
  •  Stored procedures
    • Stored procedures: SQL
    • Stored procedures: External
  • Functions
  •  Triggers
    • Triggers: Principles of operation
    • Creating and modifying triggers
    • Triggers and procedures
    • Triggers and transactions
    • Trigger privileges and security
    • Raising errors from inside triggers
    • Obtaining trigger information
    • Trigger parameter settings
    • Trigger example
  • Sequences
  •  Events
    • Using events: Example 1
    • Using events: Example 2
    • Using events: Example 3
•  Using SQL for database administration
  •  Using solidDB® SQL syntax
    • solidDB® SQL data types
    • solidDB® ADMIN COMMANDs
    • Scalar functions
  •  Managing user privileges and roles
    • User privileges
    • User roles
    • Creating users
    • Deleting users
    • Changing a password
    • Creating roles
    • Deleting roles
    • Granting privileges to a user or a role
    • Granting privileges to a user by giving the user a role
    • Revoking privileges from a user or a role
    • Revoking privileges by revoking the role of a user
    • Granting administrator privileges to a user
    • Changing administrator’s user name and password
    • DDL privileges
  •  Managing tables
    • Accessing system tables
    • Creating tables
    • Removing tables
    • Adding columns to a table
    • Deleting columns from a table
    • Viewing table names and definitions
  •  Managing indexes
    • Primary key indexes
    • Secondary key indexes
    • Protection against duplicate indexes
    • Creating an index on a table
    • Creating a unique index on a table
    • Deleting an index
  •  Referential integrity
    • Primary keys and candidate keys
    • Foreign keys
    • Referential actions
    • Dynamic constraint management
  •  Managing database objects
    • Catalogs
    • Schemas
    • Uniquely identifying objects within catalogs and schemas
    • Creating a catalog
    • Setting a catalog and schema context
    • Deleting a catalog
    • Creating a schema
    • Deleting a schema
•  Managing transactions
  • Defining read-only and read-write transactions
  •  Concurrency control and locking
    • PESSIMISTIC vs. OPTIMISTIC concurrency control
    • Locks and lock modes
    • Setting concurrency control
  •  Choosing transaction durability level
    • Setting transaction durability level
•  Diagnostics and troubleshooting for SQL
  •  Observing performance
    • SQL Info facility
    • EXPLAIN PLAN FOR statement
  •  Tracing facilities for stored procedures and triggers
    • User-definable trace output from procedure code
    • Procedure execution trace
  •  Measuring and improving performance of START AFTER COMMIT statements
    • Tuning performance of START AFTER COMMIT statements
    • Analyzing failures in START AFTER COMMIT statements
•  Tuning performance with SQL
  •  Tuning SQL statements and applications
    • Evaluating application performance
    • Using stored procedure language
    • Executing prepared statements (prepare once, execute many)
  • Optimizing single-table SQL queries
  •  Using indexes to improve query performance
    • Full table scan
    • Concatenated indexes
  • Waiting on events
  •  Optimizing batch inserts and updates
    • Increasing speed of batch inserts and updates
  • Using optimizer hints
  • Diagnosing poor performance
•  solidDB® SQL statements
  • ADMIN COMMAND
  • ADMIN EVENT
  • ALTER SEQUENCE
  •  ALTER TABLE
    • ALTER TABLE ... SET HISTORY COLUMNS
    • ALTER TABLE ... SET SYNCHISTORY
  • ALTER TRIGGER
  •  ALTER USER
    • ALTER USER (replica)
  •  CALL
    • Access rights for remote stored procedure calls
  • COMMIT WORK
  • CREATE CACHE SEGMENT
  • CREATE CATALOG
  • CREATE EVENT
  • CREATE FUNCTION
  • CREATE FUNCTION (external)
  • CREATE INDEX
  •  CREATE PROCEDURE
    • Usage
    • parameter_modes
    • prepare_statement
    • execute_statement
    • fetch_statement
    • post_statement
    • wait_register_statement
    • wait_event_statement
    • control_statement
    • writetrace_statement
    • exec_direct_statement
    • Procedure stack functions
    • Dynamic cursor names
    • Examples of CREATE PROCEDURE statement
  • CREATE PROCEDURE (external)
  • CREATE [OR REPLACE] PUBLICATION
  • CREATE ROLE
  • CREATE SCHEMA
  • CREATE SEQUENCE
  • CREATE SYNC BOOKMARK
  • CREATE TABLE
  •  CREATE TRIGGER
    • trigger_name
    • BEFORE | AFTER clause
    • INSERT | UPDATE | DELETE clause
    • table_name
    • trigger_body
    • REFERENCING clause
    • Triggers usage notes and restrictions
  • CREATE USER
  • CREATE VIEW
  • DELETE
  • DESCRIBE
  • DROP CACHE SEGMENT
  • DROP CATALOG
  • DROP EVENT
  • DROP FUNCTION
  • DROP INDEX
  • DROP MASTER
  • DROP PROCEDURE
  • DROP PUBLICATION
  • DROP PUBLICATION REGISTRATION
  • DROP REPLICA
  • DROP ROLE
  • DROP SCHEMA
  • DROP SEQUENCE
  • DROP SUBSCRIPTION
  • DROP SYNC BOOKMARK
  • DROP TABLE
  • DROP TRIGGER
  • DROP USER
  • DROP VIEW
  • EXPLAIN PLAN FOR
  • EXPORT SUBSCRIPTION
  • EXPORT SUBSCRIPTION TO REPLICA
  • GRANT
  • GRANT REFRESH
  •  HINT
    • Hints supported by solidDB®
  • IMPORT
  • INSERT
  • LIST
  • LOCK TABLE
  •  MESSAGE
    • MESSAGE APPEND
    • MESSAGE BEGIN
    • MESSAGE DELETE
    • MESSAGE DELETE CURRENT TRANSACTION
    • MESSAGE END
    • MESSAGE EXECUTE
    • MESSAGE FORWARD
    • MESSAGE FROM REPLICA DELETE
    • MESSAGE FROM REPLICA EXECUTE
    • MESSAGE FROM REPLICA RESTART
    • MESSAGE GET REPLY
  • POST EVENT
  • REFRESH
  • REGISTER EVENT
  • REVOKE
  • REVOKE REFRESH
  • ROLLBACK WORK
  • SAVE
  • SAVE PROPERTY
  • SELECT
  •  SET
    • Differences between SET and SET TRANSACTION
    • SET (read/write level)
    • SET CATALOG
    • SET DURABILITY
    • SET ISOLATION LEVEL
    • SET SAFENESS
    • SET SCHEMA
    • SET SEQUENCE
    • SET SQL
    • SET STATEMENT MAXTIME
    • SET SYNC
    • SET TIMEOUT
  •  SET TRANSACTION
    • Differences between SET and SET TRANSACTION
    • SET TRANSACTION (read/write level)
    • SET TRANSACTION DURABILITY
    • SET TRANSACTION ISOLATION LEVEL
    • SET TRANSACTION SAFENESS
  • START AFTER COMMIT
  • TRUNCATE TABLE
  • UNLOCK TABLE
  • UNREGISTER EVENT
  • UPDATE
  • WAIT EVENT
  •  Common clauses
    • check_condition
    • data_type
    • expression
    • query_specification
    • search_condition
    • table_reference
    • Pseudo columns in SELECT statement
    • Date and time literals
    • Wildcard characters
    • Using SQL wildcards
    • Wildcard characters as literals
•  Functions
  • String functions
  • Numeric functions
  • Date time functions
  • System functions
  • Miscellaneous functions
  •  Advanced replication functions
    • GET_PARAM() function
    • PUT_PARAM() function
  • Trigger functions
•  Data types
  • Character data types
  • Numeric data types
  • Binary data types
  • Date data type
  • Time data type
  • Timestamp data type
  • Smallest possible non-zero numbers
  • BLOBs and CLOBs
• Reserved words
•  Database system tables and system views
  •  System tables
    • SQL_LANGUAGES
    • SYS_ATTAUTH
    • SYS_AUDIT_TRAIL
    • SYS_BACKGROUNDJOB_INFO
    • SYS_BLOBS
    • SYS_CACHESEGMENT_CONDITIONS
    • SYS_CACHESEGMENTS
    • SYS_CARDINAL
    • SYS_CATALOGS
    • SYS_CHECKSTRINGS
    • SYS_COLUMNS
    • SYS_COLUMNS_AUX
    • SYS_DL_REPLICA_CONFIG
    • SYS_DL_REPLICA_DEFAULT
    • SYS_EVENTS
    • SYS_FEDT_DB_PARTITION
    • SYS_FEDT_TABLE_PARTITION
    • SYS_FORKEYPARTS
    • SYS_FORKEYS
    • SYS_HOTSTANDBY
    • SYS_INFO
    • SYS_KEYPARTS
    • SYS_KEYS
    • SYS_LOGPOS
    • SYS_PROCEDURES
    • SYS_PROCEDURE_COLUMNS
    • SYS_PROPERTIES
    • SYS_RELAUTH
    • SYS_SCHEMAS
    • SYS_SEQUENCES
    • SYS_SYNC_REPLICA_PROPERTIES
    • SYS_SYNONYM
    • SYS_TABLEMODES
    • SYS_TABLES
    • SYS_TRIGGERS
    • SYS_TYPES
    • SYS_UROLE
    • SYS_USERS
    • SYS_VIEWS
  •  System tables for data synchronization
    • SYS_BULLETIN_BOARD
    • SYS_PUBLICATION_ARGS
    • SYS_PUBLICATION_REPLICA_ARGS
    • SYS_PUBLICATION_REPLICA_STMTARGS
    • SYS_PUBLICATION_REPLICA_STMTS
    • SYS_PUBLICATION_STMTARGS
    • SYS_PUBLICATION_STMTS
    • SYS_PUBLICATIONS
    • SYS_PUBLICATIONS_REPLICA
    • SYS_SYNC_BOOKMARKS
    • SYS_SYNC_HISTORY_COLUMNS
    • SYS_SYNC_INFO
    • SYS_SYNC_MASTER_MSGINFO
    • SYS_SYNC_MASTER_RECEIVED_BLOB_REFS
    • SYS_SYNC_MASTER_RECEIVED_MSGPARTS
    • SYS_SYNC_MASTER_RECEIVED_MSGS
    • SYS_SYNC_MASTER_STORED_BLOB_REFS
    • SYS_SYNC_MASTER_STORED_MSGPARTS
    • SYS_SYNC_MASTER_STORED_MSGS
    • SYS_SYNC_MASTER_SUBSC_REQ
    • SYS_SYNC_MASTER_VERSIONS
    • SYS_SYNC_MASTERS
    • SYS_SYNC_RECEIVED_BLOB_ARGS
    • SYS_SYNC_RECEIVED_STMTS
    • SYS_SYNC_REPLICA_MSGINFO
    • SYS_SYNC_REPLICA_RECEIVED_BLOB_REFS
    • SYS_SYNC_REPLICA_RECEIVED_MSGPARTS
    • SYS_SYNC_REPLICA_RECEIVED_MSGS
    • SYS_SYNC_REPLICA_STORED_BLOB_REFS
    • SYS_SYNC_REPLICA_STORED_MSGS
    • SYS_SYNC_REPLICA_STORED_MSGPARTS
    • SYS_SYNC_REPLICA_VERSIONS
    • SYS_SYNC_REPLICAS
    • SYS_SYNC_SAVED_BLOB_ARGS
    • SYS_SYNC_SAVED_STMTS
    • SYS_SYNC_TRX_PROPERTIES
    • SYS_SYNC_USERMAPS
    • SYS_SYNC_USERS
  •  System views
    • COLUMNS
    • SERVER_INFO
    • TABLES
    • USERS
  •  Synchronization-related views
    • SYNC_FAILED_MESSAGES
    • SYNC_FAILED_MASTER_MESSAGES
    • SYNC_ACTIVE_MESSAGES
    • SYNC_ACTIVE_MASTER_MESSAGES
•  Database virtual tables
  • SYS_LOG
•  System stored procedures
  •  System stored procedures for advanced replication
    • SYNC_SETUP_CATALOG
    • SYNC_REGISTER_REPLICA
    • SYNC_UNREGISTER_REPLICA
    • SYNC_REGISTER_PUBLICATION
    • SYNC_UNREGISTER_PUBLICATION
    • SYNC_SHOW_SUBSCRIPTIONS
    • SYNC_SHOW_REPLICA_SUBSCRIPTIONS
    • SYNC_DELETE_MESSAGES
    • SYNC_DELETE_REPLICA_MESSAGES
    • SYNC_GET_CATALOG_SYNC_NODE_NAME_AND_MODE
  •  Miscellaneous stored procedures
    • SYS_GETBACKGROUNDJOB_INFO
    • SYS_HSBDSTATE_WAIT
    • SYS_TF1_WAIT
•  System events
  • Miscellaneous events
  • Conditions or warnings that cause SYS_EVENT_ERROR
  • Conditions or warnings that cause SYS_EVENT_MESSAGES