4.42 out of 5
4.42
408 reviews on Udemy

Learn SQL Using PostgreSQL: From Zero to Hero

Master PostgreSQL starting with basics to advanced features like triggers, PL/pgSQL functions, Window Functions and CTE
Instructor:
Will Bunker
3,424 students enrolled
English [Auto-generated]
Perform sophisticated queries
Join tables together
Use Group By to answer questions on aggregated date
Create and modifying tables
Create indexes to improve search speed
Put in constraints to keep data clean
Use pgAdmin tool
Learn recursive queries
How views make accessing data simpler
Subqueries
Sequences to auto increment fields
Common Table Expressions
Conditional Expressions
Window Functions
How to handle date and time data
SQL Functions
Transactions and Concurrency Control
PL/pgSQL Functions
Triggers
Composite Data Types
Array Data Types

Learning SQL was one of the most valuable skills I learned while building Match.  We had one of the largest instances of Microsoft SQL Server in the 90s with millions of records to keep the site running.  The better we got at SQL, the better we could make the site work for our users and answers sophisticated questions about our users.  

I want to teach you how to use PostgreSQL.  We will walk through

  • Basic selection statements

  • Joining multiple tables together

  • Grouping records to get aggregate data

  • Inserting, updating and deleting records

  • Creating tables and indexes

  • Subqueries to create sophisticated reports

  • Table constraints to keep data clean

  • Sequences to create auto incrementing fields

  • CTE – common table expressions that include recursive queries

  • Views to simply accessing complex queries

  • Conditional Expressions for queries

  • Window functions to combine regular queries with aggregate data

  • How to work with date, time and intervals

  • Create SQL Functions to capture complex statements

  • Create PL/pgSQL Functions that allow programming with if/then and loops

  • Triggers

  • Array data types

  • Composite data types

  • Transactions and concurrency control

Introduction

1
Introduction

Why learn SQL and what are the major databases.  Explains what tables, fields and rows are in the context of databases.

Installing PostgreSQL and pgAdmin

1
Installing PostgreSQL on Mac and Windows

How to install PostgreSQL using EnterpriseDB installer

2
Installing PostgreSQL on Ubuntu

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

3
Install Northwind Database

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

4
Install Some Additional Databases.

Add 3 more databases to learn from.

Simple Selection of All Records

1
Selecting All Data From a Table

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

2
Selecting Specific Fields

How to return specific fields when running a SELECT statement.

3
Selecting Distinct Values

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

4
Counting Results

Using COUNT statement to return the number of records.

5
Combining Fields in SELECT

How to derive information using more than one field.

6
Practice What You've Learned

Use pagila database to practice your basic SELECT.

Using WHERE to Select Records

1
What If You Don't Want All Records

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

2
Searching For Specific Text

How to select records based on matching text fields.

3
Searching Numeric Fields

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

4
Searching Date Fields

How to select records that have date fields.

5
WHERE Using Logical AND Operator

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

6
WHERE Using Logical OR Operator

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

7
WHERE Using Logical NOT Operator

Reverse the meaning of operator using logical NOT operator.

8
WHERE Combining AND, OR, and NOT

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

9
Using BETWEEN

Using BETWEEN to find values >= and <=.

10
Using IN

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

11
Practice What You've Learned

Use the usda resource to practice your WHERE clauses.

Schemas

1
Schema Basics

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

Using psql To Connect To Postgres

1
Connecting With psql

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

2
Eliminate Typing Connection Parameters

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

3
Databases In psql

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

4
Schemas In psql

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

Intermediate SELECT Statements

1
ORDER BY

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

2
Using MIN and MAX Functions

Find the smallest record with MIN and largest with MAX.

3
Using AVG and SUM

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

4
LIKE to Match Patterns

LIKE allows you to match text patterns for partial matches.

5
Renaming Columns With Alias

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

6
LIMIT to Control Number of Records Returned

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

7
NULL Values

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

8
Practice What You've Learned

Practice using AdventureWorks database.

Joining multiple tables together

1
Diagramming Table Relationships

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

2
Grabbing Information From Two Tables

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

3
Grabbing Information From Multiple Tables

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

4
Left Joins

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

5
Right Joins

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

6
Full Joins

Full joins pull all records from both tables.

7
Self Joins

Connect a table back to itself.

8
USING To Reduce Typing

Reduce typing with USING instead of ON in joins.

9
Even Less Typing With NATURAL

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

10
Practice What You've Learned

Practice joins using the AdventureWorks database.

Grouping and Aggregation Functions

1
Group By

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

2
Use HAVING to Filter Groups

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

3
Grouping Sets

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

4
Rollup

Using ROLLUP as a shortcut for complex GROUPING SET

5
Cube - Rollup On Steroids

CUBE creates all combinations of fields while grouping.

Combining Queries

1
Union

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

2
Intersect

Use INTERSECT to find records that are in both queries.

3
Except

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

Subqueries

1
Subquery Using EXISTS

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

2
Subquery Using ANY and ALL

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

3
IN Using Subquery

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

Modifying Data In Tables: INSERT, UPDATE and DELETE

1
INSERT INTO

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

2
UPDATE

Alter existing records using UPDATE statement.

3
DELETE

Remove data using the DELETE statement.

4
SELECT INTO

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

5
INSERT INTO SELECT

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

6
Returning Data From Update, Delete, and Insert

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

Indexes and Performance Tuning

1
What Are Indexes?

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

2
CREATE INDEX

Create indexes on tables that will result in faster searches.

3
DROP INDEX

Remove an existing index from a table.

4
How To Kill Runaway Queries

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

5
Using Explain To See Query Plan

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

6
Use Analyze To Update Table Statistics

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.

7
How Is Query Plan Cost Calculated

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

8
Using Indexes On More Than One Field

Learn how to properly use multi-column indexes. 

9
Expression Indexes

Make indexes on modified columns using expression indexes.

10
Types Of Indexes

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

11
Speeding Up Text Matching

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

Database Design and Normalization

1
Design Process Overview
2
Database Terminology
3
A Design Process

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

4
Finding Mission Statement And Ojectives

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

5
Analyzing Current Systems

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

6
Create Table Structure

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

7
Establishing Keys

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

8
Specifying Fields

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

9
Relationships Between Tables

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

10
Business Rules

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

11
Establish Needed Views

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

12
Double Checking Data Integrity

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

Creating and Modifying Tables

You can view and review the lecture materials indefinitely, like an on-demand channel.
Definitely! If you have an internet connection, courses on Udemy are available on any device at any time. If you don't have an internet connection, some instructors also let their students download course lectures. That's up to the instructor though, so make sure you get on their good side!
4.4
4.4 out of 5
408 Ratings

Detailed Rating

Stars 5
178
Stars 4
170
Stars 3
42
Stars 2
13
Stars 1
6
102aa66ea41fde004e7e7666286b17fe
30-Day Money-Back Guarantee

Includes

16 hours on-demand video
6 articles
Full lifetime access
Access on mobile and TV
Certificate of Completion