Unit 2 Assignment 1: String Manipulation & Calculated Fields Complete Solution

Question: #8460

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 column Bowler'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 as Point Difference and sort on the match field.
•   Display the 3rd, 4th, and 5th letters of each tournament location displaying the resulting data set as Location 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 dataset bowler.
•   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 as Full Name.
•   For the bowler named David Cunningham, display only the world Old that is located in his address. Rename the field in your result Partial Address.
•   Display the team name MintJuleps as Mint Juleps, naming the new resulting column NewName.
o   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.
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 U2_Assignment1_Lastname.docx. Submit your file using the Upload Instructions below.