Project 3 - 1 Create Tables Using Existing Table Data

Project 3 “1 Create Tables Using Existing Table Data

Now that you know the different ways of creating tables and adding data to new or existing tables, you will make practical use of these techniques. In this project, you will create a copy of an entire existing table, create a new table with columns from an existing table, create a temporary table from an existing table, insert data from an existing table into a newly created table, and finally, load data from a text file into an existing table.

Step by Step

Open a command-line window and log in to MySQL, using the \mysql\bin\mysql “u root “p command (Windows) or the mysql “p command (Linux), and then enter your password when prompted.
Type USE duckwear; and press the ENTER key.
Type in the following command to create a copy of the duck_cust table:
```
 CREATE TABLE copy_duck_cust SELECT * FROM duck_cust; 
```
Type in the following command to verify the creation of the copy of the duck_cust table, including its data:
```
 SELECT * FROM copy_duck_cust; 
```
Your table should be identical to the duck_cust table you created in Module 2.

Enter the commands to describe both the existing duck_cust table and the copy_duck_cust table and compare them:

 DESC duck_cust;

 DESC copy_duck_cust;

Note	Remember that the only discrepancy between the two tables, other than their different names , is that the duck_cust table has the cust_num field set as PRIMARY and auto-incremented. This is because the index (or key) did not transfer to the copy of the table.

Create a table called cust_names using the table definitions and data from the duck_cust table. Type in the following command:
```
 CREATE TABLE cust_names SELECT cust_first, cust_last  FROM duck_cust; 
```
Display the new table s contents using the following command:
```
 SELECT * FROM cust_names; 
```

Create a similar table, but include the customer number field. You want to preserve the auto-incrementing key, so create the table, called cust_num_names first, and then insert data into it from the duck_cust table.

 CREATE TABLE cust_num_names (names_num MEDIUMINT(9) NOT NULL AUTO_INCREMENT, names_first CHAR(15) NOT NULL, names_last CHAR(20) NOT NULL, PRIMARY KEY (names_num))TYPE=MyISAM; INSERT INTO cust_num_names SELECT cust_num, cust_first, cust_last FROM duck_cust;

View both the table contents and description by using the following commands:
```
 SELECT * FROM cust_num_names; DESC cust_num_names; 
```
Compare the cust_num column description in the output from the DESC copy_duck_ cust; command in step 5 with the names_num column description here. You ll see that the auto-incrementing primary key did not duplicate in the copy process used to create the copy_duck_cust table. But since you specified it when making the names_num column, the key is set, so you do not need to re-create the index.
For marketing reasons, the sales department of DuckWear needs to know how many customers reside in a particular state. Use the following commands to make a temporary table to provide this data in an easily readable format:
```
 CREATE TEMPORARY TABLE tempKS_list SELECT cust_num, cust_last  FROM duck_cust WHERE cust_state LIKE "KS"; SELECT * FROM tempKS_list; 
```
The tempKS_list table has two customers and lists the required information: their customer numbers and last names.
Type in the SHOW TABLES; command and press the ENTER key. The temporary table tempKS_list does not appear in the list of tables.
Type in the following command to demonstrate that the temporary table still exists:
```
 SELECT cust_num FROM tempKS_list; 
```
Sever the current connection with MySQL by typing the EXIT command, and then reenter the MySQL Client using the same command you used in step 1.
Enter the SELECT command from step 12 again. As shown by the error message, the temporary table tempKS_list no longer exists in the duckwear database.

Create a text (.txt) file with the following information and format, using whatever word processor you are familiar with.

 NULL ,Wolf ,Davidson NULL ,Raccoon ,Danson NULL ,Bob ,Filled NULL ,Monte ,Billiards NULL ,Jet ,Beauvais NULL ,Kevin ,Seta

Save the file as DataFile.txt to a location on your hard drive that you can remember. In the example, the path will be \mysql\data\duckwear\DataFile.txt , but you could as easily save it to your normal file for documents.
Enter the following commands to use the DataFile.txt file to insert data into the cust_num_ names table, replacing the /mysql/data/ portion of the path in the first command with the correct path to your DataFile.txt file. Also, if you are using a Linux system, replace the ˜\r\n in the LINES TERMINATED BY syntax command with just ˜\n .
```
 LOAD DATA INFILE 
```