Programming with SQL
Objectives: Using Character, Number and Date Functions
• Create and execute single-row functions that perform case conversion and/or character manipulation
• Create and execute single-row number functions ROUND and TRUNC
• Create and execute single-row functions ADD_MONTHS and NEXT_DAY that operate on date data
Vocabulary:
Directions: Identify the vocabulary word(s) for each definition below.
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:
1. Using the words "Longview" and "College", use the CONCAT command to produce the following output. Use a column alias for "The Best School".
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.
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.
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.
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:
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)
Number Functions
Vocabulary:
Directions: Identify the vocabulary word(s) for each definition below.
1. Returns the remainder of a division.
Answer:
2. These functions accept numeric input and return numeric value.
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
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.
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:
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.
Date Functions
Vocabulary:
Directions: Identify the vocabulary word(s) for each definition below.
1. Function that adds calendar months to date.
Answer:
2. Function that determines next day of the date specified.
Answer:
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'.
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)
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
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.
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
• Create and execute a SQL query that correctly applies NVL single-row function
Vocabulary:
Directions: Identify the vocabulary word(s) for each definition below.
1. Used for text and character data of fixed length, including numbers, dashes, and special characters.
2. Used for character data of variable length, including numbers, special characters, and dashes.
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
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.
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
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.
:
Null Functions:
Vocabulary:
Directions: Identify the vocabulary word(s) for each definition below.
1. Returns the first non-null expression in the list.
2. Converts nulls to an actual value.
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
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.