Merencanakan kueri

Pelaporan dengan SQL

Tyler Pernes

Learning & Development Consultant

Tujuan bab

Pertanyaan untuk ditanyakan

Kita perlu mengambil dari tabel apa?
Bagaimana kita harus menggabungkan tabel?
Bidang apa yang perlu kita buat?
Filter apa yang perlu disertakan?
Perlu pengurutan atau pembatasan?

Skenario

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     |
+----------+--------------------+-------+

1 - Kita perlu mengambil dari tabel apa?

2 - Bagaimana kita menggabungkan tabel?

3 - Bidang apa yang perlu kita buat?

+----------+--------------------+-------+
| 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()

4 - Filter apa yang perlu disertakan?

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 atau HAVING?
Filter pada dimensi = WHERE

5 - Perlu pengurutan atau pembatasan?

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     |
+----------+--------------------+-------+

Tidak perlu LIMIT
Urutkan berdasarkan golds menurun

Ayo berlatih!

Pelaporan dengan SQL