Lectures

In-Class Activity - SQL Joins Practice with Bend Blockbuster Database

DATA 351: Data Management with SQL

Web Scraping with Web Scraper

DATA 351: Data Management with SQL

This session introduces the Web Scraper browser extension for Chromium-based browsers. We follow the official “Scraping a site” workflow (sitemap, selectors, scrape, export), then walk through a live-style demo: paginated Discogs search results, a Link selector into each release detail page, and Text selectors for community statistics on that page (for example Have, Want, and rating summaries). Primary reference: Web Scraper documentation.

Lucas P. Cordova, Ph.D.

Mining Text Data ⚒️

DATA 351: Data Management with SQL

This lecture covers string functions, POSIX regular expressions in PostgreSQL, extracting structured fields from messy narrative text, and full-text search with tsvector, tsquery, GIN indexes, ts_headline, and ts_rank. We use crime report narratives and State of the Union speeches. Based on Chapter 14 of Practical SQL, 2nd Edition.

Lucas P. Cordova, Ph.D.

Lecture 13: Advanced Query Techniques

DATA 351: Data Management with SQL

This lecture covers advanced SQL query techniques including subqueries (in WHERE, FROM, and SELECT clauses), subquery expressions (IN and EXISTS), LATERAL joins, Common Table Expressions, cross tabulations with crosstab(), and CASE expressions. We apply these patterns to Census, survey, and temperature data. Based on Chapter 13 of Practical SQL, 2nd Edition.

Lucas P. Cordova, Ph.D.

Lecture 12: Working with Dates and Times

DATA 351: Data Management with SQL

This lecture covers working with dates, times, and time zones in PostgreSQL. We explore date/time data types, extraction functions, time zone handling, date math, and interval calculations. We then apply these concepts to real-world datasets: NYC yellow taxi trip data and Amtrak train schedules. Based on Chapter 12 of Practical SQL, 2nd Edition.

Lucas P. Cordova, Ph.D.

Lecture 11-1: Statistical Functions in SQL

DATA 351: Data Management with SQL

This lecture covers statistical functions built into PostgreSQL. We explore correlation, linear regression, variance, standard deviation, ranking with window functions, rate calculations, and rolling averages. Based on Chapter 11 of Practical SQL, 2nd Edition.

Lucas P. Cordova, Ph.D.

Lecture 11: Advanced Statistics with Flight and Weather Data

DATA 351: Data Management with SQL

This lecture introduces advanced SQL statistics using real flight and weather data collected over Portland, Oregon. We transform raw JSON data into clean relational tables, then use correlation, ranking, window functions, and CASE statements to answer a simple question: do more planes fly over Portland when the weather is nice? Based on Chapter 11 of Practical SQL, 2nd Edition.

Lucas P. Cordova, Ph.D.

Lecture 10: Modifying Data and Migrating Tables

DATA 351: Data Management with SQL

This lecture covers how to modify data in PostgreSQL using ALTER TABLE, UPDATE, DELETE, and transactions. Building on Chapter 10 of Practical SQL, we apply these techniques to a Gen-Z pop music catalog (2015-2025) with intentional data quality issues. Students will clean a messy staging table and migrate the cleaned data into a 3NF normalized schema of artists, albums, and tracks. This is a hands-on, interactive session where students build their own SQL cleanup script to turn in.

Lucas P. Cordova, Ph.D.

Lecture 09: Inspecting and Modifying Data

DATA 351: Data Management with SQL

This lecture covers how to inspect raw data for quality issues and modify it using SQL. We continue with our music catalog dataset, finding duplicates, missing values, inconsistent casing, and typos in a staging table, then fixing them with UPDATE, ALTER TABLE, DELETE, and transactions before migrating data into normalized tables.

Lucas P. Cordova, Ph.D.

Lecture 08: Creating Tables and Constraints

DATA 351: Data Management with SQL

This lecture covers how to build database tables with proper constraints in PostgreSQL. After learning normalization, we now implement those designs with CREATE TABLE, primary keys, foreign keys, CHECK, UNIQUE, and NOT NULL constraints. We also cover ALTER TABLE for evolving schemas and indexes for query performance. A music catalog dataset serves as the running example.

Lucas P. Cordova, Ph.D.

Lecture 06: SQL Joins

DATA 351: Data Management with SQL

This lecture covers SQL joins using the Blockbuster Bend database. We explore inner joins, outer joins, cross joins, and self joins with real examples from film rental data.

Lucas P. Cordova, Ph.D.

Lecture 07: Normalization and Data Modeling

DATA 351: Database Design

This lecture covers database normalization and data modeling fundamentals. We explore the normalization process from messy flat files to well-structured relational schemas, practice with the Dunder Mifflin employee dataset, explore database design for the Oregon Turtle Conservation project, and learn to create Entity-Relationship diagrams using draw.io.

Lucas P. Cordova, Ph.D.

Lecture 03: Data Types

DATA 351: Data Management with SQL

This lecture covers the data types.

Lucas P. Cordova, Ph.D.

Lecture 04: Importing and Exporting Data

DATA 351: Data Management with SQL

This lecture covers the importing and exporting data.

Lucas P. Cordova, Ph.D.

Lecture 05: Math Operators and Functions

DATA 351: Data Management with SQL

This lecture covers the math operators and functions.

Lucas P. Cordova, Ph.D.

Lecture 02: Being Selective with SQL

DATA 351: Data Management with SQL

This lecture covers the SELECT, WHERE, ORDER BY, and LIMIT clauses of SQL.

Lucas P. Cordova, Ph.D.

Lecture 00: Course Mechanics

DATA 351: Data Management with SQL

This lecture covers the course mechanics for DATA 351: Data Management with SQL.

Lucas P. Cordova, Ph.D.

Lecture 01:Introduction to Data Management with SQL

DATA 351: Data Management with SQL

This lecture covers the introduction to data management with SQL.

Lucas P. Cordova, Ph.D.