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
