PostgreSQL Bootcamp : Go From Beginner to Advanced, 60+hours from Udemy

What's inside

Learning objectives

Learn with 60+ hours of hands-on practical exercises
Step-by-step walkthrough to perform simple to complex sql queries
Subqueries, ctes, indexes, pl/sql, pl/pgsql, triggers, cursors, partitions, views, json, window functions, and much more
How to create a database from scratch
Creating and using various data types
Creating user-defined data types
Table and column constraints
Understanding, using, and creating custom sequences
String functions
Aggregate functions
Date/time functions with query analysis
Grouping data with group sets
Schemas creations and privileges
Table partitioning techniques
Exploring array functions
In-depth json data types and queries
Subqueries
Common table expressions (ctes)
Recursive common table expressions
Advanced window functions
Server programming
Functions with sql language

Functions with pl/pgsql language
Exploring stored procedures
Indexes and queries performance optimizations
Multiple data views types and security
Using regular expressions for text patterns
Power text searches with special data types and indexes
Crosstab reports with crosstab extensions
Making our database for global languages - internationalization
Psql utility commands
Understanding postgresql internals
Triggers
Using cursors
Data integrity with transactions and savepoints
Managing postgresql security
Extended postgresql echosystem
Analyze sample hr, stocks, northwind, global trades databases
Practice with huge number of queries
Use sql to perform data analysis
Confident putting sql and postgresql on your resume
Show more
Show less

Learn with 60+ hours of hands-on practical exercises
Step-by-step walkthrough to perform simple to complex sql queries
Subqueries, ctes, indexes, pl/sql, pl/pgsql, triggers, cursors, partitions, views, json, window functions, and much more
How to create a database from scratch
Creating and using various data types
Creating user-defined data types
Table and column constraints
Understanding, using, and creating custom sequences
String functions
Aggregate functions
Date/time functions with query analysis
Grouping data with group sets
Schemas creations and privileges
Table partitioning techniques
Exploring array functions
In-depth json data types and queries
Subqueries
Common table expressions (ctes)
Recursive common table expressions
Advanced window functions
Server programming
Functions with sql language
Functions with pl/pgsql language
Exploring stored procedures
Indexes and queries performance optimizations
Multiple data views types and security
Using regular expressions for text patterns
Power text searches with special data types and indexes
Crosstab reports with crosstab extensions
Making our database for global languages - internationalization
Psql utility commands
Understanding postgresql internals
Triggers
Using cursors
Data integrity with transactions and savepoints
Managing postgresql security
Extended postgresql echosystem
Analyze sample hr, stocks, northwind, global trades databases
Practice with huge number of queries
Use sql to perform data analysis
Confident putting sql and postgresql on your resume
Show more
Show less

Syllabus

Welcome to the class

Welcome to the course!

I've created this lecture to contains;

Important sample data files
All files are attached to the lecture

Introduction to PostgreSQL

In this video, we will install the postgreSQL server on a Mac.

How to install PostgreSQL on Windows

We will configure the pgAdmin 4 client interface to connect to our PostgreSQL 12 server installed earlier.

We will be creating a new user login to access our PostgreSQL database, and creating training tables.

We will be creating our learning database on our new server.

A quick introduction on how we will be running queries within the pgAdmin tool

We will be working on uploading and installing the sample data files to our PostgreSQL server.

Install Human Resources (hr) database

In this video, we will be installing some sample stock market data to our tables.

Install northwind database

How to drop a database

Creating and Modifying Tables

Movie Database Structure

We will create an actor table which will contains actor related information.

We will create an directors table which will contains director related information.

We will create a movies table and store various movies related information like movie name, release_date and more.

In this video, we will show you how to create a movies_revenues table which will contains movies revenues.

We will show you how to create a junction table which will connect movies and actors table together by means of foreign keys.

Install sample data for 'movies' database

We will create a sample Database called "mydata" and then we will create tables and data inside as per later videos.

We will use the pgAdmin Graphical User Interface (GUI) tool to create and modify a table.

We will use the pgAdmin Graphical User Interface (GUI) tool to view existing table structure like columns, constraints etc., and create a column to an existing table.

We will use the pgAdmin Graphical User Interface (GUI) tool to view a column properties, rename it, and change its data type.

How to use pgAdmin or DROP TABLE command to delete a table from a database.

How to insert, update and delete data into our tables.

How to insert data into a table.

How to insert multiple records in one single INSERT statement.

How to insert a data if this contains a quote e.g. we want to insert 'users's log' as text in our table.

How to use RETURNING clause in INSERT statement to get information of added row.

How to update data in a table

How to updating a row and returning the updated row

How to update all records in a table.

How to delete a row based on a condition or delete all data from a table

Using UPSERT

How to get simple records from a table using SELECT command

How to select all data from a table

How to use SELECT statement to get selected columns from a table.

How we can use column aliases to make our sql commands results output more presentable and readable for all users.

We will demonstrate how to use SELECT statement with expressions. i.e. use SELECT but not on a table?

How to use ORDER BY to list records in a table

How to use ORDER BY with alias column name

How to use ORDER BY to sort rows by expressions

How to use either column name or column number for ORDER BY clause.

How to use ORDER BY when you have NULL data in a table

How to use DISTINCT function to get unique or distinct value of a table.

How to filter data using SQL functions

We will look into comparison, Logical, and Arithmetic operators

How to use WITH with AND operator

How to use WITH along with OR operator

How to use AND, OR operators together, and how with/without parenthesis will change the query result output.

What is allowed before and after WHERE clause

How SQL process AND, OR operators in terms of executions, and what role parenthesis play in query output.

Can we use column aliases with WHERE?

How SQL does the order of execution of WHERE, SELECT and ORDER BY clause

Using Logical operators

How to use LIMIT and OFFSET to slice and dice to limit output records

Using FETCH

How to use IN and NOT IN with WHERE clause to filter data and more.

How to use BETWEEN and NOT BETWEEN clause with WHERE statement

Using LIKE and ILIKE

How to use IS NULL and IS NOT NULL to filter missing values and more.

Concatenation techniques

How to use ||, CONCAT and CONCAT_WS to concatenate strings and table columns

PostgreSQL Data types

Boolean data type

Characters data types

Numeric data types

Decimals data types

Selecting Numbers data types

Date/Time data types

DATE

TIME

TIMESTAMP and TIMESTAMPTZ

UUID data type

Array

hstore data type

JSON data type

Network Address data types

Modifying Table Structures, Add constraints

Creating sample database, adding columns

Modify Table Structures, Add/Modify Columns

Add constraints to columns

Data type conversions

Using CAST for data conversions

Table data conversion

Conversion Functions

to_char

to_number

to_date

to_timestamp

User-defined Data Types

CREATE DOMAIN - Create a DOMAIN data type, create an address

We will create a data type which will allow only a positive number for a column

CREATE DOMAIN - Create a postal code validation data type

Good to know

Know what's good

, what to watch for

, and possible dealbreakers

Covers server programming with SQL and PL/pgSQL, which are essential for database server-side logic

Teaches advanced window functions, which are useful for complex data analysis and reporting

Explores stored procedures, which are crucial for encapsulating and reusing database logic

Begins with installing PostgreSQL on different operating systems, which is helpful for beginners

Discusses indexes and query performance optimizations, which are critical for maintaining database efficiency

Uses PostgreSQL 12, which was released in 2019 and is no longer supported with security updates

Activities

Be better prepared before your course. Deepen your understanding during and after it. Supplement your coursework and achieve mastery of the topics covered in PostgreSQL Bootcamp : Go From Beginner to Advanced, 60+hours with these activities:

Review Basic SQL Syntax

Show steps

Reinforce your understanding of fundamental SQL commands before diving into PostgreSQL-specific features.

Browse courses on SQL Syntax

Show steps

Review SQL SELECT, INSERT, UPDATE, and DELETE statements.
Practice writing basic queries with WHERE clauses and ORDER BY.
Familiarize yourself with common SQL data types.

Review 'PostgreSQL: Up and Running'

Show steps

Get a solid foundation in PostgreSQL fundamentals with this practical guide.

View PostgreSQL: Up and Running: A Practical Guide... on Amazon

Show steps

Read the chapters on installation, basic SQL commands, and data types.
Follow the examples in the book to practice using PostgreSQL.

Review 'SQL Cookbook'

Show steps

Explore practical SQL solutions to deepen your understanding of query design and data manipulation.

View SQL Cookbook: Query Solutions and Techniques... on Amazon

Show steps

Read relevant chapters on data retrieval, filtering, and aggregation.
Try implementing the solutions in PostgreSQL using the course's sample databases.

Four other activities

Expand to see all activities and additional details

Show all seven activities

Create a PostgreSQL Cheat Sheet

Show steps

Compile a cheat sheet of commonly used PostgreSQL commands and functions for quick reference.

Show steps

Categorize commands by functionality (e.g., data definition, data manipulation, functions).
Include syntax examples and brief descriptions for each command.
Share your cheat sheet with other students for feedback and improvement.

Practice Window Functions

Show steps

Sharpen your skills in using window functions, a powerful feature in PostgreSQL for advanced data analysis.

Show steps

Solve practice problems involving ranking, partitioning, and aggregation using window functions.
Experiment with different window function clauses like OVER, PARTITION BY, and ORDER BY.

Design a Database Schema

Show steps

Apply your knowledge by designing a database schema for a real-world application.

Show steps

Choose a domain (e.g., e-commerce, social media, library management).
Identify the entities and relationships in your chosen domain.
Create tables with appropriate data types and constraints.
Document your schema with descriptions of tables and columns.

Contribute to a PostgreSQL Project

Show steps

Enhance your understanding by contributing to an open-source PostgreSQL project.

Show steps

Find a PostgreSQL-related open-source project on GitHub or GitLab.
Identify a bug or feature request that you can contribute to.
Submit a pull request with your changes.

Career center

Learners who complete PostgreSQL Bootcamp : Go From Beginner to Advanced, 60+hours will develop knowledge and skills that may be useful to these careers:

SQL Developer

A SQL developer specializes in writing and optimizing SQL queries, stored procedures, and other database code. This role is critical for ensuring efficient data access and manipulation in applications. The PostgreSQL Bootcamp is directly applicable to the work of a SQL developer, as it covers complex SQL commands, subqueries, CTEs, window functions, the creation of tables and indexes, and stored procedures. Learning to improve query performance is a key learning objective of the course, which is very helpful experience. Those looking to become SQL developers will find this course helpful as it will prepare them to use all of the features they need on the job.

See salaries and explore the career path for SQL Developer

Database Developer

A database developer is responsible for designing, implementing, and maintaining databases, as well as creating and optimizing stored procedures, triggers, and other database objects. This role is key to ensuring the reliable and efficient operation of databases. The PostgreSQL Bootcamp is very helpful to a database developer due to its in-depth coverage of database creation, table management, and advanced SQL techniques. Moreover, the course's content on server programming with SQL and PL/pgSQL, stored procedures, and triggers directly relates to the daily work of a database developer. Aspiring database developers should take this particular course as it includes many practical exercises.

See salaries and explore the career path for Database Developer

Database Administrator

A database administrator is responsible for maintaining the performance, security, and availability of an organization's databases. This role involves tasks such as database design, implementation, and troubleshooting, ensuring data integrity and accessibility for users. The PostgreSQL Bootcamp, with its comprehensive coverage of database creation, table management, and query optimization, helps build a strong foundation for success in this field. Specifically, mastering the course's content on user creation, privileges, and data security is directly applicable to the responsibilities of a database administrator. Those aspiring to become a database administrator should take this course in particular because it covers server programming with SQL and PL/pgSQL, which are essential skills.

See salaries and explore the career path for Database Administrator

Data Analyst

A data analyst examines data using database tools and statistical methods, translating the results into actionable insights for business decisions. SQL is crucial for retrieving, manipulating, and analyzing data stored in databases. This role often deals with creating reports and dashboards that present clear, concise findings. The PostgreSQL Bootcamp assists a data analyst through its deep dive into complex SQL queries, window functions, and aggregate functions. The course's emphasis on real-world database applications and data analysis using SQL makes it particularly valuable for aspiring data analysts, as they will use these skills daily in the role. This course is also helpful because a data analyst will need to manipulate table data, which is covered extensively in the bootcamp.

See salaries and explore the career path for Data Analyst

Teaching Assistant

A teaching assistant supports instructors by assisting with course content, tutoring students, and grading assignments. This role requires a strong mastery of course material. The PostgreSQL Bootcamp helps a teaching assistant because they will have an expert-level understanding of the topics in the bootcamp, such as complex queries, functions, stored procedures, and database design. The teaching assistant should be confident in their ability to explain these topics to students. Those with strong PostgreSQL skills from the course can help others succeed in their learning process.

See salaries and explore the career path for Teaching Assistant

Business Intelligence Developer

A business intelligence developer designs and builds data warehousing and reporting systems to support business decision making. This role demands strong database skills, especially in writing efficient queries and transforming data into meaningful formats. The PostgreSQL Bootcamp may be useful in this role because it covers subqueries, CTEs, and stored procedures which are essential tools for organizing and presenting large data sets. This course also covers data analysis using SQL, which is helpful to learning how to build useful reports and dashboards. A business intelligence developer would benefit from a course that teaches server programming with SQL and PL/pgSQL.

See salaries and explore the career path for Business Intelligence Developer

Reporting Analyst

A reporting analyst creates reports and dashboards to communicate key performance indicators to stakeholders. This role requires a solid understanding of database query languages and reporting tools. The PostgreSQL Bootcamp helps a reporting analyst by teaching them how to write efficient SQL queries to extract and manipulate data, as well as how to use aggregate functions and window functions for data analysis. This course provides a strong foundation in the practical aspects of data handling. The content on complex SQL queries and database manipulation will be an important part of the skill set of a good reporting analyst.

See salaries and explore the career path for Reporting Analyst

Backend Developer

A backend developer builds the server-side logic and architecture of software applications, often interacting directly with databases. This role involves coding APIs, handling data storage, and ensuring the system's performance and scalability. The PostgreSQL Bootcamp may be useful because it covers database creation, table manipulation, and the use of SQL queries for data retrieval and manipulation. The course content on server programming with SQL and PL/pgSQL is especially relevant to the daily tasks performed by backend developers, as these languages are used to interact with the database. Furthermore, the course’s exploration of indexes and query optimization could help improve the developer's ability to improve performance.

See salaries and explore the career path for Backend Developer

Data Engineer

A data engineer builds and maintains data infrastructure, including databases, data pipelines, and data warehouses. This role requires an expert understanding of data storage, retrieval, and transformation. The PostgreSQL Bootcamp is useful for aspiring data engineers because it includes hands-on practice with creating and managing databases, writing complex SQL queries, and working with various data types, including JSON. This course's instruction on server programming, stored procedures, and triggers provides relevant skills for creating robust data solutions. The data engineer will need to be an expert in creating tables which this course goes over in detail.

See salaries and explore the career path for Data Engineer

Software Engineer

A software engineer designs, develops, and tests software applications. This role requires a broad range of programming skills, including database interactions. The PostgreSQL Bootcamp may be useful in this role as it provides a deep understanding of how databases work and how to interact with them using SQL. This knowledge is particularly relevant for software engineers who build applications that rely on database persistence. The course material on complex SQL queries, stored procedures, and server programming with SQL and PL/pgSQL relates to the daily tasks of a software engineer.

See salaries and explore the career path for Software Engineer

Data Science Researcher

A data science researcher focuses on developing new methods for data analysis and creating innovative solutions using databases and data analytics tools. This role often requires a strong background in mathematics and programming. The PostgreSQL Bootcamp may be helpful in this role as it provides a deep understanding of how to work with databases, create complex queries, and use server-side programming. Researchers often need to retrieve and manipulate data using database tools such as SQL. The course's material on JSON, window functions, and text search may be useful to data scientists in their work.

See salaries and explore the career path for Data Science Researcher

Data Architect

A data architect designs and builds data management systems for an organization. This role requires an expert knowledge of databases, data modeling, and data warehousing. The PostgreSQL Bootcamp may be useful for someone in this role as it provides knowledge of how to work with data types, tables, and databases. It is important for a data architect to understand how to efficiently store and retrieve data from a database. This course provides a good working knowledge of the PostgreSQL database. This course's in-depth look at query optimization and database design may be most useful.

See salaries and explore the career path for Data Architect

System Administrator

A system administrator is responsible for the maintenance, configuration, and reliable operation of computer systems, including database servers. This role involves managing user access, monitoring server performance, and ensuring system security. The PostgreSQL Bootcamp is helpful to the system administrator because it touches on database security, user management, and performance optimization. Although this course focuses on using databases, it may help someone who needs to manage database servers. The course's content regarding security and database administration is helpful.

See salaries and explore the career path for System Administrator

Technical Consultant

A technical consultant advises clients on technical projects, often involving database systems. This role requires a strong understanding of various technologies, including database design, SQL, and data management. The PostgreSQL Bootcamp may be helpful for this job role because it provides a good understanding of database design. The consultant should be able to use SQL to manipulate the database, as well as have an understanding of server programming with SQL and PL/pgSQL. This course provides a strong foundation in those technical skills.

See salaries and explore the career path for Technical Consultant

IT Manager

An IT manager oversees the information technology infrastructure of an organization, which includes managing database systems. This role requires a high-level understanding of IT operations, including database security, user management, and system performance. The PostgreSQL Bootcamp may be helpful because it provides a practical overview of database administration, user privileges, data security, and SQL. An IT manager needs to have knowledge of the database tools used by their team, though this is not the focus of their job. This course provides a practical introduction to database management.

See salaries and explore the career path for IT Manager

Reading list

We've selected two books that we think will supplement your learning. Use these to develop background knowledge, enrich your coursework, and gain a deeper understanding of the topics covered in PostgreSQL Bootcamp : Go From Beginner to Advanced, 60+hours.

PostgreSQL: Up and Running

Save

Provides a comprehensive introduction to PostgreSQL. It covers installation, configuration, and basic usage. It's a great resource for beginners to get up to speed with PostgreSQL quickly. It is commonly used as a textbook at academic institutions.

PostgreSQL: Up and Running: A Practical Guide to...

Kindle Edition

SQL Cookbook

Save

Provides practical solutions to common SQL problems. It's a useful reference for understanding different approaches to querying and manipulating data. While not specific to PostgreSQL, the core SQL concepts are transferable. It can be used as a reference to expand on the course materials.

SQL Cookbook: Query Solutions and Techniques for...

Paperback

SQL Cookbook: Query Solutions and Techniques for...

Kindle Edition

PostgreSQL Bootcamp

Go From Beginner to Advanced, 60+hours

What's inside

Learning objectives

Syllabus

Good to know

Save this course

Activities

Career center

Reading list

Share

Similar courses