{"id":772,"date":"2023-03-15T17:19:31","date_gmt":"2023-03-15T17:19:31","guid":{"rendered":"https:\/\/jirivalasek.com\/?page_id=772"},"modified":"2023-04-22T09:54:46","modified_gmt":"2023-04-22T09:54:46","slug":"sql-en","status":"publish","type":"page","link":"https:\/\/jirivalasek.com\/?page_id=772&lang=en","title":{"rendered":"SQL-en"},"content":{"rendered":"\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t
FACTORS INFLUENCING THE SPREAD OF CORONAVIRUS<\/strong><\/h5>

Preparation of data to determine factors influencing the spread of coronavirus at the national level.<\/p>

Technologies used: DBeaver, SQL, Tableau.<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t

The resulting data will be panel data, keys will be state and day.<\/p>

In addition to daily increases in infections, the number of tests performed and the population of a given state must be matched to determine the main drivers of coronavirus spread. These three variables can then be used to create a suitable explanatory variable. The daily numbers of infections will be further explained by variables of several types. Each column in the table will represent one variable. We want to obtain the following columns:<\/p>

  1. Time variables:<\/li><\/ol>
    • Binary variable for weekend\/workday,<\/li>
    • the season of the day (coded as 0 to 3).<\/li><\/ul>

      \u00a0 \u00a0 \u00a0<\/p>

      \u00a0 \u00a0 \u00a02. State-specific variables:<\/p>

      • Population density – in states with higher population density, the disease may spread faster,<\/li>
      • GDP per capita – to be used as an indicator of the economic maturity of the state,<\/li>
      • GINI coefficient – does wealth inequality affect the spread of coronavirus?<\/li>
      • infant mortality – use as an indicator of the quality of health care,<\/li>
      • median age of the population in 2018 – states with older populations may be more affected,<\/li>
      • proportions of different religions – use as a proxy variable for cultural specificity. For each religion in a given state, the percentage of its adherents in the total population,<\/li>
      • difference between life expectancy in 1965 and in 2015 – countries that have experienced rapid development may respond differently than countries that have been developed for a longer period of time.<\/li><\/ul>

        \u00a0<\/p>

        \u00a0 \u00a0 \u00a03. Weather (affects people’s behaviour and also the ability to spread the virus):<\/p>

        • Average daytime (not nighttime!) temperature,<\/li>
        • number of hours in a given day with non-zero rainfall,<\/li>
        • maximum wind gusts during the day.<\/li><\/ul>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t
          \n\t\t\t\t\t\t
          \n\t\t\t\t\t
          \n\t\t\t
          \n\t\t\t\t\t\t
          \n\t\t\t\t\t\t
          \n\t\t\t\t\t
          \n\t\t\t
          \n\t\t\t\t\t\t
          \n\t\t\t\t
          \n\t\t\t\t\t\t\t\t\t

          RESULTS<\/strong><\/h1>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t
          \n\t\t\t\t\t\t
          \n\t\t\t\t\t
          \n\t\t\t
          \n\t\t\t\t\t\t
          \n\t\t\t\t
          \n\t\t\t\t\t\t\t\t\t

          Sample of the resulting panel data.<\/p>

          The resulting table contains the data in each country valid on that day.<\/p>

          The table has 94 142 rows.<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

          \n\t\t\t\t\t\t
          \n\t\t\t\t\t
          \n\t\t\t
          \n\t\t\t\t\t\t
          \n\t\t\t\t
          \n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\"\"\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t
          \n\t\t\t\t\t\t
          \n\t\t\t\t\t
          \n\t\t\t
          \n\t\t\t\t\t\t
          \n\t\t\t\t
          \n\t\t\t\t\t\t\t\t\t

          One possible visualisation of the spread of coronavirus in European countries.<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t

          \n\t\t\t\t\t\t
          \n\t\t\t\t\t
          \n\t\t\t
          \n\t\t\t\t\t\t
          \n\t\t\t\t
          \n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\"\"\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t
          \n\t\t\t\t\t\t
          \n\t\t\t\t\t
          \n\t\t\t
          \n\t\t\t\t\t\t
          \n\t\t\t\t\t\t
          \n\t\t\t\t\t
          \n\t\t\t
          \n\t\t\t\t\t\t
          \n\t\t\t\t
          \n\t\t\t\t\t\t\t\t\t

          PROCEDURE<\/strong><\/h1>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t
          \n\t\t\t\t\t\t
          \n\t\t\t\t\t
          \n\t\t\t
          \n\t\t\t\t\t\t
          \n\t\t\t\t
          \n\t\t\t\t\t\t\t\t\t
          # Zaklad dat budou tabulky covid19_basic_differences a covid19_tests.<\/em>
          # Seznam zemi v covid19_basic_differences se musi shodovat se seznamem z covid19_tests.<\/em>
          # Nektere nazvy zemi se v tabulkach lisi.<\/em><\/h6>
          # Zeme co jsou v covid tests, ale ne v differences:<\/em><\/h6>

          create view v_zeme_v_tests_ale_ne_v_diff as
          select ct.country
          from covid19_tests ct
          except
          select cbd.country
          from covid19_basic_differences cbd ;<\/p>

          # Zeme co jsou v differences, ale ne v tests:<\/h6>


          create view v_zeme_v_diff_ale_ne_v_tests as
          select cbd.country
          from covid19_basic_differences cbd
          except
          select ct.country
          from covid19_tests ct ;<\/p>

          # Tyto zeme prejmenovat v tabulce tests:<\/h6>


          —- Czech republic = Czechia
          —- Myanamar = Burma
          —- South Korea = Korea, South
          —- Taiwan= Taiwan*
          —- United States = US<\/p>

          create table t_covid_tests_uprava_zemi as
          select * from covid19_tests ct ;<\/p>

          update t_covid_tests_uprava_zemi set country = ‘Czechia’ where ISO = ‘CZE’;
          update t_covid_tests_uprava_zemi set country = ‘Burma’ where ISO = ‘MMR’;
          update t_covid_tests_uprava_zemi set country = ‘Korea, South’ where ISO = ‘KOR’;
          update t_covid_tests_uprava_zemi set country = ‘Taiwan*’ where ISO = ‘TWN’;
          update t_covid_tests_uprava_zemi set country = ‘US’ where ISO = ‘USA’;<\/p>

          #Vytvor tabulku, kde bude zaznam o potvrzenych pripadech a testech. Ke vsem zaznamenavanym dnum potvrzenych pripadu nejsou zaznamy o testech:<\/h6>

          \u00a0<\/p>

          create table t_covid_confirmed_tests as
          select
          cbd.country , cbd.`date` , cbd.confirmed , tctuz.tests_performed
          from covid19_basic_differences cbd
          left join t_covid_tests_uprava_zemi tctuz
          on cbd.country = tctuz.country and cbd.`date` = tctuz.`date`
          order by country asc;<\/p>

          # Tabulka s poslednim dostupnym udajem o detske umrtnosti:<\/h6>


          create table t_mort5 as
          select
          country , mortaliy_under5
          from economies e
          where mortaliy_under5 is not null
          group by country
          order by country asc;<\/p>

          # Udaje o HDP na obyvatele chci za 2020, nebo 2019. Starsi nechci.
          # HDP na obyvatele za 2020:<\/h6>


          create table t_gdp_per_capita
          select
          country ,
          GDP ,
          round (gdp \/ population, 2) as gdp_per_capita
          from economies e
          where `year` = ‘2020’
          order by country asc;<\/p>

          # HPD na obyvatele za rok 2019 u zemi, kde chybi udaj za rok 2020:<\/h6>


          create table t_gdp_per_capita_2019
          select
          z.country ,
          e.GDP ,
          round (e.gdp \/ e.population, 2) as gdp_per_capita
          from economies e
          join
          (select country
          from economies e2
          where `year`= ‘2020’ and gdp is null) as z
          on z.country = e.country
          where e.`year` = ‘2019’ and e.GDP is not null;<\/p>

          # Ted updatuju tabulku t_gdp_per_capita tam, kde chybi udaje za 2020 a doplnim zde udaj za 2019:<\/h6>


          update t_gdp_per_capita as base
          inner join t_gdp_per_capita_2019 as a
          on base.country = a.country
          set base.gdp_per_capita = a.gdp_per_capita
          where base.gdp_per_capita is null
          and a.gdp_per_capita is not null;<\/p>

          # GINI: Nejmladsi dostupna hodnota GINI, ale zaroven ne starsi, nez z roku 2010:<\/h6>


          create table t_gini as
          select
          country ,
          `year` ,
          gini as GINI
          from economies e
          where year >= 2010 and gini is not null
          group by country
          order by country asc;<\/p>

          # Population density vypocitam. V tabulce countries se hodnota population_density u nekterych zemi lisi od vypoctene hodnoty z population\/surface_area:<\/h6>

          # Vypocitavam jen z udaju dostupnych:<\/h6>


          create table t_pop_density
          select
          country ,
          round (population \/ surface_area,4) as population_density
          from countries c
          where population != 0 and surface_area != 0
          order by country asc;<\/p>

          # Podily nabozenstvi. Pocitam podil populace na nabozenstvi \/ suma populace dle zeme. Vse pocitam z tabulky religions:<\/h6>


          create table t_religion_share as
          select
          base.country,
          base.religion,
          base.population,
          a.total_population,
          round ((base.population\/a.total_population)*100,2) as perc_share_on_total_population
          from
          (select country , religion , population
          from religions r
          where `year` = 2020) as base
          join
          (select country, sum (population) as total_population
          from religions r
          where `year` = 2020
          group by country) as a
          on base.country = a.country;<\/p>

          # Tabulka rozdil v ocekavane delce doziti v roce 1965 a 2015:<\/h6>


          create table t_life_expectancy_diff as
          select
          le15.country,
          le15.life_expectancy_2015,
          le65.life_expectancy_1965,
          round (le15.life_expectancy_2015-le65.life_expectancy_1965, 2) as life_expectancy_diff
          from
          (select country, life_expectancy as life_expectancy_2015
          from life_expectancy le
          where `year` = 2015) as le15
          join
          (select country, life_expectancy as life_expectancy_1965
          from life_expectancy le
          where `year` = 1965) as le65
          on le15.country = le65.country
          order by country ;<\/p>

          # Do tabulky weather pridam sloupec country:<\/h6>


          create table t_weather as
          select
          zeme.country,
          base.*
          from
          (select *
          from weather w ) as base
          left join
          (select country, capital_city
          from countries) as zeme
          on base.city = zeme.capital_city ;<\/p>

          # Tabulku t_weather jsem updatoval o nazvy zemi tam kde chybely:<\/h6>


          update t_weather set country = ‘Greece’ where city = ‘Athens’;
          update t_weather set country = ‘Belgium’ where city = ‘Brussels’;
          update t_weather set country = ‘Romania’ where city = ‘Bucharest’;
          update t_weather set country = ‘Finland’ where city = ‘Helsinki’;
          update t_weather set country = ‘Ukraine’ where city = ‘Kiev’;
          update t_weather set country = ‘Portugal’ where city = ‘Lisbon’;
          update t_weather set country = ‘Luxembourg’ where city = ‘Luxembourg’;
          update t_weather set country = ‘Czechia’ where city = ‘Prague’;
          update t_weather set country = ‘Italy’ where city = ‘Rome’;
          update t_weather set country = ‘Austria’ where city = ‘Vienna’;
          update t_weather set country = ‘Poland’ where city = ‘Warsaw’;<\/p>

          # Upravil jsem v tabulce t_weather nazev Ruska aby se shodoval s tabulkou t_covid_confirmed_tests na kterou budu napojovat:<\/h6>


          update t_weather set country = ‘Russian Federation’ where city = ‘Moscow’;<\/p>

          # Tabulka s prumernou denni teplotou:<\/h6>


          create table t_avg_temp as
          select
          *,
          avg (cast (trim (trim (trailing ‘\u00b0c’ from temp))as float)) as avg_temp
          from t_weather tw
          where `time` between ’06:00′ and ’18:00′
          and country is not null
          group by country, `date`;<\/p>

          # Pocet hodin srazek behem dne:<\/h6>


          create table t_srazky as
          select
          *,
          count (rain2) as pocet_zaznamu_srazek, (count (rain2))*3 as Rain_hours
          from
          (select *,cast (trim (trim (trailing ‘mm’ from rain))as float) as rain2
          from t_weather tw
          where country is not null) as base
          where rain2 > 0
          group by country, `date`;<\/p>

          # Maximalni sila vetru v narazech behem dne:<\/h6>


          create table t_max_gusty_wind as
          select
          *,
          max (cast (trim (trim (trailing ‘km\/h’ from gust))as int)) as max_gusty_wind
          from t_weather tw
          where `time` between ’06:00′ and ’18:00′
          and country is not null
          group by country,`date` ;<\/p>

          # Tabulku t_covid_confirmed_tests rozsirim o casove promenne, ktere budou nasledovat za sloupcem date:<\/h6>


          create table t_covid_confirmed_tests_cas as
          select
          country ,
          `date` ,
          case
          when dayofweek(`date`) IN (1,7) then ‘YES’
          else ‘NO’
          end as Weekend,
          case when date_format(`date`, ‘%m %d’) BETWEEN (’03 20′) AND (’06 20′) then 0
          when date_format(`date`, ‘%m %d’) BETWEEN (’06 21′) AND (’09 21′) then 1
          when date_format(`date`, ‘%m %d’) BETWEEN (’09 22′) AND (’12 20′) then 2
          else 3
          end as Season,
          confirmed ,
          tests_performed
          from t_covid_confirmed_tests tcct ;<\/p>

          # Uprava tabulek vychazejicich z economies, aby se nazvy zemi shodovaly se zakladni tabulkou:<\/h6>

          t_covid_confirmed_tests_cas.
          update t_mort5 set country = ‘Brunei’ where country = ‘Brunei Darussalam’;
          update t_mort5 set country = ‘Czechia’ where country = ‘Czech Republic’;
          update t_mort5 set country = ‘Burma’ where country = ‘Myanmar’;
          update t_mort5 set country = ‘Russia’ where country = ‘Russian Federation’;
          update t_mort5 set country = ‘Korea, South’ where country =’South Korea’;
          update t_mort5 set country = ‘Saint Kitts and Nevis’ where country = ‘St. Kitts and Nevis’;
          update t_mort5 set country = ‘Saint Lucia’ where country = ‘St. Lucia’;
          update t_mort5 set country = ‘Saint Vincent and the Grenadines’ where country = ‘St. Vincent and the Grenadines’;
          update t_mort5 set country = ‘Congo (Kinshasa)’ where country = ‘The Democratic Republic of Congo’;
          update t_mort5 set country = ‘Congo (Brazzaville)’ where country = ‘Congo’;
          update t_mort5 set country = ‘US’ where country = ‘United States’;<\/p>

          update t_gdp_per_capita set country = ‘Brunei’ where country = ‘Brunei Darussalam’;
          update t_gdp_per_capita set country = ‘Czechia’ where country = ‘Czech Republic’;
          update t_gdp_per_capita set country = ‘Burma’ where country = ‘Myanmar’;
          update t_gdp_per_capita set country = ‘Russia’ where country = ‘Russian Federation’;
          update t_gdp_per_capita set country = ‘Korea, South’ where country =’South Korea’;
          update t_gdp_per_capita set country = ‘Saint Kitts and Nevis’ where country = ‘St. Kitts and Nevis’;
          update t_gdp_per_capita set country = ‘Saint Lucia’ where country = ‘St. Lucia’;
          update t_gdp_per_capita set country = ‘Saint Vincent and the Grenadines’ where country = ‘St. Vincent and the Grenadines’;
          update t_gdp_per_capita set country = ‘Congo (Kinshasa)’ where country = ‘The Democratic Republic of Congo’;
          update t_gdp_per_capita set country = ‘Congo (Brazzaville)’ where country = ‘Congo’;
          update t_gdp_per_capita set country = ‘US’ where country = ‘United States’;<\/p>

          update t_gini set country = ‘Czechia’ where country = ‘Czech Republic’;
          update t_gini set country = ‘Burma’ where country = ‘Myanmar’;
          update t_gini set country = ‘Russia’ where country = ‘Russian Federation’;
          update t_gini set country = ‘Korea, South’ where country =’South Korea’;
          update t_gini set country = ‘Saint Lucia’ where country = ‘St. Lucia’;
          update t_gini set country = ‘Congo (Kinshasa)’ where country = ‘The Democratic Republic of Congo’;
          update t_gini set country = ‘Congo (Brazzaville)’ where country = ‘Congo’;
          update t_gini set country = ‘US’ where country = ‘United States’;<\/p>

          # Uprava tabulky t_pop_density, aby se nazvy zemi shodovaly se zakladni tabulkou:<\/h6>


          update t_pop_density set country = ‘US’ where country = ‘United States’;
          update t_pop_density set country = ‘Korea, South’ where country = ‘South Korea’;
          update t_pop_density set country = ‘Burma’ where country = ‘Myanmar’;
          update t_pop_density set country = ‘Czechia’ where country = ‘Czech Republic’;
          update t_pop_density set country = ‘Congo (Kinshasa)’ where country = ‘Congo’;<\/p>

          # Uprava nazvu zemi v tabulce t_religion_share:<\/h6>


          update t_religion_share set country = ‘Czechia’ where country = ‘Czech Republic’;
          update t_religion_share set country = ‘Burma’ where country = ‘Myanmar’;
          update t_religion_share set country = ‘Russia’ where country = ‘Russian Federation’;
          update t_religion_share set country = ‘Korea, South’ where country =’South Korea’;
          update t_religion_share set country = ‘Saint Kitts and Nevis’ where country = ‘St. Kitts and Nevis’;
          update t_religion_share set country = ‘Saint Lucia’ where country = ‘St. Lucia’;
          update t_religion_share set country = ‘Saint Vincent and the Grenadines’ where country = ‘St. Vincent and the Grenadines’;
          update t_religion_share set country = ‘Congo (Kinshasa)’ where country = ‘The Democratic Republic of Congo’;
          update t_religion_share set country = ‘Congo (Brazzaville)’ where country = ‘Congo’;
          update t_religion_share set country = ‘US’ where country = ‘United States’;<\/p>

          # Uprava nazvu Taiwan v tabulce t_religion_share:<\/h6>


          update t_religion_share set country = ‘Taiwan*’ where country = ‘Taiwan’;<\/p>

          # Uprava zemi v t_life_expectancy_diff:<\/h6>


          update t_life_expectancy_diff set country = ‘Czechia’ where country = ‘Czech Republic’;
          update t_life_expectancy_diff set country = ‘Burma’ where country = ‘Myanmar’;
          update t_life_expectancy_diff set country = ‘Russia’ where country = ‘Russian Federation’;
          update t_life_expectancy_diff set country = ‘Korea, South’ where country =’South Korea’;
          update t_life_expectancy_diff set country = ‘Congo (Kinshasa)’ where country = ‘The Democratic Republic of Congo’;
          update t_life_expectancy_diff set country = ‘Congo (Brazzaville)’ where country = ‘Congo’;
          update t_life_expectancy_diff set country = ‘US’ where country = ‘United States’;
          update t_life_expectancy_diff set country = ‘Taiwan*’ where country = ‘Taiwan’;<\/p>

          # Uprava zemi v tabulkach vychazejicich z t_weather:<\/h6>


          update t_avg_temp set country = ‘Russia’ where country = ‘Russian Federation’;
          update t_srazky set country = ‘Russia’ where country = ‘Russian Federation’;
          update t_max_gusty_wind set country = ‘Russia’ where country = ‘Russian Federation’;<\/p>

          # Tabulky pro nabozenstvi:<\/h6>


          create table t_christianity as
          select country , religion, perc_share_on_total_population as Christianity
          from t_religion_share trs2 where religion = ‘Christianity’;<\/p>

          create table t_islam as
          select country , religion, perc_share_on_total_population as Islam
          from t_religion_share trs2 where religion = ‘Islam’;<\/p>

          create table t_Unaffiliated_Religions as
          select country , religion, perc_share_on_total_population as Unaffiliated_Religions
          from t_religion_share trs2 where religion = ‘Unaffiliated Religions’;<\/p>

          create table t_hinduism as
          select country , religion, perc_share_on_total_population as Hinduism
          from t_religion_share trs2 where religion = ‘Hinduism’;<\/p>

          create table t_buddhism as
          select country , religion, perc_share_on_total_population as Buddhism
          from t_religion_share trs2 where religion = ‘Buddhism’;<\/p>

          create table t_Folk_Religions as
          select country , religion, perc_share_on_total_population as Folk_Religions
          from t_religion_share trs2 where religion = ‘Folk Religions’;<\/p>

          create table t_Other_Religions as
          select country , religion, perc_share_on_total_population as Other_Religions
          from t_religion_share trs2 where religion = ‘Other Religions’;<\/p>

          create table t_Judaism as
          select country , religion, perc_share_on_total_population as Judaism
          from t_religion_share trs2 where religion = ‘Judaism’;<\/p>

          # Vytvoreni tabulky s udaji o celkove populaci a uprava nazvu zemi:<\/h6>


          create table t_population as
          select country ,population
          from countries;<\/p>

          update t_population set country = ‘US’ where country = ‘United States’;
          update t_population set country = ‘Korea, South’ where country = ‘South Korea’;
          update t_population set country = ‘Burma’ where country = ‘Myanmar’;
          update t_population set country = ‘Czechia’ where country = ‘Czech Republic’;
          update t_population set country = ‘Congo (Kinshasa)’ where country = ‘Congo’;<\/p>

          # Kontroloval jsem chybejici udaje ve finalni tabulce a nasel chyby:<\/h6>

          \u00a0<\/p>

          update t_population set country = “Cote d’Ivoire” where country = ‘Ivory Coast’;
          update t_population set country = ‘Fiji’ where country = ‘Fiji Islands’;
          update t_population set country = ‘Holy See’ where country = ‘Holy See (Vatican City State)’;
          update t_population set country = ‘Libya’ where country = ‘Libyan Arab Jamahiriya’;
          update t_population set country = ‘Micronesia’ where country = ‘Micronesia, Federated States of’;
          update t_population set country = ‘Russia’ where country = ‘Russian Federation’;
          update t_population set country = ‘Timor-Leste’ where country = ‘East Timor’;<\/p>

          update t_pop_density set country = “Cote d’Ivoire” where country = ‘Ivory Coast’;
          update t_pop_density set country = ‘Fiji’ where country = ‘Fiji Islands’;
          update t_pop_density set country = ‘Holy See’ where country = ‘Holy See (Vatican City State)’;
          update t_pop_density set country = ‘Libya’ where country = ‘Libyan Arab Jamahiriya’;
          update t_pop_density set country = ‘Micronesia’ where country = ‘Micronesia, Federated States of’;
          update t_pop_density set country = ‘Russia’ where country = ‘Russian Federation’;
          update t_pop_density set country = ‘Timor-Leste’ where country = ‘East Timor’;<\/p>

          update t_mort5 set country = “Cote d’Ivoire” where country = ‘Ivory Coast’;
          update t_gdp_per_capita set country = “Cote d’Ivoire” where country = ‘Ivory Coast’;
          update t_gini set country = “Cote d’Ivoire” where country = ‘Ivory Coast’;<\/p>

          # Jeste upravy chyb v primarnich datech:<\/h6>


          update t_life_expectancy_diff set country = “Cote d’Ivoire” where country = ‘Ivory Coast’;
          update t_life_expectancy_diff set country = ‘Micronesia’ where country = ‘Micronesia (country)’;
          update t_life_expectancy_diff set country = ‘Timor-Leste’ where country = ‘Timor’;<\/p>

          update t_religion_share set country = “Cote d’Ivoire” where country = ‘Ivory Coast’;<\/p>

          # Tabulka finale:<\/h6>


          create table t_Jiri_Valasek_projekt_SQL_final as
          select
          tcctc.*,
          tp.population ,
          tpd.population_density ,
          tgpc.gdp_per_capita ,
          tg.GINI ,
          tm.mortaliy_under5 ,
          tled.life_expectancy_diff ,
          tc.Christianity ,
          ti.Islam ,
          tb.Buddhism ,
          th.Hinduism ,
          tj.Judaism ,
          tfr.Folk_Religions ,
          tur.Unaffiliated_Religions ,
          tor.Other_Religions ,
          tat.avg_temp ,
          ts.Rain_hours ,
          tmgw.max_gusty_wind
          from t_covid_confirmed_tests_cas tcctc
          left join t_population tp
          on tcctc.country = tp.country
          left join t_pop_density tpd
          on tcctc.country = tpd.country
          left join t_gdp_per_capita tgpc
          on tcctc.country = tgpc.country
          left join t_gini tg
          on tcctc.country = tg.country
          left join t_mort5 tm
          on tcctc.country = tm.country
          left join t_life_expectancy_diff tled
          on tcctc.country = tled.country
          left join t_christianity tc
          on tcctc.country = tc.country
          left join t_islam ti
          on tcctc.country = ti.country
          left join t_buddhism tb
          on tcctc.country = tb.country
          left join t_hinduism th
          on tcctc.country = th.country
          left join t_judaism tj
          on tcctc.country = tj.country
          left join t_folk_religions tfr
          on tcctc.country = tfr.country
          left join t_unaffiliated_religions tur
          on tcctc.country = tur.country
          left join t_other_religions tor
          on tcctc.country = tor.country
          left join t_avg_temp tat
          on tcctc.country = tat.country and tcctc.`date` = tat.`date`
          left join t_srazky ts
          on tcctc.country = ts.country and tcctc.`date` = ts.`date`
          left join t_max_gusty_wind tmgw
          on tcctc.country = tmgw.country and tcctc.`date` = tmgw.`date` ;<\/p>\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<\/div>\n\t\t","protected":false},"excerpt":{"rendered":"

          FACTORS INFLUENCING THE SPREAD OF CORONAVIRUS Preparation of data to determine factors influencing the spread of coronavirus at the national level. Technologies used: DBeaver, SQL, Tableau. The resulting data will be panel data, keys will be state and day. In addition to daily increases in infections, the number of tests performed and the population of […]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-772","page","type-page","status-publish","hentry"],"yoast_head":"\nSQL-en - DATASYNTHIQ - Ji\u0159\u00ed Val\u00e1\u0161ek<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/jirivalasek.com\/?page_id=772&lang=en\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL-en - DATASYNTHIQ - Ji\u0159\u00ed Val\u00e1\u0161ek\" \/>\n<meta property=\"og:description\" content=\"FACTORS INFLUENCING THE SPREAD OF CORONAVIRUS Preparation of data to determine factors influencing the spread of coronavirus at the national level. Technologies used: DBeaver, SQL, Tableau. The resulting data will be panel data, keys will be state and day. In addition to daily increases in infections, the number of tests performed and the population of […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/jirivalasek.com\/?page_id=772&lang=en\" \/>\n<meta property=\"og:site_name\" content=\"DATASYNTHIQ - Ji\u0159\u00ed Val\u00e1\u0161ek\" \/>\n<meta property=\"article:modified_time\" content=\"2023-04-22T09:54:46+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/jirivalasek.com\/wp-content\/uploads\/2023\/04\/panel_data-1024x588.png\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"13 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/jirivalasek.com\\\/?page_id=772&lang=en\",\"url\":\"https:\\\/\\\/jirivalasek.com\\\/?page_id=772&lang=en\",\"name\":\"SQL-en - DATASYNTHIQ - Ji\u0159\u00ed Val\u00e1\u0161ek\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/jirivalasek.com\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/jirivalasek.com\\\/?page_id=772&lang=en#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/jirivalasek.com\\\/?page_id=772&lang=en#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/jirivalasek.com\\\/wp-content\\\/uploads\\\/2023\\\/04\\\/panel_data-1024x588.png\",\"datePublished\":\"2023-03-15T17:19:31+00:00\",\"dateModified\":\"2023-04-22T09:54:46+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/jirivalasek.com\\\/?page_id=772&lang=en#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/jirivalasek.com\\\/?page_id=772&lang=en\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/jirivalasek.com\\\/?page_id=772&lang=en#primaryimage\",\"url\":\"https:\\\/\\\/i0.wp.com\\\/jirivalasek.com\\\/wp-content\\\/uploads\\\/2023\\\/04\\\/panel_data.png?fit=1476%2C847&ssl=1\",\"contentUrl\":\"https:\\\/\\\/i0.wp.com\\\/jirivalasek.com\\\/wp-content\\\/uploads\\\/2023\\\/04\\\/panel_data.png?fit=1476%2C847&ssl=1\",\"width\":1476,\"height\":847},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/jirivalasek.com\\\/?page_id=772&lang=en#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/jirivalasek.com\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL-en\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/jirivalasek.com\\\/#website\",\"url\":\"https:\\\/\\\/jirivalasek.com\\\/\",\"name\":\"DATASYNTHIQ - Ji\u0159\u00ed Val\u00e1\u0161ek\",\"description\":\"Mgr. Ji\u0159\u00ed Val\u00e1\u0161ek\",\"publisher\":{\"@id\":\"https:\\\/\\\/jirivalasek.com\\\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/jirivalasek.com\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\\\/\\\/jirivalasek.com\\\/#organization\",\"name\":\"DATASYNTHIQ - Ji\u0159\u00ed Val\u00e1\u0161ek\",\"url\":\"https:\\\/\\\/jirivalasek.com\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/jirivalasek.com\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/i0.wp.com\\\/jirivalasek.com\\\/wp-content\\\/uploads\\\/2023\\\/03\\\/brandmark-design-5.png?fit=2742%2C1140&ssl=1\",\"contentUrl\":\"https:\\\/\\\/i0.wp.com\\\/jirivalasek.com\\\/wp-content\\\/uploads\\\/2023\\\/03\\\/brandmark-design-5.png?fit=2742%2C1140&ssl=1\",\"width\":2742,\"height\":1140,\"caption\":\"DATASYNTHIQ - Ji\u0159\u00ed Val\u00e1\u0161ek\"},\"image\":{\"@id\":\"https:\\\/\\\/jirivalasek.com\\\/#\\\/schema\\\/logo\\\/image\\\/\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL-en - DATASYNTHIQ - Ji\u0159\u00ed Val\u00e1\u0161ek","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/jirivalasek.com\/?page_id=772&lang=en","og_locale":"en_US","og_type":"article","og_title":"SQL-en - DATASYNTHIQ - Ji\u0159\u00ed Val\u00e1\u0161ek","og_description":"FACTORS INFLUENCING THE SPREAD OF CORONAVIRUS Preparation of data to determine factors influencing the spread of coronavirus at the national level. Technologies used: DBeaver, SQL, Tableau. The resulting data will be panel data, keys will be state and day. In addition to daily increases in infections, the number of tests performed and the population of […]","og_url":"https:\/\/jirivalasek.com\/?page_id=772&lang=en","og_site_name":"DATASYNTHIQ - Ji\u0159\u00ed Val\u00e1\u0161ek","article_modified_time":"2023-04-22T09:54:46+00:00","og_image":[{"url":"https:\/\/jirivalasek.com\/wp-content\/uploads\/2023\/04\/panel_data-1024x588.png","type":"","width":"","height":""}],"twitter_card":"summary_large_image","twitter_misc":{"Est. reading time":"13 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/jirivalasek.com\/?page_id=772&lang=en","url":"https:\/\/jirivalasek.com\/?page_id=772&lang=en","name":"SQL-en - DATASYNTHIQ - Ji\u0159\u00ed Val\u00e1\u0161ek","isPartOf":{"@id":"https:\/\/jirivalasek.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/jirivalasek.com\/?page_id=772&lang=en#primaryimage"},"image":{"@id":"https:\/\/jirivalasek.com\/?page_id=772&lang=en#primaryimage"},"thumbnailUrl":"https:\/\/jirivalasek.com\/wp-content\/uploads\/2023\/04\/panel_data-1024x588.png","datePublished":"2023-03-15T17:19:31+00:00","dateModified":"2023-04-22T09:54:46+00:00","breadcrumb":{"@id":"https:\/\/jirivalasek.com\/?page_id=772&lang=en#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/jirivalasek.com\/?page_id=772&lang=en"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jirivalasek.com\/?page_id=772&lang=en#primaryimage","url":"https:\/\/i0.wp.com\/jirivalasek.com\/wp-content\/uploads\/2023\/04\/panel_data.png?fit=1476%2C847&ssl=1","contentUrl":"https:\/\/i0.wp.com\/jirivalasek.com\/wp-content\/uploads\/2023\/04\/panel_data.png?fit=1476%2C847&ssl=1","width":1476,"height":847},{"@type":"BreadcrumbList","@id":"https:\/\/jirivalasek.com\/?page_id=772&lang=en#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/jirivalasek.com\/"},{"@type":"ListItem","position":2,"name":"SQL-en"}]},{"@type":"WebSite","@id":"https:\/\/jirivalasek.com\/#website","url":"https:\/\/jirivalasek.com\/","name":"DATASYNTHIQ - Ji\u0159\u00ed Val\u00e1\u0161ek","description":"Mgr. Ji\u0159\u00ed Val\u00e1\u0161ek","publisher":{"@id":"https:\/\/jirivalasek.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/jirivalasek.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/jirivalasek.com\/#organization","name":"DATASYNTHIQ - Ji\u0159\u00ed Val\u00e1\u0161ek","url":"https:\/\/jirivalasek.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jirivalasek.com\/#\/schema\/logo\/image\/","url":"https:\/\/i0.wp.com\/jirivalasek.com\/wp-content\/uploads\/2023\/03\/brandmark-design-5.png?fit=2742%2C1140&ssl=1","contentUrl":"https:\/\/i0.wp.com\/jirivalasek.com\/wp-content\/uploads\/2023\/03\/brandmark-design-5.png?fit=2742%2C1140&ssl=1","width":2742,"height":1140,"caption":"DATASYNTHIQ - Ji\u0159\u00ed Val\u00e1\u0161ek"},"image":{"@id":"https:\/\/jirivalasek.com\/#\/schema\/logo\/image\/"}}]}},"jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/jirivalasek.com\/index.php?rest_route=\/wp\/v2\/pages\/772","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jirivalasek.com\/index.php?rest_route=\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/jirivalasek.com\/index.php?rest_route=\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/jirivalasek.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jirivalasek.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=772"}],"version-history":[{"count":13,"href":"https:\/\/jirivalasek.com\/index.php?rest_route=\/wp\/v2\/pages\/772\/revisions"}],"predecessor-version":[{"id":786,"href":"https:\/\/jirivalasek.com\/index.php?rest_route=\/wp\/v2\/pages\/772\/revisions\/786"}],"wp:attachment":[{"href":"https:\/\/jirivalasek.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=772"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}