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
