ex12.sql

INSERT INTO DateRep(dateRep, day, month, year)
SELECT DISTINCT RawData.dateRep, RawData.day, RawData.month, RawData.year FROM RawData;

CREATE TEMPORARY TABLE CountryFull(
    countryId INTEGER PRIMARY KEY AUTOINCREMENT,
   	countriesAndTerritories VARCHAR(30),
    geoId VARCHAR(30),
    countryterritoryCode CHAR(3),
	popData2018 INTEGER,
	continentExp VARCHAR(20)
);

INSERT INTO CountryFull(countriesAndTerritories, geoId, countryterritoryCode,popData2018,continentExp)
SELECT DISTINCT countriesAndTerritories, geoId, countryterritoryCode,popData2018,continentExp FROM RawData;

INSERT INTO Country(countryId, countryterritoryCode, popData2018, continentExp)
SELECT countryId, countryterritoryCode, popData2018, continentExp FROM CountryFull;

INSERT INTO CountryName(countryId, countriesAndTerritories)
SELECT countryId, countriesAndTerritories FROM CountryFull;

INSERT INTO GeoId(countryId, geoId)
SELECT countryId, geoId FROM CountryFull;

DROP TABLE CountryFull;

INSERT INTO Report(dateRep, countryId, cases, deaths)
SELECT dateRep, countryId, cases, deaths FROM RawData
NATURAL JOIN GeoId