ITEC 340 , Spring 2006

Homework #2

Gymnastics Queries

Eexamine the structure and data of the athletes, teams, events, meets, and performances tables in the gymnastics schema. Create 10 SQL scripts for the queries below using these five tables only. Where “name” is asked for assume first name and last name. You may not use the RESULTS view that you queried for homework assignment 1. Please place each clause of the SQL select command on a different line.

h2a.sql A list of athlete names and their all-around score for the Snowflake Meet from higheset all-around score to lowest all-round score.

h2b.sql The names of all athletes who competed in both the Snowflake and Valentine meets.

h2c.sql The names of all athletes who competed in the Snowflake meet but not the Valentine meet.

h2d.sql The names of all athletes who competed in the Valentine meet but not the Snowflake meet.

h2e.sql A list of all athletes and their coaches.

h2f.sql The name, phone, and team of all athletes who competed in the Snowflake meet.

h2g.sql. The name, phone, and team of any athlete who did not compete in the Snowflake meet.

h2h.sql The name of any athlete who improved their all-around score from the Snowflake to the valentine meet.

h2i.sql The highest score in the database for each event including the name of the athlete or athletes who made the score, their team name, and the date of the performance.

h2j.sql The name and team of every athlete who made a 9.0 or higher on any event this year.

Create a folder named RU02 in your student folder on Neelix and place your scripts in that folder by 11:00p.m. on Thursday, February 2^nd.