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:
  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. Calculate a code number in the following way using the table you created above. The number has to be the second,third and fourth characters of one of your fields concatenated with the answer from a calculation, concatenated with the mod from your numeric field divided by 3, concatenated with the length of one of your fields, concatenated with the lower case version of one of your fields.
  7. Alter the structure of the table by adding a column/field and populating it. Show me the results.
  8. Show all records with condA true and either condB or condC true.
  9. Using the table you created 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 you should also exclude one of the groups from the list.
  12. 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 the 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.
  13. The name you defined should now be altered. I want you to display the name as first, space, middle last. So Doe/John M would be John M Doe and Jones/Mary would become Mary Jones.