SQL Class Exercise: Managing Database Objects

1.  Create a tractor Implement table from these column definitions.
    Column Name                 Type         Constraint
    --------------------------- -----------  -----------
    Name                        varchar(25)  primary key
    Cost                        decimal(7,2)
    Category                    char(10)
    Quantity                    int(1)


	
	
	
	
2.  Load the Implement table with data from a file named implement.csv.


3.  Alter the Implement table to drop the primary key.


4.  Add a column named id, data type int, unsigned, not null
    with the auto_increment type attribute and designated the primary
    key. [Place id first in left-to-right sequence.]


5.  Create a unique index named xName on the Name column.


6.  Create a non-unique index named xCategory on the Category column.


7.  Create a view named Utility that includes all rows with the Category value
    'Utility'.


8.  Grant the insert and select privileges on Implement and the select privilege
    on the Utility view to another user (not root).


9.  Switch connections to the other user and verify insert and select privileges
    were granted correctly.


10. Use the show command or query the information_schema database to 
    document the objects and constraints created to answer the preceding
    questions. Include table and column definitions, indexes, and view text.


11. Revoke the privileges granted to access Implement and Utility.

implement.csv
Box Blade-4 ft,429.99,grader,7
Cultivator,224.99,tillage,5
Finishing Mower-5 ft,1379.00,cutter,5
Finishing Mower-6 ft,1599.00,cutter,8
Landscape Rake,469.99,utility,4
Middle Buster,138.62,tillage,0
Post Hole Digger-6 ft,449.99,utility,2
Rear Blade-5 ft,319.99,grader,5
Rotary cutter-4 ft,749.99,cutter,0
Rotary Tiller-4 ft,1449.00,tillage,6
Rotary Tiller-5 ft,1499.99,tillage,4
Seeder Spreader,379.99,planter,3
Sub-Soiler,179.99,tillage,3
Trailer Mover,168.65,utility,4
Wire Roller,319.99,utility,0