Reference no: EM13923208

Assignment 2: Grouping Data & Summarizing Grouped Data

Part 1 

After reviewing and completing the Unit 1 Guided Practice 2, I suggest that you review all tables using the Object Browser area of the SQL Workshop associated with the scenarios below, as well as field data types and data (case sensitivity) before constructing your SQL statements.

After logging into Oracle Application Express, perform these steps to get started:

1. To access the SQL command area, click on SQL Workshop and then on the SQL command icon.

2. Your screen should resemble the following:

Directions: For all of the scenarios below, compose SQL syntax to retrieve data, click the Run button, and then review the resulting set in the bottom pane.  Save all scenarios, SQL syntax, and the screenshots for the resulting data sets in a Microsoft Word document to upload for review and grading.

Scenarios

• Display the bowler's last name and the average of every bowler's raw score for each bowler. Hint: You will JOIN the Bowlers table on the Bowler_Score table using the AVG() function and a Group By clause. To remove all of the decimal places within the AVG() results, you can use the ROUND() function.
• Calculate the current total raw score and games bowled for each bowler. Display the bowlers ID, last name, and first name in the result set. Rename the total raw score asTotal Pinsand the total number of games bowledBowled Games.
  Hint: Here you will JOIN the Bowlers table on the Bowler_Scores table using the SUM() and COUNT() functions as well as a Group By clause.
• Display the highest raw score for each bowler. Display the bowler's last and first name in the resulting data set. Hint: Here you will JOIN the Bowlers table on the Bowler_Scores table using the MAX() function. You can also use a subquery to return the same data set.
• List the last name of every bowler whose average raw score is less than or equal to the overall average score. Display both the bowlers' last and first names in the resulting set. Hint: Here you will JOIN the Bowlers table with the Bowler_Scores table using the HAVING clause and a subquery to compare the averages AVG(). You will use the Group By clause to accurately display the data. To remove all of the decimal places within the AVG() results, you can use the ROUND() function.
• Display the team ID, bowler ID, and bowler's lastname for every bowler as well as their highest bowling score. Hint: You will JOIN the Teams table, Bowlers table, and the Bowlers_Score table using the MAX() function and a Group By clause. Rename the highest bowling scores in the resulting data setMax Raw Score.
• Display for each bowler the bowler last name and the average of the bowler's raw games scores for the bowlers whose average is greater than 160. Hint: You will JOIN the Bowlers table with the Bowlers_Scores table using the AVG() function, Group By, and a HAVING clause. To remove all of the decimal places within the AVG() results, you can use the ROUND() function.
• Show the bowler ID, and bowler's lastname and firstname for every bowler as well as their lowest bowling score. Hint: You will JOIN the Bowlers table with the Bowlers_Score table using the MIN() function and a Group By clause. Rename the lowest bowling scores in the resulting data setLowest Raw Score.

Part 2

Directions: After reviewing and completing the Unit 3 Guided Practice 2, create three scenarios using the following SQL syntax, clauses, and keywords.

• One scenario must include a subquery.
• You need to include the GROUP By clause in at least two scenarios.
• You need to use at least two of the following aggregate functions: COUNT(), SUM(), AVG(), MIN(), MAX()
• You also need to include one scenario that utilizes the Having clause.
• Lastly, you will need to rename at least one resulting field in your result data set using the AS keyword.
  **Feel free to include any other functions that you have learned in the guided practice exercises or previous SQL syntax to expand your learning experience.

You may use any of the Bowling League tables or the Demo_CUSTOMERS/ORDERS/PRODUCTS tables in your Oracle account.  Be sure to list the scenario, include the SQL syntax that retrieves the data, and include a screenshot of your result data sets.