Module 1: Introduction to SQL Relational Databases

• Definition of a relational database
• Components of a relational database table
• Forming relationships in a relational database
• Types of relationships
• Description of Structured Query Language: Data Definition Language, Data Manipulation Language, Data Control Language, and Transaction Control Language
• Characteristics of a Relational Database Management System (RDBMS)

Lab

Module 2: Overview of SQL/MX Architecture

• SQL/MX architecture
• SQL/MX system metadata
• User Metadata (UMD) tables
• User catalog and schemas
• SQL/MX user tables, objects, tables, indexes, views, constraints, triggers, object namespaces – object type, security model, process architecture, components, catalog manager, DDL operations, utilities
• NSM/web architecture

Lab

Module 3: Introduction to mxci

• SQL/MX Help Facilities
• Starting an mxci session
• mxci prompts and termination character
• SQL/MX identifiers
• Logical (ANSI) names
• Specifying ANSI
• Using logical names in an mxci session
• mxci:

  • SET NAMETYPE command (ANSI)
  • SET CATALOG command
  • SET SCHEMA command
• mxci cd command, Is commands
• mxci—LOG command

Lab

Module 4: Query Writing Process

• Overview of query execution
• Overview of query development process
• Analyzing the query objective, Generating the query, executing the query
• Verifying the results, assessing performance

Lab

Module 5: Retrieving Data from a Single Table

Module 6: Functions and Expressions

• Aggregate functions
• Character functions
• Datetime functions
• Mathematical functions
• Types of expressions
• Literal expressions
• Numeric expressions

Lab

Module 7: Retrieving Data from Multiple Tables

• Generating the following types of joins: CROSS, NATURAL, INNER, EQUI, LEFT, RIGHT, Self
• Correlation names
• Join with additional search conditions
• UNION operation

Lab

Module 8: Query Expressions

• Query expression: Definition, types, joined table, syntax
• Non-joined query expression table: VALUES statement, TABLE statement, SELECT query specification
• Simple table—SELECT expression
• Subquery: Definition, non-correlated, correlated, evaluation of a correlated subquery, classification, SELECT form of a subquery
• Predicates: Subquery, comparison, BETWEEN, IN, and EXISTS, and EXISTS examples
• Subqueries using the comparison, BETWEEN, and IN predicates
• Subquery key points

Lab

Module 9: Creating SQL/MX Objects

• Creating SQL/MX Objects
• SQL/MX Object Naming
• CREATE CATALOG Command—Syntax, REGISTER CATALOG Command—Syntax, UNREGISTER CATALOG Command—Syntax, Catalog Considerations
• CREATE SCHEMA Command—Syntax
• Rules for Naming SQL/MX Schema Subvolumes, Schema Considerations, Creating a User Schema
• Creating a SQL/MX Table—Topics
• Column Definitions, Column Name Rules, Character Sets, Default Value, ISO88591 Character Set Examples
• SYSTEM_DEFAULTS Table— NOT_NULL_CONSTRAINT_DROPPABLE_OPTION
• Constraints, Constraints Names, Table Constraints
• Specifying Physical Location and Name for the Underlying Guardian File
• Specifying a Clustering Key, Specifying a Clustering Key—STORE BY Clause, Terminology
• Clustering Key—No STORE BY Clause and No Primary Key Specified, Clustering Key—STORE BY PRIMARY KEY: Primary Key Specified As DROPPABLE
• Specifying Guardian File Attributes
• CREATE INDEX—Syntax, CREATE VIEW—Syntax, CREATE VIEW—Example, Considerations for Creating a View

Lab

Module 10: Inserting Data and Updating Statistics

• Methods for Loading Multiple Rows of Data
• Inserting Data into the Database, INSERT Statement—Syntax, Inserting a Single Row, Inserting Multiple Rows, INSERT Considerations
• SQL/MX Histogram Statistics, Statistics Tables, mxci UPDATE STATISTICS Utility, Examples of mxci UPDATE STATISTICS

Lab

Module 11: Modifying Data

• Maintaining Database Consistency
• Transaction Management Statements
• Explicit Transaction: User-Defined Transaction, INSERT, UPDATE, DELETE
• Implicit Transaction: System-Defined Transactions, SELECT, INSERT, UPDATE, DELETE
• Modifying Existing Data
• UPDATE Statement—Syntax, Updating a Single Row, Updating Multiple Rows, UPDATE Statement—Scalar Subquery, UPDATE Considerations
• Removing Data from the Database
• DELETE Statement—Syntax, Deleting Data, DELETE Considerations

Lab

Module 12: Access Options and Isolation Levels

• Concurrency Control and Contention
• Locking Considerations, Dirty Reads, Non-Repeatable Reads, Phantoms
• Access Options and Isolation Levels, READ UNCOMMITTED Access Option, READ COMMITTED Access Option, READ COMMITTED Considerations, SERIALIZABLE or REPEATABLE READ Access Option
• Lock Modes, Access Options and Lock Modes
• SET TRANSACTION Statement, SET TRANSACTION Statement—Example, Transaction Isolation-Level Rules
• DEADLOCK, Viewing Locks on a Table

Lab

Module 13: Management Functions

• SQL/MX Object Dependencies
• SQL Authorization ID
• Object Ownership and Security Rules
• Granting Privileges to Users—Example
• Altering SQL/MX Objects in a SQL/MX Database
• Authorization Requirements for Altering Database Object
• Altering TABLE or INDEX FILE Attributes, Adding Columns to a Table, Altering Considerations
• Removing SQL/MX Database Objects, Dropping SQL/MX Objects From a SQL/MX Database, Removing Your Database Objects
• Managing Data
• mxtool VERIFY Utility, mxtool VERIFY Utility—Security Considerations, mxtool VERIFY Utility—Syntax
• Performance, Monitoring Performance
• Using the EXPLAIN Function with a Prepared Query
• EXPLAIN statement with OPTIONS ‘f’
• NSM/web Connectivity Services, Visual Query Planner DISPLAY STATISTICS

Lab

Module 14: Advanced Topics

• Referential Integrity (RI)
• Trigger Definition
• Partitioning—Range Partitioning, Hash Partitioning
• Publish and Subscribe Services
• Rowsets
• Compound Statements
• SELECT statement—TRANSPOSE Clause, SAMPLE Clause
• Sequence Function

Module 15: MXDM

• Features and requirements of MXDM
• Installing and uninstalling MXDM
• Example screens