Paths
Querying Data with SQL from PostgreSQL
SQL (“sequel” or Structured Query Language) is a common language used to query and manage data in relational database and data stream management systems. This skill will teach... Read more
What You Will Learn
- Querying data with the SELECT statement
- Filtering data with the WHERE clause
- Combining data with JOINs
- Aggregating data
- Inserting, updating, and deleting data
Pre-requisites
- Relational database literacy
Beginner
Learn to use the SELECT statement, along with JOINs and WHERE clasues, to create organized projections of data stored in PostgreSQL.
Querying Data from PostgreSQL
1h 28m
Description
While each organization will have its own database preference, data analysts will benefit from knowing the basics of querying data from PostgreSQL, a popular open-source database platform. In this course, Querying Data from PostgreSQL, you will get a comprehensive introduction of how to query data using PostgreSQL and its pgAdmin tool. First, you will learn the basic structure of a relational database. Next, you will discover how to select data from tables, apply criteria and filters to limit your results, and enhance these skills by retrieving data from multiple tables. Finally, you will explore basic aggregation functions and be exposed to ANSI-compliant SQL. When you're finished with this course, you will have the skills and knowledge of PostgreSQL necessary to easily retrieve data from relational databases.
Table of contents
- Course Overview
- Understanding the Relational Model
- SELECTing Your Data
- Limiting Your Results
- Joining for Further Insight
- Presenting and Aggregating Your Results
- Summary
Combining and Filtering Data with PostgreSQL
1h 57m
Description
In this course, Combining and Filtering Data with PostgreSQL, you will learn how to expand your queries to retrieve additional data and achieve your desired results. First, you will learn the fundamentals of data types and how to work with string functions. Next, you will discover aggregate functions and how to filter aggregate results. Then, you will explore how to use joins to retrieve data from multiple tables. Set theory and unions will also be used to combine multiple result sets. Subqueries and common table expressions are introduced to help conduct more sophisticated analyses using multiple filter criteria. Finally, you will figure out window functions and how to partition and analyze data. When you’re finished with this course, you will have the skills and knowledge of PostgreSQL necessary to begin writing more complex queries to analyze data from multiple sources.
Table of contents
- Course Overview
- Working with String Functions
- Aggregating Functions
- Exploring Join Types
- Introducing Set Theory
- Implementing Subqueries
- Simplifying Queries with Common Table Expressions
- Limiting Results with Window Functions
- Wrapping Up
Intermediate
Insert, update, and delete data from PostgreSQL.
PostgreSQL Data Manipulation Playbook
2h 33m
Description
Multi-user database applications involve many challenges. While many designers focus on performance, scalability, and other very important aspects, data modifications and consistency challenges are sometimes overlooked. In this course, PostgreSQL Data Manipulation Playbook, you will learn about the various concurrency phenomena that may occur when multiple users access shared resources. First, you will cover how to predict, prepare, and plan for these challenges. Next, you will discover how to use PostgreSQL rich DML syntax to tackle data modifications, and how to use advanced techniques to solve harder challenges, associated with large scale data modifications. Finally, you will explore how to avoid many common pitfalls along the way. After completing this course, you will have both the fundamental theoretical knowledge needed to understand concurrency challenges, and the practical tools and techniques you will need to tackle these efficiently for your database applications.
Table of contents
- Course Overview
- Understanding SQL Sublanguages
- What Are Concurrency Challenges?
- Understanding ANSI Concurrency Phenomena
- How ANSI Isolation levels Solve Concurrency Challenges
- Using Transaction Control Language
- Inserting Data with the INSERT Statement
- Using the UPDATE Statement
- Deleting Data Using the DELETE Statement
- Performing Bulk Deletes
- Course Review and Wrap Up
Advanced
Create PostgreSQL database objects, such as functions to capture reusable query logic.
Capturing Logic with Custom Functions in PostgreSQL
1h 54m
Description
At the core of a PostgresSQL database developer's toolkit is a thorough knowledge of how to build correct, efficient functions. In this course, Capturing Logic with Custom Functions in PostgreSQL, you will learn foundational knowledge of how to build your own functions in PostgreSQL. First, you will learn how to move from a basic query to a fully-parameterized function. Next, you will discover how to write your functions using PL/pgSQL, one of the many procedural languages available to the PostgreSQL developer. Finally, you will explore important matters relating to polymorphism, security, defensive programming and error handling. When you are finished, you will be able to solve real-world problems using PostgresSQL functions.
Table of contents
- Course Overview
- Introduction
- Creating Your First Function
- Creating Functions in PL/pgSQL
- Creating Polymorphic Functions to Control Repetition
- Protecting Your Database by Securing Your Functions
- Defending Your Database and Handling Errors
- Course Summary
PostgreSQL Functions Playbook
2h 28m
Description
At the core of a database developer's skill set is a solid knowledge of Structured Query Language, often called by its acronym, SQL. Most database vendors strive to implement as many of the ANSI SQL standards as possible, including the functions defined in that standard. PostgreSQL does a better job than some others in its adherence to the standard, including the ANSI SQL functions.
In this course, PostgreSQL Functions Playbook, you will learn both sets of functions and how they can be used to write simpler, more effective queries. First, you'll have a quick review of the most common data types and how to use functions to build, convert, and format them to get started. Then, you will explore the many other functions and operators that work on those types for a wide variety of purposes. Finally, you'll discover how to use system functions to extract data about the system itself. When you are finished with this course, you'll have a foundational knowledge of PostgreSQL functions as well as the ANSI functions it implements, that will help you move forward as a database developer, both in PostgreSQL and other database systems.
Software required: You will need access to a running PostgreSQL database with populated tables and an IDE to use for editing and running queries. The demos in the course use an instance of PostgreSQL running in a docker container and Azure Data Studio for the IDE but you are free to use a PostgreSQL database and IDE of your choice.
Table of contents
- Course Overview
- Introducing Built In Functions in PostgreSQL
- Understanding Data Types
- Doing Math with Operators and Functions
- Handling Character Data with String Functions and Operators
- Time Travel with Date and Time Functions
- Calculating Values over Row Groups Using Windowing Functions
- Controlling Results with Subquery and Conditional Functions
- Using Array and Range Functions
- Digging into Metadata with System Information and Administration Functions
- Course Summary