Course ID 00086594
HP product number U4184S
Course format ILT
Course length 5 days
Skill level Foundational
Delivery languages English
Related certifications
In preparation for these exams
Selected items from this course are included in these exams:
Register for this course.
Find this course in the Training calendar and click the “Register” link in the last column to begin your registration.

Course description

This 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).

Who should attend

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

Prerequisites

• Concepts and Facilities course (U4147S)
For complete prerequisites and requirements to achieve any of the related certifications or upgrade paths, see the certification description on the HP ExpertOne website.

Topics

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

Objectives

After completing this course, you should be able to do the following:
  • Understand key relational database concepts and terminology
  • Use mxci commands to create SQL/MX objects
  • Use SQL statements to retrieve or modify data in SQL/MX tables
  • 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

How to register

View the HP ExpertOne Global Training Calendar to register for the trainings offering that best meets your needs.

Policies, fees and cancellations

Course fees may vary and are established and collected by the training center delivering the course. Cancellation fees may apply. Contact your HP Authorized Training Partner for their respective policies.