Module 1: Query writing process

• The query writing process:

  • Analysis and design
  • Implementation
  • Assessment
  • NonStop server tools

Lab

• Apply the query writing process to a simple query using the available NonStop server tools

Module 2: Basic query component overview

• Functions and syntax of SQL Data Manipulation Language (DML) statements
• Overview of query components for restricting row selections, predicates, aggregate functions, grouping functions, and handling SQL null values

Lab

• Design simple queries for a given set of problem scenarios

Module 3: Advanced query writing

• Generating joins and subqueries to access data from multiple tables
• Specifying the use of multivalued predicates in queries
• Manipulating string data with HPE NonStop SQL/MP string functions
• The use of the CASE operator in a query
• Manipulating SQL DATETIME and INTERVAL data types
• Designing a query for concurrent access

Lab

• Generate queries to solve complex business problems

Module 4: Performance considerations in query design

• The importance of integrating performance design issues in the query writing process
• Tools that can be used for performance planning and assessment
• Functions of these tools

Lab

• Become familiar with the physical implementation of the training database

Module 5: Query processing using HPE NonStop SQL/MP

Module 6: HPE NonStop SQL/MP optimizer functions

• How the SQL optimizer transforms key predicates and restructures queries
• Differentiating the three types of selectivity: predicate, index, and table; how the optimizer determines these selectivities
• How the SQL optimizer determines and selects the appropriate join and sort strategies, computes the cost for a query, and selects the optimal query execution plan for the query
• How to use the QEP information to predict the performance level of a query

Lab

• Interpret the information for various EXPLAIN PLAN reports and evaluate the performance levels of the queries

Module 7: improving query performance through query design

• Using efficient predicates in queries to improve performance
• Designing efficient join queries
• Improving query performance by selecting appropriate columns for faster data access, minimizing costs for sort operations, and using index-only scan

Lab

• Construct queries for a given set of problem scenarios and apply performance-enhancement features to improve query performance

Module 8: Improving query performance through environment control

• CONTROL statements that influence the optimizer’s decision to use an index
• CONTROL statements that influence the optimizer to consider parallel processing
• CONTROL statements that affect table processing
• The compilation option that does not invalidate a program

Lab

• Experiment with different CONTROL statements to study the effects on query performance

Module 9: Query design for DSS and OLTP environment

• Database design characteristics of DSS and OLTP databases
• Performance requirements for both DSS and OLTP applications
• Query design characteristics of DSS and OLTP environment
• General guidelines for designing queries for DSS and OLTP environments