Q1. Draw from the gymnast data model to prepare a statement that will show event name, meet name, and average (AVG) score (from the score field in the g2m2e table). Round the average score to the thousandths place. Give each column in the SELECT clause an alias.
Recall that all non-parameterized fields included in the SELECT clause must also appear in the GROUP BY clause. The eventName field should be listed first in the SELECT and GROUP BY clauses. Sort by average score in descending order. Hint: you’ll need to pull from three tables.
Q2. Draw from the project data model to prepare a statement that will show showing department name and the average (AVG) employee hourly wage (i.e., empHourlyWage), but only for those departments with a greater-than-average employee hourly wage. Give each column in the SELECT clause an alias (e.g., “Avg. Employee Hourly Wage”). Average employee hourly wage should be rounded to the hundredths place in the SELECT clause. Sort by average employee hourly wage in descending order.
Hint #1: You’ll need a GROUP BY clause and a HAVING clause.
Hint #2: You’ll need a subquery in the HAVING clause on the right side of the operator.
Q3. Draw from the gymnast data model to prepare a statement that will show university name, meet name, and university final score (i.e., univFinalScore). Include only the top 5 final scores from the univFinalScore field. Also create a view (named topFiveFinalScores) for this statement.
Hint #1: This statement requires a WHERE ROWNUM clause, so you'll need a subquery in your FROM clause.
Hint #2: Within the subquery you'll need three tables in your FROM clause, and you'll need to join them.
GYMNAST DATA MODEL
PROJECT DATA MODEL
