SQL Query Fundamentals with PostgreSQL

Set up a productive database development environment by installing and configuring Visual Studio Code, learning relational database management fundamentals, and using Docker to run a PostgreSQL instance.

Install and use Visual Studio Code on Windows or macOS to edit sql code, access the terminal, and customize settings like auto save, zoom, and night owl theme.

Explore relational database management systems and SQL with PostgreSQL, to design tables, define relationships, compare with NoSQL, and perform create, read, update, and delete operations.

Install and run a PostgreSQL database in a Docker container, using Docker Desktop, Docker Hub images, and a docker-compose setup with a persistent volume and port mapping.

Download and extract the module source code, open it in Visual Studio Code, and set up Docker containers with Docker Compose to initialize the Postgres database and run init.sql.

Design and manage PostgreSQL databases by defining schemas, primary and foreign keys, constraints, and relationships; build and modify tables with SQL and PgAdmin, guided by ERD to ensure data integrity.

Explore how a database schema and an ERD guide SQL development, detailing tables, columns, data types, primary and foreign keys, and relationships using draw.io to visualize an inventory model.

Learn to connect to a PostgreSQL database via the command line in a Docker environment, start the container, access the SQL terminal, and run commands to explore databases.

Learn how to create and drop databases in Postgres using sql, connect to an inventory and a separate management database, and set up a docker-based Postgres environment.

Learn to create and manage tables in PostgreSQL, handle reserved words, use schemas, follow naming conventions, snake_case and plural vs singular, and practice with create table and drop table commands.

Learn how PostgreSQL data types define string, number, and boolean fields; explore varchar, text, boolean, integer, and numeric with precision and scale in practical table design.

Master PostgreSQL date and time types, including timestamp, time zone, and interval, and learn to add createdat and updatedat fields with alter table for robust logging of promotions.

Define and enforce data integrity by applying null and not null constraints in PostgreSQL, use data dictionaries to document column rules, and ensure required fields are populated.

Learn how default values in PostgreSQL auto populate columns on insert, including booleans and integers, and how to set defaults via alter table for created at and updated at timestamps.

Discover how PostgreSQL enforces uniqueness with unique constraints and indexes, preventing duplicates across fields like product names, slugs, category names, and user emails.

Learn how primary keys uniquely identify records, enforce not null and unique constraints, and support fast lookups with auto-incrementing serial keys in PostgreSQL.

Create and enforce foreign keys to link tables, ensuring referential integrity, and build one-to-many relationships such as category to products, including self-referring keys for hierarchy.

Learn how on-delete rules govern one-to-many foreign keys in PostgreSQL, using cascade, set null, and restrict options to avoid orphaned rows and preserve referential integrity.

Build a many-to-many relationship in Postgres using a link table to connect products and promotion events, enforce not null, cascade deletes, and a unique constraint to prevent duplicates.

Establish a one-to-one relationship between products and stock management by adding a unique foreign key on the stock management table and enabling on delete cascade.

Enforce data integrity in PostgreSQL with check constraints that ensure column values meet a condition, including not empty strings and regex-based slug formats across single or multiple columns.

Define a complete category table in a single create table statement, including primary key, self-referencing foreign key, not null, unique, defaults, and a check constraint; explain table relationships.

Connect to a Postgres database using Pgadmin, set up the Pgadmin container with docker compose, then use the SQL query tool to run SQL against the inventory_db database.

Connect to a postgres database via PgAdmin, build and manage inventory tables one at a time with docker-compose, and use the ERD tool to visualize relationships and generate SQL.

Master essential data manipulation in PostgreSQL by inserting, updating, and deleting records, while ensuring data integrity, managing relationships, and handling bulk operations, backups, and restoration strategies.

Restart the Docker container and Postgres database, then recreate the database using the provided code to begin learning insert, update, and delete operations.

Learn how to insert new rows into SQL tables using insert into, with or without column lists, while honoring constraints, primary keys, and foreign keys.

Learn how to insert multiple rows in PostgreSQL efficiently using SQL insert statements, including loading data from JSON or CSV, and bulk inserts from other tables.

Learn to update existing records in PostgreSQL using the update statement, set clauses, and the where clause, with the returning clause to inspect changed rows for single or multiple records.

Learn to insert data with foreign keys in a one-to-many setup in PostgreSQL by creating a category first, then a product linked by category_id, enforcing referential integrity.

Explore handling one-to-one inserts in PostgreSQL by enforcing a unique foreign key in the stock management table, ensuring a matching product, using now() for timestamps, and configuring time zones.

Master many-to-many inserts by linking products and promotion events through a junction table of foreign keys, enforce uniqueness, and normalize data across categories, products, and promotions.

Explore deleting records with the delete statement in PostgreSQL, including table specification and where clauses, while understanding referential constraints and behaviors like on delete cascade and restrict.

Apply SQL deletion skills in PostgreSQL by using delete from with a where clause, and study on delete cascade versus restrict to manage foreign key relationships between category and product.

Load json data into postgresql by bulk inserting from json files, map a local folder into the docker container, and convert json to rows with jsonb into the category table.

Load CSV data into PostgreSQL using copy, ensure data matches table structure and headers, handle delimiters and UTF-8 encoding, and optimize imports with constraints and error handling.

Learn how to backup and restore a PostgreSQL database using logical backups with pg_dump, physical backups with pg_basebackup, and continuous archiving of wal logs for point-in-time recovery.

Learn to persist PostgreSQL data in a Docker container using a bind mount, binding the host PG data directory to /var/lib/postgresql/data, and compare with Docker volumes for data durability.

Learn to administer database data using pgAdmin and command line tools. Connect to a Postgres inventory database, run queries, and insert, update, or delete data via GUI and SQL workflows.

Create a PostgreSQL initialization script to set up tables and load CSV data before startup, and bind it via Docker Compose to initdb.d for automation.

Learn to query a PostgreSQL database by retrieving, filtering, sorting, and manipulating data with SQL, including selecting columns, conditions, pagination, and using functions, views, and aliases.

Learn to use the select statement to retrieve data from one or more tables, choosing all columns or specific fields, with aliases for readability. Choose specific columns for performance.

Explore how the where clause filters records from tables like products and category by applying conditions, using equals to return active or inactive rows and selecting specific fields.

Learn how to sort query results using order by in sql, choosing ascending or descending by one or multiple columns such as price, category id, or dates.

Learn to return a single record from the products table by ordering by price desc and applying limit one, to fetch the most expensive item.

Learn how limit and offset enable pagination in SQL using PostgreSQL, retrieving subsets of records and displaying data in pages with practical examples of starting points and offsets.

Use the distinct keyword after select to remove duplicate values and return only unique results, illustrated with active boolean fields and categories referenced by products.

Use aliases to rename columns and tables in SQL queries for readability, as they exist only for the duration of the query and do not alter the database structure.

Explore how views in PostgreSQL serve as virtual tables that fetch data dynamically from underlying tables, enabling simplified, secure access via a central resource that applications connect to and utilize.

The lecture introduces PostgreSQL functions, showing how to create, return types, parameters, and dynamic queries with plpgsql, and contrasts functions with views for encapsulating business logic.

Master SQL data filtering with logical and comparison operators, pattern matching, and range-based techniques to retrieve precise results and boost database efficiency for analytics.

Apply the where clause with and, or, and not to filter rows by multiple conditions, negate criteria, and build flexible SQL queries.

Explore SQL comparison operators such as =, not equal, >, <, >=, and <=, and learn how to filter data with where clauses, dates, and logical combinations.

Use the like operator for pattern matching in text with the where clause, employing modulus and underscore wildcards, with case sensitivity and casting examples.

Use in and not in to filter rows by whether a column matches values in a list or is not in it, with examples on category IDs, usernames, and orders.

Master the between operator to filter records by ranges in PostgreSQL, using numbers, dates, and text, with examples on prices, orders, and promotion events.

Master SQL joins to combine data across tables, including inner, left, and full outer joins for one-to-many, 1-to-1, and many-to-many relationships, retrieve matching and non-matching records, and optimize queries.

Learn how SQL joins combine rows from related tables using foreign keys, linking customers to orders, and explore the three join types, including right join and full join.

Use inner joins to combine related tables in one-to-many relationships, such as products and categories, linking via foreign keys and primary keys to return only matching rows.

Explore inner joins for one-to-one relationships in PostgreSQL, linking products to stock management via foreign keys, filtering nonzero stock, and using table aliases.

Use inner joins to map many-to-many relationships between promotions and products via a join table, returning event names and product names. Identify currently active promotions by start and end dates.

Use the left join (left outer join) to return records from the left table, with matches from the right table or nulls when no match, illustrated by products and categories.

Master left joins in a 1-to-1 relationship between stock management and products, retrieving all products with their stock data, including missing records, from either side.

Learn to implement left joins for many-to-many relationships using a link table, returning all left-side records with any matching right-side promotion events.

master the full outer join by returning all rows from both tables, with nulls for unmatched data, enabling comprehensive reports that preserve records from both sources, even when no match.

Master SQL aggregation and grouping in PostgreSQL to count records, sum and average values, and filter results with group by and having, enabling insightful data analysis and decision making.

Learn how to use the count function in SQL to count all rows, non-null values, and unique values with distinct in PostgreSQL across products and categories.

Master the sum function in SQL to calculate the total of a numeric column, with stock, inventory, and revenue examples using joins and aliases.

Learn to compute the average (mean) of a numeric column using the avg function in a select statement, applying where filters and aliases to calculate active product prices.

This lecture demonstrates using group by to group rows by category or order and apply aggregate functions like sum, count, and average for per-group sales, stock, and revenue.

Learn how to use the having clause to filter groups after aggregation with group by, using aggregate values and comparison operators to refine total sales per category.

Use min to find the smallest value and max to find the largest value in a column, applying to prices or dates and filtering with group by and having.

Master a PostgreSQL SQL query to retrieve only active products, returning id, name, price, and is_active, and order results by created_at in descending (newest first).

Query the category table to retrieve root categories where parent_id is null, returning id, name, slug, and is_active, and order results by name.

Learn to fetch all active promotions from the promotion events table by filtering start and end dates using now in PostgreSQL and order by start date ascending.

Write a PostgreSQL query using inner join between category and product to return category id, category name, product id, product name, price for a given category, ordered by product name.