René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

Setting up an example table to demonstrate analytical functions in oracle

Use the following scriptlet to create a table named TEMPERATURES and insert some test data into it. It also creates a view named MONTHLY_AVG_TEMPERATURES. 
alter session set nls_date_format='dd/mm/yyyy'; 

DROP TABLE TEMPERATURES;

CREATE TABLE TEMPERATURES (
  D DATE,
  V NUMBER(3,1)
);

INSERT INTO TEMPERATURES VALUES ('15/01/1998', -1.5);
INSERT INTO TEMPERATURES VALUES ('15/02/1998', -1.8);
INSERT INTO TEMPERATURES VALUES ('15/03/1998',  2.5);
INSERT INTO TEMPERATURES VALUES ('15/04/1998',  4.7);
INSERT INTO TEMPERATURES VALUES ('14/05/1998',  4.9);
INSERT INTO TEMPERATURES VALUES ('15/06/1998',  6.8);
INSERT INTO TEMPERATURES VALUES ('17/06/1998',  6.6);
INSERT INTO TEMPERATURES VALUES ('15/07/1998', 10.9);
INSERT INTO TEMPERATURES VALUES ('15/08/1998', 17.8);
INSERT INTO TEMPERATURES VALUES ('15/09/1998', 12.5);
INSERT INTO TEMPERATURES VALUES ('15/10/1998',  8.1);
INSERT INTO TEMPERATURES VALUES ('15/11/1998',  4.4);
INSERT INTO TEMPERATURES VALUES ('15/12/1998',  2.1);

INSERT INTO TEMPERATURES VALUES ('15/01/1999', -1.4);
INSERT INTO TEMPERATURES VALUES ('15/02/1999', -1.9);
INSERT INTO TEMPERATURES VALUES ('15/03/1999',  2.6);
INSERT INTO TEMPERATURES VALUES ('19/04/1999',  4.8);
INSERT INTO TEMPERATURES VALUES ('15/05/1999',  5.2);
INSERT INTO TEMPERATURES VALUES ('15/06/1999',  8.1);
INSERT INTO TEMPERATURES VALUES ('15/07/1999', 11.9);
INSERT INTO TEMPERATURES VALUES ('15/08/1999', 16.4);
INSERT INTO TEMPERATURES VALUES ('15/09/1999', 13.2);
INSERT INTO TEMPERATURES VALUES ('15/10/1999',  9.2);
INSERT INTO TEMPERATURES VALUES ('15/11/1999',  5.6);
INSERT INTO TEMPERATURES VALUES ('15/12/1999',  4.2);

INSERT INTO TEMPERATURES VALUES ('15/01/2000', -1.7);
INSERT INTO TEMPERATURES VALUES ('15/02/2000', -2.0);
INSERT INTO TEMPERATURES VALUES ('15/03/2000',  3.1);
INSERT INTO TEMPERATURES VALUES ('15/04/2000',  5.3);
INSERT INTO TEMPERATURES VALUES ('15/05/2000',  5.4);
INSERT INTO TEMPERATURES VALUES ('22/06/2000',  6.8);
INSERT INTO TEMPERATURES VALUES ('15/07/2000', 10.9);
INSERT INTO TEMPERATURES VALUES ('15/08/2000', 17.2);
INSERT INTO TEMPERATURES VALUES ('15/09/2000', 13.1);
INSERT INTO TEMPERATURES VALUES ('15/10/2000',  8.9);
INSERT INTO TEMPERATURES VALUES ('15/11/2000',  5.2);
INSERT INTO TEMPERATURES VALUES ('15/12/2000',  2.0);


CREATE OR REPLACE VIEW MONTHLY_AVG_TEMPERATURES AS 
  SELECT 
    avg(v)                 as AVG_TEMPERATURE, 
    to_char (d, 'YYYY/MM') as YEAR_MONTH FROM TEMPERATURES 
  GROUP BY TO_CHAR(d, 'YYYY/MM');