(Click Category to List Courses)

38 - IT-D Information Technology - Databases and Related Technologies


IT-D 257 - Advanced Topics in PostgreSQL for Data Analysis

Code Start Date Duration Venue
IT-D 257 22 August 2022 5 Days Istanbul Registration Form Link
IT-D 257 17 October 2022 5 Days Istanbul Registration Form Link
IT-D 257 12 December 2022 5 Days Istanbul Registration Form Link
Please contact us for fees

 

Course Description

 PostgreSQL, also known as Postgres, is a free and open-source database. Understanding relational data stores are becoming increasingly important for data scientists and analysts. SQL databases have long been used by businesses to store transactional and business intelligence data. This course is designed to help you learn how to perform common relational database and data science tasks, such as writing advanced queries, data analysis, finding, exploration, and extraction within relational databases, if you need to work with SQL databases. You will learn how to aggregate data, analyze data, retrieve data from multiple tables using table joins, and write more advanced queries in this course.

Course Objectives

  • Learn what PostgreSQL databases are
  • Learn the basics of working with PostgreSQL
  • Learn how geographical data is stored in a database and spatial extension (PostGIS)
  • Learn about the analytic part of the database
  • Learn how to write SQL queries

Who Should Attend?

  • PostgreSQL Developer
  • Database Admins 
  • Data Analyst and Scientist
  • Managers
  • Anyone who wants to learn about PostgreSQL

Course Details/Schedule

Day 1

  • Basics of PostgreSQL
  • Installing PostgreSQL on Windows 10
  • Load Sample Database
  • Creating a new Table
  • Populating a Table
  • Query a Table
  • PostGIS on top of PostgreSQL for spatial data analysis
 

Day 2

  • Server and Database
  • Schema
  • Functions 
  • Sequences 
  • Extensions
  • Procedures
  • Operators (UNION, IN, AND, OR, LIKE, etc.)

Day 3

  • Introduction to Joins 
  • Joins (Inner, Left, Right, Cross and Natural, etc.)
  • Introduction to Aggregate Functions
  • Functions (AVG, COUNT, MAX, MIN, SUM etc.)
  • Spatial data queries with joins and functions

Day 4

  • Introduction to Triggers 
  • Creating Triggers
  • Managing Triggers
  • Introduction to Views
  • Creating Views
  • Managing Views
  • Creating and Updatable Views
  • Creating and Materialized Views

Day 5

  • Introduction to Analytic Functions 
  • Creating Sample Tables
  • Using AVG as a Window Function
  • ROW Number Functions
  • Rank Functions
  • Dense Rank Functions
  • First Value Function
  • Last Function
  • LAG Function
  • LEAD Function
  • Spatial Analysis and GIS Data Processing
  • Integrating PostgreSQL to Projects: Case Implementations