Creating procedure to measure statistics from other table

Question

I have university task which gives me trouble, I will walk you through basic information so that you can understand my problem.

First of all I have table:

CREATE TABLE Marriage (
       Id_Marriage        INTEGER,
       Marriage_date      DATE,
       Date_of_expiration DATE,
       Reason_of_expiration VARCHAR2(80)    
);

Data in this table looks like in example:

ID  Marriage_date  Date_of_expiration  Reason_of_expiration
1   39/04/12       94/11/16            Death 

Then I have to create second table which would be:

CREATE TABLE Statistics (
       Year                INTEGER, --(or date perhaps?)
       Number_of_marriages INTEGER,
       Number_of_divorces  INTEGER
);

And my job is to create procedure which would fill this statistics table with information. So there has to be number of marriages and divorces (or marriages ended) for each year in table marriages. Optionally it can also print data on screen as well as insert rows into marriages.

I have failed to come up with idea on how to get started. I guess I will need some sort of cursor and I know that I can extract year from date by doing something like this:

year := SUBSTR (marriage_Date, 1, 2);

What can I try next?

Answer 1

Reading your question I feel the biggest flaw is that there is no need to create a procedure, in fact, there is no need to create a table itself! I wonder what sort of exercise this is, where you are taught of everything else except the basics of database design, including normalization. Real life examples are nowhere similar to this.

As for your question, as nobody cares about the details of execution, the following code would suffice:

CREATE OR REPLACE PROCEDURE calculateStatistics
IS
BEGIN
    DELETE FROM statistics;
    INSERT INTO statistics
         SELECT to_number(to_char(marriage_date, 'YYYY')) AS YEAR,
                count(id_marriage) as number_of_marriages,
                sum(CASE UPPER(reason_of_expiration)
                         WHEN  = 'DIVORCE' THEN 1
                         ELSE 0
                    END) number_of_divorces
         FROM   marriage
         GROUP BY to_number(to_char(marriage_date, 'YYYY'));
END;
/