TTSQL005 - TTSQL005: Next Level SQL Programming (with Advanced SQL Topics)

A company’s success hinges on responsible, 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. Our SQL Programming and Database Training Series provides students with the skills they require to develop, analyze and maintain data and in correctly structured, modern and secure databases.

Advanced SQL explores how to identify and use advanced querying techniques to manipulate and index tables. All hands-on work in this course is ANSI SQL compliant and should work with most SQL databases such as Oracle, SQL Server, MySQL, MS Access, Informix, Sybase, or any other ANSI SQL compliant database.

Audience Profile
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.

Student Testimonials

Instructor did a great job, from experience this subject can be a bit dry to teach but he was able to keep it very engaging and made it much easier to focus. Student
Excellent presentation skills, subject matter knowledge, and command of the environment. Student
Instructor was outstanding. Knowledgeable, presented well, and class timing was perfect. Student

Click here to print this page »

Prerequisites


Students should have recently attended a basic SQL class or have equivalent experience.

Detailed Class Syllabus


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