SQL Programming Training Class

• Overview of Relational Database Concepts
• Relational Databases and Relational Database Management Systems
• SQL, DML and DDL Languages

Manipulating Query Results

• Using Row Functions
  • Character
  • Numeric
  • Date and Time
  • Data Conversion
• Using Column Functions
• Handling NULLs
• Aggregating Results using GROUP BY
• Restricting Groups with the HAVING Clause

• Displaying Table Structures
• Column Datatypes
• SELECTing Column Data FROM a Table
• SELECT DISTINCT Values
• COUNTing Rows
• Restricting Rows using the WHERE Clause
  • Using Operators
  • Mathematical
  • Logical
  • Comparison
  • Concatenation
  • Set Operators
• Sorting Results Using ORDER BY
• Joining Multiple Tables
• Using Column and Table Aliases
• Pseudo Columns

• Outer Joins
• Joining a Table to Itself
• Subqueries
• Hierarchical Queries
• Using the Data Dictionary
• Grouping, Filtering and Sorting Query Results
• Tips for Developing Complex SQL Queries

• INSERT into Tables
• UPDATE Table Data
• DELETE from Tables
• TRUNCATE Tables
• Controlling Transactions
  • COMMIT
  • ROLLBACK
  • SAVEPOINT

• Database Development Methodology Overview
• Building a Logical Data Model
  • Identifying Entities and Attributes
  • Isolating Keys
  • Relationships between Entities
  • Creating Entity-Relationship Diagrams
• Transforming to Physical Design
  • Migrating Entities to Tables
  • Selecting Primary Keys
  • Defining Columns
  • Enforcing Relationships with Foreign Keys
• Constructing the Database using DDL
  • Creating Tables, Indexes, Constraints and Views
  • Using Table and Column COMMENTs
  • Using SYNONYMs
• Facilitating Development Using CASE Tools

• Dropping Tables, Indexes and Views
• ALTERing Tables, Indexes and Views
• Enabling and Disabling Constraints
• Managing Security

Performance Tuning

• Writing Efficient SQL
• EXPLAIN Query Analysis
• Indexing for Performance
• Index-Organized Tables
• Clustering

• Definition and Benefits of Use
• Perils and Pitfalls
• CREATE PROCEDURE
  • Syntax
  • Variables and Parameters
  • Code Examples
• Temporary Tables
• Control of Program Flow
• ALTER and DROP PROCEDURE
• Implementation Differences

• Definition and Benefits of Use
• CREATE FUNCTION
  • Syntax
  • RETURN Clause and the RETURNS Statement
  • Scalar vs. Table Functions
• Comparison with Stored Procedures
• Returning Scalar Values and Tables
• ALTER and DROP FUNCTION

• Definition and Benefits of Use
• Alternatives (e.g., Constraints)
• CREATE TRIGGER
  • Syntax
  • Trigger Types
• "Inserted" (or "NEW") and "Deleted" (or "OLD") Tables
• Event Handling and Trigger Execution
• Nested and Recursive Triggers
• ALTER and DROP TRIGGER