Course ID 00071731
HP product number U4181S
Course format ILT
Course length 4 days
Skill level Foundational
Delivery languages English
Related certifications
In preparation for these exams
Selected items from this course are included in these exams:
Register for this course.
Find this course in the Training calendar and click the “Register” link in the last column to begin your registration.

Course description

This course is an introduction to the HP NonStop™ SQL/MP database product and serves as a prerequisite to more advanced courses. Hands on lab sessions provide practical experience with creating databases and generating queries to access the data.

Who should attend

• Anyone working with or interested in the NonStop SQL/MP product
• Anyone responsible for managing or designing a database or developing embedded SQL programs

Prerequisites

• Concepts and Facilities course
For complete prerequisites and requirements to achieve any of the related certifications or upgrade paths, see the certification description on the HP ExpertOne website.

Topics

  • Relational database definitions
    • Components of a relational table
    • Relational database structure
    • Primary and foreign keys in a relational table
    • How relationships are formed in a relational database
    • Characteristics of a relational database management system
  • NonStop SQL/MP relational database management system (RDBMS)
    • NonStop SQL/MP RDBMS overview
    • Advantages of using NonStop SQL/MP
    • Starting and stopping an SQLCI session
    • Major components of NonStop SQL/MP
    • Operating environment of NonStop SQL/MP
    • Interaction of NonStop SQL/MP and other Tandem products such as Transaction Monitor/MP (TM/MP), FastSort, DP2, and the file system
    • Different interfaces to a NonStop SQL/MP database
    • Hierarchical structure of system catalogs, user catalogs, and user tables NonStop SQL/MP Database Structure
    • Characteristics and functions of Nonstop SQL/MP objects
    • Types of database access paths (table versus index)
    • Difference between a protection view and a shorthand view
    • Using the SQLCI FILEINFO utility to display table characteristics
      Nonstop SQL/MP Conversational Interface Overview
    • Standard sublanguage groups and their functions
    • Common SQL statements and their functions of each SQL sublanguage group
    • SQL utilities and extensions, and description of their functions
      Lab Exercise (30 minutes)
    • Become familiar with objects in the sample database and explore the capabilities of the NonStop SQL/MP online help facility
    • Using standard SQLCI commands
    • Setting up and saving SQLCI environment attributes
    • Setting up and saving SQLCI session attributes
    • Creating an SQLCI session log
    • Executing saved session and environment command files
    • Executing other utilities from within an SQLCI session
    • Creating CATALOG and MAP DEFINEs
      Lab Exercise (30 minutes)
    • Set up your own SQLCI environment and save the settings for later use
      Ad Hoc Query Processing
    • Components involved in the query execution process
    • Three ways of executing ad hoc SQL queries
    • Functions of the PREPARE and EXECUTE statements
    • Functions of the DISPLAY STATISTICS and EXPLAIN statements
      Lab Exercise (30 minutes)
    • Run predefined SQL queries
    • Standard sublanguage groups and their functions
    • Common SQL statements and their functions of each SQL sublanguage group
    • SQL utilities and extensions, and description of their functions
      Lab Exercise (30 minutes)
    • Become familiar with objects in the sample database and explore the capabilities of the NonStop SQL/MP online help facility
  • NonStop SQL/MP conversational interface (CI) setting up an SQLCI session
    • Using standard SQLCI commands
    • Setting up and saving SQLCI environment attributes
    • Setting up and saving SQLCI session attributes
    • Creating an SQLCI session log
    • Executing saved session and environment command files
    • Executing other utilities from within an SQLCI session
    • Creating CATALOG and MAP DEFINEs
      Lab Exercise (30 minutes)
    • Set up your own SQLCI environment and save the settings for later use
      Ad Hoc Query Processing
    • Components involved in the query execution process
    • Three ways of executing ad hoc SQL queries
    • Functions of the PREPARE and EXECUTE statements
    • Functions of the DISPLAY STATISTICS and EXPLAIN statements
      Lab Exercise (30 minutes)
    • Run predefined SQL queries
  • Retrieving data from the database
    Generating ad hoc queries for a single table
    • Using the INVOKE command to display table attributes and describe the column projection
    • Using the SQL clauses (WHERE, ORDER BY, GROUP BY, AND HAVING) in a query
    • Developing SQL predicates with LIKE, BETWEEN, and IN; comparison and compound predicates; and using these predicates in a query
    • Using expressions in a select list or a search condition
    • Using aggregate functions in a select list or search condition
      Lab Exercise (1.7 hours)
    • Access individual tables using SQL clauses, predicates, expressions, and aggregate functions to retrieve specified data from the database
      Generating Ad Hoc Queries for Multiple Tables
    • Concepts of join, union, and Cartesion product
    • Obtaining column definitions with the INVOKE statement and identifying join columns
    • Syntax of join conditions, predicates, and clauses
      Lab Exercise (1.7 hours)
    • Retrieve data from several tables using appropriate SQL clauses to return specified data
      Generating Subqueries
    • Concepts of subquery
    • Syntax of subquery
  • Creating a NonStop SQL/MP database
    •Catalog
    •Table and Partition
    •Index
    •Constraint
    •View
    •Comment
    •Collation
    Lab Exercise (1.7 hours)
    •Use SQLCI to create NonStop SQL/MP objects to comply with specifications
  • Loading a NonStop SQL/MPdatabase
    • Using the INSERT statement to load a single row of data into a NonStop SQL/MP database
    • SQL utilities CONVERT, COPY, and LOAD
      Lab Exercise (45 minutes)
    • Learn how to propagate your database
  • Modifying database data
    • Creating user-defined transactions and committing or rolling back the transaction
    • Inserting, updating, or deleting a row or set of rows in a single table with specified data and selection criteria
      Lab Exercise (45 minutes)
    • Learn how to keep your database up-to-date
  • NonStop SQL/MP management functions
    • Generating the syntax to alter or drop a NonStop SQL/MP object
    • Impact and considerations of altering or dropping SQL objects in the database
    • Security issues related to adding, altering, or dropping an SQL object from the database
    • Object dependencies related to these functions
    • Appropriate SQLCI and NonStop Kernel utilities that can be used to manage a production database
      Lab Exercise (30 minutes)
    • Learn how to remove objects from your database

Objectives

After completing this course, you should be able to do the following:
  • Understand relational database concepts and terminology.

How to register

View the HP ExpertOne Global Training Calendar to register for the trainings offering that best meets your needs.

Policies, fees and cancellations

Course fees may vary and are established and collected by the training center delivering the course. Cancellation fees may apply. Contact your HP Authorized Training Partner for their respective policies.