Udemy Business

Teach on Udemy

Turn what you know into an opportunity and reach millions around the world.

Learn More

Your cart is empty.

Keep shopping

PostgreSQL Tutorial for Data Analysis and Full-Stack

Name: PostgreSQL Tutorial for Data Analysis and Full-Stack
Rating: 3.9 (12 reviews)

Learn Basics of PostgreSQL for Full-Stack Development and Data Anlaysis .

Created byAnkit Srivastava

Last updated 1/2025

English

English [Auto],

What you'll learn

Using PostgreSQL for Management and Administration of Databases
Creating Databases for Organizations or Applications
Data Analysis using PostgreSQL Queries
Create, Read, Update and Delete Records in a Database

Course content

6 sections • 30 lectures • 6h 49m total length

Introduction and Installation of PostgreSQL14:48
Install PostgreSQL, the open-source relational database management system, on Windows using binary packages or EnterpriseDB, configure the data directory, set a password, and access via pgAdmin.
Creating First Database and First Table using UI in PostgreSQL32:43
Create a database and its first table in PostgreSQL using the user interface, defining an id, name, and other attributes with appropriate data types and constraints.
PostgreSQL creating a Database using SQL query26:37
Learn to create a PostgreSQL database and tables using sql, define a public schema, and implement identity primary keys with appropriate data types like character, decimal, and date/time.
Primary Key Constraint and Setting it on already created table7:52
Learn how to set a primary key constraint on an already created table in PostgreSQL, including adding the constraint later, creating unique identifiers, and understanding composite keys and identity columns.
Inserting Records in PostgreSQL using Queries13:59
Master inserting records in PostgreSQL with insert queries, including column lists and multi-row values. Apply these concepts to schema tables like user and rooms, handling strings, numbers, and identity columns.
Updating Records in PostgreSQL15:02
Update records in PostgreSQL using update statements with set and where clauses to modify specific columns by their unique identities or primary keys.
Deleting Records in PostgreSQL AND and OR Operators16:40
Explore deleting records in PostgreSQL using delete from, where clauses, and the and/or operators, with examples of id and name and combining multiple conditions.
Importing an External Database in PostgreSQL from SQL Code13:34
Learn how to import an external database into PostgreSQL from SQL code, using the Northwind sample with tables like customers, products, categories, and suppliers for relational data analysis.
Comparison Operators in PostgreSQL with Where Clause27:01
Learn PostgreSQL comparison operators and the where clause, including is equal to, not equal, greater than, less than, and in, with examples on customers and cities in the public schema.
Comparison and Logical Operators Together in PostgreSQL With Where Clause20:12
Explore how to use comparison and logical operators in PostgreSQL with the where clause, combining conditions with and, or, not, and keywords like between, in, and like.
Between Operator in PostgreSQL with WHere Clause6:54
Explore the between operator to filter products by unit price between 30 and 50, including greater than or equal to 30 and less than or equal to 50.
IN and NOT IN with Where Clause in PostgreSQL7:35
Learn to use in and not in with a where clause in PostgreSQL to filter rows by a set of values, with practical examples using city lists and category IDs.
LIKE Operator for Pattern Matching in PostgreSQL15:49
Explore pattern matching in PostgreSQL using the like operator, with % and _ wildcards to find city and customer names starting with, ending with, or containing a sequence.

Creating Multiple School Management System DB in PostgreSQL9:02
Learn to create and relate multiple school management databases in PostgreSQL by building tables with primary keys, constraints, and foreign keys across schools.
Concept of Foreign Key and FK Constraint in PostgreSQL7:32
Explore foreign key concepts and constraints in PostgreSQL, and see how primary keys like student_id and school_id enforce referential integrity and prevent actions that break table links.
Creating students table with 2 foreign keys in database using Postgresql12:32
Create a PostgreSQL student table with two foreign keys, school_id and class_id, linking to schools and classes, and define a primary key plus fields such as name and email.
Inserting dummy values in tables and testing foreign key constraint14:58
Insert dummy values into the schools, classes, and students tables, test foreign key constraints on school IDs, and validate auto-generated IDs for data analysis and full-stack workflows.
Joins in PostgreSQL - Inner, Left, Right, Full33:51
Explore how to join PostgreSQL tables using inner, left, right, and full joins to combine school, class, and student data based on common columns.

How to Delete or Drop Database in PostgreSQL1:09
Delete or drop a database in PostgreSQL by closing all editing windows, right-clicking the database, and selecting delete or drop to remove it.
Dropping Regular Columns and those with Foreign Key Constraint2:38
Drop regular columns and those with foreign key constraints in PostgreSQL. Understand how dependents affect drops and how constraints prevent accidental deletions.

Variables in PostgreSQL15:23
Declare and use variables in PostgreSQL within a begin-end block, assign values, and print with notices. Learn variable data types, naming rules, and the now function for current time.
Functions or Stored Procedures in PostgreSQL - Part 112:46
Explore creating and using functions and stored procedures in PostgreSQL, including create or replace function, passing arguments, and returning values, with examples like add, multiply, and triangle area.
Functions or Stored Procedures in PostgreSQL - More Examples14:17
Explore creating and using PostgreSQL functions with and without arguments, including begin and end blocks, variables, and immutable, stable, and volatile options, with examples like sales tax and total students.
Views in PostgreSQL19:51
Explore views in PostgreSQL, learn to create, query, and reuse complex joins across multiple tables, and manage view lifecycles with selection, limiting, and dropping.

Aggregate Functions in PostgreSQL17:37
Master aggregate functions in PostgreSQL, including max, min, average, and sum, with practical examples on unit price data, aliasing, and subqueries for data analysis.
GROUP BY in PostgreSQL for Data Analysis9:23
Explore how to use group by in PostgreSQL for data analysis, counting customers by country or department with aggregate functions, and ordering results to reveal most populated groups.

Install Sample Database Pagila (Movie Rental Database) for Analysis8:16
Install the Pagella sample movie database in PostgreSQL with Pgadmin, load the schema and data, and prepare a dataset for analysis of actors, films, and rentals.
Understanding Diagram of this Database3:55
Explore a simple er diagram of a PostgreSQL database in PgAdmin, mapping actor, address, category, city, country, customer, film, inventory, language, payment, rental, staff, and store tables.
Practice Question 1 - List all movies along with their release year and category3:07
Learn use of Joins on 3 Tables
Practice question 2 - Top 5 actors who have acted in the most movies.4:36
Join the actor and film_actor tables to count each actor's movies, then group by actor id, first name, and last name, order by count, and limit to five.

Requirements

Basic understanding of databases

Description

Syllabus of the Course

Lecture 1: Introduction and Installation of PostgreSQL

Lecture 2: Creating First Database and First Table using UI in PostgreSQL

Lecture 3: PostgreSQL creating a Database using SQL query

Lecture 4: Primary Key Constraint and Setting it on already created table

Lecture 5: Inserting Records in PostgreSQL using Queries

Lecture 6: Updating Records in PostgreSQL

Lecture 7: Deleting Records in PostgreSQL AND and OR Operators

Lecture 8: Importing an External Database in PostgreSQL from SQL Code

Lecture 9: Comparison Operators in PostgreSQL with Where Clause

Lecture 10: Comparison and Logical Operators Together in PostgreSQL With Where Clause

Lecture 11: Between Operator in PostgreSQL with WHere Clause

Lecture 12: IN and NOT IN with Where Clause in PostgreSQL

Lecture 13: LIKE Operator for Pattern Matching in PostgreSQL

Section 2: Multiple Schools Management System Database Example

Lecture 14: Creating Multiple School Management System DB in PostgreSQL

Lecture 15: Concept of Foreign Key and FK Constraint in PostgreSQL

Lecture 16: Creating students table with 2 foreign keys in database using Postgresql

Lecture 17: Inserting dummy values in tables and testing foreign key constraint

Lecture 18: Joins in PostgreSQL - Inner, Left, Right, Full

Section 3: Deleting or Dropping Databases and Columns

Lecture 19: How to Delete or Drop Database in PostgreSQL

Lecture 20: Dropping Regular Columns and those with Foreign Key Constraint

Section 4: Stored Procedures or Functions

Lecture 21: Variables in PostgreSQL

Lecture 22: Functions or Stored Procedures in PostgreSQL - Part 1

Lecture 23: Functions or Stored Procedures in PostgreSQL - More Examples

Lecture 24: Views in PostgreSQL

Section 5: Misc

Lecture 25: Aggregate Functions in PostgreSQL

Lecture 26: GROUP BY in PostgreSQL for Data Analysis

Who this course is for:

Beginner Developers Curious to Learn PostgreSQL for projects or Job
Beginners who want to learn PostgreSQL for Data Analysis

PostgreSQL Tutorial for Data Analysis and Full-Stack

What you'll learn

Explore related topics

Course content

Introduction13 lectures • 3hr 39min

Multiple Schools Management System Database Example5 lectures • 1hr 18min

Deleting or Dropping Database and Columns2 lectures • 4min

Stored Procedures or Functions4 lectures • 1hr 2min

Aggregate and Groupby2 lectures • 27min

Movie Stores sample database for practice4 lectures • 20min

Requirements

Description

Who this course is for: