MySQL Alter Table Statement Exercises: Drop the index indx_job_id from job_history table
MySQL Alter Table Statement: Exercise-15 with Solution
Write a SQL statement to drop the index indx_job_id from job_history table.
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;
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 | 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 | | +-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous: Write a SQL statement to add an index named indx_job_id on job_id column in the table job_history.
Next: Basic SELECT statement
What is the difficulty level of this exercise?
- New Content published on w3resource:
- HTML-CSS Practical: Exercises, Practice, Solution
- Java Regular Expression: Exercises, Practice, Solution
- Scala Programming Exercises, Practice, Solution
- Python Itertools exercises
- Python Numpy exercises
- Python GeoPy Package exercises
- Python Pandas exercises
- Python nltk exercises
- Python BeautifulSoup exercises
- Form Template
- Composer - PHP Package Manager
- PHPUnit - PHP Testing
- Laravel - PHP Framework
- Angular - JavaScript Framework
- Vue - JavaScript Framework
- Jest - JavaScript Testing Framework