Table of Contents
Foundations of Relational Databases
Welcome & Tech CheckMike 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 ModelIn 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."
Using the Command Line & Simple SELECT queriesAll 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 OverviewMike 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 ExerciseSince 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 SolutionMike walks through the solution to SELECTing Columns Exercise.
Filtering via WHERE clausesMike 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 ExerciseIn 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 SolutionMike walks through the solution to the Filtering via WHERE Exercise.
LIMITing and ORDERing the result setMike 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 ExerciseIn 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 SolutionMike walks through the solution to the LIMIT and ORDER Exercise.
Querying Across Tables
Relationships & JoinsThere 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 ExerciseThere 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 SolutionMike walks through the solution to the JOIN Exercise.
Aggregate Functions and GROUP BYOften, 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 ExerciseIn 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 SolutionMike walks through the solution to the Aggregate Functions and GROUP BY Exercise.
Creating, Updating, and Deleting
Creating & Deleting RecordsSince 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 ExerciseIn 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 SolutionMike walks through the solution to the Creating & Deleting Records Exercise.
Transactions & IsolationMike 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 ExerciseIn this exercise, students use a transaction to update our SQL statement for creating a new order.
Transactions SolutionMike walks through the solution to the Transaction Exercise.
Updating RecordsMike discusses updating records, and how various databases address the "insert or update" idea.
Updating Records ExerciseIn this exercise, students fix the "edit order" feature by building a transaction that updates an order and its associated OrderDetail records
Updating Records SolutionMike walks through the solution to the Updating Records Exercise.
The Schema Evolves
MigrationsStoring 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.
IndicesIndices 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 ExerciseWhen 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 SolutionMike walks through the solution to the Add Indices Exercise.
Column ConstraintsAs 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 ExerciseIn 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 SolutionMike walks through the solution to the Column Constraints Exercise.