MySQL, PostgreSQL and MongoDB Database Design Masterclass

Discover why learning PostgreSQL matters: it's a free, open source, latest-version object-relational database offering reliability, robust features, and strong performance for managing tables.

Download PostgreSQL on Windows using the interactive installer and choose the 64‑bit version 17.0. Complete the setup wizard with the server, Pgadmin, and stack builder, then set a password.

Connect to a PostgreSQL database using the sql shell and PgAdmin 4, configure localhost port 5432, enter credentials, and explore creating and selecting the default database.

Create your first PostgreSQL database using SQL shell and pgAdmin, using backward slash commands and the create statement with a semicolon.

Learn to connect to your PostgreSQL databases using the backslash commands, typing connect followed by the database name, and switch between databases such as strains, student, memoir.

Learn how to drop a PostgreSQL database, a dangerous operation, using drop database and \l to list databases, and handle active connections via pgadmin or SQL shell.

learn to create a PostgreSQL table in the school database, defining students with columns student_id integer, first_name varchar, last_name varchar, gpa decimal (one decimal place), and enroll_date date.

Create a student table with constraints in PostgreSQL, enforce not null for id, first name, last name, gp, and enroll date, make email optional, and use auto increment primary key.

Learn to insert data into a PostgreSQL students table using insert into, set first_name, last_name, and gp, manage enrolled_date and email, and note primary key autoincrement and not null constraints.

Learn how to drop a column in PostgreSQL using either the graphical interface or SQL shell, with a step-by-step example removing the GP column from the students table.

Master PostgreSQL filtering with the where clause to extract records that meet specific conditions, using examples with student_id, first name, and last name fields in pgadmin queries.

Learn how to add a new age column to an existing PostgreSQL table using alter table and add, with the age column as integer on the students table.

Learn how to update rows in PostgreSQL, including setting emails and ages, filtering with where clauses, and verifying results with select queries and PgAdmin.

Learn how to delete a record in PostgreSQL using a where clause and id, and perform deletion via pgAdmin to manage student data.

Learn how to reset a PostgreSQL table's id sequence after deletions by using delete, insert, and setval to align the sequence with the current max id in the students table.

Generate 1000-row student data set using mockaroo via Mercado, export as SQL or CSV, and create a table with incrementing id, first name, last name, email, gender, date, and country.

Master the basic select syntax in PostgreSQL to query a table, choosing all columns or specific ones like first name, last name, email, and enroll date.

Learn how the order by clause in PostgreSQL sorts data in ascending or descending order using columns like first name, last name, and email.

Learn how the distinct keyword returns unique values in PostgreSQL queries, using select for countries and names, and applying order by in ascending or descending order.

Learn to apply where and and clauses in PostgreSQL to filter a students dataset by country, gender, and first name, using multiple conditions to produce precise results.

Master the in keyword in PostgreSQL to replace long or chains, writing cleaner queries that list multiple countries (Nigeria, Brazil, China, Mexico, Yemen) and extend this pattern to other fields.

Explore how to use the PostgreSQL between keyword to filter date ranges and IDs, with practical examples on dates and student IDs.

Learn to use wildcards in PostgreSQL with the like operator, leveraging % and _ to filter emails and names, with attention to case sensitivity.

Learn to group by clause in PostgreSQL to count students by country, using group by country, count, and order by country to display results alphabetically.

Learn how to use the having clause in PostgreSQL to filter grouped country counts, applying conditions like greater than, less than, and greater than or equal to 5 or 100.

Discover how the coalesce function in PostgreSQL returns the first non-null argument from a list. Explore practical examples with nulls, numbers, and emails, including using a default like no email.

Use the age function in PostgreSQL to compute student ages from enrollment dates, address negative ages by adjusting dates, and create a new table for students' age.

Discover how PostgreSQL enforces unique identifiers with a primary key, preventing duplicate student IDs and triggering a duplicate key value error, and how to drop constraints when needed.

Learn how to enforce unique emails in PostgreSQL by adding a unique constraint on the emails column, removing duplicates, and understanding constraint naming.

Discover how PostgreSQL check constraints enforce valid gender values, using a constraint on the students table to allow only male or female.

Explore PostgreSQL's on conflict do nothing to gracefully handle duplicate keys on unique constraints such as student_id and email. See when this approach works and when it doesn't.

learn how to use on conflict do update in PostgreSQL to update existing records using excluded values for fields like email, gender, and names.

Create a PostgreSQL table for a phone, with id as auto increment primary key and not null brand, model, price. Generate SQL and verify with a select from phone.

Create and link the student and phone tables with a foreign key that references the phone table's primary key, enforcing not null and unique constraints.

Assign phones to students by updating the students.phone_id using PostgreSQL. A foreign key constraint prevents assigning the same phone to more than one student.

Master how to join two tables in PostgreSQL using join and on, matching student and phone IDs to display each student's name with their phone brand and price.

Discover how to use left join in PostgreSQL to include all students, with or without phones, by comparing left join with inner join and building a complete dataset.

Learn how to delete a foreign key in PostgreSQL by removing child rows before their parent, enforcing foreign key constraints, and troubleshooting violations during delete operations.

Export a joined student and phone dataset to a csv file in PostgreSQL using a left join and copy command, then save it to a folder for easy sharing.

Install and configure MongoDB on Windows, including the community edition, compass GUI, and mongosh shell. Learn to work with collections and documents in this non-relational database.

Learn the basics of MongoDB, a non-relational database using collections and documents. Start Mongo, switch databases, create a new db and a sample collection, insert and query documents.

Learn how to create and manage collections in MongoDB, including using create collection, insert one, show collections, and capped collections with sizing and auto index on _id.

Connect and manage MongoDB from Visual Studio Code using the MongoDB extension, install and trust the workspace, and explore local, admin, and config databases with documents and object IDs.

Launch the MongoDB shell inside Visual Studio Code from localhost to write queries in the terminal, switch databases, and explore collections like employee, person, and students with documents.

Master MongoDB Compass to connect to a server, create and drop databases, manage collections, and synchronize changes with the MongoDB shell and Visual Studio Code.

Learn to insert multiple documents into a MongoDB collection with insertMany, after first inserting a single document in the school.students collection.

Learn to insert single and multiple documents in MongoDB Compass, format data correctly, handle errors, and manage collections within your database.

Enable the embedded MongoDB shell inside Compass, connect to a local host, and use autocomplete to query and insert data.

Master how to sort and limit documents in MongoDB by name, age, or GPA, using ascending and descending orders and combining sort with limit on a students collection.

Explore how to use the find method in MongoDB to query by name, age, or GPA, and apply the optional projection parameter to control returned fields and exclude _id.

Learn to update documents in MongoDB with the update method, choosing one or many documents via a filter and applying changes with $set or $unset; verify results with find.

Learn to delete documents in MongoDB using the mongo shell, including single deletions by field value and multi deletions by a condition, with JSON export/import for backup.

Learn how MongoDB comparison operators filter data by value, including not equal, less than, greater than, and range queries, and use in and not in with array syntax.

Learn how to use the and, or, and not logical operators in MongoDB to filter data by conditions like undergraduate status and age, with practical find examples.

Learn how MongoDB uses indexes to speed queries, inspect existing indexes with explain and get indexes, create and drop indexes, and understand the trade-offs for insert, update, and delete operations.

Learn to create unique IDs in MongoDB by inserting documents with a custom _id (for example 1000) and observe how it differs from MongoDB's default ObjectId.

Explore embedded documents in MongoDB by nesting documents inside a student record, then insert and import JSON, and query school fees payment type yearly or monthly, noting 16 MB limit.

Learn to update a nested field inside an embedded document in MongoDB using update to one and set, and retrieve nested values with find one.

Embed arrays inside MongoDB documents, insert complex structures with addresses and courses, and query by nested fields such as Paris and geology, including course levels from beginner to advanced.

Compare embedded and reference documents in MongoDB, showing how to model data across collections. Learn the advantages, drawbacks, and how to avoid data duplication and 16 MB size limits.

Demonstrate implementing a 1-to-1 relation in MongoDB using reference documents, by creating students and costs collections, updating with set, and linking via object IDs.

Master the one-to-one reference using the lookup in an aggregation to join the student and course collections, using from, local field, foreign field, and as to populate the related data.

Master ordered insertion in MongoDB by using custom IDs, inserting with insertMany, and handling duplicate key errors, including the effect of the ordered option.