IIRF Online > Development > Database Design & Development > PostgreSQL > Learn SQL Using PostgreSQL: From Zero to Hero
Learn SQL Using PostgreSQL: From Zero to Hero by Udemy
Master PostgreSQL starting with basics to advanced features like triggers, PL/pgSQL functions, Window Functions and CTE
Course Highlights
- 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
Skills you will learn!
Curriculum
1 Topic
Introduction
5 Topics
Installing PostgreSQL on Mac
Installing PostgreSQL on Windows
Installing PostgreSQL on Ubuntu
Install Northwind Database
Install Some Additional Databases.
6 Topics
Selecting All Data From a Table
Selecting Specific Fields
Selecting Distinct Values
Counting Results
Combining Fields in SELECT
Practice What You've Learned
11 Topics
What If You Don't Want All Records
Searching For Specific Text
Searching Numeric Fields
Searching Date Fields
WHERE Using Logical AND Operator
WHERE Using Logical OR Operator
WHERE Using Logical NOT Operator
WHERE Combining AND OR and NOT
Using BETWEEN
Using IN
Practice What You've Learned
1 Topic
Schema Basics
4 Topics
Connecting With psql
Eliminate Typing Connection Parameters
Databases In psql
Schemas In psql
8 Topics
ORDER BY
Using MIN and MAX Functions
Using AVG and SUM
LIKE to Match Patterns
Renaming Columns With Alias
LIMIT to Control Number of Records Returned
NULL Values
Practice What You've Learned
10 Topics
Diagramming Table Relationships
Grabbing Information From Two Tables
Grabbing Information From Multiple Tables
Left Joins
Right Joins
Full Joins
Self Joins
USING To Reduce Typing
Even Less Typing With NATURAL
Practice What You've Learned
5 Topics
Group By
Use HAVING to Filter Groups
Grouping Sets
Rollup
Cube - Rollup On Steroids
3 Topics
Union
Intersect
Except
3 Topics
Subquery Using EXISTS
Subquery Using ANY and ALL
IN Using Subquery
6 Topics
INSERT INTO
UPDATE
DELETE
SELECT INTO
INSERT INTO SELECT
Returning Data From Update Delete and Insert
11 Topics
What Are Indexes?
CREATE INDEX
DROP INDEX
How To Kill Runaway Queries
Using Explain To See Query Plan
Use Analyze To Update Table Statistics
How Is Query Plan Cost Calculated
Using Indexes On More Than One Field
Expression Indexes
Types Of Indexes
Speeding Up Text Matching
12 Topics
Design Process Overview
Database Terminology
A Design Process
Finding Mission Statement And Ojectives
Analyzing Current Systems
Create Table Structure
Establishing Keys
Specifying Fields
Relationships Between Tables
Business Rules
Establish Needed Views
Double Checking Data Integrity
5 Topics
CREATE TABLE
ALTER TABLE - Part One
ALTER TABLE - Part Two
ALTER TABLE - Part Three
DROP TABLE
8 Topics
NOT NULL Constraint
UNIQUE Constraint
PRIMARY KEY Constraint
FOREIGN KEY Constraint
CHECK Constraint
DEFAULT Values
Changing a Column's Default Value
Adding and Removing a Column's Constraint
3 Topics
Create A Sequence
Alter and Delete Sequences
Using Serial Datatypes
4 Topics
WITH Queries
Using CTE to Grab Identity Field From Insert
Creating Hierarchical Data To Use For Recursive WITH Queries
Using Recursion in CTEs
5 Topics
Views - How To Create
Views - How To Modify
Creating Updatable Views
With Check Option
Deleting Views
3 Topics
CASE WHEN
COALESCE
NULLIF
6 Topics
Date Time and Timestamp Data Types
Time Zones
Interval Data Type
Date Arithmetic
Pulling Out Parts of Dates and Times
Converting One Data Type Into Another
3 Topics
Basic Window Function Example
Using Window Functions With Subqueries
Using Rank() To Find The First N Records In Join
2 Topics
Composite Type Basics
Using Composite Types
10 Topics
Write Your First Function
Write A Function That Returns A Single Value
Functions With Parameters
Functions That Have Composite Parameters
Functions That Return A Composite
Functions With Output Parameters
Functions With Default Values
Using Functions as Table Source
Functions That Return More Than One Row
Procedures - Functions That Don't Return Anything
5 Topics
ACID Transactions
Simple Transaction Control
Rollbacks & Savepoints
SQL Transaction Isolation
PostgreSQL Transaction Isolation
6 Topics
Declaring Arrays
Inputting Array Values
Accessing Arrays
Modifying Arrays
Searching Arrays
Array Operators
9 Topics
Build Your First PL/pgSQL Function
Handling Functions With Output Variables
Returning Query Results
Declaring Variables
Looping Through Query Results
Using If-Then Statements
Returning Query Results Continued
Loop and While Loops
Looping Over Array Elements
2 Topics
Build Your First Trigger
Statement Triggers
2 Topics
Importing CSV
Practice What You've Learned
7 Topics
What Is JSON And How To Store In Database
Create JSON From Tables
Aggregating JSON Fields
Building airports_json Table
Selecting Information Out of JSON Fields
Searching JSON Data
Updating And Deleting Information Inside JSON Fields
2 Topics
CREATE DATABASE
DROP DATABASE
3 Topics
Basic Import/Export With Copy
Basic pg_dump And Restore
Custom Format Dumps
7 Topics
Overview of Roles and Users
Instance Level Security
Database Level Security
Schema Level Security
Table Level Security
Column Level Security
Row Level Security
3 Topics
Install PostGIS on Ubuntu (Not needed for Windows/Mac)
Converting Airport Data
Your First Geographical Query
1 Topic
Thank you
Learn SQL Using PostgreSQL: From Zero to Hero
Related PostgreSQL Courses
Thank You!
Your review has been submitted successfully.