Home About Courses Schedule Services Webinars Contact Search

Advanced SQL on PostgreSQL

SEE SCHEDULE

Duration: 3 days

Method: Instructor led with hands-on.

Price: $1800.00

Course Code: PS1010


Audience

Programmers, Analysts, Database Administrators, Testers, and IT Managers who are exposed to, or are required to learn more about the PostgreSQL relational database while writing more advanced SQL associated with its data.

Description

Get ready to go deep with PostgreSQL Structured Query Language (SQL) and become a power user. Whether you are being asked to solve complicated problems with SQL or you need to read and understand complex SQL that is already written, this course is for you! Come prepared to master Structured Query Language (SQL) in this fast paced and hands-on course. We begin with an in-depth discussion of joins and move quickly to more complex operations involving set processing, grouping, subqueries, table expressions, recursion and more. Each topic is reinforced with hands-on workshops. The SQL language has efficiencies and inefficiencies in the language and many of these topics are covered. PostgreSQL optimization, file structures, data statistics, indexes and more are covered.

Prerequisites

SQL Essentials or equivalent experience.

Topics

  • I. Complex Joins
    • Inner Joins
    • Cross Joins
    • LEFT and RIGHT Outer Joins
    • Full Outer Joins
    • “Anti” Joins
    • Self Joins
  • II. Multiple Query Blocks
    • Non-correlated Subqueries
    • Correlated Subqueries
    • Scalar Fullselects
  • III. Table Expressions and Views
    • Views
    • Nested Table Expressions
    • Common Table Expressions
  • IV. Set Operations
    • UNION
    • INTERSECT
    • EXCEPT / MINUS
  • V. Advanced Grouping
    • GROUP BY Clause
    • HAVING Clause
    • ROLLUP
    • CUBE
  • VI. Solving Complex Problems with SQL
    • Relational Difference
    • Quota Queries
    • Relational Division
    • Recursion
    • Complex CASE Statements
    • Table Pivoting
  • VII. Managing Transactions and Units of Work
    • Data Modification Statements
    • COMMIT, ROLLBACK and SAVEPOINT
    • When to COMMIT?
    • Checkpoint and Restart Considerations
    • Constraint Violations
  • VIII. Temporary Data
    • Temporary Tables
    • Materialized Query Tables & Materialized Views
  • IX. Additional Topics
    • Data Files
    • Data Statistics
    • Indexing
    • Reorganizing files
    • SQL Explain
    • Table Design
    • Surrogate Keys
    • Data Compression
    • Statics vs Dynamic SQL