About this course

Course code TPU4184S
Duration 5 Days

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

Prerequisites

  • Concepts and Facilities course


Audience

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

Delegates will learn how to

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

Benefits to you

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

Outline

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

5 Days

Duration

This is a QA approved partner course

Delivery Method

Delivery method

Classroom

Face-to-face learning in the comfort of our quality nationwide centres, with free refreshments and Wi-Fi.

Trusted, awarded and accredited

Fully accredited to ensure we provide the highest possible standards in learning

All third party trademark rights acknowledged.