SQL assignment #2 for MySQL - CIT32 For this assignment you should take a picture of the screen for each question that shows what you entered and the results.
  1. Create a table with at least a varchar field, a char field, a number field with decimal places, an integer and a date. The table should have at least two fields that you can group on: things like dept or state. The table should include a name field where the name is entered in the format last name slash first name middle name or initial if any. For example: Doe/John or Smith/Mary S or Adams-Costa/Susan A. Populate the table with at least 5 records, one of the records should contain NULL in an amount field. Show me the table with data and the description.
  2. Do a query using single row functions that flip the name so that it displays as first middle (if any) last instead of last/first middle. For example: Doe/John should be flipped so it displays as John DOe, Smith/Mary S should be flipped so it displays as Mary S Smith, Adams-Costa/Susan A should be flipped so it displays as Susan A Adams-Costa.
  3. Do a query where you find the count of the records.
  4. Do a query where you have the count, sum and average on a numeric field (use the numeric field that has NULL in at least one record)
  5. Do a query and group on a field and show the sum and average as well as the count.
  6. Do a query where you group and eliminate a record from the group based on your choice of criteria. You can also decide what you are showing for the group.
  7. Modify the query above so that you eliminate certain groups from the display.
  8. Do a query that involves a sort on two fields (be sure to tell me which is the primary field and which is the secondary field).
  9. Do a query that involves a nested function (a function within a function)
  10. Explain the difference between a query that involves using the function upper and a query that involves using the function sum. I am looking for an explanation about what each query acts on and the results.
  11. Do a query that requires that condition A be true and either condition B or condition C be true
  12. Add a record to your table.
  13. Change information on a record on your table.
  14. Delete a record from your table.