Database Tutorial

Course Tutorial Site

Site Admin

Lab #4 : MySQL Inserts

with 28 comments

Lab #2: Oracle Assignment

You begin these steps by running the apply_mysql_lab2.sql and seed_mysql_store_ri.sql script. A great starting point for this lab is to review the seed_mysql_store.sql script. You should use the script provided in the downloaded instance or create a script like:

-- Run the prior lab script.
\. /home/student/Data/cit225/mysql/lab2/apply_mysql_lab2.sql
\. /home/student/Data/cit225/mysql/lib/seed_mysql_store_ri.sql
 
TEE apply_mysql_lab4.txt
 
... insert code here ...
 
NOTEE

You should embed the verification queries inside your apply_mysql_lab4.sql script. All foreign key columns in steps 1 through 10 should be populated by subqueries that use the natural key values to discover the surrogate key values. No credit will be given for INSERT statements that use numeric literals for foreign key column values with an exception for both the CREATED_BY and LAST_UPDATED_BY columns.

Step 11 requires you to enter data values into the MEMBER_LAB, CONTACT_LAB, ADDRESS_LAB, STREET_ADDRESS_LAB, TELEPHONE_LAB, RENTAL_LAB, and RENTAL_ITEM_LAB tables. You will insert data into these tables in a specific order. The order depends on the degree that tables are unconstrained or constrained by their foreign key to primary key dependencies. That means you insert into the least dependent table first, which is the MEMBER_LAB table because it only has dependencies on the SYSTEM_USER_LAB and COMMON_LOOKUP_LAB tables. After inserting into the MEMBER_LAB table, you insert customer data into the CONTACT_LAB, ADDRESS_LAB table, STREET_ADDRESS_LAB table, and TELEPHONE_LAB tables. Then, you insert rows in the RENTAL_LAB and RENTAL_ITEM_LAB tables. Step 11 mimics how you would insert values into a series of tables in a dependent relationship from a web application form.

  1. [1 point] Insert two rows into the SYSTEM_USER_LAB table:

Instruction Details ↓

Some of the data values require that you look them up. You look them up in the COMMON_LOOKUP_LAB table. Then, you figure out (a) how to write a subquery to return the correct COMMON_LOOKUP_LAB_ID value and (b) how to embed the subquery in an INSERT statement.

Table Name: SYSTEM_USER_LAB
Column Name Column Value #1 Column Value #2
SYSTEM_USER_LAB_ID Sequence value Sequence value
SYSTEM_USER_NAME REACHERJ OWENSR
SYSTEM_USER_GROUP_ID Database Administrator Database Administrator
SYSTEM_USER_TYPE Home Home
FIRST_NAME Jack Ray
MIDDLE_NAME    
LAST_NAME Reacher Owens
CREATED_BY System Administrator System Administrator
CREATION_DATE Today’s date Today’s date
LAST_UPDATED_BY System Administrator System Administrator
LAST_UPDATE_DATE Today’s date Today’s date

You should use the following formatting and query to verify completion of this step:

1

SELECT * FROM system_user_lab;

It should display the following results:

+--------------------+------------------+----------------------+------------------+------------+-------------+-----------+------------+---------------+-----------------+------------------+
| system_user_lab_id | system_user_name | system_user_group_id | system_user_type | first_name | middle_name | last_name | created_by | creation_date | last_updated_by | last_update_date |
+--------------------+------------------+----------------------+------------------+------------+-------------+-----------+------------+---------------+-----------------+------------------+
|                  1 | SYSADMIN         |                    1 |                1 | NULL       | NULL        | NULL      |          1 | 2015-01-26    |               1 | 2015-01-26       |
|                  2 | REACHERJ         |                 1002 |             1010 | Jack       | NULL        | Reacher   |          1 | 2015-01-26    |               1 | 2015-01-26       |
|                  3 | OWENS            |                 1002 |             1010 | Ray        | NULL        | Owens     |          1 | 2015-01-26    |               1 | 2015-01-26       |
+--------------------+------------------+----------------------+------------------+------------+-------------+-----------+------------+---------------+-----------------+------------------+
3 rows in set (0.03 sec)

  1. [1 point] Insert two rows into the COMMON_LOOKUP_LAB table:

Instruction Details ↓

Here you enter only the new values for the American Express and Diner’s Club cards.

Table Name: COMMON_LOOKUP_LAB
Column Name Column Value #1 Column Value #2
COMMON_LOOKUP_LAB_ID Sequence value Sequence value
COMMON_LOOKUP_CONTEXT MEMBER_LAB MEMBER_LAB
COMMON_LOOKUP_TYPE AMERICAN_EXPRESS_CARD DINERS_CLUB_CARD
COMMON_LOOKUP_MEANING American Express Card Diners Club Card
CREATED_BY System Administrator System Administrator
CREATION_DATE Today’s date Today’s date
LAST_UPDATED_BY System Administrator System Administrator
LAST_UPDATE_DATE Today’s date Today’s date

You should use the following formatting and query to verify completion of this step:

1
2
3
4

SELECT   *
FROM     common_lookup_lab
WHERE    common_lookup_context = 'MEMBER_LAB'
AND      common_lookup_type IN ('AMERICAN_EXPRESS_CARD','DINERS_CLUB_CARD');

It should display the following results:

+----------------------+-----------------------+-----------------------+-----------------------+------------+---------------+-----------------+------------------+
| common_lookup_lab_id | common_lookup_context | common_lookup_type    | common_lookup_meaning | created_by | creation_date | last_updated_by | last_update_date |
+----------------------+-----------------------+-----------------------+-----------------------+------------+---------------+-----------------+------------------+
|                 1018 | MEMBER_LAB            | AMERICAN_EXPRESS_CARD | American Express Card |          1 | 2015-01-26    |               1 | 2015-01-26       |
|                 1019 | MEMBER_LAB            | DINERS_CLUB_CARD      | Diners Club Card      |          1 | 2015-01-26    |               1 | 2015-01-26       |
+----------------------+-----------------------+-----------------------+-----------------------+------------+---------------+-----------------+------------------+
2 rows in set (0.00 sec)

  1. [1 point] Insert two rows into the MEMBER_LAB table:

Instruction Details ↓

Some of the data values require that you look them up. You look them up in the COMMON_LOOKUP_LAB table. Then, you figure out (a) how to write a subquery to return the correct COMMON_LOOKUP_LAB_ID value and (b) how to embed the subquery in an INSERT statement.

Table Name: MEMBER_LAB
Column Name Column Value #1 Column Value #2
MEMBER_LAB_ID Sequence value Sequence value
MEMBER_TYPE Individual Individual
ACCOUNT_NUMBER X15-500-01 X15-500-02
CREDIT_CARD_NUMBER 9876-5432-1234-5678 9876-5432-1234-5679
CREDIT_CARD_TYPE American Express Card Diners Club Card
CREATED_BY System Administrator System Administrator
CREATION_DATE Today’s date Today’s date
LAST_UPDATED_BY System Administrator System Administrator
LAST_UPDATE_DATE Today’s date Today’s date

You should use the following formatting and query to verify completion of this step:

1
2
3
4
5
6
7
8
9

SELECT   m.member_lab_id
,        m.member_type
,        m.account_number
,        m.credit_card_number
,        cl.common_lookup_meaning AS credit_card_type
FROM     member_lab m INNER JOIN common_lookup_lab cl
ON       m.credit_card_type = cl.common_lookup_lab_id
WHERE    common_lookup_context = 'MEMBER_LAB'
AND      common_lookup_type IN ('AMERICAN_EXPRESS_CARD','DINERS_CLUB_CARD');

It should display the following results:

+---------------+-------------+----------------+---------------------+-----------------------+
| member_lab_id | member_type | account_number | credit_card_number  | credit_card_type      |
+---------------+-------------+----------------+---------------------+-----------------------+
|          1001 |        1005 | X15-500-01     | 9876-5432-1234-5678 | American Express Card |
|          1002 |        1005 | X15-500-02     | 9876-5432-1234-5679 | Diners Club Card      |
+---------------+-------------+----------------+---------------------+-----------------------+
2 rows in set (0.00 sec)

  1. [1 point] Insert two rows into the CONTACT_LAB table:

Instruction Details ↓

Some of the data values require that you look them up. You look them up in the COMMON_LOOKUP_LAB table. Then, you figure out (a) how to write a subquery to return the correct COMMON_LOOKUP_LAB_ID value and (b) how to embed the subquery in an INSERT statement.

Table Name: CONTACT_LAB
Column Name Column Value #1 Column Value #2
CONTACT_LAB_ID Sequence value Sequence value
MEMBER_LAB_ID MEMBER_LAB Foreign Key MEMBER_LAB Foreign Key
CONTACT_TYPE Customer Customer
LAST_NAME Jones Jones
FIRST_NAME John Jane
CREATED_BY System Administrator System Administrator
CREATION_DATE Today’s date Today’s date
LAST_UPDATED_BY System Administrator System Administrator
LAST_UPDATE_DATE Today’s date Today’s date

You should use the following formatting and query to verify completion of this step:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

SELECT   c.contact_lab_id
,        m.credit_card_type
,        c.member_lab_id
,        c.contact_type
,        c.last_name
,        c.first_name
FROM     member_lab m INNER JOIN common_lookup_lab cl1
ON       m.credit_card_type = cl1.common_lookup_lab_id INNER JOIN contact_lab c
ON       m.member_lab_id = c.member_lab_id INNER JOIN common_lookup_lab cl2
ON       c.contact_type = cl2.common_lookup_lab_id
WHERE    cl1.common_lookup_context = 'MEMBER_LAB'
AND      cl1.common_lookup_type IN ('AMERICAN_EXPRESS_CARD','DINERS_CLUB_CARD')
AND      cl2.common_lookup_context = 'CONTACT_LAB'
AND      cl2.common_lookup_type = 'CUSTOMER';

It should display the following results:

+----------------+------------------+---------------+--------------+-----------+------------+
| contact_lab_id | credit_card_type | member_lab_id | contact_type | last_name | first_name |
+----------------+------------------+---------------+--------------+-----------+------------+
|           1001 |             1018 |          1001 |         1004 | Jones     | John       |
|           1002 |             1018 |          1001 |         1004 | Jones     | Jane       |
+----------------+------------------+---------------+--------------+-----------+------------+
2 rows in set (0.03 sec)

  1. [1 point] Insert two rows into the ADDRESS_LAB table:

Instruction Details ↓

Some of the data values require that you look them up. You look them up in the COMMON_LOOKUP_LAB table. Then, you figure out (a) how to write a subquery to return the correct COMMON_LOOKUP_LAB_ID value and (b) how to embed the subquery in an INSERT statement.

Table Name: ADDRESS_LAB
Column Name Column Value #1 Column Value #2
ADDRESS_LAB_ID Sequence value Sequence value
ADDRESS_TYPE Home Home
CITY Draper Draper
STATE_PROVINCE Utah Utah
POSTAL_CODE 84020 84020
CREATED_BY System Administrator System Administrator
CREATION_DATE Today’s date Today’s date
LAST_UPDATED_BY System Administrator System Administrator
LAST_UPDATE_DATE Today’s date Today’s date

You should use the following formatting and query to verify completion of this step:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

SELECT   c.contact_lab_id
,        a.address_type
,        c.first_name
,        c.last_name
,        a.city
,        a.state_province
,        a.postal_code
FROM     contact_lab c INNER JOIN common_lookup_lab cl1
ON       c.contact_type = cl1.common_lookup_lab_id INNER JOIN address_lab a
ON       c.contact_lab_id = a.contact_lab_id INNER JOIN common_lookup_lab cl2
ON       a.address_type = cl2.common_lookup_lab_id
WHERE    cl1.common_lookup_context = 'CONTACT_LAB'
AND      cl1.common_lookup_type = 'CUSTOMER'
AND      cl2.common_lookup_context = 'MULTIPLE'
AND      cl2.common_lookup_type = 'HOME';

It should display the following results:

+----------------+--------------+------------+-----------+--------+----------------+-------------+
| contact_lab_id | address_type | first_name | last_name | city   | state_province | postal_code |
+----------------+--------------+------------+-----------+--------+----------------+-------------+
|           1001 |         1010 | John       | Jones     | Draper | Utah           | 84020       |
|           1002 |         1010 | Jane       | Jones     | Draper | Utah           | 84020       |
+----------------+--------------+------------+-----------+--------+----------------+-------------+
4 rows in set (0.27 sec)

  1. [1 point] Insert two rows into the STREET_ADDRESS_LAB table:

Instruction Details ↓

Some of the data values require that you look them up. You look them up in the COMMON_LOOKUP_LAB table. Then, you figure out (a) how to write a subquery to return the correct COMMON_LOOKUP_LAB_ID value and (b) how to embed the subquery in an INSERT statement.

Table Name: STREET_ADDRESS_LAB
Column Name Column Value #1 Column Value #2
STREET_ADDRESS_LAB_ID Sequence value Sequence value
ADDRESS_ID ADDRESS_LAB Foreign Key ADDRESS_LAB Foreign Key
STREET_ADDRESS 372 East 12300 South 1872 West 5400 South
CREATED_BY System Administrator System Administrator
CREATION_DATE Today’s date Today’s date
LAST_UPDATED_BY System Administrator System Administrator
LAST_UPDATE_DATE Today’s date Today’s date

You should use the following formatting and query to verify completion of this step:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

SELECT   c.contact_lab_id
,        a.address_lab_id
,        a.address_type
,        c.first_name
,        c.last_name
,        sa.street_address
,        a.city
,        a.state_province
,        a.postal_code
FROM     contact_lab c INNER JOIN common_lookup_lab cl1
ON       c.contact_type = cl1.common_lookup_lab_id INNER JOIN address_lab a
ON       c.contact_lab_id = a.contact_lab_id INNER JOIN street_address_lab sa
ON       a.address_lab_id = sa.address_lab_id INNER JOIN common_lookup_lab cl2
ON       a.address_type = cl2.common_lookup_lab_id
WHERE    cl1.common_lookup_context = 'CONTACT_LAB'
AND      cl1.common_lookup_type = 'CUSTOMER'
AND      cl2.common_lookup_context = 'MULTIPLE'
AND      cl2.common_lookup_type = 'HOME';

It should display the following results:

+----------------+----------------+--------------+------------+-----------+----------------------+--------+----------------+-------------+
| contact_lab_id | address_lab_id | address_type | first_name | last_name | street_address       | city   | state_province | postal_code |
+----------------+----------------+--------------+------------+-----------+----------------------+--------+----------------+-------------+
|           1001 |           1001 |         1010 | John       | Jones     | 372 East 12300 South | Draper | Utah           | 84020       |
|           1002 |           1002 |         1010 | Jane       | Jones     | 1872 West 5400 South | Draper | Utah           | 84020       |
+----------------+----------------+--------------+------------+-----------+----------------------+--------+----------------+-------------+
2 rows in set (0.00 sec)

  1. [1 point] Insert two rows into the TELEPHONE_LAB table:

Instruction Details ↓

Some of the data values require that you look them up. You look them up in the COMMON_LOOKUP_LAB table. Then, you figure out (a) how to write a subquery to return the correct COMMON_LOOKUP_LAB_ID value and (b) how to embed the subquery in an INSERT statement.

Table Name: TELEPHONE_LAB
Column Name Column Value #1 Column Value #2
TELEPHONE_LAB_ID Sequence value Sequence value
CONTACT_LAB_ID CONTACT_LAB Foreign Key CONTACT_LAB Foreign Key
ADDRESS_LAB_ID ADDRESS_LAB Foreign Key ADDRESS_LAB Foreign Key
TELEPHONE_TYPE Home Home
COUNTRY_CODE 001 001
AREA_CODE 801 801
TELEPHONE_NUMBER 435-7654 435-7655
CREATED_BY System Administrator System Administrator
CREATION_DATE Today’s date Today’s date
LAST_UPDATED_BY System Administrator System Administrator
LAST_UPDATE_DATE Today’s date Today’s date

You should use the following formatting and query to verify completion of this step:

1
2
3
4
5
6
7
8
9
10
11
12

SELECT   c.contact_lab_id
,        t.telephone_lab_id
,        t.telephone_type
,        c.first_name
,        c.last_name
,        t.country_code
,        t.area_code
,        t.telephone_number
FROM     contact_lab c INNER JOIN telephone_lab t
ON       c.contact_lab_id = t.contact_lab_id
WHERE    c.first_name IN ('John','Jane')
AND      c.last_name = 'Jones';

It should display the following results:

+----------------+------------------+----------------+------------+-----------+--------------+-----------+------------------+
| contact_lab_id | telephone_lab_id | telephone_type | first_name | last_name | country_code | area_code | telephone_number |
+----------------+------------------+----------------+------------+-----------+--------------+-----------+------------------+
|           1001 |             1001 |           1010 | John       | Jones     | 001          | 801       | 389-0687         |
|           1002 |             1002 |           1010 | Jane       | Jones     | 001          | 801       | 389-0688         |
+----------------+------------------+----------------+------------+-----------+--------------+-----------+------------------+
2 rows in set (0.02 sec)

  1. [1 point] Insert two rows into the RENTAL_LAB table:

Instruction Details ↓

Some of the data values require that you look them up. You look them up in the COMMON_LOOKUP_LAB table. Then, you figure out (a) how to write a subquery to return the correct COMMON_LOOKUP_LAB_ID value and (b) how to embed the subquery in an INSERT statement.

Table Name: RENTAL_LAB
Column Name Column Value #1 Column Value #2
RENTAL_LAB_ID Sequence value Sequence value
CUSTOMER_ID CONTACT_LAB Foreign Key CONTACT_LAB Foreign Key
CHECK_OUT_DATE January 2, 2015 January 3, 2015
RETURN_DATE January 6, 2015 January 5, 2015
CREATED_BY System Administrator System Administrator
CREATION_DATE Today’s date Today’s date
LAST_UPDATED_BY System Administrator System Administrator
LAST_UPDATE_DATE Today’s date Today’s date

You should use the following formatting and query to verify completion of this step:

1
2
3
4
5
6

SELECT   r.rental_lab_id
,        r.customer_id
,        r.check_out_date
,        r.return_date
FROM     rental_lab r 
WHERE    r.check_out_date IN ('2015-01-02','2015-01-03');

It should display the following results:

+---------------+-------------+----------------+-------------+
| rental_lab_id | customer_id | check_out_date | return_date |
+---------------+-------------+----------------+-------------+
|          1001 |        1001 | 2015-01-02     | 2015-01-06  |
|          1002 |        1002 | 2015-01-03     | 2015-01-05  |
+---------------+-------------+----------------+-------------+
2 rows in set (0.19 sec)

  1. [1 points] Insert two rows into the ITEM_LAB table:

Instruction Details ↓

Some of the data values require that you look them up. You look them up in the COMMON_LOOKUP_LAB table. Then, you figure out (a) how to write a subquery to return the correct COMMON_LOOKUP_LAB_ID value and (b) how to embed the subquery in an INSERT statement.

Table Name: ITEM_LAB
Column Name Column Value #1 Column Value #2
ITEM_LAB_ID Sequence value Sequence value
ITEM_BARCODE B00N1JQ2UO B00OY7YPGK
ITEM_TYPE DVD Wide Screen Blu-ray
ITEM_TITLE Guardians of the Galaxy The Maze Runner
ITEM_SUBTITLE
ITEM_RATING PG-13 PG-13
ITEM_RELEASE_DATE Dec 9, 2014 Dec 16, 2014
CREATED_BY System Administrator System Administrator
CREATION_DATE Today’s date Today’s date
LAST_UPDATED_BY System Administrator System Administrator
LAST_UPDATE_DATE Today’s date Today’s date

You should use the following formatting and query to verify completion of this step:

1
2
3
4
5
6

SELECT   i.item_lab_id
,        i.item_title
,        i.item_rating
,        i.item_release_date
FROM     item_lab i 
WHERE    i.item_release_date IN ('2014-12-09','2014-12-16');

It should display the following results:

+-------------+-------------------------+-------------+-------------------+
| item_lab_id | item_title              | item_rating | item_release_date |
+-------------+-------------------------+-------------+-------------------+
|        1001 | Guardians of the Galaxy | PG-13       | 2014-12-09        |
|        1002 | The Maze Runner         | PG-13       | 2014-12-16        |
+-------------+-------------------------+-------------+-------------------+
2 rows in set (0.02 sec)

  1. [1 point] Insert two rows into the RENTAL_ITEM_LAB table:

Instruction Details ↓

Some of the data values require that you look them up. You look them up in the COMMON_LOOKUP_LAB table. Then, you figure out (a) how to write a subquery to return the correct COMMON_LOOKUP_LAB_ID value and (b) how to embed the subquery in an INSERT statement.

Table Name: RENTAL_ITEM_LAB
Column Name Column Value #1 Column Value #2
RENTAL_ITEM_LAB_ID Sequence value Sequence value
RENTAL_LAB_ID RENTAL_LAB Foreign Key RENTAL_LAB Foreign Key
ITEM_LAB_ID ITEM_LAB Foreign Key ITEM_LAB Foreign Key
CREATED_BY System Administrator System Administrator
CREATION_DATE Today’s date Today’s date
LAST_UPDATED_BY System Administrator System Administrator
LAST_UPDATE_DATE Today’s date Today’s date

You should use the following formatting and query to verify completion of this step:

1
2
3
4
5
6
7
8
9
10
11
12
13

SELECT   ri.rental_item_lab_id
,        ri.rental_lab_id
,        ri.item_lab_id
FROM     rental_item_lab ri INNER JOIN rental_lab r
ON       r.rental_lab_id = ri.rental_lab_id INNER JOIN item_lab i
ON       i.item_lab_id = ri.item_lab_id
WHERE    r.rental_lab_id IN (SELECT   r.rental_lab_id
                             FROM     rental_lab r 
                             WHERE    r.check_out_date IN ('2015-01-02','2015-01-03'))
AND      i.item_lab_id IN (SELECT   i.item_lab_id
                           FROM     item_lab i
                           WHERE    item_title IN ('Guardians of the Galaxy','The Maze Runner')
                           AND      item_release_date IN ('2014-12-09','2014-12-16'));

It should display the following results:

+--------------------+---------------+-------------+
| rental_item_lab_id | rental_lab_id | item_lab_id |
+--------------------+---------------+-------------+
|               1001 |          1001 |        1001 |
|               1002 |          1002 |        1002 |
+--------------------+---------------+-------------+
2 rows in set (0.00 sec)

  1. [10 points] You insert rows into the a series of tables differently than you insert rows individually. For example, you insert them using the auto incrementing sequence and the last_insert_id() function. The last_insert_id() function returns the last auto incrementing sequence value from any table. The value written to the last_insert_id() function may come from any table. This step requires you to capture the last auto incrementing value and assign it to a local variable, which can be done with the following syntax:
SET @lv_table_name_id := last_insert_id();

Instruction Details ↓

This step requires you to enter data values into the MEMBER_LAB, CONTACT_LAB, ADDRESS_LAB, STREET_ADDRESS_LAB, TELEPHONE_LAB, RENTAL_LAB, and RENTAL_ITEM_LAB tables. As mentioned earlier, you need to insert data into these tables in a specific order. The order depends on the degree that tables are unconstrained or constrained by their foreign key to primary key dependencies.

The video store model for the course has five key components. They are these:

  • An Access Control List (ACL), which lets tracks individuals who enter and update data.
  • A common lookup repository, which hosts a series of small tables to support various end-user forms.
  • Customer information, which supports member accounts, customers, and their addresses and telephone numbers.
  • An inventory system for Blu-ray and DVD items.
  • A order management component that supports rental agreements with multiple rental items.

You’ve already seeded a value in the SYSTEM_USER_LAB table (or the ACL component) and COMMON_LOOKUP_LAB table (or common lookup repository). This step skips those tables. In this step you mimic setting up a new customer and renting them their first set of videos.

You create a customer by determining the least to most dependent tables. To simplify this step the order is:

  1. You insert into the MEMBER_LAB table because it only has dependencies on the SYSTEM_USER_LAB and COMMON_LOOKUP_LAB tables, which are outside of the customer information process.
  2. You insert into the MEMBER_LAB table to establish an account that support multiple parties, like family members.
  3. You insert customer data into the CONTACT_LAB, ADDRESS_LAB table, STREET_ADDRESS_LAB table, and TELEPHONE_LAB tables respectively for all family members because they may have different addresses and telephone numbers.
  4. You insert a row in the RENTAL_LAB table for a rental agreement and one row into the RENTAL_ITEM_LAB table for each item attached to the rental.

You must write the INSERT statements to manage sequence values for the surrogate primary and foreign key column values. As explained earlier, you do that by capturing the last sequence value with the last_insert_id() function.

You will insert an account for Peter Quill and Yondu Udonta as a family. Yondu has kidnapped Peter and treats him as an adopted child. Yondu is setting up the account based on the following information because Peter likes classic 1970 and 1980 Terran films:

Customer Entry
Column Name Column Value #1 Column Value #2
MEMBER_TYPE Group Membership  
ACCOUNT_NUMBER X21-777-01  
CREDIT_CARD_NUMBER 9876-5432-1234-5678  
CREDIT_CARD_TYPE Discover Card  
CONTACT_TYPE Group Group
LAST_NAME Udonta Quill
FIRST_NAME Yondu Peter
ADDRESS_TYPE Home Home
STREET_ADDRESS 12129 South State Street 12129 South State Street
CITY Draper Draper
STATE_PROVINCE Utah Utah
POSTAL_CODE 84020 84020
TELEPHONE_TYPE Home Home
COUNTRY_CODE 001 001
AREA_CODE 801 801
TELEPHONE_CODE 342-8940 342-8941

The following query should return the following four rows. The first two rows come from the earlier INSERT statements, and the latter two from the sequencing of INSERT statements:

SELECT   m.member_type
,        m.account_number
,        m.credit_card_number
,        m.credit_card_type
,        c.first_name
,        c.last_name
,        sa.street_address
,        a.city
,        a.state_province
,        a.postal_code
,        t.country_code||' '||t.area_code||' '||t.telephone_number
FROM     member_lab m INNER JOIN contact_lab c
ON       m.member_lab_id = c.member_lab_id INNER JOIN address_lab a
ON       c.contact_lab_id = a.contact_lab_id INNER JOIN street_address_lab sa
ON       a.address_lab_id = sa.address_lab_id INNER JOIN telephone_lab t
ON       c.contact_lab_id = t.contact_lab_id
AND      a.address_lab_id = t.address_lab_id;

These are the rows that should be returned:

+-------------+----------------+---------------------+------------------+------------+-----------+--------------------------+--------+----------------+-------------+--------------------+
| member_type | account_number | credit_card_number  | credit_card_type | first_name | last_name | street_address           | city   | state_province | postal_code | telephone_number   |
+-------------+----------------+---------------------+------------------+------------+-----------+--------------------------+--------+----------------+-------------+--------------------+
|        1005 | X15-500-01     | 9876-5432-1234-5678 |             1018 | John       | Jones     | 372 East 12300 South     | Draper | Utah           | 84020       | 001 (801) 389-0687 |
|        1005 | X15-500-01     | 9876-5432-1234-5678 |             1018 | Jane       | Jones     | 1872 West 5400 South     | Draper | Utah           | 84020       | 001 (801) 389-0688 |
|        1006 | X21-777-01     | 8765-1234-9988-8899 |             1018 | Yondu      | Udonta    | 12129 South State Street | Draper | Utah           | 84020       | 001 (801) 342-8940 |
|        1006 | X21-777-01     | 8765-1234-9988-8899 |             1018 | Peter      | Quill     | 12129 South State Street | Draper | Utah           | 84020       | 001 (801) 342-8940 |
+-------------+----------------+---------------------+------------------+------------+-----------+--------------------------+--------+----------------+-------------+--------------------+
4 rows in set (0.00 sec)

After entering a customer, you can now enter a rental with two rental items with the following data:

Rental Entry
Column Name Column Value #1 Column Value #2
CHECK_OUT_DATE January 8, 2015 January 9, 2015
RETURN_DATE January 12, 2015 January 11, 2015
ITEM_ID Guardians of the Galaxy The Maze Runners

The following query should return the following four rows:

SELECT   r.check_out_date
,        r.return_date
,        ri.rental_item_lab_id
,        i.item_title
FROM     rental_lab r INNER JOIN rental_item_lab ri
ON       r.rental_lab_id = ri.rental_lab_id INNER JOIN item_lab i
ON       ri.item_lab_id = i.item_lab_id
WHERE    r.check_out_date IN ('2015-01-08','2015-01-09');

These are the rows that should be returned:

+----------------+-------------+--------------------+-------------------------+
| check_out_date | return_date | rental_item_lab_id | item_title              |
+----------------+-------------+--------------------+-------------------------+
| 2015-01-08     | 2015-01-12  |               1003 | Guardians of the Galaxy |
| 2015-01-09     | 2015-01-11  |               1004 | The Maze Runner         |
+----------------+-------------+--------------------+-------------------------+
2 rows in set (0.00 sec)

Written by michaelmclaughlin

December 2nd, 2014 at 2:39 am

Posted in

28 Responses to 'Lab #4 : MySQL Inserts'

Subscribe to comments with RSS or TrackBack to 'Lab #4 : MySQL Inserts'.

  1. in step 8 the query says customer_id but I was told it should say customer_lab_id, and the output has more fields than the query asks for.

    Samuel Heindel

    29 Jan 15 at 9:12 pm

  2. step 9 mysql store didnt contain BLU-Ray so I couldnt use it in the Item Lab table

    Samuel Heindel

    29 Jan 15 at 10:12 pm

  3. SYSTEM_USER_LAB table (step 1) the first column should be SYSTEM_USER_LAB_ID instead of SYSTEM_USER_ID.

    ADDRESS_LAB table (step 5) check output is incorrect it is just a copy from step 4.

    TELEPHONE_CODE column name in the TELEPHONE_LAB (STEP 7) should be TELEPHONE_NUMBER.

    All throughout you need to change the last name of Jones to Smith.

    Tyler Nelson

    30 Jan 15 at 12:48 am

  4. RENTAL_LAB table (step 8) check output is incorrect it is just a copy from step 7.

    Tyler Nelson

    30 Jan 15 at 1:05 am

  5. RENTAL_ITEM_LAB table (step 10) needs to have the column RENTAL_ID changed to RENTAL_LAB_ID.

    Tyler Nelson

    30 Jan 15 at 1:19 am

  6. RENTAL_ITEM_LAB table (step 10) needs to have the column ITEM_ID changed to ITEM_LAB_ID.

    Tyler Nelson

    30 Jan 15 at 1:20 am

  7. Step 8 Validation is wrong.

    SELECT   r.rental_lab_id
,        r.customer_id
,        r.check_out_date
,        r.return_date
FROM     rental_lab r 
WHERE    r.check_out_date IN ('2015-01-02','2015-01-03');

    Output should be different

    Jeremy

    30 Jan 15 at 5:10 pm

  8. John and Jane Smith in step 4 needs to be changed to John and Jane Jones.

    Jeremy

    30 Jan 15 at 5:11 pm

  9. Jeremy, It’s fixed. It should’ve been Jones.

    michaelmclaughlin

    30 Jan 15 at 5:18 pm

  10. In step 5 the display of the verification query has items in it like credit card type that is not in the verification query.

    James Stakebake

    31 Jan 15 at 1:05 pm

  11. In step 8 the display of the verification query has items in it that is not in the verification query.

    James Stakebake

    31 Jan 15 at 1:22 pm

  12. in step 8 verification query the r.customer_id needs to be changed to r.customer_lab_id

    James Stakebake

    31 Jan 15 at 1:32 pm

  13. verification output display for step 5 is a repeat from step 4. Should display address results instead of credit card.

    Benjamin

    31 Jan 15 at 5:06 pm

  14. Concerning step 1: In Lab 2 you have us change the column system_user_id in the system_user table to system_user_lab_id. Here in Lab 4 your having us insert into the system_user_lab table but the column is still named system_user_id. It should reflect the changes we made in lab 2 and be called system_user_lab_id here as well.

    Jonathan

    5 Feb 15 at 1:37 am

  15. Step 4 should have them be putting in CUSTOMER not group for the contact type.

    Brett

    14 Feb 15 at 4:36 pm

  16. Brett, Great catch. It’s fixed. Thanks.

    michaelmclaughlin

    7 Mar 15 at 2:13 am

  17. Jeremy, I fixed it with the UTC_DATE() function call.

    michaelmclaughlin

    7 Mar 15 at 3:18 am

  18. James, The r.customer_id is correct because it an example of a foreign key column name that doesn’t conform or mirror the primary key column name. Hence, the change to include _lab is unnecessary and incorrect.

    michaelmclaughlin

    8 Mar 15 at 2:39 am

  19. Samuel, The r.customer_id is correct because it an example of a foreign key column name that doesn’t conform or mirror the primary key column name. Hence, the change to include _lab is unnecessary and incorrect.

    michaelmclaughlin

    8 Mar 15 at 2:42 am

  20. Samuel, The Lab #2 Instructions say you should insert it. Does your Lab #2 script insert it?

    Table Name: COMMON_LOOKUP_LAB
    Context Type Meaning
    SYSTEM_USER_LAB SYSTEM_ADMIN System Administrator
    SYSTEM_USER_LAB DBA Database Administrator
    CONTACT_LAB EMPLOYEE Employee
    CONTACT_LAB CUSTOMER Customer
    MEMBER_LAB INDIVIDUAL Individual Membership
    MEMBER_LAB GROUP Group Membership
    MEMBER_LAB DISCOVER_CARD Discover Card
    MEMBER_LAB MASTER_CARD Master Card
    MEMBER_LAB VISA_CARD VISA Card
    MULTIPLE HOME Home
    MULTIPLE WORK Work
    ITEM_LAB DVD_FULL_SCREEN DVD: Full Screen
    ITEM_LAB DVD_WIDE_SCREEN DVD: Wide Screen
    ITEM_LAB NINTENDO_GAMECUBE Nintendo Gamecube
    ITEM_LAB PLAYSTATION2 PlayStation2
    ITEM_LAB XBOX XBox
    ITEM_LAB BLU-RAY Blu-ray

    michaelmclaughlin

    8 Mar 15 at 2:57 am

  21. Jonathan, Great catch! It wasn’t updated to system_user_lab_id and now it is.

    michaelmclaughlin

    8 Mar 15 at 3:04 am

  22. James, You are correct. I’ve fixed the verification output for Step #8. Thanks.

    michaelmclaughlin

    8 Mar 15 at 9:56 pm

  23. James, You are correct. I’ve fixed the verification output for Step #5. Thanks.

    michaelmclaughlin

    8 Mar 15 at 10:00 pm

  24. Tyler, Good catch on Step #10. I’ve renamed the ITEM_ID column to ITEM_LAB_ID.

    michaelmclaughlin

    8 Mar 15 at 10:19 pm

  25. Tyler, Good catch on Step #10. I’ve renamed the RENTAL_ID column to RENTAL_LAB_ID.

    michaelmclaughlin

    8 Mar 15 at 10:26 pm

  26. Benjamin, The output display for Step #5 has been fixed.

    michaelmclaughlin

    8 Mar 15 at 10:34 pm

  27. Tyler, Your observation on Step #8 output appears to have been fixed due to an earlier comment. Thanks though for finding the error.

    michaelmclaughlin

    8 Mar 15 at 10:36 pm

  28. Tyler, Great catches! The observations for Step #1 and Step #5 have been fixed earlier. However, the TELEPHONE_CODE did need to change to TELEPHONE_NUMBER; it’s now fixed. Everything has been changed from Smith to Jones.

    michaelmclaughlin

    8 Mar 15 at 11:17 pm

Leave a Reply