Hi everyone! Could somebody please help me figure out how to solve this problem? I've been working on it for some time now and I cannot seem to find the answer.
With this file, run the script to insert the temperatures for Jan, Feb, and Mar
drop table weather;
create table weather(date_reading date, city varchar(20), temp_c number (3,1), constraint pk_citytemp primary key (date_reading, city));
insert into weather values ('01-JAN-2021', 'LIMA', 23.5);
insert into weather values ('02-JAN-2021', 'LIMA', 21.2);
insert into weather values ('03-JAN-2021', 'LIMA', 22.9);
insert into weather values ('04-JAN-2021', 'LIMA', 25.8);
insert into weather values ('05-JAN-2021', 'LIMA', 19.3);
insert into weather values ('01-JAN-2021', 'MIAMI', 16.2);
insert into weather values ('02-JAN-2021', 'MIAMI', 15.4);
insert into weather values ('03-JAN-2021', 'MIAMI', 16.1);
insert into weather values ('04-JAN-2021', 'MIAMI', 18.4);
insert into weather values ('05-JAN-2021', 'MIAMI', 14.5);
insert into weather values ('01-JAN-2021', 'TORONTO', -3.2);
insert into weather values ('02-JAN-2021', 'TORONTO', -5.9);
insert into weather values ('03-JAN-2021', 'TORONTO', -1.2);
insert into weather values ('04-JAN-2021', 'TORONTO', -0.1);
insert into weather values ('05-JAN-2021', 'TORONTO', -0.2);
insert into weather values ('01-FEB-2021', 'LIMA', 22.5);
insert into weather values ('02-FEB-2021', 'LIMA', 20.2);
insert into weather values ('03-FEB-2021', 'LIMA', 19.9);
insert into weather values ('04-FEB-2021', 'LIMA', 25.2);
insert into weather values ('05-FEB-2021', 'LIMA', 14.3);
insert into weather values ('01-FEB-2021', 'MIAMI', 18.2);
insert into weather values ('02-FEB-2021', 'MIAMI', 17.4);
insert into weather values ('03-FEB-2021', 'MIAMI', 18.1);
insert into weather values ('04-FEB-2021', 'MIAMI', 19.4);
insert into weather values ('05-FEB-2021', 'MIAMI', 13.5);
insert into weather values ('01-FEB-2021', 'TORONTO', -5.2);
insert into weather values ('02-FEB-2021', 'TORONTO', -6.6);
insert into weather values ('03-FEB-2021', 'TORONTO', -1.6);
insert into weather values ('04-FEB-2021', 'TORONTO', -13.2);
insert into weather values ('05-FEB-2021', 'TORONTO', -13.8);
insert into weather values ('01-MAR-2021', 'LIMA', 22.5);
insert into weather values ('02-MAR-2021', 'LIMA', 20.2);
insert into weather values ('03-MAR-2021', 'LIMA', 19.9);
insert into weather values ('04-MAR-2021', 'LIMA', 25.2);
insert into weather values ('05-MAR-2021', 'LIMA', 14.3);
insert into weather values ('01-MAR-2021', 'MIAMI', 13.2);
insert into weather values ('02-MAR-2021', 'MIAMI', 16.4);
insert into weather values ('03-MAR-2021', 'MIAMI', 14.1);
insert into weather values ('04-MAR-2021', 'MIAMI', 19.4);
insert into weather values ('05-MAR-2021', 'MIAMI', 12.5);
insert into weather values ('01-MAR-2021', 'TORONTO', -5.2);
insert into weather values ('02-MAR-2021', 'TORONTO', -6.6);
insert into weather values ('03-MAR-2021', 'TORONTO', -10.6);
insert into weather values ('04-MAR-2021', 'TORONTO', -13.2);
insert into weather values ('05-MAR-2021', 'TORONTO', -13.8);
Task: Produce a table showing the average temperature by month and city for the days available (first 5 days of each month), and order it chronologically for each city. The column headings and month names should be in Spanish.
MONTH - MES
CITY - CIUDAD
AVG_TEMP - TEMP_MED
HINT: We only have the dates by day, so we need to extract the month from the date. For this, you can use the built-in EXTRACT function to return a number representing the month of the year.
extract(month from date_reading)
To convert this to a readable date in Spanish:
to_char(to_date(extract(month from date_reading), 'MM'), 'Month', 'NLS_DATE_LANGUAGE = spanish')
Submit your code and table screenshot.