This course has been updated! We now recommend you take the Complete Intro to SQL & PostgreSQL course.
Table of Contents
Foundations of Relational Databases
Welcome & Tech Check
Mike North introduces the SQL Fundamentals course. Mike discusses why it's worthwhile to master using a database, even if you're a frontend-focused developer. - Detailed setup instructions are here: https://github.com/mike-works/sql-fundamentals
Relational Algebra & Codds Relational Model
In 1970, Edgar Codd invented a new way to model large, organized and shared piles of data using the expressive semantics provided by relational algebra. Today, virtually all relational databases are still based on these fundamental principles. Mike reviews the course agenda which includes evaluating conceptual models behind tables, columns, result sets and "joins."
Retrieving Data
Using the Command Line & Simple SELECT queries
All relational databases use some variant of a mostly declarative programming language called Structured Query Language (SQL) to perform operations. Mike shows what SQL looks like and writes a few SQL statements.
Project Overview
Mike takes a close look at the course project, including where code and tests are located. Mike also examines important scripts for building and running tests.
SELECTing Columns Exercise
Since selecting all columns in a table is inappropriate for a production app, Mike shows how to explicitly pick which columns are needed for several collections of data that the app needs, and shows the improved performance gained.
SELECTing Columns Solution
Mike walks through the solution to SELECTing Columns Exercise.
Filtering via WHERE clauses
Mike demonstrates that it is often undesirable to work with all tuples, or "rows," from a given table. Adding a WHERE clause to our SELECT query allows us to specify one or more criteria for filtering the result set.
Filtering via WHERE Exercise
In this exercise, students show how to add WHERE clauses to the collection query for two pages on the app. On the products list page, students allow the user to filter by those products that need to be reordered, discontinued, or display the full list. On the customer list page, students add a rudimentary search field and use a LIKE clause to find matching rows.
Filtering via WHERE Solution
Mike walks through the solution to the Filtering via WHERE Exercise.
LIMITing and ORDERing the result set
Mike illustrates that when working with large collections of data, it is essential to be able to sort data as well as paginate or scroll through the results. Mike demonstrates how to do this with LIMIT and OFFSET to retrieve the records of interest, and ORDER BY to sort.
LIMIT and ORDER Exercise
In the example app, the orders page has over 16,000 records. This large amount of data is too much data to show to users all at once. Even looking at an individual customer’s orders is a bit overwhelming. In this exercise, students use the existing user interface for sorting and pagination, and modify the “orders list” and “customer orders list” queries as appropriate.
LIMIT and ORDER Solution
Mike walks through the solution to the LIMIT and ORDER Exercise.
Querying Across Tables
Relationships & Joins
There are five types of joins in most relational database systems, but we can get away with focusing almost entirely on the two categories: INNER and OUTER joins. Mike discusses the distinction between these two types, and when to use the appropriate join operation.
JOIN Exercise
There are several places in the course app where alphanumeric IDs are shown to users. Since users prefer referring to things by names, students in this exercise use JOIN to transform these references into records that are more user-friendly.
JOIN Solution
Mike walks through the solution to the JOIN Exercise.
Aggregate Functions and GROUP BY
Often, there are situations when we want to summarize data that is aggregated over a result set. For example, a possible query would be “give me the number of products we have in each category." Through using GROUP BY, Mike shows how to define the records using aggregate functions like sum, count, group_concat to aggregate over duplicate data.
Aggregate Functions and GROUP BY Exercise
In this exercise, students need to get the subtotal of an order’s line items and display it prominently at the bottom of the order page. Then, students count and concatenate aggregate results as we group records on the employee, customer, and product list pages.
Aggregate Functions and GROUP BY Solution
Mike walks through the solution to the Aggregate Functions and GROUP BY Exercise.
Creating, Updating, and Deleting
Creating & Deleting Records
Since CREATE and DELETE are considerably simpler than the SELECT statement, and these are queries that are often created by users in an application, Mike reviews SQL injection attacks and how to defend against them.
Creating & Deleting Records Exercise
In this exercise, students build the proper queries for creating new orders and updating existing ones, while being sure to avoid susceptibility to SQL injection attacks.
Creating & Deleting Records Solution
Mike walks through the solution to the Creating & Deleting Records Exercise.
Transactions & Isolation
Mike explains the importance of transactions which help to guarantee data consistency. He then discusses how isolation levels can effect the trade-off between concurrency and consistency.
Transactions Exercise
In this exercise, students use a transaction to update our SQL statement for creating a new order.
Transactions Solution
Mike walks through the solution to the Transaction Exercise.
Updating Records
Mike discusses updating records, and how various databases address the "insert or update" idea.
Updating Records Exercise
In this exercise, students fix the "edit order" feature by building a transaction that updates an order and its associated OrderDetail records
Updating Records Solution
Mike walks through the solution to the Updating Records Exercise.
The Schema Evolves
Migrations
Storing changes to database's schema alongside code ensures that developers can have reproducible builds. Mike studies the concept of database migrations, and how they can used to incremental and ideally reversible changes to the shape of our data to manage complexity more easily.
Indices
Indices are a speed-for-memory tradeoff, where the database does some extra bookkeeping to keep queries fast. Mike looks at how to identify an opportunity for adding an index, how to create one, and how to measure its effectiveness.
Add Indices Exercise
When asking our database to explain the work required to perform JOIN queries, developers can quickly recognize that some well-placed indices should be able to improve performance. In this exercise, students create a new DB migration that adds these new indices to a database's schema.
Add Indices Solution
Mike walks through the solution to the Add Indices Exercise.
Column Constraints
As part of ensuring data integrity in a relational database involves designing the right column constraints, Mike looks at types, NOT NULL, and foreign keys as mechanisms for validating data at the database level.
Column Constraints Exercise
In this exercise, students create a new table for payment transactions on orders and design appropriate column constraints to ensure only valid, paid orders.
Column Constraints Solution
Mike walks through the solution to the Column Constraints Exercise.
