Choose Your Desired Option(s)
Courses in this path
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.
Beginner
1. Querying Data from PostgreSQL
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.
2. Combining and Filtering Data with PostgreSQL 9
In this course, Combining and Filtering Data with PostgreSQL 9, 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.
Intermediate
3. PostgreSQL Data Manipulation Playbook
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.
Advanced
4. Capturing Logic with Custom Functions in PostgreSQL
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.
5. PostgreSQL Functions Playbook
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.
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
Experience
- Relational database literacy
Share Now!