Querying Data with SQL from PostgreSQL

Paths

Querying Data with SQL from PostgreSQL

Authors: Jason Browning, Ami Levin, Gerald Britton

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

Related topics

Relational databases (SQL Server MariaDB OracleDB) Database administration Database development Business Intelligence Platforms like Tableau Power BI Database use in web development

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

by Jason Browning

Mar 6, 2019 / 1h 29m

1h 29m

Start Course
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
  1. Course Overview
  2. Understanding the Relational Model
  3. SELECTing Your Data
  4. Limiting Your Results
  5. Joining for Further Insight
  6. Presenting and Aggregating Your Results
  7. Summary

Combining and Filtering Data with PostgreSQL

by Jason Browning

Aug 13, 2019 / 1h 58m

1h 58m

Start Course
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
  1. Course Overview
  2. Working with String Functions
  3. Aggregating Functions
  4. Exploring Join Types
  5. Introducing Set Theory
  6. Implementing Subqueries
  7. Simplifying Queries with Common Table Expressions
  8. Limiting Results with Window Functions
  9. Wrapping Up

Intermediate

Insert, update, and delete data from PostgreSQL.

PostgreSQL Data Manipulation Playbook

by Ami Levin

Apr 25, 2019 / 2h 35m

2h 35m

Start Course
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
  1. Course Overview
  2. Understanding SQL Sublanguages
  3. What Are Concurrency Challenges?
  4. Understanding ANSI Concurrency Phenomena
  5. How ANSI Isolation levels Solve Concurrency Challenges
  6. Using Transaction Control Language
  7. Inserting Data with the INSERT Statement
  8. Using the UPDATE Statement
  9. Deleting Data Using the DELETE Statement
  10. Performing Bulk Deletes
  11. Course Review and Wrap Up

Advanced

Create PostgreSQL database objects, such as functions to capture reusable query logic.

PostgreSQL Functions Playbook

by Gerald Britton

Dec 10, 2019 / 2h 29m

2h 29m

Start Course
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
  1. Course Overview
  2. Introducing Built In Functions in PostgreSQL
  3. Understanding Data Types
  4. Doing Math with Operators and Functions
  5. Handling Character Data with String Functions and Operators
  6. Time Travel with Date and Time Functions
  7. Calculating Values over Row Groups Using Windowing Functions
  8. Controlling Results with Subquery and Conditional Functions
  9. Using Array and Range Functions
  10. Digging into Metadata with System Information and Administration Functions
  11. Course Summary