COEN 178 Intro to Database Systems Assign 1 solution

$24.99

Original Work ?
Category: You will Instantly receive a download link for .ZIP solution file upon Payment

Description

5/5 - (5 votes)

Create a table called MyExpenses to hold the expenses by date and in various categories, for example, food, commute and misc.

For example, the table should have data as shown below.

CDATE         FOOD    COMMUTE       MISC

21-Oct-19         30          5              15

22-Oct-19         50          0              25

23-Oct-19         80         10         60

 

Note: You are free to choose the expense categories (3 categories are shown in the example). You must use Date as the datatype for CDATE and this is the primary key of the table.

Insert some data of your choice into the MyExpenses table.

Example:

insert into MyExpenses values (’21-OCT-19′,30.00,5.00,15.00);

Hint: The standard Oracle date format is DD-MON-YY, as shown above.

 

  1. Write a PLSQL function called getExpensesByDate to calculate the total expenses (expenses for all categories) for a single date, where the date is passed as a parameter.

Example:

Create or Replace Function getExpensesByDate(v_date In Date)Return Number IS

— Complete the function

Now test your function by calling the function as follows:

Select getExpensesByDate(’21-OCT-19′) from Dual;

Note: You can replace the date by one of your choice.

  1. Write a PLSQL function called getExpensesBetweenDates() which takes two parameters of type Date and returns the total for all the days between the two dates. See the signature of the function below:

 

Create or Replace Function getExpensesBetweenDates(v_fromdate In Date, v_toDate In Date)Return Number IS

 

— Complete this function

Test your function as follows:

Select getExpensesBetweenDates(’21-OCT-19′, ’23-OCT-19′) from Dual;

Run the queries and capture the results in assign1_output.txt, using spool.

What to submit:

  1. Your PLSQL code segments.
  2. The data in your table (Do a Select * .. and capture it in the spool file)
  3. The spool file that shows how you are calling the functions and output.

References:

https://www.oracletutorial.com/oracle-date-functions/

Date functions