Assignment 1: Aggregate Functions
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
- Show the total number of bowlers that live in Bethell, WA.
- Display the number of games won in match 14, game 2. Rename the resulting data column WINNERS.
- Show the number of games that the bowler with bowlerID 8 has won thus far.
- What is the total raw scores of all bowlers who played in match 13, game 3?
- Display the total handicap scores of the bowler that has a BowlerID of 18 for all matches and games. Rename the result column Total Handicap.
- Show the average of all raw scores of games lost in match 19. Note: the WonGame field is a Boolean value, therefore 0 represents a lost game, and 1 represents a game that was won.
- Display the largest handicap score of games won in match 20, game 2. Note: the WonGame field is a Boolean value, therefore 0 represents a lost game, and 1 represents a game that was won.
- Show the most recent tournament date in the bowling league schedule. Rename the resulting column Last Tournament Date.
- Display the raw scores of the games that are less than the average of all raw scores of games lost in match 20, game 1. Note: the WonGame field is a Boolean value, therefore 0 represents a lost game, and 1 represents a game that was won. You will use a subquery to retrieve this data. See p. 434 in your textbook.
- Display how many tournaments have been played at Sports World Lanes. Rename the result column Number of Tournaments.
Part 2
Directions: After reviewing and completing the Unit 3 Guided Practice 1, create five scenarios using the following SQL syntax, clauses, and keywords.
- Each scenario must include a select clause.
- You need to include the where clause in at least two scenarios.
- You need to use at least three of the following aggregate functions: COUNT(), SUM(), AVG(), MIN(), MAX()
- You also need to include one scenario that includes using aggregate function using filters as described on p. 432 in the textbook.
- 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.