HP NonStop SQL/MP essentials

Prerequisites

Concepts and Facilities course

Audience

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

Ways to save

Benefits to you

• Relational database concepts and terminology
• HP NonStop SQL/MP components
• HP NonStop SQL/MP database structures
• HP NonStop SQL/MP conversational interface
• HP NonStop SQL/MP queries
• HP NonStop SQL/MP database object creation and management

Next Steps

• HP NonStop SQL/MP Programming U4182S
• HP NonStop SQL/MP Database Management U4180S
• HP NonStop SQL/MP Query Design for Performance U4183S
• HP NonStop SQL/MX database for SQL/MP users U4185S

Course outline

• 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
• HP NonStop SQL/MP relational database management system (RDBMS)
• HP NonStop SQL/MP RDBMS overview
• Advantages of using HP NonStop SQL/MP
• Starting and stopping an SQLCI session
• Major components of HP NonStop SQL/MP
• Operating environment of HP NonStop SQL/MP
• Interaction of HP NonStop SQL/MP and other Tandem products such as Transaction Monitor/MP (TM/MP), FastSort, DP2, and the file system
• Different interfaces to a HP NonStop SQL/MP database
• Hierarchical structure of system catalogs, user catalogs, and user tables
• HP 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 HP NonStop SQL/MP online help facility
• HP 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 HP NonStop SQL/MP database
• Catalog
• Table and Partition
• Index
• Constraint
• View
• Comment
• Collation
• Lab Exercise (1.7 hours)
• Use SQLCI to create HP NonStop SQL/MP objects to comply with specifications
• Loading a HP NonStop SQL/MP database
• Using the INSERT statement to load a single row of data into a HP 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
• HP NonStop SQL/MP management functions
• Generating the syntax to alter or drop a HP 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 HP NonStop Kernel utilities that can be used to manage a production database
• Lab Exercise (30 minutes)
• Learn how to remove objects from your database