Course description

This course provides a thorough introduction to PostgreSQL, one of the most powerful open-source relational database management systems. Participants will learn to install and configure PostgreSQL and pgAdmin, create and manage databases and tables, and perform data manipulation tasks including insertion, updating, and deletion. The course covers essential SQL operations such as querying, filtering, and joining data, as well as advanced topics like user-defined functions, constraints, sequences, and aggregate functions. Additionally, learners will explore views, transactions, and data import/export techniques to manage and interact with their data effectively. Through practical exercises and real-world examples, this course equips participants with the skills needed to leverage PostgreSQL for robust data management and analysis.

Course objectives

By the end of this course, you will be able to:

• Learn to install PostgreSQL and configure the pgAdmin 4 client for effective database management.
• Understand how to create, modify, and drop databases and tables, including setting up user permissions and handling foreign keys.
• Gain proficiency in inserting, updating, deleting, and querying data using various SQL commands and functions.
• Explore PostgreSQL data types, conversion techniques, and how to use them effectively in table design and queries.
• Learn to apply and manage constraints (e.g., primary keys, foreign keys) and sequences for data integrity and auto-incrementing values.
• Develop skills in complex SQL operations such as joins, subqueries, and aggregate functions to analyze and manipulate data.
• Understand how to create, update, and use views, including materialized views, to simplify data access and enhance query performance.
• Master transaction management, including commit and rollback, and learn to export and import data for effective data transfer and backup.

Target groups

The course is designed for:

• Database Administrators: Individuals responsible for managing and maintaining database systems, who need to understand PostgreSQL's installation, configuration, and performance optimization.
• Data Analysts: Professionals who analyze and interpret data, and require skills in querying, manipulating, and visualizing data within PostgreSQL.
• Developers: Software developers who need to integrate PostgreSQL into their applications, manage database schemas, and write complex SQL queries.
• IT Professionals: IT staff involved in setting up and supporting database infrastructure, who need to learn about PostgreSQL's features and management tools.
• Students and Researchers: Individuals in academic or research settings who need to handle large datasets and perform advanced data analysis using PostgreSQL.

Course requirements

To get the best out of the course, the following will be required:

• Basic Computer Skills: Proficiency in using operating systems, file management, and basic software applications.
• Understanding of Databases: Familiarity with basic database concepts such as tables, schemas, and SQL queries.
• PostgreSQL Installation: PostgreSQL and pgAdmin must be installed on your computer before the course starts.
• Sample Data Files: Download and prepare sample data files provided for practical exercises and demonstrations.
• Basic Programming Knowledge (Optional): Some familiarity with programming concepts can be beneficial but is not required.

Course outline

1. Introduction to PostgreSQL
   1. Installing PostgreSQL on Windows
   2. Configure pgAdmin 4 client
   3. Creating a Database User
   4. Creating a Database
   5. Running a query in pgAdmin tool
   6. Install sample data files on server
   7. Drop a database
2. Creating and Modifying Tables
   1. Database structure
   2. Creating a database and table
   3. Creating a table with a foreign key
   4. Create a sample Database
   5. Using pgAdmin - Create and modify a table
   6. Using pgAdmin - View table structure, and create column
   7. Using pgAdmin - Rename, delete and change the data type of a column
   8. Deleting tables from a database
3. Modifying Data in Tables
   1. Insert a data into table
   2. Insert multiple records into a table
   3. Insert a data that had quotes
   4. Use RETURNING to get info on added rows
   5. Update data in a table
   6. Updating a row and returning the updated row
   7. Updating all records in a table
   8. Delete data from a table
   9. Using UPSERT
4. Querying Data
   1. Select all data from a table
   2. Selecting specific columns from a table
   3. Adding Aliases to columns in a table
   4. Using SELECT statement for expressions
   5. Using ORDER BY to sort records
   6. Using ORDER BY with alias column name
   7. Using ORDER BY to sort rows by expressions
   8. Using ORDER BY with column name or column number
   9. Using ORDER BY with NULL values
   10. Using DISTINCT for selecting distinct values
5. Filter Data
   1. Comparison, Logical and Arithmetic operators
   2. AND operator
   3. OR operator
   4. Combining AND, OR operators
   5. What goes before and after WHERE clause
   6. Execution order with AND, OR operators
   7. Can we use column aliases with WHERE?
   8. Order of execution of WHERE, SELECT and ORDER BY clauses
   9. Using Logical operators
   10. Using LIMIT and OFFSET
   11. Using FETCH
   12. Using IN, NOT IN
   13. Using BETWEEN and NOT BETWEEN
   14. Using LIKE and ILIKE
   15. Using IS NULL and IS NOT NULL keywords
   16. Concatenation techniques
   17. Concatenation with ||, CONCAT and CONCAT_WS
6. Data Types and Conversion
   1. Boolean
   2. CHAR, VARCHAR and TEXT
   3. NUMERIC
   4. DECIMALS
   5. Selecting Numbers data types
   6. Date/Time data types
   7. DATE
   8. TIME
   9. TIMESTAMP and TIMESTAMPTZ
   10. UUID
   11. Array
   12. hstore
   13. Network Addresses
   14. What is a data type conversion
   15. Using CAST for data conversions
   16. Implicit to Explicit conversions
   17. Table data conversion
   18. to_char
   19. to_number
   20. to_date
   21. to_timestamp
7. Creating User-Defined Functions
   1. CREATE DOMAIN - Create a DOMAIN data type, create an address
   2. CREATE DOMAIN - Create a postal code validation data type
   3. CREATE DOMAIN - Create a domain data type for an email validation
   4. CREATE DOMAIN - Create a Enum or Set of values domain data type
   5. CREATE DOMAIN - Get the list of all DOMAIN data types
   6. CREATE DOMAIN - How to drop a domain data type
   7. CREATE TYPE - Create a composite address object
   8. CREATE TYPE - Create a composite inventory_item data type
   9. CREATE TYPE - Create an ENUM data type and see how to drop a data type
   10. ALTER TYPE - Alter a composite data type, change schema and more
   11. ALTER TYPE - Alter an ENUM data type
   12. Update an ENUM data in production server
   13. An ENUM with a DEFAULT value in a table
   14. Create a type if not exists using a PL/pgSQL function
8. PostgreSQL Constraints
   1. Introduction to constraints
   2. NOT NULL constraint
   3. UNIQUE constraint
   4. DEFAULT constraint
   5. PRIMARY KEY Constraints
   6. PRIMARY KEY Constraints on multiple columns
   7. FOREIGN KEY Constraints
   8. Tables without foreign key constraints
   9. Creating foreign key constraints
   10. Foreign keys maintains referential data integrity
   11. Drop a constraint
   12. Add or update foreign key constraint on existing table
   13. CHECK constraint - An Introduction
   14. CHECK constraint - Add to new table
   15. CHECK constraint - Add, Rename, Drop on existing table
9. PostgreSQL Sequences
   1. Create a sequence, advance a sequence, get current value, set value
   2. Restart, rename a sequence, and use pgAdmin to alter a sequence
   3. Create a sequence with START WITH, INCREMENT, MINVALUE and MAXVALUE
   4. Create a sequence using a specific data type
   5. Creating a descending sequence, and CYCLE sequence
   6. Delete a sequence
   7. Attach a sequence to a table column
   8. List all sequences in a database
   9. Share one sequence between two tables
   10. Create an alphanumeric sequence
10. String Functions
    1. UPPER, LOWER and INITCAP
    2. LEFT and RIGHT
    3. REVERSE
    4. SPLIT_PART
    5. TRIM, BTRIM, LTRIM and RTRIM
    6. LPAD and RPAD
    7. LENGTH
    8. POSITION
    9. STRPOS
    10. SUBSTRING
    11. REPEAT
    12. REPLACE
11. Aggregate Functions
    1. Counting results via COUNT function
    2. COUNT(), COUNT(*) and COUNT(1)
    3. Sum with SUM function
    4. MIN and MAX functions
    5. GREATEST AND LEAST functions
    6. AVERAGE or AVG function
    7. STRING_AGG function
    8. GROUP BY clause
    9. Grouping records with ROLLUP
    10. Grouping records with CUBE
    11. Filtering groups using the HAVING clause
    12. Order of execution of WHERE, SELECT and GROUP BY
    13. Using GROUP BY and HAVING clauses
12. Joining Tables
    1. Introduction to Joins
    2. INNER JOIN
    3. LEFT JOIN
    4. RIGHT JOIN
    5. FULL OUTER JOIN
    6. CROSS JOIN
    7. Self-Join
    8. Union, Union All
    9. Intersect
    10. Except
13. Subqueries
    1. Introduction to Subqueries
    2. Single row and multiple row subqueries
    3. Find actors who worked in both movies
    4. Returning multiple columns from a subquery
    5. Subquery in SELECT clause
    6. Subquery in FROM clause
    7. Subquery with WHERE clause
    8. Subquery with HAVING clause
    9. Subquery with JOIN clause
    10. Subquery with EXISTS and NOT EXISTS
    11. Subquery with ANY, ALL and IN
    12. IN vs EXISTS
14. Views
    1. Create a view
    2. Query a view
    3. Creating a view with joins
    4. Creating a view with conditional expression
    5. Drop a view
    6. Update a view
    7. Altering a view
    8. Creating a recursive view
    9. Creating a materialized view
    10. Refreshing a materialized view
15. Transactions
    1. Start a transaction
    2. Commit a transaction
    3. Rollback a transaction
    4. Savepoint
    5. Autocommit
16. Exporting and Importing Data
    1. Exporting to a CSV file
    2. Exporting to a text file
    3. Exporting to a table
    4. Importing from a CSV file
    5. Importing from a text file
    6. Importing into a table