Homework assignment #5
CS 643, Spring 2002
Due: Wednesday, March 13, 2002
Total points: 100

You're be asked to write the following queries with the tables in the SH schema. Save operations in "queries.sql". This section can be shared by two students.

  1. Use the "EXPLAIN PLAN FOR" statement or "SET AUTOTRACE TRACEONLY" to view the execution plan for the following queries. (The structures of the tables in SH)
  1. Write a query on a table of your choice. Specify the condition as a value of an index attribute.
  2. Write a query on a table of your choice. Specify the condition as a value of a non-index attribute.
  3. Write a query on a table of your choice. Specify the condition as either greater than or less than a value of an index attribute.
  4. Write a query on a table of your choice. Specify the condition as either greater than or less than a value of a non-index attribute.
  5. Write a query of equijoin between two tables. Specify additional condition on one of or both of the tables to see if there's any change of the execution plan.
  6. Write a query of non-equijoin between two tables. Specify additional condition on one or both of the tables to see if there's any change of the execution plan.
  7. Write a query of outer join between two tables. Specify additional condition on one or both of the tables to see if there's any change of the execution plan.
  8. Write a query of full outer join between two tables. Specify additional condition on one or both of the tables to see if there's any change of the execution plan.
  9. Write a query of Cartesian join between two tables. Specify additional condition on one or both of the tables to see if there's any change of the execution plan.
  10. Write a query of anti-join between two tables. Specify additional condition on one or both of the tables to see if there's any change of the execution plan.
  11. Write a query of semi-join between two tables. Specify additional condition on one or both of the tables to see if there's any change of the execution plan.
  12. Write a star query between a fact table and two lookup tables. Specify additional condition on either type of the tables to see if there's any change of the execution plan.
  1. Apply various optimizer hints to some of the above queries to see if there's any change of the execution plan.

Back to CS643 schedule