answer Q
basil101516
3.rtfPage 8 of 8
Delete this text and type your name here
This file will become rather large due to your screen shots. Please compress this file (zip) before submitting it.
Lab 3: 4 0 Total Points Possible
You will need to log into Apex at https://iacademy.oracle.com in order to complete this assignment.
Sections 1-2 Programming with SQL
Section 1 Objectives : Using Character, Number and Date Functions
Create and execute single-row functions that perform case conversion and/or character manipulation
Vocabulary:
Directions: Identify the vocabulary word(s) for each definition below. (1 Point each)
1. Dummy table used to view results from functions and calculations.
Answer:
2. Replaces a sequence of characters in a string with another set of characters.
Answer:
Try it/Solve it:
Log into Apex and execute the following from the SQL Workshop:
1. Using the words "Longview" and "College", use the CONCAT command to produce the following output. Use a column alias for "The Best School".
Provide the s elect s tatement you used : ( 2 Point s )
Your Answer below :
Using your print screen option (PrntScr button across top of keyboard), p rovide a screen shot of your results below . It is necessary that I see your 'entire' screen. Your workspace/username MUST appear in the results to earn credit. (1 Point)
Your Screens hot below :
2. Display the order date and the order total from the Global Fast Foods F_ORDERS table. Name the order total as TOTAL (using column alias) and fill in the empty spaces to the left of the order total with $ (using the LPAD character manipulation function). The F_ORDERS table data is located in the Oracle Student Tables spreadsheet located under the Video and Resources button in Blackboard. Results will appear as shown below:
Provide the s elect s tatement you used : ( 2 Point s )
Your Answer below:
Using your print screen option (PrntScr button across top of keyboard), provide a screen shot of your results below. It is necessary that I see your 'entire' screen. Your workspace/username MUST appear in the results to earn credit. (1 Point)
Your Screens hot below:
3. Display the last name and birth date of staff members from the Global Fast Foods F_STAFFS table. Use character case-manipulation function UPPER so the last name displays in uppercase. Use a column alias so the last name displays as 'User Name'. Use a column alias so the birth date displays as 'Birthday'. Results should appear as shown below:
Provide the s elect s tatement you used : (2 Point s )
Your Answer below:
Using your print screen option (PrntScr button across top of keyboard), provide a screen shot of your results below. It is necessary that I see 'entire' screen. Your workspace/username MUST appear in the results to earn credit. (1 Point)
Your Screens hot below:
Nu mber Functions
Vocabulary:
Directions: Identify the vocabulary word(s) for each definition below. (1 point each)
1. Returns the remainder of a division.
Answer:
2. These functions accept numeric input and return numeric value.
Answer:
Try it/Solve it:
Log into Apex and execute the following from the SQL Workshop:
1. Use the ROUND function to round 34.5444 so that it is displayed with three decimals. Use a column alias so the column displays 'Round Function'. Results should appear as shown below:
Round Function
34.544
Provide the s elect s tatement you used : (2 Point s )
Your Answer below:
Using your print screen option (PrntScr button across top of keyboard), provide a screen shot of your results below. It is necessary that I see your 'entire' screen. Your workspace/username MUST appear in the results to earn credit. (1 Point)
Your Screens hot below:
2. Using the EMPLOYEES table data, display the employee's last name and salary for employees who work in department 80. Give each of them a raise of 5.333% and use the TRUNC function to display no more than two decimal places. The salary should display as 'Raise Amount'. Results should appear as shown below:
Provide the s elect s tatement you used : ( 2 Point s )
Your Answer below:
Using your print screen option (PrntScr button across top of keyboard), provide a screen shot of your results below. It is necessary that I see your 'entire' screen. Your workspace/username MUST appea r in the results to earn credit . (1 Point)
Your Screens hot below:
Date Functions
Vocabulary:
Directions: Identify the vocabulary word(s) for each definition below. (1 Point each)
1. Function that adds calendar months to date.
Answer:
2. Function that determines next day of the date specified.
Answer:
Try it/Solve it:
Log into Apex and execute the following from the SQL Workshop:
1. Your next dentist appointment is six months from today (the day you are working on this assignment). What day will this be? Name the output 'Dentist Appointment'.
Provide the s elect s tatement you used : ( 2 Point s )
Your Answer below:
Using your print screen option (PrntScr button across top of keyboard), provide a screen shot of your results below. It is necessary that I see 'entire' screen. Your workspace/username MUST appear in the results to earn credit. (1 Point)
Your Screens hot below:
2. Write and execute a statement that will return only the DJs on Demand CDs (d_cds table) with years greater than 2000 but less than 2003. Display both the title and year. Results should appear as shown below:
TITLE
YEAR
Back to the Shire
2002
Here Comes the Bride
2001
Provide the s elect s tatement you used : ( 2 Point s )
Your Answer below:
Using your print screen option (PrntScr button across top of keyboard), provide a screen shot of your results below. It is necessary that I see your 'entire' screen. Your workspace/username MUST appear in the results to earn credit. (1 Point)
Your Screenshot below:
Section 2 Objectives : Using Single Row Functions
Create and execute a SQL query that correctly applies TO_CHAR and TO_DATE single row functions to produce a desired result
Vocabulary:
Directions: Identify the vocabulary word(s) for each definition below. (1 Point each)
1. Used for text and character data of fixed length, including numbers, dashes, and special characters.
Answer:
2. Used for character data of variable length, including numbers, special characters, and dashes.
Answer:
Try it/Solve it:
Log into Apex and execute the following from the SQL Workshop:
1. Display the ID, name and salary for all Global Fast Foods employees. Display salary with a $ sign and two decimal places. Salary column should display as 'Hourly Rate'. Results should appear as shown below:
ID
FIRST_NAME
Hourly Rate
12
Sue
$6.75
9
Bob
$10.00
19
Monique
$60.00
Provide the s elect s tatement you used : ( 2 Point)
Your Answer below:
Using your print screen option (PrntScr button across top of keyboard), provide a screen shot of your results below. It is necessary that I see 'entire' screen. Your workspace/username MUST appear in the results to earn credit. (1 Points)
Your Screenshot below:
2. Create a select statement using TO_DATE that will convert January 3, 04 to the default date format. Use an alias to label the output 'Date'. Results should appear as shown below:
Date
03/Jan/2004
Provide the s elect s tatement you used : ( 2 Point s )
Your Answer below:
Using your print screen option (PrntScr button across top of keyboard), provide a screen shot of your results below. It is necessary that I see your 'entire' screen. Your workspace/username MUST appear in the results to ear n credit . (1 Point)
Your Screenshot below:
Null Functions:
Vocabulary:
Directions: Identify the vocabulary word(s) for each definition below. (1 point each)
1. Returns the first non-null expression in the list.
Answer:
2. Converts nulls to an actual value.
Answer:
Try it/Solve it:
Log into Apex and execute the following from the SQL Workshop:
1. Not all Global Fast Foods staff members (f_staffs table) receive overtime pay. Instead of displaying a null value for these employees, replace null with zero. Include the employee’s last name and overtime rate in the output. Label the overtime rate as 'Overtime Status'. Results should appear as shown below:
LAST_NAME
Overtime Status
Doe
10.25
Miller
0
Tuttle
0
Provide the s elect s tatement you used : ( 2 Point s )
Your Answer below:
Using your print screen option (PrntScr button across top of keyboard), provide a screen shot of your results below. It is necessary that I see your 'entire' screen. Your workspace/username MUST appear in the results to earn credit. (1 Point)
Your Screenshot below:
