🔗 Link: https://awsrestart.instructure.com/courses/1632/modules/items/886877

Lab Overview

Scenario

As your design plan has come together with solid input from the customer and your planning phase of the process, you have noted that it is time to insert some data into your database. The loyalty spreadsheet that has been provided has the customer’s ID number, loyalty number, points available, and other detailed information about the customer. You need to create a database table and insert this information into it from the csv file provided.

Objectives

In this lab, you will:

Exercise 1: Create a Table and Add Data

Before you start inserting data into the database, you need to create the loyalty table and insert a template line to start the table off with generic information.

TODO

Using MySQL Workbench, create a new table named loyalty in the pub1 database and add the following data into the table.

cust_id loyalty_number phone email name points
0 8675309 800-867-5309 [email protected] Jenny Martha 100

Steps

  1. Ensure that you are logged into the workspace and have MySQL Workbench open and are connected to the pub1 database.

  2. Right-click the Tables menu in Schemas box of MySQL Workbench, and click Create Table.

    Untitled

    1. Select inside the Name field text box and type loyalty to change the name of the table.

    2. Below the Name field, select the first line in the Column Name column. Type cust_id to add the first column.

    3. Select the first line in the Datatype column and choose INT as the appropriate data type.

      Untitled

    4. For cust_id column only, select PK to represent primary key and NN to represent not null.

      Untitled

    5. Repeat ### Steps 4–6 for the loyalty_number, phone, email, name, and points columns using the predetermined data types.

    6. In the bottom right corner of Workbench, click Apply to complete the table creation wizard.

    7. Review the SQL Script to ensure that you have listed the table columns and data types correctly; then click Apply and close the pane.

    8. In the Schemas pane, right-click and select the Refresh All menu option to populate the new table.

    9. Right-click the loyalty table and select the Select Rows – Limit 1000 option.

      Untitled

    10. Click the NULL value in the cust_id column within the Result Grid window.

    11. Type 0 and click in the loyalty_number column.

    12. Type 8675309 in the loyalty_number column; then click in the phone column.

    13. Fill out the remaining column information from the following table.

    cust_id loyalty_number phone email name points
    0 8675309 800-867-5309 [email protected] Jenny Martha 100

    Example View

    After you have entered all the data, your screen should look similar to the following figure.

    Untitled

    Note: To see the entire field, hover the mouse between table column names until a double error appears. Then click and drag the field to make it large enough to view your entries.

    Exercise 2: Import Data from a CSV File

    The loyalty csv file contains customer data with loyalty points already set up. Now that you have the initial table created for the loyalty customers and some template data set up, use the Data Import Wizard, from the Workbench Table menu to input the data into the table.

    TODO

    Use workbench to insert additional data from work / loyalty.csv that you extracted in the initial setup activity.

    Helpful Hint

    You may have to go up a level above your home directory to see the work directory.

    Steps

    1. Right-click the loyalty table and select the Table Data Import Wizard option.

    2. Click Browse, browse to the work directory and select your csv file to import, and click Open, then click Next.

    3. Confirm that pub1.loyalty table is selected in the drop down menu; then click Next.

    4. In the Configure Import Settings portion of the Table Data Import wizard, verify that the source columns match up to the appropriate destination columns and click Next.

      Untitled

    5. Click Next through the Table Data Import wizard dialog boxes, accepting all options, to execute the import of the csv file.