HPE NonStop SQL/MX Basics Training

• Concepts and Facilities course

Audience

• Anyone requiring an introduction to SQL and working with the NonStop SQL/MX product

• Describe relational database concepts and terminology
• Describe the HP NonStop™ SQL/MX processes and objects
• Use a mxci session and reference ANSI names for SQL/MX database objects
• Describe the basic process to write queries and the tools to evaluate the query performance
• Use the mxci SELECT statement and predicates to retrieve data from single tables
• List the types of functions supported in SQL/MX
• Retrieve data from
• Create a SQL/MX database (Catalog, Schema, Tables, Indexes, Views and Constraints)
• Modify data in a table using SQL/MX INSERT, UPDATE, and DELETE statements
• Describe the SQL/MX access options and isolation levels
• Describe SQL/MX database management functions

Benefits to you

Upon completion of this course, you will:

• Understand key relational database concepts and terminology
• Be able to use mxci commands to create SQL/MX objects
• Be able to use SQL statements to retrieve or modify data in SQL/MX tables

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
• 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