Question

1. Create a function that will accept a date as input and will return if this date happens in fall, spring, winter or summer. Before you start writing this function experiment with queries below.

Current date

select sysdate from dual;

Yesterday

select sysdate - 1 from dual;

Tomorrow

select sysdate + 1 from dual;

The date format element D returns the number of the day of the week (1-7).

select to_char(sysdate,'D') from dual;

MM returns two-digit numeric abbreviation of month (01-12; JAN = 01)

select to_char(sysdate,'MM') from dual;

Let us for simplicity use winter December-February, spring March – May, summer June-August, and fall September – November

SYSDATE 21-MAY-19 SYSDATE-1 20-MAY-19 SYSDATE+1 22-MAY-19 TO 05 Ln Worksheet Query Builder SELECT season ('29-JAN-19') FROM DUAL SE LECT season ('11-APR-19') FROM DUAL; Query Result Script Output x Task completed in 0.047 seconds SEASON ('29-JAN-19') 29-JAN-19 is winter SEASON ('11-APR-19') 11-APR-19 is spring

Answer 1

Below is the script that wil create a function names SEASON as per the requirement. It is well explained inside the script using comments.

-- Creates or replaces function SEASON
-- It expectsdate as input
CREATE OR REPLACE FUNCTION SEASON(inputDate DATE)
   RETURN VARCHAR2
   IS
    mm INT; -- declarevarible to store month
   BEGIN
    -- extract month from the given date
    SELECT CAST(TO_CHAR(inputDate,'MM')AS INT) INTO mm FROM DUAL;
  
    -- find season using month, and return the message with season
    IF mm BETWEEN 3 AND 5
    THEN
        RETURN TO_CHAR(inputDate, 'DD-MON-YY') ||' is spring';
    ELSIF mm BETWEEN 6 AND 8
    THEN
        RETURN TO_CHAR(inputDate, 'DD-MON-YY') ||' is summer';
    ELSIF mm BETWEEN 9 AND 11
    THEN
        RETURN TO_CHAR(inputDate, 'DD-MON-YY') ||' is fall';
    ELSE
        RETURN TO_CHAR(inputDate, 'DD-MON-YY') ||' is winter';
    END IF;
    
    END SEASON;
/

Below screenshot displays that it ran successfully:

Creates or replaces function SEASON It expects date as input ECREATE OR REPLACE FUNCTION SEASON (inputDate DATE) RETURN VARCHAR2 IS mm INT: declare varible to store month _ BEGIN extract month from the given date SELECT CAST (TO CHAR (input Date, 'MM)AS INT) INTO mm FROM DUAL; find season using month, and return the message with season _ IF mm BETWEEN 3 AND 5 THEN RETURN TO CH is spring'; (inputDate, 'DD-MON-YY') || ELSIF mm BETWEEN 6 AND 8 THEN RETURN TO CHAR (inputDate, 'DD-MON-YY') |' is summer'; ELSIF mm BETWEEN 9 AND 11 THEN RETURN TO CHAR (inputDate, 'DD-MON-YY') ||' is fall'; ELSE RETURN TO CHAR (inputDate, 'DD-MON-YY) II' is winter'; END IF; END SEASON: Script Output x Task completed in 0.072 seconds Function SEASON compiled п

Below are the sample outputs of function run:

SELECT SEASON ('29-JAN-19') FROM DUAL; Query Result x All Rows Fetched: 1 in 0.004 seconds E sQL SEASON(29-JAN-19) 1 29-JAN-19 is winter

SELECT SEASON '11-APR-19') FROM DUAL; Query Result sQL | All Rows Fetched: 1 in 0.006 seconds * 2 h SEASON(11-APR-19) 1 11-APR-19 is spring

SELECT SEASON ('10-AUG-19') FROM DUAL; Query Resultx SQL| All Rows Fetched: 1 in 0.01 seconds SEASON(10-AUG-19) 1 10-AUG-19 is summer

This completes the requirement. Let me know if you have any queries.

Thanks!