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: –SET NAMETYPE Command (ANSI) –SET CATALOG Command –SET SCHEMA Command
• 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 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 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, 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 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