{"id":156,"date":"2023-03-15T17:19:31","date_gmt":"2023-03-15T17:19:31","guid":{"rendered":"https:\/\/jirivalasek.com\/?page_id=156"},"modified":"2023-08-19T07:12:39","modified_gmt":"2023-08-19T07:12:39","slug":"sql-projekt","status":"publish","type":"page","link":"https:\/\/jirivalasek.com\/?page_id=156","title":{"rendered":"SQL"},"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

FAKTORY OVLIV\u0147UJ\u00cdC\u00cd \u0160\u00cd\u0158EN\u00cd KORONAVIRU<\/strong><\/h4>

P\u0159\u00edprava dat pro ur\u010dov\u00e1n\u00ed faktor\u016f ovliv\u0148uj\u00edc\u00edch \u0161\u00ed\u0159en\u00ed koronaviru na \u00farovni jednotliv\u00fdch st\u00e1t\u016f.<\/p>

Pou\u017eit\u00e9 technologie: 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

V\u00fdsledn\u00e1 data budou panelov\u00e1, kl\u00ed\u010de budou st\u00e1t\u00a0a den.<\/p>

Pro ur\u010den\u00ed hlavn\u00edch faktor\u016f \u0161\u00ed\u0159en\u00ed koronaviru je pot\u0159eba krom\u011b denn\u00edch n\u00e1r\u016fst\u016f naka\u017een\u00fdch p\u0159i\u0159adit i po\u010dty proveden\u00fdch test\u016f a po\u010det obyvatel dan\u00e9ho st\u00e1tu. Z t\u011bchto t\u0159\u00ed prom\u011bnn\u00fdch je potom mo\u017en\u00e9 vytvo\u0159it vhodnou vysv\u011btlovanou prom\u011bnnou. Denn\u00ed po\u010dty naka\u017een\u00fdch se budou d\u00e1le vysv\u011btlovat pomoc\u00ed prom\u011bnn\u00fdch n\u011bkolika typ\u016f. Ka\u017ed\u00fd sloupec v tabulce bude p\u0159edstavovat jednu prom\u011bnnou. Chceme z\u00edskat n\u00e1sleduj\u00edc\u00ed sloupce:<\/p>

  1. \u010casov\u00e9 prom\u011bnn\u00e9:
    • bin\u00e1rn\u00ed prom\u011bnn\u00e1 pro v\u00edkend \/ pracovn\u00ed den,<\/li>
    • ro\u010dn\u00ed obdob\u00ed dan\u00e9ho dne (zak\u00f3dovat jako 0 a\u017e 3).<\/li><\/ul><\/li>
    • Prom\u011bnn\u00e9 specifick\u00e9 pro dan\u00fd st\u00e1t:
      • hustota zalidn\u011bn\u00ed – ve st\u00e1tech s vy\u0161\u0161\u00ed hustotou zalidn\u011bn\u00ed se n\u00e1kaza m\u016f\u017ee \u0161\u00ed\u0159it rychleji,<\/li>
      • HDP na obyvatele – pou\u017eijeme jako indik\u00e1tor ekonomick\u00e9 vysp\u011blosti st\u00e1tu,<\/li>
      • GINI koeficient – m\u00e1 majetkov\u00e1 nerovnost vliv na \u0161\u00ed\u0159en\u00ed koronaviru?<\/li>
      • d\u011btsk\u00e1 \u00famrtnost – pou\u017eijeme jako indik\u00e1tor kvality zdravotnictv\u00ed,<\/li>
      • medi\u00e1n v\u011bku obyvatel v roce 2018 – st\u00e1ty se star\u0161\u00edm obyvatelstvem mohou b\u00fdt posti\u017eeny v\u00edce,<\/li>
      • pod\u00edly jednotliv\u00fdch n\u00e1bo\u017eenstv\u00ed – pou\u017eijeme jako proxy prom\u011bnnou pro kulturn\u00ed specifika. Pro ka\u017ed\u00e9 n\u00e1bo\u017eenstv\u00ed v dan\u00e9m st\u00e1t\u011b procentn\u00ed pod\u00edl jeho p\u0159\u00edslu\u0161n\u00edk\u016f na celkov\u00e9m obyvatelstvu,<\/li>
      • rozd\u00edl mezi o\u010dek\u00e1vanou dobou do\u017eit\u00ed v roce 1965 a v roce 2015 – st\u00e1ty, ve kter\u00fdch prob\u011bhl rychl\u00fd rozvoj mohou reagovat jinak ne\u017e zem\u011b, kter\u00e9 jsou vysp\u011bl\u00e9 u\u017e del\u0161\u00ed dobu.<\/li><\/ul><\/li>
      • Po\u010das\u00ed (ovliv\u0148uje chov\u00e1n\u00ed lid\u00ed a tak\u00e9 schopnost \u0161\u00ed\u0159en\u00ed viru):
        • pr\u016fm\u011brn\u00e1 denn\u00ed (nikoli no\u010dn\u00ed!) teplota,<\/li>
        • po\u010det hodin v dan\u00e9m dni, kdy byly sr\u00e1\u017eky nenulov\u00e9,<\/li>
        • maxim\u00e1ln\u00ed s\u00edla v\u011btru v n\u00e1razech b\u011bhem dne.<\/li><\/ul><\/li><\/ol>

          \u00a0<\/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

          V\u00ddSTUP<\/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

          Uk\u00e1zka z v\u00fdsledn\u00fdch panelov\u00fdch dat.<\/p>

          V\u00fdsledn\u00e1 tabulka obsahuje \u00fadaje v jednotliv\u00fdch zem\u00edch platn\u00fdch dan\u00fd den.<\/p>

          Tabulka m\u00e1 94 142 \u0159\u00e1dk\u016f.<\/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

          Jedna z mo\u017en\u00fdch vizualizac\u00ed \u0161\u00ed\u0159en\u00ed koronaviru v Evropsk\u00fdch zem\u00edch.<\/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

          POSTUP<\/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":"

          FAKTORY OVLIV\u0147UJ\u00cdC\u00cd \u0160\u00cd\u0158EN\u00cd KORONAVIRU P\u0159\u00edprava dat pro ur\u010dov\u00e1n\u00ed faktor\u016f ovliv\u0148uj\u00edc\u00edch \u0161\u00ed\u0159en\u00ed koronaviru na \u00farovni jednotliv\u00fdch st\u00e1t\u016f. Pou\u017eit\u00e9 technologie: DBeaver, SQL, Tableau. V\u00fdsledn\u00e1 data budou panelov\u00e1, kl\u00ed\u010de budou st\u00e1t\u00a0a den. Pro ur\u010den\u00ed hlavn\u00edch faktor\u016f \u0161\u00ed\u0159en\u00ed koronaviru je pot\u0159eba krom\u011b denn\u00edch n\u00e1r\u016fst\u016f naka\u017een\u00fdch p\u0159i\u0159adit i po\u010dty proveden\u00fdch test\u016f a po\u010det obyvatel dan\u00e9ho st\u00e1tu. Z t\u011bchto t\u0159\u00ed prom\u011bnn\u00fdch […]<\/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-156","page","type-page","status-publish","hentry"],"yoast_head":"\nSQL - 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=156\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL - DATASYNTHIQ - Ji\u0159\u00ed Val\u00e1\u0161ek\" \/>\n<meta property=\"og:description\" content=\"FAKTORY OVLIV\u0147UJ\u00cdC\u00cd \u0160\u00cd\u0158EN\u00cd KORONAVIRU P\u0159\u00edprava dat pro ur\u010dov\u00e1n\u00ed faktor\u016f ovliv\u0148uj\u00edc\u00edch \u0161\u00ed\u0159en\u00ed koronaviru na \u00farovni jednotliv\u00fdch st\u00e1t\u016f. Pou\u017eit\u00e9 technologie: DBeaver, SQL, Tableau. V\u00fdsledn\u00e1 data budou panelov\u00e1, kl\u00ed\u010de budou st\u00e1t\u00a0a den. Pro ur\u010den\u00ed hlavn\u00edch faktor\u016f \u0161\u00ed\u0159en\u00ed koronaviru je pot\u0159eba krom\u011b denn\u00edch n\u00e1r\u016fst\u016f naka\u017een\u00fdch p\u0159i\u0159adit i po\u010dty proveden\u00fdch test\u016f a po\u010det obyvatel dan\u00e9ho st\u00e1tu. Z t\u011bchto t\u0159\u00ed prom\u011bnn\u00fdch […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/jirivalasek.com\/?page_id=156\" \/>\n<meta property=\"og:site_name\" content=\"DATASYNTHIQ - Ji\u0159\u00ed Val\u00e1\u0161ek\" \/>\n<meta property=\"article:modified_time\" content=\"2023-08-19T07:12:39+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=156\",\"url\":\"https:\\\/\\\/jirivalasek.com\\\/?page_id=156\",\"name\":\"SQL - DATASYNTHIQ - Ji\u0159\u00ed Val\u00e1\u0161ek\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/jirivalasek.com\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/jirivalasek.com\\\/?page_id=156#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/jirivalasek.com\\\/?page_id=156#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/jirivalasek.com\\\/wp-content\\\/uploads\\\/2023\\\/04\\\/panel_data-1024x588.png\",\"datePublished\":\"2023-03-15T17:19:31+00:00\",\"dateModified\":\"2023-08-19T07:12:39+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/jirivalasek.com\\\/?page_id=156#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/jirivalasek.com\\\/?page_id=156\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/jirivalasek.com\\\/?page_id=156#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=156#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/jirivalasek.com\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL\"}]},{\"@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 - 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=156","og_locale":"en_US","og_type":"article","og_title":"SQL - DATASYNTHIQ - Ji\u0159\u00ed Val\u00e1\u0161ek","og_description":"FAKTORY OVLIV\u0147UJ\u00cdC\u00cd \u0160\u00cd\u0158EN\u00cd KORONAVIRU P\u0159\u00edprava dat pro ur\u010dov\u00e1n\u00ed faktor\u016f ovliv\u0148uj\u00edc\u00edch \u0161\u00ed\u0159en\u00ed koronaviru na \u00farovni jednotliv\u00fdch st\u00e1t\u016f. Pou\u017eit\u00e9 technologie: DBeaver, SQL, Tableau. V\u00fdsledn\u00e1 data budou panelov\u00e1, kl\u00ed\u010de budou st\u00e1t\u00a0a den. Pro ur\u010den\u00ed hlavn\u00edch faktor\u016f \u0161\u00ed\u0159en\u00ed koronaviru je pot\u0159eba krom\u011b denn\u00edch n\u00e1r\u016fst\u016f naka\u017een\u00fdch p\u0159i\u0159adit i po\u010dty proveden\u00fdch test\u016f a po\u010det obyvatel dan\u00e9ho st\u00e1tu. Z t\u011bchto t\u0159\u00ed prom\u011bnn\u00fdch […]","og_url":"https:\/\/jirivalasek.com\/?page_id=156","og_site_name":"DATASYNTHIQ - Ji\u0159\u00ed Val\u00e1\u0161ek","article_modified_time":"2023-08-19T07:12:39+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=156","url":"https:\/\/jirivalasek.com\/?page_id=156","name":"SQL - DATASYNTHIQ - Ji\u0159\u00ed Val\u00e1\u0161ek","isPartOf":{"@id":"https:\/\/jirivalasek.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/jirivalasek.com\/?page_id=156#primaryimage"},"image":{"@id":"https:\/\/jirivalasek.com\/?page_id=156#primaryimage"},"thumbnailUrl":"https:\/\/jirivalasek.com\/wp-content\/uploads\/2023\/04\/panel_data-1024x588.png","datePublished":"2023-03-15T17:19:31+00:00","dateModified":"2023-08-19T07:12:39+00:00","breadcrumb":{"@id":"https:\/\/jirivalasek.com\/?page_id=156#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/jirivalasek.com\/?page_id=156"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jirivalasek.com\/?page_id=156#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=156#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/jirivalasek.com\/"},{"@type":"ListItem","position":2,"name":"SQL"}]},{"@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\/156","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=156"}],"version-history":[{"count":35,"href":"https:\/\/jirivalasek.com\/index.php?rest_route=\/wp\/v2\/pages\/156\/revisions"}],"predecessor-version":[{"id":874,"href":"https:\/\/jirivalasek.com\/index.php?rest_route=\/wp\/v2\/pages\/156\/revisions\/874"}],"wp:attachment":[{"href":"https:\/\/jirivalasek.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=156"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}