HPE-training-courses
8147  Reviews star_rate star_rate star_rate star_rate star_half

HPE NonStop SQL/MX Basics

This course is an introduction to SQL, relational database principles, and the HPE NonStop SQL/MX product and serves as a prerequisite to more advanced HPE NonStop SQL/MX courses. Hands-on lab...

Read More
$5,000 USD
Course Code U4184S
Duration 5 days
Available Formats Classroom

This course is an introduction to SQL, relational database principles, and the HPE NonStop SQL/MX product and serves as a prerequisite to more advanced HPE NonStop SQL/MX courses. Hands-on lab sessions provide practical experience with generating SQL/MX queries to access data and creating database objects (catalogs, tables, indexes, views, and constraints).

Skills Gained

  • 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: multiple tables using joins and union operations, derived tables using query expressions, and subqueries
  • 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

Who Can Benefit

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

Prerequisites

Concepts and Facilities course (U4147S)

Course Details

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