After logging into oracle application express, perform these

Assignment Help Business Management

Reference no: EM13909642

Unit 2 Assignment 1: String Manipulation & Calculated Fields

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 all bowlers first and last names in one column result set, renaming the columnBowler's Name.
Display a list of all bowlers and addresses formatted suitability for a mailing list, sorted by zipcode.
What was the point spread between a bowler's handicap and raw score for each game played in match 10? Rename the point spread field in the data result set asPoint Differenceand sort on the match field.
Display the 3rd, 4th, and 5th letters of each tournament location displaying the resulting data set asLocation Letters.
Using a string function, display the tournament location of the tournament that occurred on 12/04/2012 in uppercase letters.
Show the last name of each bowler that lives in Seattle, WA in lowercase letters. Rename the column in the datasetbowler.
Display each bowler's last name, first, name and middle initial all in lowercase in one result column. Be sure that you include spaces between each piece of data. Also, rename the field asFull Name.
For the bowler named David Cunningham, display only the world Old that is located in his address. Rename the field in your resultPartial Address.
Display the team name MintJuleps asMint Juleps, naming the new resulting columnNewName.
- Note: Here you will use string and concatenation functions/clauses to take apart and piece back together the new string that contains a space.

Part 2

Directions: After reviewing and completing the Unit 2 Guided Practice 1, create six 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 3 scenarios.
You need to use at least two of the following string functions: LOWER(), UPPER(), CONCAT(), and SUBSTR().
You need to create two scenarios that contain mathematical expressions.
Lastly, you will need to rename one resulting field in your result data set using the AS keyword.**Feel free to include any string function that you have learned in the guided practice exercises 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.

Unit 2 Assignment 2: Date/Time & Formatting 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:

Scenarios

Display all of the tournament locations that hosted tournaments in the year 2012. Sort the results on the tourney date field in descending order.
Display the month using formatting techniques of all tournaments that were held at Imperial Lanes. Rename the data column result asMonth of Tournament.
Display all tournament dates of tournaments played at Bolero Lanes in the following fashion: Full written month/day/year (i.e., SEPTEMBER/28/10). Rename the result column asLong Date.
Provide an SQL statement that will return the current date and time. Rename the result column asCurrent Time. Please note that you will need to use a dual table for this scenario.
Provide an SQL statement that will return the day that you were born. You will need to use both a formatting and date function as well as a dual table to complete this task. Please pay attention to how to return a day of a date via formatting functionality. Rename the resulting column asMy Birth Day.
Use the months_between() SQL function to find the value between the dates 02/13/2004 and 06/28/2003. Here you will also be using a dual table. Feel free to rename the result column. Also if you would like to round the decimal place to two places, you can use the ROUND() function.
Find all of the distinct months and years of all tournament locations that have been involved in the bowling league since its start. The results will look like this:SEPT 12 OCT 12 NOV 12 Etc.

Part 2

Directions: After reviewing and completing the Unit 2 Guided Practice 2, 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 2 scenarios.
You need to use at 3 of the following date/time or formatting functions: to_char(), to_date(), DATE(), TIME(), NOW(), SYSDATE()
Feel free to use a dual table if you are returning values that are not contained in the database tables.
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.

Unit 3 Assignment 1: Aggregate Functions

Part 1

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:

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 columnWINNERS.
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 columnTotal 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 columnLast 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 columnNumber 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.

Unit 3 Assignment 2: Grouping Data & Summarizing Grouped Data

Part 1

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:

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.

After you are finished, please submit the Microsoft Word file that contains Part 1 and Part 2 of your assignment. Your document should be named U3_Assignment2_Lastname.docx. Submit your file using the Upload Instructions below.

Unit 4 Assignment 1: Subqueries

Part 1

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:

Scenarios

List the bowler's first and last names of all players on the Marlins Team. Hint: You will use a subquery to join the Bowlers and Team tables using the TeamID field.
Display the number of games each bowler has played. Show the bowler's first and last name in the result data set. Hint: You will create a subquery using the bowler ID field to join the Bowler and Bowler Scores tables. You will also need to utilize the COUNT() function.
List thelasttournament date that the bowling team Swordfish played. Hint: Here you will create a subquery using the Teams and Tournaments tables connecting the TourneyID with the TeamsID fields using the MAX() function.
Display the captain's first and last name of the Manatees. Hint: Here you will use a subquery with the Bowlers and Teams tables.
List the names of all teams that have played on odd numbered lanes. Hint: Here you will use a subquery with the Teams and Tourney_Matches tables. Because this query will return multiple rows, you will need to use the IN or ALL keywords.
Display the names of all bowlers that have a handicap score greater than 200. Display the last name and handicap scores in the results. Here you will use a subquery with the Bowlers and Bowlers_Scores tables. Because this query will return multiple rows, you will need to use the IN or ALL keywords.

Part 2

Directions: After reviewing and completing the Unit 4 Guided Practice 1, create four scenarios using the following SQL syntax, clauses, and keywords.

Each scenario must include a subquery using data from multiple tables.
You need to include the where clause in at least three scenarios.
You need to use at least two of the following aggregate functions: COUNT(), SUM(), AVG(), MIN(), MAX()
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.

Unit 4 Assignment 2: JOINs

Part 1

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:

Scenarios

Display the bowling teams and the name of each captain. Be sure that you include both the first and last name in the same column, renaming the column, and using concatenation techniques. Hint: You should use JOIN notation with the Teams and Bowlers tables.
Display a list of bowling teams and all team members along with the game scores. Hint: You will use a JOIN statement with the Teams, Bowlers, and Bowler_Scores tables.
Find the bowlers that live in the same zip code. Hint: You should join the bowlers table to itself creating an alias comparing equal zip codes AND non-matching BowlerIDs. Name the resulting columns like so:First Bowler,Bowler Zip, andSecond Bowler. To include both the first and last name in the same column, you will need to use concatenation techniques.
Display all of the good bowlers who have played at Thunderbird Lanes. Note: A good player has a raw score >=170. Hint: Here you will JOIN the Bowlers, Tourney_Matches, and the Tournaments tables.
Display all game scores greater than 180. Hint: Here you will JOIN Bowler_Scores, Tourney_Matches, and Tournaments together.
Show a list of all tournaments that have not yet been played. Hint: You will JOIN the Tournaments table with the Tourney_Matches table. Remember you are checking if the MatchID field is NULL. Display the Tournaments ID, Date, and Location in the result data set.
Show all tournament matches that have been played thus far. Hint: You will JOIN the Teams table with the Tourney_Matches table. Remember you need to consider matches played on both even and odd lanes. Display the match ID, Tournament ID, Team Names of teams that played on even lanes, and team name of the teams that played on odd lanes in the result data set. Sort the results by the match ID field.

Part 2

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

Each scenario must include JOIN notation to extract data from multiple tables.
You need to use alias notation as needed to join key fields.
You need to use at least one of the following aggregate functions: COUNT(), SUM(), AVG(), MIN(), MAX()
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.

Unit 5 Assignment: Set Logic and Unions

Part 1

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:

Scenarios

List all names from the Teams and Bowlers tables. Hint: Use a Union clause.
List all of the tournaments that are not scheduled for tournament matches. Hint: Here you will use the MINUS clause with the Tournaments and Tourney_Matches tables.
Find the bowlers who had a raw score of 165 or better at Thunderbird Lanes combined with bowlers who had a raw score of 150 or better at Bolero Lanes. Hint: You will use Join notation and a Union to retrieve the data in the Bowlers, Tournaments, Tournament_Matches, and Bowlers_Scores tables.
List the tourney matches, team names, and team captains for teams starting on the odd lane together with the tourney matches, team names, and team captains for teams starting on the even lane. Sort by tournament date and match number. Hint: Here you will Join Tournaments, Tourney_Matches, and the Teams tables. You will also use the UNION ALL clause. Your resulting data set should resemble the screen shot below.

Part 2

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

Each scenario must include multiple tables.
You need to use alias notation as needed to join key fields.
You need to use one of the following clauses in each scenario: UNION, INTERSECT, MINUS
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.

Reference no: EM13909642

Questions Cloud

Create an entity relationship diagram : For your final project you will create an Entity Relationship Diagram (ERD), create tables in your own database, and import data into these tables. For Part 1 of the Project, you will create the ERD. For Part 2 you will build the tables and load t..

What is sustainable investing? : What is sustainable investing?

Explain why given expression must be jointly gaussian : Explain why X1, ... , Xn, Z1, ... , Zm must be jointly Gaussian rv s. Then explain why X1, ... , Xn, Y1, ... , Ym must be jointly Gaussian.

What about the environment at the coffee bean : 1. How would you gauge Peter's achievements orientation? What are some of the needs not being met for Peter Gibbons at Initech? What changes might improve Peter's motivation? What about the environment at The Coffee Bean? Pygmalion or Golem?

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

What are financial actions : What are financial actions and revised goals Jenny might want to consider at this time?

Case study company''s csr and business ethics practices : case study company's CSR and business ethics practices

Give detailed descriptions and be specific : In the space provided write eight to ten sentences on of the following topics. Make sure you write in complete sentences. Give detailed descriptions and be specific.

Goods transferred from production to finished goods : Dream Toys Company manufactures video game consoles and accounts for product costs using process costing. The following information is available regarding its June inventories.

User Account

All Pages