PostgreSQL Course Contents

(4 Days)

Day 1

 Querying Data

• Select – show you how to query data from a single table.
• Column aliases – learn how to assign temporary names to columns or expressions in a query.
• Order By – guide you on how to sort the result set returned from a query.
• Select Distinct  – provide you with a clause that removes duplicate rows in the result set.

Filtering Data

• Where – filter rows based on a specified condition.
• Limit – get a subset of rows generated by a query.
• Fetch– limit the number of rows returned by a query.
• In – select data that matches any value in a list of values.
• Between – select data that is a range of values.
• Like – filter data based on pattern matching.
• Is Null – check if a value is null or not.

Joining Multiple Tables

• Joins – show you a brief overview of joins in PostgreSQL.
• Table aliases – describes how to use table aliases in the query.
• Inner Join – select rows from one table that has the corresponding rows in other tables.
• Left Join – select rows from one table that may or may not have the corresponding rows in other tables.
• Self-join – join a table to itself by comparing a table to itself.
• Full Outer Join – use the full join to find a row in a table that does not have a matching row in another table.
• Cross Join – produce a Cartesian product of the rows in two or more tables.
• Natural Join – join two or more tables using implicit join conditions based on the common column names in the joined tables.

 

 

Day 2

Grouping Data

• Group By – divide rows into groups and applies an aggregate function on each.
• Having – apply conditions to groups.

Set Operations

• Union – combine result sets of multiple queries into a single result set.
• Intersect – combine the result sets of two or more queries and returns a single result set that has the rows appear in both result sets.
• Except – return the rows in the first query that does not appear in the output of the second query.

Grouping sets, Cube, and Rollup

• Grouping Sets  – generate multiple grouping sets in reporting.
• Cube – define multiple grouping sets that include all possible combinations of dimensions.
• Rollup – generate reports that contain totals and subtotals.

Subquery

• Subquery – write a query nested inside another query.
• ANY  – retrieve data by comparing a value with a set of values returned by a subquery.
• ALL – query data by comparing a value with a list of values returned by a subquery.
• EXISTS  – check for the existence of rows returned by a subquery.

Common Table Expressions

• PostgreSQL CTE – introduce you to PostgreSQL common table expressions or CTEs.
• Recursive query using CTEs – discuss the recursive query and learn how to apply it in various contexts.

Modifying Data

• Insert – guide you on how to insert a single row into a table.
• Insert multiple rows – show you how to insert multiple rows into a table.
• Update – update existing data in a table.
• Update join – update values in a table based on values in another table.
• Delete – delete data in a table.
• Upsert – insert or update data if the new row already exists in the table.

Day 3

Transactions

• PostgreSQL Transactions – show you how to handle transactions in PostgreSQL using BEGIN, COMMIT, and ROLLBACK statements.

 Import & Export Data

You will learn how to import and export PostgreSQL data from and to CSV file format using the copy command.

• Import CSV file into Table – show you how to import CSV file into a table.
• Export PostgreSQL Table to CSV file – show you how to export tables to a CSV file.

Managing Tables

• Data types – cover the most commonly used PostgreSQL data types.
• Create a table – guide you on how to create a new table in the database.
• Select Into & Create table as– shows you how to create a new table from the result set of a query.
• Auto-increment column with SERIAL – uses SERIAL to add an auto-increment column to a table.
• Sequences – introduce you to sequences and describe how to use a sequence to generate a sequence of numbers.
• Identity column – show you how to use the identity column.
• Alter table – modify the structure of an existing table.
• Rename table – change the name of the table to a new one.
• Add column – show you how to use add one or more columns to an existing table.
• Drop column – demonstrate how to drop a column of a table.
• Change column data type – show you how to change the data of a column.
• Rename column – illustrate how to rename one or more columns of a table.
• Drop table – remove an existing table and all of its dependent objects.
• Truncate table – remove all data in a large table quickly and efficiently.
• Temporary table – show you how to use the temporary table.
• Copy a table – show you how to copy a table to a new one.

 Understanding PostgreSQL Constraints

• Primary key – illustrate how to define a primary key when creating a table or adding a primary key to an existing table.
• Foreign key – show you how to define foreign key constraints when creating a new table or add foreign key constraints for existing tables.
• CHECK constraint – add logic to check value based on a Boolean expression.
• UNIQUE constraint – make sure that values in a column or a group of columns are unique across the table.
• NOT NULL constraint – ensure values in a column are not NULL.

PostgreSQL Data Types in Depth

• Boolean – store TRUE and FALSE values with the Boolean data type.
• CHAR, VARCHAR and TEXT – learn how to use various character types including CHAR, VARCHAR, and TEXT.
• NUMERIC – show you how to use NUMERIC type to store values that precision is required.
• Integer – introduce you to various integer types in PostgreSQL including SMALLINT, INT and BIGINT.
• DATE  – introduce the DATE data type for storing date values.
• Timestamp – understand timestamp data types quickly.
• Interval – show you how to use interval data type to handle a period of time effectively.
• TIME – use the TIME datatype to manage the time of day values.
• UUID – guide you on how to use UUID datatype and how to generate UUID values using supplied modules.
• Array – show you how to work with the array and introduces you to some handy functions for array manipulation.
• hstore – introduce you to data type which is a set of key/value pairs stored in a single value in PostgreSQL.
• JSON – illustrate how to work with JSON data type and shows you how to use some of the most important JSON operators and functions.
• User-defined data types – show you how to use the CREATE DOMAIN and CREATE TYPE statements to create user-defined data types.

 

Day 4

Conditional Expressions & Operators

• CASE – show you how to form conditional queries with CASE expression.
• COALESCE – return the first non-null argument. You can use it to substitute NULL by a default value.
• NULLIF – return NULL if the first argument equals the second one.
• CAST – convert from one data type into another e.g., from a string into an integer, from a string into a date.

 

PostgreSQL Utilities

• psql commands – show you the most common psql commands that help you interact with psql faster and more effectively.

PostgreSQL Recipes

• How to compare two tables – describe how to compare data in two tables in a database.
• How to delete duplicate rows in PostgreSQL – show you various ways to delete duplicate rows from a table.
• How to generate a random number in a range  – illustrate how to generate a random number in a specific range.
• EXPLAIN statement– guide you on how to use the EXPLAIN statement to return the execution plan of a query.
• PostgreSQL vs. MySQL – compare PostgreSQL with MySQL in terms of functionalities.
• Using PostgreSQL with NodeJS
• Using PostgreSQL with PHP

Case Study, Live Project & Post Assessment