Next Level SQL Programming (with Advanced SQL Topics)

Course Objectives

This “skills-centric” course is about 50% hands-on lab and 50% lecture, designed to train attendees in next level SQL and database skills, coupling the most current, effective techniques with the soundest industry practices. Throughout the course students will be led through a series of progressively advanced topics, where each topic consists of lecture, group discussion, comprehensive hands-on lab exercises, and lab review.

Our engaging instructors and mentors are highly experienced practitioners who bring years of current "on-the-job" experience into every classroom.  Working within in an engaging, hands-on learning environment, guided by our expert team, attendees will explore:

• Advanced Query Techniques
• Manipulating Table Data Using SQL's Data Manipulation Language (DML)
• User-Defined Functions
• Stored Procedures
• Triggers

Course Prerequisites

This is an intermediate and beyond level SQL course geared for experienced end users, data scientists, business analysts, application developers and database administrators. Students should have recently attended a basic SQL class or have equivalent experience.

Course Agenda

Please note that this list of topics is based on our standard course offering, evolved from typical industry uses and trends. We’ll work with you to tune this course and level of coverage to target the skills you need most. Topics, agenda and labs are subject to change, and may adjust during live delivery based on audience needs and skill-level.

1. Advanced Query Techniques

• Inner Joins
• Outer Joins (Left, Right, Full)
• Performing Self-Joins
• Subqueries

• Simple
• Correlated

• Using the EXISTS Operator
• Tips for Developing Complex SQL Queries
• Using Aggregate Functions

• AVG
• COUNT
• SUM
• MIN
• MAX

• Performing Set Operations

• UNION
• INTERSECT
• EXCEPT/MINUS

• Aggregating Results Using GROUP BY
• Restricting Groups with the HAVING Clause
• Creating Temporary Tables

2. Manipulating Table Data Using SQL's Data Manipulation Language (DML)

• Inserting Data into Tables
• Updating Existing Data
• Deleting Records
• Truncating Tables
• Implementing Data Integrity with Transactions

• Beginning Explicit Transactions
• Committing Transactions
• Rolling Back Transactions

3. User-Defined Functions

• 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

4. Stored Procedures

• Definition and Benefits of Use
• CREATE PROCEDURE

• Syntax
• Variables and Parameters

• Control of Program Flow
• ALTER and DROP PROCEDURE
• Implementation Differences

5. Triggers

• 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
• ALTER and DROP TRIGGER