At a glance
View schedule & enroll Sorted by: location or date
Course number U4184S
Length 5 days
Delivery method Virtual Instructor-Led Training (VILT)
Instructor-led training (ILT)
Onsite dedicated training (OST)
Price USD $3,500
CAD $3,850
*Courses are supported in the delivery formats above, but are not necessarily scheduled in every delivery format listed. Please click the schedule links at the top of the page to see which delivery formats are currently scheduled.

Course overview

This 5-day course is an introduction to SQL, relational database principles, and the HP NonStop™ SQL/MX product and serves as a prerequisite to more advanced 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).


Prerequisites

  • Concepts and Facilities course (U4147S)

Audience

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

Ways to save

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

Course objectives

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

Course outline

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

U4184S E.00