Database Tutorial

This lesson focuses two things. First, how to aggregate numeric values in the database. Second, on how to get external data into the database in bulk.

It takes a considerable amount of data to make aggregation useful and to illustrate methods. Initial aggregation examples use only ordinal numbers but the lab uses transaction amounts linked to Video Store rentals.

The loading examples focus on importing Comma Separated Value (CSV) files into tables. These are often critical skills in any Extract, Transform, and Load (ETL) operation of a data warehouse or major data migration effort.

Transactions control an all or nothing import of values. They are the reason we learn how to write queries and other DML statements. Transactions are the process whereby we guarantee the ordering of events among several tables, and guarantee ACID compliant transactions. These elements linked to loading the data reinforce concepts introduced during Lesson #4 on Inserting Data, and extend the content of the Transaction Management post.

Aggregation

Learn how to use aggregation in SQL statements.

Oracle External Tables

Learn how to use Oracle External Tables to read the contents of CSV files.

MySQL CSV Upload

Learn how to use the LOAD LOCAL DATA clause to read a CSV file into an existing table.

MERGE Statement

Learn how to use the MERGE statement to insert a new record when it doesn’t exist, or update a row when the value already exists.

Import Transaction Units

Learn how to import CSV files into a database by leveraging the MERGE statement and ON DUPLICATE KEY clause. These let you insert a new record when it doesn’t exist, or update a row when the value already exists.