SQL Assignment in Access

This assignment is to be done entirely using SQL - please try to copy the SQL before Access modifies it too much - easier to read. Remember your best chances of saving the "pure" version are to do a file save.

To copy the SQL to a word document simply highlight, copy and paste.

Show me also the results of the query. This can be accomplished by clicking on the blank box above the leftmost column which selects all and then pasting in your word document.

Using the oil database or another database that you have created. DO NOT use the donor database since all my examples are from that database. Be sure to give me the layout of the table and the data in the table at the top of the assignment.

  1. Do a query that involves a group function such as AVG, SUM, COUNT, MAX, MIN. Use it with the GROUP BY clause.
  2. Do a second query that involves a group function with the GROUP BY clause.
  3. Do a query that involves a group function that excludes records prior to forming the group.
  4. Do a query that involves a group function that excludes a group after it has been formed.
  5. Do an update that allows the user to key in the change to made and the record to be changed.
  6. Do a simple subquery using an operator such as >, <, =.
  7. Do a subquery using IN.
  8. Explain the difference between when you can use >, <, = and when you must use IN.
  9. Do a subquery that uses one table for the inner subquery and a different table for the outer subquery.
  10. Do a subquery that uses a group function in the inner subquery.
  11. Do a query involving two subqueries in an AND relationship.
  12. Do a query involving two subqueries in an OR relationship.
  13. Do a subquery using ANY.
  14. Do a subquery using ALL.
  15. Do a query with a subquery that uses the HAVING clause.