courseoutline_metadesc.tpl

HPE NonStop SQL/MX Basics (U4184S) – Details

Detaillierter Kursinhalt

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