Tag Archives: postgresql

Training Foundations of SQL for PostgreSQL – 3 days

Class overview :

This is a course that covers SQL for querying and modifying databases, as well as database transaction processing, creation and dropping of tables and databases and tables. The aim is to provide a sound foundation of SQL, both in general, and in the context of PostgreSQL.

The course is a hands on course and the emphasis is on creating SQL scripts textually, rather than through a GUI.

Class goals :

This is a course that covers SQL for querying and modifying databases, as well as database transaction processing, creation and dropping of tables and databases and tables. The aim is to provide a sound foundation of SQL, both in general, and in the context of PostgreSQL.

The course is a hands on course and the emphasis is on creating SQL scripts textually, rather than through a GUI.

Content of Foundations of SQL for PostgreSQL

Introduction to Database Concepts

  • overview of relational database and the client – server model
  • database concepts – tables, rows and columns, primary keys and foreign keys
  • database data types

Introduction to SQL

  • creating and running SQL scripts
  • overview of SQL syntax
  • guidelines for writing readable and maintainable SQL

Retrieving data – SELECT, WHERE and ORDER

  • Overview of the SELECT statement and its clauses
  • specifying source table(s) – using the FROM clause
  • specifying which columns in a table ( or the entire table ) are to be retrieved
  • result sets
  • Renaming columns using aliases
  • Sorting the query results using ORDER BY

Filter Results using the WHERE clause

  • logical expressions using numeric and string comparison Operators
  • basic numeric and string based filtering
  • filtering based on results of calculations
  • duplicate removal – using DISTINCT
  • combining and extending logical expressions using AND , OR and NOT
  • specifing ranges – BETWEEN and IN
  • handling NULL values
  • basic pattern matching – LIKE
  • Using UNION, INTERSECT and EXCEPT to join SELECT Results
  • Conditional expressions
    • The CASE statement
    • The COALESCE function
    • The NULLIF function

Queries involving multiple tables

  • Overview of the concept of a Join
  • Specifying specific columns in specific tables
  • Inner Joins, Outer Joins , Cross joins, Natural joins
  • Systematic techniques for devising and simplifying complex joins

Working with the Standard PostgreSQL Functions

  • overview and syntax
  • Math, String and Conversion Functions
  • Date modification and calculation functions
  • replacing NULLs with a specific value
  • standard PostgreSQL functions in WHERE clauses

Grouping and Summarizing Results

  • Overview of the Aggregate Functions (MAX(), SUM(), AVG(), COUNT() … )
  • Correct use of Aggregate functions
  • GROUP BY clause
  • HAVING clause

Subqueries

  • Overview of Subqueries and their use
  • Strategies fo designing and constructing subqueries
  • Filtering using subqueries
  • Derived Columns based on subqueries

Views

  • The VIEW concept
  • VIEWS as a means of simplifying complex queries
  • Creating and Dropping Views

Inserting, Updating and Deleting Data

  • Inserting single and multiple rows
  • specifying which columns values are to be inserted in – by column position vs. column name
  • working with Auto-Incrementing Values
  • handling NULL values
  • Inserting Data from one Table into Another
  • Updating and Deleting Data
  • Modifying Data through a View

Inserts, Updates and Deletes in a Transaction Oriented Environment

  • the concept and purpose of a transaction
  • setting up a Transaction Environment
  • Checking the Work to be done
  • Undoing Changes with ROLLBACK
  • Committing a Transaction

Creating and Modifying Tables

  • The CREATE TABLE clause
  • Specifying Primary and Foreign Keys
  • DEFAULT values
  • Constraining Input values
  • Creating and using Temporary Tables
  • Creating a New Table From an Existing Table
  • Altering and Dropping Tables
Foundations of SQL for PostgreSQL
Training Foundations of SQL for PostgreSQL

Onze voordelen :

  • Type of training: Inter-company, intra-company and individual
  • 100% flexible & personalised training : You choose the place, the dates and the training program
  • Offer request : Response within 24 hours
  • 50% discount for SME’s from Brussels-Capital Region
  • Free parking, lunch & drinks
  • Free use of our Digital Competence Centre: Manuals, courses, exercises, …

Training PostgreSQL Administration using Navicat – 2 days

Class overview :

This course covers the installation and use of Navicat for administering PostgreSQL database. It will cover the administration, tuning, backup and recovery of PostgreSQL databases using the Navicat Database Administration Tool.

Class goals :

This course covers the installation and use of Navicat for administering PostgreSQL database. It will cover the administration, tuning, backup and recovery of PostgreSQL databases using the Navicat Database Administration Tool.

Content of PostgreSQL Administration using Navicat

INavicat for PostgreSQL – installation and configuration

Configuring connectivity

  • SSL vs. non SSL
  • Tunneling connections through SSH
  • Understanding Connection keep-alives
  • connection parameter storage

Navicat and Cluster Management

  • Adding and dropping databases
  • Configuring database defaults
  • Carrying out database VACCUUM and ANALYZE operations
  • Duplicating database objects

Using server monitor to modify and manage systems

Backup and Recovery

  • creating backups
  • Converting a backup files into an SQL Script
  • Running recovery scripts
  • Exporting data using Navicat Data export tools

Working with the Navicat Data Viewer/Editor

  • Form and Grid Views
  • BLOB and large text editing
  • custom display formats
  • Sorting & Searching
  • Following Primary Key – Foreign key relationships
  • more complex record filtering

Working with the Navicat Query Builder

  • Overview and features – Syntax highlighting. SQL code completion
  • SQL console
  • Navicat Query logging

Importing and exporting data

  • importing via ODBC
  • importing data from MS-Excel, CSV, Text, DBF …
  • exporting data to Excel, Access, PDF, HTML

Graphical exploration of database/table structure

Managing schemas, view and stored procedures

Managing privileges amd access control

  • visual user manager
  • cloning users and their permissions
  • group membership management

Overview of the Navicat Report Management Tools

  • Report Builder
  • Report Viewer
PostgreSQL Administration using Navicat
Training PostgreSQL Administration using Navicat

Onze voordelen :

  • Type of training: Inter-company, intra-company and individual
  • 100% flexible & personalised training : You choose the place, the dates and the training program
  • Offer request : Response within 24 hours
  • 50% discount for SME’s from Brussels-Capital Region
  • Free parking, lunch & drinks
  • Free use of our Digital Competence Centre: Manuals, courses, exercises, …