Hands-on Lab: Create Tables using SQL Scripts and Load Data into Tables

Estimated time needed: 30 minutes

In this lab, you will learn how to run SQL scripts to create several tables at once, as well as how to load data into tables from .csv files.

Software Used in this Lab

In this lab, you will use IBM Db2 Database. Db2 is a Relational Database Management System (RDBMS) from IBM, designed to store, analyze and retrieve the data efficiently.

To complete this lab you will utilize a Db2 database service on IBM Cloud. If you did not already complete this lab task earlier in this module, you will not yet have access to Db2 on IBM Cloud, and you will need to follow this lab first:

Database Used in this Lab

The database used in this lab is an internal database. You will be working on a sample HR database. This HR database schema consists of 5 tables called EMPLOYEES, JOB_HISTORY, JOBS, DEPARTMENTS and LOCATIONS. Each table has a few rows of sample data. The following diagram shows the tables for the HR database:

Objectives

After completing this lab, you will be able to:

NOTE : Make sure that you are using the CSV file and datasets from the same instruction file.

Exercise 1: Create tables using SQL scripts

In this exercise, you will learn how to execute a script containing the CREATE TABLE commands for all the tables rather than create each table manually by typing the DDL commands in the SQL editor.

  1. Download the script file to your computer:

  1. Login to IBM Cloud and go to the Resource List where you can find the Db2 service instance that you created in a previous lab under Services section. Click on the Db2-xx service. Next, click on Go to UI button.

    image

  2. Click on Run SQL on the left corner and select the from file option.

    image

  3. Locate the file HR_Database_Create_Tables_Script.sql that you downloaded to your computer earlier and open it.

  4. Once the statements are in the SQL Editor tool , you can run the queries against the database by selecting the Run All button.

    image

  5. On the right side of the SQL editor window you will see a Result section. Clicking on a query in the Result section will show the execution details of the job like whether it ran successfully, or had any errors or warnings. Ensure your queries ran successfully and created all the tables.

    image

  6. Now you can look at the tables you created. Click on the data icon and then click on Tables tab

    image

  7. Select the Schema corresponding to your Db2 userid. It typically starts with 3 letters (not SQL) followed by 5 numbers (but will be different from the MYG36304 example below). Then on the right side of the screen you should see the 5 newly created tables listed – DEPARTMENTS, EMPLOYEES, JOBS, JOB_HISTORY and LOCATIONS (plus any other tables you may have created in previous labs e.g. PETSALE, PETRESCUE, etc.).

    image

  8. Click on any of the tables and you will see its Table Definition (that is, its list of columns, data types, etc).

    image

Exercise 2: Load data into tables

In this exercise, you will learn how data can be loaded into Db2. You could manually insert each row into the table one by one, but that would take a long time. Instead, Db2 (and almost every other database) allows you to load data from .CSV files.

The steps below explain the process of loading data into the tables you created earlier in exercise 1.

  1. Download the 5 .csv files below to your local computer:

  1. In the Db2 Console, from the 3-bar menu icon in the top left corner, click Load, and then select Load Data.

    image

  2. On the Load Data page that opens, ensure My Computer is selected as the source. Click on the browse files link.

    image

  3. Choose the file Employees.csv that you downloaded to your computer and click Open.

    image

  4. Once the File is selected, click Next in the bottom right corner.

    image

  5. Select the schema for your Db2 Userid (the one where you created the tables earlier).It will show all the tables that have been created in this schema previously, including the Employees table. Select the EMPLOYEES table, and in the new Table Definition tab that appears, choose Overwrite table with new data (note the warning message), then click Next. Select the Employees table.

    image

  6. Since the source data files do not contain any rows with column labels, turn off the setting for Header in first row. Also, click on the down arrow next to Date format and choose MM/DD/YYYY since that is how the date is formatted in the source file.

    image

  7. Click Next. Review the load settings and click Begin Load in the bottom right corner.

    image

  8. After loading has completed, you will notice that you were successful in loading all 10 rows of the Employees table. If there are any Errors or Warnings, you can see them on this screen.

    image

  9. Click on the Tables tab and then select the EMPLOYEES table and then click on View data.

    image

  10. Now you can view the table data.

    image

  11. Now it's your turn to load data to the remaining 4 tables of the HR database – LOCATIONS, JOB_HISTORY, JOBS, and DEPARTMENTS from the remaining source files.

  12. Click Load More Data and then follow the steps from Step 3 above again to load the remaining 4 tables.

IMPORTANT Make sure you perform the steps in Step 7 for each of the 4 remaining file loads.

Congratulations! You have completed this lab, and you are ready for the next topic.

Author(s)

Other Contributor(s)

Changelog

Date Version Changed by Change Description
2021-07-30 2.3 Lakshmi Holla Updated screenshot of DB2
2021-07-08 2.2 Malika Updated screenshot
2020-12-23 2.1 Steve Ryan ID Review
2020-12-08 2.0 Sandip Saha Joy Created revised version from DB0201EN
2020 1.0 Rav Ahuja Created initial version

© IBM Corporation 2020. All rights reserved.