How to install PostgreSQL using EnterpriseDB installer

How to install PostgreSQL on Windows with pgAdmin 4 and psql tools

Get up and running with PostgreSQL 11 and pgAdmin 4 on Ubuntu.

How to install Northwind database using pgAdmin using the restore feature and northwind.tar file.

Add 3 more databases to learn from.

How to select all information from a table using simple SELECT statement.

How to return specific fields when running a SELECT statement.

If you want to find all the unique values in a specific field in table, you use the DISTINCT keyword.

Using COUNT statement to return the number of records.

How to derive information using more than one field.

Use pagila database to practice your basic SELECT.

Learn about the WHERE clause and how it is used to narrow down the number of records returned.

How to select records based on matching text fields.

How to use WHERE with numeric fields with =, >, >=, <, and <= 

How to select records that have date fields.

Using AND to select records where all conditions must be true.

You can select records where any of the conditions are true using OR operator.

Reverse the meaning of operator using logical NOT operator.

Using parenthesis to create more complicated queries that combine logical operators.

Using BETWEEN to find values >= and <=.

If you have a long list of values the IN operator is easier to understand and read.

Use the usda resource to practice your WHERE clauses.

Learn what schemas are and how to use tables that are located in schemas.

Use psql command line to connect to your local database and run commands.

Remove inputing connection parameters for command line tools by using environment variables, a .pgpass file, or .pg_service.conf file.

Learn how to what databases are present and connect to them in psql.

How to list the schemas and see the tables in a schema using psql.

If you need the results to be returned in a specific order, use ORDER BY.

Find the smallest record with MIN and largest with MAX.

Use these function to find the average value or the sum of all the values.

LIKE allows you to match text patterns for partial matches.

You can change the name of a column with AS syntax.

If you only need a certain number of records use LIMIT to control.

Nulls are a special value to indicate an unknown.  Learn how to use IS NULL and IS NOT NULL to select based on NULLs.

Practice using AdventureWorks database.

How to map out the tables and relationships in a database with diagrams.

How to pull information from 2 different tables in a single statement using JOIN.

Use multiple JOIN statements to pull together 3 or more tables.

Left joins allow you to pull all records from first table and any matching records from second table.

Right joins allow you to pull matching records from first table and all records from second table.

Full joins pull all records from both tables.

Connect a table back to itself.

Reduce typing with USING instead of ON in joins.

NATURAL joins combine tables where fields are named the same in each table.

Practice joins using the AdventureWorks database.

GROUP BY allows you to aggregate records and perform an aggregate function like AVG.

HAVING clause lets you filter out results of your GROUP BY results.

Use GROUPING SETS to group by multiple fields separately in a single query.

Using ROLLUP as a shortcut for complex GROUPING SET

CUBE creates all combinations of fields while grouping.

UNION allows you to combine 2 or more queries into a single result.

Use INTERSECT to find records that are in both queries.

Use EXCEPT to only bring back records from first query that are not in the second query.

EXISTS subqueries allow you to check a condition in another table as part of the criteria to return a record.

Find records that return if any or all of the subquery match the condition.

You can use a subquery with IN operator to dynamically build list.

You will learn how to insert new data into a table.

Alter existing records using UPDATE statement.

Remove data using the DELETE statement.

Create a new table based on records returned from select statement.

Insert records into an existing table by selecting from another table.

Using RETURNING to bring back data after INSERT, UPDATE, or DELETE.

Learn what indexes are, what they help with and the drawbacks of too many indexes.

Create indexes on tables that will result in faster searches.

Remove an existing index from a table.

Find running queries using pg_stat_activity table and cancel them with pg_cancel_backend.

We will create a large table and demonstrate how EXPLAIN works.  Then see the difference when an index is added.

Learn to use EXPLAIN ANALYZE to see actual performance versus the prediction by the query analyzer.  Use ANALYZE table_name to update the table statistics.

Learn how PostgreSQL uses calculates the query plan cost by estimated disk I/O and CPU usage for the query.

Learn how to properly use multi-column indexes. 

Make indexes on modified columns using expression indexes.

Learn about B-Tree, Hash, GIN, GiST, BRIN, and SP-GiST indexes and what situations are best for each type.

Use a GIN index with gin_trgm_ops to speed up text matching in the middle of text for LIKE '%some%' operations.

Learn 7 steps to go through in order to design a database.

Conduct interviews with employees and management to find out main purpose and tasks the database should support.

Learn how to analyze the current paper and database systems in a business and turn into tables and field lists.

Take the interviews and information collected in previous step and identify tables and fields that will go in new database.

Learn what makes a good primary key and how to find or create one for every table.

Document and specify all the fields in database, including descriptions, uniqueness, data type, and length.

Create a table matrix to map out the relationships between tables. Make an ER diagram to communicate to others what the relationships are.

Learn about different types of business rules and how to document them.

Learn what views are used for and how to map out needed views.

The final step is reviewing data integrity and pulling documentation into a single source.