Microsoft
Course Description
SQL is a timeless skillset you'll find in nearly every modern application! Using the popular PostgreSQL database, you'll learn to set up, model, and query your data through real-world projects. You'll also understand how to model complex relationships in your data and query data from large datasets.
Preview
Course Details
Published: October 3, 2022
Rating
Learning Paths
Learn Straight from the Experts Who Shape the Modern Web
Your Path to Senior Developer and Beyond
- 300+ In-depth courses
- 24 Learning Paths
- Industry Leading Experts
- Live Interactive Workshops
Table of Contents
Introduction
Section Duration: 20 minutes
Brian Holt walks through the course website and explains how this course is designed for developers who want to know how to use SQL and PostgreSQL.
Brian explains that Docker allows any computer to run a lightweight emulation of a pre-made environment. Two Docker containers that contain PostgresSQL 14 and prepopulated data are installed and will be used throughout the course.
Databases and Tables
Section Duration: 34 minutes
Brian shares some of the history of SQL and demonstrates how to create a database. The psql client provides a number of built-in commands to navigate and display information about databases and tables. While SQL syntax is case insensitive, SQL commands are typically written in all capital letters.
Brian demonstrates how to create a table in a database and adds data to the table. Tables are structured like spreadsheets because they contain rows and columns. The columns are the different fields within the table. The row are individual records of data.
Brian uses the ALTER TABLE command to add and remove columns in the ingredients table. When columns are added to an existing table, default values should be specified to satisfy any NOT NULL constraints.
SQL Commands
Section Duration: 1 hour, 15 minutes
Brian uses the INSERT INTO command to add data to the table. The values for each column should match the order the columns are listed in the insert statement. The ON CONFLICT command indicates how primary key conflicts should be handled.
Brian updates existing data by specifying the columns to update and a WHERE clause for the row that should be updated. If the WHERE clause is omitted, all rows will receive the updated value. Deleting data also utilizes a WHERE clause for determining the correct row to remove.
Brian introduces the SELECT command and takes a deeper look at WHERE clauses. Conditional operators like >=, <=, and <> can be used to narrow the results. Pagination is implemented by using the LIMIT keyword.
Brian demonstrates how LIKE and ILIKE can be added to a WHERE clause to search for partial strings. SQL functions like LOWER and CONCAT can be combined to create more unique search conditions.
Brian introduces node-postgres which is a collection of node.js modules for interfacing with your PostgreSQL database. One benefit of node-postgres is the ability to prevent SQL injection which happens when a user attempts to pass SQL through a query string. node-postgres will sanitize the query string parameters before running the database query.
Students are instructed to use node-postgres to implement the ingredients API in the application. Instructions are located in the code comments in /ingredients/API.js. Installation instructions and troubleshooting tips are also included in this lesson.
Brian walks through the solution to the Ingredients API exercise.
Joins and Constraints
Section Duration: 1 hour, 14 minutes
Brian explains database relationships and demonstrates inner joins. IDs from one table are stored in a column of another table and used to display a combination of columns from both tables. With inner join, if any data is missing from either table, the entire row is omitted from the resulting dataset.
Brian explains the difference between left, right, and full joins. The join direction determines which tables data is included in the dataset and which table data is omitted if a column's value doesn't exist.
Brian demonstrates natural joins which use columns with the same name to create a join. Cross joins generate a dataset with every permutation between the two joining tables.
Brian explains how foreign key constraints enforce relationships between tables. Adding an ON DELETE clause will instruct the database on how to handle the removal of foreign keyed items. For example, when a foreign keyed item is removed, the database should remove all other instances of the foreign key from the referenced table.
Brian models a many-to-many relationship between the recipies table and the ingredients table. Since one ingredient can belong to many recipes and a recipe has many ingredients, the primary key for the recipe_ingredients table is the combination of a recipe_id and an ingredient_id.
Brian uses the CHECK constraint to ensure the type column can only be a specific value.
Brian demonstrates how to return the first instance of a value by using the DISTINCT constraint. This is useful when a column contains duplicate values and only the first record is required.
Students are instructed to implement the search and get recipe by ID APIs. Instructions are located in the code comments in /recipes/API.js.
Brian walks through the solution to the Recipes exercise.
JSONB
Section Duration: 25 minutes