Postgresql From Zero To Hero

Installing PostgreSQL on Mac Preview 02:47

How to install PostgreSQL using EnterpriseDB installer

Installing PostgreSQL on Windows Preview 05:58

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

Installing PostgreSQL on Ubuntu Preview 05:59

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

Install Northwind Database Preview 03:16

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

Install Some Additional Databases. Preview 00:19

Add 3 more databases to learn from.

Selecting All Data From a Table Preview 03:42

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

Selecting Specific Fields Preview 02:07

How to return specific fields when running a SELECT statement.

Selecting Distinct Values Preview 02:37

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

Counting Results Preview 02:48

Using COUNT statement to return the number of records.

Combining Fields in SELECT Preview 01:38

How to derive information using more than one field.

Practice What You've Learned Preview 00:43

Use pagila database to practice your basic SELECT.

What If You Don't Want All Records Preview 01:02

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

Searching For Specific Text Preview 02:18

How to select records based on matching text fields.

Searching Numeric Fields Preview 01:58

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

Searching Date Fields Preview 02:03

How to select records that have date fields.

WHERE Using Logical AND Operator Preview 02:44

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

WHERE Using Logical OR Operator Preview 02:56

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

WHERE Using Logical NOT Operator Preview 01:19

Reverse the meaning of operator using logical NOT operator.

WHERE Combining AND, OR, and NOT Preview 02:25

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

Using BETWEEN Preview 02:00

Using BETWEEN to find values >= and <=.

Using IN Preview 01:48

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

Practice What You've Learned Preview 01:19

Use the usda resource to practice your WHERE clauses.

Schema Basics Preview 03:12

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

Connecting With psql Preview 03:39

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

Eliminate Typing Connection Parameters Preview 05:38

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

Databases In psql Preview 01:45

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

Schemas In psql Preview 02:22

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

ORDER BY Preview 03:14

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

Using MIN and MAX Functions Preview 02:06

Find the smallest record with MIN and largest with MAX.

Using AVG and SUM Preview 02:05

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

LIKE to Match Patterns Preview 04:01

LIKE allows you to match text patterns for partial matches.

Renaming Columns With Alias Preview 03:24

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

LIMIT to Control Number of Records Returned Preview 03:21

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

NULL Values Preview 02:22

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 What You've Learned Preview 01:06

Practice using AdventureWorks database.

Diagramming Table Relationships Preview 03:10

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

Grabbing Information From Two Tables Preview 05:50

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

Grabbing Information From Multiple Tables Preview 05:07

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

Left Joins Preview 03:25

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

Right Joins Preview 03:17

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

Full Joins Preview 02:25

Full joins pull all records from both tables.

Self Joins Preview 06:02

Connect a table back to itself.

USING To Reduce Typing Preview 01:39

Reduce typing with USING instead of ON in joins.

Even Less Typing With NATURAL Preview 04:19

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

Practice What You've Learned Preview 01:04

Practice joins using the AdventureWorks database.

Group By Preview 08:18

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

Use HAVING to Filter Groups Preview 05:32

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

Grouping Sets Preview 07:00

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

Rollup Preview 06:16

Using ROLLUP as a shortcut for complex GROUPING SET

Cube - Rollup On Steroids Preview 06:55

CUBE creates all combinations of fields while grouping.

Union Preview 03:24

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

Intersect Preview 04:24

Use INTERSECT to find records that are in both queries.

Except Preview 03:53

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

Subquery Using EXISTS Preview 08:43

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

Subquery Using ANY and ALL Preview 06:24

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

IN Using Subquery Preview 01:41

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

INSERT INTO Preview 07:59

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

UPDATE Preview 03:28

Alter existing records using UPDATE statement.

DELETE Preview 02:10

Remove data using the DELETE statement.

SELECT INTO Preview 02:48

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

INSERT INTO SELECT Preview 02:39

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

Returning Data From Update, Delete, and Insert Preview 07:56

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

What Are Indexes? Preview 02:32

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

CREATE INDEX Preview 04:23

Create indexes on tables that will result in faster searches.

DROP INDEX Preview 02:01

Remove an existing index from a table.

How To Kill Runaway Queries Preview 03:04

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

Using Explain To See Query Plan Preview 09:12

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

Use Analyze To Update Table Statistics Preview 03:26

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.

How Is Query Plan Cost Calculated Preview 12:17

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

Using Indexes On More Than One Field Preview 05:39

Learn how to properly use multi-column indexes. 

Expression Indexes Preview 06:05

Make indexes on modified columns using expression indexes.

Types Of Indexes Preview 05:04

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

Speeding Up Text Matching Preview 04:35

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

Design Process Overview Preview 07:52

Database Terminology Preview 08:34

A Design Process Preview 05:15

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

Finding Mission Statement And Ojectives Preview 03:32

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

Analyzing Current Systems Preview 03:35

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

Create Table Structure Preview 05:44

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

Establishing Keys Preview 03:20

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

Specifying Fields Preview 04:17

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

Relationships Between Tables Preview 04:52

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

Business Rules Preview 02:47

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

Establish Needed Views Preview 01:53

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

Double Checking Data Integrity Preview 02:40

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

CREATE TABLE Preview 09:59

How to create tables and use basic data types for fields.

ALTER TABLE - Part One Preview 03:46

Learn how to rename a field in a table and rename the table itself.

ALTER TABLE - Part Two Preview 04:48

Learn how to add and remove a field from a table.

ALTER TABLE - Part Three Preview 02:56

Change the data type of an existing field in a table.

DROP TABLE Preview 01:34

Remove an existing table.

NOT NULL Constraint Preview 05:15

Prevent NULL values to a field by adding NOT NULL constraint.

UNIQUE Constraint Preview 03:45

Ensure that all the values inserted into a field are unique.

PRIMARY KEY Constraint Preview 05:54

Primary key constraint is a combination of NOT NULL and UNIQUE.

FOREIGN KEY Constraint Preview 07:00

Foreign keys make sure that a field's value is present in another table.

CHECK Constraint Preview 05:29

Use CHECK Constraints to test if a value matches a condition before the data is added to the table.

DEFAULT Values Preview 02:43

Use DEFAULT Constraints to provide values if nothing is present in INSERT statement.

Changing a Column's Default Value Preview 03:51

How to add, change or delete a default on an existing table column.

Adding and Removing a Column's Constraint Preview 10:23

Change constraints on existing tables with different ALTER TABLE statements.

Create A Sequence Preview 14:31

Learn what a sequence is used for and how to create.

Alter and Delete Sequences Preview 05:22

Learn how to modify existing sequences with ALTER SEQUENCE and delete sequences with DROP SEQUENCE.

Using Serial Datatypes Preview 04:07

Create fields with SERIAL datatype that automatically sets up sequence to increase id field.

WITH Queries Preview 08:58

Create a basic CTE (Common Table Expressions) using WITH AS syntax.

Using CTE to Grab Identity Field From Insert Preview 04:53

Use WITH syntax to grab id field from INSERT statement and use in further inserts to linked tables.

Creating Hierarchical Data To Use For Recursive WITH Queries Preview 01:06

We need to add more employee records, so we can demonstrate recursive WITH queries.

Using Recursion in CTEs Preview 10:54

You will learn how to use recursion to drill down in hierarchical data like company organizational charts.

Views - How To Create Preview 09:13

Learn why views are used and how to create and query with them.

Views - How To Modify Preview 05:03

How to add fields to an existing view and change their name.

Creating Updatable Views Preview 08:12

Not every view is updatable, you will learn how to create views that can be modified.

With Check Option Preview 06:43

Use WITH CHECK OPTION to prevent bad data from being entered into your updatable field.

Deleting Views Preview 01:35

Teaches how to remove views using SQL.

CASE WHEN Preview 11:00

CASE WHEN allows you to perform IF THEN type logic in your SELECT statements

COALESCE Preview 02:25

Use COALESCE to return first non-null value.

NULLIF Preview 04:23

Learn to use NULLIF to handle empty strings.

Date, Time, and Timestamp Data Types Preview 12:41

Learn how to use date, time, timestamp data types.  Also will cover how to set DateStyle which controls the default order of month and day in dates.

Time Zones Preview 08:12

Learn the different ways to input Timestamps and Time with Time Zones.  Also, learn how to see what time zone your session is in and how to change it.

Interval Data Type Preview 09:49

Learn how to define interval data types.  Also, learn the different formats intervals can be created in, including ISO 8601, SQL Standard, and Postgres.  Learn how to set the output interval style.

Date Arithmetic Preview 12:28

How to handle arithmetic with dates, times, timestamps, and intervals.

Pulling Out Parts of Dates and Times Preview 03:47

How to pull out the different parts of date, time, and timestamp fields.

Converting One Data Type Into Another Preview 03:20

Learn to use CAST and :: operator to convert from one data type to another.

Basic Window Function Example Preview 09:09

Run basic window functions to get a feel for how they work.

Using Window Functions With Subqueries Preview 12:54

Subqueries combined with windows is a powerful combination.  It allows you to aggregate information and then filter with an outer WHERE clause.

Using Rank() To Find The First N Records In Join Preview 07:12

Learn how to return the first N records from a join relationship.  This can't be done with limit and requires the window function rank().

Composite Type Basics Preview 08:08

Learn what composite types are, how to create, use in a table and delete.

Using Composite Types Preview 08:50

You will learn how to build a composite type for INSERT and DELETE statement.  Also, how to grab individual fields in SELECT statements and use in WHERE clauses.

Write Your First Function Preview 06:48

Write the most simple SQL functions that take no parameters and don't return values.

Write A Function That Returns A Single Value Preview 04:35

Learn how to return a single value from your functions.

Functions With Parameters Preview 09:38

Create functions that take parameters for more sophisticated behavior.

Functions That Have Composite Parameters Preview 06:28

How to pass in a composite data type to a function.  Can be used to pass a single row of a table or user defined composites.

Functions That Return A Composite Preview 05:54

Learn how to return a composite type from a function and access in a select statement.  Used mainly to return a single row of a table.

Functions With Output Parameters Preview 04:51

Use IN, OUT, and INOUT on your parameters.  Out parameters can replace return or label the returns of function.

Functions With Default Values Preview 04:43

Learn to use defaults on your input parameters for functions.

Using Functions as Table Source Preview 02:29

You can use functions in the FROM clause of select statements.

Functions That Return More Than One Row Preview 15:56

Use RETURNS SETOF to return multiple rows from a function.

Procedures - Functions That Don't Return Anything Preview 04:35

Learn how to create procedures which are similar to functions but don't return any values.

ACID Transactions Preview 02:59

Learn how databases use the transaction concept and what ACID transactions are.

Simple Transaction Control Preview 05:17

Use BEGIN and COMMIT or END to create transactions from multiple statements.

Rollbacks & Savepoints Preview 06:13

Use SAVEPOINT and ROLLBACK to cancel all or part of any transaction.

SQL Transaction Isolation Preview 04:04

Learn about the SQL standards around transaction isolation levels including dirty reads, nonrepeatable reads, and phantom reads.

PostgreSQL Transaction Isolation Preview 03:33

Cover how isolation levels are handled and specified in PostgreSQL.

Declaring Arrays Preview 04:09

How to declare arrays using both SQL standard and PostgreSQL syntax.

Inputting Array Values Preview 05:21

How to create array values with '{ }' and ARRAY[ ] syntax.

Accessing Arrays Preview 04:04

How to grab individual elements from an array and ranges of elements.

Modifying Arrays Preview 05:38

You will learn how to replace the whole array, update a single element, or a range of elements.

Searching Arrays Preview 03:16

How to search arrays using ANY and ALL.

Array Operators Preview 06:30

Learn how to compare operators with =, >, <, <>, <=, and >=.  Also learn about the contains operators @> and <@.  Finish by learning about overlaps operator @@.

Build Your First PL/pgSQL Function Preview 06:37

Create your first PL/pgSQL function that returns a single value using RETURN.  Learn about the block structure using BEGIN/END.

Handling Functions With Output Variables Preview 04:59

Learn how assignment works and use it to return values using OUT parameters.

Returning Query Results Preview 06:25

Use RETURNS QUERY to return full query results from your PL/pgSQL function.

Declaring Variables Preview 11:29

Learn how to use DECLARE section to create extra variables.

Looping Through Query Results Preview 13:28

Learn how to go through each row returns from a query and process the rows in your function using FOR IN syntax.

Using If-Then Statements Preview 08:10

Learn how to use IF-THEN, IF-THEN-ELSE, and IF-THEN-ELSIF statements to run statements conditionally.

Returning Query Results Continued Preview 10:50

Learn how to build a result set using RETURN NEXT, and how to do further processing after RETURN QUERY.

Loop and While Loops Preview 05:54

Learn 2 new looping techniques: LOOP and WHILE LOOP.  Learn  how to leave a loop with EXIT and skip to top with CONTINUE.

Looping Over Array Elements Preview 10:37

Learn to use FOREACH IN LOOP to iterate through array elements in a function.

Build Your First Trigger Preview 11:07

Learn how to make functions that work with triggers and create your first trigger to update a timestamp on update and insert statements.

Statement Triggers Preview 21:30

Use statement triggers to create an audit table.  These triggers fire once per every INSERT, DELETE or UPDATE statement instead of for each row affected.

Importing CSV Preview 13:33

Use the \copy command in the psql command line to import 2 CSV files.

Practice What You've Learned Preview 00:21

Import the remaining CSV files using psql and \copy command.

What Is JSON And How To Store In Database Preview 07:02

Learn what JSON is and the difference between json and jsonb data types.  Input and select out JSON data from a books table.

Create JSON From Tables Preview 12:04

Use jsonb_build_object, string_to_array, and to_jsonb to create JSON from tables.

Aggregating JSON Fields Preview 07:38

Learn how to use JSONB_AGG combined with to_jsonb and a subquery to create a JSON array from multi-row results.

Building airports_json Table Preview 08:28

Pull all the data from the airport database into a single JSON table airports_json

Selecting Information Out of JSON Fields Preview 06:08

Learn to use ->, ->>, and #> to select information out of jsonb fields.

Searching JSON Data Preview 06:04

Learn how to use ->, ->>, and @> to search jsonb columns.

Updating And Deleting Information Inside JSON Fields Preview 09:39

Use || to add and updated fields in JSON data.  Delete fields using - or #- operators.

CREATE DATABASE Preview 03:02

Create new databases using CREATE DATABASE and pgAdmin GUI.

DROP DATABASE Preview 03:09

Remove a database using DROP DATABASE and pgAdmin GUI.

Basic Import/Export With Copy Preview 08:51

Master COPY command for moving data into and out of tables.

Basic pg_dump And Restore Preview 04:06

Backup databases using pg_dump and replay with psql to restore.

Custom Format Dumps Preview 06:36

Use the -Fc switch on pg_dump to store in a custom format that saves space and allows partial restoration.  Learn how to use pg_restore to restore from custom format files.

Overview of Roles and Users Preview 03:15

Learn about 6 layers of security in PostgreSQL: instance, database, schema, table, column and row.  Understand how roles, users, and groups work.

Instance Level Security Preview 05:52

How to create users/roles that can login, roles for job functionality and add your users to functional roles.

Database Level Security Preview 02:36

How to set permissions at the database level, including removing public permissions so not everyone can connect.

Schema Level Security Preview 03:48

How to control access to the schemas in your database including removing permissions for public from the public schema.

Table Level Security Preview 02:14

Master permissions for CRUD operations, as well as, truncate, references and trigger creation.

Column Level Security Preview 05:04

Control which columns each role can access to protect privacy and prevent employee fraud.

Row Level Security Preview 08:31

Learn how to turn on row level security and use policies to grant permissions.

Install PostGIS on Ubuntu (Not needed for Windows/Mac) Preview 01:10

Install PostGIS on Ubuntu using apt-get

Converting Airport Data Preview 09:26

Add geometrical point fields to the airport database and convert existing longitude/latitude data into PostGIS format.

Your First Geographical Query Preview 08:42

Query data based on how close two points are in the real world using GiST indexes and ST_DWithin function.

Thank you Preview 00:26