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, …
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s