Learn SQL Using PostgreSQL: From Zero to Hero
4.4 (1,376 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
8,526 students enrolled

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
4.4 (1,376 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
8,526 students enrolled
Created by Will Bunker
Last updated 5/2020
English
English [Auto]
Current price: $86.99 Original price: $124.99 Discount: 30% off
5 hours left at this price!
30-Day Money-Back Guarantee
This course includes
  • 16 hours on-demand video
  • 6 articles
  • 159 downloadable resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
Training 5 or more people?

Get your team access to 4,000+ top Udemy courses anytime, anywhere.

Try Udemy for Business
What you'll learn
  • 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
Course content
Expand all 181 lectures 16:07:46
+ Introduction
1 lecture 05:04

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

Preview 05:04
+ Installing PostgreSQL and pgAdmin
4 lectures 12:21

How to install PostgreSQL using EnterpriseDB installer

Preview 02:47

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

Preview 05:59

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

Preview 03:16

Add 3 more databases to learn from.

Preview 00:19
+ Simple Selection of All Records
6 lectures 13:35

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

Preview 03:42

How to return specific fields when running a SELECT statement.

Preview 02:07

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

Selecting Distinct Values
02:37

Using COUNT statement to return the number of records.

Counting Results
02:48

How to derive information using more than one field.

Combining Fields in SELECT
01:38

Use pagila database to practice your basic SELECT.

Practice What You've Learned
00:43
+ Using WHERE to Select Records
11 lectures 21:52

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

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

How to select records based on matching text fields.

Searching For Specific Text
02:18

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

Searching Numeric Fields
01:58

How to select records that have date fields.

Searching Date Fields
02:03

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

WHERE Using Logical AND Operator
02:44

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

WHERE Using Logical OR Operator
02:56

Reverse the meaning of operator using logical NOT operator.

WHERE Using Logical NOT Operator
01:19

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

WHERE Combining AND, OR, and NOT
02:25

Using BETWEEN to find values >= and <=.

Using BETWEEN
02:00

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

Using IN
01:48

Use the usda resource to practice your WHERE clauses.

Practice What You've Learned
01:19
+ Schemas
1 lecture 03:12

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

Schema Basics
03:12
+ Using psql To Connect To Postgres
4 lectures 13:24

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

Connecting With psql
03:39

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

Eliminate Typing Connection Parameters
05:38

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

Databases In psql
01:45

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

Schemas In psql
02:22
+ Intermediate SELECT Statements
8 lectures 21:39

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

ORDER BY
03:14

Find the smallest record with MIN and largest with MAX.

Using MIN and MAX Functions
02:06

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

Using AVG and SUM
02:05

LIKE allows you to match text patterns for partial matches.

LIKE to Match Patterns
04:01

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

Renaming Columns With Alias
03:24

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

LIMIT to Control Number of Records Returned
03:21

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

NULL Values
02:22

Practice using AdventureWorks database.

Practice What You've Learned
01:06
+ Joining multiple tables together
10 lectures 36:18

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

Diagramming Table Relationships
03:10

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

Grabbing Information From Two Tables
05:50

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

Grabbing Information From Multiple Tables
05:07

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

Left Joins
03:25

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

Right Joins
03:17

Full joins pull all records from both tables.

Full Joins
02:25

Connect a table back to itself.

Self Joins
06:02

Reduce typing with USING instead of ON in joins.

USING To Reduce Typing
01:39

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

Even Less Typing With NATURAL
04:19

Practice joins using the AdventureWorks database.

Practice What You've Learned
01:04
+ Grouping and Aggregation Functions
5 lectures 34:01

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

Group By
08:18

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

Use HAVING to Filter Groups
05:32

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

Grouping Sets
07:00

Using ROLLUP as a shortcut for complex GROUPING SET

Rollup
06:16

CUBE creates all combinations of fields while grouping.

Cube - Rollup On Steroids
06:55
+ Combining Queries
3 lectures 11:41

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

Union
03:24

Use INTERSECT to find records that are in both queries.

Intersect
04:24

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

Except
03:53
Requirements
  • Basic computer skills.
  • Ability to install software on system.
  • Computer that can run PostgreSQL ( I will show you how to install ).
Description

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

Who this course is for:
  • Someone wanting to master PostgreSQL and SQL query language
  • Anyone wanting to learn advanced query expressions