ITEC 340 , Spring 2006

Homework #2 Solution

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.

SELECT fname, lname, SUM(score)

FROM gymnastics.athletes NATURAL INNER JOIN gymnastics.performances

WHERE meet = ‘Snowflake’

GROUP BY aid, fname, lname

ORDER BY sum(score) DESC;

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

SELECT fname, lname

FROM gymnastics.athletes

WHERE aid IN(SELECT aid FROM gymnastics.performances

WHERE meet = ‘Snowflake’

INTERSECT

SELECT aid FROM gymnastics.performances

WHERE meet = ‘Valentine’);

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

SELECT fname, lname

FROM gymnastics.athletes

WHERE aid IN(SELECT aid FROM gymnastics.performances

WHERE meet = ‘Snowflake’

MINUS

SELECT aid FROM gymnastics.performances

WHERE meet = ‘Valentine’);

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

SELECT fname, lname

FROM gymnastics.athletes

WHERE aid IN(SELECT aid FROM gymnastics.performances

WHERE meet = ‘Valentine’

MINUS

SELECT aid FROM gymnastics.performances

WHERE meet = ‘Snowflake’);

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

SELECT fname, lname, coachfname, coachlname

FROM gymnastics.athletes INNER JOIN gymnastics.teams

ON athletes.team = teams.name;

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

SELECT fname, lname, phone, team

FROM gymnastics.athletes NATURAL INNER JOIN gymnastics.performances

WHERE meet=’Snowflake’;

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

SELECT fname, lname, phone, team

FROM gymnastics.athletes

MINUS

SELECT fname, lname, phone, team

FROM gymnastics.athletes NATURAL INNER JOIN gymnastics.performances

WHERE meet=’Snowflake’;

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

SELECT lname, fname, SnowflakeScore, ValentineScore

FROM

(SELECT aid, SUM(score) as SnowflakeScore

FROM gymnastics.performances

WHERE meet=’Snowflake’

GROUP BY AID)

NATURAL INNER JOIN

(SELECT aid, SUM(score) as ValentineScore

FROM gymnastics.performances

WHERE meet=’Valentine’

GROUP BY AID)

NATURAL INNER JOIN

Gymnastics.athletes

WHERE ValentineScore > SnowflakeScore;

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.

SELECT lname, fname, team, meetdate

FROM

gymnastics.athletes

NATURAL INNER JOIN

(SELECT performances.event, maxScore, meet, aid

FROM

(Select event, MAX(score) AS maxScore

FROM gymnastics.performances

GROUP BY event) topscores

INNER JOIN

gymnastics.performances

ON performances.event = topscores.event and performances.score = topscores.maxScore) p

INNER JOIN gymnastics.MEETS m

ON m.name = p.meet;

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

SELECT fname, lname, team

FROM gymnastics.athletes NATURAL INNER JOIN gymnastics.performances

WHERE score >= 9.0;