4 Days. Instructor-led.
A company’s success hinges on responsible and accurate database management. Organizations rely on highly available data to complete all sorts of tasks, from creating marketing reports and invoicing customers, to setting financial goals. Data professionals like analysts, developers, and architects are tasked with creating, optimizing, managing, and analyzing data from databases, with little room for error. When databases aren’t built or maintained correctly, it’s easy to mishandle or lose valuable data.
This 4-day course explores how to identify and use advanced querying techniques to manipulate and index tables. All hands-on work is ANSI SQL compliant and should work with SQL databases such as Oracle, SQL Server, mySQL, MS Access, Informix, Sybase, or any other ANSI SQL compliant database. Join us and learn the skills you need to develop, analyze, and maintain data in structured, modern, and secure databases.
Learning Objectives
After completing this course, you will be comfortable with:
- Advanced Query Techniques
- Manipulating Table Data Using SQL’s Data Manipulation Language (DML)
- User-Defined Functions
- Stored Procedures
- Triggers
Audience
This course assumes that you are familiar with the SQL language and basic SQL constructs.
Course Outline
- 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
- 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
- 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
- Stored Procedures
- Definition and Benefits of Use
- CREATE PROCEDURE
- Syntax
- Variables and Parameters
- Control of Program Flow
- ALTER and DROP PROCEDURE
- Implementation Differences
- 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