U4184S Outline - HPE NonStop SQL/MX Basics

Detailed Course Outline

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

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

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

Generating the following types of joins: CROSS, NATURAL, INNER, EQUI, LEFT, RIGHT, Self
Correlation Names
Join with Additional Search Conditions
UNION Operation
Lab exercise

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

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

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

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

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

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