Programming with sql using number and date fun

Assignment Help Database Management System

Reference no: EM13777835

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.

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

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:

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'.

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

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

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

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

Reference no: EM13777835

Questions Cloud

Value chain through vertical integration : In some cases one of the purposes of initiating a business is to control your value chain through vertical integration. General Motors relies on an unrelated company for a key component. General Motors relies on a vertically integrated subsidiary for..

Construct an entity relationship diagram : Explain the differences between the notions of Database (DB), Database Management System (DBMS) and Database System (DBS).

Number of kanban cards increase issue : How does input from other areas into PAC and the feedback PAC provides impact PAC? Explain.

Production cost report using the weighted-average method : Edenton Boat Company manufactures small pleasure boats on an assembly-line basis. The units are started in the Department A. On July 1 of this year, the Work-in-Process inventory of the department A consisted of 200 units 100% complete as to material..

Programming with sql using number and date fun : 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. •..

Should a department policy be updated with each new case : As a student of human resource management in criminal justice organizations, you have studied the potential effects and cost associations of section 1983 Liability lawsuits arising out of the violation of certain protected Civil Rights. Should a d..

What effect will such a drastic measure have : Is it possible that you could lose some really good personnel who only messed up once in their whole career? What effect will such a drastic measure have on the personnel's families? If you keep them, will it affect the overall credibility of your ag..

What are the major concerns about outsourcing : Locate a recent article on outsourcing practices. Compute the following requirements based on the article. What are the major reasons for the decision to outsource? What are the major concerns about outsourcing? Which business functions are outsource..

What decisions were or were not made in the case study : What decisions were or were not made in the case study? Do you believe the decisions or best practices were appropriate

User Account

All Pages