Plánování dotazu

Reporting in SQL

Tyler Pernes

Learning & Development Consultant

Cíl kapitoly

Reporting in SQL

Klíčové otázky

  • Ze kterých tabulek čerpáme?
  • Jak tabulky propojit?
  • Jaká pole je třeba vytvořit?
  • Jaké filtry použít?
  • Je potřeba řadit nebo omezit výsledky?
Reporting in SQL

Scénář

Gold Medals by Demographic Group
(Western European Countries Only)
+----------+--------------------+-------+
| season   |  demographic_group | golds |
|----------|--------------------|-------|
| Winter   | Male Age 26+       | 13    |
| Winter   | Female Age 26+     | 8     |
| Summer   | Male Age 13-25     | 7     |
| Summer   | Female Age 13-25   | 6     |
| Winter   | Male Age 13-25     | 4     |
| Summer   | Male Age 26+       | 4     |
| Winter   | Female Age 13-25   | 4     |
| Summer   | Female Age 26+     | 2     |
+----------+--------------------+-------+
Reporting in SQL

1 – Ze kterých tabulek čerpáme?

Reporting in SQL

2 – Jak propojit tabulky?

Reporting in SQL

2 – Jak propojit tabulky?

Reporting in SQL

2 – Jak propojit tabulky?

Reporting in SQL

2 – Jak propojit tabulky?

Reporting in SQL

3 – Jaká pole je třeba vytvořit?

+----------+--------------------+-------+
| season   |  demographic_group | golds |
|----------|--------------------|-------|
| Winter   | Male Age 26+       | 13    |
| Winter   | Female Age 26+     | 8     |
| Summer   | Male Age 13-25     | 7     |
| Summer   | Female Age 13-25   | 6     |
| Winter   | Male Age 13-25     | 4     |
| Summer   | Male Age 26+       | 4     |
+----------+--------------------+-------+
  • season - static string
  • demographic_group - conditional
  • golds - SUM()
Reporting in SQL

4 – Jaké filtry je třeba použít?

Gold Medals by Demographic Group
(Western European Countries Only)
+----------+--------------------+-------+
| season   |  demographic_group | golds |
|----------|--------------------|-------|
| Winter   | Male Age 26+       | 13    |
| Winter   | Female Age 26+     | 8     |
| Summer   | Male Age 13-25     | 7     |
| Summer   | Female Age 13-25   | 6     |
| Winter   | Male Age 13-25     | 4     |
| Summer   | Male Age 26+       | 4     |
+----------+--------------------+-------+
  • WHERE nebo HAVING?
  • Filtr na dimenzi = WHERE
Reporting in SQL

5 – Je potřeba řadit nebo omezit výsledky?

Gold Medals by Demographic Group
(Western European Countries Only)
+----------+--------------------+-------+
| season   |  demographic_group | golds |
|----------|--------------------|-------|
| Winter   | Male Age 26+       | 13    |
| Winter   | Female Age 26+     | 8     |
| Summer   | Male Age 13-25     | 7     |
| Summer   | Female Age 13-25   | 6     |
| Winter   | Male Age 13-25     | 4     |
| Summer   | Male Age 26+       | 4     |
+----------+--------------------+-------+
  • LIMIT není potřeba
  • Seřadit podle golds sestupně
Reporting in SQL

Pojďme procvičovat!

Reporting in SQL

Preparing Video For Download...