HPE NonStop SQL/MX Basics

HPE NonStop SQL/MX BasicsU4184SHPHPHP-U4184S1.0<ul> <li>Describe relational database concepts and terminology</li><li>Describe the HPE NonStop SQL/MX processes and objects</li><li>Use a mxci session and reference ANSI names for SQL/MX database objects</li><li>Describe the basic process to write queries and the tools to evaluate the query performance</li><li>Use the mxci SELECT statement and predicates to retrieve data from single tables</li><li>List the types of functions supported in SQL/MX</li><li>Retrieve data from multiple tables using joins and union operations – derived tables using Query Expression by using subqueries</li><li>Create a SQL/MX database (Catalog, Schema, Tables, Indexes, Views, and Constraints)</li><li>Modify data in a table using SQL/MX INSERT, UPDATE, and DELETE statements</li><li>Describe the SQL/MX access options and isolation levels</li><li>Describe SQL/MX database management functions</li></ul><h5>Upon completion of this course, you will:</h5><ul> <li>Understand key relational database concepts and terminology</li><li>Be able to use mxci commands to create SQL/MX objects</li><li>Be able to use SQL statements to retrieve or modify data in SQL/MX tables</li></ul><ul> <li>Concepts and Facilities course (U4147S)</li></ul><ul> <li>Anyone requiring an introduction to SQL and working with the HPE NonStop SQL/MX product</li></ul><h5>Module 1: Introduction to SQL Relational Databases </h5><ul> <li>Definition of a relational database</li><li>Components of a relational database table</li><li>Forming relationships in a relational database</li><li>Types of relationships</li><li>Description of Structured Query Language: Data Definition Language, Data Manipulation Language, Data Control Language, and Transaction Control Language</li><li>Characteristics of a Relational Database Management System (RDBMS)</li><li>Lab exercise</li></ul><h5>Module: 2 Overview of SQL/MX Architecture</h5> <ul> <li>SQL/MX Architecture</li><li>SQL/MX System Metadata</li><li>User Metadata (UMD) Tables</li><li>User Catalog and Schemas</li><li>SQL/MX User Tables, Objects, Tables, Indexes, Views, Constraints, Triggers, Object Namespaces-Object Type, Security Model, Process Architecture, Components, Catalog Manager, DDL Operations, Utilities</li><li>NSM/web Architecture</li><li>Lab exercise</li></ul><h5>Module: 3 Introduction to mxci </h5><ul> <li>SQL/MX Help Facilities</li><li>Starting an mxci session</li><li>mxci Prompts and Termination Character</li><li>SQL/MX Identifiers</li><li>Logical (ANSI) Names</li><li>Specifying ANSI</li><li>Using Logical Names in an mxci Session</li><li>mxci: –SET NAMETYPE Command (ANSI) –SET CATALOG Command –SET SCHEMA Command</li><li>mxci cd Command, Is Commands</li><li>mxci—LOG Command</li><li>Lab exercise</li></ul><h5>Module: 4 Query Writing Process </h5><ul> <li>Overview of query execution</li><li>Overview of query development process</li><li>Analyzing the query objective, Generating the query, Executing the query</li><li>Verifying the results, Assessing performance</li><li>Lab exercise</li></ul><h5>Module: 5 Retrieving Data from a Single Table </h5><ul> <li>Data Types, Character Data Types, Numeric Data Types-Exact and Approximate, Datetime Data Types, Interval Data Types</li><li>INVOKE Command</li><li>SELECT Statement-Clauses, Syntax, Select List</li><li>SELECT-Select List, ALL or DISTINCT Rows, [ANY N] or [FIRST N]</li><li>FROM and WHERE Clause</li><li>Predicates</li><li>Row-Value-Constructor</li><li>Comparison Predicates-Syntax, Examples</li><li>LIKE, BETWEEN, and IN Predicates</li><li>Boolean Operators and Compound Predicates</li><li>NULL Values</li><li>IS [NOT] Predicate</li><li>ORDER BY, GROUP By, HAVING Clause</li><li>Lab exercise</li></ul><h5>Module: 6 Functions and Expressions </h5><ul> <li>Aggregate functions</li><li>Character functions</li><li>Datetime functions</li><li>Mathematical functions</li><li>Types of expressions</li><li>Literal expressions</li><li>Numeric expressions</li><li>Lab exercise</li></ul> <h5>Module: 7 Retrieving Data from Multiple Tables</h5> <ul> <li>Generating the following types of joins: CROSS, NATURAL, INNER, EQUI, LEFT, RIGHT, Self</li><li>Correlation Names</li><li>Join with Additional Search Conditions</li><li>UNION Operation</li><li>Lab exercise</li></ul><h5>Module: 8 Query Expressions </h5><ul> <li>Query expression: Definition, Types, Joined Table, Syntax</li><li>Non-Joined Query Expression Table: VALUES Statement, TABLE Statement, SELECT Query Specification</li><li>Simple Table—SELECT expression</li><li>Subquery: Definition, Non-Correlated, Correlated, Evaluation of a Correlated Subquery, Classification, SELECT Form of a subquery</li><li>Predicates: Subquery, Comparison, BETWEEN, IN, and EXISTS, and EXISTS Examples</li><li>Subqueries using the Comparison, BETWEEN, and IN Predicates</li><li>Subquery key points</li><li>Lab exercise</li></ul> <h5>Module: 9 Creating SQL/MX Objects </h5><ul> <li>Creating SQL/MX Objects</li><li>SQL/MX Object Naming</li><li>CREATE CATALOG Command—Syntax, REGISTER CATALOG Command—Syntax, UNREGISTER CATALOG Command—Syntax, Catalog Considerations</li><li>CREATE SCHEMA Command—Syntax</li><li>Rules for Naming SQL/MX Schema Subvolumes, Schema Considerations, Creating a User Schema</li><li>Creating a SQL/MX Table—Topics</li><li>Column Definitions, Column Name Rules, Character Sets, Default Value, ISO88591 Character Set Examples</li><li>SYSTEM_DEFAULTS Table— NOT_NULL_CONSTRAINT_DROPPABLE_OPTION</li><li>Constraints, Constraints Names, Table Constraints</li><li>Specifying Physical Location and Name for the Underlying Guardian File</li><li>Specifying a Clustering Key, Specifying a Clustering Key—STORE BY Clause, Terminology</li><li>Clustering Key—No STORE BY Clause and No Primary Key Specified, Clustering Key—STORE BY PRIMARY KEY: Primary Key Specified As DROPPABLE</li><li>Specifying Guardian File Attributes</li><li>CREATE INDEX—Syntax, CREATE VIEW—Syntax, CREATE VIEW—Example, Considerations for Creating a View</li><li>Lab exercise</li></ul> <h5>Module: 10 Inserting Data and Updating Statistics </h5><ul> <li>Methods for Loading Multiple Rows of Data</li><li>Inserting Data into the Database, INSERT Statement—Syntax, Inserting a Single Row, Inserting Multiple Rows, INSERT Considerations</li><li>SQL/MX Histogram Statistics, Statistics Tables, mxci UPDATE STATISTICS Utility, Examples of mxci UPDATE STATISTICS</li><li>Lab exercise</li></ul><h5>Module: 11 Modifying Data </h5><ul> <li>Maintaining Database Consistency</li><li>Transaction Management Statements</li><li>Explicit Transaction: User-Defined Transaction, INSERT, UPDATE, DELETE</li><li>Implicit Transaction: System-Defined Transactions, SELECT, INSERT, UPDATE, DELETE</li><li>Modifying Existing Data</li><li>UPDATE Statement—Syntax, Updating a Single Row, Updating Multiple Rows, UPDATE Statement—Scalar Subquery, UPDATE Considerations</li><li>Removing Data from the Database</li><li>DELETE Statement—Syntax, Deleting Data, DELETE Considerations</li><li>Lab exercise</li></ul><h5>Module: 12 Access Options and Isolation Levels </h5><ul> <li>Concurrency Control and Contention</li><li>Locking Considerations, Dirty Reads, Non-Repeatable Reads, Phantoms</li><li>Access Options and Isolation Levels, READ UNCOMMITTED Access Option, READ COMMITTED Access Option, READ COMMITTED Considerations, SERIALIZABLE or REPEATABLE READ Access Option</li><li>Lock Modes, Access Options and Lock Modes</li><li>SET TRANSACTION Statement, SET TRANSACTION Statement—Example, Transaction Isolation-Level Rules</li><li>DEADLOCK, Viewing Locks on a Table</li><li>Lab exercise</li></ul><h5>Module: 13 Management Functions</h5> <ul> <li>SQL/MX Object Dependencies</li><li>SQL Authorization ID</li><li>Object Ownership and Security Rules</li><li>Granting Privileges to Users—Example</li><li>Altering SQL/MX Objects in a SQL/MX Database</li><li>Authorization Requirements for Altering Database Object</li><li>Altering TABLE or INDEX FILE Attributes, Adding Columns to a Table, Altering Considerations</li><li>Removing SQL/MX Database Objects, Dropping SQL/MX Objects From a SQL/MX Database, Removing Your Database Objects</li><li>Managing Data</li><li>mxtool VERIFY Utility, mxtool VERIFY Utility—Security Considerations, mxtool VERIFY Utility—Syntax</li><li>Performance, Monitoring Performance</li><li>Using the EXPLAIN Function with a Prepared Query</li><li>EXPLAIN statement with OPTIONS ‘f’</li><li>NSM/web Connectivity Services, Visual Query Planner DISPLAY STATISTICS</li><li>Lab exercise</li></ul> <h5>Module: 14 Advanced Topics </h5><ul> <li>Referential Integrity (RI)</li><li>Trigger Definition</li><li>Partitioning—Range Partitioning, Hash Partitioning</li><li>Publish and Subscribe Services</li><li>Rowsets</li><li>Compound Statements</li><li>SELECT statement—TRANSPOSE Clause, SAMPLE Clause</li><li>Sequence Function</li></ul> <h5>Module: 15 MXDM</h5> <ul> <li>Features and requirements of MXDM</li><li>Installing and Uninstalling MXDM</li><li>Example screens</li></ul>- Describe relational database concepts and terminology - Describe the HPE 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 Expression by 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 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- Concepts and Facilities course (U4147S)- Anyone requiring an introduction to SQL and working with the HPE NonStop SQL/MX productModule 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 screens5 jours3750.004500.003750.00