Duration
3 days
OVERVIEW
Postgres is a sophisticated open source relational database management system. Postgres supports the structured query language (SQL) with a wide variety of data types and support for triggers, a number of embedded programming languages, and full transactional processing.
This is a course for PostgreSQL database developers or users who need to manipulate data by interacting with PostgreSQL relational databases using Structured Query Language (SQL) and who need to progress their SQL skills beyond the basics. It is aimed at those who need to extract data from, and insert data into, existing PostgreSQL databases to an in-depth level and who also need to create and modify PostgreSQL databases and tables. The course is highly practical in nature and the focus throughout is on coding PostgreSQL-based SQL by hand. On completion, a comprehensive set of course notes, examples, tutor and attendee scripts are made available online for each attendee.
PROGRAM OBJECTIVES
Participants will learn
- Understand the features and benefits of postgresql
- Be able to install and start the postgresql server
- Understand the basics of Relational Databases
- Understand the SQL and postgresql languages
- Understand Data/Column types with regard to efficient database design
- Be able to view a database design structure/content
- Create a database design using an efficient structure
- Be able to extract basic database information using the SELECT command
- Be able to troubleshoot syntax and understand typical warnings and errors
- Be able to delete or modify a database, and table row data
- Grouping query data with Aggregation
- Connect data from multiple table rows using JOIN
- Perform nested sub-queries
- Use of Simple Functions (String, Date, Numerical)
- Describe postgresql connectors, their major features and differences
- Understand postgresql storage engines, transactions and features of the common engines
- Creating PLSQL stored procedure
- Creating Triggers
- Creating Views
- PostgreSQL built-in functions
WHO SHOULD ATTEND
This course is aimed at an audience of Software Developers, Database Analysts, Data Architects, and System Architects.
PREREQUISITE
PostgreSQL or relational database basic experience is required.
Day 1
- Getting started
- Querying Data
- Select statement
- Order By
- Select Distinct
- Where
- Limit
- In
- Between
- Like
- Joining multiple tables
- Inner Join
- Left Join
- Full Outer Join
- Cross Join
- Natural Join
- Grouping data
- Group By
- Having
- Performing set operations
- Union
- Intersect
- Except
- Sub-Query
- Modifying data
- Insert
- Update
- Update join
- Delete
- Upsert
- Import & export
- Import CSV file into Table
- Export PostgreSQL Table to CSV file
- Managing tables
- Create table
- Alter table
- Rename table
- Add column / Drop column
- Change column data type
- Rename column
- Drop table
- Truncate table
- Data types in depth
- Boolean, CHAR, VARCHAR and TEXT
- NUMERIC, Integer, SERIAL
- DATE, Timestamp, Interval, TIME
- UUID
- Array, hstore, JSON
- User-defined data types
- Understanding PostgreSQL constraints
- Primary key
- Foreign key
- CHECK constraint
- UNIQUE constraint
- NOT NULL constraint
- Conditional expressions & operators
- CASE
- COALESCE
- NULLIF
- CAST
Day 2
- Introduce to PostgreSQL stored procedures
Introduction
- Advantages
- Disadvantages
- Stored Procedure Syntax
- Develop the first user-defined function
- Creating a function using psql
- Creating a function using pgAdmin
- PL/pgSQL function parameters
- PL/pgSQL IN parameters
- PL/pgSQL OUT parameters
- PL/pgSQL INOUT parameters
- PL/pgSQL VARIADIC parameters
- PL/pgSQL function
- Overloading
- PL/pgSQL function overloading and default value
- PL/pgSQL function that returns a table
- PL/pgSQL block structure
- PL/pgSQL Subblock
- PL/pgSQL errors and messages
- PL/pgSQL reporting messages
- PL/pgSQL raising errors
- PL/pgSQL putting debugging checks using ASSERT Statement
- PL/pgSQL variables
- Copying data types
- Assigning alias to variables
- PL/pgSQL constants
- Declaring constant syntax
- PL/pgSQL constants example
- Conditional Statements
- Simple IF statement
- IF THEN ELSE statement
- Simple CASE statement
- Searched CASE statement
- PL/pgSQL loop statements
- FOR loop statement
- Looping through a query result
- Looping through a query result of a dynamic query
Day 3
- Aggregate functions
- AVG
- COUNT
- MAX
- MIN
- SUM
- Date functions
- AGE
- CURRENT_DATE, CURRENT_TIME
- DATE_PART, DATE_TRUNC
- EXTRACT
- LOCALTIME, LOCALTIMESTAMP
- NOW
- TO_DATE, TO_TIMESTAMP
- String functions
- CONCAT
- FORMAT
- INITCAP
- LEFT, RIGHT
- LENGTH
- LOWER, UPPER
- LPAD, RPAD
- LTRIM, RTRIM, TRIM
- POSITION
- REPLACE
- REVERSE
- SUBSTRING
- PL/pgSQL cursors
- Declaring cursors
- Opening cursors
- Using cursors
- Moving the cursor
- Deleting or updating row
- Closing cursors
- Trigger procedures using PL/pgSQL
- Introduction to PostgreSQL trigger
- Creating your first PostgreSQL trigger
- Managing PostgreSQL triggers Post
- PostgreSQL Views
- Managing PostgreSQL views
- Creating PostgreSQL updatable views
- PostgreSQL materialized views
- Creating updatable views using the WITH CHECK OPTION clause
- Creating recursive views
Programme scope, delivery arrangements and suitability are confirmed during enquiry.