After the course, the participant will be able to:

• Connect to a database using a client application,
• Perform simple queries to display data from single tables,
• Use single line functions to transform and convert data and group functions to aggregate entire sets as well as groups of records,
• Formulate complex queries involving joins of multiple tables and simple and correlated,
• Use aliases for columns and tables,
• Use subqueries for processing complex queries
• Manipulate data in tables: enter, modify and delete data,
• Use subqueries in manipulating data,
• Use transactions to approve or reject the effects of editing data.

The training is designed for people who use databases as a source of information when constructing a variety of reports, analyzing data and developing applications.

The participants of the training are required to have basic knowledge of MS Windows environment.

2 days, 16 training hours

The training is conducted in the form of consecutive training sessions divided into modules. Within a training module there is a theoretical part explaining a given issue, collectively and independently solved by the participant exercise and a block of questions and answers.

Presentation of the objectives and scope of the training
Licensing principles - open source
Features distinguishing PostgreSQL from other database servers
PgAdmin application
Basics of the relational model of data organization
Concept of a database server
Server objects:

• Bases
• Tablespaces
• Schemas
• Roles

SQL language - selecting the data
Overlay software for performing queries
Creating simple queries

• Keywords
• Command syntax
• Using aliases for columns and tables

Sorting data
Using functions in queries
Single line functions
Group functions
Filtering on aggregation results
Selecting data from multiple tables

• Ways of joining tables
• Types of joins

Equi-join
Non-equi-join
Outer-join:

• left-joined,
• right-handed,
• both sides

Self-join
Collective exercises
Bulk operations on query results

• Sum
• Intersection
• Difference
• Subqueries
• The role of subqueries
• Query decomposition
• Location of subqueries

Simple subqueries
Multiple times nested subqueries
Correlated subqueries
Data Manipulation Language (DML)

• Insert
• Update
• Delete

DML operations based on query results
Transactions

• The idea of transactivity of operations
• Commit
• Rollback
• Savepoint

Repetition of material on key operations in SQL
Additional questions from participants
Training summary
Test
Survey

After the training, it is recommended to attend:

• SQL – the language of access to database Oracle
• Oracle SQL language for Data Analysts 
• https://www.postgresql.org/docs/12/index.html