Practical SQL with PostgreSQL: From Queries to Real Projects

Build practical SQL skills with PostgreSQL, from your first queries to database programming, query performance and essential administration. Throughout the course, you will work with TrainingHub, a synthetic database for a training business, and learn to answer questions about courses, customers, enrollments and payments.

What you will learn:

• Understand relational databases, tables and relationships; design tables using data types, primary and foreign keys, constraints and identity columns.

• Write SELECT queries, filter and sort results, use aliases and expressions, and work with NULL, CASE, COALESCE, NULLIF, text functions, regular expressions, dates and timestamps.

• Insert, update and delete data; use RETURNING, transactions and savepoints; compare DELETE, TRUNCATE and DROP.

• Combine information with INNER, LEFT, RIGHT, FULL, CROSS and self joins, and understand missing matches and duplicate rows.

• Analyze results with COUNT, SUM, AVG, MIN, MAX, GROUP BY and HAVING, while avoiding double counting in reports.

• Build more complex queries with subqueries, EXISTS, common table expressions and set operations such as UNION, INTERSECT and EXCEPT.

• Explore window functions, rankings, running totals, period comparisons, ROLLUP, CUBE and GROUPING SETS.

• Create views and materialized views, query JSON and JSONB, and compare tables with arrays and composite types.

• Read EXPLAIN and EXPLAIN ANALYZE plans, experiment with indexes and planner controls, and consider statistics and query performance.

• Explore scheduling with pg_cron and optional query hints with pg_hint_plan in separately configured environments.

• Introduce PL/pgSQL blocks, variables, functions, procedures, exceptions and triggers, then work with roles, privileges, data exchange, backup and restore.

The course combines explanations with demonstrations in pgAdmin, downloadable SQL materials, practical exercises, worked solutions and short knowledge checks. You can pause the lessons, run the examples and compare your results with the demonstrations.

Who this course is for: beginners building a foundation in SQL and PostgreSQL, and developers, analysts or testers who want to practice PostgreSQL features through examples.

To follow the demonstrated local setup, you will need a computer, a browser, Docker Desktop and Docker Compose. The setup demonstrations use Windows. Internet access is needed to obtain the environment and any required container images; some optional extension labs require additional setup. Setup on another operating system may require adapting the steps.

This course contains the use of artificial intelligence.

Some course materials and narration were created with AI assistance, including AI-generated speech.

Learn practical SQL and PostgreSQL by working with TrainingHub, a synthetic database for a training business. Use questions about courses, customers, enrollments and payments to practice reading, changing and explaining data.