SQL Practice Exercise:  Indexes

Index command syntax: 
    CREATE [UNIQUE] INDEX index_name ON table_name 
        (index_column_name [(length)] [ASC|DESC], ...)

    ALTER TABLE tbl_name ADD [UNIQUE] INDEX index_name 
        (index_columns)

    DROP INDEX index_name ON tbl_name

    ALTER TABLE tbl_name DROP INDEX index_name

    SHOW INDEX FROM tbl_name

1.  Create a table with these names and characteristics:
    Table Name  Column Name  Data Type  Length  Comment
    ----------  -----------  ---------  ------  --------
    Staff:      id           int           3    
                name         varchar      15    
                office       char          7
                fee          decimal     7,2
                reviewDate   date          -





2.  Define an index for Name on the Staff table. It should allow duplicates.
      


3.  Define an index for Id that prevents duplicate Id values.
    What constraint does this place on inserts to the tables?



4.  Show a way to cause a unique index to be created for Id that does not
    allow NULL values.


5.  Define an index to improve performance when searching by Name and Office.



6.  Define an index for fee. Indicate the index is to be in descending
    order. What might be the purpose for overriding the default sequencing?




7.  Issue a show index command to document the indexes.



8.  Use the DROP INDEX command to remove one of the indexes created for 
    this exercise.



9.  Apply the ALTER TABLE command to remove another of the indexes.