Sorguyu planlama

SQL ile Raporlama

Tyler Pernes

Learning & Development Consultant

Bölüm hedefi

Sorulacak sorular

Hangi tablolardan çekmeliyiz?
Tabloları nasıl birleştirmeliyiz?
Hangi alanları oluşturmalıyız?
Hangi filtreler eklenmeli?
Sıralama veya sınır gerekli mi?

Senaryo

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 - Hangi tablolardan çekmeliyiz?

2 - Tabloları nasıl birleştirmeliyiz?

3 - Hangi alanları oluşturmalıyız?

+----------+--------------------+-------+
| 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 - Hangi filtreler eklenmeli?

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 mı yoksa HAVING mi?
Boyuta göre filtre = WHERE

5 - Sıralama veya sınır gerekli mi?

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 gerekmez
golds alanına göre azalan sıralama

Hadi pratik yapalım!

SQL ile Raporlama