HPE NonStop SQL/MX Basics

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 exercise

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 exercise

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 cd Command, Is Commands
• mxci — LOG Command
• Lab exercise

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 exercise

Module 5: Retrieving Data from a Single Table

• Data Types, Character Data Types, Numeric Data Types-Exact and Approximate, Datetime Data Types, Interval Data Types
• INVOKE Command
• SELECT Statement-Clauses, Syntax, Select List
• SELECT-Select List, ALL or DISTINCT Rows, [ANY N] or [FIRST N]
• FROM and WHERE Clause
• Predicates
• Row-Value-Constructor
• Comparison Predicates-Syntax, Examples
• LIKE, BETWEEN, and IN Predicates
• Boolean Operators and Compound Predicates
• NULL Values
• IS [NOT] Predicate
• ORDER BY, GROUP By, HAVING Clause
• Lab exercise

Module 6: Functions and Expressions

• Aggregate functions
• Character functions
• Datetime functions
• Mathematical functions
• Types of Expressions
• Literal Expressions
• Numeric Expressions
• Lab Exercise

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 exercise

Module 8: Query Expressions

• Query Expression: Definition, Types, Joined Table, Syntax
• Non-Joined Query Expresion 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 exercise

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 exercise

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 exercise

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 exercise

Module 12: Access Options and Isolation Levels

• Concurrency Control and Contention
• Locking Considerations, Locking Considerations, Dirty Reads, Non-Repeatable Reads, Phantoms
• Access Options and Isolation Levels, READ UNCOMMITTED Access Option, READ COMMITTED Access Option, READ COMMITED 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 exercise

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 exercise

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