Unit 2 Assignment 2: Date Time & Formatting Functions Complete Solution

Question: #8461

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:

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 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 as Month 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 as Long Date.
•   Provide an SQL statement that will return the current date and time. Rename the result column as Current 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 as My 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.
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_Assignment2_Lastname.docx. Submit your file using the Upload Instructions below.