Learn SQL Using PostgreSQL: From Zero to Hero

Name: Learn SQL Using PostgreSQL: From Zero to Hero
Rating: 4.5 (3822 reviews)

Master PostgreSQL starting with basics to advanced features like triggers, PL/pgSQL functions, Window Functions and CTE

Created byWill Bunker

Last updated 9/2021

English

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

35 sections • 182 lectures • 16h 13m total length

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

Installing PostgreSQL on Mac2:47
How to install PostgreSQL using EnterpriseDB installer
Installing PostgreSQL on Windows5:58
How to install PostgreSQL on Windows with pgAdmin 4 and psql tools
Installing PostgreSQL on Ubuntu5:59
Get up and running with PostgreSQL 11 and pgAdmin 4 on Ubuntu.
Install Northwind Database3:16
How to install Northwind database using pgAdmin using the restore feature and northwind.tar file.
Install Some Additional Databases.0:19
Add 3 more databases to learn from.

Selecting All Data From a Table3:42
How to select all information from a table using simple SELECT statement.
Selecting Specific Fields2:07
How to return specific fields when running a SELECT statement.
Selecting Distinct Values2:37
If you want to find all the unique values in a specific field in table, you use the DISTINCT keyword.
Counting Results2:48
Using COUNT statement to return the number of records.
Combining Fields in SELECT1:38
How to derive information using more than one field.
Practice What You've Learned0:43
Use pagila database to practice your basic SELECT.

What If You Don't Want All Records1:02
Learn about the WHERE clause and how it is used to narrow down the number of records returned.
Searching For Specific Text2:18
How to select records based on matching text fields.
Searching Numeric Fields1:58
How to use WHERE with numeric fields with =, >, >=, <, and <=
Searching Date Fields2:03
How to select records that have date fields.
WHERE Using Logical AND Operator2:44
Using AND to select records where all conditions must be true.
WHERE Using Logical OR Operator2:56
You can select records where any of the conditions are true using OR operator.
WHERE Using Logical NOT Operator1:19
Reverse the meaning of operator using logical NOT operator.
WHERE Combining AND, OR, and NOT2:25
Using parenthesis to create more complicated queries that combine logical operators.
Using BETWEEN2:00
Using BETWEEN to find values >= and <=.
Using IN1:48
If you have a long list of values the IN operator is easier to understand and read.
Practice What You've Learned1:19
Use the usda resource to practice your WHERE clauses.

Connecting With psql3:39
Use psql command line to connect to your local database and run commands.
Eliminate Typing Connection Parameters5:38
Remove inputing connection parameters for command line tools by using environment variables, a .pgpass file, or .pg_service.conf file.
Databases In psql1:45
Learn how to what databases are present and connect to them in psql.
Schemas In psql2:22
How to list the schemas and see the tables in a schema using psql.

ORDER BY3:14
If you need the results to be returned in a specific order, use ORDER BY.
Using MIN and MAX Functions2:06
Find the smallest record with MIN and largest with MAX.
Using AVG and SUM2:05
Use these function to find the average value or the sum of all the values.
LIKE to Match Patterns4:01
LIKE allows you to match text patterns for partial matches.
Renaming Columns With Alias3:24
You can change the name of a column with AS syntax.
LIMIT to Control Number of Records Returned3:21
If you only need a certain number of records use LIMIT to control.
NULL Values2: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 Learned1:06
Practice using AdventureWorks database.

Diagramming Table Relationships3:10
How to map out the tables and relationships in a database with diagrams.
Grabbing Information From Two Tables5:50
How to pull information from 2 different tables in a single statement using JOIN.
Grabbing Information From Multiple Tables5:07
Use multiple JOIN statements to pull together 3 or more tables.
Left Joins3:25
Left joins allow you to pull all records from first table and any matching records from second table.
Right Joins3:17
Right joins allow you to pull matching records from first table and all records from second table.
Full Joins2:25
Full joins pull all records from both tables.
Self Joins6:02
Connect a table back to itself.
USING To Reduce Typing1:39
Reduce typing with USING instead of ON in joins.
Even Less Typing With NATURAL4:19
NATURAL joins combine tables where fields are named the same in each table.
Practice What You've Learned1:04
Practice joins using the AdventureWorks database.

Group By8:18
GROUP BY allows you to aggregate records and perform an aggregate function like AVG.
Use HAVING to Filter Groups5:32
HAVING clause lets you filter out results of your GROUP BY results.
Grouping Sets7:00
Use GROUPING SETS to group by multiple fields separately in a single query.
Rollup6:16
Using ROLLUP as a shortcut for complex GROUPING SET
Cube - Rollup On Steroids6:55
CUBE creates all combinations of fields while grouping.

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

Learn SQL Using PostgreSQL: From Zero to Hero

What you'll learn

Explore related topics

Course content

Introduction1 lecture • 5min

Installing PostgreSQL and pgAdmin5 lectures • 18min

Simple Selection of All Records6 lectures • 14min

Using WHERE to Select Records11 lectures • 22min

Schemas1 lecture • 3min

Using psql To Connect To Postgres4 lectures • 13min

Intermediate SELECT Statements8 lectures • 22min

Joining multiple tables together10 lectures • 36min

Grouping and Aggregation Functions5 lectures • 34min

Combining Queries3 lectures • 12min

Requirements

Description

Who this course is for: