Home About Courses Schedule Services Webinars Contact Search

SQL Essentials on PostgreSQL

SEE SCHEDULE

Duration: 3.0 days

Method: Instructor led with hands-on.

Price: $1800.00

Course Code: PG1000


Audience

Programmers, Analysts, Testers, Database Administrators, and IT Managers who are exposed to, or are required to perform SQL activities associated with data stored in a PostgreSQL relational database.

Description

PostgreSQL has been said to be the world’s ‘Most Advanced Open Source Relational Database’. Whether this is actually true or not its popularity has increased dramatically over the last few years.

Come prepared to master Structured Query Language (SQL) as used on the PostgreSQL platform in this fast paced and hands on course. The course begins with a discussion of relational database concepts and terminology. A review of normalization design and table creates takes place and quickly moves into a very practical discussion of data retrieval and manipulation using SQL. All topics have a slant on performance, and each topic is reinforced with hands-on workshops. pgAdmin is the most popular PostgreSQL administration and development platform and will be used for all workshops. Students will come away from this class with a clear understanding of the PostgreSQL relational database, SQL programming and related performance.

Objectives

Upon successful completion of this course, the student will be able to:

  • Write good efficient SQL code to retrieve data
  • Understand database design, integrity, and data manipulation
  • Translate and code business problems in SQL
  • Understand SQL Joins and table relationships
  • Understand indexable vs non-indexable predicates
  • Understand the role of the PostgreSQL optimizer and the approaches to access path selection

Prerequisites

None

Topics

  • I. Relational Database Concepts
    • The Relational Model
    • Relational Terminology
    • Normalization
    • Table Relationships (PKs and FKs)
    • Intro to SQL and Transact-SQL
    • Table Creates
    • The SQL Server Optimizer
  • II. Basic Data Retrieval
    • SELECT Statement
    • Using SELECT
    • Projecting Columns Onto the Result
    • Basic Predicates
    • Expressions in the SELECT Clause
    • Expressions in the WHERE clause
  • III. Intermediate Data Manipulation
    • Compound Conditions
    • Other WHERE Clause Operators
      • BETWEEN
      • IN
      • LIKE
      • IS NULL
    • Using DISTINCT
    • ORDER BY Clause
  • IV. Built-in Functions
    • Working with Datatypes and Casting
    • SQL string functions
    • SQL numeric functions
    • SQL date functions and logic
    • Functions Involving NULLs
    • CASE Statements
  • V. Aggregation
    • SUM, AVG, MIN, MAX & COUNT Functions
    • Grouping Results with GROUP BY
    • Filtering Groups with HAVING
  • VI. Joins
    • Inner Equi-Join
    • Join Classification
    • Cartesian Products
    • Inner Joins of More than 2 Tables
  • VII. Data Modification
    • INSERT
    • UPDATE
    • DELETE
    • COMMIT and ROLLBACK