ACCENTURE BATCH 2 LABORATORY

MySQL Alter Table - Exercises, Practice,

Solution
Page | 1 1. Write a SQL statement to rename the table countries to country_new.

2. Write a SQL statement to add a column region_id to the table locations.

3. Write a SQL statement to add a columns ID as the first column of the table
locations.

4. Write a SQL statement to add a column region_id after state_province to the

table locations.

5. Write a SQL statement change the data type of the column country_id to
integer in the table locations.

6. Write a SQL statement to drop the column city from the table locations.

7. Write a SQL statement to change the name of the column state_province to

state, keeping the data type and size same.

8. Write a SQL statement to add a primary key for the columns location_id in the
locations table.

Here is the sample table employees.

Sample table: employees

9. Write a SQL statement to add a primary key for a combination of columns

location_id and country_id.

10. Write a SQL statement to drop the existing primary from the table locations
on a combination of columns location_id and country_id.

11. Write a SQL statement to add a foreign key on job_id column of job_history
table referencing to the primary key job_id of jobs table.
 ACCENTURE BATCH 2 LABORATORY

12. Write a SQL statement to add a foreign key constraint named fk_job_id on
job_id column of job_history table referencing to the primary key job_id of jobs
table.

Page | 2 13. Write a SQL statement to drop the existing foreign key fk_job_id from
job_history table on job_id column which is referencing to the job_id of jobs table.

14. Write a SQL statement to add an index named indx_job_id on job_id column
in the table job_history.

15. Write a SQL statement to drop the index indx_job_id from job_history table.
 ACCENTURE BATCH 2 LABORATORY

SOLUTION

MySQL Alter Table Statement: Exercise-1 with Solution

Page | 3
Write a SQL statement to rename the table countries to country_new.

Here is the list of tables.

+---------------+
| Tables_in_hrr |
+---------------+
| countries |
| departments |
| dup_countries |
| employees |
| jobs |
+---------------+
Code:
ALTER TABLE countries RENAME country_new;

Copy

Let execute the above code in MySQL 5.6 command prompt

Now, after execute the command see the list of tables.

+---------------+
| Tables_in_hrr |
+---------------+
| country_new |
| departments |
| dup_countries |
| employees |
| jobs |
+---------------+
 ACCENTURE BATCH 2 LABORATORY

MySQL Alter Table Statement: Exercise-2 with Solution

Write a SQL statement to add a column region_id to the table locations.

Page | 4
Here is the structure of the table locations.
mysql> SHOW COLUMNS FROM locations;
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| LOCATION_ID | decimal(4,0) | YES | | NULL | |
| STREET_ADDRESS | varchar(40) | YES | | NULL | |
| POSTAL_CODE | varchar(12) | YES | | NULL | |
| CITY | varchar(30) | YES | | NULL | |
| STATE_PROVINCE | varchar(25) | YES | | NULL | |
| COUNTRY_ID | varchar(2) | YES | | NULL | |
+----------------+--------------+------+-----+---------+-------+
Code:
ALTER TABLE locations

ADD region_id INT;

Copy

Let execute the above code in MySQL 5.6 command prompt

See the structure of the table after alteration.

mysql> SHOW COLUMNS FROM locations;
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| LOCATION_ID | decimal(4,0) | YES | | NULL | |
| STREET_ADDRESS | varchar(40) | YES | | NULL | |
| POSTAL_CODE | varchar(12) | YES | | NULL | |
| CITY | varchar(30) | YES | | NULL | |
| STATE_PROVINCE | varchar(25) | YES | | NULL | |
| COUNTRY_ID | varchar(2) | YES | | NULL | |
| region_id | int(11) | YES | | NULL | |
+----------------+--------------+------+-----+---------+-------+
 ACCENTURE BATCH 2 LABORATORY

MySQL Alter Table Statement: Exercise-3 with Solution

Write a SQL statement to add a columns ID as the first column of the table
Page | 5 locations.

Here is the structure of the table locations.

mysql> SHOW COLUMNS FROM locations;
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| LOCATION_ID | decimal(4,0) | YES | | NULL | |
| STREET_ADDRESS | varchar(40) | YES | | NULL | |
| POSTAL_CODE | varchar(12) | YES | | NULL | |
| CITY | varchar(30) | YES | | NULL | |
| STATE_PROVINCE | varchar(25) | YES | | NULL | |
| COUNTRY_ID | varchar(2) | YES | | NULL | |
+----------------+--------------+------+-----+---------+-------+
Code:
ALTER TABLE locations

ADD ID INT FIRST;

Copy

Let execute the above code in MySQL 5.6 command prompt

See the structure of the table after alteration.

mysql> SHOW COLUMNS FROM locations;
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| ID | int(11) | YES | | NULL | |
| LOCATION_ID | decimal(4,0) | YES | | NULL | |
| STREET_ADDRESS | varchar(40) | YES | | NULL | |
| POSTAL_CODE | varchar(12) | YES | | NULL | |
| CITY | varchar(30) | YES | | NULL | |
| STATE_PROVINCE | varchar(25) | YES | | NULL | |
| COUNTRY_ID | varchar(2) | YES | | NULL | |
+----------------+--------------+------+-----+---------+-------+
 ACCENTURE BATCH 2 LABORATORY

MySQL Alter Table Statement: Exercise-4 with Solution

Write a SQL statement to add a column region_id after state_province to the

Page | 6 table locations.

Here is the structure of the table locations.

mysql> SHOW COLUMNS FROM locations;
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| LOCATION_ID | decimal(4,0) | YES | | NULL | |
| STREET_ADDRESS | varchar(40) | YES | | NULL | |
| POSTAL_CODE | varchar(12) | YES | | NULL | |
| CITY | varchar(30) | YES | | NULL | |
| STATE_PROVINCE | varchar(25) | YES | | NULL | |
| COUNTRY_ID | varchar(2) | YES | | NULL | |
+----------------+--------------+------+-----+---------+-------+
Code:
ALTER TABLE locations

ADD region_id INT

AFTER state_province;

Copy

Let execute the above code in MySQL 5.6 command prompt

Here is structure of the table locations after alteration.

mysql> SHOW COLUMNS FROM locations;
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| LOCATION_ID | decimal(4,0) | YES | | NULL | |
| STREET_ADDRESS | varchar(40) | YES | | NULL | |
| POSTAL_CODE | varchar(12) | YES | | NULL | |
| CITY | varchar(30) | YES | | NULL | |
| STATE_PROVINCE | varchar(25) | YES | | NULL | |
| region_id | int(11) | YES | | NULL | |
| COUNTRY_ID | varchar(2) | YES | | NULL | |
+----------------+--------------+------+-----+---------+-------+
 ACCENTURE BATCH 2 LABORATORY

MySQL Alter Table Statement: Exercise-5 with Solution

Write a SQL statement change the data type of the column country_id to integer
Page | 7 in the table locations.

Here is the structure of the table locations.

mysql> SHOW COLUMNS FROM locations;
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| LOCATION_ID | decimal(4,0) | YES | | NULL | |
| STREET_ADDRESS | varchar(40) | YES | | NULL | |
| POSTAL_CODE | varchar(12) | YES | | NULL | |
| CITY | varchar(30) | YES | | NULL | |
| STATE_PROVINCE | varchar(25) | YES | | NULL | |
| COUNTRY_ID | varchar(2) | YES | | NULL | |
+----------------+--------------+------+-----+---------+-------+
Code:
ALTER TABLE locations

MODIFY country_id INT;

Copy

Let execute the above code in MySQL 5.6 command prompt

Now see the structure of the table locations after alteration.

mysql> SHOW COLUMNS FROM locations;
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| LOCATION_ID | decimal(4,0) | YES | | NULL | |
| STREET_ADDRESS | varchar(40) | YES | | NULL | |
| POSTAL_CODE | varchar(12) | YES | | NULL | |
| CITY | varchar(30) | YES | | NULL | |
| STATE_PROVINCE | varchar(25) | YES | | NULL | |
| country_id | int(11) | YES | | NULL | |
+----------------+--------------+------+-----+---------+-------+
 ACCENTURE BATCH 2 LABORATORY

MySQL Alter Table Statement: Exercise-6 with Solution

Write a SQL statement to drop the column city from the table locations.
Page | 8
Here is the structure of the table locations.
mysql> SHOW COLUMNS FROM locations;
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| LOCATION_ID | decimal(4,0) | YES | | NULL | |
| STREET_ADDRESS | varchar(40) | YES | | NULL | |
| POSTAL_CODE | varchar(12) | YES | | NULL | |
| CITY | varchar(30) | YES | | NULL | |
| STATE_PROVINCE | varchar(25) | YES | | NULL | |
| COUNTRY_ID | varchar(2) | YES | | NULL | |
+----------------+--------------+------+-----+---------+-------+
Code:
ALTER TABLE locations

DROP city;

Copy

Let execute the above code in MySQL 5.6 command prompt

Now see the structure of the table locations after alteration.

mysql> SHOW COLUMNS FROM locations;
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| LOCATION_ID | decimal(4,0) | YES | | NULL | |
| STREET_ADDRESS | varchar(40) | YES | | NULL | |
| POSTAL_CODE | varchar(12) | YES | | NULL | |
| STATE_PROVINCE | varchar(25) | YES | | NULL | |
| COUNTRY_ID | varchar(2) | YES | | NULL | |
+----------------+--------------+------+-----+---------+-------+
 ACCENTURE BATCH 2 LABORATORY

MySQL Alter Table Statement: Exercise-7 with Solution

Write a SQL statement to change the name of the column state_province to

Page | 9 state, keeping the data type and size same.

Here is the structure of the table locations.

mysql> SHOW COLUMNS FROM locations;
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| LOCATION_ID | decimal(4,0) | YES | | NULL | |
| STREET_ADDRESS | varchar(40) | YES | | NULL | |
| POSTAL_CODE | varchar(12) | YES | | NULL | |
| CITY | varchar(30) | YES | | NULL | |
| STATE_PROVINCE | varchar(25) | YES | | NULL | |
| COUNTRY_ID | varchar(2) | YES | | NULL | |
+----------------+--------------+------+-----+---------+-------+
Code:
ALTER TABLE locations

DROP state_province,

ADD state varchar(25)

AFTER city;

Copy

Let execute the above code in MySQL 5.6 command prompt

In that case, if there are no data in the table, the old column will be removed and
new column will be create, no problem at all, but if data in the table you can use
the following statement :
ALTER TABLE locations

CHANGE state_province state varchar(25);

Copy

Now see the structure of the table locations after alteration.

mysql> SHOW COLUMNS FROM locations;
 ACCENTURE BATCH 2 LABORATORY

+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| LOCATION_ID | decimal(4,0) | YES | | NULL | |
| STREET_ADDRESS | varchar(40) | YES | | NULL | |
Page | 10 | POSTAL_CODE | varchar(12) | YES | | NULL | |
| CITY | varchar(30) | YES | | NULL | |
| state | varchar(25) | YES | | NULL | |
| COUNTRY_ID | varchar(2) | YES | | NULL | |
+----------------+--------------+------+-----+---------+-------+
 ACCENTURE BATCH 2 LABORATORY

MySQL Alter Table Statement: Exercise-8 with Solution

Write a SQL statement to add a primary key for the columns location_id in the
Page | 11 locations table.

Here is the sample table employees.

Sample table: locations

location_id street_address postal_code city state_province
country_id
----------- -------------------- ----------- ---------- -------------- -
---------
1000 1297 Via Cola di Rie 989 Roma
IT
1100 93091 Calle della Te 10934 Venice
IT
1200 2017 Shinjuku-ku 1689 Tokyo Tokyo Prefectu
JP
1300 9450 Kamiya-cho 6823 Hiroshima
JP
1400 2014 Jabberwocky Rd 26192 Southlake Texas
US
1500 2011 Interiors Blvd 99236 South San California
US
1600 2007 Zagora St 50090 South Brun New Jersey
US
1700 2004 Charade Rd 98199 Seattle Washington
US
1800 147 Spadina Ave M5V 2L7 Toronto Ontario
CA

Here is the structure of the table locations.

mysql> SHOW COLUMNS FROM locations;
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| LOCATION_ID | decimal(4,0) | YES | | NULL | |
| STREET_ADDRESS | varchar(40) | YES | | NULL | |
| POSTAL_CODE | varchar(12) | YES | | NULL | |
| CITY | varchar(30) | YES | | NULL | |
| STATE_PROVINCE | varchar(25) | YES | | NULL | |
| COUNTRY_ID | varchar(2) | YES | | NULL | |
+----------------+--------------+------+-----+---------+-------+
Code:
 ACCENTURE BATCH 2 LABORATORY

ALTER TABLE locations

ADD PRIMARY KEY(location_id);

Copy
Page | 12
Let execute the above code in MySQL 5.6 command prompt

Now see the structure of the table locations after alteration.

mysql> SHOW COLUMNS FROM locations;
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| LOCATION_ID | decimal(4,0) | NO | PRI | 0 | |
| STREET_ADDRESS | varchar(40) | YES | | NULL | |
| POSTAL_CODE | varchar(12) | YES | | NULL | |
| CITY | varchar(30) | YES | | NULL | |
| STATE_PROVINCE | varchar(25) | YES | | NULL | |
| COUNTRY_ID | varchar(2) | YES | | NULL | |
+----------------+--------------+------+-----+---------+-------+
 ACCENTURE BATCH 2 LABORATORY

MySQL Alter Table Statement: Exercise-9 with Solution

Write a SQL statement to add a primary key for a combination of columns

Page | 13 location_id and country_id.

Here is the structure of the table locations.

mysql> SHOW COLUMNS FROM locations;
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| LOCATION_ID | decimal(4,0) | YES | | NULL | |
| STREET_ADDRESS | varchar(40) | YES | | NULL | |
| POSTAL_CODE | varchar(12) | YES | | NULL | |
| CITY | varchar(30) | YES | | NULL | |
| STATE_PROVINCE | varchar(25) | YES | | NULL | |
| COUNTRY_ID | varchar(2) | YES | | NULL | |
+----------------+--------------+------+-----+---------+-------+
Code:
ALTER TABLE locations

ADD PRIMARY KEY(location_id,country_id);

Copy

Let execute the above code in MySQL 5.6 command prompt

Now see the structure of the table locations after alteration.

mysql> SHOW COLUMNS FROM locations;
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| LOCATION_ID | decimal(4,0) | NO | PRI | 0 | |
| STREET_ADDRESS | varchar(40) | YES | | NULL | |
| POSTAL_CODE | varchar(12) | YES | | NULL | |
| CITY | varchar(30) | YES | | NULL | |
| STATE_PROVINCE | varchar(25) | YES | | NULL | |
| COUNTRY_ID | varchar(2) | NO | PRI | | |
+----------------+--------------+------+-----+---------+-------+
Here is the index file which have been created with creation of primary key.
 ACCENTURE BATCH 2 LABORATORY

+-----------+------------+----------+--------------+------------
-+-----------+-------------+----------+--------+------+---------
---+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name
| Collation | Cardinality | Sub_part | Packed | Null |
Page | 14 Index_type | Comment |
+-----------+------------+----------+--------------+------------
-+-----------+-------------+----------+--------+------+---------
---+---------+
| locations | 0 | PRIMARY | 1 | LOCATION_ID
| A | NULL | NULL | NULL | | BTREE
| |
| locations | 0 | PRIMARY | 2 | COUNTRY_ID
| A | 0 | NULL | NULL | | BTREE
| |
+-----------+------------+----------+--------------+------------
-+-----------+-------------+----------+--------+------+---------
---+---------+
 ACCENTURE BATCH 2 LABORATORY

MySQL Alter Table Statement: Exercise-10 with Solution

Write a SQL statement to drop the existing primary from the table locations on a
Page | 15 combination of columns location_id and country_id.

Here is the structure of the table locations.

mysql> show columns from locations;
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| LOCATION_ID | decimal(4,0) | NO | PRI | NULL | |
| STREET_ADDRESS | varchar(40) | YES | | NULL | |
| POSTAL_CODE | varchar(12) | YES | | NULL | |
| CITY | varchar(30) | YES | | NULL | |
| STATE_PROVINCE | varchar(25) | YES | | NULL | |
| COUNTRY_ID | varchar(2) | NO | PRI | NULL | |
+----------------+--------------+------+-----+---------+-------+
Code:
ALTER TABLE locations DROP PRIMARY KEY;

Copy

Let execute the above code in MySQL 5.6 command prompt

Now see the structure of the table locations after alteration.

mysql> SHOW COLUMNS FROM locations;
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| LOCATION_ID | decimal(4,0) | NO | | NULL | |
| STREET_ADDRESS | varchar(40) | YES | | NULL | |
| POSTAL_CODE | varchar(12) | YES | | NULL | |
| CITY | varchar(30) | YES | | NULL | |
| STATE_PROVINCE | varchar(25) | YES | | NULL | |
| COUNTRY_ID | varchar(2) | NO | | NULL | |
+----------------+--------------+------+-----+---------+-------+
 ACCENTURE BATCH 2 LABORATORY

MySQL Alter Table Statement: Exercise-11 with Solution

Write a SQL statement to add a foreign key on job_id column of job_history table
Page | 16 referencing to the primary key job_id of jobs table.

Here is the structure of the table jobs and job_history.

mysql> SHOW COLUMNS FORM jobs;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| JOB_ID | int(11) | NO | PRI | NULL | |
| JOB_TITLE | varchar(35) | NO | | NULL | |
| MIN_SALARY | decimal(6,0) | YES | | NULL | |
| MAX_SALARY | decimal(6,0) | YES | | NULL | |
+------------+--------------+------+-----+---------+-------+

mysql> SHOW COLUMNS FROM job_history;

+---------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------+------+-----+---------+-------+
| EMPLOYEE_ID | int(11) | NO | | NULL | |
| START_DATE | date | YES | | NULL | |
| HIRE_DATE | date | YES | | NULL | |
| JOB_ID | int(11) | NO | | NULL | |
| DEPARTMENT_ID | int(11) | NO | | NULL | |
+---------------+---------+------+-----+---------+-------+
Code:
ALTER TABLE job_history

ADD FOREIGN KEY(job_id)

REFERENCES jobs(job_id);

Copy

Let execute the above code in MySQL 5.6 command prompt

Now see the structure of the table job_history after being altered.
mysql> SHOW COLUMNS FROM job_history;
+---------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------+------+-----+---------+-------+
 ACCENTURE BATCH 2 LABORATORY

| EMPLOYEE_ID | int(11) | NO | | NULL | |

| START_DATE | date | YES | | NULL | |
| HIRE_DATE | date | YES | | NULL | |
| JOB_ID | int(11) | NO | MUL | NULL | |
| DEPARTMENT_ID | int(11) | NO | | NULL | |
Page | 17 +---------------+---------+------+-----+---------+-------+
Now see the created index file.
mysql> SHOW INDEX FROM job_history;
+-------------+------------+----------+--------------+----------
---+-----------+-------------+----------+--------+------+-------
-----+---------+
| Table | Non_unique | Key_name | Seq_in_index |
Column_name | Collation | Cardinality | Sub_part | Packed | Null
| Index_type | Comment |
+-------------+------------+----------+--------------+----------
---+-----------+-------------+----------+--------+------+-------
-----+---------+
| job_history | 1 | JOB_ID | 1 | JOB_ID
| A | NULL | NULL | NULL | | BTREE
| |
+-------------+------------+----------+--------------+----------
---+-----------+-------------+----------+--------+------+-------
-----+---------+
 ACCENTURE BATCH 2 LABORATORY

MySQL Alter Table Statement: Exercise-12 with Solution

Write a SQL statement to add a foreign key constraint named fk_job_id on job_id
Page | 18 column of job_history table referencing to the primary key job_id of jobs table.

Here is the structure of the table jobs and job_history.

mysql> SHOW COLUMNS FORM jobs;
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| JOB_ID | int(11) | NO | PRI | NULL | |
| JOB_TITLE | varchar(35) | NO | | NULL | |
| MIN_SALARY | decimal(6,0) | YES | | NULL | |
| MAX_SALARY | decimal(6,0) | YES | | NULL | |
+------------+--------------+------+-----+---------+-------+

mysql> SHOW COLUMNS FROM job_history;

+---------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------+------+-----+---------+-------+
| EMPLOYEE_ID | int(11) | NO | | NULL | |
| START_DATE | date | YES | | NULL | |
| HIRE_DATE | date | YES | | NULL | |
| JOB_ID | int(11) | NO | | NULL | |
| DEPARTMENT_ID | int(11) | NO | | NULL | |
+---------------+---------+------+-----+---------+-------+
Code:
ALTER TABLE job_history

ADD CONSTRAINT fk_job_id

FOREIGN KEY (job_id)

REFERENCES jobs(job_id)

ON UPDATE RESTRICT

ON DELETE CASCADE;

Copy

Let execute the above code in MySQL 5.6 command prompt

 ACCENTURE BATCH 2 LABORATORY

Now see the structure of the table locations after being altered.
mysql> SHOW COLUMNS FROM job_history;
+---------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
Page | 19 +---------------+---------+------+-----+---------+-------+
| EMPLOYEE_ID | int(11) | NO | | NULL | |
| START_DATE | date | YES | | NULL | |
| HIRE_DATE | date | YES | | NULL | |
| JOB_ID | int(11) | NO | MUL | NULL | |
| DEPARTMENT_ID | int(11) | NO | | NULL | |
+---------------+---------+------+-----+---------+-------+
Now see the created index file.
mysql> SHOW INDEX FROM job_history;
+-------------+------------+-----------+--------------+---------
----+-----------+-------------+----------+--------+------+------
------+---------+
| Table | Non_unique | Key_name | Seq_in_index |
Column_name | Collation | Cardinality | Sub_part | Packed | Null
| Index_type | Comment |
+-------------+------------+-----------+--------------+---------
----+-----------+-------------+----------+--------+------+------
------+---------+
| job_history | 1 | fk_job_id | 1 | JOB_ID
| A | NULL | NULL | NULL | | BTREE
| |
+-------------+------------+-----------+--------------+---------
----+-----------+-------------+----------+--------+------+------
------+---------+
 ACCENTURE BATCH 2 LABORATORY

MySQL Alter Table Statement: Exercise-13 with Solution

Write a SQL statement to drop the existing foreign key fk_job_id from job_history
Page | 20 table on job_id column which is referencing to the job_id of jobs table.

Here is the structure of the table job_history.

mysql> SHOW COLUMNS FROM job_history;
+---------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------+------+-----+---------+-------+
| EMPLOYEE_ID | int(11) | NO | | NULL | |
| START_DATE | date | YES | | NULL | |
| HIRE_DATE | date | YES | | NULL | |
| JOB_ID | int(11) | NO | MUL | NULL | |
| DEPARTMENT_ID | int(11) | NO | | NULL | |
+---------------+---------+------+-----+---------+-------+
Code:
ALTER TABLE job_history

DROP FOREIGN KEY fk_job_id;

Copy

Let execute the above code in MySQL 5.6 command prompt

Now see the structure of the table job_history after being altered.
mysql> SHOW COLUMNS FROM job_history;
+---------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------+------+-----+---------+-------+
| EMPLOYEE_ID | int(11) | NO | | NULL | |
| START_DATE | date | YES | | NULL | |
| HIRE_DATE | date | YES | | NULL | |
| JOB_ID | int(11) | NO | MUL | NULL | |
| DEPARTMENT_ID | int(11) | NO | | NULL | |
+---------------+---------+------+-----+---------+-------+
Now see the index file.
+-------------+------------+-----------+--------------+---------
----+-----------+-------------+----------+--------+------+------
------+---------+
 ACCENTURE BATCH 2 LABORATORY

| Table | Non_unique | Key_name | Seq_in_index |

Column_name | Collation | Cardinality | Sub_part | Packed | Null
| Index_type | Comment |
+-------------+------------+-----------+--------------+---------
----+-----------+-------------+----------+--------+------+------
Page | 21 ------+---------+
| JOB_HISTORY | 1 | fk_job_id | 1 | JOB_ID
| A | 1 | NULL | NULL | | BTREE
| |
+-------------+------------+-----------+--------------+---------
----+-----------+-------------+----------+--------+------+------
------+---------+
 ACCENTURE BATCH 2 LABORATORY

MySQL Alter Table Statement: Exercise-14 with Solution

Write a SQL statement to add an index named indx_job_id on job_id column in

Page | 22 the table job_history.

Here is the structure of the table job_history.

mysql> SHOW COLUMNS FROM job_history;
+---------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------+------+-----+---------+-------+
| EMPLOYEE_ID | int(11) | NO | PRI | NULL | |
| START_DATE | date | YES | | NULL | |
| HIRE_DATE | date | YES | | NULL | |
| JOB_ID | int(11) | NO | | NULL | |
| DEPARTMENT_ID | int(11) | NO | | NULL | |
+---------------+---------+------+-----+---------+-------+

Here is the index file of job_history table.

mysql> SHOW INDEXES FROM job_history;

+-------------+------------+----------+--------------+----------
---+-----------+-------------+----------+--------+------+-------
-----+---------+
| Table | Non_unique | Key_name | Seq_in_index |
Column_name | Collation | Cardinality | Sub_part | Packed | Null
| Index_type | Comment |
+-------------+------------+----------+--------------+----------
---+-----------+-------------+----------+--------+------+-------
-----+---------+
| job_history | 0 | PRIMARY | 1 |
EMPLOYEE_ID | A | 0 | NULL | NULL |
| BTREE | |
+-------------+------------+----------+--------------+----------
---+-----------+-------------+----------+--------+------+-------
-----+---------+
Code:
ALTER TABLE job_history

ADD INDEX indx_job_id(job_id);

Copy

Let execute the above code in MySQL 5.6 command prompt

 ACCENTURE BATCH 2 LABORATORY

Now see the structure of the table job_history after being altered.
mysql> SHOW COLUMNS FROM job_history;
+---------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
Page | 23 +---------------+---------+------+-----+---------+-------+
| EMPLOYEE_ID | int(11) | NO | PRI | NULL | |
| START_DATE | date | YES | | NULL | |
| HIRE_DATE | date | YES | | NULL | |
| JOB_ID | int(11) | NO | MUL | NULL | |
| DEPARTMENT_ID | int(11) | NO | | NULL | |
+---------------+---------+------+-----+---------+-------+
Now see the index file.
mysql> SHOW INDEXES FROM job_history;
+-------------+------------+-------------+--------------+-------
------+-----------+-------------+----------+--------+------+----
--------+---------+
| Table | Non_unique | Key_name | Seq_in_index |
Column_name | Collation | Cardinality | Sub_part | Packed | Null
| Index_type | Comment |
+-------------+------------+-------------+--------------+-------
------+-----------+-------------+----------+--------+------+----
--------+---------+
| job_history | 0 | PRIMARY | 1 |
EMPLOYEE_ID | A | 0 | NULL | NULL |
| BTREE | |
| job_history | 1 | indx_job_id | 1 | JOB_ID
| A | 0 | NULL | NULL | | BTREE
| |
+-------------+------------+-------------+--------------+-------
------+-----------+-------------+----------+--------+------+----
--------+---------+
 ACCENTURE BATCH 2 LABORATORY

MySQL Alter Table Statement: Exercise-15 with Solution

Write a SQL statement to drop the index indx_job_id from job_history table.
Page | 24
Here is the structure of the job_history and index file of the table job_history.
mysql> SHOW COLUMNS FROM job_history;
+---------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------+------+-----+---------+-------+
| EMPLOYEE_ID | int(11) | NO | PRI | NULL | |
| START_DATE | date | YES | | NULL | |
| HIRE_DATE | date | YES | | NULL | |
| JOB_ID | int(11) | NO | MUL | NULL | |
| DEPARTMENT_ID | int(11) | NO | | NULL | |
+---------------+---------+------+-----+---------+-------+

mysql> SHOW INDEXES FROM job_history;

+-------------+------------+-------------+--------------+-------
------+-----------+-------------+----------+--------+------+----
--------+---------+
| Table | Non_unique | Key_name | Seq_in_index |
Column_name | Collation | Cardinality | Sub_part | Packed | Null
| Index_type | Comment |
+-------------+------------+-------------+--------------+-------
------+-----------+-------------+----------+--------+------+----
--------+---------+
| job_history | 0 | PRIMARY | 1 |
EMPLOYEE_ID | A | 0 | NULL | NULL |
| BTREE | |
| job_history | 1 | indx_job_id | 1 | JOB_ID
| A | 0 | NULL | NULL | | BTREE
| |
+-------------+------------+-------------+--------------+-------
------+-----------+-------------+----------+--------+------+----
--------+---------+
+

Code:
ALTER TABLE job_history

DROP INDEX indx_job_id;

 ACCENTURE BATCH 2 LABORATORY

Copy

Let execute the above code in MySQL 5.6 command prompt

Now see the structure of the table job_history after being altered.
Page | 25
mysql> SHOW COLUMNS FROM job_history;
+---------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------+------+-----+---------+-------+
| EMPLOYEE_ID | int(11) | NO | PRI | NULL | |
| START_DATE | date | YES | | NULL | |
| HIRE_DATE | date | YES | | NULL | |
| JOB_ID | int(11) | NO | | NULL | |
| DEPARTMENT_ID | int(11) | NO | | NULL | |
+---------------+---------+------+-----+---------+-------+
Now see the index file.
mysql> SHOW INDEXES FROM job_history;
+-------------+------------+----------+--------------+----------
---+-----------+-------------+----------+--------+------+-------
-----+---------+
| Table | Non_unique | Key_name | Seq_in_index |
Column_name | Collation | Cardinality | Sub_part | Packed | Null
| Index_type | Comment |
+-------------+------------+----------+--------------+----------
---+-----------+-------------+----------+--------+------+-------
-----+---------+
| job_history | 0 | PRIMARY | 1 |
EMPLOYEE_ID | A | 0 | NULL | NULL |
| BTREE | |
+-------------+------------+----------+--------------+----------
---+-----------+-------------+----------+--------+------+-------
-----+---------+