PostgreSQL Querying Fundamentals

Learn how to retrieve the data you need from a PostgreSQL database using SQL.

Description

You will build practical PostgreSQL querying skills from the ground up. Starting with SELECT and FROM, you will quickly move into filtering with WHERE, combining conditions with AND and OR, sorting with ORDER BY, and joining data across multiple tables. You will also shape results with CASE expressions, built-in text and date functions, and grouping and aggregation using GROUP BY and HAVING. By the end, you will confidently use subqueries for real query scenarios and apply core data modification statements.

Learning Goals

• Write clear, structured and documented queries [Apply]
• Apply filters and ordering to query results [Apply]
• Use joins to retrieve columns from multiple tables [Apply]
• Apply built-in functions or CASE statements to query results [Apply]
• Produce queries to group and aggregate rows, and filter on groups [Apply]
• Use functions to modify text, date and time [Apply]
• Use subqueries for complex scenarios [Apply]
• Differentiate between the statements to insert, update or delete rows [Understand]

Subjects

• Querying Data with PostgreSQL
• The SELECT Statement
• Joining Tables
• CASE Expressions
• NULL and Data Conversion
• Grouping and Aggregating
• Subqueries
• Built-in Functions
• Data Manipulation Language (DML)

• Query structure and execution order

• Selecting, filtering, and sorting data
• Aliases, DISTINCT, operators, and LIKE
• LIMIT and OFFSET

• JOIN types and combining multiple tables
• Self joins and legacy join syntax

• Simple and searched CASE

• NULL behavior and COALESCE
• Data types, implicit and explicit conversion
• Text functions

• Aggregate functions and GROUP BY
• Filtering groups with HAVING

• Self-contained and correlated subqueries
• EXISTS and NOT EXISTS

• Mathematical, date, and time functions

• INSERT, UPDATE, and DELETE