MySQL Assignment

Copy and paste is a dangerous thing and I messed up in a few places. Hopefully I have caught them all with your help.

1. Create a MySQL table with a minimum of two varchar fields, a char field, a numeric field with decimal places, a numeric field for a whole number.
2. Show me a description of the table.
3. Populate the table with data you should put in a variety of data so that it can be effectively queried.
   One of the varchar fields should contain a name in the format last name slash first name middle name or initial if any. For example:
   • Doe/John M
   • Jones/Mary
   • Adams-Costa/Susan Ann
   • Langley/M Richard
4. Change a record and show me the before and after.
5. Do a query that does a sort on two fields. Tell me which field is the primary sort and which is the secondary sort. Show me the results.
6. Do a query using single row functions that flip the name so that it reads first middle (if any) last instead of last/first middle. For example:
   • Doe/John M should be flipped to be John M Doe
   • Jones/Mary should be flipped to Mary Jones
   • Adams-Costa/Susan Ann should be flipped to be Susan Ann Adams-Costa
   • Langley/M Richard should be flipped to be M Richard Langley
7. Calculate a code number in the following way using the table you created above. The number has to be the third and fourth characters of one of your fields concatenated with the mod from your numeric field divided by 3, concatenated with the last name from the name field concatenated with the length of one of your fields.
8. Alter the structure of the table by adding a column/field and populating it. Show me the results.
9. Again using the table you created above group on one field and show me the count and the sum of your numeric field.
10. Repeat the query above but exclude one or more records as you form the groups.
11. Repeat the query again but now also exclude one of the groups from the list.
12. This one I really copied and pasted the wrong thing. Here is the fix:
    Pick a field from your table such as dept or state or something else that occurs multiple times. If you do not have something add a field to your table and populate it. You are going to form groups based on that field. Now eliminate one or more records from the groups as you are forming them. Then I want you to eliminate one or more groups before you display. I want the display to be in order by the field and I want to see a sum and average of something when you display.