Learn SQL Using PostgreSQL: From Zero to Hero

Udemy

Master the fundamentals and techniques associated with SQL using the concepts involved with PostgreSQL.

Brochure

Enquire

Online

₹ 499 2999

particular

details

                                    Medium of instructions
                                    English

                                    Mode of learning
                                    Self study

                                    Mode of Delivery
                                    Video and Text Based

Introduction

Introduction

Installing PostgreSQL and pgAdmin

Installing PostgreSQL on Mac
Installing PostgreSQL on Windows
Installing PostgreSQL on Ubuntu
Install Northwind Database
Install Some Additional Databases.

Simple Selection of All Records

Selecting All Data From a Table
Selecting Specific Fields
Selecting Distinct Values
Counting Results
Combining Fields in SELECT
Practice What You've Learned

Using WHERE to Select Records

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

Schemas

Schema Basics

Using psql To Connect To Postgres

Connecting With psql
Eliminate Typing Connection Parameters
Databases In psql
Schemas In psql

Intermediate SELECT Statements

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

Joining multiple tables together

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

Grouping and Aggregation Functions

Group By
Use HAVING to Filter Groups
Grouping Sets
Rollup
Cube - Rollup On Steroids

Combining Queries

Union
Intersect
Except

Subqueries

Subquery Using EXISTS
Subquery Using ANY and ALL
IN Using Subquery

Modifying Data In Tables: Insert, Update And Delete

Insert Into
Update
Delete
Select Into
Insert Into Select
Returning Data From Update, Delete, And Insert

Indexes And Performance Tuning

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

Database Design And Normalization

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

Creating And Modifying Tables

Create Table
Alter Table - Part One
Alter Table - Part Two
Alter Table - Part Three
Drop Table

Table Constraints

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

Sequences

Create A Sequence
Alter And Delete Sequences
Using Serial Datatypes

Cte - Common Table Expressions

With Queries
Using Cte To Grab Identity Field From Insert
Creating Hierarchical Data To Use For Recursive With Queries
Using Recursion In Ctes

Views

Views - How To Create
Views - How To Modify
Creating Updatable Views
With Check Option
Deleting Views

Conditional Expressions

Case When
Coalesce
Nullif

Using Date/Time In Postgresql

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

Window Functions

Basic Window Function Example
Using Window Functions With Subqueries
Using Rank() To Find The First N Records In Join

Composite Types

Composite Type Basics
Using Composite Types

Sql Functions And Procedures

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

Transactions And Concurrency Control

Acid Transactions
Simple Transaction Control
Rollbacks & Savepoints
Sql Transaction Isolation
Postgresql Transaction Isolation

Array Data Type

Declaring Arrays
Inputting Array Values
Accessing Arrays
Modifying Arrays
Searching Arrays
Array Operators

Pl/Pgsql - Sql Procedural Language

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

Triggers

Build Your First Trigger
Statement Triggers

Importing Csv

Importing Csv
Practice What You've Learned

Json And Jsonb Data Types

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

Managing Databases

Create Database
Drop Database

Backups And Recovery

Basic Import/Export With Copy
Basic Pg_Dump And Restore
Custom Format Dumps

Security Using Roles, Users And Permissions

Overview Of Roles And Users
Instance Level Security
Database Level Security
Schema Level Security
Table Level Security
Column Level Security
Row Level Security

Introduction To Postgis

Install Postgis On Ubuntu (Not Needed For Windows/Mac)
Converting Airport Data
Your First Geographical Query

Thanks And Good Luck

Thank You

Popular Courses

Popular Platforms

Popular Searches

Learn SQL Using PostgreSQL: From Zero to Hero

Online

₹ 499 2999

Quick Facts

Course overview

The highlights

Program offerings

Course and certificate fees

Fees information

certificate availability

certificate providing authority

Who it is for

What you will learn

The syllabus